patternsqlMinor
Dump with special characters in table column name
Viewed 0 times
dumpcolumnwithspecialnamecharacterstable
Problem
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:
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:
Server version is
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_bancheServer version is
4.1.22-log, client 5.5.38. Is there any way to correctly produce the dump?Solution
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
Then I took the dump with the data, adjusted the
Thank you very much Rolando for your support!
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!
Context
StackExchange Database Administrators Q#90939, answer score: 2
Revisions (0)
No revisions yet.