Database error 1054 on new install and remote MySQL database

ℹ️ Support
1

I am trying to install PHPList on a new Ubuntu 14.04 server with the MySQL database on another server (in this case Windows). From the Linux terminal I can login to the remote MySQL server and run commands like “show databases”, etc. - in fact, I created the database for PHPList using terminal from the Ubuntu server.

However, when I go to the page “http://mysite/lists/admin” I get the following error:
Database error 1054 while doing query Unknown column ‘phplist’ in ‘where clause’

If I create a local MySQL database and user on the linux box then go to the admin page the site loads and I can initialize the database. I really want to put the database on the remote server and not locally on the linux box. How can I get around this?

Linux: 4.2.0-27-generic #32~14.04.1-Ubuntu SMP Fri Jan 22 15:32:26 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
MySQL (remote): 5.6.12-log MySQL Community Server (GPL)
MySQL (local): 5.5.47-0ubuntu0.14.04.1 (Ubuntu)

Thanks,
Charles

2

You created the database on a Windows server (allowing remote connection from nux box).
You created the user (with proper permissions).
Then you setup the PHPList config file.
Then you tried to install PHPList.

Did it appear to install normally?
Can you verify that the database was initialized and contains tables with data?
Also check that the table prefix in the config file matches the table prefix in the database.

3

No, it did not install. I cannot get the site to load to start the install. I can only get the site to load if I change the config to use a MySQL database running on “localhost”. Once, I change the config to use the remote MySQL server (via hostname or IP) I get the 1054 error.

I confirmed I can talk to the database from the linux server and created the table to be used by PHPList.

Thanks for replying!!

  • Charles
4

@jchaven I guess this might be a configuration difference between the two databases, but you will need to get the query that is being rejected. Can you get it from the remote mysql server’s logs? If not then you can try modifying file admin/mysqli.inc

Look for this line

function Sql_Query($query,$ignore = 0) {

then add this line after

var_dump($query);

There will probably be lots of queries dumped but it might help to see what is failing.

5

If the PHPList config settings look good, I would try something a little quicker than figuring out the whole shebang.
Create the DB (AND USER) on the Linux box.
Adjust the connection settings in the config file.
Run the install and see if it initializes properly.
If not… problem with nux config for PHP or MySql.
If it goes well…
Migrate the DB to the Windows server and adjust the config file to point to the right place.

6

@duncanc Thanks for replying! I added the var_dump to the top of the function and the site displayed this instead:

string(79) "select table_name from information_schema.tables where table_schema = "phplist""
Database error 1054 while doing query  Unknown column 'phplist' in 'where clause'

Nothing was recorded in MySQL’s error log or slow log.

@NYChris I thought about doing this but, if this is a bug then it may be worth time hunting it down. In any event this is a good idea - thanks for the suggestion.

  • Charles
7

Does PHPList used double quotes? If I run the query from the terminal on the Linux box against the remote MySQL server I get this error:

mysql> select table_name from information_schema.tables where table_schema = "phplist";
ERROR 1054 (42S22): Unknown column 'phplist' in 'where clause'

However, if I change the double-quotes to single quotes it works:

mysql> select table_name from information_schema.tables where table_schema = 'phplist';
Empty set (0.00 sec)

This is also run from terminal on the Linux box and it shows the database exists (although empty):

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dbacclaim          |
| dbassets           |
| dbfiles            |
| dbleads            |
| dbmain             |
| dbprojects         |
| dbreport           |
| dbsupport          |
| mysql              |
| performance_schema |
| phplist            |
| shoreware          |
| shorewarecdr       |
| test               |
+--------------------+
15 rows in set (0.00 sec)

Hope this helps!

  • Charles
8

I don’t see how (or why) there would be values related to non-existent tables in the schema.
If the database doesn’t exist, OR the tables can’t be created, the installation should halt.

We need to track down why the program isn’t creating the tables.

Double-check that the DB User has the proper permissions to modify the DB.
OR just try this:

GRANT ALL PRIVILEGES ON phplist.* TO 'USERNAME';
FLUSH PRIVILEGES;
9

User already had privileges and granting to phplist.* didn’t work.

mysql> show grants for 'editor'@'%';
+----------------------------------------------------------------------------------------------------------------------------------+
| Grants for editor@%                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'editor'@'%' IDENTIFIED BY PASSWORD '*84xxx...xxxFC' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON phplist.* TO 'editor';
Query OK, 0 rows affected (0.27 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.25 sec)

Thanks!
Charles

10

On my local linux/mysql both single and double quotes work. This particular query does use double quotes to wrap the literal phpist, but I am not sure whether that is consistent throughout the code.

Try this query to see what mysql allows for quotes

SHOW SESSION VARIABLES LIKE 'sql_mode';
11

@duncanc - it’s ANSI mode:

mysql> SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------+
| Variable_name | Value                                                       |
+---------------+-------------------------------------------------------------+
| sql_mode      | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+---------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

@NYChris - I can also create a table using the PHPList schema from the Linux terminal:

mysql> use phplist;
Database changed

mysql> CREATE TABLE IF NOT EXISTS jch1 (
    ->     id int(5) NOT NULL AUTO_INCREMENT,
    ->     name varchar(50) DEFAULT NULL,
    ->     PRIMARY KEY(id)
    ->     );
Query OK, 0 rows affected (1.19 sec)

mysql>
mysql> show columns in jch1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(5)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.07 sec)

I really appreciate you guys helping out on this!!
Charles

12

That seems to stop double quotes being used, so you need to change that.

http://dev.mysql.com/doc/refman/5.5/en/sql-mode.html#sqlmode_ansi_quotes

There looks to be an assumption by phplist that both single and double quotes can be used, so the documentation should make that clear and probably the code needs to test it too.

1 Like
13

ANSI mode was it. I change the SQL mode in MySQL to use “TRADITIONAL” and I was able to start the initializing of the PHPList database.

Thanks again for all your help!
Charles

1 Like