Error related to only_full_group_by when executing a query in MySql

I upgraded my system from MySQL 5.4 to 5.7.17 with PHP for a web application I’m working on. I have a query that is dynamically created, and when run in older versions of MySql it works fine. Since upgrading to 5.7.17 I get this error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘xxx.tablenamehere’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by This is my query: So I tried to…

Continue reading »

Writing a trigger to generate uuid in a table

If you never had a field containing uuid before in MySQL table, now you can add a new field default is null. Then create a trigger so that every time there’s a new record inserted, the uuid will be automatically generated for that row. Here’s how you write a trigger to add uuid to a field “user_api_key” in the table “api_users” If you run into this error, make sure you don’t duplicate the trigger. This version of MySQL doesn’t yet support ‘multiple triggers with the…

Continue reading »

Setting up MySQL replication in V5.6.17

Setting up MySQL replication In this article the Master is 210.126.187.015 and the Slave is 210.126.187.016 On the Master, edit /etc/mysql/my.cnf (my.ini in Windows): 1) server-id = 126187015 # choose a unique integer 2) log_bin = /var/log/mysql/mysql-bin.log 3) bind-address = 0.0.0.0 # listen on all interfaces (by default 127.0.0.1 is used) 4) binlog_do_db = mydb # replicate only the ‘mydb’ database On Windows environment: 1) server-id = 127001 # choose a unique integer 2) log_bin = C:/Documents and Settings/JohnDoe/My Documents/Application Data/MySQL/MySQL56/mysql-bin.log 3) bind-address = 0.0.0.0…

Continue reading »

How to insert an image to MySql database with php

A BLOB is a binary large object that can hold a variable amount of data. There’re 4 datatypes that you can select for a blob field, depending how much storage of an image you’ll need. TINYBLOB A binary large object column with a maximum length of 255 (2^8 – 1) characters. BLOB A binary large object column with a maximum length of 65535 (2^16 – 1) characters. MEDIUMBLOB A binary large object column with a maximum length of 16777215 (2^24 – 1) characters. LONGBLOB A…

Continue reading »

SQL Stored Procedures

Sometimes in your Database Admin career path, you’ve got to create a procedure to save your time and reduce loads of work on server. For example, everyday you need to run a report of how many customers have signed up for a newsletter, or you need to pull out a list of customers who purchased items for the past 12 hours… In these cases, you don’t want to re-write a SQL query every time; instead, you’ll create a procedure and store it in the Database…

Continue reading »