[sr-dev] [ openser-Bugs-1605410 ] Installation problems while creating mysql dbase

Bugs item #1605410, was opened at 2006-11-29 09:56 Message generated for change (Comment added) made by You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=743020&aid=160541… Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: tools Group: None Status: Closed Resolution: Fixed Priority: 5 Private: No Submitted By: Nobody/Anonymous (nobody) Assigned to: Bogdan-Andrei Iancu (bogdan_iancu) Summary: Installation problems while creating mysql dbase Initial Comment: Trying to execute scripts/mysqldb.sh: omega1 scripts # sh ./mysqldb.sh create MySql password for root: creating database openser ... ERROR 1071 (42000) at line 106: Specified key was too long; max key length is 1000 bytes The problem is in primary key of 'location' table: omega1 scripts # mysql -u root -h localhost -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1212 to server version: 4.1.20-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> connect test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Connection id: 1213 Current database: test mysql> CREATE TABLE location ( -> username varchar(64) NOT NULL default '', -> domain varchar(128) NOT NULL default '', -> contact varchar(255) NOT NULL default '', -> received varchar(255) default NULL, -> path varchar(255) default NULL, -> expires datetime NOT NULL default '2020-05-28 21:32:15', -> q float(10,2) NOT NULL default '1.0', -> callid varchar(255) NOT NULL default 'Default-Call-ID', -> cseq int(11) NOT NULL default '42', -> last_modified datetime NOT NULL default "1900-01-01 00:00", -> flags int(11) NOT NULL default '0', -> user_agent varchar(255) NOT NULL default '', -> socket varchar(128) default NULL, -> methods int(11) default NULL, -> PRIMARY KEY(username, domain, contact) -> ) TYPE=MyISAM; ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes OS/mySQL versions: omega1 scripts # uname -a Linux omega1 2.6.15-gentoo-r5 #1 SMP Thu May 4 10:27:42 Local time zone must be set--see zic m x86_64 AMD Opteron(tm) Processor 248 AuthenticAMD GNU/Linux omega1 scripts # mysqladmin version -p Enter password: mysqladmin Ver 8.41 Distrib 4.1.20, for pc-linux-gnu on x86_64 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.1.20-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 20 days 14 min 38 sec Threads: 7 Questions: 146329 Slow queries: 0 Opens: 59 Flush tables: 1 Open tables: 19 Queries per second avg: 0.085 ---------------------------------------------------------------------- Comment By: Jethro () Date: 2012-02-24 23:30 Message: This is no longer an issue in recent versions of MySQL. Please revert this patch so as to remove this archaic restriction. ---------------------------------------------------------------------- Comment By: Bogdan-Andrei Iancu (bogdan_iancu) Date: 2006-12-05 10:52 Message: Logged In: YES user_id=1275325 Originator: NO Patch from Alex applied on CVS. Thanks Alex! regards, bogdan ---------------------------------------------------------------------- Comment By: Bogdan-Andrei Iancu (bogdan_iancu) Date: 2006-12-05 03:12 Message: Logged In: YES user_id=1275325 Originator: NO Hi Alex, thanks for the patch and your explanation, nice job :) - I will apply the patch and also I will take care in the near feature to change the primary keys to all tables. Thanks and regards, Bogdan PS: could you please provide the patch in unified format? ---------------------------------------------------------------------- Comment By: Nobody/Anonymous (nobody) Date: 2006-12-01 10:02 Message: Logged In: NO 2 bogdan_iancu: Greetings! Of course, as it's suggested in RDBMS theory (or concepts) - every table should have field ID as unique number getting from auto increment sequence. If you do so - it will be right enhancement, but will not solve the problem, because you still need to guarantee unique of whole those fields. It can be reached only with using UNIQUE mysql constraint which is also have the same limitation on max length in bytes. The most proper solution will be next one: 1) Test, what's the character encoding is used in database. 2) If it's a UCS-2 or UTF8 (where 3 bytes per char are used) then warn user that db couldn't be created and prompt him to select another character encoding among available. 3) In a "CREATE DATABASE" use closure "CHARACTER SET" Here is mysqldb.sh.patch file - that's do it. Please apply it on mysqldb.sh and include patched file into future version. Sincerely yours, Khalukhin Alex &lt;khalukhin(a)gmail.com&gt; ---------------------- << CUT_HERE << ------------------------- 236a237,254 $CMD "-p$PW" | awk '{print $2}' | sed -e 1d` $1}' | sed -e 1d | grep -ivE utf8\|ucs2` used to create DB. Please choice another one from the following list:" 244a263,264 248c268 < create database $1; --- ---------------------- << CUT_HERE << ------------------------- ---------------------------------------------------------------------- Comment By: Bogdan-Andrei Iancu (bogdan_iancu) Date: 2006-12-01 03:28 Message: Logged In: YES user_id=1275325 Originator: NO Hi, not sure if "DEFAULT CHARACTER SET LATIN1" is standard enough to be directly forced by the installation script - I'm not a mysql expert to know if the LATIN1 set will be present on all mysql servers. The long term solution wil be to change the primary keys of all tables to int auto-increment - this will secondary solve this issue also. regards, bogdan ---------------------------------------------------------------------- Comment By: Nobody/Anonymous (nobody) Date: 2006-11-29 11:25 Message: Logged In: NO 2bogdan_iancu: Thanks for providing me this info - it will be helpful for me to solve problem. But I still want to ask you - is there any way to find out what's the encoding is used in database? So why don't you include such kind of testing in this script or just explictly set character set for each table? For example: mysql> CREATE TABLE location ( -> username varchar(64) NOT NULL default '', -> domain varchar(128) NOT NULL default '', -> contact varchar(255) NOT NULL default '', -> received varchar(255) default NULL, -> path varchar(255) default NULL, -> expires datetime NOT NULL default '2020-05-28 21:32:15', -> q float(10,2) NOT NULL default '1.0', -> callid varchar(255) NOT NULL default 'Default-Call-ID', -> cseq int(11) NOT NULL default '42', -> last_modified datetime NOT NULL default "1900-01-01 00:00", -> flags int(11) NOT NULL default '0', -> user_agent varchar(255) NOT NULL default '', -> socket varchar(128) default NULL, -> methods int(11) default NULL, -> PRIMARY KEY(username, domain, contact) -> ) TYPE=MyISAM; ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes mysql> CREATE TABLE location ( -> username varchar(64) NOT NULL default '', -> domain varchar(128) NOT NULL default '', -> contact varchar(255) NOT NULL default '', -> received varchar(255) default NULL, -> path varchar(255) default NULL, -> expires datetime NOT NULL default '2020-05-28 21:32:15', -> q float(10,2) NOT NULL default '1.0', -> callid varchar(255) NOT NULL default 'Default-Call-ID', -> cseq int(11) NOT NULL default '42', -> last_modified datetime NOT NULL default "1900-01-01 00:00", -> flags int(11) NOT NULL default '0', -> user_agent varchar(255) NOT NULL default '', -> socket varchar(128) default NULL, -> methods int(11) default NULL, -> PRIMARY KEY(username, domain, contact) -> ) TYPE=MyISAM DEFAULT CHARACTER SET LATIN1; Query OK, 0 rows affected, 1 warning (0.00 sec) ---------------------------------------------------------------------- Comment By: Bogdan-Andrei Iancu (bogdan_iancu) Date: 2006-11-29 10:19 Message: Logged In: YES user_id=1275325 Originator: NO Hi, actually it is not a bug in openser, but a bug related to mysql. This was already reporded some time ago and thanks to Norman Brandinger &lt;norm(a)goes.com&gt;om>, here is the answer: "Perhaps the problem is related to the fact that UTF-8 uses 3-bytes to save a character. This issue was discussed at: http://bugs.mysql.com/bug.php?id=4541 If the datbase encoding is UTF-8, then this will be important to you." visit also: http://openser.org/pipermail/users/2006-November/007738.html regards, bogdan ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=743020&aid=160541…