0

I am transferring a single table from MySQL 5.7 to MySQL 8.

First, I used mysqldump db_name table_name > table_name.sql in my current server(MYSQL 5.7); taken from here

Then, in my VM with MySQL 8, I used mysql -u username -p db_name < /path/to/table_name.sql.

The columns with the 'blob' data type are not correct, the other columns are okay. the 'blob' columns went from "International Master (1973); Grandmaster (1975); FIDE Senior Trainer (2004).Alexander Genrikhovich Beliavsky was..." to "0x3C703E496E7465726E6174696F6E616C204D6173746572202831393733293B204772616E646D617374657220...".

Both databases have character-set = latin1 and collation = latin1_swedish_ci.

How can I properly transfer the 'blob' columns from MySQL 5.7 to MySQL 8?

Output when running SHOW CREATE TABLE table_name:

OLD:

CREATE TABLE `Player` (
  `pid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(34) DEFAULT NULL,
  `longname` varchar(64) DEFAULT NULL,
  `rating` int(10) unsigned NOT NULL DEFAULT '0',
  `born` varchar(10) DEFAULT NULL,
  `died` varchar(10) DEFAULT NULL,
  `country` varchar(10) DEFAULT NULL,
  `nationality` varchar(10) DEFAULT NULL,
  `gender` enum('M','F') DEFAULT NULL,
  `bio` blob,
  `fiderating` int(10) unsigned NOT NULL DEFAULT '0',
  `rawbio` blob,
  `fidenumber` int(10) unsigned DEFAULT NULL,
  `fideblitz` int(10) unsigned DEFAULT '0',
  `fiderapid` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`pid`),
  KEY `name` (`name`),
  KEY `rating` (`rating`),
  KEY `country` (`country`),
  KEY `nationality` (`nationality`),
  KEY `gender` (`gender`),
  KEY `fidenumber` (`fidenumber`),
  FULLTEXT KEY `longname` (`longname`)
) ENGINE=MyISAM AUTO_INCREMENT=168045 DEFAULT CHARSET=latin1

NEW:

| Player | CREATE TABLE `Player` (
  `pid` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(34) DEFAULT NULL,
  `longname` varchar(64) DEFAULT NULL,
  `rating` int unsigned NOT NULL DEFAULT '0',
  `born` varchar(10) DEFAULT NULL,
  `died` varchar(10) DEFAULT NULL,
  `country` varchar(10) DEFAULT NULL,
  `nationality` varchar(10) DEFAULT NULL,
  `gender` enum('M','F') DEFAULT NULL,
  `bio` blob,
  `fiderating` int unsigned NOT NULL DEFAULT '0',
  `rawbio` blob,
  `fidenumber` int unsigned DEFAULT NULL,
  `fideblitz` int unsigned DEFAULT '0',
  `fiderapid` int unsigned DEFAULT '0',
  PRIMARY KEY (`pid`),
  KEY `name` (`name`),
  KEY `rating` (`rating`),
  KEY `country` (`country`),
  KEY `nationality` (`nationality`),
  KEY `gender` (`gender`),
  KEY `fidenumber` (`fidenumber`),
  FULLTEXT KEY `longname` (`longname`)
) ENGINE=MyISAM AUTO_INCREMENT=168045 DEFAULT CHARSET=latin1 |
Stephen McDonald
  • 106
  • 8
  • 1
    *Both databases have* What about a table and a column? Does server and connection charsets/collations matches too? – Akina Apr 01 '21 at 18:03
  • 1
    Does `SELECT HEX(blob_column) FROM table_name` does not show the same output on both servers? – Akina Apr 01 '21 at 18:11
  • The Collations for the table is the same(latin1_swedish_ci) but I noticed the 'blob' columns do not have a collations, I am updating that now. Using the HEX function still doesn't show the correct data. Thanks! I'll keep you posted – Stephen McDonald Apr 01 '21 at 18:17
  • It seems I am unable to add 'collation' to a 'blob' column – Stephen McDonald Apr 01 '21 at 18:41
  • 1
    blob is a binary type. text is the equivalent character type that allows collation – ysth Apr 01 '21 at 18:47
  • 1
    could you show output of `show create table table_name` for the new and old? – ysth Apr 01 '21 at 18:49
  • follow the migration tutorial of the mysql side – nbk Apr 01 '21 at 19:13
  • `select convert(blob_column using latin1) from table;` displays the correct data; a step in the right direction, thanks. – Stephen McDonald Apr 01 '21 at 19:16
  • Please test data copying via SELECT INTO OUTFILE - LOAD DATA INFILE (and HEX/UNHEX for BLOB column) - does the problem remains? – Akina Apr 01 '21 at 19:58
  • try using the `--hex-blob` option when you dump the data from the old db. – Jerry Apr 02 '21 at 00:33

0 Answers0