[MySQL][Resolved] find out your unix socket

login your  mysql usig root permission , command :

mysql -u root -p

and then paste this sql to find it out :

show variables like '%sock%';

then you will found there is a variable_name named socket and a value "/var/lib/mysql/mysql.sock" was stored there, in this case, /var/lib/mysql/mysql.sock is the unix socket.

Beware that your socket location maybe different.

[SQL][Resolved] Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails

Error Message:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`mhtest`.`test`, CONSTRAINT `tests_type_foreign` FOREIGN KEY (`type`) REFERENCES `options` (`option_id`) ON DELETE CASCADE) (SQL: update `tests` set `slug` = 5555555, `lang` = en, `title` = 5555555,  `type` = 218, `detail` = 44444444, `remarks` = , `updated_at` = 2017-07-21 01:46:02 where test_id` = 2)

There are reasons cause this error, and i double checked the constraint has been set at correct table and referenced to an existing column

In my case there are 2 tables, one is named "tests" and another is "options", a column "type" in table tests was set as foreign key referencing to column "option_id" in "options" table. Foreign key constraint "tests_type_foreign" is used for this relation.

Since error caused by constraint "tests_type_foreign", I found the value i update on column "tests.type" is not exists in table "options.option_id", that means the column you updated with foreign key will be referencing for nothing. So an error caused and won't allow you update.

To solve the problem please double check you are updating /inserting a existing value which's the foreign key referencing to.

Reference:

https://stackoverflow.com/questions/14063652/integrity-constraint-violation-1452-cannot-add-or-update-a-child-row

[MySQL][Resolved] MySQL kill running query

I get a strange situation that the application keep inserting records (over 10,000,000 records LOL) to MySQL database, it looks cause by an endless loop. After I close the broswer queries are skill keep inserting records, and then i restart broswer and clean all broswer's history it still keep going on.

And stackoverflow save me again.

Firstly, use this command to know all process id

show processlist;

Mark down this holy shit process id 1296, and then run then command , text in red is the process id:

kill 1296;

Reference

https://stackoverflow.com/questions/3787651/how-can-i-stop-a-running-mysql-query
https://stackoverflow.com/questions/1903838/how-do-i-kill-all-the-processes-in-mysql-show-processlist

[MySQL][sql] Select first four characters only from returned field

You can use the MySQL LEFT() function:

Syntax

LEFT (string, length)

Example:

SELECT date AS year FROM news

SELECT left(date,4) AS year FROM news 

Reference:
http://stackoverflow.com/questions/12504985/how-to-take-last-four-characters-from-a-varchar

[MySQL][SQL][Example] ORDER BY column and nulls value at last

If you want to order the MySQL records with a column and in ascending order, by default, null values is smaller than the numbers, and would makes the rows with null values placed at the beginning of your returned results.

An Example:

SELECT c.section_id, c.url_name, c.lineage, c.url, c.sorting
FROM tbl_ccc as c
ORDER BY c.sorting ASC

 

=====================================
The "syntax NULLS LAST" may not works for MySQL old version, and you may try the solution provided by Luksurious at stackoverflow to make Null values to last, that works for me:

Select *
from some_table
order by some_column DESC NULLS LAST

Apply that to an real case:

SELECT c.section_id, c.url_name, c.lineage, c.url, c.sorting
FROM tbl_ccc as c
ORDER BY c.sorting IS NULL, c.sorting ASC

Reference:
http://stackoverflow.com/questions/1498648/sql-how-to-make-null-values-come-last-when-sorting-ascending
http://stackoverflow.com/questions/5826210/rails-order-with-nulls-last/7055259#7055259

[SQL][Resolved] Syntax error or access violation: 1066 Not unique table/alias: xxxx

if you got this error message, try double check if you have ever joined any table grades to itself, there is an example:

select * from `a_branches`
left join `a_branches` on `a_branches`.`agency_ref` = `a_agencies`.`agency_id`
left join `ab_addresses` on `a_branches`.`branch_id` = `ab_addresses`.`branch_ref`
where `a_branches`.`branch_id` = 8

it joined the table "a_branches" to itself.
The solution is edit that tables name:

select * from `a_agencies`
left join `a_branches` on `a_branches`.`agency_ref` = `a_agencies`.`agency_id`
left join `ab_addresses` on `a_branches`.`branch_id` = `ab_addresses`.`branch_ref`
where `a_branches`.`branch_id` = 8

 
Reference:
http://stackoverflow.com/questions/2077355/mysql-php-not-unique-table-alias

[MySQL][SQL] Example of if and case sql statement in select statment

If statement:

SELECT id, IF( post_title = "Title 1", "TRUE", "FALSE" )
FROM  `wp_posts`

A closer look of the if part

IF( post_title = "Title 1", "TRUE", "FALSE" )

Text in blue is the condition part of the if statement, check if the value with column name “post_title” is equal to “Title 1”.
Text in green is the output if the “IF” statement returns true, string “TRUE” would be output in this example.
Text in red the is output if the “IF” statement returns false, string “FALSE” would be output in this example.

To make the results look better, set the column which used if statement as new_post_title:

SELECT id, post_title, IF( post_title = "Title 1", "TRUE", "FALSE" ) AS new_post_title
FROM `wp_posts`

 

And now let try an SQL with CONCAT to add some string after the returned result, you can  click here to make the sample table to try this example SQL:

SELECT id, post_title, IF( post_title =  "Title 1", CONCAT(  'TRUE: ', post_title ), post_title ) AS new_post_title

FROM  `wp_posts`

* To know more about CONCAT, please click here.

-------------------------

If you want to IF…THEN in an SQL SELECT, it’s better to use the CASE statement. CASE statement is the closest to IF in SQL and is supported on all versions of SQL Server.

SELECT id,

    CASE post_title

        WHEN 'Title 1' THEN id

        WHEN 'Title 2' THEN -id

        WHEN 'This is Title 3' THEN -id

        ELSE 'Others title'

    END AS result

FROM wp_posts

Reference:

http://stackoverflow.com/questions/63447/how-do-you-perform-an-if-then-in-an-sql-select

http://dev.mysql.com/doc/refman/5.0/en/if.html

Nice article to read:

http://www.mysqltutorial.org/mysql-if-function.aspx

Sample MySQL table for tutorial article.

This is the sample table for some of the article in the website, table was named "wp_posts" and there is the table structure:

the Create SQL:

CREATE TABLE IF NOT EXISTS `wp_posts` (
  `id` int(11) DEFAULT NULL,
  `post_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `post_title` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The insert SQL:

INSERT INTO `wp_posts` (`id`, `post_date`, `post_title`) VALUES
(1, '2014-03-31 04:41:16', 'Title 1'),
(2, '2014-03-31 04:41:16', 'Title 2'),
(3, '2014-03-31 04:41:16', 'This is Title 3'),
(4, '2014-03-31 04:41:16', 'This is Title 4'),
(5, '2014-03-31 04:41:16', 'Title 5 !'),
(6, '2014-03-31 04:41:16', 'Title 6~'),
(7, '2014-03-31 04:41:16', 'And this is Title 7~'),
(8, '2014-03-31 04:41:16', 'This is Title 8'),
(9, '2014-03-31 04:41:16', ' Title 9'),
(10, '2014-03-31 04:41:16', ' Title 10'),
(11, '2014-03-31 04:41:16', 'Title 11'),
(12, '2014-03-31 04:41:16', 'Title 12'),
(13, '2014-03-31 04:41:16', 'This is Title 13'),
(14, '2014-03-31 04:41:16', 'This is Title 14'),
(15, '2014-03-31 04:41:16', 'Title 15 !'),
(16, '2014-03-31 04:41:16', 'Title 16~'),
(17, '2014-03-31 04:41:16', 'And this is Title 17~'),
(18, '2014-03-31 04:41:16', 'This is Title 18'),
(19, '2014-03-31 04:41:16', ' Title 19'),
(20, '2014-03-31 04:41:16', ' Title 20'),
(21, '2014-03-31 04:41:16', 'Title 21'),
(22, '2014-03-31 04:41:16', 'Title 22'),
(23, '2014-03-31 04:41:16', 'This is Title 23'),
(24, '2014-03-31 04:41:16', 'This is Title 24'),
(25, '2014-03-31 04:41:16', 'Title 25 !'),
(26, '2014-03-31 04:41:16', 'Title 26~'),
(27, '2014-03-31 04:41:16', 'And this is Title 27~'),
(28, '2014-03-31 04:41:16', 'This is Title 28'),
(29, '2014-03-31 04:41:16', ' Title 29'),
(30, '2014-03-31 04:41:16', ' Title 30');

[SQL][Resolved] #1063 - Incorrect column specifier for column 'user_id'

Since auto_increment columns must be integer type (TINYINT, SMALLINT, INTEGER, or BIGINT), I use varchar as type of value with “auto_increment” with cause error

This is my case,

CREATE TABLE  .`p_users` (

 `user_id` VARCHAR( 15 ) AUTO_INCREMENT ,

 `username` VARCHAR( 20 ) NOT NULL ,

 `nickname` VARCHAR( 20 ) NOT NULL ,

 `password` VARCHAR( 20 ) NOT NULL ,

 `permission` VARCHAR( 10 ) NOT NULL ,

 `email` VARCHAR( 50 ) NOT NULL ,

 `reg_time` TIMESTAMP NOT NULL ,

 `reg_code` TEXT NOT NULL ,

 `reset_code` TEXT NOT NULL ,

PRIMARY KEY (  `user_id` )

) ENGINE = MYISAM

 Corrected :

CREATE TABLE  `p_users` (

 `user_id` INTEGER( 15 ) AUTO_INCREMENT,

 `username` VARCHAR( 20 ) NOT NULL ,

 `nickname` VARCHAR( 20 ) NOT NULL ,

 `password` VARCHAR( 20 ) NOT NULL ,

 `permission` VARCHAR( 10 ) NOT NULL ,

 `email` VARCHAR( 50 ) NOT NULL ,

 `reg_time` TIMESTAMP NOT NULL ,

 `reg_code` TEXT NOT NULL ,

 `reset_code` TEXT NOT NULL ,

PRIMARY KEY (  `user_id` )

) ENGINE = MYISAM