21

if query fails, PDO throws an exception and updater does a rollback. like i said, this works like it should on postgres.

there’s also begin … commit in the SQL script which are technically not needed but removing them doesn’t make any difference whatsoever.

well, it works like it should in mariadb console. strange.

MariaDB [ttrss]> select distinct created from ttrss_archived_feeds;
+---------------------+
| created             |
+---------------------+
| 2019-03-06 18:58:15 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [ttrss]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [ttrss]> update ttrss_archived_feeds set created = NOW();
Query OK, 247 rows affected (0.03 sec)
Rows matched: 247  Changed: 247  Warnings: 0

MariaDB [ttrss]> select distinct created from ttrss_archived_feeds;
+---------------------+
| created             |
+---------------------+
| 2019-03-06 22:01:24 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [ttrss]> rollback;
Query OK, 0 rows affected (0.03 sec)

MariaDB [ttrss]> select distinct created from ttrss_archived_feeds;
+---------------------+
| created             |
+---------------------+
| 2019-03-06 18:58:15 |
+---------------------+
1 row in set (0.00 sec)
22

looks like it’s this:

Some databases, including MySQL, automatically issue an implicit COMMIT when a database definition language (DDL) statement such as DROP TABLE or CREATE TABLE is issued within a transaction. The implicit COMMIT will prevent you from rolling back any other changes within the transaction boundary.

mysql is irredeemable shit garbage
whoever developed this pile of fuck should rot in hell for crimes against principles of ACID

https://dev.mysql.com/doc/refman/5.7/en/cannot-roll-back.html

god i wish i could fucking roll back support for mysql from tt-rss

23

Just… wow… :roll_eyes:

e:

Well realistically it would probably be easier than the move to PDO. But I have so much stuff in MySQL (MariaDB) that it would suck for me because I’d have to keep two databases going.

24

eh, we all know it’s not going to happen, not at this point anyway.

You should design your transactions not to include such statements.

just never update your schema, ok, problem solved.

e: how does this look? menacing enough?

03uR0q2s

25

Here is a line by line how-to for a recovery. Substitute without quotes anywhere below you see DB_*_FROM_CONFIG_PHP

Do a pull to get the latest goodness from git.

mysql -u DB_USER_FROM_CONFIG_PHP -p
You will be asked for password. Use DB_PASS from config.php

mysql> USE DB_NAME_FROM_CONFIG_PHP
mysql> alter table ttrss_archived_feeds drop column created;
mysql> update ttrss_version set schema_version = 135;

Reloading your ttrss page will give a new warning about mysql. The next page will ask to perform the update. It worked for me and I’m back up in running.

thanks

26

Well, then never consider to include support for Oracle DBs. DDL statements in Oracle are generally not “transactionable” as Oracle has the same approach as MySQL (implicit commit).

27

oh it’s really not that hard to eschew oracle, the most openly mustache twirling evil company in tech sector, even if you ignore this particular oddity of their database

28

It’s worth it… I was there, once, then set up postgres… and have pretty much moved everything except mythtv over now.

29

Can you make it strobe? :smiley:

30

I see you’re developing a taste for subtle understatement, Fox.

31

Yeah…I’ll have to do it at some point. It’ll happen when something breaks and I’m angry/annoyed.

32

IIRC, Sybase (and so probably MSSQL also?) didn’t allow DDL in transactions. It makes a little sense, Q: how do you rollback a drop table? A: DB restore. I guess the db could temporarily rename the table and actually drop it on commit, but DDL could get quite complex inside a transaction. If postgres does it, then I am impressed.