MantisBT - TestLink
View Issue Details
0008562TestLinkDatabase MySQLpublic2019-02-08 16:112019-02-09 16:17
gcomeau 
fman 
normalcrashalways
resolvedfixed 
1.9.19 (2019 Q1) 
1.9.20 Raijing DEV 
MySql 8.0
Chrome
7.2.15
READY FOR TESTING
0008562: MySQL 8 Syntax Changes blocks installation
I setup testlink on centos 7, using the following technology stack:

nginx version: nginx/1.12.2
Mysql: 8.0.15
PHP version: 7.2.15

The installation went fairly smooth until I got to the portion where the database tables are set up. When the script runs, I was getting the following error:

[_errorMsg] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY '6wBV5pBvLW1kpotFuLfu''

After reviewing the code in: installUtils.php

I was able to fix it by making the following changes:

[code]// 20070310 - $the_host -> $db_host
if (strlen(trim($db_host)) != 0)
{
  $stmt .= "@" . "'" . $dbhandler->prepare_string($db_host) . "'";
}
$stmt .= " IDENTIFIED BY '" . $passwd . "'"; // this is bad[/code]

Should be:

[code] // 20070310 - $the_host -> $db_host
if (strlen(trim($db_host)) != 0)
{
  $stmt .= "@" . "'" . $dbhandler->prepare_string($db_host) . "'";
}
// $stmt .= " IDENTIFIED BY '" . $passwd . "'"; // this is good[/code]

And:

i[code]f( strcasecmp('localhost',$db_host) != 0)
  {
    // 20060514 - franciscom - missing
    $stmt = "GRANT SELECT, UPDATE, DELETE, INSERT ON " .
             "`" . $dbhandler->prepare_string($db_name) . "`" . ".* TO " .
             "'" . $dbhandler->prepare_string($login) . "'@'localhost'" .
            " IDENTIFIED BY '" . $passwd . "'"; // this is bad
    if ( !@$dbhandler->exec_query($stmt) )
    {
      $op->msg = "ko - " . $dbhandler->error_msg();
      $op->status_ok=false;
    }
  }
}[/code]

Should be:

[code]if( strcasecmp('localhost',$db_host) != 0)
  {
    // 20060514 - franciscom - missing
    $stmt = "GRANT SELECT, UPDATE, DELETE, INSERT ON " .
             "`" . $dbhandler->prepare_string($db_name) . "`" . ".* TO " .
             "'" . $dbhandler->prepare_string($login) . "'@'localhost'"; // this is good
    if ( !@$dbhandler->exec_query($stmt) )
    {
      $op->msg = "ko - " . $dbhandler->error_msg();
      $op->status_ok=false;
    }
  }
}[/code]


So, in a brief explanation, " IDENTIFIED BY '" . $passwd . "'"; can no longer be used in mysql 8 with a grant sql operation, it's not considered valid syntax.
No tags attached.
child of 0008549assigned fman Availables hot-fixes for 1.9.19 & How To get full fixed package from GitHub 
Issue History
2019-02-08 16:11gcomeauNew Issue
2019-02-09 11:54fmanDatabase (MySQL,Postgres,etc)MySql => MySql 8.0
2019-02-09 11:54fmanQA Team - Task Workflow Status => TBD
2019-02-09 11:54fmanSummaryNewest version of testlink has incorrect syntax for newest version of mysql (mysql8) => MySQL 8 Syntax Changes blocks installation
2019-02-09 11:54fmanSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=5742#r5742
2019-02-09 16:15fmanNote Added: 0028515
2019-02-09 16:16fmanQA Team - Task Workflow StatusTBD => READY FOR TESTING
2019-02-09 16:16fmanNote Added: 0028516
2019-02-09 16:16fmanStatusnew => resolved
2019-02-09 16:16fmanFixed in Version => 1.9.20 Raijing DEV
2019-02-09 16:16fmanResolutionopen => fixed
2019-02-09 16:16fmanAssigned To => fman
2019-02-09 16:16fmanNote Added: 0028517
2019-02-09 16:17fmanRelationship addedchild of 0008549

Notes
(0028515)
fman   
2019-02-09 16:15   
thanks
(0028516)
fman   
2019-02-09 16:16   
https://github.com/TestLinkOpenSourceTRMS/testlink-code/commit/6691f4ff4671fb2b9de7b071780ac83e685b2035 [^]
(0028517)
fman   
2019-02-09 16:16   
would you mind apply changes provided on note 28516, retest and provide feedback?