Problems after upgrading MySQL to 5.7

[CoyoteKG]

Hello, I'm in panic

I have dedicated Hetzner server with Debian 7

I need to instal Magento 2.0, and I could not do that because I did not have proper PHP and MySQL version.

Firstly I upraded Plesk version from 12.0 to 12.5, and after that I updated PHP without problems.
After that I updated Mysql with this tutirial
https://kb.plesk.com/en/127962

But after that problems started and I don't have idea how to solve it.
Few sites works fine, but one the most important not.
Also htop command showing me that server is overload


During instalation I chose to create new my.cnf file.
I tried to back old my.cnf, but mysql service could not start, so I leave new my.cnf file.

Here in attachment is that new my.cnf file.

Also, I add error log file in attachment, and I don't understand it, and how to solve that problem.

Please help me. Where to start?


edit:
Now all others sites stops work also...
In Plesk panel I have this warning

Internal error.
ERROR: PleskDBException: Unable to connect to database: mysql_connect(): Too many connections (Error code: 1040) (Abstract.php:69)

 

[IgorG]

Unable to connect to database: mysql_connect(): Too many connections (Error code: 1040) (Abstract.php:69)

I would suggest you check this KB article https://kb.plesk.com/en/769

 

[CoyoteKG]

Haj Igor,
I already found that article and set max number of connection.
But other problems are still there...

Can you help me about that :/

 

[IgorG]

I'm not sure, but maybe it is related to fact that MySQL 5.7 is not supported by Plesk yet.

 

[CoyoteKG]

I know it is related with MySQL 5.7, but I hoped that you have prepared "magic" script, or experience with this :/

In appache error log I found a ton of this logs

[Mon Apr 25 04:40:07 2016] [warn] mod_fcgid: process 7911 graceful kill fail, sending SIGKILL
[Mon Apr 25 04:40:11 2016] [warn] mod_fcgid: process 7915 graceful kill fail, sending SIGKILL
[Mon Apr 25 04:40:15 2016] [warn] mod_fcgid: process 7918 graceful kill fail, sending SIGKILL
[Mon Apr 25 04:40:27 2016] [warn] mod_fcgid: process 7927 graceful kill fail, sending SIGKILL
[Mon Apr 25 04:40:27 2016] [warn] mod_fcgid: process 7926 graceful kill fail, sending SIGKILL
[Mon Apr 25 04:40:31 2016] [warn] mod_fcgid: process 7931 graceful kill fail, sending SIGKILL
[Mon Apr 25 04:40:59 2016] [warn] mod_fcgid: process 7942 graceful kill fail, sending SIGKILL
[Mon Apr 25 04:40:59 2016] [warn] mod_fcgid: process 7944 graceful kill fail, sending SIGKILL
[Mon Apr 25 04:41:03 2016] [warn] mod_fcgid: process 7948 graceful kill fail, sending SIGKILL
[Mon Apr 25 04:41:07 2016] [warn] mod_fcgid: process 7951 graceful kill fail, sending SIGKILL
[Mon Apr 25 04:41:11 2016] [warn] mod_fcgid: process 7956 graceful kill fail, sending SIGKILL
[Mon Apr 25 04:41:23 2016] [warn] mod_fcgid: process 7963 graceful kill fail, sending SIGKILL
[Mon Apr 25 04:41:23 2016] [warn] mod_fcgid: process 7961 graceful kill fail, sending SIGKILL
[Mon Apr 25 04:41:27 2016] [warn] mod_fcgid: process 7966 graceful kill fail, sending SIGKILL

And in htop, I see that all of mysql processes start, one by one, and overload CPU, but not finished

 

[Dave W]

Log into mysql and see what the query is that hanging the mysql server?

 

[CoyoteKG]

here, if you mean this

root@server ~ # mysqladmin -uadmin -p`cat /etc/psa/.psa.shadow` processlist
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
+------+--------------+-----------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id   | User         | Host      | db           | Command | Time | State        | Info                                                                                                 |
+------+--------------+-----------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 978  | fc_shop_live | localhost | fc_px90_live | Query   | 1935 | Sending data | SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_a |
| 981  | fc_shop_live | localhost | fc_px90_live | Query   | 1924 | Sending data | SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_a |
| 994  | fc_shop_live | localhost | fc_px90_live | Query   | 1901 | Sending data | SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_a |
| 1031 | fc_shop_live | localhost | fc_px90_live | Query   | 1855 | Sending data | SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_a |
| 1041 | fc_shop_live | localhost | fc_px90_live | Query   | 1839 | Sending data | SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_a |
| 1067 | fc_shop_live | localhost | fc_px90_live | Query   | 1791 | Sending data | SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_a |
| 1068 | fc_shop_live | localhost | fc_px90_live | Query   | 1788 | Sending data | SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_a |
| 1071 | fc_shop_live | localhost | fc_px90_live | Query   | 1786 | Sending data | SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_a |
| 1098 | fc_shop_live | localhost | fc_px90_live | Query   | 1758 | Sending data | SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_a |
| 1101 | fc_shop_live | localhost | fc_px90_live | Query   | 1751 | Sending data | SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_a |
| 1102 | fc_shop_live | localhost | fc_px90_live | Query   | 1750 | Sending data | SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_a |
| 1113 | fc_shop_live | localhost | fc_px90_live | Query   | 1737 | Sending data | SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_a |
| 1114 | fc_shop_live | localhost | fc_px90_live | Query   | 1735 | Sending data | SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_a |
| 1123 | fc_shop_live | localhost | fc_px90_live | Query   | 1716 | Sending data | SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_a |
| 1129 | fc_shop_live | localhost | fc_px90_live | Query   | 1703 | Sending data | SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_a |
.
.
.
.
and much more...

 

[CoyoteKG]

Hello again,

I solved this problem with this solution
https://talk.plesk.com/threads/ples...kill-fail-sending-sigkill.319029/#post-755701

it works for me

thanks

 

[CoyoteKG]

Hello again.

obviously, with
IPCCommTimeout 120
I fixed something, and my server is online, and site...
But, maybe that is not solution.
Because, now I need to reindex magento db, and server and mysql processes again go crazy...

In mysql log, when I run reindex, log is full with

2016-04-30T17:19:51.900066Z 6 [Note] Aborted connection 6 to db: 'bc_bla_live' user: 'bc_blop_live' host: 'localhost' (Got an error writing communication packets)
2016-04-30T17:19:53.832372Z 2 [Note] Aborted connection 2 to db: 'bc_bla_live' user: 'bc_blop_live' host: 'localhost' (Got an error writing communication packets)
2016-04-30T17:19:54.472228Z 8 [Note] Aborted connection 8 to db: 'bc_bla_live' user: 'bc_blop_live' host: 'localhost' (Got an error writing communication packets)
2016-04-30T17:20:01.157996Z 9 [Note] Aborted connection 9 to db: 'bc_bla_live' user: 'bc_blop_live' host: 'localhost' (Got an error writing communication packets)
2016-04-30T17:20:12.985951Z 5 [Note] Aborted connection 5 to db: 'bc_bla_live' user: 'bc_blop_live' host: 'localhost' (Got an error writing communication packets)
2016-04-30T17:20:14.538040Z 10 [Note] Aborted connection 10 to db: 'bc_bla_live' user: 'bc_blop_live' host: 'localhost' (Got an error writing communication packets)
2016-04-30T17:20:15.378833Z 3 [Note] Aborted connection 3 to db: 'bc_bla_live' user: 'bc_blop_live' host: 'localhost' (Got an error writing communication packets)
2016-04-30T17:20:17.875258Z 7 [Note] Aborted connection 7 to db: 'bc_bla_live' user: 'bc_blop_live' host: 'localhost' (Got an error writing communication packets)
2016-04-30T17:21:31.345378Z 32 [Note] Aborted connection 32 to db: 'bc_bla_live' user: 'bc_blop_live' host: 'localhost' (Got an error writing communication packets)
2016-04-30T17:21:41.228238Z 31 [Note] Aborted connection 31 to db: 'bc_bla_live' user: 'bc_blop_live' host: 'localhost' (Got an error writing communication packets)
2016-04-30T17:21:42.858007Z 39 [Note] Aborted connection 39 to db: 'bc_bla_live' user: 'bc_blop_live' host: 'localhost' (Got an error writing communication packets)
2016-04-30T17:21:45.182597Z 40 [Note] Aborted connection 40 to db: 'bc_bla_live' user: 'bc_blop_live' host: 'localhost' (Got an error writing communication packets)
2016-04-30T17:22:04.758019Z 62 [Note] Aborted connection 62 to db: 'bc_bla_live' user: 'bc_blop_live' host: 'localhost' (Got an error writing communication packets)
2016-04-30T17:22:04.941078Z 61 [Note] Aborted connection 61 to db: 'bc_bla_live' user: 'bc_blop_live' host: 'localhost' (Got an error writing communication packets)
2016-04-30T17:22:06.634843Z 64 [Note] Aborted connection 64 to db: 'bc_bla_live' user: 'bc_blop_live' host: 'localhost' (Got an error writing communication packets)
2016-04-30T17:22:49.681305Z 72 [Note] Aborted connection 72 to db: 'bc_bla_live' user: 'bc_blop_live' host: 'localhost' (Got an error writing communication packets)

I found this article, but I'm now with this stuf and it is no clearly to me...

The client program did not call mysql_close() before exiting.

Is that command IPCCommTimeout 120 kill proccess after 120 seconds, and those logs are because that?

How to check in Plesk if my DB is Remotely enabled?
My fail2ban blocked today more then 40 IPs. Maybe someone trying to connect to my DB?
Do I need enabled remote, just for web hosting?

This is my htop

And when I delete IPCCommTimeout 120 from Plesk's webspace appache settings... number of mysql processes contstantly growing up... Till 250...
And Load Average over 70...
After that I need to kill mysql service...

What to do next?

 

[Linulex]

sometimes "stuff" hangs for reasons only known to Gandhi, the dalai lama and my granny. And the only real solutions then is to restart the complete server. Have you tried that? i mean, giving it a hard reboot, power on, power off ...

and tbh, don't run magento on a plesk server. magento is very resource heavy, it needs memcashe, varnish, and a lot of other stuff to run half decent.

Plesk is very, very good to use for shared hosting, its less good for dedicated fast single webshop hosting.

plesk is a truk, magento needs a ferrarri

reagrds
Jan

 

[Linulex]

power on, power off ...

i know someone is going to ask ... no the order is not a drunk error ... remember the karate kid? ... wax on ...etc ...

regards
Jan

 

[CoyoteKG]

Yes, I restarted it several times, because I needed to do that in some moments... For example, once I had over 300 mysql running processes, and I could not stop mysql service... :/. And In that case I needed to reboot it...

I have dedicated server with Intel Xeon E5-1650 and 64GB of RAM. And when I fix all of those problem that now I have, in plan is to see how to set varnish if it is possible to do myself

I'm using plesk for most things, but not for everything.
I'm new with Linux, MySQL and Plesk, and for now it is not so easy for me . But plesk helping me so much.

 

[Linulex]

As Igor sayd: 5.7 isn't supported by plesk (yet) and as far as i know: magento 2.x needs 5.6, not 5.7.

http://devdocs.magento.com/guides/v2.0/install-gde/system-requirements.html

I know it isn't very helpfull but in my opinion there is only 1 way to fix this:

- restore the mysql to before the upgrade
- remove the mysql 5.7. delete the rpm's, install the previous installed version, and replace the upgraded databases with the ones from the backup you made just prior to upgrading
- install 5.6, as explaned in the how to in the kb on page https://kb.plesk.com/en/127962

I know that is what you used to install 5.7, but what is the point in following a how to if you don't follow it? The KB article says in bod at the top:

[HOW TO] Upgrade MySQL 5.5 to 5.6

regards
Jan

 

[CoyoteKG]

Surely I'm in wrong, but I think that somewhere I found that Plesk support Mysql 5.7 on Debian. And that Magento support 5.6 and 5.7 .
And now I can't find that link to show you... But that is not important now...


What do you mean with "restore the mysql to before the upgrade?"
I have backup --all-databases before I was upgrade...

Do I can just to remove mysql 5.7 and rpm's? And after that I need to instal 5.5??? And after that upgrade to 5.6?

And can you explain me, what kind of problems MySQL 5.7 can do with Plesk?

 

[PriyanA]

Agree with @Linulex

- Remove your MySQL completely from your system.
- Disable any repos which you used to install MySQL 5.7
- Install MySQL with your system default repo.
- Restore the Database using your BACKUP
- At this stage your Plesk should start working.
- Upgrade yo My 5.6

 

[Linulex]

I think that somewhere I found that Plesk support Mysql 5.7 on Debian.

Don't believe unofficial sources. I am sure somewhere, some super sysadmin might have got it working, but for us mere mortals, only the official plesk page is correct.

http://docs.plesk.com/release-notes/12.5/software-requirements/

Database servers and tools:

• MySQL 5.0.x-5.6

regards
Jan

 

[Oliver Meyer]

Have you run 'mysql_upgrade'? Btw. - MariaDB 10.1 runs without any problems with Plesk / Magento 2.x.

 

[CoyoteKG]

Hello, sorry for late respond. I was on some business trip...

OK, I moved this one problematic base to another server with 5.6 MySQL and it seems like works fine.
Now I need like you said, to remove completely MySQL server, and install new one...
But I don't understand why I need to back it to 5.5 and after that upgrade it to 5.6? Why not install it to 5.6 straightaway?


@Oliver Wagner Yes I did that. And I even dump all databases and import it back again. I rwas red that will rebuild them correctly, but unfortunately, with this one Magento site I still have problems...

Site works fine, but when I start reindex from SSH, CPU go crazy... Too many slow queries which try to execute over 500K rows... over 50 mysql proccesses are in Running state, and cannot be finished.
Funny is that, with 5.5 maybe I had some slow queries, I did not follow this, but now also on that onother server, I can start reindex without problem...

And one more thing, I noticed that you think that Plesk not working...
No... Plesk working perfectly, even this mysql is 5.7.
I had that problem with connections, at the same time when I runn reindex, and CPU go crazy because too many mysql started proccessess in state Running.

 

[Oliver Meyer]

Hm... don't know what's the problem there.
I'm running MariaDB on all my servers (installed before Plesk) without any problems.
Absolutely no problem with reindexing (big) magento shops. Maybe the different MySQL
versions have different default settings/limits. Have you adjusted your MySQL configuration?
Especially connections, threads and buffer pool.

 

[shoxicwaste]

@CoyoteKG If it makes you feel any better I've been having the same issues - Client wants a Magento 2.0 installation, which means MySQL 5.6 > ...... ffs.

After a paid SI completely destroyed my dev environment I had to do it myself.. Just out of interest, did you encounter any issues with the lib64 connectors?