LeeVee Posted January 15, 2007 Share Posted January 15, 2007 Hi,There seems to be a limit to the number of records I can add in a MySQL table.The database has been working fine, but then it reached cardinality of 677 records, then stopped adding new records.I now cannot add any more records.There doesn't appear to be any limitation in the PHP script.Members are trying to sign up on my site, and it is not adding the new records to the table. I cannot find any settings or options for this.Any ideas? Anyone? This is urgent - it's stopping members from signing up on my site!L Quote Link to comment Share on other sites More sharing options...
Jessica Posted January 15, 2007 Share Posted January 15, 2007 That's a very odd number to stop at. Can you post the structure of the table? What is the datatype of the PK Quote Link to comment Share on other sites More sharing options...
Philip Posted January 15, 2007 Share Posted January 15, 2007 Does your host have a limit on Database sizes? (not just number of rows, but physical size (.5MB or something)) Quote Link to comment Share on other sites More sharing options...
LeeVee Posted January 15, 2007 Author Share Posted January 15, 2007 [quote author=KingPhilip link=topic=122511.msg505323#msg505323 date=1168886119]Does your host have a limit on Database sizes? (not just number of rows, but physical size (.5MB or something))[/quote]hmmm... not as far as i know - i've just changed to a new host, and it seems to be since then.... i have looked in the whm and cpanel sql setup options, and i don't see anything pertaining to database limits Quote Link to comment Share on other sites More sharing options...
Philip Posted January 15, 2007 Share Posted January 15, 2007 Trying looking around on your host's FAQ and such. Quote Link to comment Share on other sites More sharing options...
LeeVee Posted January 15, 2007 Author Share Posted January 15, 2007 [quote author=jesirose link=topic=122511.msg505322#msg505322 date=1168886020]That's a very odd number to stop at. Can you post the structure of the table? What is the datatype of the PK[/quote]herewith structure; - not sure what you mean by datatype of PK? CREATE TABLE `user` ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) default NULL, `email` varchar(50) default NULL, `passwd` varchar(20) default NULL, `pay_to` int(11) default '1', `pay_to2` int(11) default '1', `pay_to3` int(11) default '1', `payout_address` varchar(50) default NULL, `payout_address2` varchar(50) default NULL, `payout_address3` varchar(50) default NULL, `ref` int(11) default '0', `invested` float(10,2) default '0.00', `acctype` int(11) default NULL, `credits` float default '0', `roi_cash` float(10,4) default '0.0000', `cshfrmallrefs` float(10,5) default '0.00000', `lifetime_cash` float(10,3) default '0.000', `lifetime_paid` float(10,2) default '0.00', `lifetot_roi` float(10,5) default '0.00000', `joindate` datetime default NULL, `lastmail` date default '0000-00-00', `minmax` int(11) default NULL, `crdsfrmallrefs` float default '0', `rbon_credits` float default '0', `rpage_credits` float default '0', `lifetime_credits` float default '0', `lifetime_pages` int(11) default '0', `sb_credits` float default '0', `sb_cash` float(10,5) default '0.00000', `ptc_clicks` int(11) default '0', `ptc_crds` float default '0', `ptc_cash` float default '0', `lastroi` date default '0000-00-00', `lastaccess` datetime default NULL, `lastsurfed` datetime default '0000-00-00 00:00:00', `upgrade_ends` date default NULL, `premregdate` date default '0000-00-00', `premmp` int(11) default '0', `toref` float default '0', `cshtoref` float(10,5) default '0.00000', `commstoref` float(10,4) default '0.0000', `allow_contact` char(3) default 'yes', `status` varchar(20) default NULL, `ip_address` varchar(15) default NULL, `ac` int(11) default '0', PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`)) ENGINE=MyISAM AUTO_INCREMENT=2501 DEFAULT CHARSET=latin1; Quote Link to comment Share on other sites More sharing options...
Jessica Posted January 15, 2007 Share Posted January 15, 2007 You said you're stuck at 677 - but your auto-increment is at 2501. Why is that?What is the highest 'id'? Quote Link to comment Share on other sites More sharing options...
Ninjakreborn Posted January 15, 2007 Share Posted January 15, 2007 It sounds like (from my perspective) that at some point an error found it's way into your database.Sql injection might have stopped it (theory only), but I have seen in most hosts, it says error check database, debug database. I know they can get error's, maybe your tables ran into some sort of error in the database itself, and messed up, stopped taking queries. I would (for debugging purposes)Dump all of the data, into an sql file. Completely erase everything on the database, completely clear it out, and empty everything, then reload the data into it, and see if that fixes the error. Quote Link to comment Share on other sites More sharing options...
LeeVee Posted January 15, 2007 Author Share Posted January 15, 2007 [quote author=jesirose link=topic=122511.msg505332#msg505332 date=1168886636]You said you're stuck at 677 - but your auto-increment is at 2501. Why is that?What is the highest 'id'?[/quote]oh - sorry - that was me experimenting trying to get it to work. It was on 677.It went up to id 676.Funny thing is, when a new member joins up,(ot tries to), it is incrementing the number, and going through the signup script without an error, but it just doesn't write the fields back to the table. It has incremented the number up to 714 at the moment....and it does keep incerementing, it's just not saving the info??! Quote Link to comment Share on other sites More sharing options...
LeeVee Posted January 15, 2007 Author Share Posted January 15, 2007 [quote author=businessman332211 link=topic=122511.msg505339#msg505339 date=1168886907]It sounds like (from my perspective) that at some point an error found it's way into your database.Sql injection might have stopped it (theory only), but I have seen in most hosts, it says error check database, debug database. I know they can get error's, maybe your tables ran into some sort of error in the database itself, and messed up, stopped taking queries. I would (for debugging purposes)Dump all of the data, into an sql file. Completely erase everything on the database, completely clear it out, and empty everything, then reload the data into it, and see if that fixes the error.[/quote]hmmm... an sql injection wouldn't surprise me - how do I spot that? I have totally deleted the database and restored from a sql backup, and no go.. Quote Link to comment Share on other sites More sharing options...
shoz Posted January 15, 2007 Share Posted January 15, 2007 Post the create statement for your table[code]SHOW CREATE TABLE tablename[/code]and the snippet that adds users to the database. Quote Link to comment Share on other sites More sharing options...
LeeVee Posted January 15, 2007 Author Share Posted January 15, 2007 [quote author=shoz link=topic=122511.msg505377#msg505377 date=1168889449]Post the create statement for your table[code]SHOW CREATE TABLE tablename[/code]and the snippet that adds users to the database.[/quote] CREATE TABLE `user` ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) default NULL, `email` varchar(50) default NULL, `passwd` varchar(20) default NULL, `pay_to` int(11) default '1', `pay_to2` int(11) default '1', `pay_to3` int(11) default '1', `payout_address` varchar(50) default NULL, `payout_address2` varchar(50) default NULL, `payout_address3` varchar(50) default NULL, `ref` int(11) default '0', `invested` float(10,2) default '0.00', `acctype` int(11) default NULL, `credits` float default '0', `roi_cash` float(10,4) default '0.0000', `cshfrmallrefs` float(10,5) default '0.00000', `lifetime_cash` float(10,3) default '0.000', `lifetime_paid` float(10,2) default '0.00', `lifetot_roi` float(10,5) default '0.00000', `joindate` datetime default NULL, `lastmail` date default '0000-00-00', `minmax` int(11) default NULL, `crdsfrmallrefs` float default '0', `rbon_credits` float default '0', `rpage_credits` float default '0', `lifetime_credits` float default '0', `lifetime_pages` int(11) default '0', `sb_credits` float default '0', `sb_cash` float(10,5) default '0.00000', `ptc_clicks` int(11) default '0', `ptc_crds` float default '0', `ptc_cash` float default '0', `lastroi` date default '0000-00-00', `lastaccess` datetime default NULL, `lastsurfed` datetime default '0000-00-00 00:00:00', `upgrade_ends` date default NULL, `premregdate` date default '0000-00-00', `premmp` int(11) default '0', `toref` float default '0', `cshtoref` float(10,5) default '0.00000', `commstoref` float(10,4) default '0.0000', `allow_contact` char(3) default 'yes', `status` varchar(20) default NULL, `ip_address` varchar(15) default NULL, `ac` int(11) default '0', PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`)) ENGINE=MyISAM AUTO_INCREMENT=2501 DEFAULT CHARSET=latin1;$new_ins = "insert into user (name, email, passwd, pay_to, pay_to2, pay_to3, payout_address, payout_address2, payout_address3, ref, acctype, credits, lifetime_credits, invested, joindate, minmax, lastaccess, allow_contact, status, ip_address, ac) values ('$name', '$email', '$passwd', '$pay_to', '$pay_to_b', '$pay_to_c', '$payout_address', '$payout_address2', '$payout_address3', $ref, 1, $ins_crds, $ins_crds, $insbonus, '$date', 0, '$date', '$allow_emails', 'Un-verified', '$my_ip_add', $ac)";$res = mysql_query($new_ins) or die (mysql_error()); Quote Link to comment Share on other sites More sharing options...
shoz Posted January 15, 2007 Share Posted January 15, 2007 I forgot that you posted the structure previously.[quote=Leevee]It has incremented the number up to 714 at the moment....and it does keep incerementing, it's just not saving the info??![/quote]If no records are being inserted how do you know that the number is now at 714? If you see new rows in the table but are not seeing anyone's info then post the script in its entirety. Did you perhaps change the form method from GET to POST or vice-versa?Put the following at the top of the script as well.[code]error_reporting(E_ALL);ini_set('display_errors', 1);[/code] Quote Link to comment Share on other sites More sharing options...
LeeVee Posted January 15, 2007 Author Share Posted January 15, 2007 [quote author=shoz link=topic=122511.msg505409#msg505409 date=1168890942]I forgot that you posted the structure previously.[quote=Leevee]It has incremented the number up to 714 at the moment....and it does keep incerementing, it's just not saving the info??![/quote]If no records are being inserted how do you know that the number is now at 714? If you see new rows in the table but are not seeing anyone's info then post the script in its entirety. Did you perhaps change the form method from GET to POST or vice-versa?Put the following at the top of the script as well.[code]error_reporting(E_ALL);ini_set('display_errors', 1);[/code][/quote]It's reading the incremented number with the following code; $usrid = mysql_insert_id(); and it echoes the new number on the screen. When I go in to try another one, it increments and gives me the next number, yet, it doesn't show any data past the 677th record in the table. Quote Link to comment Share on other sites More sharing options...
shoz Posted January 15, 2007 Share Posted January 15, 2007 How are you determining what data is in the table? Are you using an app like phpMyadmin or mysql monitor?Also post the version of MYSQL you're using. Perhaps there is a known bug.[code]SELECT VERSION();[/code] Quote Link to comment Share on other sites More sharing options...
LeeVee Posted January 15, 2007 Author Share Posted January 15, 2007 [quote author=shoz link=topic=122511.msg505434#msg505434 date=1168891795]How are you determining what data is in the table? Are you using an app like phpMyadmin or mysql monitor?Also post the version of MYSQL you're using. Perhaps there is a known bug.[code]SELECT VERSION();[/code][/quote]Using PhpMyAdmin . MySQL 4.1.21When I use the error reporting code as above, it shows up a lot of errors - all unknown variable type errors and POST related?? Quote Link to comment Share on other sites More sharing options...
shoz Posted January 15, 2007 Share Posted January 15, 2007 Post the errors and the script as well Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.