3 ms·

Am I understanding this correctly: LOAD DATA LOCAL is used to bulk load datasets (like CSV files) into a MySQL database from the client. Why is the file transfe

by lqet 8y ago

Am I understanding this correctly: LOAD DATA LOCAL is used to bulk load datasets (like CSV files) into a MySQL database from the client. Why is the file transfer initiated by the server, and not by the client? Is it because they wanted to cover cases where the server processes / simplifies the query, and after processing, it becomes evident that the file is not needed at all? If so, why isn't there an additional check on the client that the file requested by the server matches a file that was previously used in a LOAD DATA LOCAL query? Is it because there is no query processing / parsing on the client side at all (in case of string SQL queries)?

beagle3 8y ago
The client does not parse the SQL prior to sending it to the server. The name of the file may actually be a computation result that depends on data in tables IIRC.
- adrianmonk 8y ago
  The docs say that it must be a literal: > The file name must be given as a literal string. (From https://dev.mysql.com/doc/refman/8.0/en/load-data.html https://dev.mysql.com/doc/refman/8.0/en/load-data.html ) However, you raise an interesting point about parsing. It is reasonable to want the server to do the parsing. You could, for example, tweak the grammar to add a new feature and do it entirely in an server update without needing to deploy a new client binary to every user. But then that makes it a bit harder to fix bugs like this. There are surely some commands parsed and implemented by the client, though. Like connect, quit, and source for example. So another way to have designed this would be to make a local command to designate a file that the server will be allowed to access. You could define the path name on the client side and pass a symbolic reference to the server. Then the client could parse that command without taking on the burden of parsing SQL, and the client could still own opening files rather than taking orders from the server.
- frutiger 8y ago
  Sure, but the command could've been: "Load this file: <stream of bytes>", rather than "Load this file: <path to file>" which necessarily requires the server to parse the command.

by lqet 8y ago

beagle3 8y ago

The client does not parse the SQL prior to sending it to the server. The name of the file may actually be a computation result that depends on data in tables IIRC.

adrianmonk 8y ago
The docs say that it must be a literal: > The file name must be given as a literal string. (From https://dev.mysql.com/doc/refman/8.0/en/load-data.html https://dev.mysql.com/doc/refman/8.0/en/load-data.html ) However, you raise an interesting point about parsing. It is reasonable to want the server to do the parsing. You could, for example, tweak the grammar to add a new feature and do it entirely in an server update without needing to deploy a new client binary to every user. But then that makes it a bit harder to fix bugs like this. There are surely some commands parsed and implemented by the client, though. Like connect, quit, and source for example. So another way to have designed this would be to make a local command to designate a file that the server will be allowed to access. You could define the path name on the client side and pass a symbolic reference to the server. Then the client could parse that command without taking on the burden of parsing SQL, and the client could still own opening files rather than taking orders from the server.
frutiger 8y ago
Sure, but the command could've been: "Load this file: <stream of bytes>", rather than "Load this file: <path to file>" which necessarily requires the server to parse the command.