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

SourceForge.net noreply at sourceforge.net
Tue Dec 5 19:52:50 CET 2006


Bugs item #1605410, was opened at 2006-11-29 19:56
Message generated for change (Comment added) made by bogdan_iancu
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=743020&aid=1605410&group_id=139143

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 (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: Bogdan (bogdan_iancu)
Date: 2006-12-05 20:52

Message:
Logged In: YES 
user_id=1275325
Originator: NO

Patch from Alex applied on CVS.
Thanks Alex!

regards,
bogdan

----------------------------------------------------------------------

Comment By: Bogdan (bogdan_iancu)
Date: 2006-12-05 13: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 20: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 <khalukhin at gmail.com>
---------------------- << 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 (bogdan_iancu)
Date: 2006-12-01 13: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 21: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 (bogdan_iancu)
Date: 2006-11-29 20: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
<norm at goes.com>, 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=1605410&group_id=139143



More information about the Devel mailing list