My MySQL Database Problem

[jazzmatazz2005]

Not to long ago i had a login script created for one of my sites. All it does is allow the user to view extra content that i have on the site. It asks them for a username, password, their first name and their email address. I just found out that it no longer allowing people to register and i don't know why. I do know that the script connects to the database with no problems and their is also plenty of disk space. I only have about 100 registered users and doesn't seem to allow anymore. Is there something that i shuould look for in the script? or is their something i should do to the database itself?

[brian.mark]

More info is needed to really help you disect this problem.

What was the script programmed in?

Did it set any limits in the code?

Do you have any ID numbers assigned that may be limited to 2 digits in the database?

Is it exactly 100? That could be an indicator.

Has the script or the database changed at all?

This is sort of like talking to a mechanic and saying your car won't run. He's going to need more information. It could be out of gas, the battery could be dead, or it could be something serious. Those questions above should get us started in the right direction.

Brian.

[jazzmatazz2005]

Ok I can answer those...

What was the script programmed in?

The Script is in PHP

Did it set any limits in the code?

I don't think so..Should i post some of the script here? If so what part

Is it exactly 100? That could be an indicator.

No. it's actually 145

Has the script or the database changed at all?

Nope!

Thanks brian.mark

[brian.mark]

Ok, now that we have some basic answers, next questions.

Can you manually add someone to your database?

Have you recently switched hosting, or has your version of PHP recently been upgraded by your web host?

I think we're starting to narrow things down a bit here.

Brian.

[jazzmatazz2005]

i can answer those 2

Can you manually add someone to your database?

Yes

Have you recently switched hosting, or has your version of PHP recently been upgraded by your web host?

I didn't switch hosting but i did move the script to a differnt site than what it was first on

[brian.mark]

Originally Posted by jazzmatazz2005

I didn't switch hosting but i did move the script to a differnt site than what it was first on

Is there anything in the script that is looking for referrer? That's one of the easiest ways to protect against fictitious registrations. A simple search for your old domain name should find that info.

Is the new site on the same server? Just curious.

Brian.

[mktplace]

If the new site isn't under the same /usr/web_login account, then it probably won't connect to the mySQL database, esspecially if it is on a different machine.

When you moved the script, did you move the mySQL database with it? Are you sure you got the permissions set correctly on the files?

Are you sure the database you are manually adding folks to is the one the script is attempting to connect with?

[jazzmatazz2005]

If the new site isn't under the same /usr/web_login account, then it probably won't connect to the mySQL database, esspecially if it is on a different machine.

When you moved the script, did you move the mySQL database with it? Are you sure you got the permissions set correctly on the files?

Are you sure the database you are manually adding folks to is the one the script is attempting to connect with?

The site connects to the DB just fine. When i moved the script i also created a new DB for it to connect to and imported all the data. Fow a while it was working correctly. It just now stopped.

[edhan]

For this type of unknown 'error', I would normally debug the PHP by adding echo on different execution points to see where the 'error' occurs.

[TrafficProducer]

Have you tried Backup - Do this, ( always), first!

Delete some users then try adding some users.

Does it stop at the same place.

[jazzmatazz2005]

For this type of unknown 'error', I would normally debug the PHP by adding echo on different execution points to see where the 'error' occurs.

I usually don't do this stuff and since i know very little aboyt MySQL i don't how to do what your talking about edhan. Perhaps you can give me some tips.

Have you tried Backup - Do this, ( always), first!

Delete some users then try adding some users.

Does it stop at the same place.

I agree TrafficProducer. I alway do backup everything. When i moved the script i moved everything.

I deleted some users as you suggested and i tried to create new ones, but that didn't work. I wont let me add any new users

[TrafficProducer]

email addresses?

I use PERL with SQL and with PERL you need a backslash before the @ symbol.

For example

email@email.com would fail (Syntax error)

email\@email.com would be OK

With SQL Blank inputs may fail

Of cause you can't ask users to input this, so the code has to add it.

Typical SQL line, note single quote even for blanks, except with numbers:-

INSERT INTO `compare_data1` VALUES ('Bookplace Ltd', 'Books', 'Books', 'HOUSE TO LET, A', 'Books', 'HOUSE TO LET, A', 10.99, 0, 'ukpounds', '', '', 'http://www.awin1.com/awclick.php?gid=21734&mid=321&awinaffid=25014&p=ht tp://www.thebookplace.co.uk/bookplace/display.asp?cid=awin&isbn=1404366571', 200, 200);

'', above is a single quote then a null then a single quote.

[jazzmatazz2005]

email addresses?

I use PERL with SQL and with PERL you need a backslash before the @ symbol.

For example

email@email.com would fail (Syntax error)

email\@email.com would be OK

With SQL Blank inputs may fail

Of cause you can't ask users to input this, so the code has to add it.

Typical SQL line, note single quote even for blanks, except with numbers:-

INSERT INTO `compare_data1` VALUES ('Bookplace Ltd', 'Books', 'Books', 'HOUSE TO LET, A', 'Books', 'HOUSE TO LET, A', 10.99, 0, 'ukpounds', '', '', 'http://www.awin1.com/awclick.php?gid=21734&mid=321&awinaffid=25014&p=ht tp://www.thebookplace.co.uk/bookplace/display.asp?cid=awin&isbn=1404366571', 200, 200);

'', above is a single quote then a null then a single quote.

TrafficProducer, I think i understand what your saying. but it doesn't use PERL. The script is all PHP and uses the MySql DB
The script worked just fine up until a few days ago.

[edhan]

Show a section of your PHP and I will let you know where you can insert the Echo to debug.

[TrafficProducer]

Can you manually add someone to your database? Yes

Implys SQL code OK :)

But I recently had a simular problem (I think with SQL temp files, going up to a limit then stopping).

I found out the following:-

#1030 - Got error 28 from table handler

It means that your hard disk is full (probably in your MySQL database but it could be elsewhere and interfering with the free running of your database). Try clearing up some files, optimising or repairing your database. This problem can be serious as your MySQL is usually stored in a partition on your hard disk and this error usually means it is full - if it is you need to free up space or your data could become corrupt.

Error 28 it says "No space left on device".

Is the temp directory that MySQL is using have sufficient space? If not you can restart the server pointing the temp directory to a location where space is more abundant.

Later after Drop-ing and Deleteing Tables the and the Database making sure data was clean, waiting a day. Creating a new Database then INSERT INTO data again it seemed to work OK again.

[jazzmatazz2005]

<?
 if (isset($_COOKIE['Admin']))
 {
  require_once("conn.php");

  if (isset($_POST['Add']))
  {
   $cUsername = $_POST['username'];
   $cPassword = $_POST['password'];
   $cFullname = $_POST['fullname'];
   $cEmail = $_POST['email'];

   //Check for existing user
   $sq = "SELECT * FROM users WHERE username = '$cUsername'";
   $sr = mysql_query($sq) or die(mysql_error());
   if (mysql_fetch_array($sr)) die("User already exist");

   //Add if not exist
   $aq = "INSERT INTO users (username, password, fullname, email) 
          VALUES ('$cUsername', '$cPassword', '$cFullname', '$cEmail')";
   $ar = mysql_query($aq);

   //Preare personal message record for this user
   $suq = "SELECT * FROM users WHERE username = '$cUsername'";
   $sur = mysql_query($suq) or die(mysql_error());
   $sua = mysql_fetch_array($sur);

   header("location:users.php");    
  }

  require_once("templates/toptempl.php");
  require_once("templates/toppanel.php");
  require_once("templates/addusertempl.php");
  require_once("templates/bottomtempl.php");
}
else
header("location:adminlogin.php");
?>

[jazzmatazz2005]

TrafficProducer....I understand what your talking about but tha data base is only 0.03 MB in size.

Here's a peice of my code where should i put a echo so i can debug?

<?
 if (isset($_POST['Add']))
  {
   require_once("conn.php");

   $cUsername = $_POST['username'];
   $cPassword = $_POST['password'];
   $cFullname = $_POST['fullname'];
   $cEmail = $_POST['email'];

   //Check for existing user
   $sq = "SELECT * FROM users WHERE username = '$cUsername'";
   $sr = mysql_query($sq) or die(mysql_error());
   if (mysql_fetch_array($sr)) die("Sorry. That user name already exists. Use your browsers back button and select a different username.");

   //Add if not exist
   $aq = "INSERT INTO users (username, password, fullname, email)
          VALUES ('$cUsername', '$cPassword', '$cFullname', '$cEmail')";
   $ar = mysql_query($aq);

   //Preare personal message record for this user
   $suq = "SELECT * FROM users WHERE username = '$cUsername'";
   $sur = mysql_query($suq) or die(mysql_error());
   $sua = mysql_fetch_array($sur);

   header("location:welcome.php");
  }

  require_once("templates/toptempl.php");
  require_once("templates/registertempl.php");
  require_once("templates/bottomtempl.php");
?>

[TrafficProducer]

I see you are using :-

(mysql_error());

I use PERL for SQL, MySQL

In which I use a $dbh->errstr Example:-

$sth->execute or &sql_error_message("Can't open Could not execute SQL statement it maybe invalid. errstr==>" . $dbh->errstr); # dienice ("Can't open Could not execute SQL statement it maybe invalid");

The sub-routine emails me with any error.

I bet PHP has similar

[edhan]

Hi

By putting the echo for Username in 3 different places and if the code is executed properly then you will see 3 times the username. If not then investigate why it does not show the 3 echoes.

Originally Posted by jazzmatazz2005

Here's a peice of my code where should i put a echo so i can debug?

<?
 if (isset($_POST['Add']))
  {
   require_once("conn.php");

   $cUsername = $_POST['username'];
   $cPassword = $_POST['password'];
   $cFullname = $_POST['fullname'];
   $cEmail = $_POST['email'];
echo "$cUsername 
";
   //Check for existing user
   $sq = "SELECT * FROM users WHERE username = '$cUsername'";
   $sr = mysql_query($sq) or die(mysql_error());
   if (mysql_fetch_array($sr)) die("Sorry. That user name already exists. Use your browsers back button and select a different username.");

   //Add if not exist
   $aq = "INSERT INTO users (username, password, fullname, email)
          VALUES ('$cUsername', '$cPassword', '$cFullname', '$cEmail')";
   $ar = mysql_query($aq);
echo "$cUsername 
";
   //Preare personal message record for this user
   $suq = "SELECT * FROM users WHERE username = '$cUsername'";
   $sur = mysql_query($suq) or die(mysql_error());
   $sua = mysql_fetch_array($sur);
echo "$cUsername 
";
   header("location:welcome.php");
  }

  require_once("templates/toptempl.php");
  require_once("templates/registertempl.php");
  require_once("templates/bottomtempl.php");
?>

[edhan]

Hi

By putting the echo for Username in 3 different places and if the code is executed properly then you will see 3 times the username. If not then investigate why it does not show the 3 echoes.

Originally Posted by jazzmatazz2005

Here's a peice of my code where should i put a echo so i can debug?

<?
 if (isset($_POST['Add']))
  {
   require_once("conn.php");

   $cUsername = $_POST['username'];
   $cPassword = $_POST['password'];
   $cFullname = $_POST['fullname'];
   $cEmail = $_POST['email'];
echo "$cUsername 
";
   //Check for existing user
   $sq = "SELECT * FROM users WHERE username = '$cUsername'";
   $sr = mysql_query($sq) or die(mysql_error());
   if (mysql_fetch_array($sr)) die("Sorry. That user name already exists. Use your browsers back button and select a different username.");

   //Add if not exist
   $aq = "INSERT INTO users (username, password, fullname, email)
          VALUES ('$cUsername', '$cPassword', '$cFullname', '$cEmail')";
   $ar = mysql_query($aq);
echo "$cUsername 
";
   //Preare personal message record for this user
   $suq = "SELECT * FROM users WHERE username = '$cUsername'";
   $sur = mysql_query($suq) or die(mysql_error());
   $sua = mysql_fetch_array($sur);
echo "$cUsername 
";
   header("location:welcome.php");
  }

  require_once("templates/toptempl.php");
  require_once("templates/registertempl.php");
  require_once("templates/bottomtempl.php");
?>