MySQL integration test fail in section testNamedParameters
ZVanoZ opened this issue · 8 comments
Bug Report
Q | A |
---|---|
Version(s) | 2.14.x |
Note, PHP 7.4 in custom Docker image.
Summary
Bound parameters by index is invalid.
Current behavior
When we run tests, laminas-db generate SQL
UPDATE `test` SET `name` = :c_0, `value` = :c_1 WHERE `id` = :where1
with params
Array
(
"c_0" => 1,
"c_1" => foo,
"where1" => bar
)
and we have an error
1) LaminasIntegrationTest\Db\Adapter\Driver\Pdo\Mysql\QueryTest::testNamedParameters
Laminas\Db\Adapter\Exception\InvalidQueryException: Statement could not be executed (22007 - 1292 - Truncated incorrect DOUBLE value: 'bar')
I think, it happend becouse value ["where1" => bar] bonded with id
= :where1
id has type int.
How to reproduce
Edit "phpunit.xml" and set TESTS_LAMINAS_DB_ADAPTER_DRIVER_MYSQL in state true
Other adapters set state false.
# Set current directory "laminas-db" sources
$ cd laminas-db
# Run tests
$ docker run --rm -it --volume $(pwd):/app zvanoz/laminas-db-test-by-docker:74 composer -vvv test-integration
Reading ./composer.json (/app/composer.json)
Loading config file ./composer.json (/app/composer.json)
Checked CA file /etc/ssl/certs/ca-certificates.crt: valid
Executing command (/app): git branch -a --no-color --no-abbrev -v
Executing command (/app): git describe --exact-match --tags
Executing command (CWD): git --version
Executing command (/app): git log --pretty="%H" -n1 HEAD
Executing command (/app): hg branch
Executing command (/app): fossil branch list
Executing command (/app): fossil tag list
Executing command (/app): svn info --xml
Failed to initialize global composer: Composer could not find the config file: /root/.config/composer/composer.json
Reading /app/vendor/composer/installed.json
Loading plugin Dealerdirect\Composer\Plugin\Installers\PHPCodeSniffer\Plugin (from dealerdirect/phpcodesniffer-composer-installer)
Running 2.1.8 (2021-09-15 13:55:14) with PHP 7.4.3 on Linux / 4.19.0-17-amd64
> test-integration: phpunit --colors=always --testsuite "integration test"
Executing command (CWD): phpunit --colors=always --testsuite "integration test"
PHPUnit 9.5.9 by Sebastian Bergmann and contributors.
Integration test started.
............E.........SSS..SSSS 31 / 31 (100%)
Time: 00:03.203, Memory: 10.00 MB
There was 1 error:
1) LaminasIntegrationTest\Db\Adapter\Driver\Pdo\Mysql\QueryTest::testNamedParameters
Laminas\Db\Adapter\Exception\InvalidQueryException: Statement could not be executed (22007 - 1292 - Truncated incorrect DOUBLE value: 'bar')
/app/src/Adapter/Driver/Pdo/Statement.php:223
/app/test/integration/Adapter/Driver/Pdo/Mysql/QueryTest.php:87
Caused by
PDOException: SQLSTATE[22007]: Invalid datetime format: 1292 Truncated incorrect DOUBLE value: 'bar'
/app/src/Adapter/Driver/Pdo/Statement.php:212
/app/test/integration/Adapter/Driver/Pdo/Mysql/QueryTest.php:87
ERRORS!
Tests: 31, Assertions: 72, Errors: 1, Skipped: 7.
Script phpunit --colors=always --testsuite "integration test" handling the test-integration event returned with error code 2
Expected behavior
All tests is success.
Could you check if:
2.13.x
is affected- PHP 8.0 and 8.1 are affected
Truncated incorrect DOUBLE value: 'bar'
This seems like MySQL telling us that bar
is not a valid bound parameter for that column: could it be that the MySQL instance is running with stricter checks, and is trying to cast 'bar'
to double
?
Could you check if:
*2.13.x
is affected
I did it for PHP 7.4 CLI and have the same result.
- Prepare to tests
# Went to clone of "laminas-db" and check "is all ok?"
$ git remote -v
# origin https://github.com/laminas/laminas-db.git (fetch)
# origin https://github.com/laminas/laminas-db.git (push)
$ git branch -a
# 2.12.x
# * 2.13.x
# 2.14.x
# remotes/origin/2.11.x
# remotes/origin/2.12.x
# remotes/origin/2.13.x
# remotes/origin/2.13.x-merge-up-into-2.14.x_3Y5r1t4x
# remotes/origin/2.13.x-merge-up-into-2.14.x_LxxfGKFv
# remotes/origin/2.14.x
# remotes/origin/HEAD -> origin/2.13.x
# remotes/origin/dev-3.0.0
# remotes/origin/dev-3.0.0-old
# remotes/origin/gh-pages
# Update vendor libraies
$ docker run --rm -it --volume $(pwd):/app zvanoz/laminas-db-test-by-docker:74 composer update
# Loading composer repositories with package information
# Updating dependencies
# Lock file operations: 0 installs, 4 updates, 1 removal
# - Removing laminas/laminas-zendframework-bridge (1.4.0)
# - Upgrading laminas/laminas-servicemanager (3.7.0 => 3.10.0)
# - Upgrading nikic/php-parser (v4.12.0 => v4.13.0)
# - Upgrading phpdocumentor/type-resolver (1.4.0 => 1.5.0)
# - Upgrading phpunit/php-code-coverage (9.2.6 => 9.2.7)
# Writing lock file
# Installing dependencies from lock file (including require-dev)
# Package operations: 46 installs, 0 updates, 0 removals
# ...
# Package container-interop/container-interop is abandoned, you should avoid using it. Use psr/container instead.
# Generating autoload files
# 33 packages you are using are looking for funding.
# Use the `composer fund` command to find out more!
# PHP CodeSniffer Config installed_paths set to ../../laminas/laminas-coding-standard/src,../../slevomat/coding-standard,../../webimpress,../../webimpress/coding-standard/src
$ git status
# На ветке 2.13.x
# Ваша ветка обновлена в соответствии с «origin/2.13.x».
#
# Изменения, которые не в индексе для коммита:
# (используйте «git add <файл>…», чтобы добавить файл в индекс)
# (используйте «git checkout -- <файл>…», чтобы отменить изменения
# в рабочем каталоге)
#
# изменено: composer.lock
- Run tests
$ docker run --rm -it --volume $(pwd):/app zvanoz/laminas-db-test-by-docker:74 composer -vvv test-integration
Result
Reading ./composer.json (/app/composer.json)
Loading config file ./composer.json (/app/composer.json)
Checked CA file /etc/ssl/certs/ca-certificates.crt: valid
Executing command (/app): git branch -a --no-color --no-abbrev -v
Executing command (/app): git describe --exact-match --tags
Executing command (CWD): git --version
Executing command (/app): git log --pretty="%H" -n1 HEAD
Executing command (/app): hg branch
Executing command (/app): fossil branch list
Executing command (/app): fossil tag list
Executing command (/app): svn info --xml
Failed to initialize global composer: Composer could not find the config file: /root/.config/composer/composer.json
Reading /app/vendor/composer/installed.json
Loading plugin Dealerdirect\Composer\Plugin\Installers\PHPCodeSniffer\Plugin (from dealerdirect/phpcodesniffer-composer-installer)
Running 2.1.8 (2021-09-15 13:55:14) with PHP 7.4.3 on Linux / 4.19.0-17-amd64
> test-integration: phpunit --colors=always --testsuite "integration test"
Executing command (CWD): phpunit --colors=always --testsuite "integration test"
PHPUnit 9.5.9 by Sebastian Bergmann and contributors.
Integration test started.
............E.........SSS..SSSS 31 / 31 (100%)
Time: 00:06.887, Memory: 8.00 MB
There was 1 error:
1) LaminasIntegrationTest\Db\Adapter\Driver\Pdo\Mysql\QueryTest::testNamedParameters
Laminas\Db\Adapter\Exception\InvalidQueryException: Statement could not be executed (22007 - 1292 - Truncated incorrect DOUBLE value: 'bar')
/app/src/Adapter/Driver/Pdo/Statement.php:223
/app/test/integration/Adapter/Driver/Pdo/Mysql/QueryTest.php:87
Caused by
PDOException: SQLSTATE[22007]: Invalid datetime format: 1292 Truncated incorrect DOUBLE value: 'bar'
/app/src/Adapter/Driver/Pdo/Statement.php:212
/app/test/integration/Adapter/Driver/Pdo/Mysql/QueryTest.php:87
ERRORS!
Tests: 31, Assertions: 72, Errors: 1, Skipped: 7.
Script phpunit --colors=always --testsuite "integration test" handling the test-integration event returned with error code 2
* PHP 8.0 and 8.1 are affected
I will do it after make test docker for PHP 8.0
To be clear, if we pass in 123
instead of 'bar'
it works?
Just making sure it is just a mistake in QueryTest
To be clear, if we pass in
123
instead of'bar'
it works?
I did it and the error disappeared
PHP 7.4
"laminas-db" 2.13.x + local changes
Note: i changed parameters for two executes ("positional parameters" and "named parameters"). Because both give the error.
// test/integration/Adapter/Driver/Pdo/Mysql/QueryTest.php
public function testNamedParameters()
{
$sql = new Sql($this->adapter);
$insert = $sql->update('test');
$insert->set([
'name' => ':name',
'value' => ':value',
])->where(['id' => ':id']);
$stmt = $sql->prepareStatementForSqlObject($insert);
//positional parameters
$stmt->execute([
1,
'foo',
123 // prev value is: 'bar',
]);
//"mapped" named parameters
$stmt->execute([
'c_0' => 1,
'c_1' => 'foo',
'where1' => 123//prev value is: 'bar',
]);
$ docker run --rm -it --volume $(pwd):/app zvanoz/laminas-db-test-by-docker:74 composer -vvv test-integration
Reading ./composer.json (/app/composer.json)
Loading config file ./composer.json (/app/composer.json)
Checked CA file /etc/ssl/certs/ca-certificates.crt: valid
Executing command (/app): git branch -a --no-color --no-abbrev -v
Executing command (/app): git describe --exact-match --tags
Executing command (CWD): git --version
Executing command (/app): git log --pretty="%H" -n1 HEAD
Executing command (/app): hg branch
Executing command (/app): fossil branch list
Executing command (/app): fossil tag list
Executing command (/app): svn info --xml
Failed to initialize global composer: Composer could not find the config file: /root/.config/composer/composer.json
Reading /app/vendor/composer/installed.json
Loading plugin Dealerdirect\Composer\Plugin\Installers\PHPCodeSniffer\Plugin (from dealerdirect/phpcodesniffer-composer-installer)
Running 2.1.8 (2021-09-15 13:55:14) with PHP 7.4.3 on Linux / 4.19.0-17-amd64
> test-integration: phpunit --colors=always --testsuite "integration test"
Executing command (CWD): phpunit --colors=always --testsuite "integration test"
PHPUnit 9.5.9 by Sebastian Bergmann and contributors.
Integration test started.
............R.........SSS..SSSS 31 / 31 (100%)
Time: 00:02.826, Memory: 8.00 MB
There was 1 risky test:
1) LaminasIntegrationTest\Db\Adapter\Driver\Pdo\Mysql\QueryTest::testNamedParameters
This test did not perform any assertions
/app/test/integration/Adapter/Driver/Pdo/Mysql/QueryTest.php:74
OK, but incomplete, skipped, or risky tests!
Tests: 31, Assertions: 87, Skipped: 7, Risky: 1.
Ok, so it's just a faulty test then :-)
1) LaminasIntegrationTest\Db\Adapter\Driver\Pdo\Mysql\QueryTest::testNamedParameters
This test did not perform any assertions
I guess this test is totally foobar'd anyway :-\
I'm glad I helped figure it out.
Can you fix the test?
I'm new to unit testing and I'm afraid of messing up anything.
I think I figured out the reason for the error and how to fix it.
Let's say we have the following request
UPDATE `test` SET `name` = :c_0, `value` = :c_1 WHERE ` id` = :where1
-- binding order
-- Index Bind Name Field name Field type
-- 0 ":c_0" "name" varchar(255)
-- 1 ":c_1" "value" varchar(255)
-- 2 ":where1" "id" int
We can call it in two ways.
- Binding substitution variables by index
Someone need to swap the values of the elements with index 0 and 2.
// positional parameters
$ stmt-> execute ([
1, // Error -- 0 ":c_0" "name" varchar(255)
'foo', // Ok. -- 1 ":c_1" "value" varchar(255)
'bar', // Error -- 2 ":where1" "id" int
]);
- Binding by substitution variable names
Someone need to swap the values of the elements with index "c_0" and "where1".
//"mapped" named parameters
$ stmt-> execute ([
'c_0' => 1, //Error. -- 0 ":c_0" "name" varchar(255)
'c_1' => 'foo', //Ok. -- 1 ":c_1" "value" varchar(255)
'where1' => 'bar', // Error -- 2 ":where1" "id" int
]);
PS: 2021-09-24
It turns out that in "laminas-db" there is a third way to bind parameters - by real field names (see the test below).