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?

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

1 Answer 1

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!

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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