MySQL Data Integrity - STRICT mode
Posted: Fri Jan 30, 2009 3:35 am
It's 3am. I'm not sleepy. And it's been a helluva long few days studying and learning some new things, so here's another JA Epic Post for you (in case you too cannot sleep tonight).
I have been using and learning about MySQL for a number of years now, and something I actually did not know until 2 days ago (most of my DB work has been with MS SQL or Oracle) - MySQL comes shipped in a non-strict mode configuration. I knew about the modes MySQL can run in, I just never knew the effects of not being in a strict database mode. Until now... What this basically means is the Database engine does not force you to insert or update with proper datatypes that the fields are configured to.
Example (which is one I discovered in my own data processes recently) - In non-strict mode, you can insert an integer value of 32767 into a destination field set to tinyint(3), and MySQL will say "Meh, that number is too big for tinyint(3), so I'm just gonna round that down for ya mkay?" and you end up with 255. Another example of non-strict is trying to shove a string value into a numeric field. The number will simply be 0 (zero) in the end.
In this sample table, I want to insert a record. So I would run the query:
The Result:
value 500 gets truncated down to 255. If you are using a GUI interface, you may have seen "1 row(s) inserted, 1 warning(s)". You can see exactly what that warning is by issuing the command:
Complex, huh? That will display the following helpful information:
And that is the a.b.c. of what may happen to your data if you are not running in STRICT mode... a real-world example could also be that you add or replace a column in an insert statement. Non-strict MySQL will not complain if the column count matches... instead, it'll just start shoving strings into ints and ints into strings and make you scratch your head for 6 mos like I've been doing. 
MySQL devs decided this was ok, since many users of an open source community DB like this do not want such massive restrictions put on their data - and frankly imo, most DB designers and application developers are too lazy to make the data types match anyway. MySQL does the thinking for them. Wee!
Enter: sql_mode = "STRICT_ALL_TABLES" - the Charles Bronson of Data Integrity. Nothing gets by this guy, in theory. If you set your local MySQL "my.ini" (or "my.cnf" on linux) to sql_mode = "STRICT_ALL_TABLES", this now rejects ANY attempt to insert an oversized or mis-matched value into your database. It is great for boasting that you have the best data in town! Running that same test above on MySQL in STRICT mode should send not a warning, but an error, and you will notice the data is not present in your table (unless you use some assy GUI tool that truncs your data FOR you before submitting it to MySQL)
Regarding EQ2Emulator World and Database:
So far, the World code itself performs beautifully and I have not found a type mismatch yet. The problem for me was in the world building tools I have. Since the Raw data has been tweaked a lot since I started on this project, but the World DB has not been changed to reflect these new discoveries. Those of you who run a server will notice today's update contains many changes to the Items structures. This is because of my recent epiphany that damage_high1 should NOT be 255 for all items, but can be well over 255 for some!
The short story is you, the Admin, likely have nothing whatsoever to worry about. Whether you run an EQ2Emulator on a STRICT MySQL instance or a LOOSE MySQL instance, the data you will get from us will be compliant with a STRICT mode of operation - and all data types will be considered valid.
I have been using and learning about MySQL for a number of years now, and something I actually did not know until 2 days ago (most of my DB work has been with MS SQL or Oracle) - MySQL comes shipped in a non-strict mode configuration. I knew about the modes MySQL can run in, I just never knew the effects of not being in a strict database mode. Until now... What this basically means is the Database engine does not force you to insert or update with proper datatypes that the fields are configured to.
Example (which is one I discovered in my own data processes recently) - In non-strict mode, you can insert an integer value of 32767 into a destination field set to tinyint(3), and MySQL will say "Meh, that number is too big for tinyint(3), so I'm just gonna round that down for ya mkay?" and you end up with 255. Another example of non-strict is trying to shove a string value into a numeric field. The number will simply be 0 (zero) in the end.
Code: Select all
CREATE TABLE `example` (
`id` int(10) unsigned NOT NULL auto_increment,
`txt` varchar(10) collate latin1_general_ci default NULL,
`num` tinyint(3) unsigned default '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDBCode: Select all
insert into example (txt, num) values ('Too Big', 500);Code: Select all
show warnings;MySQL devs decided this was ok, since many users of an open source community DB like this do not want such massive restrictions put on their data - and frankly imo, most DB designers and application developers are too lazy to make the data types match anyway. MySQL does the thinking for them. Wee!
Enter: sql_mode = "STRICT_ALL_TABLES" - the Charles Bronson of Data Integrity. Nothing gets by this guy, in theory. If you set your local MySQL "my.ini" (or "my.cnf" on linux) to sql_mode = "STRICT_ALL_TABLES", this now rejects ANY attempt to insert an oversized or mis-matched value into your database. It is great for boasting that you have the best data in town! Running that same test above on MySQL in STRICT mode should send not a warning, but an error, and you will notice the data is not present in your table (unless you use some assy GUI tool that truncs your data FOR you before submitting it to MySQL)
Regarding EQ2Emulator World and Database:
So far, the World code itself performs beautifully and I have not found a type mismatch yet. The problem for me was in the world building tools I have. Since the Raw data has been tweaked a lot since I started on this project, but the World DB has not been changed to reflect these new discoveries. Those of you who run a server will notice today's update contains many changes to the Items structures. This is because of my recent epiphany that damage_high1 should NOT be 255 for all items, but can be well over 255 for some!
The short story is you, the Admin, likely have nothing whatsoever to worry about. Whether you run an EQ2Emulator on a STRICT MySQL instance or a LOOSE MySQL instance, the data you will get from us will be compliant with a STRICT mode of operation - and all data types will be considered valid.