MySQL works in console but gives 500 internal server error when executed from PHP

Question

I have a small php file which just creates the tables in a database. I've spent days trying to debug it and it's left me stumped. It poops out with an ugly HTTP Error 500 (Internal Server Error): An unexpected condition was encountered while the server was attempting to fulfil the request. when I execute the queries. However, when I print out the queries and paste them into the database, it works fine.

I also noticed that "table already exists" would flash when entering the queries manually, but disappear and work quickly. Similar to this question Mysql 1050 Error "Table already exists" when in fact, it does not , but I've tried deleting and remaking the database without success. If anyone could help it'd be greatly appreciated. Here's the php code

$users = 'CREATE TABLE users (
        id int UNSIGNED NOT NULL AUTO_INCREMENT,
        PRIMARY KEY(id),
        username varchar(30) NOT NULL, UNIQUE(username),
        password varchar(30) NOT NULL,
        email varchar(50) NOT NULL, UNIQUE(email),
        confirmation_code int NOT NULL,
        confirmation_tries int NOT NULL
    ) ENGINE=InnoDB';

$players = 'CREATE TABLE players (
        id int UNSIGNED NOT NULL,
        FOREIGN KEY(id) REFERENCES users(id),
        cost int UNSIGNED NOT NULL,
        strategy TINYINT UNSIGNED NOT NULL,
        age int UNSIGNED NOT NULL,
        %s
    ) ENGINE=InnoDB';
$records = array();
for ($i = 0; $i < NUM_RECORDS; $i++) {
    $records[$i] = sprintf('record_%d int UNSIGNED NOT NULL', $i);
}
$players = sprintf($players, implode(', ', $records));

$kingdoms = 'CREATE TABLE kingdoms (
        id int UNSIGNED NOT NULL,
        FOREIGN KEY(id) REFERENCES users(id),
        location int UNSIGNED NOT NULL,
        building text NOT NULL,
        level int UNSIGNED NOT NULL,
        troops int UNSIGNED NOT NULL
    ) ENGINE=InnoDB';

$inboxes = 'CREATE TABLE inboxes (
        recipientID int UNSIGNED NOT NULL,
        FOREIGN KEY(recipientID) REFERENCES users(id),
        messageID int UNSIGNED NOT NULL AUTO_INCREMENT,
        PRIMARY KEY(messageID),
        message text NOT NULL,
        date DATETIME NOT NULL
    ) ENGINE=InnoDB';

$lastActions = 'CREATE TABLE last_actions (
        id int UNSIGNED NOT NULL,
        FOREIGN KEY(id) REFERENCES users(id),
        history text
    ) ENGINE=InnoDB';

$news = 'CREATE TABLE news (
        id int UNSIGNED NOT NULL,
        FOREIGN KEY(id) REFERENCES users(id),
        entries text
    ) ENGINE=InnoDB';

/*
$dbh->exec($users);
$code = $dbh->errorCode();
if ($code !== '00000') {
    var_dump($code);
    echo '111111';
    exit;
}
$dbh->exec($players);
$code = $dbh->errorCode();
if ($code !== '00000') {
    var_dump($code);
    echo '22222';
    exit;
}
$dbh->exec($kingdoms);
$code = $dbh->errorCode();
if ($code !== '00000') {
    var_dump($code);
    echo '33333';
    exit;
}
$dbh->exec($inboxes);
$code = $dbh->errorCode();
if ($code !== '00000') {
    var_dump($code);
    echo '44444';
    exit;
}
$dbh->exec($lastActions);
$code = $dbh->errorCode();
if ($code !== '00000') {
    var_dump($code);
    echo '555555';
    exit;
}
$dbh->exec($news);//
$code = $dbh->errorCode();
if ($code !== '00000') {
    var_dump($code);
    echo '666666';
    exit;
}//*/
echo $users . ";" . $players . ";" . $kingdoms . ';' . $inboxes . ';' . $lastActions .';' . $news;

Add error_reporting( E_ALL | E_STRICT ); ini_set( 'display_errors', 1 ); to the beginning of your PHP file to see what's going on. — MaxSem, Jun 23 '12 at 06:44
It's not per se your SQL that give the error: check your error logs, it could be something in your PHP code, your htaccess, etc. You should a) check you error logs for an error, b) show us a minimal piece of code that has the behaviour, instead of this big thing (with most of it commented out?) — Nanne, Jun 23 '12 at 06:44
@MaxSem thanks - took a few more hours but finally fixed the issue. Was something funky going on with my host. If you want to post as an answer I'll select it as correct — Raekye, Jun 23 '12 at 18:01

score 1 · Accepted Answer · answered Jun 23 '12 at 07:43

1

HTTP Error 500 (Internal Server Error) is error response of a HTTP request.
Use mysql_error or whatever you are using to echo the response of mysql_query in order to get error. or check error_logs. Its not exactly returned by mysql.

If your error_reporting is off, make it ON.

error_reporting( E_ALL | E_STRICT ); 
ini_set( 'display_errors', 1 )

answered Jun 23 '12 at 07:43

Nishu Tayal

20,106
8
49
101

The `error_reporting...` part helped, but that was from an earlier user. Also, I'm not using the outdated `mysql_...` functions, but the PDO connection and statements. Would still select it but the other guy was first. sorry! – Raekye Jun 23 '12 at 18:03

MySQL works in console but gives 500 internal server error when executed from PHP

1 Answers1