Grails(21)GORM Configuration and MYSQL Logging

Grails(21)GORM Configuration and MYSQL Logging

1. Mysql Logging
First of all, login in MYSQL
mysql> show variables like "%general_log%";
+------------------+----------------------------------------+

| Variable_name    | Value                                  | +------------------+----------------------------------------+ |

general_log      | OFF                                    | |

general_log_file | /usr/local/mysql/data/sparkworker1.log |

This show variables command will show us if the Logging is On in MySQL Configuration.

Then we can turn it On
mysql>set global general_log = true;

After that, our grails application will connect to Mysql and execute the command. We can see all the SQL statement in the file
>sudo tail -f /usr/local/mysql/data/sparkworker1.log

The content will be something like this>
select campaign0_.id as id14_, campaign0_.version as version14_, campaign0_.announcement_delivery_details_delay as announce3_14_, campaign0_.announcement_delivery_details_delivery_type as announce4_14_, campaign0_.announcement_delivery_details_target_delivery as announce5_14_, campaign0_.tenant_id as tenant6_14_, campaign0_.campaign_name as campaign7_14_, campaign0_.campaign_type as campaign8_14_, campaign0_.created_by_id as created9_14_, campaign0_.date_created as date10_14_, campaign0_.description as descrip11_14_, campaign0_.enabled as enabled14_, campaign0_.end_date as end13_14_, campaign0_.last_updated as last14_14_, campaign0_.message_location as message15_14_, campaign0_.modified_by_id as modified16_14_, campaign0_.notification_message as notific17_14_, campaign0_.notification_title as notific18_14_, campaign0_.promotion_duration as promotion19_14_, campaign0_.promotion_expiration_date as promotion20_14_, campaign0_.promotion_message as promotion21_14_, campaign0_.promotion_message_type as promotion22_14_, campaign0_.promotion_title as promotion23_14_, campaign0_.start_date as start24_14_, campaign0_.announcement_schedule_id as announc26_14_, campaign0_.throttle_interval as throttle27_14_, campaign0_.class as class14_ from campaign campaign0_ inner join campaign_beacons beacons1_ on campaign0_.id=beacons1_.campaign_id inner join beacon_region beaconregi2_ on beacons1_.beacon_id=beaconregi2_.id inner join geo_fence beaconregi2_1_ on beaconregi2_.id=beaconregi2_1_.id where beaconregi2_.id=3 and campaign0_.enabled=1 and campaign0_.tenant_id=1 and campaign0_.campaign_type='STORE_CHECKIN' and campaign0_.start_date<='2014-05-21 18:55:16' and campaign0_.end_date>='2014-05-21 18:55:16'

2. GORM Configuration Changes
Set<Beacon> beacons = []
static hasMany = [beacons:Beacon]

static mapping = {
     beacons(lazy: false, joinTable: [name:’campagin_beacons’, key: ‘campaign_id’, column: ‘beacon_id’])
}

So the relation_table1_table2 table name will be campaign_beacons, and the 2 columns in that table should be
campagin_id and beacon_id.


References:
http://stackoverflow.com/questions/2568507/how-to-log-sql-statements-in-grails

http://grails.org/doc/1.3.7/guide/5.%20Object%20Relational%20Mapping%20(GORM).html#5.2.1.3%20Many-to-many