fox
13
ah wtf why is it bool, it should be datetime.
also, how did this work on my mariadb test instance? jesus. how can you convert datetime to bool. 
fox
14
alright, script has been fixed.
if your database is in inconsistent state (because i donāt think PDO on mysql actually has functional rollback - for whatever reason, i have no idea) do the following to revert back to 135:
alter table ttrss_archived_feeds drop column created;
update ttrss_version set schema_version = 135;
sorry about that. let this be a lesson to me^Wyou - use postgres. 
Hehā¦ I was just running through the schema in my dev environment and it workedā¦ But I copy/pasted it after you just made that last commit.
Yeahā¦ the new updated schema change worked when I run it manually.
fox
16
maybe it works on mariadb but not on mysql. since thereās no functional transactions first update failed on change column, op retried, and couldnāt add it because it was already there for the first attempt
well thatās my theory 
iām worried about this lack of rollback though, itās a real problem
Yeah, I also had both errors and just manually deleted the ācreatedā column it created.
After your change (on c8fc9eee0c5d7a202bd79db41292859c69a79347) it works without problems for me.
Thanks for the fix!
But itās not like you can really control it. It not reverting the change on a failed commit is outside the scope of TT-RSS.
fox
19
schema version update is wrapped in a transaction block. it just doesnāt work on mysql.
But without some active [PHP] scripting to check the result of each query, youād never know whether it worked, and thatās the whole point of transactions. It not working on MySQL sucks, but what else can you do? The whole point of the transaction is all-or-nothing so you donāt have to worry about a failure at one point.
Arenāt most distros shipping MariaDB anyway? You could always update the system requirements to use MariaDB and drop āMySQLā supportāeven though they are similar. Itās a drastic approach but TT-RSS uses transactions a lot and thereās going to be corruption if this isnāt working.
fox
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)
fox
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
Justā¦ wowā¦ 
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.
fox
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?
jboehm
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
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).
fox
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
Itās worth itā¦ I was there, once, then set up postgresā¦ and have pretty much moved everything except mythtv over now.
I see youāre developing a taste for subtle understatement, Fox.
Yeahā¦Iāll have to do it at some point. Itāll happen when something breaks and Iām angry/annoyed.
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.
