Localhost port forwarding causing invalid SQL [FIXED]

lucianocloudpay · July 8, 2016, 11:55am

I expect this issue isn’t actually related to SQL or MySQL. I think this is just highlighting a deeper issue with the way the localhost port forwarding feature works.

Expected behavior

Running a large piece of SQL from a Java App or MySQL Client (e.g. Toad for MySQL) against a MySQL instance running in Docker via the localhost/127.0.0.1 hostname should return the result set.

Actual behavior

The SQL that is run isn’t valid when running through localhost and MySQL returns an “You have an error in your SQL syntax”. Yet the same SQL does work when run using the “docker” hostname.

Information

5039D5E2-2700-49D9-9962-A550890481DE/2016-07-08_12-34-33
Window 10 Pro
Docker 1.12.0-rc3-beta18 (build:5226)
Image: mysql:5.5
This was working before the last Docker for Windows release (RC2)

Steps to reproduce the behavior

The SQL only fails to run when using “localhost” or “127.0.0.1” in the connection details. It works when using the “docker” hostname. Which is why i think it’s the port forwarding that is causing the issue.

Enable “Expose container ports on localhost” in docker settings
Fire up the official mysql image on port 3306 with user: root/password

docker run --name test-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password -d mysql

Then run a large bit of valid SQL against the database using the MySQL CLI (or any other MySQL Client) on the Windows host…

mysql -h localhost -u root --password=password -e “select ‘Moby says moooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo’”

This will error with the message…

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘says moooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo’ at line 1

Weirdly if you run this a second time in the same Poweshell window then it works. If you close and open a new window then it fails.

If you repeat the above but use the hostname “docker” in the MySQL client then it will work and return the result set correctly.

lucianocloudpay · July 11, 2016, 1:05pm

It would be great if someone could verify this issue using the steps above. thanks

friism · July 11, 2016, 1:11pm

Thanks for reporting this. Would it be possible to provide a simpler test that reproduces the error? Also, can you reproduce the error if using just the mysql CLI tool instead of toad?

lucianocloudpay · July 11, 2016, 1:45pm

@friism, i’ve updated the description with a MySQL CLI command. Let me know if you need anything else from me. Cheers

lucianocloudpay · July 14, 2016, 10:03pm

Tested on rc3/Beta19 and it’s still an issue.

codeamatic · July 15, 2016, 3:53am

I am able to reproduce something similar to this as well when using a GUI (sqlyog, mysql workbench and toad for mysql) and even when using the docker containers IP address.

If I drop the sql script directly into the editor of one of the GUI’s, it will run without issue. Same applies when I “exec” into the container and run mysql -uroot -proot < x.sql.

The problem arises when I use a GUI to import or run an external script. If I try to import or run the same external script (x.sql), I receive the following error message:

Error Code: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '...' at line 1

If I reduce the size of script by quite a bit, it will run successfully.

I’ve tested with multiple versions of the mysql images locally and they all fail. I’m also on Windows 10.

lucianocloudpay · July 20, 2016, 9:30am

Tested on rc4/Beta20 and it’s still an issue.

sreltgen · July 25, 2016, 10:09pm

I encountered the very same issue today : with the latest Docker for Windows beta (beta20), on Windows 10, running a large piece of SQL from a Java App against a PostgresSQL instance running in Docker would raise an error message in the PostgresSQL instance log file : ERROR: invalid message format if executed via the localhost/127.0.0.1 hostname. The exact same piece of SQL executed via the “docker” hostname would work flawlessly.

spbolton · July 26, 2016, 12:45am

I have the issue rc4/Beta20 with smaller form posts to my application server. It seems to loose random characters in the body of the message e.g. The <Analyzers/ element is missing an end > This happens with other data sent also. using docker instead of localhost or 127.0.0.1 works fine. and sometimes the invalid byte sequence error.

` An error occurred while parsing the XML data: Element type "Analyzers" must be followed by either attribute specifications, ">" or "/>". (line 72; col 21)            </ResultProcessingExits>         <Analyzers/       <TextConverters/>      </PSXSearchConfig>`

friism · July 26, 2016, 1:02am

Weve found a bug in the networking proxy that’s the likely cause of this. As a very temporary workaround, you can use docker instead of localhost. This will be fixed in the next beta release that’s going out this week. The docker hostname will stop working in that release (but localhost will be reliable).

lucianocloudpay · July 27, 2016, 1:14pm

Thank you @friism. Looking forward to the next release!

lucianocloudpay · July 29, 2016, 10:21am

Tested in Beta21 and it works!

mymmij · January 11, 2019, 12:26pm

Hi, I’m still having this issue with the latest version of the MySQL Container, any ideas? I can provide more details but I’m not sure what I should be looking for - the error is not very helpful & I’m not well-versed in MySQL.

Running docker exec -it mysql01 mysql -uroot -p

Followed by:

UPDATE mysql.user SET Password=PASSWORD('Password') WHERE User='root';

(or any other ostensibly valid command, but that’s the main one)