dbdelta(): issues when using backticks for reserved SQL keywords

[harmr]

Within my plugin "Leaflet Maps Marker" (​http://wordpress.org/extend/plugins/leaflet-maps-marker/) I am using the following dbdelta()-statement within /inc/install-and-updates.php to create the tables I need for my plugin:

$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);

I now found out that some mySQL-servers (clustered edition) have clustering as defined keyword (although it is not mentioned in their doc at ​https://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-5.html or ​https://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-6.html - have told the mySQLs guys about it, but they did not add it yet).

To fix this for my plugin users, I added backticks to corresponding SQL statements, including the dbdelta()-function:

$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);

This workaround is ok and works for my users (tested on several different installations) - it produces a PHP error (Undefined offset: 1) though in the PHP log files.

I now read in the Codex (​http://codex.wordpress.org/Creating_Tables_with_Plugins) the following:

You must not use any apostrophes or backticks around field names.

Which is really bad - I know that backticks work in dbdelta(), but it is officially not supported. Changing the column name to an unreserved name is not really an option, as dbdelta() does not support renaming existing columns and just adds a new column at the end of the table with the new name (as well as other dependencies I would not want to change because of this if there is another workaround).

Is the info in the codex about backticks still valid? If yes, is there a way to officially support backticks within dbdelta() in one of the next releases, as currently I do not know another way how to solve this within my plugin.
Thanks for any help!

Robert

[jdgrimes]

Possibly a duplicate of #20263.

[dd32]

Duplicate of #20263.