MySQL

Some common MySQL commands

Joining 2 tables on some columns

mysql> select T1.training_data_set_id, T1.food_aws_url from \
_training_data_set as T1, _observation_data as T2 \
where T2.source like '%<source-name>' and T2.training_data_set_id=T1.training_data_set_id;

mysql> SELECT T1.inserted_date, T1.training_data_set_id, T1.food_aws_url, T1.annotate_file_url, \
T3.commodity FROM _training_data_set as T1, _observation_data as \
T2, _training_commodity_variety as T3 where T2.source='<source-name>' \
and T2.training_data_set_id=T1.training_data_set_id and T1.training_data_set_id=T3.training_data_set_id \
and T3.commodity='blueberry';

mysql> SELECT T1.inserted_date, T1.training_data_set_id, T1.food_aws_url, T1.annotate_file_url, \
T3.commodity FROM _training_data_set as T1, _observation_data as T2, \
_training_commodity_variety as T3 where T2.source='<source-name>';

# to connect multiple tables and also use pattern search using 'like'
mysql> select tds.training_data_set_id from _training_commodity_variety as tcv, \
_training_data_set as tds  where tds.training_data_set_id=tcv.training_data_set_id and \
tcv.commodity="strawberry-IU" and tcv.variety="green" and food_aws_url like '%_top_0%';

To see the structure of the table

mysql> show create table _coordinate_data;  
mysql> describe _coordinate_data;

To find all the tables that have the 'delete cascade on' set on the FK

Ref: http://www.mysqltutorial.org/mysql-on-delete-cascade/

mysql> use information_schema;
No connection. Trying to reconnect...
Connection id:    76494
Current database: *** NONE ***

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Database changed
mysql> 
mysql> select table_name from referential_constraints \
where constraint_schema='<db name>' \
and referenced_table_name='_training_data_set' \
and delete_rule='cascade';

+------------------------------------------+
| table_name                               |
+------------------------------------------+
| _camera_data                      |
| _config_data                      |
| _coordinate_data                  |
| _driscoll_produce_parameter       |
| _driscoll_produce_parameter_count |
| _generic_produce_parameter        |
| _observation_data                 |
| _pixel_data                       |
| _usda_produce_count               |
| _usda_produce_parameter           |
+------------------------------------------+
10 rows in set (0.29 sec)

To get to the EC2 MySQL from Mac terminal as root

 ------------------------------
 For the RDS first instance
 ------------------------------
 mysql -h <hostname>.us-west-2.rds.amazonaws.com -P 3306 -u root -p

 for password enter: <passwd>
 regular user password: <regular user passwd>

 ------------------------------
 For the RDS second instance
 ------------------------------
 root user: mysql -h <hostname>.us-west-2.rds.amazonaws.com -P 3306 -u <user id> --password="<passwd>"
 regular user: mysql -h <hostname>.us-west-2.rds.amazonaws.com -P 3306 -u <user id> --password="<passwd>"

To copy database

# From Mac or Ubuntu machine, first create an empty destination database: - did it as root  )
> mysqladmin -h <hostname>.us-west-2.rds.amazonaws.com -P 3306 -u root -p create <db name>

# Next dump the source database locally on mac disk - did it as a regular user 
> mysqldump -h <hostname>.us-west-2.rds.amazonaws.com -P 3306 -u <user id> -p <passwd> > <db dump name>.sql

# Finally, copy the dumped *.sql file on the empty destination database - did it as root
> mysql -h <hostname>.us-west-2.rds.amazonaws.com -P 3306 -u root -p <passwd> < <db dump name>.sql


# Another example - From Mac
 mysqladmin -h <hostname>.us-west-2.rds.amazonaws.com -P 3306 -u <user id> -p create <db name>
 mysqldump -h <hostname>.us-west-2.rds.amazonaws.com -P 3306 -u <user id> -p <db name> > /Users/folder1/folder2/<db dump name>.sql

# copy the dumped sql file as root
 mysql -h <hostname>.us-west-2.rds.amazonaws.com -P 3306 -u root -p <db name> < /Users/folder1/folder2/<db dump name>.sql

To get csv file from command line

  mysql -h <hostname>.us-west-2.rds.amazonaws.com -P 3306 -u <user id> --password="<passwd>" <db name> -e "select inserted_date, training_data_set_id, license_key, input_data_id from _training_data_set where food_aws_url like '%almond_DC%' ;" > /tmp/almond_DC_full.txt

  # Then open the file and replace tabs with comma using :%s/\t/,/g

To drop database

 mysql> drop database <db name>;
 mysql> drop database `<db name>`; # use backtick to escape characters like '-'

 to do the same using mysqladmin from Mac terminal:

   mysqladmin -h <hostname>.us-west-2.rds.amazonaws.com -P 3306 -u root -p drop <DB NAME>
 then enter root password

To DROP table

mysql>  DROP TABLE IF EXISTS `_training_commodity_variety`;

# To drop multiple tables at once:
DROP TABLE IF EXISTS auth__access_tokens, auth__clients;

To create table

 mysql>  CREATE TABLE `_training_commodity_variety` (
           `id` int(11) NOT NULL AUTO_INCREMENT,
           `training_data_set_id` int(11) NOT NULL,
           `commodity` varchar(45) DEFAULT NULL,
           `variety` varchar(45) DEFAULT NULL,
           PRIMARY KEY (`id`),
           KEY `commodity_variety_data_id_idx` (`training_data_set_id`),
           CONSTRAINT `commodity_variety_data_id` FOREIGN KEY (`training_data_set_id`) REFERENCES `_training_data_set` (`training_data_set_id`) ON DELETE CASCADE ON UPDATE CASCADE
           ) ENGINE=InnoDB;


 mysql> CREATE TABLE `_cgroup` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `training_data_set_id` int(11) NOT NULL,
    -> `cgroup` varchar(45) DEFAULT NULL,
    -> `pair_shot` boolean DEFAULT 0,
    -> PRIMARY KEY (`id`),
    -> KEY `cgroup_id_idx` (`training_data_set_id`),
    -> CONSTRAINT `cgroup_id` FOREIGN KEY (`training_data_set_id`) REFERENCES `_training_data_set` (`training_data_set_id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

To update table

# to deactivate a user from using the  annotation platform
 update _user_details SET status='0' where user_id=9;

# to update multiple records (deactivate multiple users)
 update _user_details SET status='0' where user_id in (13, 18, 19, 20, 21, 22, 23, 24, 25, 26);

# To update table, after selecting id's
UPDATE _training_data_set as t, 
( 
SELECT training_data_set_id, annotated_food_aws_url 
FROM _training_data_set 
WHERE annotated_food_aws_url like '%INPUTEDITEDImages%' 
) as s 
SET t.annotated_input_food_aws_url = s.annotated_food_aws_url, t.annotated_food_aws_url = NULL WHERE t.training_data_set_id=s.training_data_set_id;

Other commands on table

# to know the directory where the DB is created
mysql> select @@datadir;

# insert
 insert into _organization (org_name, org_short_name) values ("com.jiddu.agskore-agshift", "agshift");
 insert into _training_user_register (email, first_name, last_name, company_name, status, mask) values ("[e-mail]", "Alexandra", "Perez", "agshift", "Active", 0);

# delete
 delete from _training_user_register where id=33;

# one complicated query:
 mysql> SELECT ts.training_data_set_id,ts.user_id,ts.food_id,ts.food_aws_url,ts.file_name,ts.file_size,ts.annotate_status,annotate_file_url,UNIX_TIMESTAMP(ts.inserted_date) as inserted_date,ps.depth,ps.pixel_height,ps.pixel_width, T3.commodity FROM agshift_training_data_set ts,_observation_data os, _pixel_data ps, _training_commodity_variety as T3 where os.training_data_set_id=ts.training_data_set_id and os.source='<source name>' and ps.training_data_set_id = ts.training_data_set_id and ts.training_data_set_id=T3.training_data_set_id and T3.commodity='cashews' AND T3.variety='gc' and ts.training_data_set_id IS NOT NULL order by ts.inserted_date DESC;

To CREATE USER and grant privileges

# first login as root user
mysql -h <hostname>.us-west-2.rds.amazonaws.com -P 3306 -u <user id> --password="<passwd>"

# Next create a user with a password
CREATE USER '<user id>'@'%' IDENTIFIED BY '<passwd>'

# Grant all privileges to the DB <db name>.* to this user
GRANT ALL PRIVILEGES ON <db name>.* TO '<user id>'@'%' WITH GRANT OPTION;

# See the privilege
SHOW GRANTS FOR '<user id>'@'%';
+-------------------------------------------------------------------------------+
| Grants for <user id>@%                                                   |
+-------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO '<user id>'@'%' IDENTIFIED BY PASSWORD <secret>    |
| GRANT ALL PRIVILEGES ON `<db name>`.* TO '<user id>'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------------------------+
2 rows in set (0.04 sec)

To change GRANT Privileges

mysql> SHOW GRANTS FOR <db name>;
mysql> SHOW GRANTS FOR root;
mysql> GRANT DELETE, DROP on <db name>.* TO '<user id>'@'%';
# for database with dashes in the name
mysql> GRANT DELETE, DROP on `<db name>`.* TO '<user id>'@'%';

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON `<db name>`.* TO '<user id>'@'%';

To DELETE data from table

 mysql> delete from _training_data_set where food_aws_url like '%strawberry-IR%';
 Query OK, 1128 rows affected (0.20 sec)

 ## Check the dependent table that the deletion actually worked
 mysql> select * from _training_data_set where food_aws_url like '%strawberry-IR%';
 Empty set (0.05 sec)

 mysql> select training_data_set_id from _training_commodity_variety where commodity="strawberry-IR";
 Empty set (0.05 sec)

 mysql> delete from _training_data_set where food_aws_url like '%strawberry-IU%';
 Query OK, 748 rows affected (0.15 sec)

 ## Check the dependent table that the deletion actually worked
 mysql> select training_data_set_id from _training_commodity_variety where commodity="strawberry-IU";
 Empty set (0.24 sec)