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

How to fix – DBeaver Native client is not specified for connection ?

Native client is not specified for connection

Download Mysql or Postgres or (appropriate database ) Server

Do the step of DBEAVER client configuration

Find bib files for mysql – open terminal and execute followin command

which mysql
--> /usr/local/bin/mysql

if using a mac to allocate mysql server bin files location

command + shift + G

Paste the path in there such as

/usr/local/mysql-5.7.28-macos10.14-x86_64/bin/

Voila you can enjoy most amazing feature of DBeaver !

Categories
MySql

Mysql Open remote access

Connect

mysql -u root -p
USE mysql;
SELECT user,host FROM user;

grant privileges

from specific IP

GRANT ALL PRIVILEGES ON *.* TO root@my_ip IDENTIFIED BY ‘root_password‘ WITH GRANT OPTION;

From everywhere

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY ‘root_password‘ WITH GRANT OPTION;

Flush privileges

FLUSH PRIVILEGES;
Categories
MySql SQL SqLite

How to SQLite ? beginner

Download page

Download Page SQLITE

Basic Commands

GUI SQLITE

–> execute sqlite excutable from downladpage

sqlite

Import CSV data to SQlite

.mode csv
.import csvfilename databaseName

Quit SQlite

.quit

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