Tag Archives: mysql

MySQL Updatable and Insertable Views

Posted on by
Reply

Learning about Updatable and Insertable Views in MySQL. I was getting the error ER_WARN_VIEW_WITHOUT_KEY which said “View being updated does not have complete key of underlying table in it.”

You would have thought that error message was entirely self-explanatory, but it took me a while to understand the problem. The problem was, well, er, that the view being updated didn’t have the complete key of the underlying table in it.

For those of you who are dense like I am, that means that the primary key for the underlying table hadn’t been included as a column in the view definition. I’d done some processing on the actual primary key to turn it from upper case to lower case, and this processing meant the underlying key wasn’t actually in the view I was updating, because the primary key had been modified slightly in the view definition. I got rid of the warning by just adding the primary key column unprocessed at the end of the column list and that got rid of the warning.

To use mysqli or PDO?

Posted on by
Reply

I’m trying to make my mind up about whether I should be using mysqli or PDO for a project I’m just starting and I’ve found a few relevant articles:

And based on all of that I think I’ve decided to use PDO.

Inserting multiple rows with an insert statement in MySQL

Posted on by
Reply

Today I had to double-check the syntax for doing multiple row inserts in a MySQL insert statement, because my first guess at the syntax was wrong. I found this article which explained the syntax which is basically:

INSERT INTO example
  (example_id, name, value, other_value)
VALUES
  (100, 'Name 1', 'Value 1', 'Other 1'),
  (101, 'Name 2', 'Value 2', 'Other 2'),
  (102, 'Name 3', 'Value 3', 'Other 3'),
  (103, 'Name 4', 'Value 4', 'Other 4');

MySQL: Error in SQL: Commands out of sync; you can’t run this command now

Posted on by
Reply

Today I was programming MySQL via PHP and I received an error “Error in SQL: Commands out of sync; you can’t run this command now”. It turned out the problem was that a previous mulit_query had mysqli_results that hadn’t been freed. This article helped me solve the problem, and now my batch mode SQL processor looks like this:

  public function execute_batch( $sql ) {

    $this->write_count++;

    if ( ! $this->link->multi_query( $sql ) ) {

      $this->throw_error( $sql );

    }

    if ( $this->link->more_results() ) {

      do {
        $result = $this->link->use_result();
        if ( $result instanceof mysqli_result ) {
          $result->free();
        }
      }
      while ( $this->link->more_results() && $this->link->next_result() );

    }
  }

This code executes the SQL batch and then frees any mysqli_results that result from the query batch.

phpMyAdmin #1045 Cannot log in to the MySQL server

Posted on by
Reply

I was using phpMyAdmin and trying to login to my server with a new user that I’d created called ‘pma’ but I was getting the error “#1045 Cannot log in to the MySQL server”. The ‘pma’ user was also my phpMyAdmin control user and as its login wasn’t working advanced features of phpMyAdmin weren’t functional and I was getting an error letting me know.

I thought maybe the problem was that I’d specified the password for the ‘pma’ user incorrectly so I reset the ‘pma’ user password just to be user:

  update user set password=PASSWORD("secret") where user='pma';

But the problem persisted. I had a look in the mysql.user table to see what was in there. The results included the following:

  mysql> use mysql;
  Database changed
  mysql> select Host, User, Password from user;
  +-----------+-----------+-------------------------------------------+
  | Host      | User      | Password                                  |
  +-----------+-----------+-------------------------------------------+
  | localhost |           |                                           |
  | %         | pma       | *BD053A55278DA675E32F28360C759B3FBEE32B3E |
  +-----------+-----------+-------------------------------------------+

Note the entry on the first line with a blank username. I have no idea how that entry got into my mysql.user table but basically it seems to be interpreted as “any user” at localhost with no password. I didn’t like the look of that so I deleted that entry. After that entry had been deleted the login for my ‘pma’ user started to work.