CODEWITHSUNDEEP
mysqlpdomariadb
DroidOS
DroidOS

Reputation: 8890

MySQL CREATE/DROP USER returns 0

Why is it that 

$dbh->exec(CREATE USER 'usr'@'localhost' IDENTIFIED BY 'Pass100');

returns 0 even though the user is created? This is with MariaDB 10.

Upvotes: 2

Views: 462

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270607

According to the PDO docs, PDO::exec() returns the number of rows modified or deleted, or zero if none were.

PDO::exec() returns the number of rows that were modified or deleted by the SQL statement you issued. If no rows were affected, PDO::exec() returns 0

A CREATE USER/DROP USER statement, although it implicitly modifies the mysql.user table does not register as having affected any rows as an INSERT or DELETE statement would, and so exec() returns zero.

For example from the command line:

MariaDB [(none)]> CREATE USER u@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> DROP USER u@localhost;
Query OK, 0 rows affected (0.00 sec)

So the 0 return value should not be treated as a failure or falsy value. In other words, be careful not to use an expression like:

// Do not do this:
if (!$dbh->exec("CREATE USER 'usr'@'localhost' IDENTIFIED BY 'Pass100'")) {
  // Handle error
}

Instead use a strict comparison === to test for a FALSE return:

if ($dbh->exec("CREATE USER 'usr'@'localhost' IDENTIFIED BY 'Pass100'") === FALSE) {
  // Handle error
}

If there is any error in creating the user, such as the user already exists or you lack the privileges to create it, MariaDB will report that as an error, causing PDO::exec() to return FALSE instead of integer 0, or throw an exception if you have configured $dbh for PDO::ERRMODE_EXCEPTION.

Upvotes: 3

Related Questions