Mantis Bugtracker          
testlink.org

View Issue Details Jump to Notes ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0008764TestLinkDatabase MySQLpublic2019-09-17 14:002019-09-19 14:11
Reporterfilipse 
Assigned To 
PrioritynormalSeveritymajorReproducibilityalways
StatusnewResolutionopen 
PlatformOSOS Version
Product Version1.9.19.01 (1.9.19 fixes) 
Fixed in Version 
Summary0008764: Issues with database while upgrading from TL 1.9.14 to TL 1.9.19
Description
Dear Testlink team,

Disclaimer: The next is more a report of issues we found while upgrading as a reference for other people facing the same issue than actual a request for support since we will provide the solution next.

We recently upgraded our Testlink instance from V1.9.14 to V1.9.19.


Such a big upgrade included new VM, new PHP version as well as a new database version.

On the process of migrating the database from the old instance to the new one we faced several issues concerning database access referring to default values of timestamp type columns that can not be null.

These issues were solved with the following instructions:

-- correct issues with wrong default values and table definitions (compared to fresh install create statements)
update req_coverage set creation_ts = '2000-01-01 00:00:00' where cast(creation_ts as char(20))='0000-00-00 00:00:00';
alter table /*prefix*/attachments change column `date_added` `date_added` timestamp not null default current_timestamp;
alter table /*prefix*/db_version change column `upgrade_ts` `upgrade_ts` timestamp not null default current_timestamp;
alter table /*prefix*/inventory change column `modification_ts` `modification_ts` timestamp not null default current_timestamp;
update milestones set start_date = '2000-01-01' where cast(start_date as char(10))='0000-00-00';
alter table /*prefix*/milestones change column `target_date` `target_date` date default null,
change column `start_date` `start_date` DATE default NULL;
alter table /*prefix*/req_revisions change column `modification_ts` `modification_ts` datetime not null default current_timestamp;
alter table /*prefix*/req_specs_revisions change column `modification_ts` `modification_ts` datetime not null default current_timestamp;
alter table /*prefix*/req_versions change column `modification_ts` `modification_ts` datetime not null default current_timestamp;
update tcversions set modification_ts = '2000-01-01 00:00:00' where cast(modification_ts as char(20))='0000-00-00 00:00:00';
alter table /*prefix*/tcversions change column `creation_ts` `creation_ts` timestamp not null default current_timestamp,
change column `modification_ts` `modification_ts` datetime not null default current_timestamp;
alter table /*prefix*/user_assignments change column `creation_ts` `creation_ts` timestamp not null default current_timestamp;


We also found some privileges issues while trying to execute some functions. This was fixed by executing the following instruction:

grant all privileges on testlink.* to 'testlink'@'localhost'

We hope to help if someone find this issues.

Thanks a lot for all your effort,


Cheers

 
TagsNo tags attached.
Database (MySQL,Postgres,etc)MySQL
Browser
PHP Version
TestCaseID
QA Team - Task Workflow Status
Attached Files

- Relationships

-  Notes
(0029166)
filipse (reporter)
2019-09-17 14:58

Clarification:
Imported database had timestamp fields defined as "timestamp not null default ‘0000-00-00 00:00:00’"

New database version has fields defined as "timestamp not null default current_timestamp" since the MySQL version does not accept ‘0000-00-00 00:00:00’ as a valid date date

This produced major errors on database access by testlink.

As to fix this we had to first change values from ‘0000-00-00 00:00:00’ to valid date ‘2000-01-01 00:00:00’ and only then set the fields type to "timestamp not null default current_timestamp"
(0029167)
fman (administrator)
2019-09-17 15:11

Thanks.
(0029178)
filipse (reporter)
2019-09-19 10:41

As an extra information I'll add more issues our DBA has found - for now only corrected on our staging instance.

Our current version of MySQL - 5.7.24 - is optimized to use engine INNODB. The previous version of our Testlink database previous was all built using the MYISAM engine.

On the migration process most tables stayed using the MYISAM engine.
Given such and for optimization of the DB we planned to migrate the whole database into the INNODB engine by running command.

alter table <tablename> engine=innodb;

for each table still in the MYISAM engine.

This was pretty fast and done with the site online without no major constraint.

However some tables failed to be changed still due to the case reported above concerning the default value of timestamp fields.

As such the following commands had also to be executed before the alter table changing the engine:


update builds set creation_ts='2000-01-01 00:00:00' where cast(creation_ts as char(20))='0000-00-00 00:00:00';
update builds set release_date='2000-01-01' where cast(release_date as char(10))='0000-00-00';
update req_revisions set modification_ts='2000-01-01 00:00:00' where cast(modification_ts as char(20))='0000-00-00 00:00:00';
update req_versions set modification_ts='2000-01-01 00:00:00' where cast(modification_ts as char(20))='0000-00-00 00:00:00';
update req_specs_revisions set modification_ts='2000-01-01 00:00:00' where cast(modification_ts as char(20))='0000-00-00 00:00:00';
update testplan_tcversions set creation_ts='2000-01-01 00:00:00' where cast(creation_ts as char(20))='0000-00-00 00:00:00';
alter table /*prefix*/text_templates change column `creation_ts` `creation_ts` timestamp not null default current_timestamp;
(0029186)
filipse (reporter)
2019-09-19 13:54

Another issue found and I think solved. Can you please confirm?

Logs had this error:

- PHP Fatal error: Uncaught Error: Call to undefined function sprtinf() in /var/www/testlink/lib/functions/testcase.class.php:1918\nStack trace:\n#0 /var/www/testlink/lib/testcases/tcEdit.php(239): testcase->copy_to(209022, 263586, 189, Array)\n#1 {main}\n thrown in /var/www/testlink/lib/functions/testcase.class.php on line 1918, referer: https://testlink.emsa.europa.eu/lib/testcases/tcEdit.php [^]


Line 1918 of file lib/functions/testcase.class.php was

$act = sprtinf(self::GHOSTSTEPMASK,$step['step_number'],


and was replaced by
$act = sprintf(self::GHOSTSTEPMASK,$step['step_number'],

sprtinf was replaced by sprintf


Thanks,
(0029187)
fman (administrator)
2019-09-19 14:05

Thanks, but Please do not use an issue that has been created with an scope to report a different issue.
this is what happens with note 29186.
Please next time open a new issue with the appropriate subject

regards
(0029189)
filipse (reporter)
2019-09-19 14:11

Dully noted.
Will do it.

I understand it is harder - almost impossible actually - for someone struggling with this issue to figure out the answer here with a total different subject.

- Issue History
Date Modified Username Field Change
2019-09-17 14:00 filipse New Issue
2019-09-17 14:58 filipse Note Added: 0029166
2019-09-17 15:11 fman Note Added: 0029167
2019-09-19 10:41 filipse Note Added: 0029178
2019-09-19 13:54 filipse Note Added: 0029186
2019-09-19 14:05 fman Note Added: 0029187
2019-09-19 14:11 filipse Note Added: 0029189



Copyright © 2000 - 2019 MantisBT Team
Powered by Mantis Bugtracker