Mysql – making MySQL stay in strict mode

Since Zapier is coming in from outside your instance, a possible cause of your problem is the security group for your instance. Most ports on EC2 instances (except the SSH port) are normally blocked by default unless you explicitly open them.

So you would need to open the mysql port 3306 on your instance up to the ip address Zapier is coming in from. (preferably you would not open it up to everyone in the world.)

See Using Amazon RDS/EC2 on the Zapier site, starting from the section where it says Can't Connect to My Database. They continue on to provide specific instructions for the security group settings.

You will also probably need to grant permission on the database using the mysql GRANT command to the user coming in from the Zapier ip address.

Something like this should work :

GRANT ALL PRIVILEGES
ON mydb.*
TO 'user'@'54.86.9.50'
IDENTIFIED BY 'newpassword';

Or you can allow that user to come in from any ip adress like this:

GRANT ALL PRIVILEGES
ON mydb.*
TO 'user'@'%'
IDENTIFIED BY 'newpassword';

(Incoming requests would still be limited to Zapier ip addresses due to the security group settings.)

First of all, there are two caches relevant to your query.

The Query cache uses SQL_(NO)_CACHE and is the less important cache. For production systems with lots of writes, you may as well turn off the QC. Every write to a table flushes all entries in the QC for the table(s) involved. That leads to another point: A large QC can lead to a lot of flushing activity. Limit query_cache_size to no more than about 50M. The QC can help with CPU.

The buffer_pool is the important cache for InnoDB. You should give about 70% of RAM to innodb_buffer_pool_size. That is where blocks of InnoDB data and indexes are cached. It helps because it avoids much of the I/O.

Often, the most important thing to do is to identify the slowest queries (using SHOW PROCESSLIST and/or the slowlog) and work on speeding them up. Sometimes adding an index, sometimes a minor change to a SELECT, etc, can speed up the system far more than tweaking the cache sizes.

The buffer_pool has a way to avoid letting a table scan blow out the cache. The QC has no mechanism like what you are asking for.

If you do keep the QC on, then set query_cache_type=DEMAND and use SQL_(NO)_CACHE.