MySQL version, Key lengths and Issue Tracker

9 years 2 months ago - 9 years 2 months ago #7 by NF
I look in the script.php and in my database after I tried to install and cannot find the creation of the _it_ tables. Which script should create the specific _it_ tables? Maybe you had that standard tables already installed on your dev environment?

In other words, from which script and which program lines do you want us to extract the specific SQLcode to create the it_people table? Than I will modifying it for my database prefix, run it directly in myphpadmin and post the (myphpadmin) error message.

Kind regards,
Nico

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

9 years 2 months ago #8 by geoffc
The script is located in the admin/sql directory and named install.mysql.utf8.sql.

I have taken the opportunity to enclose it below.
CREATE TABLE IF NOT EXISTS `#__it_people` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'The system generated unique identifier for the person.',
  `user_id` int COMMENT 'The user identifier as recorded in the Joomla user table.',
  `person_name` varchar(255) NOT NULL COMMENT 'The unique name of the person.',
  `alias` varchar(10) DEFAULT NULL COMMENT 'Person Alias.  Used to mask primary key of person from random selection.',
  `person_email` varchar(100) NOT NULL COMMENT 'The email address of the person.',
  `person_role` int(11) NOT NULL COMMENT 'The role the person plays within the company.',
  `username` varchar(150) NOT NULL COMMENT 'The username of this person. Used to link login to person details.',
  `phone_number` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci COMMENT 'User phone number.',
  `assigned_project` int UNSIGNED DEFAULT NULL COMMENT 'The project that the person is currently assigned to.',
  `issues_admin` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Indicates that the person is an Issues administrator.',
  `staff` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Indicates that the person is a member of staff.',  
  `email_notifications` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Person has requested email notifications when their raised issues are changed.',
  `sms_notify` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Person receives SMS notifications when their issues are changed.',
  `registered` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether user is registered.',
  `published` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether visible in the front end.',
  `ordering` int(11) NOT NULL DEFAULT '0' COMMENT 'Order in which people are presented.',
  `checked_out` INT(11) NOT NULL DEFAULT '0' COMMENT 'Checked out indicator.  User id of user editing the record.',
  `checked_out_time` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Time and date when the record was checked out.',
  `created_on` datetime NOT NULL COMMENT 'Audit Column: Date the record was created.',
  `created_by` varchar(255) NOT NULL COMMENT 'Audit Column: The user who created the record.',
  `modified_on` datetime DEFAULT NULL COMMENT 'Audit Column: Date the record was last modified.',
  `modified_by` varchar(255) DEFAULT NULL COMMENT 'Audit Column: The user who last modified the record.',
  PRIMARY KEY (`id`),
  UNIQUE KEY `#__it_people_userid_uk` (`user_id`),
  UNIQUE KEY `#__it_people_username_uk` (`username`),
  KEY `#__it_people_phone_number_uk` (phone_number),
  KEY `#__it_people_project_fk` (`assigned_project`),
  KEY `#__it_people_role_fk` (`person_role`),
  KEY `#__it_people_name_uk` (`person_name`(150),`person_email`(90))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=31 COMMENT='All people within the company.';

I have removed the two included constraint lines since they would cause a problem if the referred to tables didn't exist.

Regards
Geoff

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

9 years 2 months ago #9 by geoffc
Further to my earlier reply. The two CONSTRAINT lines, which are present in the main install SQL script work fine in that situation since the referred to tables would have been created earlier in the script running. When run stand alone I cannot be sure that they do exist hence thier removal in my post entry.

My tested had been done on a 'fresh' install so I am sure that in my tests the tables did not exist prior to the install.
This is proving to be an annoying error impacting a few (2 or possibly 3 known) that is most likely caused by either the MySQL version installed or some specific database option. Given the number of versions and possible option combinations it is like finding a needle in a haystack!

Regards
Geoff

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

9 years 2 months ago - 9 years 2 months ago #10 by NF
Running that query (with only 1 change in first line for the prefix #__ to prefix_) in PHPmyAdmin gives no errors and creates the table.

After that I tried to install the 1.6.6 again and now he gives an table not excist error for the next table _it_issues:
1146 Table 'DBxxx.prefix_it_issues' doesn't exist SQL=UPDATE prefix_it_people SET staff = 1 WHERE user_id IN (SELECT distinct assigned_to_person_id FROM prefix_it_issues)

So I think it doesn´t run the script admin/sql/install.mysql.utf8.sql.

My versions are:
MySQL Versie 5.5.40
PHP gebouwd op SunOS localhost 5.10 Generic_142901-13 i86pc
Database versie 5.5.40-log
Database collatie latin1_german1_ci
PHP versie 5.3.29
Webserver Apache/2.2.29 (Unix)
WebServer naar PHP interface cgi-fcgi
Joomla! versie Joomla! 3.3.6 Stable [ Ember ] 01-October-2014 02:00 GMT
Joomla! Platform versie Joomla Platform 13.1.0 Stable [ Curiosity ] 24-Apr-2013 00:00 GMT
Gebruikersagent Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.95 Safari/537.36

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

9 years 2 months ago #11 by geoffc
That is expected.

To explain, when you ran the re-install it was picking up that the install had already occured (from a previous attempt), hence it didn't run the install.sql script. The previous run had failed at the it_people table creation, all the other tables were bypassed, but despite this Joomla thinks that the install succeeded. There will be an entry in the '#__extensions' table for the component, and the various IT files will exist within the file system..

To resolve this situation there are a few alternatives.
1) Perform an uninstall. I suspect that Joomla will barf at thism and this will probably require that you physically drop the IT tables from the database, drop a few entries from the '#__extensions'', '#__menus' and probably also the '#__assets' tables. Also remove the com_issuestracker directories (and contents from the admin and site components directories. I suspect thought that this will place us back at square one and a 'fresh' install will probably then fail in the same way as before, which does not get one any further along.

2) Could try extracting the other SQL create statements from the install.sql file and running them manually. Then the componnet reinstall would (should) find them. Should also really add back in the two constraints in the 'it_people' table as well for completeness.

What is puzzling me is why the '#__it_people' table is created in myphpadmin and not by the Joomla installer, since the code syntax is the same. I was expecting the table creation to fail and give an information message. Having said that we did remove the two constraint lines, not that I think they are incorrect but when all the possibilities are eliminated what is left is possibly the cause. If the two referred to tables exist in the database we could try dropping the 'it_people' table and then rerun the table creation with the constraint lines added. You would have to add your database suffix to these lines as well.

I cannot get it to fail on any of my systems, but there must be something lurking there somewhere.

Thank you for your perserverance with this problem. I am thinking that to really pin it down I may need access to your system. This would be at the Joomla admin level, FTP level and also be able to access your database either through myphpadmin or via the 'console'. This may not be something that you cannot or do not wish to provide and I can fully understand this. If agreeable you should send me the details at 'This email address is being protected from spambots. You need JavaScript enabled to view it.'.

Regards
Geoff

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

9 years 2 months ago #12 by NF
Okay, you are right about the #__extensions table etc. I will do an uninstall and if necessary manual drops etc.

In parallel I start a total new Joomla 3.3.6 installation. Will try to install only your extension on that and if it doesn't work I will mail you all the credentials so you can login and look by yourself. I think this will be ready in about 1 hour.

Regards,
Nico

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

Time to create page: 0.197 seconds
Go To Top

Joomla! Debug Console

Session

Profile Information

Memory Usage

Database Queries