NineChime forum

Furry stuff, oekaki stuff, and other stuff.

You are not logged in.

#1 08-31-2008 22:17:54

rainbow
Member

sqlfail error + piccount/PIC_ID variable glitch

Wac, since the upgrade to Wax Poteto 5.7.1 almost three weeks ago, I've noticed that users have been getting sqlfail error messages when attempt or save, retouch or upload images in formats such as ShiPainter and PaintBBS. Here's a excerpt of the log:

Errors on logs 40 - 35:

sqlfail - shiget: cannot save image 140: (Duplicate entry '140' for key 2) - Topdoggie7
sqlfail - shiget: cannot save image 139: (Duplicate entry '139' for key 2) - Topdoggie7
sqlfail - shiget: cannot save image 137: (Duplicate entry '137' for key 2) - Topdoggie7
sqlfail - shiget: cannot save image 136: (Duplicate entry '136' for key 2) - Topdoggie7
sqlfail - shiget: cannot save image 135: (Duplicate entry '135' for key 2) - Topdoggie7
sqlfail - paintbbsget: cannot save image 134: (Duplicate entry '134' for key 2) - Waccoon

Errors on logs 25 - 19:

sqlfail - paintbbsget: cannot save image 137: (Duplicate entry '137' for key 2) - rainbow
sqlfail - paintbbsget: cannot save image 136: (Duplicate entry '136' for key 2) - rainbow
sqlfail - paintbbsget: cannot save image 135: (Duplicate entry '135' for key 2) - rainbow
sqlfail - paintbbsget: cannot save image 134: (Duplicate entry '134' for key 2) - rainbow
sqlfail - paintbbsget: cannot save image 133: (Duplicate entry '133' for key 2) - rainbow
sqlfail - upload: cannot save image 133.png: (Duplicate entry '133' for key 2) - rainbow
sqlfail - chibipaintget: cannot save image 133: (Duplicate entry '133' for key 2) - rainbow

Errors on logs 17 - 11:

sqlfail - chibipaintget: cannot save image 137: (Duplicate entry '137' for key 2) - test
sqlfail - chibipaintget: cannot save image 136: (Duplicate entry '136' for key 2) - test
sqlfail - chibipaintget: cannot save image 135: (Duplicate entry '135' for key 2) - test
sqlfail - chibipaintget: cannot save image 134: (Duplicate entry '134' for key 2) - test
sqlfail - chibipaintget: cannot save image 133: (Duplicate entry '133' for key 2) - test   
sqlfail - paintbbsget: cannot save image 134: (Duplicate entry '134' for key 2) - test
sqlfail - paintbbsget: cannot save image 133: (Duplicate entry '133' for key 2) - test

Errors on logs 8 - 1:

sqlfail - getoekakibbs: cannot save image 134: (Duplicate entry '134' for key 2) - test
sqlfail - paintbbsget: cannot save image 133: (Duplicate entry '133' for key 2) - test
sqlfail - paintbbsget: cannot save image 132: (Duplicate entry '132' for key 2) - test
sqlfail - paintbbsget: cannot save image 131: (Duplicate entry '131' for key 2) - test
sqlfail - paintbbsget: cannot save image 133: (Duplicate entry '133' for key 2) - test
sqlfail - paintbbsget: cannot save image 132: (Duplicate entry '132' for key 2) - test
sqlfail - paintbbsget: cannot save image 131: (Duplicate entry '131' for key 2) - test
sqlfail - paintbbsget: cannot save image 131: (Duplicate entry '131' for key 2) - test

(Notes: Left field, indicates category, middle field indicates comment, right field indicates username.)

Also, the sqlfail problem and animation issues since the upgrade are causing the piccount variable from the op_oekakidta table to erratically increase by more than one and have gone past the number of stored images as indicated in the Control Panel. Here's what the current output of the Diagnostics look like:

Current Picture Number: 310
Newest 5 pictures: 301 142 138 133 127 (Newest first)
Oldest 5 pictures: 134 135 136 137 139 (Oldest first)

The PIC_ID variable of 301 refers to the ID_2 variable of 1637.
The PIC_ID variable of 142 refers to the ID_2 variable of 1636.
The PIC_ID variable of 138 refers to the ID_2 variable of 1634.
The PIC_ID variable of 133 refers to the ID_2 variable of 1633.
The PIC_ID variable of 127 refers to the ID_2 variable of 1627.

Prior to the upgrade, the oekaki has been literally working perfectly with the picture count recycling to zero after reaching the 300 mark (with or without animation or uploading), but the PIC_ID count no longer resets to zero after the upgrade.

While I can get the PIC_ID count in sync with the ID_2 count by renaming the files and updating the mySQL database, how do I get these glitches fixed? sad


Update: I even tried inserting this snippet in the functions.php file:

// Reset picture counter to zero if it hits the maximum number of pictures
if ($picno > $cfg['pic_store']) {
    $result = mysql_query ("UPDATE {$OekakiPoteto_MemberPrefix}oekaki SET piccount = '0' WHERE usrname='{$OekakiU}'");
}

But the mySQL database completely ignored the commands.

Last edited by rainbow (09-01-2008 13:08:02)

Offline

#2 09-01-2008 16:11:25

Waccoon
Administrator

Re: sqlfail error + piccount/PIC_ID variable glitch

OK, don't panic.  I need to explain how the piccount works.

In old versions of the board, the piccount increased until it reached the maximum number of posts, and then reset to 0.  Old picture numbers were overwritten in ascending order, and the board would simply skip over archived pictures.  This causes a whole bunch of problems, because pictures could be deleted out of order.  That is, newer images would be overwritten while really old images stayed on the board almost forever.

As of 5.6.0, Wax now uses a sorting system based on dates.  The piccount number will count up infinitely, as it is just a database marker, and should never be reset to zero.  This is still not the best way of doing it, but it is more compatible with the old board system while solving the overwriting problem.

The updater is supposed to set it to the maximum value stored in the database, but since you had trouble with the updater, that is probably the issue.  Download this file and run it, and that will reset the picture counter.

If you're still having issues after the picture counter has been fixed, then we'll have to go from there.  However, "key 2" is the picture number set with piccount, so I'm pretty sure resetting the picture counter will fix this.

Offline

#3 09-01-2008 20:03:31

rainbow
Member

Re: sqlfail error + piccount/PIC_ID variable glitch

Waccoon wrote:

OK, don't panic.  I need to explain how the piccount works.

In old versions of the board, the piccount increased until it reached the maximum number of posts, and then reset to 0.  Old picture numbers were overwritten in ascending order, and the board would simply skip over archived pictures.  This causes a whole bunch of problems, because pictures could be deleted out of order.  That is, newer images would be overwritten while really old images stayed on the board almost forever.

As of 5.6.0, Wax now uses a sorting system based on dates.  The piccount number will count up infinitely, as it is just a database marker, and should never be reset to zero.  This is still not the best way of doing it, but it is more compatible with the old board system while solving the overwriting problem.

The updater is supposed to set it to the maximum value stored in the database, but since you had trouble with the updater, that is probably the issue.  Download this file and run it, and that will reset the picture counter.

If you're still having issues after the picture counter has been fixed, then we'll have to go from there.  However, "key 2" is the picture number set with piccount, so I'm pretty sure resetting the picture counter will fix this.

Well. I had to a bit of tinkering with the reset_pic.php file where as:

<if ($new_piccount > $cfg['pic_store'])
        $new_piccount = 0

And in the functions.php file there is this line where the piccount value increases:

// Increment picture counter
    $result = mysql_query ("UPDATE {$OekakiPoteto_MemberPrefix}oekaki SET piccount=(piccount + 1) WHERE usrname='{$OekakiU}'");

Now that the piccount has been reset to zero (temporarly to be in sync and divisible by 300), I'm still getting sqlfail error messages:

sqlfail: paintbbsget: cannot save image 9: (Duplicate entry '9' for key 2)
sqlfail: paintbbsget: cannot save image 1: (Duplicate entry '1' for key 2)

The sqlfail error message occurred after I tried saving a test image in which it was 1.png, sadly that caused the piccount to skip to 2 instead. sad

My suspicion is that something might be causing sqlfailures upon trying to save, upload or retouch images:

This is a snippet from the paintbbsget.php file:

// Insert new slot
    $tries = 5;
    while ($tries > 0) {
        $sqlres = "INSERT INTO {$OekakiPoteto_Prefix}oekakidta SET usrname='$OekakiU', postdate=NOW(), hostname='$hostname', comment='', PIC_ID='$resno', IP='$address', datatype=0, edittime='$thetime', password=''";
        if ($mode == 'anim') {
            $sqlres .= ', animation=1';
        } else {
            $sqlres .= ', animation=0';
        }
        $result = mysql_query ($sqlres);

        if ($result) {
            $tries = 0;
        } else {
            $tries--;
            sleep (1);
        }
    }
    if (!$result) {
        w_log_sql('paintbbsget: cannot save image '.$resno);
        quiet_exit($langop_err_saveimg);
    }

Upon attempting to save images, the $tries variable counts down from 5 to 0 with the sleep value of 1 and if the $tries value counts down to 0 then the picture saving is a success, otherwise, paintbbsget returns this error message resulting in a sqlfail error causing the piccount variable to skip by one. I don't know if I'm correct about this or not...

But Wac, before we go on the topic about automatically resetting the pic count to zero once the picture count reaches 300, how can I fix the sqlfail issue that causes the piccount to skip?


Update: I know what might be causing the sqlfail error message to occur. When you try to save a drawing and if the drawing exists, you will not be able to save the drawing until it saves on a unused slot. It's not supposed to do that.

My oekaki should be instructed to overwrite the oldest images and not skip it. My oekaki used to overwrite images once it rolls over at 0 prior to the upgrade, ya know and I never archive images. Again, how would I get it fixed?

Last edited by rainbow (09-01-2008 21:23:46)

Offline

#4 09-03-2008 05:06:01

Waccoon
Administrator

Re: sqlfail error + piccount/PIC_ID variable glitch

The reason why the insertion doesn't work is because the board will no longer replace posts already in the database (by deleting the old one and then writing a new one in its place).   This is the behavior that caused so many sorting issues in the past and had to be fixed.

What's happening is that the database is set up so there cannot be more than one PIC_ID in the database at a time (using the SQL clause "UNIQUE KEY PIC_ID (PIC_ID)").   If you reset the piccount value to zero, the board will attempt to insert a new slot on top of one that already exists, and the database will throw an error.  This is the "duplicate key" issue.  It can't post image 9 because image 9 already exists.  The new code is designed not to re-use old PIC_IDs.  It always uses new, never-used IDs.  Old PIC_IDs are deleted automatically when the picture limit is reached, but the piccount value is never reset, anymore.  It cannot be reset.

As of Wax 5.6.0, setting piccount to a lower value than the PIC_ID of the newest image posted will cause a problem.  So, if 84 images have been posted to the board, and none have ever been deleted, the current value of piccount must be no lower than 84.  It will be incremented to 85 when the next picture is posted.

The script I posted will check for the lowest PIC_ID currently in use, and will set piccount to that value.  You are not allowed to reset piccount, only to increase it.

My oekaki should be instructed to overwrite the oldest images and not skip it.

The new code cannot overwrite posts unless you are retouching a picture.

Let's suppose you set the picture limit to 300.  When picture number 301 is posted, picture 1 will be deleted.  When picture 461 is posted, picture 161 will be deleted, and so on.  The board will always have a maximum of 300 images, but piccount will just increase forever.  If images have been archived, they will be protected from deletion, but otherwise the oldest images will be deleted first.  In the old board, images with the next piccount value were deleted, and that caused a whole bunch of problems, because it didn't guarantee that the oldest images would be the ones to be overwritten.  It kind of sucks when an image posted last month gets deleted before an image that was posted two years ago.

Don't worry about overwriting old posts.  The new code will take care of old posts, and piccount has nothing to do with it.

Offline

#5 09-03-2008 05:49:23

rainbow
Member

Re: sqlfail error + piccount/PIC_ID variable glitch

Waccoon wrote:

The reason why the insertion doesn't work is because the board will no longer replace posts already in the database (by deleting the old one and then writing a new one in its place).   This is the behavior that caused so many sorting issues in the past and had to be fixed.

What's happening is that the database is set up so there cannot be more than one PIC_ID in the database at a time (using the SQL clause "UNIQUE KEY PIC_ID (PIC_ID)").   If you reset the piccount value to zero, the board will attempt to insert a new slot on top of one that already exists, and the database will throw an error.  This is the "duplicate key" issue.  It can't post image 9 because image 9 already exists.  The new code is designed not to re-use old PIC_IDs.  It always uses new, never-used IDs.  Old PIC_IDs are deleted automatically when the picture limit is reached, but the piccount value is never reset, anymore.  It cannot be reset.

As of Wax 5.6.0, setting piccount to a lower value than the PIC_ID of the newest image posted will cause a problem.  So, if 84 images have been posted to the board, and none have ever been deleted, the current value of piccount must be no lower than 84.  It will be incremented to 85 when the next picture is posted.

The script I posted will check for the lowest PIC_ID currently in use, and will set piccount to that value.  You are not allowed to reset piccount, only to increase it.

My oekaki should be instructed to overwrite the oldest images and not skip it.

The new code cannot overwrite posts unless you are retouching a picture.

Let's suppose you set the picture limit to 300.  When picture number 301 is posted, picture 1 will be deleted.  When picture 461 is posted, picture 161 will be deleted, and so on.  The board will always have a maximum of 300 images, but piccount will just increase forever.  If images have been archived, they will be protected from deletion, but otherwise the oldest images will be deleted first.  In the old board, images with the next piccount value were deleted, and that caused a whole bunch of problems, because it didn't guarantee that the oldest images would be the ones to be overwritten.  It kind of sucks when an image posted last month gets deleted before an image that was posted two years ago.

Don't worry about overwriting old posts.  The new code will take care of old posts, and piccount has nothing to do with it.

Thanks for understanding about this issue, Wac. Because of the mess that I've gotten myself into, I have been forced to wipe out the pictures and comments on my oekaki. Now that you explained it, I have set the PIC_ID variable to be in sync with the ID_2 variable.

Oh and one more thing, Wac...if the number of pictures reach the maximum number of pictures stored, will the oldest pictures that have the .png extension for example be deleted off the server? Just wondering.

Offline

#6 09-04-2008 03:07:39

Waccoon
Administrator

Re: sqlfail error + piccount/PIC_ID variable glitch

Thanks for understanding about this issue, Wac. Because of the mess that I've gotten myself into, I have been forced to wipe out the pictures and comments on my oekaki. Now that you explained it, I have set the PIC_ID variable to be in sync with the ID_2 variable.

That's fine.  Just keep in mind that the ID_2 and PIC_ID will not always be synced.  ID_2 refers to the total number of database insertions, and PIC_ID is the current picture.  Technically, an ID_2 will always be equal or greater than any PIC_ID.

Oh and one more thing, Wac...if the number of pictures reach the maximum number of pictures stored, will the oldest pictures that have the .png extension for example be deleted off the server? Just wondering.

Yes.  The oldest non-archived picture and its animation/thumbnails are deleted one slot at a time as necessary to make room for new pictures.

Bear in mind that this is a hack to make up for the fact that the oekaki can't easily determine the total amount of server space used.  If you want to know how much space is being used on the server, go into the diagnostics screen, and it will give you some stats on the pictures folder, including how much total space is being used.  Almost all oekakis these days can handle having 1,000 picture slots, so if you're concerned about losing pictures, set the number of stored pictures to a very high value, check how much storage space your web host gives you, and keep track of how much space is actually being used.

On my oekaki, the average size of a picture post is 60K.  So, if my host gives me 300MB of space, that gives me space for several thousands of pictures.  There's no need to keep the limit to, say, 200 pictures.

Offline

#7 09-04-2008 05:06:22

rainbow
Member

Re: sqlfail error + piccount/PIC_ID variable glitch

Waccoon wrote:

Thanks for understanding about this issue, Wac. Because of the mess that I've gotten myself into, I have been forced to wipe out the pictures and comments on my oekaki. Now that you explained it, I have set the PIC_ID variable to be in sync with the ID_2 variable.

That's fine.  Just keep in mind that the ID_2 and PIC_ID will not always be synced.  ID_2 refers to the total number of database insertions, and PIC_ID is the current picture.  Technically, an ID_2 will always be equal or greater than any PIC_ID.

Oh and one more thing, Wac...if the number of pictures reach the maximum number of pictures stored, will the oldest pictures that have the .png extension for example be deleted off the server? Just wondering.

Yes.  The oldest non-archived picture and its animation/thumbnails are deleted one slot at a time as necessary to make room for new pictures.

Bear in mind that this is a hack to make up for the fact that the oekaki can't easily determine the total amount of server space used.  If you want to know how much space is being used on the server, go into the diagnostics screen, and it will give you some stats on the pictures folder, including how much total space is being used.  Almost all oekakis these days can handle having 1,000 picture slots, so if you're concerned about losing pictures, set the number of stored pictures to a very high value, check how much storage space your web host gives you, and keep track of how much space is actually being used.

On my oekaki, the average size of a picture post is 60K.  So, if my host gives me 300MB of space, that gives me space for several thousands of pictures.  There's no need to keep the limit to, say, 200 pictures.

>> Yes.  The oldest non-archived picture and its animation/thumbnails are deleted one slot at a time as necessary to make room for new pictures.

That's good to hear that. big_smile


What can cause the ID_2 and PIC_ID variables to not always be in sync? :? I've recall that I fixed certain errors in the past when older versions of OP allowed overwriting of older pictures. I've been hosted on Oekaki Art since June 2005 and my Kameloh allocated 1 GB of disk space to the ppgrainbow subdomain name. Because I had to clear out the pictures and the comments associated with it the average size of a picture post has been reduced to just 652 bytes and prior to this, it was 49.7 KB.

I'm gonna try finding ways to promote my oekaki as the data wipeout and dispute over the PIC_ID variable glitch literally killed it. sad

Last edited by rainbow (09-04-2008 05:37:22)

Offline

#8 09-05-2008 04:00:33

Waccoon
Administrator

Re: sqlfail error + piccount/PIC_ID variable glitch

What can cause the ID_2 and PIC_ID variables to not always be in sync?

Leftovers from the old sorting system which don't cause technical issues, but do result in piccount not incrementing in some situations.  Technically, "piccount" should be removed from the code entirely.  But, that's not possible without a complete rewrite of all the image processing code, and that's too much work.

I'm gonna try finding ways to promote my oekaki as the data wipeout and dispute over the PIC_ID variable glitch literally killed it.

The real problem is that you don't have a testing platform.  If you're serious about developing the board any further (and without upsetting your members if things go wrong), you need to install a web server on your computer to test code.

How to do this is well beyond anything I can offer.  I install everything on my system "from scratch", but a more complete package which includes everything is Xampp.  I'd recommend it only if you own your own computer, as it will add a bunch of background tasks that will affect and possibly annoy other people who use the computer.

Offline

#9 09-05-2008 05:57:10

rainbow
Member

Re: sqlfail error + piccount/PIC_ID variable glitch

Waccoon wrote:

What can cause the ID_2 and PIC_ID variables to not always be in sync?

Leftovers from the old sorting system which don't cause technical issues, but do result in piccount not incrementing in some situations.  Technically, "piccount" should be removed from the code entirely.  But, that's not possible without a complete rewrite of all the image processing code, and that's too much work.

Well...good riddance for the old sorting system. wink

That's the probable reason why all of the images on the oekaki as well as the pictures and comments from the database were removed in a attempt to get the ID_2 and PIC_ID variables it in sync. I'm just hoping that no more problems will occur in the near future. As for doing a complete rewrite of the image processing code, that would be awful as it could cause so many errors to a point where it could break the oekaki's functionality.


I'm gonna try finding ways to promote my oekaki as the data wipeout and dispute over the PIC_ID variable glitch literally killed it.

The real problem is that you don't have a testing platform.  If you're serious about developing the board any further (and without upsetting your members if things go wrong), you need to install a web server on your computer to test code.

How to do this is well beyond anything I can offer.  I install everything on my system "from scratch", but a more complete package which includes everything is Xampp.  I'd recommend it only if you own your own computer, as it will add a bunch of background tasks that will affect and possibly annoy other people who use the computer.

As for Xampp, I'll check it out later today. big_smile

I'm pretty lucky that I can check for parse errors using DevPHP and editing the code on Windows Notepad. wink

Offline

Board footer

Yep, still running PunBB
© Copyright 2002–2008 PunBB