The mailing list for listmasters using Sympa

[Dev List <address@concealed>]

Appears this is resolved now.

mysql> alter table list_table drop column editors_list;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

After "sympa.pl --sync_list_db" and checking sympa.log, errors still there.

(HY000) Field 'owners_list' doesn't have a default value

(HY000) Field 'path_list' doesn't have a default value

(HY000) Field 'subject_list' doesn't have a default value

So removed those columns from the list_table also and re-ran sync_list_db after each removal unit no errors.  The list_table is now populated.

I don't know how I missed the DB schema being changed; I've always run "sympa.pl --upgrade".  Maybe I missed seeing an error on the screen during a version upgrade.

Cheers 

---

From: address@concealed <address@concealed> on behalf of Stefan Hornburg (Racke) <address@concealed>
Sent: Saturday, May 11, 2019 12:59 PM
To: address@concealed
Subject: Re: [sympa-users] rebuilding the list_table DB getting error "(HY000) Field 'editors_list' doesn't have a default value".

 

On 5/11/19 6:50 PM, Dev List wrote:
>
> Thanks for the reply.
>
>
> So "perl /home/sympa/bin/sympa.pl --health_check" should of detected this DB schema anomaly and corrected it?

In my opinion it should have at least warned you. In my opinion what --health-check does is really
--health-check-and-push-patient-to-operating-theatre-without-asking.

All corrections should be done by --upgrade or some other commandline flag.

>
>
> I suppose I have to manually remove the non-needed column so the list DB rebuild completes successfully?
>

In my opinion yes, but if you have valid data in there, it will be removed due. So take care.

Regards
        Racke

>
> Thanks again Racke
>
>
>
>
> From: address@concealed <address@concealed> on behalf of Stefan Hornburg (Racke) <address@concealed>
> Sent: Saturday, May 11, 2019 8:49 AM
> To: address@concealed
> Subject: Re: [sympa-users] rebuilding the list_table DB getting error "(HY000) Field 'editors_list' doesn't have a default value".
>    
> On 5/9/19 4:39 PM, Dev List wrote:
>> I am having difficulties rebuilding the "list_table" DB, getting error "(HY000) Field 'editors_list' doesn't have a default value".
>
> Hello,
>
> this field has been removed long ago from the list_table, so Sympa doesn't consider it when preparing the insert
> statement. MySQL complains because this field *requires* a value.
>
> Regards
>           Racke
>
>>
>>
>> I set log_level to 4
>>
>> # perl /home/sympa/bin/sympa.pl --version
>> Sympa 6.2.42
>>
>> # perl /home/sympa/bin/sympa.pl --health_check
>> (no output)
>>
>> # perl /home/sympa/bin/sympa.pl --sync_list_db --list="address@concealed"
>> (no output)
>>
>> # tail /var/log/sympa/sympa.log
>>
>> May  9 10:25:34 ET1772xubuntu sympa[2300]: debug3 Sympa::Database::do_prepared_query() Will perform query "UPDATE list_table SET status_list = ?, name_list = ?, robot_list = ?, family_list = ?, creation_epoch_list = ?, creation_email_list = ?, update_epoch_list  = ?, update_email_list = ?, searchkey_list = ?, web_archive_list = ?, topics_list = ? WHERE robot_list = ? AND name_list = ?"
>> May  9 10:25:34 ET1772xubuntu sympa[2300]: debug3 Sympa::Database::do_prepared_query() Did not find prepared statement for UPDATE list_table SET status_list = ?, name_list = ?, robot_list = ?, family_list = ?, creation_epoch_list = ?, creation_email_list  = ?, update_epoch_list = ?, update_email_list = ?, searchkey_list = ?, web_archive_list = ?, topics_list = ? WHERE robot_list = ? AND name_list = ?. Doing it
>> May  9 10:25:34 ET1772xubuntu sympa[2300]: debug3 Sympa::Database::do_prepared_query() Will perform query "INSERT INTO list_table (status_list, name_list, robot_list, family_list, creation_epoch_list, creation_email_list, update_epoch_list, update_email_list,  searchkey_list, web_archive_list, topics_list) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
>> May  9 10:25:34 ET1772xubuntu sympa[2300]: debug3 Sympa::Database::do_prepared_query() Did not find prepared statement for INSERT INTO list_table (status_list, name_list, robot_list, family_list, creation_epoch_list, creation_email_list, update_epoch_list,  update_email_list, searchkey_list, web_archive_list, topics_list) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?). Doing it
>> May  9 10:25:34 ET1772xubuntu sympa[2300]: debug3 Sympa::Database::connect(Sympa::DatabaseDriver::MySQL <db_host=localhost;db_name=sympa;db_port=3306;db_user=sympa>)
>> May  9 10:25:34 ET1772xubuntu sympa[2300]: debug3 Sympa::Database::connect() Connection to database Sympa::DatabaseDriver::MySQL <db_host=localhost;db_name=sympa;db_port=3306;db_user=sympa> already available
>> May  9 10:25:34 ET1772xubuntu sympa[2300]: err main::#1023 > Sympa::List::_update_list_db#9260 > Sympa::Database::do_prepared_query#382 Unable to execute SQL statement "INSERT INTO list_table (status_list, name_list, robot_list, family_list, creation_epoch_list,  creation_email_list, update_epoch_list, update_email_list, searchkey_list, web_archive_list, topics_list) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)": (HY000) Field 'editors_list' doesn't have a default value
>> May  9 10:25:34 ET1772xubuntu sympa[2300]: err main::#1023 > Sympa::List::_update_list_db#9292 Unable to update list Sympa::List <address@concealed> in database
>>
>>
>> mysql> desc list_table;
>> +---------------------+----------------------------------------------------------------+------+-----+---------+-------+
>> | Field               | Type                                                           | Null | Key | Default | Extra |
>> +---------------------+----------------------------------------------------------------+------+-----+---------+-------+
>> | creation_email_list | varchar(100)                                                   | NO   |     | NULL    |       |
>> | creation_epoch_list | int(11)                                                        | YES  |     | NULL    |       |
>> | editors_list        | varchar(100)                                                   | NO   |     | NULL    |       |
>> | name_list           | varchar(100)                                                   | NO   | PRI | NULL    |       |
>> | owners_list         | varchar(100)                                                   | NO   |     | NULL    |       |
>> | path_list           | varchar(100)                                                   | NO   |     | NULL    |       |
>> | robot_list          | varchar(100)                                                   | NO   | PRI | NULL    |       |
>> | status_list         | enum('open','closed','pending','error_config','family_closed') | NO   |     | NULL    |       |
>> | subject_list        | varchar(100)                                                   | NO   |     | NULL    |       |
>> | topics_list         | varchar(255)                                                   | YES  |     | NULL    |       |
>> | web_archive_list    | tinyint(1)                                                     | NO   |     | NULL    |       |
>> | family_list         | varchar(50)                                                    | YES  |     | NULL    |       |
>> | searchkey_list      | varchar(255)                                                   | YES  |     | NULL    |       |
>> | total_list          | int(7)                                                         | YES  |     | NULL    |       |
>> | update_email_list   | varchar(100)                                                   | YES  |     | NULL    |       |
>> | update_epoch_list   | int(11)                                                        | YES  |     | NULL    |       |
>> +---------------------+----------------------------------------------------------------+------+-----+---------+-------+
>> 16 rows in set (0.00 sec)
>>
>>
>> mysql> select * from list_table;
>> Empty set (0.00 sec)
>>
>>
>
>


--
Ecommerce and Linux consulting + Perl and web application programming.
Debian and Sympa administration. Provisioning with Ansible.