Lubomir Host 'rajo' wrote:
Description:
Migration problem from 4.0.22 to 5.0.x. I can't create following table on 5.0.x
version of mysql. Problem does't apper on 4.0.x version:
CREATE TABLE `PHONESlog_uniq` (
`user_agent` varchar(80) default NULL,
`http_x_wap_profile` varchar(255) default NULL,
`pid` smallint(5) unsigned NOT NULL default '0',
UNIQUE KEY `uniq_phone_key` (`user_agent`,`http_x_wap_profile`,`pid`)
) TYPE=MyISAM;
How-To-Repeat:
server 1:
mysql> SELECT VERSION();
+------------+
| VERSION() |
+------------+
| 4.0.22-log |
+------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE `PHONESlog_uniq` ( `user_agent` varchar(80) default NULL,
`http_x_wap_profile` varchar(255) default NULL, `pid` smallint(5) unsigned NOT
NULL default '0', UNIQUE KEY `uniq_phone_key`
(`user_agent`,`http_x_wap_profile`,`pid`) ) TYPE=MyISAM;
Query OK, 0 rows affected (0.06 sec)
server 2:
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.0.18 |
+-----------+
1 row in set (0.00 sec)
mysql> CREATE TABLE `PHONESlog_uniq` ( `user_agent` varchar(80) default NULL,
`http_x_wap_profile` varchar(255) default NULL, `pid` smallint(5) unsigned NOT
NULL default '0', UNIQUE KEY `uniq_phone_key`
(`user_agent`,`http_x_wap_profile`,`pid`) ) TYPE=MyISAM;
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
Fix:
Submitter-Id: <submitter ID>
Originator: Lubomir Host
Organization:
Lubomir Host 'rajo' <rajo AT platon.sk> ICQ #: 257322664 ,''`.
Platon Group http://platon.sk/ : :' :
Homepage: http://rajo.platon.sk/ `. `'
http://www.gnu.org/philosophy/no-word-attachments.html `-
MySQL support: extended email support
Synopsis: Migration problem from 4.0.22 to 5.0.x. I can't create
following table on 5.0.x version of mysql. Problem does't apper on 4.0.x version
Severity: serious
Priority: high
Category: mysql
Class: sw-bug
Release: mysql-5.0.22-Debian_3 (Debian Etch distribution)
C compiler: gcc (GCC) 4.1.2 20060613 (prerelease) (Debian 4.1.1-4)
C++ compiler: g++ (GCC) 4.1.2 20060613 (prerelease) (Debian 4.1.1-4)
Environment:
Debian GNU/Linux or FreeBSD, all versions of MySQL 5.0.x
System: Linux Idea 2.6.15-1-686 #2 Mon Mar 6 15:27:08 UTC 2006 i686 GNU/Linux
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Using built-in specs.
Target: i486-linux-gnu
Configured with: ../src/configure -v
--enable-languages=c,c++,java,f95,objc,ada,treelang --prefix=/usr
--enable-shared --with-system-zlib --libexecdir=/usr/lib
--without-included-gettext --enable-threads=posix --enable-nls
--program-suffix=-4.0 --enable-__cxa_atexit --enable-clocale=gnu
--enable-libstdcxx-debug --enable-java-awt=gtk-default --enable-gtk-cairo
--with-java-home=/usr/lib/jvm/java-1.4.2-gcj-4.0-1.4.2.0/jre --enable-mpfr
--disable-werror --with-tune=i686 --enable-checking=release i486-linux-gnu
Thread model: posix
gcc version 4.0.4 20060507 (prerelease) (Debian 4.0.3-3)
Compilation info: CC='gcc' CFLAGS='-DBIG_JOINS=1 -O2' CXX='g++'
CXXFLAGS='-DBIG_JOINS=1 -felide-constructors -fno-rtti -O2' LDFLAGS=''
ASFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Jun 28 23:32 /lib/libc.so.6 -> libc-2.3.6.so
-rwxr-xr-x 1 root root 1177116 May 31 08:59 /lib/libc-2.3.6.so
-rw-r--r-- 1 root root 2628734 Jun 8 09:25 /usr/lib/libc.a
-rwxr-xr-x 1 root root 204 Jun 8 09:07 /usr/lib/libc.so
lrwxrwxrwx 1 root root 19 Jun 28 22:04 /usr/lib/libc-client.a ->
/usr/lib/c-client.a
lrwxrwxrwx 1 root root 28 Jun 28 22:05 /usr/lib/libc-client.so.2002edebian ->
libc-client.so.2002edebian.1
-rw-r--r-- 1 root root 772872 Jan 16 21:34 /usr/lib/libc-client.so.2002edebian.1
Configure command: ./configure '--build=i486-linux-gnu' '--host=i486-linux-gnu'
'--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin'
'--datadir=/usr/share' '--sysconfdir=/etc/mysql'
'--localstatedir=/var/lib/mysql' '--includedir=/usr/include'
'--infodir=/usr/share/info' '--mandir=/usr/share/man'
'--with-server-suffix=-Debian_3' '--with-comment=Debian Etch distribution'
'--enable-shared' '--enable-static' '--enable-thread-safe-client'
'--enable-assembler' '--enable-local-infile' '--with-big-tables' '--with-raid'
'--with-unix-socket-path=/var/run/mysqld/mysqld.sock'
'--with-mysqld-user=mysql' '--with-libwrap' '--with-vio' '--without-openssl'
'--without-docs' '--without-bench' '--without-readline'
'--with-extra-charsets=all' '--with-innodb' '--with-isam'
'--with-archive-storage-engine' '--with-csv-storage-engine'
'--with-federated-storage-engine' '--without-embedded-server'
'--with-ndbcluster' '--with-ndb-shm' '--without-ndb-sci' '--without-ndb-test'
'--with-embedded
-server' '--with-embedded-privilege-control' '--with-ndb-docs' 'CC=gcc'
'CFLAGS=-DBIG_JOINS=1 -O2' 'CXXFLAGS=-DBIG_JOINS=1 -felide-constructors
-fno-rtti -O2' 'CXX=g++' 'build_alias=i486-linux-gnu'
'host_alias=i486-linux-gnu'
Are you sure your systems are identical?
Please show us result from both systems of:
SHOW VARIABLES LIKE '%char%'
...seems like this could be possible if old system uses latin1 or
cp1251/1252 or any other single byte per char code page and new one
probably is UTF-8 then just (80 + 255) * 3 = 1005 bytes + 1 smallint (2
more bytes) is just above the limit of "max key length is 1000 bytes"
Could you just cut 3 chars from either `user_agent` or
`http_x_wap_profile` and give it a quick try to see if same error would
show again...
HTH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
