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.

  1. Mysql Workbench Download
  2. Mysql Workbench Browse Tables
  3. Mysql Workbench Brewing Company

Question or issue on macOS:

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?

MySQL version:

my.cnf:

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:

Argh!

How to solve this problem?

Solution no. 1:

On Centos 6.5 i had to edit /usr/my.cnf
and set (even though /etc/my.cnf existed and bindings were successfully set there

Solution no. 2:

@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 /usr/local/mysql.

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?

Solution no. 3:

Mysql workbench brewing

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.

Solution no. 4:

Please check /usr/local/mysql/my.cnf and comment out the problematic setting.

Source: MySQL Strict Mode on OS X

Solution no. 5:

You need to edit:

and set

Hope this helps!

Related

Join the DigitalOcean Community

Join 1M+ other developers and:

  • Get help and share knowledge in Q&A
  • Subscribe to topics of interest
  • Get courses & tools that help you grow as a developer or small business owner
Brewery
Cannot import a DB from a shared hosting at Bluehost to a DB on Vesta CP Question

Question

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!

Related

Mysql Workbench Download

Join the DigitalOcean Community

Join 1M+ other developers and:

Mysql Workbench Browse Tables

  • Get help and share knowledge in Q&A
  • Subscribe to topics of interest
  • Get courses & tools that help you grow as a developer or small business owner
Cannot import a DB from a shared hosting at Bluehost to a DB on Vesta CP Question
  • FunShedJuly 28, 2020

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.

Mysql Workbench Brewing Company

×