MySQL Bugs: #87787: mysqldbimport shell utility fails if any column name includes

Bug #87787	mysqldbimport shell utility fails if any column name includes _value in its name
Submitted:	17 Sep 2017 17:16	Modified:	19 Sep 2017 13:21
Reporter:	Don Alejandro	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Utilities	Severity:	S3 (Non-critical)
Version:	1.6.5	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
If I use the MySQL Workbench 6.3 shell utility mysqldbimport to import a database from an sql file, the import fails if it encounters any INSERT statement that includes ‘_value’ within a column name. This happens only if the INSERT statement lists the column names, which seems to be standard with phpMyAdmin exports (or if the ‘complete-insert’ option is used when exporting with MySQL Workbench).
	
For example, a typical wordpress database has a column named meta_value in the wp_commentmeta table.  The import fails when mysqldbimport encounters this statement in the sql file:

INSERT INTO `wp_commentmeta` (`meta_id`, `comment_id`, `meta_key`, `meta_value`) VALUES
(1, 642, '_wp_trash_meta_status', '0'),…

The error message looks like this:

ERROR: Invalid statement:
INSERT INTO `wp_commentmeta` (`meta_id`, `comment_id`, `meta_key`, `meta_ VALUES `) 
ERROR: Query failed. 1064 (42000): You have an error in your SQL syntax; check t
he manual that corresponds to your MySQL server version for the right syntax to
use near '' at line 1

Note that in the error message `meta_value` became `meta_ VALUES` (there is a space between meta_ and VALUES).  It appears that the last part of the column name was dropped and replaced with a space and the prettified keyword VALUES.  The insert fails because there is no such column name.

As an experiment, I edited the sql file to change ‘meta_value’ to ‘meta_malue’ and the mysqldbimport utility successfully imported the wp_commentmeta table and other tables, until it failed when executing the following INSERT:

INSERT INTO `wp_options` (`option_id`, `option_name`, `option_value`, `autoload`) VALUES
(1, 'siteurl', 'http://wvcag.org/blog';, 'yes'),..

which resulted in the following error:

ERROR: Invalid statement:
INSERT INTO `wp_options` (`option_id`, `option_name`, `option_ VALUES`, `autoload`) 

The same problem happens on another database containing a table with a column named ‘cag_ddl_value’ (mysqldbimport changes the column name to ‘cag_ddl_ VALUES’ before sending the sql to the MySQL5.7 server).

I note that if I use the ‘Data import/Restore’ panel of MySQL Workbench to import the same sql files the imports complete with no errors.  The problem only seems to occur when using mysqldbimport shell utility.  If I edit the sql files to change all instances of ‘_value’ to ‘_malue’ the imports complete successfully using the mysqldbimport utility.

I’m running MySQL 5.7, workbench, shell, and utilities on two separate laptops as testing/development servers, one windows 7 and the other windows 8 – both installations exhibit the problem with mysqldbimport screwing up table names that include _value.

How to repeat:
Attempt to import a wordpress database that was exported by phpMyAdmin.  

Or create a test database with a table that has a column named 'foo_value' and at least one row of data in the table, export that database with the ‘complete-insert’ option, then try to import it using the mysqldbimport shell utility.  I created just such a test database and uploaded it as a zip file here: http://spectrumz.com/test-database-recreating-mysqldbimport-error/

Please provide the output of SHOW CREATE TABLE wp_commentmeta; Thanks.

The ‘wp_commentmeta’ table (and the ‘some_table’ table in my test schema) appear to have been created correctly – the column names are correct.  The results of SHOW CREATE TABLE wp_commentmeta;   is:

# Table, Create Table
wp_commentmeta, CREATE TABLE `wp_commentmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `comment_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`meta_id`),
  KEY `comment_id` (`comment_id`),
  KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

I ran another test using a database that has one table with columns named ID and foovaluefoo, and one row of data in that table.  I exported the schema (with the complete-insert option turned on) and then imported it using mysqldbimport, which caused the following error:

ERROR: Invalid statement:
INSERT INTO `some_table` (`ID`, `foo VALUES foo`)

In general, if the substring ‘value’ appears as a fragment of a column name in any table, mysqldbimport will corrupt the column name by inserting the prettified keyword ‘ VALUES ‘ before executing the INSERT, thereby causing the INSERT to fail (which also terminates the import).  I’m not sure if this behavior is limited to the string ‘value’ or if mysqldbimport may also be corrupting other keyword-esque column names.

Note that mysqldpimport only screws up INSERT statements if the column names are listed in the INSERT (and if a column name contains ‘value’).  The same sql files that fail when using mysqldbimport work fine with the Data import/Restore panel of WorkBench.

Fwiw, I tweaked the sql file I made available for testing to use foovalufoo as a column name (referenced in the ‘How to repeat’ section of my original bug report).

Just above I meant foovaluefoo, as in
I tweaked the sql file I made available for testing to use foovaluefoo as a column name (referenced in the ‘How to repeat’ section of my original bug report).

Thank you for the requested details.
Observed this with MySQL utilities 1.6.5

Thanks,
Umesh

i created a conection in workbench but i cant join this and send me an error messeg (report bug) how i can fix it