mySQL syntax error: queries suddenly do not work anymore WordPress
- SOLVED
I am the developer of [[LINK href="www.mapsmarker.com"]]www.mapsmarker.com[[/LINK]] and 2 users have reported that there layer maps suddenly stopped working. I already digged deep into the issue but can't find out yet what is causing this. I am pretty much assuming that it could be related to mySQL-version used (PHP version is unlikely as the statements fail using phpmyadmin but could also be).
Let me explain what´s going on:
the table for layer maps is created with dbdelta():
$table_name_layers = $wpdb->prefix.'leafletmapsmarker_layers';
$sql_layers_table = "CREATE TABLE " . $table_name_layers . " (
id int(6) unsigned NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
basemap varchar(25) NOT NULL,
layerzoom int(2) NOT NULL,
mapwidth int(4) NOT NULL,
mapwidthunit varchar(2) NOT NULL,
mapheight int(4) NOT NULL,
panel tinyint(1) NOT NULL,
layerviewlat decimal(9,6) NOT NULL,
layerviewlon decimal(9,6) NOT NULL,
createdby varchar(30) NOT NULL,
createdon datetime NOT NULL,
updatedby varchar(30) DEFAULT NULL,
updatedon datetime DEFAULT NULL,
controlbox int(1) NOT NULL,
overlays_custom int(1) NOT NULL,
overlays_custom2 int(1) NOT NULL,
overlays_custom3 int(1) NOT NULL,
overlays_custom4 int(1) NOT NULL,
wms tinyint(1) NOT NULL,
wms2 tinyint(1) NOT NULL,
wms3 tinyint(1) NOT NULL,
wms4 tinyint(1) NOT NULL,
wms5 tinyint(1) NOT NULL,
wms6 tinyint(1) NOT NULL,
wms7 tinyint(1) NOT NULL,
wms8 tinyint(1) NOT NULL,
wms9 tinyint(1) NOT NULL,
wms10 tinyint(1) NOT NULL,
listmarkers tinyint(1) NOT NULL,
multi_layer_map tinyint(1) NOT NULL,
multi_layer_map_list varchar(4000) DEFAULT NULL,
address varchar(255) NOT NULL,
clustering tinyint(1) unsigned NOT NULL,
gpx_url varchar(2083) NOT NULL,
gpx_panel tinyint(1) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;";
dbDelta($sql_layers_table);
When adding a new layer map, the following SQL-Statement is executed, which works fine on my servers, resulting in a row in the table wp_leafletmapsmarker_layers:
INSERT INTO wp_leafletmapsmarker_layers (name, basemap, layerzoom, mapwidth, mapwidthunit, mapheight, panel, layerviewlat, layerviewlon, createdby, createdon, updatedby, updatedon, controlbox, overlays_custom, overlays_custom2, overlays_custom3, overlays_custom4, wms, wms2, wms3, wms4, wms5, wms6, wms7, wms8, wms9, wms10, listmarkers, multi_layer_map, multi_layer_map_list, address, clustering, gpx_url, gpx_panel ) VALUES ('', 'osm_mapnik', 11, 640, 'px', 480, 1, '48.216038', '16.378984', 'Robert', '2014-08-18 17:04:33', 'Robert', '2014-08-18 17:04:33', 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, '', '', 1, '', 0)
If I execute the same statement on the servers of the 2 affected customers having issues, I am getting the following error [one uses mySQL 5.6.19, PHP 5.5.14 (updated recently but couldnt tell if maps still worked afterwards), phpMyAdmin 2.11.11.3, MySQL-Client-Version: 5.1.61]:
<blockquote>#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 'clustering, gpx_url, gpx_panel ) VALUES ('', 'osm_mapnik', 11, 640, 'px', 480, 1' at line 1 </blockquote>
If I remove <strong>clustering, gpx_url, gpx_panel</strong> and the according values <strong>1, '', 0</strong> from the statement it works and a new marker gets added.
I just do not see any syntax error here and I wonder why the exact statement works on my server. In addition here are screenshots from the customers phpmyadmin interface:
[[LINK href="http://s23.postimg.org/7y7h8aq6z/db_structure_not_working.png"]]structure of the table[[/LINK]]
[[LINK href="http://s14.postimg.org/xx7o4bhht/db_content_rows_non_working.png"]]content of the table[[/LINK]]
The mySQL query above should have actually created a new line with ID 11, but didnt.
Another effect of this bug is, that the retrieval of the data also does not work: when a layer is retrieved from database. Here the following SQL-statement is used:
$row = $wpdb->get_row('SELECT
id,name,basemap,mapwidth,mapheight,mapwidthunit,panel,layerzoom,layerviewlat,layerviewlon,controlbox,overlays_custom,overlays_custom2,overlays_custom3,overlays_custom4,wms,wms2,wms3,wms4,wms5,wms6,wms7,wms8,wms9,wms10,listmarkers,multi_layer_map,multi_layer_map_list,clustering,gpx_url,gpx_panel
FROM '.$table_name_layers.' WHERE id='.intval($layer), ARRAY_A);
this does not work in contrast to
$row = $wpdb->get_row('SELECT * FROM '.$table_name_layers.' WHERE id='.intval($layer), ARRAY_A);
respectively the query without selecting the column <strong>clustering</strong> - which made me think clustering might be a reserved word but according to my research it isnt - neither in mySQL nor PHP.
Any help on this issue is really appreciated as I really need to know what is causing this and how many users are potentially affected.
thx,
Robert
Answers (2)
Hariprasad Vijayan answers:
Hi Robert,
Error #1064 probably get when you use reserved words, am not sure which one is reserved word. Use backticks ( ` ) on your insert and select query.
Ex:
INSERT INTO `table` (`col1`, `col2`, `col3`) VALUES (1, 'test', '100');
SELECT `col1`, `col2`, `col3` FROM `table` WHERE `col1` = 1
Check following thread for more details.
[[LINK href="http://www.inmotionhosting.com/support/website/database-troubleshooting/error-1064"]]http://www.inmotionhosting.com/support/website/database-troubleshooting/error-1064[[/LINK]]
Hope it solve the issue.
Regards,
Hariprasad
Robert Seyfriedsberger comments:
thx - using backtips could be a workaround, anyway I would like to know the real reason here first, especially as I couldnt find any entries for reserved keywords for mysql 5.5 ([[LINK href="https://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-5.html"]]https://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-5.html[[/LINK]]) mysql 5.6 ([[LINK href="https://dev.mysql.com/doc/refman/5.6/en/reserved-words.html"]]https://dev.mysql.com/doc/refman/5.6/en/reserved-words.html[[/LINK]]) and PHP: [[LINK href="http://php.net/manual/en/reserved.php"]]http://php.net/manual/en/reserved.php[[/LINK]]
Hariprasad Vijayan comments:
I checked few more threads, so many people posted they got same error(Error #1064) for other reasons. Am not sure the exact reason might be version creates issue.
Robert Seyfriedsberger comments:
backticks seem to work on my clients site. Just one more question: is it also needed to use backticks when creating the column clustering via dbdelta?
Hariprasad Vijayan comments:
<em>backticks</em> is not strictly necessary for queries but a good idea for safety. @Dbrans posted more details about it.
Thanks.
Dbranes answers:
Hi, I tried your setup here on sqlfiddle:
[[LINK href="http://sqlfiddle.com/#!9/3837f/3"]]http://sqlfiddle.com/#!9/3837f/3[[/LINK]]
without errors on MySQL server version 5.6.6.
---
If you use clustering to store 0 or 1, I wonder why you don't use BOOL instead of TINYINT(1), to store it.
---
Did you try to create a new stripped down table, with only the id and the clustering fields? And play with INSERTS?
---
You could also try the backticks mentioned by @Hariprasad.
---
To test for reserved field names, you could try for example
select 1+1 as interval; // ERROR on sqlfiddle
select 1+1 as `interval`; // OK on sqlfiddle
so in your case you could test:
select 1+1 as clustering; // ???, OK on sqlfiddle
select 1+1 as `clustering`; // OK on sqlfiddle
Robert Seyfriedsberger comments:
backticks seem to work on my clients site. Just one more question: is it also needed to use backticks when creating the column clustering via dbdelta?
Dbranes comments:
Using preserved words for column names would give you error right there in dbDelta.
I wonder if we would like to use reserved words, in the first place ;-)
But then again, MySQL might introduce some of our field names as a reserved word in the future.
<strong>Possible solutions:</strong>
<blockquote>1. Use backticks - portability issues? Used by PHPMyAdmin.
2. Use double quotes - must have ANSI_QUOTES enabled?
3. Use columns names with words that are unlikely to be reserved in the future - but it's difficult to see far into the future!
4. Use a custom prefix on the columns - need to rewrite the PHP application?
</blockquote>
[[LINK href="http://stackoverflow.com/questions/5952677/should-i-use-backticks-or-not-when-escaping-keywords-in-mysql"]]Reference.[[/LINK]]
I checked all the dbDelta cases in the WordPress core, and the ones I encountered, did not use <em>backticks</em>, <em>double quotes</em> or special columns names <em>prefixes</em> ;-)
So it looks like the WordPress core is evolving according to #3.
=> So I guess there's no black/white options here, just gray ones with pros & cons ;-)