Archives: mysql

how to automatically blacklist unsubscribed emails on interspire email marketer

Simply create a trigger which will insert unsubscribed users in the blacklist after every update on the subscriber table.

FOR EACH ROW INSERT IGNORE INTO ya_banned_emails 
(emailaddress, list, bandate)
SELECT emailaddress, ‘g’, UNIX_TIMESTAMP( ) 
FROM iem_list_subscribers
WHERE unsubscribed != ‘0’;

Moodle installation problem

I just installed moodle on my server and got into a big mysql error complaining about binlog and other stuffs…

Cannot execute statement: impossible to write to binary log since 
BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine 
limited to row-based logging. InnoDB is limited to row-logging when 
transaction isolation level is READ COMMITTED or READ UNCOMMITTED

After a bit of googling and understand what this was about I figured I should add the following in my my.cnf file and then restart mysql.


How to get the value of the latest auto-incremented value on a mysql table with concurrent connections

When building a web application it can seem a bit tricky to get the value of the id (primary key) of the latest row inserted in a mysql table.

It is not that complicated.

The function LAST_INSERT_ID() can help you finding that. This function will return the latest auto-incremented value. No need to specify the table, it just takes the latest one.

SELECT LAST_INSERT_ID() ;

The next question is: what happens when I have many concurrent sessions on the same server? Will I get the latest inserted id from another user?


Modify the session timeout in phpmyadmin

Login and re-login and re-re-login in phpmyadmin is a huge pain.

Here is the way to extend sessions duration.

Open the file config.inc.php in /usr/share/phpmyadmin or in /etc/phpmyadmin

Add the following line:

$cfg['LoginCookieValidity']=86400;

And in php.ini, modify the following line:

session.gc_maxlifetime = 86400

Restart apache.

apache2ctl restart

You now have 24h sessions.