user warning: You have an error in your SQL syntax;

Hm. This looks like it was trying to query a view when you wanted the table. I'm unclear why it should do that; the install file prefers a view, but if you went into the settings, selected to use a table, and hit submit, it should create the table then. If you manage to figure out what happened, please let me know. I can't replicate this problem, though.

I seem to have the same error. What information is neccessary to help you to find the error?

1. user warning: Unknown column 'cd.profile_p_i_s' in 'field list' query: SELECT DISTINCT cd.uid as uid_key, cd.name, cd.profile_p_i_s, cd.profile_p_i_a, cd.profile_p_i_b, cd.profile_persoenliche_i_c FROM site_user_list_table as cd WHERE cd.uid not in (0) ORDER BY cd.name ASC in /path/to/the/database.mysql.inc on line 172.

2. When I try to Regenerate Site User List

user warning: Table 'site_user_list_table' already exists query: CREATE TABLE site_user_list_table AS SELECT u.uid as uid, u.name as name, u.mail as mail, t_36.value as profile_p_i_s, t_37.value as profile_p_i_m_a, t_38.value as profile_p_i_m_s, t_44.value as profile_p_i_s_s FROM dp5users as u LEFT OUTER JOIN dp5profile_values as t_36 on (u.uid = t_36.uid and t_36.fid = 36) LEFT OUTER JOIN dp5profile_values as t_37 on (u.uid = t_37.uid and t_37.fid = 37) LEFT OUTER JOIN dp5profile_values as t_38 on (u.uid = t_38.uid and t_38.fid = 38) LEFT OUTER JOIN dp5profile_values as t_44 on (u.uid = t_44.uid and t_44.fid = 44) WHERE u.status = 1 in /path/to/database.mysql.inc on line 172.

After I removed and deinstalled the module, I installed it again.
When I click to enable the module and the site reloads, I will get this error message:

user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OR REPLACE VIEW site_user_list_view AS SELECT u.uid as uid, u.name as name, u.ma' at line 1 query: CREATE OR REPLACE VIEW site_user_list_view AS SELECT u.uid as uid, u.name as name, u.mail as mail FROM dp5users as u WHERE u.status = 1 in /path/to/includes/database.mysql.inc on line 172.
user warning: Table 'site_user_list_table' already exists query: CREATE TABLE site_user_list_table AS SELECT u.uid as uid, u.name as name, u.mail as mail FROM dp5users as u WHERE u.status = 1 in /path/to/includes/database.mysql.inc on line 172.

Hi! What version of MySQL are you using?

Also, try going in and dropping site_user_list_view and site_user_list_table before going to regen.

frerin, kip mentioned he has a db_preffix set in the settings.php file; do you as well? Check the DB directly - is the table correcly named <db_pref>_site_user_list_table or just site_user_list_table?

If you do use a db_prefix, then the cause of your problem is probably the same as http://drupal.org/node/134897 - there is a patch there to solve the problem. Off hand it wouldn't be obvious that the db_pref bug would cause this error ("table already exists"), but it does.

Getting the same thing using the most recent version of the module and drupal 5.1

user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\site_user_list_view AS SELECT u.uid as uid, u.name as name, u.mail as mail FROM' at line 1 query: CREATE OR REPLACE VIEW \site_user_list_view AS SELECT u.uid as uid, u.name as name, u.mail as mail FROM users as u WHERE u.status = 1 in \includes\database.mysql.inc on line 172.

I have views installed and when trying to switch between views and table I get the same error.

Hi!

First, the view that I refer to in this module is not at all related to the Views module — a database view is kind of like an imaginary table that is defined by other tables. It allows me to define a "table" across the users and their profile fields that is always up-to-date with the rest of the database because the "table" I select from is really just a stored SQL query (which the database engine can make very efficient to select from).

That being said, what version of PHP are you using? And what version of MySQL are you using?

Ricky

Also, you might try getting the latest version (either from CVS, or once the tarball is updated) — the CVS id should be 1.1.2.6.2.11.

Drupal 5.1
MySQL database 5.0.27
PHP 5.2.1

After using the files from the CVS the errors I got on the settings page and after installation disappeared, however on the user search page I still get

user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM site_user_list_table as cd WHERE cd.uid not in (0) ORDER BY cd.name ASC' at line 1 query: SELECT DISTINCT cd.uid as uid_key, cd.name (FROM site_user_list_table as cd WHERE cd.uid not in (0) ORDER BY cd.name ASC in \includes\database.mysql.inc on line 172.

Sorry about that; try version 1.1.2.6.2.12, just committed.

Works perfectly, thank you very much!

I think this is a case closed.

I got this error when I installed the module:
user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OR REPLACE VIEW site_user_list_view AS SELECT u.uid as uid, u.name as name, u.ma' at line 1 query: CREATE OR REPLACE VIEW site_user_list_view AS SELECT u.uid as uid, u.name as name, u.mail as mail FROM users as u WHERE u.status = 1 in /var/MYPATH/includes/database.mysql.inc on line 120.

Drupal Version 4.7.3
Apache Version 1.3.27
PHP Version 5.1.2
MySQL Version 4.1.9-standard-log

That being said, the module seems to be working fine. Thanks for all the hard work!

Hi! This is a known issue, and not really a problem. When the 4.7.x version is installed, it first tries to create a view. If this fails, it falls back to a table. However, that initial creation of a view statement causes this error to be logged.