0

I'm in the process of migrating a PHP application from a Windows host (IIS10 & mysql) to Linux CentOS 7 (nginx & percona). The copying of the actual php files, etc, is not a problem, the problem I am having is when transferring the databases over. I use phpmyadmin on both machines and went about the simple process of exporting all the databases from the Windows machine and copying over the file(s) to the Linux machine. After which I opened phpmyadmin on CentOS 7 and imported the databases one by one. They imported fine, or so I thought. Looking at the sizes of the databases and the tables within them I found that one table that should contain 77036 rows of data only imported around 73000 rows. I say 'around' because I can't remember the figure as something else strange then happened. I dropped all tables and then went about importing the tables using the Linux Terminal:

sudo mysql -u username -p tablename < filename.sql

They file seemed to import fine, but now I have all the data AND a lot of additional blank rows. I should have 77036 rows, but now have 78147 rows of which 1,111 rows are blank and added to the bottom of the table. Now, I don't understand what's going on with the imports and wondered if anyone had a clue as to what may be causing the issue. Also, 1,111 seems to be an incredibly coincidental number (if you know what I mean) - does it mean something?

The other tables seem to import fine apart from this one and 1 or 2 others. Is it possible that the content could be effecting this somehow?

Linux CentOS 7 and the DB's are on a XFS partition and are coming from a Windows 10 machine formatted to NTFS.

I did try also exporting just the troublesome table from the Windows machine and importing just that, but it made no difference. Also, I did open the sql file with a text editor to make sure all the data was in there and scrolling to the bottom showed me the last row was present.

Can anyone help please?

executable
  • 3,365
  • 6
  • 24
  • 52
Linko
  • 45
  • 7
  • 2
    How are you getting the total number of rows imported for a single table? Are you performing a `COUNT(*)` on the table or are you relying on the numbers displayed in phpMyAdmin? – jaseeey Nov 14 '18 at 12:44
  • I'm relying on the count provided in phpmyadmin. The pagination pulldown menu also shows additional 'pages' which contain nothing - the extra empty rows. – Linko Nov 14 '18 at 13:54
  • 1
    Were there no errors or warnings when importing the data? (Also, the count on phpmyadmin is likely an estimate and you have to do a `select count(*)` to find the exact number of rows) – Joni Nov 14 '18 at 14:33
  • No errors, all green. I'll try a count, although I know there were rows missing as they didn't appear at all in the table after import at one point. – Linko Nov 14 '18 at 14:35
  • Running SELECT COUNT(*) FROM table resulted in the tables matching. Thank you for your help. Why on earth would phpmyadmin 'estimate' the number of records rather than running this simple query and outputting the correct result? Seems odd to me! – Linko Nov 15 '18 at 09:12
  • Based on this result, I'm going to assume that you are using InnoDB for the table engines? There is a good answer [here](https://stackoverflow.com/questions/11926259/why-is-the-estimated-rows-count-very-different-in-phpmyadmin-results) which explains the basics. – jaseeey Nov 19 '18 at 13:03

0 Answers0