Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I have gigabytes of tabular data stored in a SQL Server database. I've been trying to migrate it to MySQL using the Migration Toolkit from Oracle. I didn't have any mapping errors and fixed all the syntax mismatches, so now the rows are being transferred. But when I go to PHPMyAdmin and try to browse, it keeps saying MySQL returned an empty result set (i.e. zero rows). ( Query took 17.0116 sec ) although it says there are 44,546,089 rows in that table, and I can see the hard drive space filling up too. I just can't access any of the data.

I researched a bit and increased the buffer size from 16M to 160M in my.conf. But I can't fix this problem.

Some things I noticed (red marked in the status page):

Innodb_buffer_pool_pages_dirty: 376

Innodb_buffer_pool_reads = 92 k

Handler_read_rnd = 41

Handler_read_rnd_next = 59 k

Any advice? This database will be read-only. Also the server specs: dedicated, 8GB RAM, Debian 6, 500GB HDD, ~2.8GHz Quadcore Intel

share|improve this question
Did you mess with log files after migrating the data? Like truncating the logs? – srini.venigalla Jul 3 '12 at 19:23
What happens if you try to dump the tables? Is it exporting the data? If so, dump and reload, it may help. – srini.venigalla Jul 3 '12 at 19:25
Have you tried the query with the MySQL command line interface? Just to eliminate the possibility that the problem is in phpMyAdmin's remote interface. – Jon of All Trades Jul 3 '12 at 21:56
1  
What serialisation level? Could it be uncommitted in the loading session, therefore invisible to phpmyadmin? – Phil Jul 4 '12 at 4:34
What is the actual statement that you are running? – a_horse_with_no_name Aug 2 '12 at 11:12

1 Answer

up vote 1 down vote

As you have a dedicated server, there is no reason not to use native MySQL command to issue queries directly. Avoiding phpMyAdmin means avoiding one more layer between you and database while trying to figure out what went wrong. My first thought is - are you actually trying "select * from table" on a 45 million row table? That could cause all kinds of problems.

1) Try "select count(*) from table" first. See if you get 0 or 45M.

2) See if partition with your data is large enough. Yes, this kind of omission happens.

3) Try using MyISAM instead of InnoDB to reveal possible server misconfiguration issue.

Perhaps it makes sense to use our battle-proven commercial database migration tool Full Convert Enterprise for your data conversion? It runs on Windows only, though. Quickest path

share|improve this answer
2  
Your other answer was inappropriate, as it did not give an answer, merely "please try our product for $$$". Please do read our FAQ, preferably this section: faq, where we cover this in slightly more detail. But with this answer you see that you have given great detail where the user could use your answer even without your product. This is the sort of interaction we value. Cheers and we hope to see you contribute with more great answers, and the occasional link to remind people you do this professionally too. – jcolebrand♦ Aug 2 '12 at 14:41

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.