Jing Luo wrote:
> To Bob: I mentioned many months ago that some SQL errors (including but
> maybe not limited to STRICT_TRANS_TABLES being on by default) would happen
> because of the newer version of MariaDB, which I tested based on Savane 3.12
> and MariaDB 10.11. I was unable to fix those at the time. Then I forgot to
> remind you of it when the migration happened.
I am sure you did warn about this. Unfortunately I have slept since
then and forgotten those details.
Thank you for alerting me to the problem on IRC this morning so that
we could get this fixed for the users soonest! That was great!
> ------8<-----cut-here------8<-------
> db_query: SQL query error in [ INSERT INTO `user` (`user_name`, `user_pw`,
> `status`, `realname`, `email`, `add_date`, `confirm_hash`) VALUES
> ('jingbot', '$y$salt$blob', 'P', 'jingbot', 'fakeemail@jing.rocks',
> 1721274927, 'deadbeef')] <i>Field 'people_resume' doesn't have a default
> value</i>
> ------8<-----cut-here------8<-------
>
> A temporary workaround would to to disable STRICT_TRANS_TABLES globally.
The root cause of the exception is this:
> Field 'people_resume' doesn't have a default value
Here is the important parts from the table schema.
MariaDB [savane]> show columns from user;
+-----------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra
|
+-----------------------+--------------+------+-----+---------+----------------+
| user_id | int(11) | NO | PRI | NULL |
auto_increment |
| user_name | varchar(33) | NO | MUL | NULL |
|
...
| people_resume | text | NO | | NULL |
|
The Null column indicates that people_resume cannot be NULL. The
Default value is NULL. It must have a value supplied for it.
It appears that previous versions of MySQL ignored this condition.
Because certainly the PHP code as indicated above has not ever set
that column to a non-null value. This seems to be a bug that has been
hanging around for a long time. I think MariaDB is justified in
throwing the exception for this error case.
To fix this I removed the requirement that the field be non-null.
It's nice if people fill in something there. Tell us a little
something about yourself! But it has not been required. Therefore
the table schema requiring it is out of sync with our practice.
MariaDB [savane]> ALTER TABLE user MODIFY people_resume text;
Query OK, 0 rows affected (9.317 sec)
Records: 0 Duplicates: 0 Warnings: 0
[[ I initially tried it on the standby system on the now throwaway
copy of the database. I used a slightly different syntax. And had a
completely different result!
MariaDB [savane]> ALTER TABLE user MODIFY people_resume text NULL;
Query OK, 27351 rows affected (1.469 sec)
Records: 27351 Duplicates: 0 Warnings: 0
That felt pretty scary! I was glad I had the non-critical discardable
spare around for this test. I researched the syntax a little more and
allowing null being the default I changed to the other syntax above
and that avoided the updating 27351 which did not need to be updated.
And not sure exactly what it did, I think it simply "updated" the row
without actually doing anything, as I could not detect a problem
afterward. It's good to have a development system for testing. ]]
> (I don't quite understand what the requester meant by "direct messages" and
> "propagate amongst the different servers".)
I think they meant email. I think they meant there was no way to send
an email to the person they wanted to contact. That the only way to
make contact was to submit a ticket to them through Savannah's ticket
tracker. They did not say what project they are wanting to submit a
ticket to and therefore I don't know either. But I presume that
unnamed project has no email for them and no mailing list either.
And then they are under the impression that the Puszcza instance of
the savane software is federated with Savannah. That's not the case.
Thanks again for the help in getting this problem resolved quickly!
Note that there is one more database action in the queue for today.
Having upgraded the internal1 database server to the current Trisquel
and MariaDB I am migrating the database back from internal2 over to
internal1 again. It's been happy there for many years. This couples
it more tightly to the git server which is very busy. internal1 has
twice the memory and cpus allocated compared to internal2. And then
we will be up to date until the next version of Trisquel releases.
Bob
