MySQL Database integrity checking

VB 2008 Script to check MySQL database integrity

Page 1 of 1

6 Replies - 13394 Views - Last Post: 31 August 2007 - 07:38 AM

#1 mhokoretaurai  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 28-August 07

MySQL Database integrity checking

Post icon  Posted 28 August 2007 - 06:57 AM

Could you please send me a vb code to check MySQL Database integrity every time I want to run my modules.

Hope to hear from you soonest


Taurai Mhokore
+27 7981 96290
Is This A Good Question/Topic? 0
  • +

Replies To: MySQL Database integrity checking

#2 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: MySQL Database integrity checking

Posted 28 August 2007 - 09:54 AM

Can you specify exactly what you wish to validate on your integrity checks?
Was This Post Helpful? 0
  • +
  • -

#3 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: MySQL Database integrity checking

Posted 30 August 2007 - 08:12 AM

IMO the best integrity check for mysql is choosing another database brand, like postgresql.
(hey dont throw rocks at me, its an _opinion_)
Was This Post Helpful? 0
  • +
  • -

#4 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: MySQL Database integrity checking

Posted 30 August 2007 - 11:29 AM

I'm not one to throw rocks, but I would ask for the reasoning behind the formation of the opinion. I don't have a vested interest in mysql, but would be interested to know what leads to the recommendation.

Also, I'm sure you're aware, but switching db software does not actually qualify as an integrity check. :)
Was This Post Helpful? 0
  • +
  • -

#5 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: MySQL Database integrity checking

Posted 30 August 2007 - 11:50 AM

View PostTrogdor, on 30 Aug, 2007 - 08:12 AM, said:

IMO the best integrity check for mysql is choosing another database brand, like postgresql.
(hey dont throw rocks at me, its an _opinion_)


Or a real database like MSSQL or Oracle :)
Was This Post Helpful? 0
  • +
  • -

#6 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: MySQL Database integrity checking

Posted 31 August 2007 - 07:04 AM

ok let me rephrase that.

IMO the best integrity check for mysql is choosing any other database brand that supports the essentials to enforce integrity.
For example, MySql is one of the few databases that do not (truly) support transactions on a low level.

Oracle and mssql do, and so does postgresql.

To get an idea of this, try to follow this quick scetch of how an update is/can be done.
(a primary key as database users use it is not the actual record identifier. the record can be identified with an object identifier (OID) uniquely, even in the rare moments that there are more then one of the same objects around)

1: command is parsed. it is an update.
2: the original record is read.
3: a reccord with the same ID and the new updated values is inserted
4: the original record is deleted (or marked as invalid, the same)

now imagine someone trips over the powercable when step 3 is finished but step 4 has not started.
The database will be in an invalid state, as there will be 2 reccords with the same (supposedly unique!) ID.

Trust me, i have seen this happening. Well not the tripping over a powercable, but the result of a similar failure in the database.
This was on a not so very old version of mysql.

A transactional database has a mechanism that prevents things like this from ever happening, even when the power fails in the middle of an operation.
The transaction might fail (when the server restarts) and be rolled back, thus you lose the insert, but at least the database will be consistent.

By the way, fair is fair:
The same byzzare results will emerge when you suddenly stop (kill) a transactional database and then truncate its transaction log. bad thing. dont do it.
I have also wittnessed this. It was (very sad) when that database suddenly completely ran out of discspace, and the admin didnt know any other sollution then to delete 'that
big logfile'. Ouch!


I have seen so many problems arrise due to lazy choice of architectures, that i can not be acidic enough when someone suggests MySql 'becouse everybody uses it'


- meh, this disimproved my mood.
Was This Post Helpful? 0
  • +
  • -

#7 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: MySQL Database integrity checking

Posted 31 August 2007 - 07:38 AM

View PostTrogdor, on 31 Aug, 2007 - 10:04 AM, said:

To get an idea of this, try to follow this quick scetch of how an update is/can be done.
(a primary key as database users use it is not the actual record identifier. the record can be identified with an object identifier (OID) uniquely, even in the rare moments that there are more then one of the same objects around)

Tried and succeeded. I'm a certified DBA on many architectures, so I'm familiar with the process.

View PostTrogdor, on 31 Aug, 2007 - 10:04 AM, said:

1: command is parsed. it is an update.
2: the original record is read.
3: a reccord with the same ID and the new updated values is inserted
4: the original record is deleted (or marked as invalid, the same)

now imagine someone trips over the powercable when step 3 is finished but step 4 has not started.
The database will be in an invalid state, as there will be 2 reccords with the same (supposedly unique!) ID.

Excellent point

View PostTrogdor, on 31 Aug, 2007 - 10:04 AM, said:

Trust me, i have seen this happening. Well not the tripping over a powercable, but the result of a similar failure in the database.
This was on a not so very old version of mysql.

MySQL is in the procees (or may have completed, not sure) of moving toward the more robust model, but you're right, it does not have the full transactional control.

View PostTrogdor, on 31 Aug, 2007 - 10:04 AM, said:

A transactional database has a mechanism that prevents things like this from ever happening, even when the power fails in the middle of an operation.
The transaction might fail (when the server restarts) and be rolled back, thus you lose the insert, but at least the database will be consistent.



By the way, fair is fair:
The same byzzare results will emerge when you suddenly stop (kill) a transactional database and then truncate its transaction log. bad thing. dont do it.
I have also wittnessed this. It was (very sad) when that database suddenly completely ran out of discspace, and the admin didnt know any other sollution then to delete 'that
big logfile'. Ouch!

Again, good points, and very admirable that you balanced your review :) I don't think we can blame the db software or architecture for the inadequacies of the people running it, however. :)

View PostTrogdor, on 31 Aug, 2007 - 10:04 AM, said:

I have seen so many problems arrise due to lazy choice of architectures, that i can not be acidic enough when someone suggests MySql 'becouse everybody uses it'


- meh, this disimproved my mood.

I'm sorry to have affected your mood...I'm just always curious as to what experiences form people's opinions. As stated earlier, I have no vested interest in MySQL as a db architecture - in my business, Oracle is really the only way to go, but I try to remain familiar with all.

thanks for taking the time to explain your point of view.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1