I’ve faced the same issue with latest MySQL Client (>5.7) while trying to connect lower versions of MySQL like 5.1.xx.
To avoid this issue (ERROR 2027 (HY000): Malformed packet), create a user with latest password authentication.
ex:
Login to MySQL 5.1.xx server and execute..
mysql> create user 'testuser'@'xx.xx.xxx.%' identified by 'testuser_Secret1';
Check if you have old_passwords enabled, then disable it for that session.
mysql> show session variables like 'old_passwords';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| old_passwords | ON |
+-----------------+-------+
mysql> set session old_passwords = 0;
mysql> GRANT select on test.* TO 'testuser'@'xx.xx.xxx.%' identified by 'testuser_Secret1';
Verify password that should begin with «*SOMETHING1123SHOWNBELOW3034».
mysql> select user,host,password from mysql.user where user = 'testuser';
+-----------+---------------+-------------------------------------------+
| user | host | password |
+-----------+---------------+-------------------------------------------+
| testuser | xx.xx.xxx.% | *053CB27FDD2AE63F03D4A0B919E471E0E88DA262 |
+-----------+---------------+-------------------------------------------+
Now try logging from MySQL 5.7.xx Client and try to establish a connection to MySQL 5.1.xx server.
[testuser@localhost]# mysql -V
mysql Ver 14.14 Distrib 5.7.31, for Linux (x86_64) using EditLine wrapper
[testuser@localhost]# mysql -hxx.xx.xxx.xxx -u testuser -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1528853
Server version: 5.1.73-log Source distribution
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>
I have a mysql database (Version 5.6.23-log running on windows) that is set up for use with Teamcity. When running a command against it using HeidiSQL I am getting an error:
SQL Error (2027): Malformed packet
This error only occurs if the result set is more than a certain number of rows (using LIMIT 14561 rows works, 14562 gives this error).
I believe this error is not due to Heidi since TeamCity is failing to start when running the same command.
I assume it is something to do with the data size becoming too large and something failing but I can’t find any way to identify more specifically what the problem is and thus how to fix it.
If anybody can tell me what I can do to prevent this problem or at the very least how I might be able to better diagnose this problem I’d be most grateful.
asked May 27, 2015 at 14:34
ChrisChris
1331 gold badge1 silver badge8 bronze badges
2
My guess would be that you have TEXT/BLOB data and that the row data in the 14562 row is bigger than your current MySQL Packet, which is sized by max_allowed_packet.
MySQL Perspective
You need to reserve more space for max_allowed_packet
Jul 29, 2013: Max_packet_allowed setting for Windows 7Jul 03, 2013: What max_allowed_packet is big enough, and why do I need to change it?Jun 12, 2012: MySQL Error Reading Communication Packets
Your InnoDB Redo Logs (sized by innodb_log_file_size) may need to be increased as well. I first learned of it (in relation to the MySQL Packet) from ServerFault. I refer to that ServerFault post in some of my posts:
Apr 27, 2011: Changed max_allowed_packet and still receiving ‘Packet Too Large’ errorAug 01, 2011: How does max_allowed_packet affect the backup and restore of a database?
I would max out the max_allowed_packet to 1G and resize your redo logs to 1.5G
[mysqld]
max_allowed_packet = 1G
innodb_log_file_size = 1536M
Next, run this to purge all transactions out of the redo logs:
mysql> SET GLOBAL innodb_fast_shutdown = 0;
Then, restart mysql (which is required).
Note to everyone running MySQL 5.5 and prior
MySQL 5.6 will handle resizing logs for you when restarting. Previous versions of MySQL require that you do that manually. Your steps after changing my.cnf and setting innodb_fast_shutdown to 0, you would do the following
service mysql stop
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak
service mysql start
HeidiSQL Perspective
One very common complaint is Error 2027, particularly with using LOAD DATA INFILE
Some have suggested using older versions of libmysql.dll
- HeidiSQL 8.1 released
- sql error(2007) Malformed packet
Some suspect a character set issue : Error Code: 2027 Malformed packet
GIVE IT A TRY !!!
answered May 27, 2015 at 17:18
RolandoMySQLDBARolandoMySQLDBA
177k32 gold badges307 silver badges505 bronze badges
6
None of the above advices were helpful in my case. Turns out it was a bug in MySQL and not a wrong configuration as suggested in the other answers. Changing max_allowed_packet etc had no effect whatsoever.
Then I found this: https://bugs.mysql.com/bug.php?id=77298
«This is duplicate of internal bug Bug#20895852 which is fixed. Noted
in 5.6.25, 5.7.8, 5.8.0 changelogs.»For small values of the read_rnd_buffer_size system variable,
internal caching of temporary results could fail and cause query
execution failure.
I went from 5.6.22 to 5.6.26 and the issue went away without any configuration changes.
All I did was
brew upgrade mysql
and restart the server in a new terminal tab using
mysql.server restart
That was all.
Chris
1331 gold badge1 silver badge8 bronze badges
answered Aug 19, 2015 at 20:48
1
Same issue haunted my team for a while, and there’s very little useful information about it on the web. We spent a lot of time troubleshooting the issue — and FINALLY found solution that solved it (at least — for our team)!
We found that Django sets «charset» option to «utf8» for database connections by default. During troubleshooting we used two separate database connection objects: one created for us by Django, and another — created manually using direct _mysql.connect() command. When we executed same exact query using both connection objects — the one created by Django resulted in «django.db.utils.OperationalError: (2027, ‘Malformed packet’)» (which is exactly what we were getting in our API), but the second connection (manual) — worked without any issues. Further comparison of the two connection objects (we actually had to use Python debugger «pdb» and set breakpoint within django.db.backends.mysql.base.py for that) — revealed that Django creates connection by passing «charset»:»utf8″, while manual _mysql connection — uses «latin1». As soon as we added «charset»:»latin1″ to DATABASES[«default»][«OPTIONS»] within our settings.py — this error disappeared.
To summarize, solution (for us) was to explicitly set «charset»:»latin1″ within «OPTIONS» config section of DATABASES configuration — for every database alias. I can not say for sure that this will work for everyone who experiences this error — but it certainly works for us.
In general, any MySQL connection that uses «utf8» charset would perform the same way (generate this error); replacing that with «latin1» should solve it.
answered Mar 12, 2021 at 0:23
In the end I found that if I dumped the data out of one database and into another on a different machine that things seemed to work. I thus decided to do a compare of the two config files and update values that were different. In doing so I updated the following:
tmp_table_sizefrom5Mto19Mmyisam_sort_buffer_sizefrom8Mto30Mread_buffer_sizefrom0to59Kread_rnd_buffer_sizefrom0to256Ksort_buffer_sizefrom0to256Kquery_cache_typefrom1to0
Doing this fixed my issues. I’m sure not all of them are needed so I would be happy for somebody to explain why this fixed my problem in a better answer (and will gladly transfer acceptance to them in that case).
answered May 28, 2015 at 10:27
ChrisChris
1331 gold badge1 silver badge8 bronze badges
4
- Автор темы
-
#1
Столкнулся с такой ошибкой
«Malformed packet»
Пошел гуглить нашел такое
Ошибка: 2027 (CR_MALFORMED_PACKET)
Сообщение: Malformed packet
вот тут Для просмотра ссылки Войди или Зарегистрируйся
На этом полезная инфа из гугла иссякла, может кто знает что это за ошибка. и как ее исправить, что нужно подкрутить
на мускульном клиента, ошибка явно лезет из за клиента?
- Модер.
-
#2
Да, причина явно в клиенте. Эта ошибка вылетала часто после того, как в mysql изменился формат обмена данными, но разработчики клиентов довольно быстро обновили свои продукты. Вы, к сожалению, не описали какой у вас клиент и его версию, поэтому довольно сложно что-либо рекомендовать.
-
#3
2PHPCod3r
Такая ошибка возникает еще при отсутствии прав на файлы используемые mysql при запросах.
- Автор темы
-
#4
-
#5
Ели маська собиралась без
тогда явно желательно ее перекомпилить, где-то видел мануал как подключать без пересборки, но там гемора столько, что проще пересобрать маську
- Автор темы
-
#6
…..
тогда явно желательно ее перекомпилить, где-то видел мануал как подключать без пересборки, но там гемора столько, что проще пересобрать маську
Если есть мануал дайте ссылку, будет интересно глянуть….
Ок, установкой заново конечно хороша, но не всегда приемлема. особено на шареных хостингах )
А хочется разобраться в вопросе, почему из консоли работает а из пхп нет.????
Чем стандартный мускульных консольный клиент, отличается от пхпшного?
Вот скажем полез я в маны , там пишут что если у нас пхп 5.2, клиентские библиотеки для работы с мускулем, вместе с ним не идут, а подрубаются к пхп при компиляции таким образом
—with-mysql[=DIR] где DIR это директория с установленным MySQL. обычно в ней можно такое найти
total 13560
drwxr-xr-x 2 root wheel 512 Aug 30 2010 .
drwxr-xr-x 20 root wheel 12800 Sep 10 2010 ..
-r—r—r— 1 root wheel 1414 Aug 17 2009 libdbug.a
-r—r—r— 1 root wheel 91314 Aug 17 2009 libheap.a
-r—r—r— 1 root wheel 460890 Aug 17 2009 libmyisam.a
-r—r—r— 1 root wheel 77976 Aug 17 2009 libmyisammrg.a
-rw-r—r— 1 root wheel 555166 Aug 17 2009 libmysqlclient.a
-rwxr-xr-x 1 root wheel 987 Aug 17 2009 libmysqlclient.la
lrwxr-xr-x 1 root wheel 20 Aug 17 2009 libmysqlclient.so -> libmysqlclient.so.16
-rwxr-xr-x 1 root wheel 479652 Aug 17 2009 libmysqlclient.so.16
-rw-r—r— 1 root wheel 565324 Aug 17 2009 libmysqlclient_r.a
-rwxr-xr-x 1 root wheel 1028 Aug 17 2009 libmysqlclient_r.la
lrwxr-xr-x 1 root wheel 22 Aug 17 2009 libmysqlclient_r.so -> libmysqlclient_r.so.16
-rwxr-xr-x 1 root wheel 489262 Aug 17 2009 libmysqlclient_r.so.16
-r—r—r— 1 root wheel 10227070 Aug 17 2009 libmysqld.a
-r—r—r— 1 root wheel 332350 Aug 17 2009 libmystrings.a
-r—r—r— 1 root wheel 371066 Aug 17 2009 libmysys.a
-r—r—r— 1 root wheel 8224 Aug 17 2009 libvio.a
Эти бинарники пхп и юзает?
Если кто в курсе обьясните плиз, очень замучал меня этот вопрос…