search

ShokoAnime / ShokoServer

Repository for Shoko Server.
http://shokoanime.com/shoko-server/
MIT License
376 stars 74 forks source link

New Version of Shoko Server cant patch Schema #1053

Closed t0815 closed 1 year ago

t0815 commented 1 year ago

VERSION INFORMATION

Server Version: 4.2.1

Desktop Version: 4.2.1

If you've compiled your own version, please use the last commit you compiled.

LOG FILE

2023-04-01.log

DESCRIPTION

Upon Startup a SQL Error is thrown during Database Schema patch phase

Database Error : DATABASE ERROR: 99.2 ALTER TABLE AniDB_File MODIFY IsCensored bit NULL; ALTER TABLE AniDB_File MODIFY `IsDepre

catedbit not null; ALTER TABLEAniDB_FileMODIFYIsChaptered` bit not null | MySql.Data.MySqlClient.MySqlException (0x80004005): Data too long for column 'IsChaptered' at row 49 at MySql.Data.MySqlClient.MySqlStream.ReadPacket() at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader() at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() at Shoko.Server.Databases.MySQL.Execute(MySqlConnection connection, String command) in D:\a\ShokoServer\ShokoServer\Shoko.Server\Databases\MySQL.cs:line 925 at Shoko.Server.Databases.MySQL.ExecuteCommand(MySqlConnection connection, String command) in D:\a\ShokoServer\ShokoServer\Shoko.Server\Databases\MySQL.cs:line 911 Notify developers about this error, it will be logged in your logs

STEPS TO REPRODUCE

  1. Update to new Shoko ver 4.2.1
  2. Start Shoko Server
  3. open web ui, displaying the error
da3dsoul commented 1 year ago

Yes, that would work

t0815 commented 1 year ago

Sorry deleted my previous message, since i did not fixed the issue.... i did update the rows where IsChaptered was -1 to 0, but still gettimg the error. Maybe, there are values greater 1 in there too... i will check

da3dsoul commented 1 year ago

I can tell you that has worked for others, so I would check for >1 and null

t0815 commented 1 year ago

ok, caching at its best... did work. But got shown same error still after restart. Guess html browser cache lol. Anyways, now next SQL issue pops up:

DROP TABLE CrossRef_AniDB_Trakt | MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown table 'jmmserver.crossref_anidb_trakt'

t0815 commented 1 year ago

did create the table manually in dbms, so patcher can drop it. Whats strange, im fairly certain i never modified any Tables manually, for that error to come up.

da3dsoul commented 1 year ago

@revam care to comment? I'm not familiar with the changes there

revam commented 1 year ago

@revam care to comment? I'm not familiar with the changes there

it wasn't me.

bc71ec2938f42ecfc7d60f1e54acfe6518d64c42

revam commented 1 year ago

it seems to be set to be not nullable and -1 by default, and we're checking for null in the database migration, so it's failing the query. I can do a quick fix for the migration, but i can't test it since i don't use mysql/mariadb.

da3dsoul commented 1 year ago

@revam no the last comment. The Trakt changes

revam commented 1 year ago

ok, caching at its best... did work. But got shown same error still after restart. Guess html browser cache lol. Anyways, now next SQL issue pops up:

DROP TABLE CrossRef_AniDB_Trakt | MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown table 'jmmserver.crossref_anidb_trakt'

we have a set of rename commands that should run before any patch commands run during every startup https://github.com/ShokoAnime/ShokoServer/blob/ebd4bd418b1b536de82e37abdd143a803122431f/Shoko.Server/Databases/MySQL.cs#L739-L814 so idk what went wrong

revam commented 1 year ago

i completely missed the SQL command. if i were to take a guess then i would say it tried to delete the table twice because the user restarted the server. sorry for not reading properly. I can't say for certain, but that is my best guess.

revam commented 1 year ago

We will have to fix the faulty mysql/mariadb migration, but I'm still not quite sure how to best do it. We can either 1) modify the existing migration and have the users re-run the upgrade on a backup, or 2) add more migrations.

I think we can ignore the last exception with the DROP TABLE command, since it was most likely a by-product of the server failing to upgrade, but, we should still check if it's reproducible after fixing the first exception.

Also, i don't have a mysql/mariadb database to test with, so if anyone with such a database can test the changes, that would be great.

t0815 commented 1 year ago

i could rollback my db to previous version and try again and report back.

EDIT: forgot, is there a daily build for this to test, or do i have to compile myself?

da3dsoul commented 1 year ago

Daily builds are every commit, so it's always the latest...unless the build pipeline breaks

t0815 commented 1 year ago

As for fixing the migration, im pretty sure i never messed with the data for this table. So i assume at some point (maybe old jmm even, since my db is pretty old by now) wrote -1 values for whatever reason in this col. So before running migration for this table, it would be best to just update all rows where -1 or NULL to 0. Then first error should be fixed and with that 2nd might not occur.

Altho, im not 100% certain, but i run mysql on a windows machine, and is far as i remember, table name case is ignored. So i assume the table did not exist in the first place. hmmm, i will check in the backup.

ADD: ok, the table in old backup is called "crossref_anidb_traktv2". There is no table named "crossref_anidb_trakt".

danmarce commented 1 year ago

In my case, when upgrading to 4.2.2 I got the same:

Database Error : DATABASE ERROR: 99.2 ALTER TABLE AniDB_File MODIFY IsCensored bit NULL; ALTER TABLE AniDB_File MODIFY IsDeprecated bit not null; ALTER TABLE AniDB_File MODIFY IsChaptered bit not null | MySql.Data.MySqlClient.MySqlException (0x80004005): Data too long for column 'IsChaptered' at row 70 at MySql.Data.MySqlClient.MySqlStream.ReadPacket() at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader() at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() at Shoko.Server.Databases.MySQL.Execute(MySqlConnection connection, String command) in D:\a\ShokoServer\ShokoServer\Shoko.Server\Databases\MySQL.cs:line 925 at Shoko.Server.Databases.MySQL.ExecuteCommand(MySqlConnection connection, String command) in D:\a\ShokoServer\ShokoServer\Shoko.Server\Databases\MySQL.cs:line 911 Notify developers about this error, it will be logged in your logs

I tested each command ALTER TABLE AniDB_File MODIFY IsCensored bit NULL ALTER TABLE AniDB_File MODIFY IsDeprecated bit not null ALTER TABLE AniDB_File MODIFY IsChaptered bit not null

And only the latter failed.

Doing select distinct IsChaptered from AniDB_File

Returned: 0 -1 1

So, is the same problem with the -1.

I just ran: update AniDB_File set IsChaptered=0 where IsChaptered=-1

Now, in my case, my database is ancient too, and the poor thing has been migrated several times (I even once moved from Windows to Linux and then back). Anyway, it seems that rows might have a -1 value for some users, as @t0815 mentions, a simple update before the schema patches will do the trick.

To note in my case, out of 33508 rows, 2309 were affected.

revam commented 1 year ago

I forgot to fix the migration...

revam commented 1 year ago

Fixed it for future users in a daily update.

Might do a hot-fix for stable soon-ish, but anyone reading this in the future that encountered the error either;

  1. needs to handle it yourself, @danmarce provided some useful info for you if you feel courageous enough to do it, or

  2. needs to restore their database back to an known good state using the automatic backup Shoko took before attempting the upgrade, then either; a. update to the next stable version after 4.2.2, or b. stay on 4.1.2 until the next stable after 4.2.2 is out, or c. upgrade to a daily version of shoko server.