The mailing list for listmasters using Sympa

[Troy Davis <address@concealed>]

Hello,

I'm new to Sympa, and I'm running into problems when starting it. The problem looks the same with 5.3b.4 and 5.2.4. Here's some system stats:

- Fedora Core release 4 (Stentz)

- mysql-4.1.20-1.FC4.1

- perl-5.8.6-24

- DBI 1.54

- DBD::mysql 4.003

The latest versions of the perl modules are installed.

So everything goes fine throughout ./configure && make && make install, but when I start it up initially, I see this:

# /etc/init.d/sympa start

Starting Sympa subsystem:

Starting module sympa.pl: DBD::mysql::db do failed: Can't DROP 'PRIMARY'; check that column/key exists at /usr/local/sympa-stable/bin/Upgrade.pm line 1000.

DBD::mysql::db do failed: Can't DROP 'list_admin'; check that column/key exists at /usr/local/sympa-stable/bin/Upgrade.pm line 1019.

DBD::mysql::db do failed: Can't DROP 'robot_admin'; check that column/key exists at /usr/local/sympa-stable/bin/Upgrade.pm line 1019.

DBD::mysql::db do failed: Can't DROP 'user_admin'; check that column/key exists at /usr/local/sympa-stable/bin/Upgrade.pm line 1019.

DBD::mysql::db do failed: Can't DROP 'role_admin'; check that column/key exists at /usr/local/sympa-stable/bin/Upgrade.pm line 1019.

DBD::mysql::db do failed: Can't DROP 'PRIMARY'; check that column/key exists at /usr/local/sympa-stable/bin/Upgrade.pm line 1000.

DBD::mysql::db do failed: Can't DROP 'id_logs'; check that column/key exists at /usr/local/sympa-stable/bin/Upgrade.pm line 1019.

DBD::mysql::db do failed: Can't DROP 'PRIMARY'; check that column/key exists at /usr/local/sympa-stable/bin/Upgrade.pm line 1000.

DBD::mysql::db do failed: Can't DROP 'email_user'; check that column/key exists at /usr/local/sympa-stable/bin/Upgrade.pm line 1019.

DBD::mysql::db do failed: Can't DROP 'PRIMARY'; check that column/key exists at /usr/local/sympa-stable/bin/Upgrade.pm line 1000.

DBD::mysql::db do failed: Can't DROP 'list_subscriber'; check that column/key exists at /usr/local/sympa-stable/bin/Upgrade.pm line 1019.

DBD::mysql::db do failed: Can't DROP 'robot_subscriber'; check that column/key exists at /usr/local/sympa-stable/bin/Upgrade.pm line 1019.

DBD::mysql::db do failed: Can't DROP 'user_subscriber'; check that column/key exists at /usr/local/sympa-stable/bin/Upgrade.pm line 1019.

DBD::mysql::db do failed: Can't DROP 'PRIMARY'; check that column/key exists at /usr/local/sympa-stable/bin/Upgrade.pm line 1000.

DBD::mysql::db do failed: Can't DROP 'netid_netidmap'; check that column/key exists at /usr/local/sympa-stable/bin/Upgrade.pm line 1019.

DBD::mysql::db do failed: Can't DROP 'serviceid_netidmap'; check that column/key exists at /usr/local/sympa-stable/bin/Upgrade.pm line 1019.

DBD::mysql::db do failed: Can't DROP 'robot_netidmap'; check that column/key exists at /usr/local/sympa-stable/bin/Upgrade.pm line 1019.

                                                           [  OK  ]

Starting module archived.pl: Creating missing /usr/local/sympa-stable/arc directory

                                                           [  OK  ]

Starting module bounced.pl:                                [  OK  ]

Starting module task_manager.pl: Missing files. Aborting.

                                                           [FAILED]

-------

I noticed in MySQL's docs the following:

"DROP PRIMARY KEY drops the primary index. Note: In older versions of MySQL, if no primary index existed, DROP PRIMARY KEY would drop the first UNIQUE index in the table. This is not the case in MySQL 5.0, where trying to use DROP PRIMARY KEY on a table with no primary key results in an error"

This could explain the first error, but the rest are a mystery to me. Mysql's query log looks like this:

-------

070425 15:08:06   33962 Query       USE `sympa`

                  33962 Query       SET character_set_connection=@@character_set_database

                  33962 Query       select @@character_set_database

                  33962 Query       SET character_set_client=latin1

                  33962 Query       SET character_set_results=latin1

                  33962 Query       SET character_set_connection=@@character_set_database

                  33962 Query       USE `sympa`

                  33962 Query       SET character_set_connection=@@character_set_database

                  33962 Query       SHOW TABLE STATUS

070425 15:08:08   33962 Query       drop table `admin_table`

                  33962 Query       drop table `logs_table`

                  33962 Query       drop table `netidmap_table`

                  33962 Query       drop table `subscriber_table`

                  33962 Query       drop table `user_table`

                  33962 Query       USE `sympa`

                  33962 Query       SET character_set_connection=@@character_set_database

                  33962 Query       SHOW TABLE STATUS

070425 15:08:28   34134 Connect     sympa@localhost on sympa

                  34134 Query       set autocommit=1

                  34134 Query       SET NAMES 'utf8'

                  34134 Query       SELECT DATABASE()

                  34134 Query       SHOW /*!50002 FULL*/ TABLES FROM `sympa` LIKE '%'

                  34134 Query       CREATE TABLE admin_table (temporary INT)

                  34134 Query       CREATE TABLE logs_table (temporary INT)

                  34134 Query       CREATE TABLE user_table (temporary INT)

                  34134 Query       CREATE TABLE subscriber_table (temporary INT)

                  34134 Query       CREATE TABLE netidmap_table (temporary INT)

                  34134 Query       SHOW FIELDS FROM admin_table

                  34134 Query       SHOW FIELDS FROM logs_table

                  34134 Query       SHOW FIELDS FROM user_table

                  34134 Query       SHOW FIELDS FROM subscriber_table

                  34134 Query       SHOW FIELDS FROM netidmap_table

                  34134 Query       ALTER TABLE admin_table ADD comment_admin varchar(150)

                  34134 Query       ALTER TABLE admin_table DROP temporary

                  34134 Query       ALTER TABLE admin_table ADD date_admin datetime NOT NULL

                  34134 Query       ALTER TABLE admin_table ADD include_sources_admin varchar(50)

                  34134 Query       ALTER TABLE admin_table ADD included_admin int(1)

                  34134 Query       ALTER TABLE admin_table ADD info_admin varchar(150)

070425 15:08:29   34134 Query       ALTER TABLE admin_table ADD list_admin varchar(50) NOT NULL

                  34134 Query       ALTER TABLE admin_table ADD profile_admin enum('privileged','normal')

                  34134 Query       ALTER TABLE admin_table ADD reception_admin varchar(20)

                  34134 Query       ALTER TABLE admin_table ADD robot_admin varchar(80) NOT NULL

                  34134 Query       ALTER TABLE admin_table ADD role_admin enum('listmaster','owner','editor') NOT NULL

                  34134 Query       ALTER TABLE admin_table ADD subscribed_admin int(1)

                  34134 Query       ALTER TABLE admin_table ADD update_admin datetime

                  34134 Query       ALTER TABLE admin_table ADD user_admin varchar(100) NOT NULL

                  34134 Query       ALTER TABLE admin_table DROP PRIMARY KEY

                  34134 Query       ALTER TABLE admin_table ADD PRIMARY KEY (list_admin,robot_admin,user_admin,role_admin)

                  34134 Query       ALTER TABLE admin_table DROP INDEX list_admin

                  34134 Query       ALTER TABLE admin_table DROP INDEX robot_admin

                  34134 Query       ALTER TABLE admin_table DROP INDEX user_admin

                  34134 Query       ALTER TABLE admin_table DROP INDEX role_admin

                  34134 Query       ALTER TABLE admin_table ADD INDEX admin_table_index (list_admin,robot_admin,user_admin,role_admin)

                  34134 Query       ALTER TABLE logs_table ADD action_logs varchar(50) NOT NULL

                  34134 Query       ALTER TABLE logs_table DROP temporary

                  34134 Query       ALTER TABLE logs_table ADD client_logs varchar(100)

                  34134 Query       ALTER TABLE logs_table ADD daemon_logs varchar(10) NOT NULL

                  34134 Query       ALTER TABLE logs_table ADD date_logs int(11) NOT NULL

                  34134 Query       ALTER TABLE logs_table ADD error_type_logs varchar(150)

                  34134 Query       ALTER TABLE logs_table ADD id_logs bigint(20) NOT NULL

                  34134 Query       ALTER TABLE logs_table ADD list_logs varchar(50)

                  34134 Query       ALTER TABLE logs_table ADD msg_id_logs varchar(255)

                  34134 Query       ALTER TABLE logs_table ADD parameters_logs varchar(100)

                  34134 Query       ALTER TABLE logs_table ADD robot_logs varchar(80)

                  34134 Query       ALTER TABLE logs_table ADD status_logs varchar(10) NOT NULL

                  34134 Query       ALTER TABLE logs_table ADD target_email_logs varchar(100)

                  34134 Query       ALTER TABLE logs_table ADD user_email_logs varchar(100)

                  34134 Query       ALTER TABLE logs_table DROP PRIMARY KEY

                  34134 Query       ALTER TABLE logs_table ADD PRIMARY KEY (id_logs)

                  34134 Query       ALTER TABLE logs_table DROP INDEX id_logs

                  34134 Query       ALTER TABLE logs_table ADD INDEX logs_table_index (id_logs)

                  34134 Query       ALTER TABLE user_table ADD attributes_user text

                  34134 Query       ALTER TABLE user_table DROP temporary

                  34134 Query       ALTER TABLE user_table ADD cookie_delay_user int(11)

                  34134 Query       ALTER TABLE user_table ADD email_user varchar(100) NOT NULL

                  34134 Query       ALTER TABLE user_table ADD gecos_user varchar(150)

                  34134 Query       ALTER TABLE user_table ADD lang_user varchar(10)

                  34134 Query       ALTER TABLE user_table ADD password_user varchar(40)

                  34134 Query       ALTER TABLE user_table DROP PRIMARY KEY

                  34134 Query       ALTER TABLE user_table ADD PRIMARY KEY (email_user)

                  34134 Query       ALTER TABLE user_table DROP INDEX email_user

                  34134 Query       ALTER TABLE user_table ADD INDEX user_table_index (email_user)

                  34134 Query       ALTER TABLE subscriber_table ADD bounce_address_subscriber varchar(100)

                  34134 Query       ALTER TABLE subscriber_table DROP temporary

                  34134 Query       ALTER TABLE subscriber_table ADD bounce_score_subscriber smallint(6)

                  34134 Query       ALTER TABLE subscriber_table ADD bounce_subscriber varchar(35)

                  34134 Query       ALTER TABLE subscriber_table ADD comment_subscriber varchar(150)

                  34134 Query       ALTER TABLE subscriber_table ADD date_subscriber datetime NOT NULL

                  34134 Query       ALTER TABLE subscriber_table ADD include_sources_subscriber varchar(50)

                  34134 Query       ALTER TABLE subscriber_table ADD included_subscriber int(1)

                  34134 Query       ALTER TABLE subscriber_table ADD list_subscriber varchar(50) NOT NULL

                  34134 Query       ALTER TABLE subscriber_table ADD reception_subscriber varchar(20)

                  34134 Query       ALTER TABLE subscriber_table ADD robot_subscriber varchar(80) NOT NULL

                  34134 Query       ALTER TABLE subscriber_table ADD subscribed_subscriber int(1)

                  34134 Query       ALTER TABLE subscriber_table ADD topics_subscriber varchar(200)

                  34134 Query       ALTER TABLE subscriber_table ADD update_subscriber datetime

                  34134 Query       ALTER TABLE subscriber_table ADD user_subscriber varchar(100) NOT NULL

                  34134 Query       ALTER TABLE subscriber_table ADD visibility_subscriber varchar(20)

                  34134 Query       ALTER TABLE subscriber_table DROP PRIMARY KEY

                  34134 Query       ALTER TABLE subscriber_table ADD PRIMARY KEY (list_subscriber,robot_subscriber,user_subscriber)

                  34134 Query       ALTER TABLE subscriber_table DROP INDEX list_subscriber

                  34134 Query       ALTER TABLE subscriber_table DROP INDEX robot_subscriber

                  34134 Query       ALTER TABLE subscriber_table DROP INDEX user_subscriber

                  34134 Query       ALTER TABLE subscriber_table ADD INDEX subscriber_table_index (list_subscriber,robot_subscriber,user_subscriber)

                  34134 Query       ALTER TABLE netidmap_table ADD email_netidmap varchar(100)

                  34134 Query       ALTER TABLE netidmap_table DROP temporary

                  34134 Query       ALTER TABLE netidmap_table ADD netid_netidmap varchar(100) NOT NULL

                  34134 Query       ALTER TABLE netidmap_table ADD robot_netidmap varchar(80) NOT NULL

                  34134 Query       ALTER TABLE netidmap_table ADD serviceid_netidmap varchar(100) NOT NULL

                  34134 Query       ALTER TABLE netidmap_table DROP PRIMARY KEY

                  34134 Query       ALTER TABLE netidmap_table ADD PRIMARY KEY (netid_netidmap,serviceid_netidmap,robot_netidmap)

                  34134 Query       ALTER TABLE netidmap_table DROP INDEX netid_netidmap

                  34134 Query       ALTER TABLE netidmap_table DROP INDEX serviceid_netidmap

                  34134 Query       ALTER TABLE netidmap_table DROP INDEX robot_netidmap

                  34134 Query       ALTER TABLE netidmap_table ADD INDEX netidmap_table_index (netid_netidmap,serviceid_netidmap,robot_netidmap)

070425 15:08:30   34134 Quit      

070425 15:08:31   34135 Connect     sympa@localhost on sympa

                  34135 Query       set autocommit=1

                  34135 Query       SET NAMES 'utf8'

                  34135 Query       SELECT email_user AS email, gecos_user AS gecos, password_user AS password, cookie_delay_user AS cookie_delay, lang_user AS lang , attributes_user AS attributes FROM user_table WHERE email_user = 'address@concealed'

070425 15:08:32   34136 Connect     sympa@localhost on sympa

                  34136 Query       set autocommit=1

                  34136 Query       SET NAMES 'utf8'

                  34136 Quit      

                  34135 Query       SELECT email_user AS email, gecos_user AS gecos, password_user AS password, cookie_delay_user AS cookie_delay, lang_user AS lang , attributes_user AS attributes FROM user_table WHERE email_user = 'address@concealed'

070425 15:08:33   34135 Quit      

                  34137 Connect     sympa@localhost on sympa

                  34137 Query       set autocommit=1

                  34137 Query       SET NAMES 'utf8'

                  34137 Quit      

070425 15:08:34   34138 Connect     sympa@localhost on sympa

                  34138 Query       set autocommit=1

                  34138 Query       SET NAMES 'utf8'

                  34138 Query       SELECT email_user AS email, gecos_user AS gecos, password_user AS password, cookie_delay_user AS cookie_delay, lang_user AS lang , attributes_user AS attributes FROM user_table WHERE email_user = 'address@concealed'

070425 15:08:35   34138 Quit

-------

So I stop sympa, then try again:

-------

# /etc/init.d/sympa start

Starting Sympa subsystem:

Starting module sympa.pl: DBD::mysql::db do failed: Table 'admin_table' already exists at /usr/local/sympa-stable/bin/Upgrade.pm line 783.

DBD::mysql::db do failed: Table 'logs_table' already exists at /usr/local/sympa-stable/bin/Upgrade.pm line 783.

DBD::mysql::db do failed: Table 'user_table' already exists at /usr/local/sympa-stable/bin/Upgrade.pm line 783.

DBD::mysql::db do failed: Table 'subscriber_table' already exists at /usr/local/sympa-stable/bin/Upgrade.pm line 783.

DBD::mysql::db do failed: Table 'netidmap_table' already exists at /usr/local/sympa-stable/bin/Upgrade.pm line 783.

DBD::mysql::st execute failed: 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 '.`admin_table' at line 1 at /usr/local/sympa-stable/bin/Upgrade.pm line 806.

Database sympa defined in sympa.conf has not the right structure or is unreachable. If you don't use any database, comment db_xxx parameters in sympa.conf

                                                           [FAILED]

Starting module archived.pl:                               [  OK  ]

Starting module bounced.pl:                                [  OK  ]

Starting module task_manager.pl: Missing files. Aborting.

                                                           [FAILED]

-------

MySQL's log shows some syntax errors in the queries:

-------

070425 15:11:30   34140 Connect     sympa@localhost on sympa

                  34140 Query       set autocommit=1

                  34140 Query       SET NAMES 'utf8'

                  34140 Query       SELECT DATABASE()

                  34140 Query       SHOW /*!50002 FULL*/ TABLES FROM `sympa` LIKE '%'

                  34140 Query       CREATE TABLE admin_table (temporary INT)

                  34140 Query       CREATE TABLE logs_table (temporary INT)

                  34140 Query       CREATE TABLE user_table (temporary INT)

                  34140 Query       CREATE TABLE subscriber_table (temporary INT)

                  34140 Query       CREATE TABLE netidmap_table (temporary INT)

                  34140 Query       SHOW FIELDS FROM sympa`.`admin_table

                  34140 Quit      

070425 15:11:31   34141 Connect     sympa@localhost on sympa

                  34141 Query       set autocommit=1

                  34141 Query       SET NAMES 'utf8'

                  34141 Quit      

070425 15:11:32   34142 Connect     sympa@localhost on sympa

                  34142 Query       set autocommit=1

                  34142 Query       SET NAMES 'utf8'

                  34142 Quit      

070425 15:11:33   34143 Connect     sympa@localhost on sympa

                  34143 Query       set autocommit=1

                  34143 Query       SET NAMES 'utf8'

                  34143 Query       SELECT email_user AS email, gecos_user AS gecos, password_user AS password, cookie_delay_user AS cookie_delay, lang_user AS lang , attributes_user AS attributes FROM user_table WHERE email_user = 'address@concealed'

070425 15:11:34   34143 Quit

-------

Look for "SHOW FIELDS FROM sympa`.`admin_table" to see what I mean. I've fumbled a bit with Upgrade.pm attempting to fix the issue, but no luck so far. Has anyone else run into this?

Thank You,

Troy

__________________

Troy Davis

Chief Technology Officer

Metaphor Studio

538 Reading Road

Loft 200

Cincinnati, Ohio 45202

Tel: 513-723-0290

Fax: 513-723-0670

http://metaphorstudio.com