Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I recently had to move from one hosting provider to another (if you're in the UK don't use Easyspace for your VPS!) as my previous host dumped my server without warning.

Fortunately I had remote backups in place and I've been able to restore almost all of my client sites and databases without a hitch. Except this one.

Since I restored my databases, some of them are exhibiting very unusual behaviour, specifically NULLing out rows of data or certain fields across multiple rows.

Has anyone experienced this before/know what the cause is? It only seems to occur when records are updated.

My backups consisted of a

mysqldump --all-databases

I restored to my new MySQL server using phpMyAdmin initially. Then I tried again, importing directly into MySQL from the command line and the same thing is happening.

share|improve this question
add comment

2 Answers

Were the databases you are importing to, created with a different default character set to your database export/source databases? Differences between iso-8859-1 and UTF-8 will often cause data to be lost as you are reporting.

share|improve this answer
    
The character sets are the same. The data wasn't lost on backup, but rather on update of the data once it's in place! –  Simon Jan 16 '10 at 14:14
add comment
up vote 0 down vote accepted

Turns out the problem is that I'm using mysql_real_escape string. My script is throwing me this:

Warning: [2] mysql_real_escape_string(): "Access denied for user 'www-data'@'localhost' (using password: NO)"

I'm using mysqli. So the function/method should be

mysqli_real_escape_string($link, $str);
// OR
mysqli::escape_string($str);

Seems really stupid, but it was working on my other server, even though there is no default setup for using mysql at all... it just seemed to get on with it. So I'm guessing it was shonky permissions and that my new server is exhibiting the correct behaviour.

Problem solved.

share|improve this answer
1  
using mysqli_real_escape_string implies that you are not using bind parameters, which means it is almost a given that your site is vulnerable to SQL injection attacks. use bind parameters. –  Justin Jan 16 '10 at 20:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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