4 ms·

note the emphasis on "serious stuff". I did in fact touch MySQL since then in the process of setting up various open source applications that require MySQL. W

by pilif 16y ago

note the emphasis on "serious stuff". I did in fact touch MySQL since then in the process of setting up various open source applications that require MySQL. While back then, I needed Postgres for some features MySQL didn't have but otherwise didn't really dislike MySQL, over those 10 years, I really learned to hate MySQL: - try (erroneously) adding or inserting latin1-data into a UTF-8 table. You'd expect an error message, but instead your data gets truncated at the first byte with the high bit set. No warning. This means that you have to re-read and compare whatever data you stored to be sure that you didn't lose any. - once, mysqldump stopped dumping after coming across a corrupt table. It did so while still exiting with a 0 exit code. That meant that for months my backups were incomplete. - "alter table sometable add somcolumn integer not null" will gladly add the column and place null values in each row. Null values that should absolutely not be there. No error. No warning. - Same goes for date columns, but there it's even better as it'll set the date to 0000-00-00 which is invalid if you try to add this in an insert or update. - I said "corrupt table" earlier. Yeah. That happens with MySQL, whereas it never ever (with the exception of faulty hardware of course) happens with any other RDBMS - including PostgreSQL. Now, in my opinion, a database that doesn't guarantee that the data you put in can then be read back out in unaltered form (or at all) is, really, no real RDBMS. Sorry. (edit: reason for not posting this in my original reply: I'm basically repeating above points over and over here on HN and I thought people would know my gripes by now)

rimantas 16y ago
So basically you learned to hate MySQL while not doing anything serious with it. Interesting. - try (erroneously) adding or inserting latin1-data into a UTF-8 table. You'd expect an error message, but instead your data gets truncated at the first byte with the high bit set. False. - "alter table sometable add somcolumn integer not null" will gladly add the column and place null values in each row. Null values that should absolutely not be there. No error. No warning. False. Same goes for date columns, but there it's even better as it'll set the date to 0000-00-00 which is invalid if you try to add this in an insert or update. That's why I said those who don't know MySQL like to complain — hardly any of doing the rants have heard about sql modes: mysql> alter table t1 add column (dob date not null); ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'dob' at row 1 - I said "corrupt table" earlier. Yeah. That happens with MySQL, whereas it never ever (with the exception of faulty hardware of course) happens with any other RDBMS - including PostgreSQL. Funny. Googling with right keywords paints a bit different picture. Must be some other PG, I guess.
- pilif 16y ago
  - try (erroneously) adding or inserting latin1-data into a UTF-8 table. You'd expect an error message, but instead your data gets truncated at the first byte with the high bit set. False. http://www.gnegg.ch/2008/02/failing-silently-is-bad/ http://www.gnegg.ch/2008/02/failing-silently-is-bad/ - "alter table sometable add somcolumn integer not null" will gladly add the column and place null values in each row. Null values that should absolutely not be there. No error. No warning. False. ok. it inserts them as 0 - that's in improvement from last I checked. Still. It silently does stuff. It should not let me do it in the first place. Same goes for the date. And please don't tell me the error message you get back there is quite correct. That alter table statement tries to silently add a default and later fails due to the default not being valid. Also, see this: mysql> alter table foo add dob date not null; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe foo; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | value | varchar(10) | NO | | | | | intvalue | int(11) | NO | | NULL | | | dob | date | NO | | NULL | | +----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) mysql> this table now contains two columns that can't be null and yet the default is null. Thanks. Concerning corrupted tables. I have around 1.5 TB of data spread over ~10 databases and over the last 10 years, I never had a corrupt table that I could not trace back to hardware failure (harddrives, raid controllers, RAM). Over the same period, I had around 200 MB of data spread over ~10 databases in MySQL and I had three cases of corrupt tables that I could not trace back to defective hardware as various diagnostic tools yielded no error and the machines still run without problems with PostgreSQL. Maybe I'm just unlucky.
  - rimantas 16y ago
    Maybe you just don't know what you are doing.
    - 16y ago

by pilif 16y ago

rimantas 16y ago

So basically you learned to hate MySQL while not doing anything serious with it. Interesting. - try (erroneously) adding or inserting latin1-data into a UTF-8 table. You'd expect an error message, but instead your data gets truncated at the first byte with the high bit set. False. - "alter table sometable add somcolumn integer not null" will gladly add the column and place null values in each row. Null values that should absolutely not be there. No error. No warning. False. Same goes for date columns, but there it's even better as it'll set the date to 0000-00-00 which is invalid if you try to add this in an insert or update. That's why I said those who don't know MySQL like to complain — hardly any of doing the rants have heard about sql modes: mysql> alter table t1 add column (dob date not null); ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'dob' at row 1 - I said "corrupt table" earlier. Yeah. That happens with MySQL, whereas it never ever (with the exception of faulty hardware of course) happens with any other RDBMS - including PostgreSQL. Funny. Googling with right keywords paints a bit different picture. Must be some other PG, I guess.

pilif 16y ago
- try (erroneously) adding or inserting latin1-data into a UTF-8 table. You'd expect an error message, but instead your data gets truncated at the first byte with the high bit set. False. http://www.gnegg.ch/2008/02/failing-silently-is-bad/ http://www.gnegg.ch/2008/02/failing-silently-is-bad/ - "alter table sometable add somcolumn integer not null" will gladly add the column and place null values in each row. Null values that should absolutely not be there. No error. No warning. False. ok. it inserts them as 0 - that's in improvement from last I checked. Still. It silently does stuff. It should not let me do it in the first place. Same goes for the date. And please don't tell me the error message you get back there is quite correct. That alter table statement tries to silently add a default and later fails due to the default not being valid. Also, see this: mysql> alter table foo add dob date not null; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe foo; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | value | varchar(10) | NO | | | | | intvalue | int(11) | NO | | NULL | | | dob | date | NO | | NULL | | +----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) mysql> this table now contains two columns that can't be null and yet the default is null. Thanks. Concerning corrupted tables. I have around 1.5 TB of data spread over ~10 databases and over the last 10 years, I never had a corrupt table that I could not trace back to hardware failure (harddrives, raid controllers, RAM). Over the same period, I had around 200 MB of data spread over ~10 databases in MySQL and I had three cases of corrupt tables that I could not trace back to defective hardware as various diagnostic tools yielded no error and the machines still run without problems with PostgreSQL. Maybe I'm just unlucky.
- rimantas 16y ago
  Maybe you just don't know what you are doing.
  - 16y ago