MySQL filling new table problem

basm

Hey all,

I am troubleshooting a very odd problem, because it only occurs once in 250 times, and I am not able to pinpoint the problem. I have a PHP script that automatically gives a user a session, creates a table for that user and fills it with a line of default values. I do that like:
mysql_connect("$host","$user","$pass");
mysql_select_db($db_name) or die ('Unable to connect to the database!'.mysql_error());
mysql_query ("CREATE TABLE $session (
id INT NOT NULL AUTO_INCREMENT ,
starttime TEXT NOT NULL ,
ctime TEXT NOT NULL ,
action TEXT NOT NULL ,
PRIMARY KEY ( id )
)") or die ('error during the creation of the session-item'.mysql_error());
mysql_query("INSERT INTO $session VALUES ('','$starttime','$thetime','$action')");

Afterwards the php-script tell a flash-movie that the table is created and the movie can go on. As I explained once in a while the movie won't go on, and when I check, I see that the table was created, but the first line wasn't filled, and I assume because of an error the script didn't pass variables to flash, but an error message.

Does anyone have a clue???

nemonoman

Two comments:

I can guess an answer, but maybe somebody has a fact rather than a speculation to back things up.

I haven't been able to take advantage of MySQL's transaction and rollback functionality. I have a fairly complex database, and many times a unit of work will address 8-10 different tables.

On some rare occasions I have seen partial units of work. I've checked with my users, and they told me that they had 'made a mistake' and pressed the escape key or closed the browser in effort to lessen the damage. Either PHP or MySQL appears to check whether the browser is stll connected before it proceeds with actions: Or is that just a misunderstanding on my part??

Second: WHY ON EARTH are you making a new table for each session? WHAT THE..?? kind of database is that???

Why don't you just make one table with 'sessionid' as a column and populate a new row? What on earth do you gain with a new table for every session??

basm

Hi,

thanks for the answer! The reason I create a new table for each session, is that I the tables are filled with a couple of tousands of rows and I dump each session-table that hasn't been used for over an hour by a cronjob that is running. I've tested this way of working with 130 simultanious users, and everything performed well. The reason I do this is to keep track of everything that happens in a Flash-game (gambling) I made, the flash game is instructed by the php-script and the php-script registers every action in the database. I want to avoid that the database is growing out of control, and this seemed an easy way to clean this up...

The problem occurs not only by users that press a wrong button, or something like that, because it also happened a couple of times to me; I go to the url, the game starts but won't go on, I look in the database and find a 0-rows table, which corresponds with the session I was starting. And on that moment I perfomed nothing illegal 😉

Any other suggestions, I'm hopless now, because I really can't firgure out what is going wrong 🙁