5 ms·
> Even tools people joke about a lot like mysql never gave me this sort of data corruption. That's about a decade out of date at this point. MySQL/InnoDB is th
by hodgesrm 6y ago
> Even tools people joke about a lot like mysql never gave me this sort of data corruption.
That's about a decade out of date at this point. MySQL/InnoDB is the standard table engine and corruption is exceedingly rare. As of 2014, when I last directly worked on MySQL prod systems, there was no practical difference from PostgreSQL in terms of transactional guarantees. That includes APIs like JDBC which we used for billions of transactions.
- morelisp 6y agoMySQL still has no transactional DDL (and I think still even autocommits if you try). This is a major difference from Postgres which I believe supports everything short of dropping tables.
- takeda 6y agoI wouldn't use that particular thing against MySQL. DDL normally supposed to be always outside of a transaction, it's just PostgreSQL feature that you can use them inside and be able to rollback. BTW I'm convinced you also can drop table within a transaction in PostgreSQL.
- morelisp 6y agoNo, MySQL stands out here. Postgres, SQL Server, DB2, and Firebird all give at least some way to do some major DDL transactionally. Usability varies (e.g. Oracle supports a very specific kind of change that is not its normal DDL statements), but it's at least possible. https://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis#Oracle https://wiki.postgresql.org/wiki/Transactional_DDL_in_Postgr... That MySQL autocommits is also even worse than just "doesn't support it."
- dragonwriter 6y ago> DDL normally supposed to be always outside of a transaction A basic element of the relational model is that metadata is stored as relational data and that the same guarantees that apply to manipulating main data in the database apply to manipulating the schema metadata. It's true that many real relational databases compromise on this element in various ways at times, but it is absolutely not the case that DDL “is supposed to be” non-transactional.
- yobert 6y agoEvery month, we do an external database import into our production PostgreSQL database. In a single transaction, we drop dozens of tables, create new ones with the same names, insert hundreds of thousands of rows, and recreate indexes, all in a single transaction. It works flawlessly.
- vijaybritto 6y agowow! have you tested for network errors in any of these steps? Will it rollback automatically if there are any errors?
- sterwill 6y agoIf it's in a transaction, it will do the same thing it would do for any failed transaction (roll it back). DDL isn't special.
- arp242 6y agoThe biggest issue with MySQL/MariaDB isn't so much data corruption at the InnoDB level but stuff like: MariaDB [test]> create table test ( i int ); Query OK, 0 rows affected (0.06 sec) MariaDB [test]> insert into test values (''), ('xxx'); Query OK, 2 row affected, 2 warning (0.01 sec) MariaDB [test]> select * from test; +------+ | i | +------+ | 0 | | 0 | +------+ 2 row in set (0.01 sec) There's a bunch of other similar caveats as well, and this can really take you by surprise. I've seen it introduce data integrity issues more than once. That's a new MariaDB 15.1 with the default settings I just installed the other day to test some WordPress stuff. I know there are warnings, and that you can configure this by adding STRICT_ALL_TABLES to SQL_MODE, but IMO it's a dangerous default. This is also an issue with using MongoDB as a generic database: every time I've seen it used there were these kind of data integrity issues: sometimes minor, sometimes brining everything down. Jepsen reports aside, this alone should make people double-check if they really want or need MongoDB, because turns out that most of the time you don't really want this.
- mathnode 6y ago15.1 is not a version. Since MariaDB 10.2, this is not possible as strict_trans_tables is enabled by default in sql_mode.