Save yourself and forget MySQL and MariaDB lazarus / 16-Databases ---------------------------------------------------------------------- Graeme · 2013-05-07 23:51 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 · 2013-05-08 03:35 I forgot where I read it but someone (somewhere) opined that Oracle intentionally made MySQL bad enough so customers would shift to Oracle . ---------------------------------------------------------------------- taazz · 2013-05-08 05:25 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 · 2013-05-08 06:07 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 · 2013-05-08 07:02 Quote from: Graeme on May 08, 2013, 01:51:23 amYou 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 · 2013-05-08 08:15 Quotemysql> insert into a (b) values (1); ERROR 1364 (HY000): Field 'c' doesn't have a default valueThis 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 · 2013-05-08 08:34 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 · 2013-05-08 09:05 QuoteSubject 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 · 2013-05-09 09:01 Quote 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 · 2013-05-09 10:10 String as  alphanumeric value  like 'Sex and wine' or integer  value as string  like '123'? Cher. A. ---------------------------------------------------------------------- chrnobel · 2013-05-09 11:18 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 · 2013-05-09 12:27 Quote from: chrnobel on May 09, 2013, 01:18:20 pmMaybe 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 · 2013-05-09 12:42 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 · 2013-05-10 11:39 Quote from: zzzato on May 08, 2013, 10:34:08 amThis 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 · 2013-05-10 11:46 Quote from: goodname on May 09, 2013, 02:42:41 pmThis 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. ---------------------------------------------------------------------- zzzato · 2013-05-10 12:09 Quote from: Graeme on May 10, 2013, 01:46:24 pmQuote from: goodname on May 09, 2013, 02:42:41 pmThis 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. I used Ibm2 (win e As/400), M$SqlServer (CE too....), Msde, Sybase, Oracle, Firebird, Interbase. Now I'm using MySql too and I've some question about it... 1) WHY default server mode IS NOT strict mode? 2) WHY mysqldump doens't have a dump default filename? 3) WHY mysqldump has a default to NOT export stored procedures? In other word, WHY MySql is different? About strict mode, MySqwl has different engines. So, it IS NOT like other databases. I'm using InnoDb engine. MAYBE, using MyIsam strict mode is not a critical behavoir... About other questions, I'm searching a VALID answer. A friend of mine says me "Mysql was not born in windows....you can get mysqldump output to do fantastic things using bash!". But...  Oracle was not born in windows too..and exp dumps stored procedures and HAS a default file name for dump... Cher. A. ---------------------------------------------------------------------- goodname · 2013-05-10 12:57 MySQL is different because it was targeted at building web pages. On a web site the last thing you want visitors to see is a database error page. If some user writes a post longer then 255 characters it didn't matter that the string was truncated. The web site kept running. The behaviour has had to adapt as the target audience has changed. SQLite is targeted at embedded data storage. Do not have a lot of experience with this engine but I'm sure there are good target user reasons for it's behaviour. It is arguably the most widely distributed and used database engine in the world so what it does it does very well. Oracle, Firebird, MSSQL, PostgreSQL were all designed for client server environments where the data integrity was the most important priority. ---------------------------------------------------------------------- zzzato · 2013-05-10 13:43 Quote from: goodname on May 10, 2013, 02:57:12 pmMySQL is different because it was targeted at building web pages. On a web site the last thing you want visitors to see is a database error page. If some user writes a post longer then 255 characters it didn't matter that the string was truncated. The web site kept running. The behaviour has had to adapt as the target audience has changed. SQLite is targeted at embedded data storage. Do not have a lot of experience with this engine but I'm sure there are good target user reasons for it's behaviour. It is arguably the most widely distributed and used database engine in the world so what it does it does very well. Oracle, Firebird, MSSQL, PostgreSQL were all designed for client server environments where the data integrity was the most important priority. Hi. Thanks for your answer. I agree about different target and/or strict mode. But what do you think about my mysqldump  questions? Why it has not a lot of default  values? Cher. A.