mysql prepared statement failing silently in a new machine

Question

I've moved a webserver from a Ubuntu machine to a Mac OS X machine, including the database. However, I am surprised to see that prepared statements that were working perfectly fine in the original server performing inserts no longer work.

The $stmt->execute(); does not return false, so everything seems to be ok, but I cannot see the new data inserted into the database.

PHP error log shows no issue.

I checked the privileges and everything looks good.

Do prepared statements need to be enabled in some specific way? How can I check whether they are succeeding?

My connection to the server is done to localhost on the same machine.

[Edit] After enabling general log I can see:

tail -f /var/log/apache2/error_log_mysql

/usr/local/mysql/bin/mysqld, Version: 5.6.17-log (MySQL Community Server (GPL)).     started 
with:

Tcp port: 0  Unix socket: (null)

Time                 Id Command    Argument
140526 13:36:28     3 Connect   root@localhost on database
                    3 Prepare   select * from `users` where email_addr=?
                    3 Execute   select * from `users` where email_addr='daorejw@ijaod.com'
                    3 Close stmt
                    3 Quit
                    4 Connect   root@localhost on database
                    4 Prepare   insert into `users` (first_name,last_name,password,email_addr,country,zip_code,company_name) VALUES(?,?,?,?,?,?,?)
                    4 Quit

I am suspicious of the port being 0 and socket Null, as well as the fact that there doesn't seem to be an execute after the prepare. I do have execute() as far as the php is concerned, but interestingly enough it doesn't seem to show up after the prepare.

$sql="insert into `users` (first_name,last_name,password,email_addr,country,zip_code,company_name,a, t)
    VALUES(?,?,?,?,?,?,?,0,?)";

    $stmt = $conn->prepare($sql);
    $stmt->bind_param('ssssssss', $p_firstname,$p_lastname,$p_password_hash,$p_emailaddr,$p_countryname,$p_zcode,$p_companyname,$t);
    $stmt->execute();

    if($stmt == false)
    {
        error_log("SQL Failed", 0);
        die('Error: ' . mysqli_error($conn));
        echo "Error adding record</br>";
    }
    else
    {
                   // This part executes successfully.
            }

Double-check that your app is not still inserting data to the old machine. :-) — Bill Karwin, May 26 '14 at 17:56
Thought it is an interesting observation, the old server is offline. — user3396423, May 26 '14 at 17:58
You can enable the `general-log` and watch for both the PREPARE and EXECUTE of SQL statements. Prepared statements should work, there's no config to enable or disable them. — Bill Karwin, May 26 '14 at 18:01
Can we have more code please? at least the parts that have to do with db for these two calls — Spikolynn, May 26 '14 at 19:08
Are the tables (and the db) still InnoDB? Is the InnoDB engine active? Oh wait, statements don't need InnoDB, right? I was thinking about transactions. — Rudie, May 26 '14 at 19:13
I took the tables and moved them as well, so they're there. I can see other users in the table. — user3396423, May 26 '14 at 19:15
@Spikolynn: I've added the php code used in the second part which is what is failing. — user3396423, May 26 '14 at 19:27
possible duplicate of [mysqli\_stmt::execute() returning false and statement not executing](http://stackoverflow.com/questions/18240022/mysqli-stmtexecute-returning-false-and-statement-not-executing) — Spikolynn, May 26 '14 at 20:03

score 0 · Accepted Answer · answered May 26 '14 at 19:32

0

My guess is that it is a problem with binding. Try printing all the variables that you bind to see if values are what you expect them to be.

answered May 26 '14 at 19:32

Spikolynn

4,067
2
37
44

Even if that were the case, I would expect the execute should be showing, even if with wrong data. – user3396423 May 26 '14 at 19:44
Try setting mysqli_report(MYSQLI_REPORT_ALL); before this code. I read that without it error handling is different – Spikolynn May 26 '14 at 19:53
Looks like we're on the right track. Adding this showed me an unhandled exception related to a lack of a default value for one of the fields. I'll try and fix it and see. – user3396423 May 26 '14 at 20:01
Probably your old server had this reporting option set differently in global settings if it worked there – Spikolynn May 26 '14 at 20:04
That fixed it. One of the fields needed a default value. Perhaps this is a difference with respect to the other version I was previously running. Basicaly I had to set a 0 to one of the fields in the bind. – user3396423 May 26 '14 at 20:05

mysql prepared statement failing silently in a new machine

1 Answers1