Save yourself and forget MySQL and MariaDB

[Graeme]

Just a little gem I wanted to share. Found out while testing. [This was brought to my attention by a fellow developer, and I confirmed all this on my test server running running a stock standard MySQL 5.5].

Code: [Select]

mysql> create table a (b int not null, c int not null);
Query OK, 0 rows affected (0.17 sec)

Code: [Select]

mysql> insert into a (b) values (1);
Query OK, 1 row affected, 1 warning (0.03 sec)
As if the above was not miraculous enough, then you get:

mysql> select * from a;

 +---+---+
 | b | c |
 +---+---+
 | 1 | 0 |
 +---+---+

1 row in set (0.00 sec)

Unbelievable!

When you switch from MySQL to Postgres or Firebird, suddenly 200 of your 900 unit tests stop working and you wonder why...

And then they have the audacity to call MySQL (and probably MariaDB too) a RDBMS! You gotta be kidding. You can even insert a string into a int field with MySQL (and SQLite for that matter).

MySQL simply ignores the rules the schema defined. So how can you ever trust such a "database system" (I honestly can't call MySQL that), and how can you trust the data you retrieve, is truly what you inserted.

Then I haven't even mentioned that the SQL standard says SQL statements are case insensitive, yet depending on where you installed MySQL databases, the table names may be case sensitive. eg the same database on FreeBSD or Linux is case sensitive, yet moving it to Windows it is not! What damn consistency is that! This issue is because the database tables are stored as files in a directory. So now the file system dictates the SQL statements!!

Developers should really stop believing the hype of MySQL or MariaDB, and switch to a real RDBMS like Firebird or PostgreSQL.

[JJVillamor]

I forgot where I read it but someone (somewhere) opined that Oracle intentionally made MySQL bad enough so customers would shift to Oracle .

[taazz]

that's BS MySQL does not need help on that department. It has done a remarkable job it even before the oracle buy out.

[skobov]

I am not mysql fan, but ...

Code: [Select]

mysql> create table a (b int not null, c int not null);
Query OK, 0 rows affected (0.12 sec)

mysql> insert into a (b) values (1);
ERROR 1364 (HY000): Field 'c' doesn't have a default value

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 1.1.5                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.10                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86                          |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
7 rows in set (0.01 sec)

mysql>


[itmitica]

You can even insert a string into a int field [...] (and SQLite for that matter).

It's a documented feature:

http://www.sqlite.org/datatype3.html

2.0 Type Affinity

In order to maximize compatibility between SQLite and other database engines, SQLite supports the concept of "type affinity" on columns. The type affinity of a column is the recommended type for data stored in that column. The important idea here is that the type is recommended, not required. Any column can still store any type of data. It is just that some columns, given the choice, will prefer to use one storage class over another. The preferred storage class for a column is called its "affinity".

--
Mitică

[ludob]

mysql> insert into a (b) values (1);
ERROR 1364 (HY000): Field 'c' doesn't have a default value

This behavior depends on the server mode. The error is thrown when in strict mode. Default is strict mode off. See http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_strict_trans_tables.

Use SELECT @@GLOBAL.sql_mode or SELECT @@SESSION.sql_mode to show the server mode.

[zzzato]

Subject is provocative.
This issue is related to server mode behavior.
I'm using MySql 5.1 in strict mode and each time I forget something, MySql says me "go away, stupid!".
5.1 is last version before Oracle coming.

Cia'!

A.

[ludob]

Subject is provocative.
This issue is related to server mode behavior.
I'm using MySql 5.1 in strict mode and each time I forget something, MySql says me "go away, stupid!".

Having strict mode off was also the default in 5.1 afaik. So the MySQL default is to welcome the stupid
This resembles a lot the strict and explicit options for Visual Basic. First you allow everything per default and then provide a mode to help you clear up the mess.

[Pascaluvr]

You can even insert a string into a int field with MySQL (and SQLite for that matter).

Very true.  So you believe that this should be picked up by the database and an exception raised?   WHY?  Surely if the program design needs strict typecasting then the programmer should be editting the data.   The job of a dbms is to store whatever data is given to it and allow for its retrieval.  The job of the programmer is to ensure that the data supplied by the user is in a valid format for storage and use by other applications

The only constraints applied by sqlite is to maintain the integrity of the data and references - as in - UNIQUE, NOT NULL, Foreign Key etc.

To suggest that a dbms is responsible for checking the format of the data would also imply that for a Phone Number the dbms should call the number to ensure its valid,,,,  for a url, it  should check if it exists ....   for sexual preference, it should check if 'bi' is legally allowed in the users country... the list goes on.

Very simply, its the programmer's job to check the validity of the data being stored - only a lazy programmer would expect the dbms to that for him and raise exceptions.

[zzzato]

String as  alphanumeric value  like 'Sex and wine' or integer  value as string  like '123'?

Cher.

A.

[chrnobel]

Maybe I do misunderstand something, but afaik, NULL is not the same as zero - so when inserting nothing into an integer field which is not allow to be blank, it should be zero.

[zzzato]

Maybe I do misunderstand something, but afaik, NULL is not the same as zero - so when inserting nothing into an integer field which is not allow to be blank, it should be zero.

Null is not zero and viceversa.
Null is not empty string and viceversa.

  Null is "not value on this field". Zero is a value.

If numeric field has not null constraint, you MUST set it to 0.
If string fields has not null constraint, you MUST set it to some string.

A.

[goodname]

Null means an unknown value and is not the same thing as 0 or an empty string which are known values. Null is the standard default value if no default value is set. Some database engines don't follow this line of thought and do whatever it can to allow inserts and updates to work.

MySQL used to and may still silently truncate insert strings if they are longer then the varchar field allows. This means you can lose data without knowing it is happening.

edit: If you want for some business logic reason the SQL functions coalesce and nullif can make null have the same meaning as 0 or empty strings.

[Graeme]

This issue is related to server mode behavior.

The thing is, I didn't change anything. I simply installed MySQL 5.5 under FreeBSD ports - which fetches the source code archive from the original authors, compiles it on my system, then installs. So the "sever mode setting" is then set to rubbish by default.  Even having such an option is unbelievable! What did they think is the point of the table schema rules then?

[Graeme]

This means you can lose data without knowing it is happening.

This is my big concern with MySQL and SQLite. They seem to have their own [warped] ideas or interpretation of the table schema rules. The result is invalid (as far as I'm concerned) or modified data - without the application/developer/end-user being notified of this. So what you put in, is not what you get out. When it comes to data - that is a huge concern to me.