1. Forum
  3. Joomla
  5. Issue Tracker
  7. Update from 1.5.2 to 1.6.0 produces database error

Update from 1.5.2 to 1.6.0 produces database error

10 years 5 days ago - 10 years 5 days ago #7 by Ray
Replied by Ray on topic Update from 1.5.2 to 1.6.0 produces database error
The answer to the newer mystery was already answered in my previous post, but in retrospect, I'm sure I didn't make it clear enough.

I developed the site locally, in XAMPP, using the prefix lti25b_. During the development, I updated the extension at least once, if not more. When I uploaded the site to the production server, I changed the prefix to ltimp_. It appears that at least this one item was not adjusted during the Import of the database. Now that I'm trying to update the extension again, that unlikely rare glitch seems to have reared its ugly head, revealing itself to me (us) for the first time.

I went poking around in phpmyadmin, brought up the ltimp_it_attachment table, switched to Structure view and then on the "Relation View" link just under the table display. It was there that I found the place to edit the incorrect reference to the old 'local' dev server prefix. I changed it from `lti25b_it_attachment_issue_id_fk' to `ltimp_it_attachment_issue_id_fk', saved the changes and then attempted to install the update again.

This time, it was able to update!, although there was a single line present that I'm not quite sure how to process.

"Insufficient database privileges to create database triggers. Skipping trigger creation. This means that the fall-back setting of default values at the database level will not happen. This should not impact normal operation of the component."

I have not yet tested the extension or even looked at it from the back-end, wanting to get this message to you while the "update" screen still existed. If I discover anything more to add to the above, I'll be back sooner than later.


Edit:
All front and back-end testing shows no negative issues, although I'm not quite sure what to look for or specifically test to see how the above 'triggers' issue affects anything. It also doesn't make sense to me that there would be any database permissions issues, as Joomla is being run on a VPN, rather than a shared hosting plan.

Please Log in or Create an account to join the conversation.

10 years 5 days ago #8 by geoffc
Replied by geoffc on topic Update from 1.5.2 to 1.6.0 produces database error
That makes things fall into place and adds the explaination of what I suspected. Knowing the previous history often reveals things which may on the surface appear unimportant.

When you restored the database or uploaded it to the production server, not all of the prefixes were changed. I do not wish to go into a deep explanation of database objects, but basically there are tables, indexes, constraints, triggers, views, routines etc (in MYSQL parlance), and all (can) make use of the 'prefix'. Issue tracker makes use of all of these database types IF it can to improve reliability, performance etc. It specifically checks on update/install whether it can create and use these object and it if can it uses them, if it can't it uses PHP alternative routines instead.

The ability to be able to use these database 'objects' is given by privileges in the database. It is completely unrelated to where the databse is located. Typically database privileges are used to control what connected users can or cannot do when they are connected to the database. On a 'private' database, i.e. one that only you connect to, then it is common that you are granted 'all' of the privileges. On a shared database then only certain privileges may be granted. All users of Joomla connect through a 'single' user to the database. It is this 'single' connection user that has the privileges to create tables, routines, triggers etc. which are all separate privileges that can be granted.

The message on the update was because the specific user that Joomla is using to connect to the database does not have the privilege to 'CREATE TRIGGERS' in the database. Look in the information schema User and/or System privs tables. They may be granted in either, depending upon whether it is system(database) wide or user granted specific permission. If you 'own' the database then you can 'GRANT' these permissions to the Joomla connection user. i.e. the user that Joomla uses to talk to the databazse. Once granted you could safely install the Issue Tracker update again and it will create the triggers. The triggers are used to provide a 'backup' to ensure that the audit fields in all of the tables are always populated, and the 'Advanced auditing feature' which you possible do not use, also exclusively uses triggers. It is not necessary to use the triggers (except for advanced auditing) as generally the PHP table store routines do a very good job on their own.

Re the table suffixes. Joomla uses the 'suffix' to keep track of all the database object associated with the Joomla instance. Since the table suffixes were changed on the 'move' to production, it would all work correctly. The names of the foreign keys, and indexes are just that 'names' and have no specific meaning in the database. The problem comes when they are 'mis-aligned' because the component update cannot 'know' that they have the 'wrong' names (as far as it is concerned), hence the original error.

Renaming the constraint/index combination resolved the problem and enabled the update to complete. The trigger problem is unrelated as mentioned above and is a database 'permissions' problem.

I believe that none of these problems would cause the component to work incorrectly. The constraint change was to add more functionality, and the triggers absence merely means that some of the auditing might (and I repeat might) not be filled in, which may or may not be a problem for your audit tracking. i.e who changed what and when, who made the last change etc. which is not generally a concern on a small one or two person installation.

I suspect that you now know a lot more about MySQL that you did before, or even perhaps wanted to know.

If you are interested in some of the internals of Issue Tracker or wish to know more there is a design guide which you can accesss on this site IF you are logged in. It is available only to registered users as a PDF or as ordinary web pages. It is reasonably up to date and expains the table structures etc in some detail.

I trust that this answers your problem and am glad that your problem(s) are now resolved.
Regards
Geoff
The following user(s) said Thank You: Ray

Please Log in or Create an account to join the conversation.

10 years 5 days ago - 10 years 5 days ago #9 by Ray
Replied by Ray on topic Update from 1.5.2 to 1.6.0 produces database error
Thanks for the mini-lesson. It's much appreciated!
Some of it I knew, but most of it more vaguely than now. ;)

My information_schema has a USER_PRIVILEGES section, but not one for SYSTEM_PRIVILEGES. I have yet to locate a mechanism for adjusting permissions, although I'm logged into PMA as root. The views available within it seem to be for display only.


On a possibly related note, I'm aware that the following is beyond anything needed to help me install the update, but I felt you might find it interesting.

On further investigation, I discovered that all of the Foreign key constraints in the ltimp_it_issues and the ltimp_it_people tables were also pointed to the old, local lti25b_ prefix - now adjusted.

However, the ltimp_it_progress table includes one that points to "idx_progress_issue_id_fk". I have never used or seen this idx_ prefix before, so I'm wondering if it's intentionally part of the extension.

Please Log in or Create an account to join the conversation.

10 years 5 days ago - 10 years 1 day ago #10 by geoffc
Replied by geoffc on topic Update from 1.5.2 to 1.6.0 produces database error
Sorry I should have said SCHEMA_PRIVILEGES, old habits die hard.

There are two system databases that implement or report on privileges: the database called "mysql", and another called INFORMATION_SCHEMA. Although it's possible to interact with the mysql database in order to set privileges, normally one sets privileges using the GRANT command.

Here is a link to the GRANT syntax and the available privileges.

There are two main tables to look in the INFORMATION_SCHEMA, the USER_PRIVILEGES and the SCHEMA_PRIVILEGES. Have a look at their contents by running a simple select statement on the tables.

Re the index and FK on the progress table. The difference is because I have changed my naming convension. There are pro's and con's which ever one chooses and generally it doesn't matter too much since as I said in an earlier post they are just 'names' and the significance is only what I place upon them. The progress table didn't exist prior to 1.6.0. You will also find the prefix used in the project table as well as the two you have already identified.

Yours was the first specific problem report I have seen related to the naming convension, although I recall long discussions with Akeeba several years ago on the topic. Akeeba handles the prefix renaming without problem, but not all backup/restore do as you have discovered.

Whilst looking around the DB you will also probably notice that there are not many components making use of anything other than basic tables and a primary key index. Most (all) other components tend to prefer to do all the processing and maintain data integrity in the web/application server, somthing which I thing is missing a trick. Still I made ablog post some time ago on that particular rant. :-)
Regards
Geoff

Please Log in or Create an account to join the conversation.

  1. Forum
  3. Joomla
  5. Issue Tracker
  7. Update from 1.5.2 to 1.6.0 produces database error
Time to create page: 0.141 seconds
Go To Top

Joomla! Debug Console

Session

Profile Information

Memory Usage

Database Queries