0008562: MySQL 8 Syntax Changes blocks installation

MantisBT - TestLink

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0008562

TestLink

Database MySQL

public

2019-02-08 16:11

2019-02-09 16:17

Reporter

gcomeau

Assigned To

fman

Priority

normal

Severity

crash

Reproducibility

always

Status

resolved

Resolution

fixed

Platform

OS Version

Product Version

1.9.19 (2019 Q1)

Fixed in Version

1.9.20 Raijing DEV

Act. Work

Issue Report Quality

Database (MySQL,Postgres,etc)

MySql 8.0

Browser

Chrome

PHP Version

7.2.15

TestCaseID

QA Team - Task Workflow Status

READY FOR TESTING

Summary

0008562: MySQL 8 Syntax Changes blocks installation

Description

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.

Steps To Reproduce

Additional Information