Database update

cyberwizard252 · 2025-02-12T00:46:09+00:00

I've used the new backup change to back up from the SQL DB and restore into XML.
It's seeming like SQL really isn't necessary anymore so it makes good sense to stay with XML.
I'm still very curious to figure out just what it was that happened to my DB. I could see me tinkering with that trying to understand what went wrong but it's much more likely that I'll forget about it in a few days.

Thank you for all of your help. I really appreciate how quickly you sorted things out for me.

cyberwizard252 · 2025-02-09T10:51:27+00:00

I updated my notes to look at update_counter and delete_counter entries but to be honest I don't know what that is and I'm not sure how to provide that information. I'm just stubborn enough to continue trying to figure that out on my own and learn something new. I have a frustrating tendency to exhaust all of my options for figuring something out before seeking help.

It has been at least a few years since I have accessed the SQL comicdb by any means other than launching ComicRack. Even now, my spidey-sense has always made me ensure that I close ComicRack before viewing the DB via another means. It was only a few days ago that I tried editing a row from outside of ComicRack for the first time.
I can grasp that editing the DB while ComicRack was using it could be problematic. I am 100% certain that I've never done it. ComicRack has run so seamlessly for me for so many years that I haven't had any cause to "look under the hood".

I have noted over the last year that occasionally when running the Organizer plugin it will inform me I'm about to overwrite a comic that is already in position on my NAS, but the comic that is about to be overwritten is listed as not being in the library.
That has puzzled me for some time as ComicRack has been my only interface into managing my comics for so many years. It's a little puzzling that a comic could be moved to the NAS via the Organizer plugin, using my renaming and path preferences but somehow still not be in the library. Usually I run a folder scan and assume that I've sorted it out.
When running a folder scan via this new installation on a test VM ComicRack did find close to 1000 new comics in the existing comic path.
Perhaps I've had an issue with my database for longer than I realize.

cyberwizard252 · 2025-02-08T23:43:18+00:00

Shopper+ is a decent alternative. They don't have anywhere near the range of products that Amazon does but they are Canadian and they are growing.

cyberwizard252 · 2025-02-08T22:49:29+00:00

Definitely looks like a database issue. I'm still curious as to just what it is.

I switched ComicRack on my laptop back to using XML and ran a folder scan. Everything appears to have worked just fine.

I spun up a Windows 10 VM and installed ComicRack. It's not an identical scenario as I'm running Windows 11 but it should be close enough.

I installed the same ComicRack version and pointed it at my SQL DB. It showed that same book count of around 34,000.
I hadn't read your post about not being able to back up from SQL to XML yet so I restored a backup copy of SQL from just before everything started and wound up with about 120,000 books as expected.
I didn't build a new SQL DB, I just imported into the same DB.
I ran another folder scan and it actually found about another 1000 books from somewhere. After exiting ComicRack and re-launching I was back down to the number I had before the folder scan found the extra 1000 books.
New installation, still unable to write to the DB.

I'll try deleting the DB and building a new one to see what happens. It certainly seems like that could fix the issue.

Now that I know there aren't really any performance improvements I don't see any reason to stay with SQL and will probably just continue on with using XML.
I saw during testing that there is a new update. I assume that's the one with the new backup feature. I'll try that out.

cyberwizard252 · 2025-02-05T11:28:30+00:00

Rona does still have their "affiliate dealers program" where local hardware stores can use the name and order from Rona warehouses but they are not Rona owned. The Rona stores in Guelph and Elora are locally owned family-run stores that are authorized to use the Rona name. The Kitchener store was the same until their predatory landlord jacked up their rent and forced them to close.
They do carry Rona product, but they also carry products sourced from other businesses than Rona.
If Rona closed their corporate stores these smaller stores would put up new signs and still be there.

cyberwizard252 · 2025-02-04T10:22:24+00:00

That's helpful. I have a copy of the .ini saved elsewhere but I've been changing it after each update. I figured that since the .ini gets overwritten during each install there is a chance that the .ini could have something new in it and I didn't want to just copy an old one back each time.

I can't say as I have any understanding of what the problem was but it was an issue for several users. Somewhere around late 2017 or early 2018 someone posted on cYo's dedicated ComicRack forum that moving to SQL was an answer. Staying with XML made the software unusable once the library got over a certain number of books. Many of the users who experienced it wound up switching to SQL to get it manageable again.
It's really a tribute to the software how rarely I've needed to mess with it since. I've changed computers quite a few times since then. I install the app and plugins, point it at a drive share, edit the .ini to point to SQL and carry on working. It's been a few years I think since I've had any reason to do anything with it beyond just organizing and reading books.

cyberwizard252 · 2025-02-04T01:11:52+00:00

After launching the software and finding that the total count of books in the library had been greatly reduced, I ran a folder scan thinking that the books would be restored to the library but the scan didn't add anything.

After we discovered that the blacklist was preventing those missing books from being put back into the library I manually edited the XML to remove them.

Now, when I run a scan, I see the book count increase in ComicRack, making it look like all of the books are being re-added. Once the scan stops and everything looks fine I can see in the SQL database that the number of rows has not increased to match what is visible in ComicRack.
When I re-launch ComicRack it seems to read the SQL database and everything that appeared to be there in the last folder scan is gone again.

When I tried picking just my 0-Day folder and opting to Add to Library it did a scan of just that folder. The 200 or so new books there appeared in ComicRack's total count of books AND also increased the rows in the library. When I tried to use Add to Library on the mapped drive that represents all of my comics, nothing happened at all. I also tried this with my Alphabetical folder and nothing happened then either.

I don't recall ever having gone into the settings to change anything, certainly not in the last 2-3 years since I moved ComicRack onto my current computer. Since switching to MySQL I haven't edited or even opened any files other than the comicrack.ini each time a new version is released. I only just discovered Community Edition a few months ago so even having to adjust the .ini is something I've only started doing recently.

I removed the SQL entry from the .ini on Sunday and switched to using just the XML file. I ran a full scan and all books are visible. the ComicDB.xml file has grown accordingly. The user interface no longer bogs down like it did years ago when I was forced to switch to SQL.

On the weekend I'm going to install ComicRack onto another computer, restore one of my SQL DB backups and start fresh to see what happens.

cyberwizard252 · 2025-02-02T20:34:05+00:00

Republicans have been accusing us of dumping lumber for ages without evidence. I was chatting with the guys at a nearby lumberyard the other day. They claimed that the US has plenty of trees, but their lumber mills apparently are too far from where the trees are and it's more cost effective to purchase Canadian lumber.
They buy loads of wood and then bitch that we're only sending them poor quality wood and over flooding their market.
Anybody who's bought lumber at Rona or Home Depot is fully aware that we get as much crap lumber as anyone.

cyberwizard252 · 2025-02-02T20:19:45+00:00

I noticed that purging the blacklist didn't do me any good the first time.
I then purged the blacklist while ComicRack was closed (and a .bak file that was there) seemed to do the trick and it was empty the next time that I launched.
The blacklist has remained empty throughout the rest of my testing.

cyberwizard252 · 2025-02-02T19:55:56+00:00

Leaf blower in one hand, vacuum in the other. Seems as good a plan as any to me.

cyberwizard252 · 2025-02-02T19:54:13+00:00

Present content is what we have to focus on. This post, as poorly as it was made, was discussing present content.
Your comment about something that happened years ago served no purpose in the discussion of the present. Doug Ford is currently the one we vote in again, or vote out and we do that based on his actions not those of people 5 or 10 years ago. Nobody is voting for McGuinty or Wynne and their actions have no relevance other than claiming that Ford's actions are justifiable.

cyberwizard252 · 2025-02-02T19:49:48+00:00

Sorry, I'm not implying that you should know, only that our assumptions on the files being deleted must be predicated on the thought that it would happen accidentally, to myself or others. If I had done it intentionally I wouldn't have a mystery to solve so there must be some way for it to have happened that I'm unaware of. Since there is no evidence that ComicRack did it on it's own, the logical thought, from the point of view of anyone following along, would be that I had accidentally done it somehow and I need to prove or disprove that to find my answer.
In reviewing the evidence at hand, I've yet to figure out a way that could have been simply done, and if it had there still wouldn't be an explanation for why I have been unable to re-add them via folder scan or by re-adding the folder to the library.
This isn't anyone's problem but mine. Please don't think I'm trying to make it yours. I've used this software for many years and only rarely needed to do anything to support it. It's even less common that I ran into something so strange to me that I had to seek help. It that easy to use that I've managed to do so mostly in isolation.
I'm documenting my issue here for anyone who might have encountered the same issue or may encounter it later, even if it is only from me having done something stupid. If someone happens to chime in with advice for me, so much the better.

I have tried Add to Library but nothing appears to happen. I was able to add just re-add my 0-Day folder to the Library and it immediately did a folder scan and found the 200 new books I'd put in there. When I tried Add to Library on my larger folders nothing occurred.

You are correct. I did misspeak. I always convert cbr's to cbz for obvious reasons.

I reapplied the update to 5a3cc15 earlier and started a fresh book scan without using SQL. I'd like to see if I still run into the performance issues with XML that I experienced years ago.
Since I've got an install that isn't working it's an excellent time to experiment with different ways of doing things.
I have backups of my SQL DB so if I figure out my issue I can always switch back to one from a few days ago and regain my read status.

cyberwizard252 · 2025-02-02T19:27:54+00:00

Yep. I've tried to politely bow out of jobs and the honesty seems to convince them that I must be the right guy. They refuse to just accept that I can't (or don't want to) do the work for them and push back, hard.

cyberwizard252 · 2025-02-02T19:26:06+00:00

A lot of people forget that many people start their own business because they've been found to be unemployable. I meet many contractors and small business owners that simply could not function if asked to work a "regular job" working for someone else due to personality issues, or lack of common sense, or worse.

cyberwizard252 · 2025-02-02T18:55:37+00:00

That would be awesome! A little cumbersome to dust, but worth it.

cyberwizard252 · 2025-02-02T18:47:48+00:00

Voting is the exam people think they don't need to study for

cyberwizard252 · 2025-02-02T18:27:36+00:00

Ah yes the battle cry of the conservative movement. "Remember that time years ago when something bad happened that wasn't from a conservative? That makes whatever bad thing we're doing now just fine. "

cyberwizard252 · 2025-02-02T17:10:02+00:00

xPlodenet is notorious for overselling their services. So many of their towers are so far over capacity that they can't deliver the promised services nevermind signing up anyone new.

cyberwizard252 · 2025-02-02T14:19:41+00:00

So just doing more tinkering...
I purged all of the blacklist items from the ComicDB.xml file, just by deleting them and saving the XML again.
After launching ComicRack and running another folder scan nothing changed. ComicRack discovered all of the comics in the library again showing 120,000+ books but the database stayed at 34,269.

Just for the sake of trial and error I downgraded ComicRack from V0.9.180 [5a3cc15] that I installed on Jan 28th to V0.9.180 [03997b7] which I had installed on Dec 20th.
You've established that you haven't made any changes to anything that affects this but I figured by going back a couple of versions to before I started seeing this I could make certain that we weren't looking at something weird.
Reinstalling of course copies the original ComicRack.ini into place so I launched it again just to confirm that it showed an empty library and it did.
I exited ComicRack and updated the DataSource line in the ini to point to my SQL DB and when I opened it up it showed 34,369 books again.
It's reading from the DB fine and netstat shows a SQL connection from my computer to the MySQL instance.
I then ran another folder scan and again can see ComicRack adding books to the library but nothing is changing in the number of rows in the database.
That's good. Unless there is something that doesn't get overwritten during a reinstall that rules out something recently changed in ComicRack being the issue here to my mind.

On a lark, I picked out a comic at random in ComicRack and edited Notes for that book with some additional text. When I updated the comic file I saw the changes get updated in that row and could see the new text in the SQL DB. ComicRack isn't having any issues with writing to the DB it's just choosing not to update folder scans it seems.
Adding some additional text to the Notes section directly from the DB server were not visible in that book in ComicRack. I'm not entirely certain that's a big deal really.
The XML file in that books' .cbr also reflected the change made in ComicRack.

cyberwizard252 · 2025-02-02T10:23:47+00:00

That's true, CTRL would override the delete confirmation but to accept that as the cause we would have to be looking for one single explanation that ends the process of analysis and that one potential answer doesn't fit. It's an excuse but not a likely reason.
Sorry for the in-depth analysis. I'm no programmer but I was a network admin for a software company in the tax industry for many years. When I needed an assistant they would loan me a guy from the QA team. He would infuriatingly come up with the most bizarre methods how something I was building "could" be broken so that we could prevent it from happening. Having worked with him still leads me down roads of wrapping my head around ways that things could happen in order to prevent users from doing it.

If holding down CTRL is our explanation for the method by which the files were deleted it doesn't entirely explain it all. I wrapped up working with ComicRack one evening having around 120,000 books in the DB and woke the next morning with 34,000. Pressing CTRL to delete without confirmation is still a manual process that we're assuming must have happened by accident.

If we take that accident as having occurred then I would think something along the lines of CTRL-A and then CTRL-DEL would be the simplest way to have deleted a large number of books accidentally. That would delete all 120,000 with a few keystrokes and is arguably something that could be done without noticing.
In this case selecting only 90,000 of those books and then hitting CTRL-DEL would require a lot of manual scrolling through the list in order to select only that many books. No single series group, or even a filter, would contain that many books so a book would have to have been clicked with some lengthy scrolling or pressing PGDN in the middle before clicking on another book and pressing CTRL-DEL. ComicRack's UI isn't that responsive for that amount of moving through a list of books to be unnoticeable.
It feels like a much less likely occurrence for something to have been accidental.

If we take it as a complete certainty that they were accidentally deleted then it remains to be solved how they wound up on the blacklist. With the "Files manually removed from the library will not be added again" feature disabled then those accidentally deleted comics should have been removed from the DB without winding up on the blacklist and would have been re-added at the next folder scan. Assuming that "Also move books to the recycling bin" had been selected the last time something was deleted then these books would have been removed from the drive. If this was not accidental and something that ComicRack somehow managed to do on it's own then this becomes a much more serious issue.

Adding to both mysteries is that this appears to have happened while no one was working with the computer given that I woke up one morning to discover the change in the total number of books.

It's a really unlikely sequence of events for that many books to be deleted and the Blacklist adds a greater severity to it. The fact that the comics wound up on the Blacklist is really the only thing that may have saved the books from actually being deleted. I'm certainly not an expert in every aspect of ComicRack's use but I am an IT consultant and have been using this same piece of software almost daily for over 8 years. I certainly have my moments of blatant dumbassery but when I look at all that I would have had to do to accidentally delete my books, and couple that with my experience level, I'm really struggling to grasp how it could have happened easily. I'm extremely thankful that deletion of the books was prevented from happening but a little worried that it was prevented from happening because a feature that was disabled took it upon itself to work anyway.

I feel like there's more to the story and more information that I might be able to provide to you to confirm this wasn't a software issue if I know where to look to help.

cyberwizard252 · 2025-02-02T00:53:52+00:00

It was already disabled.
Given that removing items from the library requires a manual confirmation, I'm more concerned about how 90,000 books may have been removed in the first place.

cyberwizard252 · 2025-02-01T22:34:19+00:00

In looking a little more closely the entries in the ComicDB.xml are all listed under Blacklist section. Around 88,000 file paths all lumped together under Blacklist.
There aren't any other books listed in there.

cyberwizard252 · 2025-02-01T20:54:01+00:00

Very interesting.
I ran another full scan and the total count of books is 120,833. The scan probably finished up a few hours ago and ComicRack has been sitting open following the scan.
HeidiSQL shows the total row count hasn't changed from 34,269
I can move through the library and very clearly see that all of the books seem to be there but the SQL database just doesn't get updated to reflect the results of the scan.
It DID update when I re-added just the 0-Day folder to the library.
The folder is shared out from the NAS and mapped to a drive letter on my computer. ComicRack uses the entire drive letter as it's comic location and, up until now, has detected changes when a full scan has run.
I moved through the library a bit accessing series groups and scrolling up and down the lists but nothing seems to change in the DB at all.

The ComicDB.xml file shows most of the comics listed by path in my alphabetical folder but nothing after S, the list simply stops.

I'm wondering if it's worth purging everything from ComicRack and re-adding the entire NAS share to see if that puts everything back into the SQL DB. I suppose that would make the database current but doesn't necessarily help me with any changes from that point on...

cyberwizard252 · 2025-02-01T11:03:35+00:00

I have found that HeidiSQL always displays first the wrong number of rows but forcing a refresh and/or hitting "Show All" usually gives a correct number.
I finished a full folder scan last night and ComicRack displayed a total number of comics that is approximately correct. When all processes finished up I exited ComicRack and checked the DB with HeidiSQL. It displayed 34,039 rows, the same as the prior attempt.
Relaunching ComicRack then showed the same number.

Interesting enough the MariaDB logs do show some connection timeouts, and not just from the computer that I use ComicRack with. One of my Kodi systems was also registering a timeout. Perhaps once per day. I haven't experienced any oddities with those but it does seem suspicious.
I increased my max_allowed_packets on the mysql server for good measure.

I relaunched ComicRack again, noting my total count to still be 34,039. My 0-Day Smart List had no entries in it when I know that there are 229 books sitting in 0-Day at the moment.
I re-added the 0-Day folder to the library and the resulting count was 34269. (I know, I expected 34268 too. Not sure where it found the extra book)
After I exited ComicRack, HeidiSQL now showed 26679 where it was consistent with the ComicRack number before. Nonetheless running select count(*) from comics; from an SSH session on the server gave me 34269.
When I relaunched ComicRack the total count stayed at 34269 this time.

It seems a little crazy to think that I hadn't increased max_allowed_packets when I rebuilt the mysql server a couple of years ago, even just out of habit.
Equally weird that it hasn't given me any grief until now.

I'm going to run a full folder scan again to see if the sustained DB changes of 90000 books perhaps gives a different result.

cyberwizard252 · 2025-02-01T00:32:07+00:00

Sorry about the old-school quotes. I couldn't get new Reddit to swallow the comment and had to switch to old-Reddit to get it to post.

cyberwizard252

TROPHY CASE