database tables utf-8 conversion error

[♥bruyndoncx]

I'm manually converting my site to UTF-8 using the provided database_tables.php scripts and got this error on a custom table

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 'Ignore = convert(binary convert(Ignore using latin1) using utf8) where char_leng' at line 1

I have no clue what has gone wrong, anyone understands the error ?

Maybe the tinyint is not supported ? everything else looks pretty standard to me

 

CREATE TABLE `prijskaart` (
	`Prijskaartid` INT(11) NOT NULL AUTO_INCREMENT,
	`Merk` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
	`FamilyMaster` CHAR(6) NULL DEFAULT 'family' COLLATE 'utf8_unicode_ci',
	`SearchString` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
	`Ignore` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	`Show6` TINYINT(4) NOT NULL DEFAULT '0',
	`Intro` MEDIUMTEXT NULL COLLATE 'utf8_unicode_ci',
	`Width` INT(11) NOT NULL DEFAULT '0',
	`MaxLines` INT(11) NOT NULL DEFAULT '0',
	`ShowModel` INT(11) NOT NULL DEFAULT '0',
	`PrintBarcode` INT(11) NOT NULL DEFAULT '0',
	`ExtraSql` MEDIUMTEXT NULL COLLATE 'utf8_unicode_ci',
	PRIMARY KEY (`Prijskaartid`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=78

[azpro]

Ignore is probably a MySQL command .. Didn't really look in to it, but came to mind first .. Try to alter your fieldname to Ignore_me and try THE conversion again ..

[Jan Zonjee]

Ignore is probably a MySQL command

You are right: ignore is a reserved word in mysql.

[♥bruyndoncx]

ahaah, so fixed it with backticks as the mysql documentation suggests works in most cases

 

line 138 becomes

 

            $queries[] = "update " . $table . " set `" . $cols['Field'] . "` = convert(binary convert(`" . $cols['Field'] . "` using " . $old_charset . ") using utf8) where char_length(`" . $cols['Field'] . "`) = length(convert(binary convert(`" . $cols['Field'] . "` using " . $old_charset . ") using utf8))";
 

[♥bruyndoncx]

on a related note, the database_tables.php file mistakenly also lists views (cc @@burt)

not sure if it is something to take into account easily, but just thought i'd mention it

[MrPhil]

Yes, MySQL is likely to complain about any reserved word used as a name. It's good to get into the habit of backticking all field names (and possibly table names?) just in case you accidentally use one of the dozens and dozens of reserved words.

[greasemonkey]

I've just run into this same thing working on my new 2.3.4bs build. I've used the line above as a stop gap to convert the affected table - as it comes from quickbooks export and cant be changed.