Hey Peeps,

 

Somehow the parts table in my newznab db has become corrupt.

 

mysql> check table parts;
+---------------+-------+----------+--------------------------------------------------------+
| Table         | Op    | Msg_type | Msg_text                                               |
+---------------+-------+----------+--------------------------------------------------------+
| newznab.parts | check | warning  | Table is marked as crashed and last repair failed      |
| newznab.parts | check | warning  | 1 client is using or hasn't closed the table properly  |
| newznab.parts | check | warning  | Size of indexfile is: 12686375936      Should be: 1024 |
| newznab.parts | check | error    | Record-count is not ok; is 158580047   Should be: 0    |
| newznab.parts | check | warning  | Found 903140288 deleted space.   Should be 0           |
| newznab.parts | check | warning  | Found 10253352 deleted blocks       Should be: 0       |
| newznab.parts | check | warning  | Found 169124437 key parts. Should be: 0                |
| newznab.parts | check | error    | Corrupt                                                |
+---------------+-------+----------+--------------------------------------------------------+
8 rows in set (4 min 37.46 sec)

 

When I run

 

repair table parts;

 

it ends in

 

ERROR 2013 (HY000): Lost connection to MySQL server during query

 

I've googled it and done some of the suggestions but haven't been able to resolve it. I'm not seeing anything obvious in the mysql log.

 

Any suggestions?

 

Using Unraid 5.0-rc8a and the mysql plugin with simple features and newznab+.

 

 

Thanks for the reply. I did know about their IRC channel, just hadn't used it.

 

In the end I exported all the nzb's and re-imported into a new DB. The guys at NewzNab suggested running the optimize db scripts with the 'true' flag or trying

 

mysql> truncate table parts;

 

Once the new db is done I'll go back and try both options and see what happens, if anything they might come in handy in the future.

Converting from MyISAM to InnoDB is also going to yield some better results. I have had our db fail, but fortunately back it up regularly.

 

 

I've been having the same problem as well.  Decided to completely start over with my install into a new DB.

I am always on the irc channel for newznab as well, nickname is shat. Additionally you can find some of us power users in #phile

The optimize_db.php true will scan every table and optimize. It is best to truncate your parts table then optimize. I do a full tabs optimization every other day and a regular optimize every 6 hours or so.

 

You can always check what's going on by connecting to mysql and using

 

show processlist;

 

You're not the first to experience a corrupt database. Not will you be the last. My index database corrupted on December 9th I think. It happens. Keep backups.

 

Check your settings for number of headers to retrieve. It defaults to 20000 which should be sufficient. If you increase it you're likely hitting socket timeouts which could lead to stalled inserts. Blah blah blah.

 

It's a lot of work

What's the best way to backup the database?

Shat - how often do you truncate Parts?

What's the best way to backup the database?

 

If you just want to manually backup the newznab db:

mysqldump -u root -pYOURmysqlPASSWORD newznab | gzip > /path/to/backups/newznab_`date +%Y%m%d`.sql.gz

 

If you want to manually backup all your MySQL databases:

mysqldump -u root -pYOURmysqlPASSWORD --all-databases | gzip > /path/to/backups/mysql_`date +%Y%m%d`.sql.gz

 

If you want an automated daily backup of your newznab database created at 2:15 am, add this line to your cron file:

15 2 * * * root mysqldump -u root -pYOURmysqlPASSWORD newznab | gzip > /path/to/backups/newznab_`date +%Y%m%d`.sql.gz

 

Replace "YOURmysqlPASSWORD" & "/path/to/backups" with your info. Note there is no space between -p & your password. If you just use "-p " you will be prompted to enter your password... this will not work in cron.

 

Personally I do daily backups of my newznab & spotweb databases then weekly backups of all databases.

I continually have to start over again because of database corruptions. I think the longest i've gone without it happening is about a week. I have Tybio's script running as a cronjob hourly and the first I notice there is a problem is when I don't see any new releases in newznab. I then go through the logs and see parts can't be added or no releases created. I then run mysqlchecks and find that tables have crashed and can't be recovered.

 

I now have all of my settings in a text file ready for the next time it happens so I can just start again and quickly get back up and running.

 

To be honest I really thought NN was going to be a solution i'd set and forget but it has become a constant ball ache and i'm thinking of ditching the whole thing and going back to downloading releases from third-party sites.

I continually have to start over again because of database corruptions. I think the longest i've gone without it happening is about a week. I have Tybio's script running as a cronjob hourly and the first I notice there is a problem is when I don't see any new releases in newznab. I then go through the logs and see parts can't be added or no releases created. I then run mysqlchecks and find that tables have crashed and can't be recovered.

 

I now have all of my settings in a text file ready for the next time it happens so I can just start again and quickly get back up and running.

 

To be honest I really thought NN was going to be a solution i'd set and forget but it has become a constant ball ache and i'm thinking of ditching the whole thing and going back to downloading releases from third-party sites.

In my case I just exported all the nzb's and created a new db as I couldn't fix the corruption.

 

So far everything has been just fine. Originally I was using a modified version of one of the screen scripts (there was a forum link to it somewhere), it was supposed to keep the db in check. Since I stopped using that my db has been fine. I just use the default update and release php scripts and after each release run a mysqlcheck auto repair, now up to 80k + releases without issue. I also prefer the default scripts because it shows how many items are left in the que.