I did a pull at ~9am PST. (Before that I did a pull ~8pm last night with no issues. So there was a small number of updates for the 9am pull.) I did not stop the update_daemon2.php during the pull. Then I hit reload in my browser. This pulled up the schema update page. My memory is fuzzy but I think it did 135 and 136, but I’m not positive. Regardless I did no hand manipulation of the schema. This was all automated vie the normal schema update. My first post is were it is now, at 135 but failing 136.
Maybe a difference between the 136.sql between mysql and pgsql?
mysql:
alter table ttrss_archived_feeds change created created bool not null;
pgsql:
alter table ttrss_archived_feeds alter column created set not null;
no, that’s how you set a not null constraint on mysql. also OPs update script fails before this line.
I have a similar issue.
OPs message comes if you retry after a failed update from 135 to 136.
I used the web updater ( …/public.php?op=dbupdate )
The original error is (at least for me)
Performing updates to schema version 136
Updating to version 136
begin
alter table ttrss_archived_feeds add column created datetime
update ttrss_archived_feeds set created = NOW()
alter table ttrss_archived_feeds change created created bool not null
Error: 22003, 1264, Out of range value for column 'created' at row 1
One of the updates failed. Either retry the process or perform updates manually.
I am on master 69a691f4e13382924d93c85091a2a5f525d1effe
I run
- Gentoo with kernel 4.20.1
- mysql Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using EditLine wrapper
- PHP 7.2.14
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. 
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.
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.
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.
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)
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.
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?
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).
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.