mysql 4.0 対応のクライアント

> ERROR 2027 (HY000): Malformed packet

mysql-clientが対応していない。

## 検証

mysql 4.0.27 に対して接続を試みた。

| client | 結果 |
|:--|:--|
| mariadb-client 10.1.18 | OK |
| mysql-client 5.6 | OK (--skip-secure-auth) |
| php 5.6 + pdo,mysqli | OK |
| ruby 2.3 + ruby-mysql (with-mysql-dir) | OK |
| mysql-connector-java-5.1.31-bin.jar | OK |
| php 5.6 + cakephp 2 | OK |
| php 5.6 + CodeIgniter 2 | OK |
| php 5.6 + CodeIgniter 3.1.2 (mysql_driver) | OK |

| client | 結果 |
|:--|:--|
| mysql 5.7 | NG |
| php 7 + pdo,mysqli | NG |
| ruby 2.3 + ruby-mysql | NG |
| ruby 2.3 + ruby-mysql2 | NG |
| ruby 2.3 + ruby-mysql2 (with-mysql-dir) | ビルドエラー |
| mysql-connector-java-5.1.32 以降 | NG |
| php 5.6 + lumen 5, laravel 5.3 | NG |
| php 5.6 + lumen 5 (facade) | NG |
| php 5.6 + cakephp 3 | NG |
| php 5.6 + CodeIgniter 3.1.2 (mysqli_driver) | NG |

* [MySQL :: Download MySQL Connector/J (Archived Versions)](https://downloads.mysql.com/archives/c-j/)

|名前|サイズ|ファイル数|
|:--|:--|:--|
| lumen 5.3 | 33M | 4928 |
| laravel 5.3 | 39M | 6085 |
| CakePHP 2.9.3 | 13M | 1517 |
| CakePHP 3.3.9 | 26M | 3877 |
| CodeIgniter 2 | 2.0MB | 190 |
| CodeIgniter 3.1.2 | 2.7MB | 301 |

* CodeIgniter は マニュアル(user_guide)を除いて計測

## php 7 + pdo

```php:pdo
$mysql = new PDO('mysql:host=$dbserver;dbname=test', $user, $pass);

$sql = "select now() as now";
$rows = $mysql->query($sql)->fetchAll(PDO::FETCH_ASSOC);

foreach($rows as $key=>$val) {
        echo $val["now"];
        echo "<br>";
}
```

> PHP Fatal error:  Uncaught PDOException: PDO::__construct(): Connecting to 3.22, 3.23 & 4.0  is not supported. Server is 4.0.27-log in a.php

```php:mysqi
$mysqli = new mysqli($dbserver, $user, $pass, "test");

$sql = "select now() as now";
if ($result = $mysqli->query($sql)) {
    while ($row = $result->fetch_assoc()) {
        echo $row["now"] . "<br>";
    }
}
```

```php:mysql_connectは廃止されたので使えない
$c = mysql_connect($dbserver, $user, $pass);
```

* [PHP: どの API を使うか - Manual](http://php.net/manual/ja/mysqlinfo.api.choosing.php)

## ruby-mysql

```rb:a.rb
require 'mysql'

connection = Mysql::connect("127.0.0.1", "root", "root", "test")
#connection.query("set character set utf8")
rs = connection.query("show tables")
rs.each do |r|
  puts r.join ", "
end
connection.close
```

```bash:/etc/ld.so.conf.d/opt-mysql.conf
/opt/mysql/lib/mysql/
```

```bash:ソースから入れるときにmysql4が入っている場所指定
sudo gem install mysql -- --with-mysql-dir=/opt/mysql
# 以下ライブラリが生成されている様子
#/var/lib/gems/2.3.0/gems/mysql-2.9.1/lib/mysql/mysql_api.so
#/var/lib/gems/2.3.0/gems/mysql-2.9.1/ext/mysql_api/mysql_api.so
#/var/lib/gems/2.3.0/extensions/x86_64-linux/2.3.0/mysql-2.9.1/mysql/mysql_api.so
```

```bash:
sudo ldconfig
ruby a.rb
```

接続成功した。

## ruby-mysql2

```rb
require "mysql2"

$client = Mysql2::Client.new(
  :host     => "127.0.0.1",
  :username => "root",
  :password => "root",
  :database => "test"
)

sql = %{
  show tables
}
res = $client.query(sql)

res.each{|row|
  p row
}
```

```bash:このビルドは成功しなかった
sudo gem install mysql2 -- --with-mysql-dir=/opt/mysql
```


## 4から5への移行について

https://speakerdeck.com/yoku0825/2022nian-nomysqlerga20nian-qian-nomysql-4-dot-0nihong-rutohe-gaqi-kiruka

4.0から5へはvarcharとtimestampが注意
mysql40dumpというwrapper