zwwcn

Just another WordPress.com site

Category Archives: MySQL

MySQL innodb file too large

We had problem when altering one of our largest table in MySQL server. Eventually we managed to apply the change, but MySQL wouldn’t release the disk space it took for temp table. MySQL mark the data as deleted, but it still holding the space. This link gives good answer on this problem:

Optimize innodb table when innodb_file_per_table disable

Solution to MySQL server has gone away

Add max_allowed_packet into my.ini, e.g: max_allowed_packet=1073741824

Concat in JPA native query

I am writing some native query to record customized info.

SELECT COUNT(DISTINCT i.OBJECT_ID),CONCAT(YEAR(i.DATE) ,'-',MONTH(i.DATE)) as yearDate FROM OP_INCIDENT i GROUP BY YEAR(i.DATE),MONTH(i.DATE)

The above query is supposed to generate data in format like [2,’2015-09′], but what I got is [2,[54,43,41,49,50]]. Instead giving a string year-month value, it gives me a data array.

An explicit type cast sovle the problem:

SELECT COUNT(DISTINCT i.OBJECT_ID),CONCAT(CAST(YEAR(i.DATE) AS CHAR),'-',CAST(MONTH(i.DATE) AS CHAR)) as yearDate FROM OP_INCIDENT i GROUP BY YEAR(i.DATE),MONTH(i.DATE)

Cannot delete or update a parent row: foreign key constraint fails

I had problem to delete a record, got the “foreign key constraint fails” error. We could temporately disable the foreign key check on MySQL, delete the wrong data, then change it back.

SET FOREIGN_KEY_CHECKS=0; — to disable foreign key check
SET FOREIGN_KEY_CHECKS=1; — to re-enable foreign key check

Mysql:There is no ‘@’ registered. Error 1449 (ERR_NO_SUCH_USER)

I copied a database from remote server to my local and I get this exception when trying to execute a trigger.

Delete the triggers fix the problem.

mysql trigger to track table change

Create a new table to store changes:

CREATE TABLE `data_log` (
	  `action` VARCHAR(40),
	  `changetime`   DATETIME,
	  `regionid` INT NOT NULL,
          `advisorid` INT NOT NULL,
          `agencyid` INT NOT NULL
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create trigger:

DELIMITER //
	CREATE TRIGGER add_region_data AFTER INSERT ON OP_REGION_ADVISOR_AGENCY
	FOR EACH ROW
	BEGIN
	  INSERT INTO data_log
	  VALUES('insert',NOW(),NEW.REGION_ID,NEW.ADVISOR_ID,NEW.AGENCY_ID);
	END;
        CREATE TRIGGER add_region_data AFTER DELETE ON OP_REGION_ADVISOR_AGENCY
        FOR EACH ROW
        BEGIN
          INSERT INTO data_log
          VALUES('insert',NOW(),OLD.REGION_ID,OLD.ADVISOR_ID,OLD.AGENCY_ID);
        END;
//

order by before group by in mysql

We have two entities: Activity 1:n Conversation. I need to get the latest conversation for each of the activity. The MySql doesn’t allow me to put order by before the group by.
I found a workaround from this :

So I get something like:
SELECT * FROM ( SELECT * FROM OP_TICKET_CONVERSATION WHERE ACTIVITY_ID IN (ticketsIds) ORDER BY OBJECT_ID DESC) AS tc GROUP BY tc.ACTIVITY_ID

This works but hibernate returns Object type, we need to specify the root entity:

this could be done by adding “addEntity”.

List conversationsList = em.createSQLQuery(sqlString).addEntity(TicketConversation.class).list();

fetch join collection break firstResult/MaxResult in hibernate

If a fetch join is used along with order by, hibernate will load all records into memory to do the sorting. It will ignore the firstResult/MaxResult which mean no real database pagination.

em.createQuery("SELECT a FROM TicketTracker tt
LEFT JOIN tt.ticket a
LEFT JOIN FETCH a.conversation tc
WHERE a.status!=400 GROUP BY tt.ticket ORDER BY a.dueDate", TicketActivity.class)

.setFirstResult(0).setMaxResults(10).list();

In this example, even we didn’t order records by the join entity, the join fetch still break the setFirstResult/SetMaxResult . I have to write a seperate query to fetch the conversations records.

tempary table

A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.)

 

CREATE TEMPORARY TABLE TMP_BILL_ADDRESS(ADDRESS_ID bigint);

INSERT INTO TMP_BILL_ADDRESS(ADDRESS_ID)
SELECT a.OBJECT_ID FROM OP_ADDRESS a;

DROP TABLE TMP_BILL_ADDRESS;

 

update , delete and left join

UPDATE OP_CONTACT c
LEFT JOIN OP_CONTACTTYPE ct ON ct.CONTACT_ID = c.OBJECT_ID
LEFT JOIN OP_CUSTOMER_CONTACT_XREF ref ON ref.CONTACT_TYPE_ID = ct.OBJECT_ID
SET c.IS_BILLING = (ct.TYPE = “BillContact”)
WHERE ref.CUSTOMER_ID IS NOT NULL AND ct.TYPE IN (“ShipContact”,”OtherContact”,”BillContact”);

 

DELETE  ad

FROM ADDRESS ad

LEFT JOIN CUSTOMER c ON c.ADDRESS_ID = ad.OBJECT_ID;