Database (1064) SQLSTATE[42000]: Syntax error | Importing using phpmydirectory

So, we use a script called “phpmydirectory”, and at this point it’s pretty outdated. It wasn’t designed to work on PHP 7+ or MySQL 5.7+ but we’re mostly making it work.

Part of the script, you can import new “listings (like articles on a blog)” from a .csv file. We recently updated our database server from Ubuntu 18.04 to 20.04 and MySQL also updated to 8 from 5.7 (we have some strict mode settings turned off for date_time format etc).

I am seeing this error when importing and I’m not sure why:

Fatal Error: Database (1064) SQLSTATE[42000]: Syntax error or access violation: 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 'ROW, @previous_parent_id := parent_id, parent_id, id FROM pmd_lo' at line 1 in 
file /usr/www/dd/phpmyd/includes/class_database.php on line 202 (SQL: UPDATE pmd_locations a, (SELECT IF(@previous_parent_id = 
parent_id, @row := @row +1, @row :=1) AS ROW, @previous_parent_id := parent_id, parent_id, id FROM pmd_locations JOIN (SELECT @row :=0, 
@previous_parent_id :=0) i ORDER BY parent_id, left_) aa SET a.child_row_id = aa.row WHERE a.parent_id = aa.parent_id AND a.id = aa.id) in 
/usr/www/dd/phpmyd/includes/class_database.php on line 207

Here is the trace:

File: /usr/www/dd/phpmyd/includes/class_tree_gateway.php   
Line: 111   
Class: Database   
Relevant PHP/MYSQL code:   
https://www.paste.org/112726

File: /usr/www/dd/phpmyd/includes/class_imports.php   
Line: 666   
Class: Tree_Gateway   
Relevant PHP/MYSQL code:    
https://www.paste.org/112731

File: /usr/www/dd/phpmyd/cp/admin_ajax.php
Line: 616
Class: Imports
Relevant PHP/MYSQL code:    
https://www.paste.org/112732

I’m really not sure here. Apologies.

Answer

Based on the keyword and reserved words information since version 8.0.2, ROW is now a reserved word:

So simply add quotes (or backticks) around that alias should help, such as: 'ROW'. There, and anywhere else the same may be needed.

Advertisement

Answer