The mediumint field affects value of next field and make datetime binary protocol error

[xpader]

In a specific table field structure, and simple query will get this error:

Amp\Sql\SqlException: Unexpected string length for datetime in binary protocol: 8 in /vendor/amphp/mysql/src/MysqlDataType.php:190

Test table SQL Dump:

CREATE TABLE `users_copy1` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `area_code` mediumint unsigned NOT NULL DEFAULT '0',
  `last_active_ip` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `users_copy1` (`id`, `area_code`, `last_active_ip`, `created_at`) VALUES
(2, 0, '10.0.0.1', '2025-06-12 18:13:22');

Reproduce code:

$config = \Amp\Mysql\MysqlConfig::fromString(
    "host=192.168.4.2 user=test password= db=test"
);

$pool = new \Amp\Mysql\MysqlConnectionPool($config);

$statement = $pool->prepare("select * from users_copy1 where id=2");

$result = $statement->execute();
foreach ($result as $row) {
    print_r($row);
}

If we delete field area_code, or delete field last_active_ip, or change area_code from type mediumint to int, the problem will gone.

MySQL: 5.7.44, 8.0.41
PHP: 8.2.28 in Docker from php:8.2.28-fpm-bookworm
amphp/mysql: 3.0.0
amphp/sql: 2.0.1
amphp/sql-common: 2.0.3

[xpader]

In some other cases, not getting error, but affected the next timestamp field value.

Table:

CREATE TABLE `user_credit_records_copy` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `remark` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `action_id` mediumint unsigned NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `action_id` (`action_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `user_credit_records_copy` (`id`, `remark`, `action_id`, `created_at`, `updated_at`) VALUES
(1, 'Hello', 2, '2025-06-16 10:49:24', '2025-06-16 10:49:24');

• When mediumint field action_id position is after field remark, but before some timestamp fields, the first timestamp field after action_id, like created_at will getting 0000-00-00 00:00:00.
• When mediumint field action is before remark field, will getting error: Unexpected string length for datetime in binary protocol: 8.
• When change action_id from mediumint to int, everything get normal.

Change select fields position in query statement have same effect.

select id, remark, action_id, created_at from user_credit_records_copy where id=1

created_at value return 0000-00-00 00:00:00.

select id, action_id, remark, created_at from user_credit_records_copy where id=1

Getting error: Unexpected string length for datetime in binary protocol: 8.

[susofontan]

Hello, the problem is that when performing binary decoding, amphp/mysql is treating the mediumint data type (Int24) as if it had a length of 3 bytes, but this is not correct. While it's true that MySQL stores mediumint in 3 bytes, for the binary transmission protocol the "int" data type (4 bytes) is used. This can be verified in table 6.2 at https://docs.oracle.com/cd/E17952_01/c-api-5.7-en/c-api-prepared-statement-type-codes.html

On https://github.com/amphp/mysql/blob/3.x/src/MysqlDataType.php

Uses of decodeUnsigned24 and decodeInt24 in the code that may expect a 3-byte size should be reviewed to apply the final fix, as well as encodeInt24 currently forced to 3 bytes.

The fastest temporary fix is to treat Int24s the same as Longs in binary decoding.

    public function decodeBinary(string $bytes, int &$offset = 0, int $flags = 0): int|float|string|null
    {
        $unsigned = $flags & 0x20;

        switch ($this) {
            case self::String:
            case self::Varchar:
            case self::VarString:
            case self::Enum:
            case self::Set:
            case self::LongBlob:
            case self::MediumBlob:
            case self::Blob:
            case self::TinyBlob:
            case self::Geometry:
            case self::Bit:
            case self::Decimal:
            case self::NewDecimal:
            case self::Json:
                return self::decodeString($bytes, $offset);

            case self::LongLong:
                return $unsigned
                    ? self::decodeUnsigned64($bytes, $offset)
                    : self::decodeInt64($bytes, $offset);
            case self::Long:
            case self::Int24:
                return $unsigned
                    ? self::decodeUnsigned32($bytes, $offset)
                    : self::decodeInt32($bytes, $offset);

//            case self::Int24:
//                return $unsigned
//                    ? self::decodeUnsigned24($bytes, $offset)
//                    : self::decodeInt24($bytes, $offset);

            case self::Short:
            case self::Year:
                return $unsigned
                    ? self::decodeUnsigned16($bytes, $offset)
                    : self::decodeInt16($bytes, $offset);

            case self::Tiny:
                return $unsigned
                    ? self::decodeUnsigned8($bytes, $offset)
                    : self::decodeInt8($bytes, $offset);

            case self::Double:
                $offset += 8;
                return \unpack("e", $bytes, $offset - 8)[1];

            case self::Float:
                $offset += 4;
                return \unpack("g", $bytes, $offset - 4)[1];

            case self::Date:
            case self::Datetime:
            case self::Timestamp:
                return $this->decodeDateTime($bytes, $offset);

            case self::Time:
                return $this->decodeTime($bytes, $offset);

            case self::Null:
                return null;

            default:
                throw new SqlException("Invalid type for Binary Protocol: 0x" . \dechex($this->value));
        }
    }