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

25 Feb 2012

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
...
  db_charset_test()
 {
       CURRCHARSET=`echo "show variables like '%character_set_server%'"
| $CMD "-p$PW" | awk '{print $2}' | sed -e 1d`
...
        ALLCHARSETS=`echo "show character set"
| $CMD "-p$PW" | awk '{print $1}' | sed -e 1d | grep -ivE
utf8\|ucs2`
...
        while [ `echo "$ALLCHARSETS" | grep
-icw $CURRCHARSET`  = "0" ]
       do
               echo "Your current default mysql characters set cannot be used
to create DB. Please choice another one from the following list:"
...
                echo "$ALLCHARSETS"
               echo -n "Enter character set name: "
               read CURRCHARSET
               if [ `echo $CURRCHARSET | grep -cE "\w+"` = "0" ];
then
                       echo "can't continue: user break"
                       exit 1
               fi
       done
       export CHARSET=$CURRCHARSET
 }

 244a263,264
...
  db_charset_test

 248c268
< create database $1;
---
...
  create database $1 character set $CHARSET;
 ---------------------- << 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…