Tiny Tiny RSS: Community

Failed Updating to version 136


#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. :face_with_raised_eyebrow:


#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. :slight_smile:


Feed "Tiny Tiny RSS: Forum": cannot unsubscribe
#15

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.


#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 :male_detective:

i’m worried about this lack of rollback though, it’s a real problem


#17

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!


#18

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.


#19

schema version update is wrapped in a transaction block. it just doesn’t work on mysql.


#20

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.


#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 begincommit 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?


#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.