| Bug #15306 | Can't load a table from a 4.0.18 mysql dump : ERROR 1103 - Incorrect table name | ||
|---|---|---|---|
| Submitted: | 29 Nov 2005 9:37 | Modified: | 7 Dec 2005 8:55 |
| Reporter: | Guilmain Nicolas | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.17-BK, 5.0.16-standard | OS: | Linux (Linux) |
| Assigned to: | CPU Architecture: | Any | |
[29 Nov 2005 9:37]
Guilmain Nicolas
[29 Nov 2005 14:53]
Valeriy Kravchuk
Thank you for a problem report. Please, send the results of SHOW VARIABLES LIKE 'collat%' from both 4.0.18 where you made dump and from 5.0.16 you are trying to use for restore.
[29 Nov 2005 15:05]
Guilmain Nicolas
On 4.0.18 : mysql> SHOW VARIABLES LIKE 'collat%'; Empty set (0.00 sec) On 5.0.16 : mysql> SHOW VARIABLES LIKE 'collat%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+ 3 rows in set (0.00 sec)
[1 Dec 2005 14:23]
Valeriy Kravchuk
Can you, please, try to set on 5.0.16 those variables mentioned in the last comment to latin1_general_ci, for example, and create the table once more? Please, inform about the results.
[1 Dec 2005 14:51]
Guilmain Nicolas
I can change collation_database and collation_server but I can't change the collation_connection parameter by doing this on the command-line : /usr/local/mysql/bin/mysql -u root -p --socket=/db/db_test/mysql.sock --default-character-set=latin1_general_ci It says : /usr/local/mysql/bin/mysql: Character set 'latin1_general_ci' is not a compiled character set and is not specified in the '/usr/local/mysql/share/mysql/charsets/Index.xml' file It's not true. I've checked the Index.xml with grep -i latin1_general Index.xml and it finds it : <collation name="latin1_general_ci" id="48"> <collation name="latin1_general_cs" id="49"> What's wrong ?
[1 Dec 2005 15:22]
Guilmain Nicolas
mysql> show collation; +----------------------+----------+-----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +----------------------+----------+-----+---------+----------+---------+ [...] | latin1_german1_ci | latin1 | 5 | | | 0 | | latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 | | latin1_danish_ci | latin1 | 15 | | | 0 | | latin1_german2_ci | latin1 | 31 | | Yes | 2 | | latin1_bin | latin1 | 47 | | Yes | 1 | | latin1_general_ci | latin1 | 48 | | | 0 | | latin1_general_cs | latin1 | 49 | | | 0 | | latin1_spanish_ci | latin1 | 94 | | | 0 | [...] latin1_general_ci is not compiled indeed... Do I have to compile MySQL myself (this is what I usually do at home but not at work) ?
[1 Dec 2005 17:01]
Guilmain Nicolas
With a 5.0.16-standard server compiled at home and started with the good options : collation_connection latin1_general_ci collation_database latin1_general_ci collation_server latin1_general_ci The CREATE TABLE statement fails too.
[4 Dec 2005 15:47]
Valeriy Kravchuk
Sorry, but I was not able to repeat the problem you described on 5.0.17-BK build of December 1st. Look:
mysql> CREATE TABLE `taux de russite par tablissement sexe spcialit Rapport
`> BTS` (
-> `code de l'examen` varchar(255) default NULL,
-> `anne d'observation` varchar(255) default NULL,
-> `libell court de l'examen` varchar(255) default NULL,
-> `code de la spcialit` varchar(255) default NULL,
-> `libell de la spcialit` varchar(255) default NULL,
-> `libell de l'option de spcialit` varchar(255) default NULL,
-> `code du diplome` varchar(255) default NULL,
-> `libell long du diplome` varchar(255) default NULL,
-> `libell de la catgorie de spcialit` varchar(255) default NULL,
-> `sexe` varchar(255) default NULL,
-> `numro de l'tablissement` varchar(255) default NULL,
-> `patronyme` varchar(255) default NULL,
-> `dnomination` varchar(255) default NULL,
-> `prsent` double default NULL,
-> `reu` double default NULL,
-> `taux` double default NULL,
-> `code du dpartement de l'tablissement` varchar(255) default NULL,
-> `libell long du dpartement de l'tablissement` varchar(255) default
-> NULL,
-> `code de la commune de l'tablissement` varchar(255) default NULL,
-> `libell long de la commune de l'tablissement` varchar(255) default NULL,
-> `code de la zone d'emploi de l'tablissement` varchar(255) default NULL,
-> `libell long de la zone d'emploi de l'tablissement` varchar(255) default
-> NULL,
-> `code du bassin de formation de l'tablissement` varchar(255) default NULL,
-> `libell long du bassin de formation de l'tablissement` varchar(255)
-> default NULL,
-> `code de la ZEP de l'tablissement` varchar(255) default NULL,
-> `libell de la ZEP de l'tablissement` varchar(255) default NULL
-> ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0,01 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------------------------+
| Level | Code | Message | +---------+------+--------------------------------------------------------------------------+
| Warning | 1287 | 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead |
+---------+------+--------------------------------------------------------------------------+
1 row in set (0,00 sec)
mysql> show variables like 'collat%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_general_ci |
| collation_server | latin1_general_ci |
+----------------------+-------------------+
3 rows in set (0,00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.17 |
+-----------+
1 row in set (0,08 sec)
So, this table should be created without any problems with proper collation set.
[5 Dec 2005 8:17]
Guilmain Nicolas
Is it a kind of html bug or the french accents really disappeared from the CREATE TABLE statement you used ?
[5 Dec 2005 10:24]
Valeriy Kravchuk
I've just copied and pasted yout table definition to the terminal of my Fedora box... I believe accents where in place. But let us check once more. Please, upload your problematic CREATE TABLE as a separate file using the File tab.
[5 Dec 2005 10:45]
Guilmain Nicolas
Dump created by the following command : mysqldump --databases dep --user=root -p --no-data --socket=/db/mysql.sock --quote-names --default-character-set=latin1 > dep_dump.sql The CREATE STATEMENT 'accused' is at line 2719. Note that some very similar statements just above this one are accepted.
[6 Dec 2005 12:48]
Valeriy Kravchuk
Sorry about French accents - it was my fault... So, that is how I repeated the problem on 5.0.17-BK (with both utf8_general_ci and latin1_general_ci - same results): [openxs@Fedora 5.0]$ bin/mysqld_safe --defaults-file=/home/openxs/dbs/5.0/my.cnf --default-character-set=utf8 & [1] 25970 [openxs@Fedora 5.0]$ Starting mysqld daemon with databases from /home/openxs/dbs/5.0/var [openxs@Fedora 5.0]$ bin/mysql -uroot test < 15306.sql Variable_name Value collation_connection latin1_general_ci collation_database utf8_general_ci collation_server utf8_general_ci ERROR 1103 (42000) at line 3: Incorrect table name 'taux de réussite par établissement sexe spécialité Rapport BTS' [openxs@Fedora 5.0]$ echo $LANG fr_FR.UTF-8 [openxs@Fedora 5.0]$ cat 15306.sql set collation_connection=utf8_general_ci; show variables like 'collat%'; CREATE TABLE `taux de réussite par établissement sexe spécialité Rapport BTS` ( `code de l'examen` varchar(255) default NULL, `année d'observation` varchar(255) default NULL, `libellé court de l'examen` varchar(255) default NULL, `code de la spécialité` varchar(255) default NULL, `libellé de la spécialité` varchar(255) default NULL, `libellé de l'option de spécialité` varchar(255) default NULL, `code du diplome` varchar(255) default NULL, `libellé long du diplome` varchar(255) default NULL, `libellé de la catégorie de spécialité` varchar(255) default NULL, `sexe` varchar(255) default NULL, `numéro de l'établissement` varchar(255) default NULL, `patronyme` varchar(255) default NULL, `dénomination` varchar(255) default NULL, `présent` double default NULL, `reçu` double default NULL, `taux` double default NULL, `code du département de l'établissement` varchar(255) default NULL, `libellé long du département de l'établissement` varchar(255) default NULL, `code de la commune de l'établissement` varchar(255) default NULL, `libellé long de la commune de l'établissement` varchar(255) default NULL, `code de la zone d'emploi de l'établissement` varchar(255) default NULL, `libellé long de la zone d'emploi de l'établissement` varchar(255) default NULL, `code du bassin de formation de l'établissement` varchar(255) default NULL, `libellé long du bassin de formation de l'établissement` varchar(255) default NULL, `code de la ZEP de l'établissement` varchar(255) default NULL, `libellé de la ZEP de l'établissement` varchar(255) default NULL ) TYPE=MyISAM; [openxs@Fedora 5.0]$ cat my.cnf [mysqld] default-collation = utf8_general_ci So, looks like this table name is "not good" for MySQL 5.0.x. Note (see previous comment), that without accents (because of the different LANG value at the OS level) it is OK.
[7 Dec 2005 8:15]
Guilmain Nicolas
Sorry, but it seems that the problem is not linked to a character set issue but to the length of the table name. See : http://dev.mysql.com/doc/refman/5.0/en/legal-names.html But the strange point is that such table name was made possible in 4.0.18... Thank you.
[7 Dec 2005 8:55]
Valeriy Kravchuk
Thank you for the additional check. I thought it is limited by 128 characters (common limit in modern RDBMS)... So, it is not a bug in 5.0.x, but a documented restriction.
