0

Currently our Database all tables are using MyIsam as a storage engine. We tried changing storage engine of our mysql tables from MyISAM to InnoDB from PHPMyAdmin operations tab. However during the change table records are dropped. So what can be the possible factor causing this problem.

This is what we tried:

We converted test_user table which was having storage engine MyIsam and was having total records 11k. When we converted it into InnoDB the records droped to 9k. This happened for many tables. Not sure what we are doing wrong.

Improve this question
2
  • instead of converting copy the data to a new table, and see what innodb doesn't like.
    – nbk
    Commented Apr 30, 2020 at 15:09
  • @nbk let me try it this way as well
    – SohaIb AhmEd
    Commented Apr 30, 2020 at 15:33

1 Answer 1

Reset to default
0

Are you are using SHOW TABLE STATUS to determine that there is a loss? Don't trust it. The "Rows" is an estimate in the case of InnoDB. Use SELECT COUNT(*) FROM tbl to get the exact row count.

More tips on the conversion: http://mysql.rjweb.org/doc.php/myisam2innodb

Improve this answer
3
  • Yes I know that it shows an approximate incase of InnoDB but SELECT COUNT(*) FROM tbl also returned the same records as the phpmyadmin showed.
    – SohaIb AhmEd
    Commented May 5, 2020 at 11:45
  • @SohaIbAhmEd - OK. Can you tell if the missing rows are the first few rows, or the last few, or some other pattern?
    – Rick James
    Commented May 5, 2020 at 18:22
  • Sorry but couldn't tell exactly from where the rows got missing.
    – SohaIb AhmEd
    Commented May 5, 2020 at 19:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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