CODEWITHSUNDEEP
mysqlpowershellgoogle-cloud-sql
user3282173
user3282173

Reputation: 113

Loading CSV into CloudSQL

I have a Powershell script that currently loads some data from a CSV into a MySQL 5.1.73 server located on a local Linux box (It works great). I am editing this code to now load the data into a Google CloudSQL 5.7.14 database.

I have enabled the 'local_infile on' flag on the CloudSQL database instance and when I attempt to run the code I get the error:

 Exception calling "Fill" with "2" argument(s): "The used command is not allowed with this MySQL version"

When I SSH into the CloudSQL instance using the syntax:

mysql --local-infile=1 -u root -h XX.XX.XX.XX -p

and attempt to load the CSV everything works fine. If I do not use the:

--local-infile=1

flag I will get a similar error.

My question is, how do I initiate the connection from Powershell to essentially include the same '--local-infile=1' flag, if that is even possible?

My current connection string looks like:

 $constring="server=XX.XX.XX.XX;uid=USERNAME;pwd=PASSWORD;database=DATABASE;Pooling=False;"

I have scoured the internet for the last few hours and tried a few flags in an attempt to guess it and I have failed.

Thanks for the help.

Upvotes: 1

Views: 181

Answers (1)

Mayeru
Mayeru

Reputation: 1094

You want to load CSV files into your Cloud SQL (MySQL) instance, there are several ways to do so, please refer to the documentation to evaluate which one adjust better to your case.

I'm inferring in this case you using the "LOAD DATA LOCAL INFILE" statement if your CSV do not comply with the format requirements.

According to the Mysql documentation, you get a "The used command is not allowed with this MySQL version" error when "LOCAL capability is disabled, on either the server or client side" (is at the end of the page).

Which confirms what you have found out, you need to enable that flag on the client side. I have search around and not found a direct way to specify the flag on the PowerShell connection method. Could you try these alternatives?:

  1. Set "local-infile=1" on my.cnf or the MySQL configuration file on your system (if your are using Linux it should be around /etc/mysql/my.cnf)

  2. Using a Python3 script instead of PowerShell, the Python Mysql connector does allow the "allow_local_infile" flag.

Upvotes: 1

Related Questions