Tagged: MySQL
SQLSTATE[HY000]: General error: 1364 Field doesn't have a default value
- by Oleg Ivanchenko
I have got an error:
SQLSTATE[HY000]: General error: 1364 Field 'XXXX' doesn't have a default value
Happened with MySQL 5.7.12, Mac OS X 10.11.5. MySQL installed from a package from mysql.com.
The obvious solution does not work: adding default value into SQL code (default '') generates another error for a TEXT field:
SQLSTATE[42000]: Syntax error or access violation: 1101 BLOB, TEXT, GEOMETRY or JSON column 'XXXX' can't have a default value
Thus I had to update MySQL settings. The default MySQL strict mode has to be disabled.
In my case there were no /etc/my.cnf file. The default settings were in /usr/local/mysql/support-files/my-default.cnf. I added /etc/my.cnf with the following code in it:
[mysqld]<br>sql_mode=NO_ENGINE_SUBSTITUTION
And of course MySQL should be restarted :-) Look here for more details.
It solved the problem.
MySQL start/stop/restart in Mac OS X
- by Oleg Ivanchenko
How to start/stop/restart MySQL in Mac OS X? Very easy!
In OS X Yosemite / El Capitan (MySQL post 5.7) run in terminal:
sudo launchctl load -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
Note: This is a persistent setting which means that MySQL will automatically start on the next OS X start.
sudo launchctl unload -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
In OS X pre Yosemite (MySQL pre 5.7) run in terminal:
sudo /usr/local/mysql/support-files/mysql.server start
sudo /usr/local/mysql/support-files/mysql.server stop
sudo /usr/local/mysql/support-files/mysql.server restart
Quite easy :-)
MySQL innodb_buffer_pool_size optimal value
- by admin
innodb_buffer_pool_size is a quite important MySQL configuration parameter which can dramatically increase your DB productivity. The larger you set this value, the less disk I/O is needed to access data in tables. Just today I have got one of a heavy SQL query time changed from 15.5 to 1.2 seconds by changing innodb_buffer_pool_size from 23M to 320M! On a dedicated database server, you may set this to up to 80% of the machine physical memory size. Of course, you need to be careful with memory consumption, especially for a non-dedicated server.To get optimal value of innodb_buffer_pool_size for your DB you may run this SQL:
SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS
FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;
It would return optimal size calculated as sum of DB stored data and indexes multiple by 1.6. But it does not know anything about you physical memory! Thus set innodb_buffer_pool_size not more than 15% of RAM size. To test how much memory your new configured MySQL would maximum consume, use this mysqltuner Perl script. You can also use this script for optimizing of your MySQL DB other settings.
The last but not the least: innodb_buffer_pool_size has to be put in mysqld section of you MySQL configuration file (/etc/my.conf in RHEL):
[mysqld]
innodb_buffer_pool_size=2G
Find duplicate records in MySQL
- by admin
So, the task is to get duplicate records from a MySQL database.The easy way:
SELECT COUNT(*), column1, column2 FROM tablename
GROUP BY column1, column2
HAVING COUNT(*)>1;
More complex case: shows each duplicated row:
It can be done using subquery:
SELECT firstname, lastname, list.address FROM list
INNER JOIN (SELECT address FROM list
GROUP BY address HAVING count(id) > 1) dup ON list.address = dup.addressor with INNER JOIN:SELECT a.firstname, a.lastname, a.address
FROM list a
INNER JOIN list b ON a.address = b.address
WHERE a.id <> b.idIf the same 'address' exist more than two times, then DISTINCT is needed.
Mac OS, MySQL: No such file or directory (trying to connect via unix:///
- by admin
Error on attempt to connect locally to MySQL server DB with PHP on MacOS X Lion 10.7:No such file or directory (trying to connect via unix:///var/mysql/mysql.sock)
Solution 1:
instead of localhost use 127.0.0.1:
mysql_connect ('127.0.0.1', $user, $password);Solution 2:
In /etc/php.ini change
pdo_mysql.default_socket = /var/mysql/mysql.sock
mysql.default_socket = /var/mysql/mysql.sock
mysqli.default_socket = /var/mysql/mysql.sock
to
pdo_mysql.default_socket = /tmp/mysql.sock
mysql.default_socket = /tmp/mysql.sock
mysqli.default_socket = /tmp/mysql.sock
Do not forget to restart Apache after update :-)
FYI: If no /etc/php.ini found just copy /etc/php.ini.default to /etc/php.ini
MySQL: ERROR 2006 (HY000) at line: ### MySQL server has gone away
- by admin
In case of MySQL error 2006 (HY000) at line: ### MySQL server has gone away which may occur while restoring a big DB dump just add (or increase, if exists) max_allowed_packet parameter of MySQL config (usually in /etc/my.cnf).For example:
[mysqld]
max_allowed_packet=128M
And do not forget to restart MySQL server :-)
Mac OS X - MySQL Workbench - Error Opening Configuration File my.cnf
- by admin
By default, the OS X installation does not use a my.cnf, and MySQL just uses the default values.To set up your own my.cnf, you could just create a file straight in /etc, or do the following (excuse me if I say anything which is obvious to you, but this may help complete OS X beginners who are not familiar with the Unix command line):
Log in to OS X using an administrator-level account (to keep things simple lower down)
Open Terminal (in Utilities folder under Applications folder)
cd /usr/local/mysql/support-files/
sudo cp my-huge.cnf /etc/my.cnf
and enter your admin password when prompted. You could do this from a non-admin account by using the su command, but that's probably a bit scary for some people ;)
You will now have a copy of my.cnf in /etc (just in case you don't know, that means the etc folder directly under the root folder, not under MySQL's install folder)
You can edit it with a text-editor such as TextWrangler by using File->Open Hidden, or if you are happy to use the command line, use:
cd /etc
sudo nano my.cnf
To exit without saving, press CTRL+X, to Save then exit it's: CTRL+O then CTRL+X
How to get the Next Auto Increment number in MySQL
- by admin
In order to get the next Auto Increment number in MySQL just run:SHOW TABLE STATUS LIKE '$tablename';
The result would be in `Auto_Increment`.
Voila !
MySQL Frequently Used Commands
- by admin
Selecting a database:
mysql> USE database;
Listing databases:
mysql> SHOW DATABASES;
Listing tables in a db:
mysql> SHOW TABLES;
Describing the format of a table:
mysql> DESCRIBE table;
Creating a database:
mysql> CREATE DATABASE db_name;
Creating a table:
mysql> CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE));
Ex: mysql> CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);
Generate the create statement of a table in MySQL:
mysql> SHOW CREATE TABLE tblname;
Load tab-delimited data into a table:
mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name;
(Use \n for NULL)
Inserting one row at a time:
mysql> INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31');
(Use NULL for NULL)
Retrieving information (general):
mysql> SELECT from_columns FROM table WHERE conditions;
All values: SELECT * FROM table;
Some values: SELECT * FROM table WHERE rec_name = "value";
Multiple critera: SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2";
Reloading a new data set into existing table:
mysql> SET AUTOCOMMIT=1; # used for quick recreation of table
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table;
Fixing all records with a certain value:
mysql> UPDATE table SET column_name = "new_value" WHERE record_name = "value";
Selecting specific columns:
mysql> SELECT column_name FROM table;
Retrieving unique output records:
mysql> SELECT DISTINCT column_name FROM table;
Sorting:
mysql> SELECT col1, col2 FROM table ORDER BY col2;
Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC;
Date calculations:
mysql> SELECT CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(date_col)) AS time_diff [FROM table];
MONTH(some_date) extracts the month value and DAYOFMONTH() extracts day.
Pattern Matching:
mysql> SELECT * FROM table WHERE rec LIKE "blah%";
(% is wildcard - arbitrary # of chars)
Find 5-char values: SELECT * FROM table WHERE rec like "_____";
(_ is any single character)
Extended Regular Expression Matching:
mysql> SELECT * FROM table WHERE rec RLIKE "^b$";
(. for char, [...] for char class, * for 0 or more instances
^ for beginning, {n} for repeat n times, and $ for end)
(RLIKE or REGEXP)
To force case-sensitivity, use "REGEXP BINARY"
Counting Rows:
mysql> SELECT COUNT(*) FROM table;
Grouping with Counting:
mysql> SELECT owner, COUNT(*) FROM table GROUP BY owner;
(GROUP BY groups together all records for each 'owner')
Selecting from multiple tables:
(Example)
mysql> SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name;
(You can join a table to itself to compare by using 'AS')
Currently selected database:
mysql> SELECT DATABASE();
Maximum value:
mysql> SELECT MAX(col_name) AS label FROM table;
Auto-incrementing rows:
mysql> CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT, name CHAR(10) NOT NULL);
mysql> INSERT INTO table (name) VALUES ("tom"),("dick"),("harry");
Adding a column to an already-created table:
mysql> ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name;
Removing a column:
mysql> ALTER TABLE tbl DROP COLUMN col;
(Full ALTER TABLE syntax available at mysql.com.)
Batch mode (feeding in a script):
# mysql -u user -p < batch_file
(Use -t for nice table layout and -vvv for command echoing.)
Alternatively: mysql> source batch_file;
Backing up a database with mysqldump:
# mysqldump --opt -u username -p database > database_backup.sql
(Use 'mysqldump --opt --all-databases > all_backup.sql' to backup everything.)
