Cache table not created on mySQL with default charset "utf8"

The API formerly known as Tera-WURFL
paulib
Posts: 1
Joined: Wed Jun 15, 2016 9:09 am

Cache table not created on mySQL with default charset "utf8"

Postby paulib » Wed Jun 15, 2016 9:36 am

Hi,

discovered bug on
ScientiaMobile WURFL Database API 1.7.1.0 (aka Tera-WURFL 2.4.1)
using MySQL5 or PDO DB connectors.

If database used for wurfl default charset is set to utf8, then during creation of device cache table script reports that "Device cache has been rebuilt.", but actually table creation fails.

Problem related to query
$createtable = "CREATE TABLE `$tablename` (
`user_agent` varchar(512) binary NOT NULL default '',
`cache_data` mediumtext NOT NULL,
PRIMARY KEY (`user_agent`)
) ENGINE=".self::$CACHE_STORAGE_ENGINE;

and following method call:
$this->dbcon->query($createtable);

- result is not verified on script (whether it was successful or not) while trying to execute query from mysql prompt I receive an error
#1071 - Specified key was too long; max key length is 1000 bytes

possible solution - add charset to SQL for table creation

$createtable = "CREATE TABLE `$tablename` (
`user_agent` varchar(512) binary NOT NULL default '',
`cache_data` mediumtext NOT NULL,
PRIMARY KEY (`user_agent`)
) ENGINE=".self::$CACHE_STORAGE_ENGINE . " DEFAULT CHARSET=latin1";

and in addition check the result of $this->dbcon->query method;

Who is online

Users browsing this forum: No registered users and 2 guests