Welcome to Centmin Mod Community
Become a Member

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

Discussion in 'Forum software usage' started by jair, Feb 12, 2017.

  1. jair

    jair New Member

    16
    1
    3
    Jan 8, 2017
    Ratings:
    +1
    Local Time:
    2:12 PM
    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:

    [​IMG]

    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?
     
    Last edited: Feb 12, 2017
  2. eva2000

    eva2000 Administrator Staff Member

    29,033
    6,589
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,782
    Local Time:
    9:12 PM
    Nginx 1.13.x
    MariaDB 5.5
    https://dev.mysql.com/doc/refman/5.7/en/gone-away.html

    Depending on how much memory you have, try raising /etc/my.cnf mysql max_allowed_packet and/or wait_timeout to 512 MB and 28800 respectively just for the conversion and restarting MySQL server
     
  3. eva2000

    eva2000 Administrator Staff Member

    29,033
    6,589
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,782
    Local Time:
    9:12 PM
    Nginx 1.13.x
    MariaDB 5.5
    though there's this.. you MySQL server on same server as php files ? If not CSF Firewall maybe blocking php client server from connecting to MariaDB MySQL server
     
  4. jair

    jair New Member

    16
    1
    3
    Jan 8, 2017
    Ratings:
    +1
    Local Time:
    2:12 PM
    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.
     
  5. Jimmy

    Jimmy Premium Member Premium Member

    1,026
    231
    63
    Oct 24, 2015
    East Coast USA
    Ratings:
    +555
    Local Time:
    7:12 AM
    1.13.x
    MariaDB 10.1.x
    How large is your db?
     
  6. jair

    jair New Member

    16
    1
    3
    Jan 8, 2017
    Ratings:
    +1
    Local Time:
    2:12 PM
    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.
     
  7. Jimmy

    Jimmy Premium Member Premium Member

    1,026
    231
    63
    Oct 24, 2015
    East Coast USA
    Ratings:
    +555
    Local Time:
    7:12 AM
    1.13.x
    MariaDB 10.1.x
    The reason I asked is because I'm going to be doing that same thing on my testing server today. So, I'll see if I have the same issues as you do via running it in IPB.

    Since it's running via IPB, you might want to adjust your PHP settings... just a thought.

    If you have a testing server setup, I'd try to run the query manually via the command line and see if you're having the same issues - that it's a mysql issue or not.
     
  8. Revenge

    Revenge Active Member

    287
    64
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +227
    Local Time:
    12:12 PM
    1.9.x
    10.1.x
    I have done that a few months ago on a 3Gb database. I didn't found any issue at that time.
     
  9. jair

    jair New Member

    16
    1
    3
    Jan 8, 2017
    Ratings:
    +1
    Local Time:
    2:12 PM
    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.
     
  10. Jimmy

    Jimmy Premium Member Premium Member

    1,026
    231
    63
    Oct 24, 2015
    East Coast USA
    Ratings:
    +555
    Local Time:
    7:12 AM
    1.13.x
    MariaDB 10.1.x
    You're trying this on IPB 4.x right?
     
  11. jair

    jair New Member

    16
    1
    3
    Jan 8, 2017
    Ratings:
    +1
    Local Time:
    2:12 PM
    Yes. I am converting from 3.4 first and then trying it on 4.1.
     
  12. eva2000

    eva2000 Administrator Staff Member

    29,033
    6,589
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,782
    Local Time:
    9:12 PM
    Nginx 1.13.x
    MariaDB 5.5
    on the server in directory with error log, try grepping for keyword 'FastCGI' and see what errors are outputted

    in directory with error log
    Code (Text):
    grep 'FastCGI' error.log
    

    that gives summary

    then to extend output to next 4 lines after FastCGI occurrence, in directory with error log
    Code (Text):
    grep -A4 'FastCGI' error.log
    

    Might want to use CODE tags for code How to use forum BBCODE code tags :)