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.

Enabling Server Side Includes (SSIs)

Posted on by
Reply

I have some old web content that works using Server Side Includes and I needed to get it functional on a new web server. I found this article How do I enable server-side includes (SSIs) on my site? which had everything I needed to know.

First I needed to add the content type and output filter to the Apache configuration:

  AddType text/html .shtml
  AddOutputFilter INCLUDES .shtml

Then I needed to add the IncludesNoExec option:

  Options MultiViews Indexes SymLinksIfOwnerMatch IncludesNoExec

Then I needed to specify the DirectoryIndex:

  DirectoryIndex index.shtml index.html

And that was it!