XAMPP - set password for root user ( MySQL / MariaDB ) on Windows

Trabla: XAMPP - set password for root user ( MySQL / MariaDB ) on Windows

Solving:

NOTE: SQL query for changing root user password, used in tutorial:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('Samurai123$%'); SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('Samurai123$%'); SET PASSWORD FOR 'root'@'::1' = PASSWORD('Samurai123$%'); FLUSH PRIVILEGES;

Where - Samurai123$ - new password, replace with yours

XAMPP - How to change MySQL / MariaDB default port 3306 on Windows

Trabla: XAMPP - How to change MySQL / MariaDB default port 3306 on Windows

Solving:

Watch on YouTube

1. Stop DB in XAMPP control panel

2. Open my.ini

3. Find string - 3306 - replace with new port e.g. 3312

4. Save & close my.ini

5. Start database in XAMPP control panel

Additional steps to fix phpMyAdmin

1. Open phpMyAdmin config file
default location
C:\xampp\phpMyAdmin\config.inc.php

2. Change host
from
127.0.0.1
to
127.0.0.1:3312

3. Save & close config file

4. Reload phpMyAdmin page in browser

Linux (Ubuntu): installing php-mysql : WARNING: The following packages cannot be authenticated!

Trabla: Linux (Ubuntu): installing php-mysql : WARNING: The following packages cannot be authenticated!

I catch this problem trying to install PrestaShop 1.6 on old Ubuntu 13.10


sudo apt-get install php5-mysql
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
  linux-headers-3.11.0-15 linux-headers-3.11.0-15-generic linux-image-3.11.0-15-generic
Use 'apt-get autoremove' to remove them.
The following extra packages will be installed:
  libapache2-mod-php5 php5-cli php5-common php5-curl php5-gd php5-intl php5-pgsql php5-readline php5-xmlrpc
Suggested packages:
  php-pear php5-user-cache
The following NEW packages will be installed:
  php5-mysql
The following packages will be upgraded:
  libapache2-mod-php5 php5-cli php5-common php5-curl php5-gd php5-intl php5-pgsql php5-readline php5-xmlrpc
9 upgraded, 1 newly installed, 0 to remove and 16 not upgraded.
Need to get 141 kB/6,613 kB of archives.
After this operation, 298 kB of additional disk space will be used.
Do you want to continue [Y/n]? y
WARNING: The following packages cannot be authenticated!
  php5-intl php5-readline php5-cli libapache2-mod-php5 php5-xmlrpc php5-gd php5-pgsql php5-curl php5-common php5-mysql
Install these packages without verification [y/N]? y
Err http://us-east-1.ec2.archive.ubuntu.com/ubuntu/ saucy-updates/main php5-xmlrpc amd64 5.5.3+dfsg-1ubuntu2.6
  404  Not Found Err http://security.ubuntu.com/ubuntu/ saucy-security/main php5-xmlrpc amd64 5.5.3+dfsg-1ubuntu2.6
  404  Not Found Err http://security.ubuntu.com/ubuntu/ saucy-security/main php5-gd amd64 5.5.3+dfsg-1ubuntu2.6
  404  Not Found Err http://security.ubuntu.com/ubuntu/ saucy-security/main php5-pgsql amd64 5.5.3+dfsg-1ubuntu2.6
  404  Not Found Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/p/php5/php5-xmlrpc_5.5.3+dfsg-1ubuntu2.6_amd64.deb  404  Not Found Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/p/php5/php5-gd_5.5.3+dfsg-1ubuntu2.6_amd64.deb  404  Not Found
Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/p/php5/php5-pgsql_5.5.3+dfsg-1ubuntu2.6_amd64.deb  404  Not Found 
E: Unable to fetch some archives, maybe run apt-get update or try with --fix-missing?

SOLVING:

To solve this problem we need edit file:
/etc/apt/sources.list
and change archive.ubuntu.com and security.ubuntu.com with old-releases.ubuntu.com

To execute this run following commands in terminal:

cd /etc/apt 
sudo cp sources.list sources.list.bak 
sudo sed -i "s/archive./old-releases./g" sources.list 
sudo sed -i "s/\/security./\/old-releases./g" sources.list 
sudo apt-get update

Discussion on superuser.com (StackOverFlow)
http://superuser.com/questions/327472/packages-cant-be-authenticated-during-apt-get-install-on-ubuntu-9-04

mysql: Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

Trabla: mysql: Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

Solving:

Example
table "my_user" : id PRIMARY KEY, "name" varchar, "email" varchar, "token" varchar

Query:
UPDATE my_user SET token = '-1' WHERE token IS NULL or token = '';
Will throw:
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

Fixing - add in where condition with primary key:
UPDATE my_user SET token = '-1' WHERE (token IS NULL or token = '') AND id > 0;

mysql: add not null constraint to existing column

Trabla: mysql: add not null constraint to existing column

Solving:

Example:
We have column "token" varchar(32) in table "my_table" with values and nulls.

1. Update table "my_table" - set some value ( NOT NULL ) to column "token"

UPDATE TABLE my_table SET token = '-1' WHERE (token IS NULL OR token = '') 
AND id >0;

2. Add NOT NULL constraint

ALTER TABLE my_table 
MODIFY token varchar(32) NOT NULL 
DEFAULT '-1' COMMENT 'Store token here';

Official Docs: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

PostgreSQL equivalent for MySQL GROUP_CONCAT example

Trabla: got MySQL sql query in php script, need PostgreSQL version

SELECT group_concat(
       
        CASE
            WHEN id = :phonemeid
                THEN concat('<u>',grapheme,'</u>')
            ELSE grapheme
        END
                    
        SEPARATOR '' )  AS phoneme
                           
FROM tbl_phoneme
WHERE wordid = :wordid
ORDER BY listindex ASC

Solving:

SELECT array_to_string(
            array(

                    SELECT
                            CASE
                                WHEN id = :phonemeid
                                    THEN '<u>' || grapheme || '</u>'
                                ELSE grapheme
                            END

                    FROM my_phoneme
                    WHERE wordid = :wordid
                    ORDER BY listindex ASC

            ), ''
        ) AS phoneme

PHP: get return values from MySQL procedure

This is simple example how to get returned values from MySQL procedure in PHP using PDO object:

Intro:
`my_sql_procedure` - simple procedure with one input string param  and 3 output params

Code:

$CFG = new stdClass();

//Connection to MySQL DB - replace with yours

$CFG->dbhost = 'localhost';

$CFG->dbname = 'mydb';

$CFG->dbuser = 'root';

$CFG->dbpass = '123' ;

 $pdo = null;

 $stmt = null;

        try {

            

            $hostname   = $CFG->dbhost;

            $dbname     = $CFG->dbname;

            $username   = $CFG->dbuser;

            $pw         = $CFG->dbpass;

            $pdo = new PDO ("mysql:host=$hostname;dbname=$dbname","$username","$pw");

        } catch (PDOException $e) {

            echo "Failed to get DB handle: " . $e->getMessage() . "\n";

            exit;

        }

        $pdo->query("SET NAMES 'utf8'"); 

      

        // my_sql_procedure - name of MySQL procedure

        $sql = "CALL `my_sql_procedure`( 'test' ,@return_param1, @return_param2, @return_param3);";

        $stmt = $pdo->prepare($sql);

        $stmt->execute();

        $outputArray = $pdo->query("select @return_param1, @return_param2, @return_param3;")->fetch(PDO::FETCH_ASSOC);

        var_dump($outputArray);
   
        unset($stmt);
        unset($pdo);