SQL Script file works with mysql but not mycli.

[Fjeldfross]

Sorry that this is a bit lengthy, I'm just trying to provide what information that I have that may be of use. I'm using version 1.27.2 of mycli.

The following SQL script works without errors using the Windows mysql client but fails when using mycli:

# 	They don't
DROP TABLE IF EXISTS `Agents`;
CREATE TABLE `Agents` (
    `agentID` VARCHAR(64) NOT NULL PRIMARY KEY,
    `agent`   VARCHAR(64) NOT NULL UNIQUE
    );
INSERT INTO `Agents` (`agentID`, `agent`) VALUES ('xyz', 'Agent XYZ');

Note that this is an extremely cut down version to try to isolate the problem.

I get the following error message with mycli:

(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 'CREATE TABLE Agents(\n agentIDVARCHAR(64) NOT NULL PRIMARY KEY,\n age' at line 4")`

Please note that there is a character between the "#" and the "They". All other white-space characters are s.

Just remove the apostrophe in "don't" and it works.
Put the apostrophe back and remove the "INSERT INTO..." line and it works.
Replace the "#They" with "#They" and it works.
Replace the "#They" with "#They" and it works.

So I turned on -l and looked in .mycli.log. Looks like I'm getting Python errors logged.

2024-07-16 16:00:58,420 (180/MainThread) mycli.main ERROR - traceback: 'Traceback (most recent call last):
  File "C:\\Users\\Me\\AppData\\Roaming\\Python\\Python312\\site-packages\\mycli\\sqlexecute.py", line 252, in run
    for result in special.execute(cur, sql):
                  ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\\Users\\Me\\AppData\\Roaming\\Python\\Python312\\site-packages\\mycli\\packages\\special\\main.py", line 58, in execute
    raise CommandNotFound
mycli.packages.special.main.CommandNotFound

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\\Users\\Me\\AppData\\Roaming\\Python\\Python312\\site-packages\\mycli\\main.py", line 728, in one_iteration
    for title, cur, headers, status in res:
  File "C:\\Users\\Me\\AppData\\Roaming\\Python\\Python312\\site-packages\\mycli\\sqlexecute.py", line 252, in run
    for result in special.execute(cur, sql):
  File "C:\\Users\\Me\\AppData\\Roaming\\Python\\Python312\\site-packages\\mycli\\sqlexecute.py", line 256, in run
    cur.execute(sql)
  File "C:\\Users\\Me\\AppData\\Roaming\\Python\\Python312\\site-packages\\pymysql\\cursors.py", line 153, in execute
    result = self._query(query)
             ^^^^^^^^^^^^^^^^^^
  File "C:\\Users\\Me\\AppData\\Roaming\\Python\\Python312\\site-packages\\pymysql\\cursors.py", line 322, in _query
    conn.query(q)
  File "C:\\Users\\Me\\AppData\\Roaming\\Python\\Python312\\site-packages\\pymysql\\connections.py", line 563, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\\Users\\Me\\AppData\\Roaming\\Python\\Python312\\site-packages\\pymysql\\connections.py", line 825, in _read_query_result
    result.read()
  File "C:\\Users\\Me\\AppData\\Roaming\\Python\\Python312\\site-packages\\pymysql\\connections.py", line 1199, in read
    first_packet = self.connection._read_packet()
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\\Users\\Me\\AppData\\Roaming\\Python\\Python312\\site-packages\\pymysql\\connections.py", line 775, in _read_packet
    packet.raise_for_error()
  File "C:\\Users\\Me\\AppData\\Roaming\\Python\\Python312\\site-packages\\pymysql\\protocol.py", line 219, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "C:\\Users\\Me\\AppData\\Roaming\\Python\\Python312\\site-packages\\pymysql\\err.py", line 150, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (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 \'CREATE TABLE `Agents` (\n    `agentID` VARCHAR(64) NOT NULL PRIMARY KEY,\n    `age\' at line 3")'

That's a bit beyond the scope of my Python debugging.

This is not mission critical. It did take me quite a while to find a work-around though.

regards,
Fjeldfross

[rolandwalker]

Hi! I've just tried this out, maintaining the invisible tab character before "They", and it executed fine. Is it possible this was fixed by a later version of mycli?

This error also looks like it would be sensitive to the version of MySQL you are running, because MySQL is emitting the error. Mine is MySQL 8.4.2, connecting with mycli v1.36.0.