How to change collation of database, table, column | Alter charset and collation in all columns in all tables in MySQL | MySQL Collation - Setting Character Sets and Collations in MySQL

How to change collation of database, table, column | Alter charset and collation in all columns in all tables in MySQL | MySQL Collation - Setting Character Sets and Collations in MySQL.

Now the database is latin1_general_ci and I want to change collation to utf8_general_ci. Is there any setting in PhpMyAdmin to change collation of database, table, column? Rather than changing one by one?

(this will convert the columns just as well), or export the database with latin1 and import it back with utf8.

Changing it database wise:

ALTER DATABASE <database_name> CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Changing it per table:

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Good practice is to change it at table level as it'll change it for columns as well. Changing for specific column is for any specific case.

Changing collation for a specific column:

ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' charset=utf8 COLLATE utf8_unicode_ci;')

from information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema' limit 100;

How create json format with group-concat mysql | How to put JSON into a column data if sub-query returns more than 1 row in MySQL | Optimized way of getting subqueries at once using JSON

How create json format with group-concat mysql |  How to put JSON into a column data if sub-query returns more than 1 row in MySQL | Optimized way of getting subqueries at once using JSON.

A bit convoluted, but you could create JSON objects for each row, concatenate them using GROUP_CONCAT and cast the result (wrapped in [] to make it an array) to JSON;

Below is the full example:

<?php
define("DB_DEFAULT_HOST", "localhost");
define("DB_DEFAULT_DATABASE", "test71");
define("DB_DEFAULT_USER", "root");
define("DB_DEFAULT_PASSWORD", "");

$dbh = null;
try {
    $dbh = new PDO(
        "mysql:host=" . DB_DEFAULT_HOST . ";dbname=" . DB_DEFAULT_DATABASE,
        DB_DEFAULT_USER,
        DB_DEFAULT_PASSWORD
    );
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->beginTransaction();

    $query =
        "SELECT
            s.name AS Name, s.roll AS Roll,
            CONCAT(
              '[', COALESCE(
                GROUP_CONCAT(
                  CONCAT('{', 
                    '\"ExamName\":', '\"', HEX(CONVERT(e.subject USING utf32)), '\"',
                    ',\"Score\":', '\"', e.score, '\"',
                  '}')
                  SEPARATOR ','
                ), ''
              ), ']'
            ) AS ExamsRaw
        
        FROM student AS s
          LEFT JOIN exams AS e ON e.student_id=s.id
        
        GROUP BY s.id";

    $students = $dbh->query($query)->fetchAll(PDO::FETCH_OBJ);
    foreach ($students as $entity) {
        $entity->Exams = array();
        foreach (json_decode($entity->ExamsRaw) as $group) {
            $group->ExamName = hexToStr($group->ExamName);
            array_push($entity->Exams, $group);
        }
        unset($entity->ExamsRaw);
    }
    echo "<pre>";
    print_r($students);
    echo "</pre>";
}
catch (\Exception $ex) {
    echo "MySQL_Error=" . $ex->getMessage();
    $dbh->rollBack();
}

function hexToStr($hex)
{
    $string = '';
    for ($index = 0; $index < strlen($hex) - 1; $index += 2) {
        $string .= chr(hexdec($hex[$index] . $hex[$index + 1]));
    }
    return $string;
}

And below is the output of above example code block:

Array
(
    [0] => stdClass Object
        (
            [Name] => Prtiom First Year
            [Roll] => P1
            [Exams] => Array
                (
                    [0] => stdClass Object
                        (
                            [ExamName] => ©|æ|þ|½|±|®|¶
                            [Score] => 1.12
                        )

                    [1] => stdClass Object
                        (
                            [ExamName] => Subject 2   
                            [Score] => 2.54
                        )

                    [2] => stdClass Object
                        (
                            [ExamName] =>  ¡¢£¤¥¦§¨©
                            [Score] => 1.98
                        )

                )

        )

    [1] => stdClass Object
        (
            [Name] => Pritom Second Year
            [Roll] => P2
            [Exams] => Array
                (
                    [0] => stdClass Object
                        (
                            [ExamName] => Subject 2
                            [Score] => 4.00
                        )

                    [1] => stdClass Object
                        (
                            [ExamName] => Subject 3
                            [Score] => 3.00
                        )

                )

        )

)

Aware that mysql group function max length is by default 1024 characters. If your group function more than 1024 then you have to change limit of mysql group function using following query:

This is for current session:
SET SESSION group_concat_max_len = 1000000;

But if you want it global scope, then do following:
SET GLOBAL group_concat_max_len = 1000000;

MySQL Update Table Using Join Of Other Tables | MySQL Join Table On Update

UPDATE table1 AS t1 LEFT JOIN table2 AS t2 ON (t1.t2_id = t2.id)
SET t1.some_field = t1.some_field + 1 
WHERE t1.id in (1,2,3) AND t2.some_field = "SOME MATCH VALUE"

MySQL Trigger | MySQL Before/After Insert Trigger | MySQL Before/After Update Trigger | MySQL Before/After Delete Trigger

MySQL Trigger | MySQL Before/After Insert Trigger | MySQL Before/After Update Trigger | MySQL Before/After Delete Trigger:

DROP TRIGGER IF EXISTS `example_table_before_insert`;
DELIMITER $$
CREATE TRIGGER `example_table_before_insert`
BEFORE INSERT ON `example_table` FOR EACH ROW BEGIN
  DECLARE ready INT DEFAULT 0;
  DECLARE rnd_str TEXT;
  IF NEW.CODE IS NULL OR CHAR_LENGTH(NEW.CODE) = 0 THEN
    WHILE NOT ready DO
      SET rnd_str := UPPER(SUBSTR(MD5(CONCAT(rand(), now())), 1, 5));
      IF NOT exists(SELECT * FROM example_table WHERE CODE = rnd_str) THEN
        SET NEW.CODE = rnd_str;
        SET ready := 1;
      END IF;
    END WHILE;
  END IF;
END
$$
DELIMITER ;

DROP TRIGGER IF EXISTS `example_table_before_update`;
DELIMITER $$
CREATE TRIGGER `example_table_before_update`
BEFORE UPDATE ON `example_table` FOR EACH ROW BEGIN
  DECLARE ready INT DEFAULT 0;
  DECLARE rnd_str TEXT;
  IF NEW.CODE IS NULL OR CHAR_LENGTH(NEW.CODE) = 0 THEN
    WHILE NOT ready DO
      SET rnd_str := UPPER(SUBSTR(MD5(CONCAT(rand(), now())), 1, 5));
      IF NOT exists(SELECT * FROM example_table WHERE CODE = rnd_str) THEN
        SET NEW.CODE = rnd_str;
        SET ready := 1;
      END IF;
    END WHILE;
  END IF;
  SET NEW.updated_at = now();
END
$$
DELIMITER ;

DROP TRIGGER IF EXISTS `example_table_after_delete`;
DELIMITER $$
CREATE TRIGGER `example_table_after_delete`
AFTER DELETE ON `example_table` FOR EACH ROW BEGIN
  DELETE FROM example_table_associations WHERE example_id=OLD.id;
END
$$
DELIMITER ;

MySQL UPDATE with SUBQUERY of same table

I want to update a table named "test" with some sub-query where sub-query is the same "test" table. I wrote the below SQL first to update my "test" table:

UPDATE test SET roll=CONCAT(roll,"-",id) WHERE id IN (SELECT id FROM test WHERE id%2=0)

And got below error:

#1093 - Table 'test' is specified twice, both as a target for 'UPDATE' and as a separate source for data.

And in this case we have a nice solution provided by MySQL itself is processing temporary table, query is below:

UPDATE test SET roll=CONCAT(roll,"-",id) WHERE id IN (SELECT t.id FROM (SELECT * FROM test) AS t WHERE t.id%2=0)

And it works. Marked yellow color section actually do the temporary table works.

MySQL Insert with While Loop | Use LOOP variable for INSERT in MySQL | INSERT using a procedure variable

Use while loop in MySQL is now very easy. It can be achieved by MySQL Procedure execution. We can all type of operation here and all type of condition checking like if, do while, else if and many more. Below is a simple code snippet to describe basic implementation of MySQL Procedure.

DELIMITER //
DROP PROCEDURE IF EXISTS doWhile;
CREATE PROCEDURE doWhile()
  BEGIN
    DECLARE i INT DEFAULT (SELECT COUNT(*) FROM HOME);
    DECLARE j INT DEFAULT i + 2;
    WHILE (i < j) DO
      INSERT INTO HOME (name, roll) VALUES (CONCAT("Name-", i), CONCAT("ROLL-", i));
      SET i = i + 1;
    END WHILE;
  END;
//
CALL doWhile();

DELIMITER //
DROP PROCEDURE IF EXISTS doWhile;
CREATE PROCEDURE doWhile()
  BEGIN
    DECLARE namex VARCHAR(300);
    DECLARE rollx VARCHAR(300);
    DECLARE cursor1 CURSOR FOR (SELECT name,roll FROM HOME WHERE id % 2 = 0);

    OPEN cursor1;
    read_loop: LOOP
      FETCH FROM cursor1 INTO namex, rollx;
      INSERT INTO HOME (name, roll) VALUE (namex, rollx);
    END LOOP;
    CLOSE cursor1;
  END;
//
CALL doWhile();

Lock wait timeout exceeded; try restarting transaction

Why is happening like this, some other thread is holding a record lock on some record (you're updating every record in the table!) for too long, and your thread is being timed out. That's why you got this error.

Your first step to lookup for which queries this problem happened.

Below are some queries to observe transaction status and other data:

show open tables where in_use > 0;
show processlist;
SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`;
SELECT * FROM `information_schema`.`innodb_locks`;
show variables like 'innodb_lock_wait_timeout';
show variables like '%wait_timeout%';

Above queries help you to find out why the problem occurs. 

Now you can check your database transaction isolation level in the mysql using following command:

SELECT @@GLOBAL.tx_isolation, @@tx_isolation, @@session.tx_isolation;

I think all values are set to "REPEATABLE-READ" if you don't configured it yet.

Make sure the database tables are using InnoDB storage engine and READ-COMMITTED transaction isolation level.

Now you can set it to "READ-COMMITTED" for better performance using below command:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

If the the above configuration is correct then please try to increase the database server innodb_lock_wait_timeout variable to 500 (it's a random value, default value is 50).

Restart MySQL database service for the configuration to take place.

Modify MySQL startup options in the configuration file my.cnf (often named my.ini on Windows), so the transaction level is set to transaction-isolation = READ-COMMITTED.

And

innodb_lock_wait_timeout=500

If this not helps you much, you need and database administration as well as expert to troubleshoot the problem.

PHP + MySQL transactions examples

MySLQL transactions can be used when you want to make sure all the statements you specify are executed. Only the tables of InnoDB storage engine support transactions.

In a transaction, if at least one statement fails, all the changes will be rolled back and database will be in its initial state (There are some statements that can not be rolled back: Will be discussed at the end).

In web applications, it’s common that you want to either run set of statements or do no change since success of only few statements can cause data corruption.

A transaction is a set of inter-dependent SQL statements that needs to execute in all-or-nothing mode. A transaction is successful if all SQL statements executed successfully. A failure of any statement will trigger the system to rollback to the  original state to avoid data inconsistency.

Suppose we have a process where need to execute 3 queries. If an error occurs in the second step, the third step should not continue and first step must reversed. In addition, if an error occurs in the third step, then first and second step must be reversed.

When you use PDO to create a connection to the MySQL database that supports the transaction, the auto-commit mode is set. It means that every query you issue is wrapped inside an implicit transaction.

Notice that not all storage engines in MySQL support transactions e.g., MyISAM does not support the transaction, however, InnoDB does. So you can't use MyISAM engine for MySQL if you want transaction support.

To handle MySQL transaction in PHP, you use the following steps:

• Start the transaction by calling the beginTransaction() method of the PDO object.
• Place the SQL statements and the  commit() method call in a try block.
• Rollback the transaction in the catch block by calling the rollBack() method of the PDO object.

Below is a simple PHP script that shows how we can achieve the functionality.

<?php
define("DB_DEFAULT_HOST", "localhost");
define("DB_DEFAULT_DATABASE", "test_database");
define("DB_DEFAULT_USER", "root");
define("DB_DEFAULT_PASSWORD", "");
$dbh = null;
try {
    $dbh = new PDO(
        "mysql:host=".DB_DEFAULT_HOST.";dbname=".DB_DEFAULT_DATABASE,
        DB_DEFAULT_USER,
        DB_DEFAULT_PASSWORD
    );
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->beginTransaction();

    $query = $dbh->prepare("SELECT COUNT(*) as count FROM student WHERE id > :id");
    $query->bindValue("id", 0);
    $query->execute();
    $count = $query->fetch()[0];
    echo "OLD COUNT=$count<br/>";

    $stmt = $dbh->prepare("INSERT INTO student (name,roll) VALUES(?,?)");
    $stmt->execute(['Name', 'Roll']);
    /* Will commit data to database, if you don't call this, data will not save */
    $dbh->commit();

    $count = $dbh->query("SELECT COUNT(*) as count FROM student WHERE id > 0")->fetch()[0];
    echo "NEW COUNT=$count<br/>";

    $students = $dbh->query("SELECT * FROM student WHERE id > 0")->fetchAll(PDO::FETCH_OBJ);
    echo "<pre>"; print_r($students); echo "</pre>";

    /* SEARCH CAPABILITY IN ARRAY DATA BIND */
    $id_list = [1, 2, 3];
    $in = rtrim(str_repeat('?,', count($id_list)), ',');
    $query = $dbh->prepare("SELECT * FROM student WHERE id in ($in)");
    $query->execute($id_list);
    $students = $query->fetchAll(PDO::FETCH_OBJ);
    echo "<pre>"; print_r($students); echo "</pre>"; die();
}
catch (\Exception $ex) {
    echo "MySQL_Error=" . $ex->getMessage();
    $dbh->rollBack();
}

And output is like below:

OLD COUNT=2
NEW COUNT=3
Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [name] => Prtiom First Year
            [roll] => P1
        )

    [1] => stdClass Object
        (
            [id] => 2
            [name] => Pritom Second Year
            [roll] => P2
        )

    [2] => stdClass Object
        (
            [id] => 21
            [name] => Some_Name
            [roll] => 3040
        )

)

Note that, with this idea, if a query fails, an Exception must be thrown:

• PDO can do that, depending on how you configure it
• See PDO::setAttribute
• and PDO::ATTR_ERRMODE and PDO::ERRMODE_EXCEPTION
• You might have to test the result of the function used to execute a query, and throw an exception yourself.

Unfortunately, there is no magic involved. You cannot just put an instruction somewhere and have transactions done automatically: you still have to specific which group of queries must be executed in a transaction.

For example, quite often you'll have a couple of queries before the transaction (before the begin) and another couple of queries after the transaction (after either commit or rollback) and you'll want those queries executed no matter what happened (or not) in the transaction.

Escape raw SQL queries in Laravel

Laravel is a strong php based framework today. Laravel has a strong query builder which handle escaping of SQL parameters. But sometimes we need to handle this situation ourselves. So we need to have the capability to handle this situation. We can do this easily using Laravel built-in function as below one line code:

DB::connection()->getPdo()->quote("TEXT 'TO" HANDLE");

Will output like:

'TEXT \'TO\" HANDLE' and MySQL will never mind with this.

Illegal mix of collations for operation 'UNION'

I don't know for how many reason this error occurred but I found it when going to create a view using two tables using UNION. 

There are different ways to solve this problem. You can solve it by compress the value and then decompress as UNCOMPRESS(COMPRESS(x.name)) AS name.

You have another way to fix this problem. You can use first hex the value and then unhex as UNHEX(HEX(x.name)) AS name.

And finally, to fix this, you need to replace some column references in the SELECT list (in one or more of the queries) with an expression, something like CONVERT(name USING UTF8) AS name.

Some more convert functions are listed below:
CONVERT('2014-02-28', DATE)
CONVERT('2014-02-28 08:14:57', DATETIME)
CONVERT('08:14:57', TIME)
CONVERT(125, CHAR)
CONVERT(4-6, SIGNED)
CONVERT(4-6, UNSIGNED)
CONVERT('4', BINARY)
CONVERT('Some String' USING UTF8)
CONVERT('Some String' USING ASCII)
CONVERT('Some String' USING LATIN1)
CONVERT(x.price, DECIMAL)
CONVERT(x.price, DECIMAL(10,2))

MySQL: Create view from two tables with different column names

Its very important to create MySQL view from two or more tables with different column names. It is not mandatory but we sometime need it hardly. This can be done using UNION each table. Union's must have same columns in the same order across all sections. You should explicitly select/declare the null columns in each part of the union. You can use normal JOIN on each part.

Views in MySQL are two types: MERGE or TEMPTABLE. MERGE is simply a query expansion with appropriate aliases and behave as well as the base table. TEMPTABLE is just what it sounds like, the view puts the results into a temporary table before running the WHERE clause, and there are no indexes on it. It cause some performance impact. 

The default option is UNDEFINED, which determined by MySQL to select the appropriate algorithm. MySQL first try to use MERGE because it is more efficient than TEMPTABLE. 

If the MERGE algorithm cannot be used (determined by MySQL itself), a temporary table must be used instead (This algorithm is TEMPTABLE). MERGE cannot be used if the view contains any of the following constructs (Aggregate function or UNION): 

SUM
AVG
DISTINCT
GROUP BY
HAVING
LIMIT
UNION or UNION ALL

And the final example is as follows:

CREATE OR REPLACE ALGORITHM=MERGE VIEW MY_VIEW AS
SELECT test1.id AS _id, test1.name AS _name, test1.roll AS _roll from test1
UNION
SELECT test2.id AS _id, test2.type AS _name, NULL AS _roll from test2

VBScript MySql connection

You have to install mysql odbc connector first.

Dim Connection, ConnectString

ConnectString = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;PORT=3306;Database=database;User=root;Password="

Set Connection = CreateObject("ADODB.Connection")

Connection.Open ConnectString

Sql = "SELECT id,created_at FROM table ORDER BY id DESC LIMIT 0, 10"
Set Result = Connection.Execute(Sql)

Do Until Result.EOF
    Wscript.Echo "Id=" + CStr(Result(0)) + ",Create_Date=" & FormatDateTime(Result(1), 1)
    Result.MoveNext
Loop

Connection.Close

VBScript: How to Automatically Restart MySQL Server

Set WshShell = CreateObject("WScript.Shell" ) 
'Just Start Apache Server & MySQL Server
WshShell.Run """C:\xampp\apache\bin\httpd.exe""", 0, false
WshShell.Run """C:\xampp\mysql\bin\mysqld.exe"" --defaults-file=C:\xampp\mysql\bin\my.ini --standalone --console", 0, false
'Stop & Start MySQL Server
WshShell.Run """C:\xampp\mysql_stop.bat""", 0, false
WshShell.Run """C:\xampp\mysql_start.bat"" --defaults-file=C:\xampp\mysql\bin\my.ini --standalone --console", 0, false
Set WshShell = Nothing

MySQL increment row value dynamically

SELECT @i := 0;
UPDATE table SET field = (select @i := @i + 1);

Windows Batch Script to create new MySQL database and import

Create a file named restore.bat with following contents & double to click to create new database & import data to selected database

@ECHO OFF
cd C:\xampp\mysql\bin
mysql.exe -uroot -hlocalhost -e "drop database if exists some_db_name";
mysql.exe -uroot -hlocalhost -e "create database some_db_name default character set utf8 default collate utf8_general_ci;";
echo DB Created
mysql.exe -uroot -hlocalhost some_db_name < "C:\tmp\back_up.sql"
echo DB Restored
pause
exit;

Windows Batch Script to backup local MySQL database

Create a file name "backup.bat" with following contents and double click to backup selected database to local file.

@ECHO OFF
cd C:\xampp\mysql\bin
mysqldump.exe -uroot -hlocalhost some_db_name > "C:\tmp\back_up.sql"
echo DB Backuped
mysql.exe -uroot -hlocalhost -e "drop database if exists some_db_name";
echo DB Removed
pause
exit;

Cannot change column used in a foreign key constraint

Disabling foreign key check using following SQL:
SET FOREIGN_KEY_CHECKS=0;

Enabling foreign key check:
SET FOREIGN_KEY_CHECKS=1;

If you want it globally then:
SET GLOBAL FOREIGN_KEY_CHECKS=0;
SET GLOBAL FOREIGN_KEY_CHECKS=1;

Alter all table of a mysql database using java

package com.pritom.kumar;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by pritom on 18/10/2016.
 */
public class StoredProcedureUtils {
    static Connection connection = null;

    public static void main(String[] args) throws Exception {
        alterTableSchema("some_db_name");
    }

    static void alterTableSchema(String databaseName) throws Exception {
        /* Query for retrieve table names from database */
        String query = "SELECT table_name FROM information_schema.tables " +
                "WHERE table_schema='" + databaseName + "' ORDER BY table_name;";

        /* Create statement */
        Statement statement = getConnection().createStatement();

        /* Retrieving all table names */
        ResultSet resultSet = statement.executeQuery(query);
        List<String> tableList = new ArrayList<String>();
        while (resultSet.next()) {
            tableList.add(resultSet.getString(1));
        }

        /* Set database not to check foreign key constraints */
        statement.execute("SET FOREIGN_KEY_CHECKS=0;");

        /* Alter table structure */
        for (Integer index = 0; index < tableList.size(); index++) {
            String tableName = tableList.get(index);
            try {
                statement.execute("ALTER TABLE " + databaseName + "." + tableName + " MODIFY id BIGINT(20) AUTO_INCREMENT;");
                pl(formatAsLength("Done Alter Table=" + tableName, 70, "") + (index + 1) + " Of " + tableList.size());
            }
            catch (Exception ex) {
                pl(formatAsLength("Error Alter Table=" + tableName, 70, "") + "Error=" + ex.getMessage());
            }
        }

        /* Set database mode to check foreign key constraints again */
        statement.execute("SET FOREIGN_KEY_CHECKS=1;");

        /* Close statement */
        statement.close();
    }

    static Connection getConnection() throws Exception {
        if (connection != null) {
            return connection;
        }
        connection = DriverManager.getConnection("jdbc:mysql://localhost/some_db_name?user=root&password=");
        return connection;
    }

    static void pl(Object o) {
        System.out.println("" + o);
    }

    static String formatAsLength(String number, Integer minLength, String replaceEmptyWith) {
        number = String.format("%-" + minLength + "s", number);
        return replaceEmptyWith.length() > 0 ? number.replace(" ", replaceEmptyWith) : number;
    }
}

java - JDBC connection error : unrecognized timezone

java.sql.SQLException: The server time zone value is unrecognized or represents more than one time zone

Have to add "serverTimezone" parameter to connect url.

jdbc:mysql://localhost/db?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

Apparently, to get version 5.1.33 of MySQL JDBC driver to work with UTC time zone, one has to specify the serverTimezone explicitly in the connection string

Get all columns from all MySQL tables

SELECT * FROM information_schema.columns
WHERE table_schema = 'database_name'
ORDER BY table_name, ordinal_position