GarciaPL Notes - Java and Others

A few days ago I found very useful solution (thanks for Biomathematicus!) for deleting tables from a MySQL schema. I know that it could be quite difficult for some developers (also for me) to manually remove every table from schema without removing this schema itself. So, there is a two-step solution which is going to help you removing a lot of tables from SQL database.

1) List all tables in the MySQL schema :

SELECT CONCAT('drop table ',table_name,'; ')
FROM information_schema.tables
WHERE table_schema = 'yourDatabaseName';

drop table table1; 
drop table table2;

Very useful piece of command line query which will help you to keep an eye on execution time of queries which are performed in your MySQL database. It helps me when I ask myself : 'Hm ... I am wondering if this query is now executed ... '. This question can be sometime annoying, so this is quite helpful adoption of mysqladmin tool.

mysqladmin -u root -p -i 1 processlist

-u your username
-p you be pleased to give database password
-i 1 interval which equals 1 second

+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 43 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

I used to find many times duplicates in my table in MySQL database this time. First of all, I will paste here my table structure below (yes i know that it is difficult to read) :

mysql> describe proxy_parser_statistics;
+-----------------------+---------+------+-----+---------+----------------+
| Field                 | Type    | Null | Key | Default | Extra          |
+-----------------------+---------+------+-----+---------+----------------+
| ID                    | int(11) | NO   | PRI | NULL    | auto_increment |
| file_name             | text    | NO   |     | NULL    |                |
| processing_stamp_date | date    | YES  |     | NULL    |                |
| processing_stamp_time | time    | YES  |     | NULL    |                |
| processed_records     | int(11) | YES  |     | NULL    |                |
+-----------------------+---------+------+-----+---------+----------------+

I just use this query :

SELECT a.id, a.file_name FROM proxy_parser_statistics a INNER JOIN proxy_parser_statistics b ON a.file_name = b.file_name WHERE a.id <> b.id