Comment 3 for bug 1706992

That bin log shows the last query that was run as (not sure if this is the problem query)

------------------------------

### UPDATE `sterling`.`pagerequests`
### WHERE
### @1='077b287a-72de-11e7-afa1-1206bb36a2db' /* STRING(144) meta=65168 nullable=0 is_null=0 */
### @2='3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8' /* STRING(144) meta=65168 nullable=0 is_null=0 */
### @3='71.183.108.253' /* VARSTRING(256) meta=256 nullable=0 is_null=0 */
### @4=NULL /* STRING(144) meta=65168 nullable=1 is_null=1 */
### @5=NULL /* STRING(16) meta=65040 nullable=1 is_null=1 */
### @6=NULL /* STRING(16) meta=65040 nullable=1 is_null=1 */
### @7='pindepot.com' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
### @8='/forgotsent.html?email=drake%40mezco.net' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### @9='https://pindepot.com/forgot.html' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### @10='Mac' /* VARSTRING(256) meta=256 nullable=1 is_null=0 */
### @11='10.12' /* VARSTRING(256) meta=256 nullable=1 is_null=0 */
### @12='Chrome' /* VARSTRING(256) meta=256 nullable=1 is_null=0 */
### @13='59.0' /* VARSTRING(256) meta=256 nullable=1 is_null=0 */
### @14='Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3071.115 Safari/537.36' /* BLOB/TEXT meta=2 nullable=0 is_null=0 */
### @15=0 /* TINYINT meta=0 nullable=0 is_null=0 */
### @16=1920 /* INT meta=0 nullable=1 is_null=0 */
### @17=1080 /* INT meta=0 nullable=1 is_null=0 */
### @18=0 /* TINYINT meta=0 nullable=1 is_null=0 */
### @19='a26f5174-047e-11e5-8309-3417ebdfde80' /* STRING(144) meta=65168 nullable=1 is_null=0 */
### @20='4a7f318f-0480-11e5-8309-3417ebdfde80' /* STRING(144) meta=65168 nullable=1 is_null=0 */
### @21=1501172310.234800 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
### @22=1501172310.234800 /* TIMESTAMP(6) meta=6 nullable=1 is_null=0 */
### @23=NULL /* STRING(144) meta=65168 nullable=1 is_null=1 */
### @24='2017:07:27' /* DATE meta=0 nullable=1 is_null=0 */
### @25='˃�E\x0c��ᨺ|҅�ϱ��\x16o��Z�Wй�V:$' /* STRING(32) meta=65056 nullable=1 is_null=0 */
### @26=NULL /* VARSTRING(512) meta=512 nullable=1 is_null=1 */
### @27='pindepot.com' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
### SET
### @1='077b287a-72de-11e7-afa1-1206bb36a2db' /* STRING(144) meta=65168 nullable=0 is_null=0 */
### @2='3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8' /* STRING(144) meta=65168 nullable=0 is_null=0 */
### @3='71.183.108.253' /* VARSTRING(256) meta=256 nullable=0 is_null=0 */
### @4=NULL /* STRING(144) meta=65168 nullable=1 is_null=1 */
### @5=NULL /* STRING(16) meta=65040 nullable=1 is_null=1 */
### @6=NULL /* STRING(16) meta=65040 nullable=1 is_null=1 */
### @7='pindepot.com' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
### @8='/forgotsent.html?email=drake%40mezco.net' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### @9='https://pindepot.com/forgot.html' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### @10='Mac' /* VARSTRING(256) meta=256 nullable=1 is_null=0 */
### @11='10.12' /* VARSTRING(256) meta=256 nullable=1 is_null=0 */
### @12='Chrome' /* VARSTRING(256) meta=256 nullable=1 is_null=0 */
### @13='59.0' /* VARSTRING(256) meta=256 nullable=1 is_null=0 */
### @14='Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3071.115 Safari/537.36' /* BLOB/TEXT meta=2 nullable=0 is_null=0 */
### @15=0 /* TINYINT meta=0 nullable=0 is_null=0 */
### @16=1920 /* INT meta=0 nullable=1 is_null=0 */
### @17=1080 /* INT meta=0 nullable=1 is_null=0 */
### @18=0 /* TINYINT meta=0 nullable=1 is_null=0 */
### @19='a26f5174-047e-11e5-8309-3417ebdfde80' /* STRING(144) meta=65168 nullable=1 is_null=0 */
### @20='4a7f318f-0480-11e5-8309-3417ebdfde80' /* STRING(144) meta=65168 nullable=1 is_null=0 */
### @21=1501172311.297198 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
### @22=1501172311.297198 /* TIMESTAMP(6) meta=6 nullable=1 is_null=0 */
### @23=NULL /* STRING(144) meta=65168 nullable=1 is_null=1 */
### @24='2017:07:27' /* DATE meta=0 nullable=1 is_null=0 */
### @25='˃�E\x0c��ᨺ|҅�ϱ��\x16o��Z�Wй�V:$' /* STRING(32) meta=65056 nullable=1 is_null=0 */
### @26=NULL /* VARSTRING(512) meta=512 nullable=1 is_null=1 */
### @27='pindepot.com' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
# at 38815
#170727 12:18:31 server id 1 end_log_pos 38846 CRC32 0x6826175b Xid = 10624876
COMMIT/*!*/;

------------------------------

(Our UUIDs are in binary, so the unprintable characters are expected)

Create table for pagerequests

------------------------------

CREATE TABLE `pagerequests` (
  `PageRequestID` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `CompanyID` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `IPAddress` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `UserID` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `SentEmailID` binary(16) DEFAULT NULL,
  `BlogID` binary(16) DEFAULT NULL,
  `Host` varchar(256) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `RequestURI` text COLLATE utf8mb4_unicode_ci,
  `Referer` text COLLATE utf8mb4_unicode_ci,
  `OS` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `OSVersion` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Browser` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `BrowserVersion` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `UserAgent` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `Mobile` tinyint(1) NOT NULL DEFAULT '0',
  `Width` int(11) DEFAULT NULL,
  `Height` int(11) DEFAULT NULL,
  `Unique` tinyint(1) DEFAULT '0',
  `CountryID` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `StateID` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `DateTimeAdded` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `DateTimeOnline` timestamp(6) NULL DEFAULT NULL,
  `LinkID` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `_DateAdded` date DEFAULT NULL,
  `_UserHash` binary(32) DEFAULT NULL,
  `_Query` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `_RootDomain` varchar(256) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`PageRequestID`),
  KEY `pagerequests_CompanyID_idx` (`CompanyID`),
  KEY `pagerequests_SentEmailID_idx` (`SentEmailID`),
  KEY `pagerequests_UserID_idx` (`UserID`),
  KEY `pagerequests_CountryID_idx` (`CountryID`),
  KEY `pagerequests_StateID_idx` (`StateID`),
  KEY `pagerequests__RootDomain` (`_RootDomain`),
  CONSTRAINT `pagerequests_CompanyID` FOREIGN KEY (`CompanyID`) REFERENCES `companies` (`CompanyID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `pagerequests_CountryID` FOREIGN KEY (`CountryID`) REFERENCES `countries` (`CountryID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `pagerequests_SentEmailID` FOREIGN KEY (`SentEmailID`) REFERENCES `sentemails` (`SentEmailID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `pagerequests_StateID` FOREIGN KEY (`StateID`) REFERENCES `states` (`StateID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `pagerequests_UserID` FOREIGN KEY (`UserID`) REFERENCES `users` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

------------------------------

And two triggers, one before insert:

CREATE DEFINER=`root`@`%` TRIGGER `pagerequests_BEFORE_INSERT` BEFORE INSERT ON `pagerequests` FOR EACH ROW BEGIN

set NEW.`_DateAdded`=date(NEW.`DateTimeAdded`);

set NEW.`DateTimeOnline`=NEW.`DateTimeAdded`;

set NEW.`_UserHash`=unhex(sha2(concat(NEW.`IPAddress`,NEW.`UserAgent`),256));

set NEW.`_Query`=`urldecoder`(`getqueryparameter`(NEW.`Referer`,'q'));

insert into `_companydatepagerequests` (`CompanyID`, `Date`, `PageRequests`)
values(NEW.`CompanyID`, date(NEW.`DateTimeAdded`), 1)
on duplicate key update `PageRequests`=`PageRequests`+1;

if NEW.`Unique` then
 insert into `_companydateuniquepagerequests` (`CompanyID`, `Date`, `PageRequests`)
 values(NEW.`CompanyID`, date(NEW.`DateTimeAdded`), 1)
 on duplicate key update `PageRequests`=`PageRequests`+1;
end if;

set NEW.`_RootDomain`=`getrootdomain`(NEW.`Referer`);

END

------------------------------

and one before update

------------------------------

CREATE DEFINER=`root`@`%` TRIGGER `pagerequests_BEFORE_UPDATE` BEFORE UPDATE ON `pagerequests` FOR EACH ROW BEGIN

set NEW.`_DateAdded`=date(NEW.`DateTimeAdded`);

END