5

I'm taking over an old database, and while doing recovery tests from my dump I found out a table is wrongly backed up, the table definition is truncated.

Looking at the live db I've found a column name with special character in the name. That is the column name is Località.

My current mysql dump produces the following:

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `contabilita_banche` (
  `ID_Banca` int(6) unsigned NOT NULL default '0',
  `Descrizione` varchar(70) default NULL,
  `Località;
/*!40101 SET character_set_client = @saved_cs_client */;

The first problem is that the table doesn't end there, there are other fields after Località, so the structure dump is incomplete. Other than this the dump doesn't throw any error and the data is dumped correctly to the end.

I tried several commands, all without success:

mysqldump -r /tmp/cbnew2.sql --default-character-set=latin1 --no-tablespaces --skip-lock-tables --quick -u root -p -h host Dbname contabilita_banche
mysqldump -r /tmp/cbnew2.sql --default-character-set=utf8 --no-tablespaces --skip-lock-tables --quick -u root -p -h host Dbname contabilita_banche

Server version is 4.1.22-log, client 5.5.38. Is there any way to correctly produce the dump?

  • When you login to MySQL 4.1, please run SHOW CREATE TABLE contabilita_banche\G. Does the complete structure appear from the console ? – RolandoMySQLDBA Feb 3 '15 at 15:56
  • no, it's truncated as well, and finishes this way: `Localit? – Maxxer Feb 3 '15 at 15:57
  • Also, on that same server, Run mysqldump. --version` does it say 4.1.22 ? – RolandoMySQLDBA Feb 3 '15 at 15:57
  • mysqldump Ver 10.9 Distrib 4.1.22, for redhat-linux-gnu (i386) – Maxxer Feb 3 '15 at 15:58
  • Please run SHOW CREATE DATABASE DBname;. What character set does it say ? – RolandoMySQLDBA Feb 3 '15 at 16:00
2

I tried renaming the table via "adminer" (php mysql manager) but even there the table name was not correct (not showing at all, to be honest). I tried renaming but of course I produced a new table column.

In the end I had to recreate the table.

I managed to get most of the schema from the SHOW CREATE TABLE contabilita_banche\G above. Then I took the dump with the data, adjusted the CREATE TABLE command with a utf-8 column name and restored the new dump.

Thank you very much Rolando for your support!

  • SHOW VARIABLES LIKE 'char%'; may give you more clues. – Rick James Feb 6 '15 at 18:22
  • Thanks for the suggestion but sadly as I said I removed the table and I cannot test anymore – Maxxer Feb 8 '15 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.