MariaDB errors initializing SMP

[johna]

Trac 	1.0.1
Genshi 	0.7 (without speedups)
MySQL 	server: "5.5.32-MariaDB", client: "5.1.54", thread-safe: 1
MySQLdb 	1.2.3c1
Pygments 	1.6
Python 	2.6.6 (r266:84292, Nov 22 2013, 12:16:22) [GCC 4.4.7 20120313 (Red Hat 4.4.7-4)]
pytz 	2013.8
setuptools 	0.6
Subversion 	1.7.4 (r1295709)
jQuery	1.7.2

After installation of the plugin and restart of the server, the database update fails. The culprit appears to be in line 154 of environmentSetup.py:

cursor.execute("""ALTER TABLE smp_project ADD restrict TEXT""")

This change appears to fix this issue. (adding backtiks to "restrict")

cursor.execute("""ALTER TABLE smp_project ADD `restrict` TEXT""")

Created a new environment and ran update. It indicated success, but none of the smp_ tables in the database have data.

[root@fw-svn01 Testing]# trac-admin /home/trac/Testing upgrade
Upgrading SimpleMultiProject database schema
SimpleMultiProject database schema version is 0, should be 5
Upgrade done.

You may want to upgrade the Trac documentation now by running:

  trac-admin /home/trac/Testing wiki upgrade

Additonally, there is the following error in the console.

ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'restrict\n                   FROM\n                        smp_project' at line 2")

This shows up in: Manage Projects -> Projects and the "Timeline", "Roadmap", "View Tickets", and "New Ticket" items.

I've not been able to test this against MySQL, but it may be that "restrict" is a restricted word for MariaDB.

[johna]

Apologies.

SMP version 0.0.4dev-py2.6

[johna]

I confirmed that the issue existed in MySQL 5.5.33

The issue appears to be the use of restrict as a column name in the smp_project table.

I renamed all instances of restrict to restrick in the following files:

admin.py
environmentSetup.py
model.py

Redid the whole thing and it appears to be working properly. Evidently restrict is a restricted word in mysql and MariaDB

[falkb]

Oh what a silly bug! It seems to me there's no other way than to rename it in an incompatible way for db schema version 5 of the plugin, and to force people, who upgraded already, to rename it manually. What do you think?

[johna]

Yeah. It is a problematic one to find this far down the roadmap.

Reserved words can be utilized as object names, but it gets kind of ugly. ​http://dev.mysql.com/doc/refman/5.5/en/identifiers.html

I tried enclosing the words in backticks, but python didn't like that at all and wouldn't compile the .egg

As a straight .sql file it ran just fine against the database with the reserved word(s) in backticks. You might consider packaging a .sql file for the next version and run that to change the row as needed.

Not pretty, but it might get the job done.

[Ryan J Ollos]

t11461.patch​ has been tested with SQLite and MySQL, and I believe it will fix the issue in a cross-db compatible way. Someone more familiar with the project should probably do some thorough testing before committing the change.