make_project fails on MySQL 8.0 in alter table commands

[mdatre]

Describe the bug
make_project script fails with error when running on MySQL 8.0 shipped with Ubuntu 20.04 LTS distro.

File "./make_project", line 234, in
project.install_project()
File "/work/boinc_src/py/Boinc/setup_project.py", line 612, in install_project
drop_first = options.drop_db_first
File "/work/boinc_src/py/Boinc/database.py", line 293, in create_database
_execute_sql_script(cursor, os.path.join(srcdir, 'db', file))
File "/work/boinc_src/py/Boinc/database.py", line 279, in _execute_sql_script
cursor.execute(query)
File "/home/user1/.local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 209, in execute
res = self._query(query)
File "/home/user1/.local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 315, in _query
db.query(q)
File "/home/user1/.local/lib/python2.7/site-packages/MySQLdb/connections.py", line 239, in query
_mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1178, "The storage engine for the table doesn't support descending indexes")

Steps To Reproduce

1. Run Ubuntu 20.04 LTS
2. Run MySQL 8.0 (standard shipped with Ubuntu 20.04 repositories, no special config options set in my.cnf).
3. Compile Boinc sources
4. Run "make_project" script with standard options.

Expected behavior
Creation and alteration of the DB tables should happen without error, as it was on older MySQL versions shipped with older Ubuntu distributions. Default engine is expected to be InnoDB in MySQL 8.0.

System Information

• OS: Linux Ubuntu 20.04 LTS
• BOINC Version: 7.17.0

Additional context
Changing engine to InnoDB in "create table team" command in /db/schema.sql passes the command successfully, but then again fails in "alter table user" of db/constraints.sql with error

Traceback (most recent call last):
File "./make_project", line 234, in
project.install_project()
File "/work/boinc_src/py/Boinc/setup_project.py", line 612, in install_project
drop_first = options.drop_db_first
File "/work/boinc_src/py/Boinc/database.py", line 293, in create_database
_execute_sql_script(cursor, os.path.join(srcdir, 'db', file))
File "/work/boinc_src/py/Boinc/database.py", line 279, in _execute_sql_script
cursor.execute(query)
File "/home/user1/.local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 209, in execute
res = self._query(query)
File "/home/user1/.local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 315, in _query
db.query(q)
File "/home/user1/.local/lib/python2.7/site-packages/MySQLdb/connections.py", line 239, in query
_mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1795, 'InnoDB presently supports one FULLTEXT index creation at a time')

[mdatre]

Was this just a change in "engine=InnoDB" in schema.sql or something more too? If only this change, then I tried that, and yet make_project failed with the following error in this SQL command on line 22 of db/constraints.sql.

alter table team add unique(name), add fulltext index team_name_desc(name, description), add fulltext index team_name(name), add index team_avg (expavg_credit desc), -- db_dump.C add index team_tot (total_credit desc), -- db_dump.C add index team_userid (userid);

Traceback (most recent call last):
File "./make_project", line 234, in
project.install_project()
File "/work/boinc_src/py/Boinc/setup_project.py", line 612, in install_project
drop_first = options.drop_db_first
File "/work/boinc_src/py/Boinc/database.py", line 293, in create_database
_execute_sql_script(cursor, os.path.join(srcdir, 'db', file))
File "/work/boinc_src/py/Boinc/database.py", line 279, in _execute_sql_script
cursor.execute(query)
File "/home/user1/.local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 209, in execute
res = self._query(query)
File "/home/user1/.local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 315, in _query
db.query(q)
File "/home/user1/.local/lib/python2.7/site-packages/MySQLdb/connections.py", line 239, in query
_mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1795, 'InnoDB presently supports one FULLTEXT index creation at a time')

[mdatre]

Further just to confirm that this is happening due to expectation mismatch between Boinc SQL script commands and latest MySQL 8.0, I started a local instance of MySQL 5.x, made a small change in db_base.py as follows by adding read_default_file and read_default_group options to MySQL connector handle to pick up the MySQL 5.x server connection.

dbconnection = MySQLdb.connect(db=db, host=host, port=port, user=user, passwd=passwd, cursorclass=MySQLdb.cursors.DictCursor, read_default_file='~/.my.cnf', read_default_group="client")

And then make_project script worked like before.

[davidpanderson]

The PR removes the 2nd fulltext index on the "team" table, and fixes that error.

[davidpanderson]

With MySQL 5.x, what happens if you don't modify db_base.py?

[mdatre]

With MySQL 5.x, what happens if you don't modify db_base.py?

It fails to connect to MySQL 5.x server instance showing Unix socket not found error. I think "make_project" can take MySQL socket file or my.cnf path as a command prompt option like db_user db_passwd etc, to avoid such hard-coded values. That will also allow user to choose any MySQL instance from multiple installed.

[AenBleidd]

@davidpanderson, are you sure this issue is fixed? Looks like the original issue is still there according to the comment above...

[davidpanderson]

I've tested it with MariaDB 10.1 (compatible with MySQL 5.x) and MariaDB 10.5 (current).