MySql

configuration location

/etc/mysql/my.cnf might need sudo to change

Login

mysql -u username -p password -h hostname_or_IP -P port

eg: mysql -u root -p 1234 -h example.com -P 3306 DBNAME mysql -uroot -proot -h192.168.2.222 -P23306 DB

Troubleshoot mysql

Mysql not accessible outside localhost

# change bind address in my.cnf
# bind-address           = 127.0.0.1
bind-address           = 0.0.0.0

drop unique index from table

ALTER TABLE marital_statuses DROP INDEX marital_statuses_uniform_code_unique;

Allow access to users

select user,host from  user;

DROP USER IF EXISTS 'user'@'localhost';
-- Grant command dont create users by deafult now
create user '_aims_'@'%' identified by 'new_password_string';

-- % can be ip or hostname
GRANT ALL PRIVILEGES ON *.* TO '_username_'@'%';
FLUSH PRIVILEGES;

-- NO_AUTO_CREATE_USER 
-- this is preasent in my.cnf
-- Prevent the GRANT statement from automatically creating new users 
-- Error: 
-- Error Code: 1133. Can't find any matching row in the user table

-- GRANT ALL PRIVILEGES ON dbname.* TO 'user'@'%' WITH GRANT OPTION; 
-- The GRANT OPTION privilege enables you to give to other users or remove from other users those privileges that you yourself possess.

Foreign Keys

> Foreign key of child must reference a UNIQUE field in parent table.  
> ON UPDATE action to CASCADE and modify your ids  
>  
> temporarily suspend foreign key checking:

-- useful when importing bad sql database
SET foreign_key_checks = 0;
UPDATE  parent 
UPDATE child
SET foreign_key_checks = 1

-- delete rows and import table
SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE table $table_name; 
SET FOREIGN_KEY_CHECKS = 1;
-- 
-- CHECK autoincreament value after importing rows

-- phpMyAdmin
Ucheck Enable foreign key checks option under SQL textarea

https://stackoverflow.com/questions/5452760/how-to-truncate-a-foreign-key-constrained-table

Update queries to fix foreign key code

/*
UPDATE ct set ct_col=col_val where ct_col NOT IN(
        SELECT `pt_col` FROM pt
    );
-- generate these without tab on first line
or mysql shell will try to autocomplete
*/

Table structure

SHOW CREATE TABLE `table_name`;
DESCRIBE `table_name`;

Table structure describe using select query in mysql

SELECT COLUMN_NAME AS `Field`, COLUMN_TYPE AS `Type`, IS_NULLABLE AS `NULL`, 
       COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS `Extra`
FROM information_schema.COLUMNS  
WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'trainees';

Update View in mysql

-- first drop view and create in phpmyadmin
-- permission issue can occur for editing views in phpmyadmin
drop view view_trainee_gender_less_disagregate_report;
create view view_trainee_gender_less_disagregate_report as
SELECT 

        (SELECT uniform_code FROM states 
            WHERE id= (select state_id from districts where district_code=t.per_district_code limit 1)
        LIMIT 1) as per_state_code,
        -- t.per_state_code,
        -- t.year_id,
        t.training_year,

        case when t.sex_code=1 then 1 end AS male,
        case when t.sex_code=2 then 1 end AS female,
        case when t.sex_code!=1 AND t.sex_code !=2 then 1 end AS other,

        case 
            when 
                t.skill_test_result='Pass' OR   t.skill_test_result='Passed' 
                OR t.skill_test_result='Standard met' OR t.skill_test_result='Yes' 
                OR t.skill_test_result='A' OR t.skill_test_result='A+'
                OR t.skill_test_result='B' OR t.skill_test_result='B+'
                OR t.skill_test_result='C' OR t.skill_test_result='C+'
                OR t.skill_test_result='D' OR t.skill_test_result='D+'
            then 1 
        end AS pass,

        case when 
            (SELECT id FROM trainee_placements WHERE trainees_id=t.id LIMIT 1) IS NOT NULL 
            then    1 END AS placement

    FROM trainees t
    LEFT JOIN sectors subsector_SECTOR ON subsector_SECTOR.uniform_code = training_trade_SUBSECTOR.sector_code 
;