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:

$sql = <<<SQL
                    SELECT o.appraisal_order_id, o.requestor_id, c.org_name
                        from $dbom.appraisal_orders o 
                            inner join $dbom.requestor c on o.requestor_id = c.requestor_id
                                where o.provider_id =%1% group by c.requestor_id
SQL;
            
            $rtn = $db->GetRows($sql, $UID);
            return $rtn;

So I tried to alter the query a few times, run it again but there was no luck. Then I found a solution and decide to give it a try. Logged in to MySQL server and I ran these 2 statements that I found on stackoverflow forums. And it works.

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

or in Mysql/my.ini, you can add this sql_mode line in mysqld section, then restart MySQL server.

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Adding Comment Search Box to comments section

When the comments section of a post/page is getting traction, users couldn’t be able to go thru each page in the pagination area. It would be nice to add a search box right above the comments section where visitors just type a keyword to search thru all the comments of that particular post/page.


<div class="prw_rup prw_filters_query_resp" data-prwidget-name="filters_query_resp" data-prwidget-init="handlers"><div class="title">Show reviews that mention</div>

<div class="uk-formR">
<form method="get" >
<input type="text" name="cs" >
<input type="submit" name="submit" value="Search">
</form>
</div>

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”

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `test`.`add uuid to api_users table` AFTER INSERT
    ON `test`.`api_users`
    FOR EACH ROW BEGIN
	IF new.user_api_key IS NULL THEN
	SET new.user_api_key =UUID();
	END IF;
    END$$

DELIMITER ;

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 same action time and event for one table.

To check for duplicate trigger, use this statement

SHOW TRIGGERS WHERE `table` = 'api_users';

Color Detection Programming

Today we’re going to use NodeJS to write a program that can detect colors from an image. User uploads an image, NodeJS will process the image on the Server side and response with a list of color RGB values. It will return predictions about the dominant colors in an image.

We test with a US flag PNG image using NodeJS

.from('./_lab/img/usflag.png').getPalette((err, palette) => console.log(palette))
.from('./_lab/img/usflag.png').getSwatches((err, swatches) => console.log(swatches))

And the response from server is:

Swatch {
_rgb: [220, 4, 4],
_population: 324,
_hsl: [0, 0.9642857142857144, 0.4392156862745098]
},
LightVibrant:
Swatch {
_rgb: [228, 70, 70],
_population: 360,
_hsl: [0, 0.7452830188679245, 0.5843137254901961]
},
DarkVibrant:
Swatch {
_rgb: [5, 5, 82],
_population: 319,
_hsl: [0.6666666666666666, 0.885057471264368, 0.17058823529411765]
},
Muted:
Swatch {
_rgb: [168, 108, 112],
_population: 0,
_hsl: [0.9888888888888889, 0.2564102564102564, 0.5411764705882353]
},
LightMuted:
Swatch {
_rgb: [181, 181, 193],
_population: 50,
_hsl: [0.6666666666666666, 0.08823529411764695, 0.7333333333333334]
},
DarkMuted:
Swatch {
_rgb: [66, 57, 114],
_population: 56,
_hsl: [0.6929824561403509, 0.3333333333333333, 0.33529411764705885]
}

Calculating Bounding box for a face on an image

This time we’re using the Bounding box returned by Clarifai’s Face Model API . Google Vision and Amazon Face Detection Rekognition APIs are returning different type of data (x,y,width, height).

Amazon Rekognition:
left = image.width*BoundingBox.Left
top = image.height*BoundingBox.Top

 

Based on the description Face Detection Model’s response on this page https://www.clarifai.com/models/face-detection-image-recognition-model/a403429f2ddf4b49b307e318f00e528b#documentation, we’ll calculate the top-left corner and bottom-right corner.

given original image size 500 width x 333 height, the top-left coordinate of the image is (0.0, 0.0), and the bottom-right of the image is (1.0, 1.0)
Bounding Box:{
“top_row”: 0.22296476,
“left_col”: 0.6717238,
“bottom_row”: 0.33909792,
“right_col”: 0.74911636
}

The calculations are essentially percentages that are measures from the (0,0) top-left corner of the image and you can interpret them as:

The Top Left Corner of the bounding box is 22% from the top and 67% from the Left
The Bottom Right Corner of the bounding box is 33% from the top and 75% from the left

The corresponding “pixel” coordinates of this box would be:

Top Left: (335, 74)
Bottom Right: (375, 110)
Width:40
Height:36

These values (x,y,width,height) are used for client to draw a blur/opacity/solid boxes on faces on a picture.