Hi and I have a mysql table that gets a few million records inserted per day and it seems to get corrupted very often. The current database structure is myisam but it keeps on getting corrupted. Are there any mysql data structures that won't corrupt even when restarting the mysql service while a record is being inserted? BTW I only need to insert and select records and needs to be fast at selecting from the database too. Please help. Thanks.
cwarn23 387 Occupation: Genius Team Colleague Featured Poster
pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster
I like InnoDB. Use it to contain several booking websites' data. It got very large, although not millions per day. Never had any corrupt data issues.
With databases with these sizes I prefer to switch to postgres, oracle or mssql (depending on the customer of course).
ShawnCplus 456 Code Monkey Team Colleague
If you're looking to avoid row corruption you'll definitely want to look into atomic transactions http://dev.mysql.com/doc/refman/5.5/en/ansi-diff-transactions.html
But the issue still remains if you restart mysql while updates are still running there's really nothing you can do, you killed the thread it can't recover if it doesn't exist :P
The only situation this solution doesn't handle is when someone kills the threads in the middle of an update. In that case, all locks are released but some of the updates may not have been executed.
cwarn23 387 Occupation: Genius Team Colleague Featured Poster
So I'm using InnoDB and so far it is ok and I will see when I have to restart the mysql service. Because hopefully if it is inserting a record while the mysql service is restarting it shouldn't corrupt the table like myisam does. That is still to be tested. But as for now, I will ask appart from obvious performance what is the difference between InnoDB and Myisam? And yes I read the earlier posts but some of those long words are confusing.
logonchristy 0 Light Poster
I think postgres might seems better...
ShawnCplus 456 Code Monkey Team Colleague
So I'm using InnoDB and so far it is ok and I will see when I have to restart the mysql service. Because hopefully if it is inserting a record while the mysql service is restarting it shouldn't corrupt the table like myisam does. That is still to be tested. But as for now, I will ask appart from obvious performance what is the difference between InnoDB and Myisam? And yes I read the earlier posts but some of those long words are confusing.
Foreign key constraints, atomic transactions, row-level locking. All-around win.
cwarn23 387 Occupation: Genius Team Colleague Featured Poster
I have been using InnoDB then since suggested in this topic and restarted the mysql service while a record was being inserted then there was no corruption. So InnoDB is the choice for when corruption is a problem.
Thanks.
*Solved*
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.