Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

database tables utf-8 conversion error


bruyndoncx

Recommended Posts

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

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

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 ..

Link to comment
Share on other sites

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

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

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

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...