MySQL Bugs: #76879: Carriage Return Issue

Bug #76879	Carriage Return Issue
Submitted:	29 Apr 2015 8:44	Modified:	4 May 2015 8:32
Reporter:	Infosys DBS Team	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.95	OS:	Linux (x86-64)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
In one of the mysql database, post db restore was performed, we could observe that for couple of tables, there were Carriage Returns within fields that shouldn't have been there and some binary fields were corrupted.

MYSQL db version is 5.0.95

Can a db restore operation cause this? 

Does this happen when specific data types are involved?

Are there any known bugs related to this w.r.t. db version we are using?

How to repeat:
Perform a db restore operation again.

Hi,
Thanks for the bug report. I need some more details from you
 - How did you perform a backup?
 - How did you perform restore?
 - Tables in question - what storage engine they use?
 - Were both server you backed from and restored to same version?

all best
Bogdan Kecman

Hello Bodgan,

Below is the information:

- How did you perform a backup?
We use below command to take the backup:
mysqldump -u root -p --all-databases > /opt/data/dump/full_dump_31072014.sql

- How did you perform restore?
mysql -u root -p < /tmp/full_dump_31072014.sql

- Tables in question - what storage engine they use?
Engine used is InnoDB.

- Were both server you backed from and restored to same version?
Yes

Thanks for looking into this and let us know if any other details required from our end.

Regards,
Shakti

Hi,
Nothing like this I could reproduce in any of the mysql versions including 5.0.95.

What type are the columns you found extra CR's in, varchar or text or? What type are the columns you found binary corruption? Blob's or?

The common problem (not a bug) with dump/restore is wrt character set's but not even that can introduce new CR's into field values. 

Did you maybe move that dump via FTP between servers? FTP is known to affect content of the file especially transfering between windows and unix systems.

all best
Bogdan Kecman

Hello Bodgan,

We found CRs in columns defined as varchar(255).

Backup, restore was done on the same server so no files were moved using ftp between servers.

One thing which I feel was incorrect in the last update was we restored only a single table from the backup taken of the complete database. So the command we used to backup was as we had shared but then we extracted a single table from the dump and inserted it in the db. Below were the steps we did:

# grep -n 'Table structure' db_backup_file_name

# cat db_backup_file_name | sed -n -e '/Table structure for table .tab1./,/Table structure for table .tab2./p' > /tmp/tab1_export.sql

# mysql -u root -p
mysql> connect db_name
mysql> source /tmp/tab1_export.sql;

Regards,
Shakti

Hi,

if you load the original dump, do you get extra CR's then?

When you open the SQL file you are sourcing, do you see those extra CR's in the SQL file? If you open original SQL, are the CR's there?

all best
Bogdan Kecman