Mysql – Dump with special characters in table column name

character-setMySQLmysql-4mysqldump

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?

Best Answer

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!

Related Question