+- Kodi Community Forum (https://forum.kodi.tv)
+-- Forum: Support (https://forum.kodi.tv/forumdisplay.php?fid=33)
+--- Forum: General Support (https://forum.kodi.tv/forumdisplay.php?fid=111)
+---- Forum: Windows (https://forum.kodi.tv/forumdisplay.php?fid=59)
+---- Thread: Broken Potential 19.1 DB incompatibility with MySQL (/showthread.php?tid=363439)
Potential 19.1 DB incompatibility with MySQL - zebraitis - 2021-07-06
tl/dr - Something is causing MySQL to lock up after scanning a large amount of content when doing so in a fresh installation of Kodi 19.1
Rebooting the server restarts MySQL (verified w/ Heidi SQL), but as soon as I start a remote Kodi it locks MySQL up again.
The troubleshooting thread below documents the challenge, and dropping back to 18.9 eliminates the problem with MySQL locking up. Of course that likely means that there is a DB problem in 19.1
---------------
WHAT CHANGED:
Everything was working great on my home server (win 10 64 box) that was running MySQL for a shared DB. Then I had this idea that I would try try the new Windows 11. Long story short, decided to fall back to 10, Which I could do...
But since I loaded Win11, suddenly the only thing that my machine wanted to do was upgrade to Win11. And the only option that Microsoft said would work to undo this being a "developer" machine would be to wipe the system completely.
So, reload Win10, which meant that I had to reload MySQL, and everything else. Curiosity indeed killed this cat.
THE NIGHTMARE BEGINS:
OK, I'll skip a lot of frustration of reloading win10 and all the stuff, and get right to some of the key parts:
* Since my drives were created in a different version of Win10, the owner was not me. Had to resolve that.
* And I had to reshare my drives. I gave access to "everyone" and ensure that there were read/write rights.
* I tried several different versions of MySQL (5.6.x, 5.7.x), but the outcome seemed to be the same: after scanning "a lot" into the mainvideo119 DB (ex: 400 complete TV series ), if I went over to SYSTEM / LIBRARY / VIDEOS (in a confluence skin) to start scanning movies... then instead of seeing my sources it would just show the confluence background. End of story.
In short, Kodi locks up, and MySQL locks up. and I tried a second Kodi remote machine, same behavior.
If I wipe out MySQL, and reload, everything works as expected until "lots" has been scanned in and then... locked up again.
I tried a lot of different versions of MySQL, I'm running 19.1+ (a "fix" version for the win10 Kodi boot screen lock-up issue), and as I said, something is locking up video119 within MySQL. I tried various scrapers, and finally just tried to use the Python based ones for 19 ... Ultimately the MySQL behavior is the same for all of 'em. As my data had been exported separately before, al of the scrapers were reading the contents of the saved NFO files.
NEXT STEPS:
Yes, I know the next statement is usually: "we need a log file". But I'm not sure what to do to find out what and how I can see what's happening in MySQL. So I'm asking for guidance on that.
Plus, I'm hoping that someone will recognize this behavior and say : Yeah, that's known, it's ... _________
It's been about 5 days of this frustration. my next steps is to leave MySQL and try MariaDB and see if the behavior is the same there.
I could really use some help. Thanks.
RE: Something is locking up MySQL - Klojum - 2021-07-06
(2021-07-06, 04:39)zebraitis Wrote: Plus, I'm hoping that someone will recognize this behavior and say : Yeah, that's known, it's ... _________
[rant]The last time I used Windows for a MySQL server, I noticed that sometimes a MySQL version would work and sometimes it wouldn't. Why? No clue. That was over 10 years ago. Apparently nothing has changed. I've been using Linux servers ever since, first MySQL 5.5 and later 5.7, both have been running rock solid. Messing with a "production" server (the whole family relies on the Kodi setup?) by going straight to Windows 11, an OS that is not even official yet, is not the greatest idea, as long as you have proper backups of "everything". Disk images of your (previous) OS, Kodi configuration files and full SQL dumps of your databases.[/rant]
That said, MySQL 5.7 would be my go-to solution, as it should give you the least headaches in terms of 'Kodi compatibility'. One other solution is to run the whole database server on the separate device: a Raspberry 3B+ and (preferably) 4B are cheap stable Linux server solutions. The 4B has PC-like performance with MySQL/MariaDB when connected to gigabit. That way you can mess with Windows 11 or other OSes a whole lot safer. Backups are still necessary, but you get the point.
One other option is to leave MySQL alone for a moment, run everything off a default SQLite local database, (hopefully that works), and do a full Video Library Export with separate files. Then change to a MySQL/MariaDB/HeidiDB database, and rescrape your media collection using the exported .nfo files.
(2021-07-06, 04:39)zebraitis Wrote: tl/dr - Something is causing MySQL to lock up after scanning a large amount of content.
Rebooting the server allows my to access MySQL via Heidi SQL, but as soon as I start a remote Kodi it locks MySQL up again.
Check on MySQL's own log files for any indications of foul play. Databases don't just lock up. It's the (server) application that does that. Lock up.. Do you get any data stored? Is there even a connection to your database server? Check your firewall settings, disable it to double-check.
Also check on MySQL's external access, which is denied by default. Things like the bind address and so on. Double all buffer settings in the my.ini/my.cnf settings file.
The database sizes of basically all Kodi users worldwide are still "kid's stuff" when looking at the sheer number of data records. A database server's first drop of sweat should be when there are a million table records or more. I'm doing roughly half the number of tv shows (and purely guessing the same half number of movies) compared to you, the only database table that has a 'serious' number of records is the table linking actors to movies and tvshows. Mine is at roughly 189,000 records, all because every actor/director gets linked to every single episode of a TV show. For example, NCIS currently has some 334 episodes. That series alone will eat up some 6,000+ of those records.
(2021-07-06, 04:39)zebraitis Wrote: But since I loaded Win11, suddenly the only thing that my machine wanted to do was upgrade to Win11. And the only option that Microsoft said would work to undo this being a "developer" machine would be to wipe the system completely.
Yeah... I'm glad I ditched Windows a long time ago.
RE: Something is locking up MySQL - zebraitis - 2021-07-06
Additional data:
Here is the log file when I try to reboot my Kodi machine when I already know MySQL is running... then I try to go to Setting / Livrary / Videos, and ... nothing
https://paste.kodi.tv/laqedoqoki.kodi
Now at this point, on the server, if I tell the service NET STOP MYSQL , well I may as well go off to make a sandwich, because it won't stop. it's stuck, or locked, or whatever you call a DB when it's taking a shit on your data.
After a L O N G time, it finally stopped with the message:
Quote:C:\Windows\system32>net stop mysqlI'll look into troubleshooting a 1067 error tomorrow using https://www.sqlmvp.org/fix-sql-server-error-1067/
The MySQL service is stopping........................................................................................................................................................................................................................................................................................................................................................................
A system error has occurred.
System error 1067 has occurred.
The process terminated unexpectedly.
The MySQL service was stopped successfully.
Unless I drop the DB tonight.. which I prolly will.
RE: Something is locking up MySQL - zebraitis - 2021-07-06
(2021-07-06, 05:52)Klojum Wrote:Yes. Currently running (when it is) is 5.7.33(2021-07-06, 04:39)zebraitis Wrote: Plus, I'm hoping that someone will recognize this behavior and say : Yeah, that's known, it's ... _________
That said, MySQL 5.7 would be my go-to solution
Quote:... do a full Video Library Export with separate files. Then change to a MySQL/MariaDB/HeidiDB database, and rescrape your media collection using the exported .nfo files.
Yes, I do that on a regular basis, and I have my export already out there with separate NFO files. plus I have a recent 40TB backup as well... so at least THAT data is safe.
And that data IS what I am scraping. and when it's there, it somehow causes MySQL to hang.
Quote:Check on MySQL's own log files for any indications of foul play. Databases don't just lock up. It's the (server) application that does that. Lock up..Not sure where those are, but willing to look for them.
Quote:Do you get any data stored?
Video119 is 224MB after scanning TV shows... so, yeah.
It's like I can write like crazy to it, but when it comes time to read, then the nightmare begins.
Quote:Is there even a connection to your database server? Check your firewall settings, disable it to double-check.Port 3306 is doing its thing.
Quote:Also check on MySQL's external access, which is denied by default. Things like the bind address and so on. Double all buffer settings in the my.ini/my.cnf settings file.Not quite sure how to do that... and since it's late, I need to restart Video119, restart MySQL and set up the profile for my wife's workout video.
Surprisingly, if I don't scan in much, it (mysql) seems to work fine. (... wait a minute... now THAT sound like a buffer thing!)
I'll check back tomorrow for tips, and try more.
RE: Something is locking up MySQL - Klojum - 2021-07-06
(2021-07-06, 06:37)zebraitis Wrote: Not quite sure how to do that.
If the database is storing data, then the remarks for firewall and bind address etcetera are not applicable as the database connection works.
You should see a reference in the MySQL settings file where the log file is stored. See https://dev.mysql.com/doc/refman/8.0/en/windows-troubleshooting.html .
RE: Something is locking up MySQL - zebraitis - 2021-07-06
Well, it's the next day. And I did find the my.ini file in C:\ProgramData\MySQL\MySQL Server 5.7
The contents are: https://paste.kodi.tv/iwayixaviw
Sadly, I don't know what I should increase to allow for additional memory for buffers... or if I really need to do so based on what I see there.
If someone can offer a review and suggestion, I would appreciate it.
BTW, this is what HeidiSQL sees when I export the MySQL config: https://paste.kodi.tv/aqojiyiyoy
The last line is me applying a fix that should have allowed for a larger field size required for using a pre-python scraper.
(please note: my video DB had almost nothing in it right now, based on making sure that my wife can do her morning workout)
RE: Something is locking up MySQL - zebraitis - 2021-07-06
I'm trying a new tactic...
Wiped out MySQL, Wiped out HeidiSQL, wiper out all subdirectories that may contain a trace of any MySQL related info.
Since I want to increase the resources available to MySQL, this time instead of installing as Server only / Development machine, I'm installing as Server only / Server computer. (That's not the most intensive. That is reserved to dedicated server, and that uses all memory resources available.)
So that should automatically throw a lot more resources at the problem. Then I will scan only one 10TB drive at a time rather that a massive scan of 40TB.
I fully plan to be disappointed but what the hell.
RE: Something is locking up MySQL - zebraitis - 2021-07-07
2000+ videos into video119 and still going...
4000+ videos into video119 and still going...
AND... at about halfway through the third 10TB disk, it crapped out. We were watching a video and when it was over, we went to look at the recently added items, and it locked up. mainvideo.119 was 73.9 MB in size.
Knowing the behavior, I restated the server (to the restart MySQL) and restarted my Kodi Machine. First thing I did was start Debugging so I could get some logs.
This log is from the restart... starting debugging, and selecting reboot to restart the Kodi machine (my plan was to restart so that I could get a full new clean debug). On selecting reboot, it locked up (memory quit displaying change from logging), likely borked the MySQL DB service, and stayed on the power menu. I had to [ctrl][alt][del] to kill Kodi:
https://paste.kodi.tv/sowokonowe.kodi
This log is from me restarting Kodi and seeing if I could connect. I got to the splash screen, and waited to load the profile menu, but it never started. The log said that it can't find MySQL.
https://paste.kodi.tv/usejifosif.kodi
This log is after I restarted the server (to restart MySQL) and restarted my Kodi Machine. I went in, selected movies, and... the system locked up. Nothing but the stock confluence background.
https://paste.kodi.tv/wezokiyefo.kodi
so: ManiVideo119 is again toast. I'll be dropping it via HeidiSQL, and put in just a few thing for my wife. All the other profiles DB's will remain. Smaller size scanned contents do not seemed to be a problem
Observation: Kodi acted like it should, after the first 10TB, and after the second. It was after getting half of the third drive's contents scanned that MySQL failed.
I hope that there is something in the log files that will help identify the MySQL issue.
RE: Something is locking up MySQL - Klojum - 2021-07-07
(2021-07-07, 01:41)zebraitis Wrote: AND... at about halfway through the third 10TB disk, it crapped out.
I didn't want to reply earlier, coz I might jinx it. But sadly it still did.
(2021-07-07, 01:41)zebraitis Wrote: The log said that it can't find MySQL.
Code:
2021-07-06 21:53:23.681 T:6728 DEBUG <general>: [WS-Discovery]: The initial servers search has completed successfully with 2 server(s) found:
2021-07-06 21:53:23.681 T:6728 DEBUG <general>: 192.168.15.14
2021-07-06 21:53:23.681 T:6728 DEBUG <general>: 127.0.0.1
2021-07-06 21:54:18.773 T:7652 INFO <general>: MYSQL server has gone. Will try 4 more attempt(s) to reconnect.
2021-07-06 21:54:18.774 T:7652 DEBUG <general>: dbiplus::MysqlDatabase::connect replacing configured host 192.168.15.14 with resolved host 192.168.15.14
2021-07-06 21:54:36.104 T:6728 DEBUG <general>: [WS-Discovery]: BYE packet received: device address = '192.168.15.14:55165'
2021-07-06 21:54:36.104 T:6728 DEBUG <general>: [WS-Discovery]: IP '192.168.15.14' has been removed from the server list.
2021-07-06 21:54:39.848 T:7652 ERROR <general>: Unable to open database: MainMusic82 [2002](Can't connect to MySQL server on '192.168.15.14' (10060))It's weird. Apparently the SQL server seemed to close itself or was closed by something, as it did so gracefully and notifying connected devices. Kodi could also have a blackout, but it claims to recieve a 'BYE packet'.
I can't really explain the Windows/MySQL server breakage. If you have a old/spare PC lying around (or a Raspberry Pi 3/4), how about trying a Linux setup running MySQL (PC) or MariaDB (RPi) ?
RE: Something is locking up MySQL - zebraitis - 2021-07-08
(2021-07-07, 23:36)Klojum Wrote:(2021-07-07, 01:41)zebraitis Wrote:It's weird.
Yep. Very weird.
I have spent nearly a week trying to understand this specific weird.
Since I can't find a clue to fix this, I have chosen a dramatic next step:
* Created a backup of my c drive
* Disconnected that backup drive
* Wiped out all partitions on remaining drives
* Reloaded Win10, and applied MS updates
* ONLY loading Buffalo NAS Navigator 2 (to connect to my NAS) and SyncBackFree (to restore video / audio / data from NAS)
My plan is to load NO other software of any type so that I have as plain a system as possible.
Following vid and audio data restoration of content to my drives (which may take a day or 2 over gig ethernet), I will load MySQL 5.7.33 and try again.
I am still open to other opinions and suggestions on what may have caused the issue, or if another release version of MySQL should be considered. (As I am not familiar w/ Linux, I don't want to go that path at this time.)
RE: Something is locking up MySQL - Klojum - 2021-07-08
Back to basics is the best approach for troubleshooting.
One other possibility: install LibreELEC onto a dependable PC box, and install the MariaDB server add-on. That's the simplest method I can think off right now.
The Linux PC solution can be a very simple one, once you know how of course. Perhaps I can try doing a tutorial on that over the weekend (no big promises though, my agenda keeps filling up).
RE: Something is locking up MySQL - zebraitis - 2021-07-09
Well, this test didn't end well:
After reloading windows, recopying my TV shows, loading a new 19.1 nightly copy of Kodi on that same local machine that has has my data (TV Shows) and the MySQL DB... It looked like it was doing well. (an all-in-one box, if you will)
I thought a local Kodi on that same box would scan well, and I could be sure that this wasn't somehow related to a server or client going into sleep mode.
The scan of the TV shows completed. I chose to Exit KODI, and it hung on the exit menu. Opening HeidiSQL, I could see the DB's, but when I clicked on Video119, it was unresponsive.
MySQL had crashed again. Something is causing the DB to get corrupted in some way.
In this crashed state, I tried restarting Kodi again, and not surprisingly it hung trying to connect to MySQL: https://paste.kodi.tv/puvirunado.kodi
-------------------------
On rebooting the machine, running HeidiSQL, I can now see the Video119 DB and the contents of the DB (it would not open in the previous crashed state)
On restarting Kodi, i quickly start Log debugging, and shutdown so I can get a new log. https://paste.kodi.tv/wicaqijace.kodi
On restarting kodi it again hangs: https://paste.kodi.tv/ikehomawuz.kodi
It appears that just the act of shutting down Kodi AGAIN crashes MySQL and (of course) that prevented Kodi from successfully loading
-------------------------
This is very frustrating
-------------------------
For my own sanity, I will remove kodi 19.1+ from this machine and try Kodi 18.9 -- of course, I will need to rescan as the DB Versions are different https://kodi.wiki/view/Databases , but that will show me if the MySQL server version is running well.
RE: Something is locking up MySQL - Klojum - 2021-07-09
Are there .nfo files along your movies/tvshows, created by some other tool?
RE: Something is locking up MySQL - zebraitis - 2021-07-09
(2021-07-09, 11:21)Klojum Wrote: Are there .nfo files along your movies/tvshows, created by some other tool?There are NFO files. They were only created via Kodi export. No other tools to create or modify NFOs were used.
RE: Something is locking up MySQL - zebraitis - 2021-07-09
18.9 is loaded.
Kept only the XML files that would populate the sources, passwords.
18.9 is scanning at a HUGELY faster pace. (same machine, same windows, same MySQL, etc). I cannot begin to tell you the differ impressive difference in speed. 19.1 took easily 12+ hours to scan 400 shows. In less than 10 minutes, 18.9 has scanned over 100.
I'm not saying that there is a problem with 19.x Matrix ... yeah... maybe I am.
In the time it took me to write that above, it is up to 140 shows.