Jump to content
Larry Ullman's Book Forums

My Program Suddenly No Longer Records New Entries


Recommended Posts

A few years ago I put together a very small program using this book.  The program is very simple - basically just a form where the user chooses a number from 1-10 and makes a few notes.  The subject is the user's mood and what the user is doing at the time of the entry.  The purpose is to discover patterns in terms of time of day or type of activity and how those might correlate to feelings of well-being or of discouragement. 

 

Last night, after a delay of months, I opened up my cPanel and PhpMyAdmin in order to do some basic housekeeping.   I deleted all the spam entries and then deleted a bunch of users.  

 

Please note: I did *not* delete my own user account nor the accounts of a few friends who also use this program.  

 

Somehow or other I seem to have disabled the program!   It no longer retains new entries at all.  I've made several, and the Web page responds by saying that the entry is confirmed -- but no entry appears in the database.  

 

As part of my effort to troubleshoot, I created a new account for myself.  The new account was created; a confirmation email was sent; and when I confirmed, the account was put into effect.  

 

However, even entries for the new account are not recorded in the database.   

 

Does anyone have any idea what the problem could be?   I opened up PhpMyAdmin just now and could not see anything obviously wrong.  

 

Please note:  I did not change any of the programming.   I did not even open up or look at the program files.    The problem has to be somewhere in the database, I guess? - and presumably was effected while I was using PhpMyAdmin.  

 

Please help, if you can! 

 

 

Link to comment
Share on other sites

Thank you, Antonio.   I can try that - I think.  (if I understand your suggestion)   Why would there suddenly be a problem, though??  Everything has remained exactly the same, except that I deleted some spam entries and some spam users.   I don't see why the primary key size would be affected.  But then there is so much that I do not understand.  :-(    Thank you again for your reply. 

Link to comment
Share on other sites

Sorry, I meant your keys in general, not especially the primary key.

 

In my case one key was was set to something like SMALLINT(3). When row number 1 000 was inserted (and the key thus had a size of 4) the program couldn't insert more rows. This was a little bit hard to debug, as the problem was not even related to a primary key, but when I tried a manual insert into PHPMyAdmin, the problem was found.

 

We insert 10 rows into our DB each football round in our league. (10 games * 36 rounds) * 2,8 seasons = 1080 match ids, and a too small key for storing the match_id. The program therefor ran nice for almost three full football seasons before crashing.

 

It's really an edge case, but it's the only thing I can really provide at the moment.

Link to comment
Share on other sites

Antonio, thank you so much for your explanation.   It does seem very plausible.   Except that it does seem odd that the limit would be reached on the very same evening that I made the deletions?  

 

Well, I should definitely give your suggestion a try.  I've never tried making that type of change through PhpMyAdmin so I don't feel very much confidence about it.  And yet it does seem like possibly the road to a solution. 

 

Thank you again.  :-) 

Link to comment
Share on other sites

Here is my database structure.  Does it limit the number of rows?  The number of entries right now is 822.   Since I deleted so many two nights ago, it could possibly have been close to 1000 - but I don't think that any limitation is listed here, is it?    I don't see one but I really don't understand these things very well.   (The number of users is still under 100.)   I have no idea what to change! 

 

users table: 

 

Field Type Null Default  

user_id smallint(5) No     

email varchar(40) No     

password varchar(40) No     

first_name varchar(15) No     

last_name varchar(30) No     

active char(32) Yes  NULL

registration_date datetime No   

 

- - - - 

moods table: 
 

Field Type Null Default  

user_idm smallint(5) No     

mood_rating char(2) No     

notes text Yes  NULL

time_entered datetime No     

time_entered2 timestamp No 

CURRENT_TIMESTAMP 

Link to comment
Share on other sites

Keep in mind that if you have auto_incremented primary key, deleting rows won't affect anything. It's not really the total number of rows that's interesting, but what the ID value is. Even failed inserts will in most cases increase the auto_increment value and deleting rows won't decrease it.

 

The easiest way to at least discard that is is the problem is to click "structure" when you browse the user table, click "edit" on user_id and setting it to 11. If it works, that was the problem. If not, we'll have to dive deeper into the problem. Doing the change will also further-proof you a little bit.

  • Upvote 1
Link to comment
Share on other sites

Antonio, thank you again for your effort to help me.   I have discovered something completely unexpected that almost certainly will shed light on this problem. 

 

The software is inserting new entries into the database but is not putting them in the last row of the database -- it is putting them in other spots, well above the last row!    Just now I intentionally deleted an entry that had been made very early in my use of the program and therefore occupied the third row in the database.   Then I made a brand new entry -- which was inserted into the third row!  

 

This is, as mentioned, completely unexpected.  I think it is also contrary to what you would have expected, Antonio.   It is very inconvenient.  One of the main uses of the program is to review the entries in chronological order and see what can be gleaned from them.  Now that can no longer really be done.  

 

Once again I must ask you - any thoughts?  

Link to comment
Share on other sites

I guess your program handles the incrementing itself? Try setting the primary key to auto_increment, increase it's size and see what happens. Also, check your relationships. If other tables depend on the key, you might want to hard link that using foreign keys and looking at transactions in the DB handling code. You might also want to manually set the increment in your table to the latest row. Most of these things are found under structure or operations tabs when you look at a table in phpmyadmin.

  • Upvote 1
Link to comment
Share on other sites

Antonio, I just don't know what to think.  I definitely haven't changed anything in the structure of the database.  It was all basically working -- each row was entered at the end, chronologically speaking -- until I deleted the spam rows and the spam users.  Now the rows seem to be entered almost randomly.   There is no apparent order at all.    It does not make sense to me to start tinkering with the db structure when I have done nothing to alter that structure? 

Link to comment
Share on other sites

As stated earlier, it looks like you don't have set auto_increment on your primary key, but instead handle that through code. You at least need to confirm or deny that before we can really help you out. Haven't stated how you integer size is on the key neighter. We need to know...

 

You can do some steps until you've given us the info:

1. Go into the "operations" tab on the table. If you have an auto_incremented primary key, you should see a auto_increment value there. Make sure that the value is in fact one higher than your latest added row.

2. In the same operations tab, look for some options at the bottom left. Try clicking "Check table", make sure it's ok. Then do "analyse table".

3. Increase the int size of your primary key/key that's related to your problem. It can't break anything, only prevent a problem in the future. 11 is a reasonable number.

Link to comment
Share on other sites

 Share

×
×
  • Create New...