Managing with MySQL Workbench Install MySQL Workbench. Unless you’re trying out for the sequel to Swordfish or some hacker movie, you can take a break from mucking around with the Terminal for now. MySQL Workbench is a good tool for database rookies (like myself) and experts alike for managing your database. Download and install it from mysql. This procedure explains how to install MySQL and MySQL Workbench using Homebrew on macOS. This is a fork of these instructions, to make them more concise and solve the issues reported here. To install MySQL enter: brew install mysql; brew services start mysql; then run mysqlsecureinstallation and follow the instructions; MySQL Workbench.
It seems that MySQL recently (5.6?) changed the default SQL mode to be more restrictive. The new mode is “STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION”. I like the change, but at least one website that I maintain does not. INSERT queries are failing because they don’t specify values for columns that don’t have defaults. Before, MySQL would infer default values by column type.
For now, I want to disable STRICT_TRANS_TABLES. I have added sql_mode=NO_ENGINE_SUBSTITUTION to my.cnf and restarted the server but the strict setting persists. What am I doing wrong?
Confirm that mysqld would use settings in my.cnf:
Confirm that mysqld is not currently running:
Property list provided by Homebrew:
Start MySQL and check SQL mode:
On Centos 6.5 i had to edit
and set (even though
/etc/my.cnf existed and bindings were successfully set there
@ssnobody’s answer prompted me to search my entire system for my.cnf files. I had already checked the locations listed by
mysqld --help --verbose. It turns out that my server is using
/usr/local/Cellar/mysql/5.6.15/my.cnf which I had wrongly assumed to be a sample file. The file is not symlinked from any of the standard locations, including
Can anybody shed some light on this? Is it a Homebrew thing? How could I have figured this out except by making test modifications to every my.cnf that I could find?
To just disable the STRICT_TRANS_TABLES for a specific script,
when you initialize your db handle in your script. That will also disable NO_ENGINE_SUBSTITUTION, so just
will leave that intact.
/usr/local/mysql/my.cnf and comment out the problematic setting.
Source: MySQL Strict Mode on OS X
You need to edit:
Join 1M+ other developers and:
I’m trying to use LOAD DATA with the below command to import CSV files into a managed DO droplet via MySQL Workbench because the Workbench wizard is incredibly slow.
Running the above gets me ‘error 3948 loading local data is disabled’. So how can I enable loading local data you ask? I found this answer (https://stackoverflow.com/questions/59993844/error-loading-local-data-is-disabled-this-must-be-enabled-on-both-the-client) with a possible solution. But I’ve tried running SET GLOBAL local_infile=1 and I just get error code 1227. access denied you need (at least one of) the super privilege(s) for this operation.
So how can I get loading local data enabled, so I can use LOAD DATA and load my CSV into Workbench/my Database? or is there another fast way to access my database?
Any suggestions would be much appreciated!
Join 1M+ other developers and:
These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.