Categories
MySql

What is the easiest way to run Mysql on a Mac ?

The easiest way to run MySQL on a Mac is to install MAMP. The advantage is that it does not conflict with valet which is another powerful tool for local development specially with Laravel. If you do not want to face the same issue as the lost of your mysql password after installing with homebrew, MAMP is the my fastest solution

See StackOverflow issue : MacOSX homebrew mysql root password

Categories
MySql SqLite

SQLITE – update a column from another table

SQLITE – update a column from another table while concatenating a string

UPDATE table1
SET table1.businessOwner =  table1.prenom1UniteLegale || ' ' || table1.nomUniteLegale, 
BusinessNameDavid = table1.denominationUniteLegale ,
BusinessAddressDavid = table1.numeroVoieEtablissement || ' ' || table1.typeVoieEtablissement || ' ' || table1.libelleVoieEtablissement 
from table2 
WHERE table1.siren = table1.siren 
Categories
Laravel MySql

LARAVEL – auto increment a column with mysql – upsert

see example below of an upsert

Polls::updateOrInsert(['business_id'=> $business_id],
    [
        "$vote_value" => DB::raw('$vote_value + 1'),
        'polls_name' => $theBusiness->BUSINESSNAME,
        'source' => env('APP_CODE'),
        'updated_at' => $lastupdated
    ]);
Categories
MySql SQL

How to resolve ERROR 1148 (42000): The used command is not allowed with this MySQL version ?

Solution

mysql> SHOW VARIABLES LIKE 'local_infile';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| local_infile  | OFF   |

+---------------+-------+

1 row in set (0.21 sec)
mysql> SET GLOBAL local_infile = 1;

Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'local_infile';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| local_infile  | ON    |

+---------------+-------+

1 row in set (0.00 sec)
mysql> load data local infile '/Users/davidr/Desktop/dob.csv' INTO TABLE dob FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
Query OK, 109093 rows affected (1.88 sec)
Records: 109093 Deleted: 0 Skipped: 0 Warnings: 0
Categories
MySql SQL Unix

Import CSV data to mysql

Fastest way to import data over time found to be csv format data

mysql> load data local infile 'Desktop/data.csv'
-> INTO TABLE cars
-> FIELDS TERMINATED BY ','
-> ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> IGNORE 1 ROWS;
Query OK, 232880 rows affected, 65535 warnings (27.87 sec)
Records: 232880 Deleted: 0 Skipped: 0 Warnings: 232880

Categories
Database SQL

SQL HELPER

RDS AUTHENTICATION

mysql -h xxxxxxxxxta.us-east-1.rds.amazonaws.com  -u admin -p 

SELECT User,Host FROM mysql.user;
SHOW GRANTS FOR 'drupal'@'%';

SQL Client Recommendation

TablePlus(Mac)

DBeaver

Mysql Workbench

SequelPro

Import database

#1 mysql h xxxxx.com -u username -p dbname < dbexport.sql

OR 

#1 mysql h xxxxx.com -u username -p 
#2 use database-name;
#3 source file.sql

Export all Databases

    sudo mysqldump --all-databases -udxxxx -p > oct-16-2019-database.sql

Export Specific Database

mysqldump --databases xxxxxx-name -udxxxx -p > oct-16-2019-database-david-raleche.sql

NOT A ROOT USER

mysqldump –all-databases> database.sql

mysqldump -all-databases -uuser -ppassword> database.sql

mysqldump –all-databases –skip-lock-tables -u user-ppassword  > Sept2018database.sql

Long Queries

Explain 

SQL TABLE SIZE

select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1;

CRON NACKUP DATABSECron Backup Database

mysqldump -uroot -p MyDatabase >/home/users/backup_MyDB/$(date +%F)_full_myDB.sql