2

I got error Variable 'collation_connection' can't be set to the value of 'NULL' when using sequelPro to dump .sql file.

Here is the beginning of my .sql file:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

Here is the end of my .sql file:

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

I've run mysql -u root -p -e "set global net_buffer_length=1000000; set global max_allowed_packet=1000000000;" according to answers on stackoverflow.But it's still not working.

Server version: 5.7.11 Homebrew

yongyu
  • 97
  • 1
  • 8

3 Answers3

4

The COLLATION_CONNECTION is a server variable. MySQL throws an error when NULL value is set to this variable. For example:

SET COLLATION_CONNECTION = null; -- error!
SET COLLATION_CONNECTION = @OLD_COLLATION_CONNECTION; -- error, when @OLD_COLLATION_CONNECTION is NULL!

It is possible that sequelPro used different MySQL sessions (connections) to run the script.

  • SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION - was run at one session
  • SET COLLATION_CONNECTION = @OLD_COLLATION_CONNECTION - was run at another session, where @OLD_COLLATION_CONNECTION had not been set.
Devart
  • 119,203
  • 23
  • 166
  • 186
  • Thanks for the info. This was exactly what was happening in my case as the database file was large and timed-out. Adding /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; before /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; at the end of my sql file fixed this error for me – Colm Fitzpatrick Feb 25 '21 at 21:24
2

I was using sequelPro when I run this .sql file and then got this error.But I tried mysql -u username -p databasename < filename.sql and it worked!I have no idea of what's going on.But it worked.Hope this can help anyone who got the same problem.

yongyu
  • 97
  • 1
  • 8
0

I had the same error info when using sequel pro, but using Navicat does not have the problem.

shaojun lyu
  • 184
  • 1
  • 6