IP.Board Mysql gone away during conversion from utf8 to utf8mb4

I am using IP.Board and I am trying to convert my database from utf8 to utfmb4 for the purpose of using emoji. There is a built in option in the admin panel that I like to use, because as I read manual conversion is not very straightforward:



Unfortunately after about a minute the conversion process is stopped. I have extracted this from the error log:

---

Code:

2017/02/06 04:13:50 [error] 27657#27657: *24088 FastCGI sent in stderr: "PHP message: PHP Fatal error:  Uncaught IPS\Db\Exception: MySQL server has gone away in /mnt/volume-fra1-01/348/system/Db/Db.php:502
Stack trace:
#0 /mnt/volume-fra1-01/348/system/Db/Db.php(839): IPS\_Db->preparedQuery('/*IPS\\Session\\_...', Array)
#1 /mnt/volume-fra1-01/348/system/Session/Admin.php(113): IPS\_Db->replace('core_sys_cp_ses...', Array)
#2 [internal function]: IPS\Session\_Admin->write('4v7okt1ld0dti4c...', 'forcedWrite|i:1...')
#3 [internal function]: session_write_close()
#4 {main}
  thrown in /mnt/volume-fra1-01/348/system/Db/Db.php on line 502" while reading response header from upstream, client: 87.120.221.130, server: devtest.magelanci.com, request: "GET /admin/?adsess=4v7okt1ld0dti4ct6b748avgn5&app=core&module=support&controller=utf8mb4&mr=106&csrfKey=386f1d99ac0901d7f72c85d1ea7cbe46&adsess=4v7okt1ld0dti4ct6b748avgn5 HTTP/2.0", upstream: "fastcgi://127.0.0.1:9000", host: "devtest.magelanci.com", referrer: "https://devtest.magelanci.com/admin/?adsess=4v7okt1ld0dti4ct6b748avgn5&app=core&module=support&controller=utf8mb4"
2017/02/06 04:13:51 [error] 27657#27657: *24088 FastCGI sent in stderr: "PHP message: PHP Fatal error:  Uncaught IPS\Db\Exception: Connection refused in /mnt/volume-fra1-01/348/system/Db/Db.php:206
Stack trace:
#0 /mnt/volume-fra1-01/348/system/Db/Db.php(117): IPS\_Db::_establishConnection('IPS\\Db', 'localhost', 'XXX', 'dgUIiN1Ug...', 'XXX', NULL, NULL, false)
#1 /mnt/volume-fra1-01/348/system/Session/Admin.php(103): IPS\_Db::i()
#2 [internal function]: IPS\Session\_Admin->write('4v7okt1ld0dti4c...', '')
#3 [internal function]: session_write_close()
#4 {main}

This is the error dump:

Code:

object(IPS\Db\Exception)#98 (9) { ["query"]=> NULL ["binds"]=> array(0) { } ["message":protected]=> string(18) "Connection refused" ["string":"Exception":private]=> string(0) "" ["code":protected]=> int(2002) ["file":protected]=> string(40) "/mnt/volume-fra1-01/348/system/Db/Db.php" ["line":protected]=> int(206) ["trace":"Exception":private]=> array(7) { [0]=> array(6) { ["file"]=> string(40) "/mnt/volume-fra1-01/348/system/Db/Db.php" ["line"]=> int(117) ["function"]=> string(20) "_establishConnection" ["class"]=> string(7) "IPS\_Db" ["type"]=> string(2) "::" ["args"]=> array(8) { [0]=> string(6) "IPS\Db" [1]=> string(9) "localhost" [2]=> string(4) "XXX" [3]=> string(16) "dgUIiN1Ug" [4]=> string(9) "XXX" [5]=> NULL [6]=> NULL [7]=> bool(false) } } [1]=> array(6) { ["file"]=> string(46) "/mnt/volume-fra1-01/348/system/Login/Login.php" ["line"]=> int(92) ["function"]=> string(1) "i" ["class"]=> string(7) "IPS\_Db" ["type"]=> string(2) "::" ["args"]=> array(0) { } } [2]=> array(6) { ["file"]=> string(46) "/mnt/volume-fra1-01/348/system/Login/Login.php" ["line"]=> int(244) ["function"]=> string(8) "handlers" ["class"]=> string(10) "IPS\_Login" ["type"]=> string(2) "::" ["args"]=> array(0) { } } [3]=> array(6) { ["file"]=> string(72) "/mnt/volume-fra1-01/348/applications/core/modules/admin/system/login.php" ["line"]=> int(43) ["function"]=> string(5) "forms" ["class"]=> string(10) "IPS\_Login" ["type"]=> string(2) "->" ["args"]=> array(2) { [0]=> bool(true) [1]=> string(108) "YWRzZXNzPTR2N29rdDFsZDBkdGk0Y3Q2Yjc0OGF2Z241JmFwcD1wb2ludHMmbW9kdWxlPXBvaW50cyZjb250cm9sbGVyPWN1cnJlbmNpZXM=" } } [4]=> array(6) { ["file"]=> string(56) "/mnt/volume-fra1-01/348/system/Dispatcher/Controller.php" ["line"]=> int(96) ["function"]=> string(6) "manage" ["class"]=> string(36) "IPS\core\modules\admin\system\_login" ["type"]=> string(2) "->" ["args"]=> array(0) { } } [5]=> array(6) { ["file"]=> string(56) "/mnt/volume-fra1-01/348/system/Dispatcher/Dispatcher.php" ["line"]=> int(129) ["function"]=> string(7) "execute" ["class"]=> string(26) "IPS\Dispatcher\_Controller" ["type"]=> string(2) "->" ["args"]=> array(0) { } } [6]=> array(6) { ["file"]=> string(39) "/mnt/volume-fra1-01/348/admin/index.php" ["line"]=> int(14) ["function"]=> string(3) "run" ["class"]=> string(15) "IPS\_Dispatcher" ["type"]=> string(2) "->" ["args"]=> array(0) { } } } ["previous":"Exception":private]=> NULL }

The two bolded parts are my mysql password (not my real password, just an example). Could it be somehow related that in the first instance it appends ... to the password? The password itself does not have ... as seen in the dump.

I tried timing the seconds since start and the error appears around 75 second mark, which is way below than all my.cnf timeouts. Basically, I've reached a roadblock.

IPS cannot help me as it is still test installation, so maybe someone here can give me some advice so at least I can understand the error and maybe point me in the right direction?

Edit: I see that my bolding is removed. In the error log I have this line:

Stack trace:
#0 /mnt/volume-fra1-01/348/system/Db/Db.php(117): IPS\_Db::_establishConnection('IPS\\Db', 'localhost', 'XXX', 'dgUIiN1Ug...', 'XXX', NULL, NULL, false)

My db password is not dgUIiN1Ug..., but only dgUIiN1Ug . Could this be the reason?

 

Thanks for the suggestions, but they didn't help. I increased the max_allowed_packet, the timeout was already high. Php and mysql are on the same server.

I'll keep reading, eventually I will try to convert it manually, but I am afraid this might bring even more problems in the long run if not done properly.

 

About 800mb. But thats not the issue I believe, because as I run the script few times, already large number of tables are converted (without the columns that contain indexes though).

Also, it breaks at between 60 to 75 seconds, which I believe shouldn't be a timeout issue, correct? If it was a timeout I assume it will always break on the exact second as before.

 

It could as well be problem with my 3.4 database, I remember back then I was having problems to convert from latin1 to utf8. Finally I did it the lame way - open in notepad++ and just fixed the collations there. It seems to work ok, but there are probably some side effects...

@Jimmy : Problem is, I cannot localize on which part of the conversion this happens and I cannot replicate the problem manually. It doesn't look that complicated - change collation of database, change collation of tables, drop larger indexes, change collations on those columns, rebuild indexes. I tried looking for other scirpts, but it seems most of them don't pay attention to the rebuilding index part at all. Anyway, let me know if you have some problems.

 

Yes. I am converting from 3.4 first and then trying it on 4.1.