|
The table in that query is NOT a federated table however I do have a federated table in that database. (ir_users)
I also looked at subsequent crashes and they do have queries related to the federated table.
Here is an example:
SELECT
|
iu.username,
|
iu.firstName,
|
iu.lastName,
|
if(iu.imJob_project_id in (6, 7, 8, 14, 16), 'TSM', 'TSR') as role,
|
case
|
when iu.imJob_project_id in (2, 6, 9) then 'Access Wireless'
|
when iu.imJob_project_id in (5, 8, 11) then 'Budget Mobile'
|
when iu.imJob_project_id in (4, 7, 10) then 'Life Wireless'
|
when iu.imJob_project_id in (13, 14, 15, 16) then 'Safelink'
|
end as mv_client
|
FROM ir_users iu
|
left join user u on iu.id = u.ir_id
|
where
|
iu.imJob_project_id in (2, 4, 5, 6, 7, 8, 9, 10, 11, 13, 14, 15, 16)
|
and iu.RecordStatus = 1
|
and u.id is null;
|
MariaDB [awims]> show index in ir_users;
|
+----------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+----------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| ir_users | 0 | PRIMARY | 1 | ID | NULL | NULL | NULL | NULL | | REMOTE | | |
|
| ir_users | 0 | UserName | 1 | UserName | NULL | NULL | NULL | NULL | YES | REMOTE | | |
|
| ir_users | 1 | RecordStatus | 1 | RecordStatus | NULL | NULL | NULL | NULL | | REMOTE | | |
|
| ir_users | 1 | GroupID | 1 | GroupID | NULL | NULL | NULL | NULL | | REMOTE | | |
|
| ir_users | 1 | CompanyID | 1 | CompanyID | NULL | NULL | NULL | NULL | YES | REMOTE | | |
|
+----------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
5 rows in set (0.02 sec)
|
MariaDB [awims]>
|
| ir_users | CREATE TABLE `ir_users` (
|
`ID` int(1) unsigned NOT NULL AUTO_INCREMENT,
|
`ModifiedBy` int(1) unsigned NOT NULL DEFAULT '0',
|
`ModifiedDT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
`CreatedBy` int(1) unsigned NOT NULL DEFAULT '0',
|
`CreatedDT` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
`RecordStatus` tinyint(1) NOT NULL DEFAULT '1',
|
`LastLogin` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
`UserName` varchar(64) DEFAULT NULL,
|
`Password` tinytext,
|
`GroupID` int(1) unsigned NOT NULL DEFAULT '0',
|
`RepType` tinyint(3) unsigned NOT NULL DEFAULT '0',
|
`MessageBoard` tinyint(1) NOT NULL DEFAULT '0',
|
`CompanyID` int(1) unsigned DEFAULT '0',
|
`Prefix` tinytext,
|
`FirstName` tinytext,
|
`MiddleName` tinytext,
|
`LastName` tinytext,
|
`SSN` tinytext,
|
`EmployeeID` tinytext,
|
`Shirt` enum('-','XS','S','M','L','XL','XXL','XXXL','TBD') DEFAULT '-',
|
`Sex` enum('-','M','F') DEFAULT '-',
|
`Title` tinytext,
|
`CompanyEmail` tinytext,
|
`PersonalEmail` tinytext,
|
`ManagerID` int(1) unsigned DEFAULT '0',
|
`ManagerID2` int(1) unsigned DEFAULT '0',
|
`HomePhone` tinytext,
|
`OfficePhone` tinytext,
|
`CellPhone` tinytext,
|
`Pager` tinytext,
|
`HomeAddress1` tinytext,
|
`HomeAddress2` tinytext,
|
`HomeCity` tinytext,
|
`HomeStateID` int(1) unsigned DEFAULT '0',
|
`HomeZIP` tinytext,
|
`HomeCountryID` int(1) unsigned DEFAULT '0',
|
`MailAddress1` tinytext,
|
`MailAddress2` tinytext,
|
`MailCity` tinytext,
|
`MailStateID` int(1) unsigned DEFAULT '0',
|
`MailZIP` tinytext,
|
`MailCountryID` int(1) unsigned DEFAULT '0',
|
`DOB` date DEFAULT '0000-00-00',
|
`StartDate` date DEFAULT '0000-00-00',
|
`LastDate` date DEFAULT '0000-00-00',
|
`Insurance` tinytext,
|
`InsExpDate` date DEFAULT NULL,
|
`ReHire` tinyint(1) DEFAULT '1',
|
`Exempt` enum('Contractor','NonExempt','Exempt') DEFAULT 'Exempt',
|
`FullTime` tinyint(1) DEFAULT NULL,
|
`Notes` varchar(1024) DEFAULT NULL,
|
`SuperUser` tinyint(1) NOT NULL DEFAULT '0',
|
`Trainer` tinyint(1) NOT NULL DEFAULT '1',
|
`Merchandiser` tinyint(1) NOT NULL DEFAULT '1',
|
`Attribute1` tinyint(3) unsigned NOT NULL DEFAULT '0',
|
`Attribute2` tinyint(3) unsigned NOT NULL DEFAULT '0',
|
`Attribute3` tinyint(3) unsigned NOT NULL DEFAULT '0',
|
`DemoDayRep` tinyint(4) NOT NULL DEFAULT '0',
|
`Hint` tinytext,
|
`PasswordHintAnswer` tinytext,
|
`MarketID` int(1) unsigned DEFAULT '0',
|
`MarketOther` tinytext,
|
`BankName` tinytext,
|
`BankAccount` tinytext,
|
`RoutingNumber` tinytext,
|
`BranchNumber` varchar(45) DEFAULT NULL,
|
`PayRoll` tinyint(1) NOT NULL DEFAULT '0',
|
`OwnCompanyName` tinytext,
|
`EIN` tinytext,
|
`Agreement` tinyint(1) DEFAULT '0',
|
`DistrictID` int(10) unsigned NOT NULL DEFAULT '0',
|
`AccountType` tinytext,
|
`DLicense` varchar(64) DEFAULT NULL,
|
`DLicenseStateID` int(10) NOT NULL DEFAULT '0',
|
`BizLic` varchar(64) DEFAULT NULL,
|
`W9BusinessName` tinytext,
|
`W9BusinessType` varchar(45) DEFAULT NULL,
|
`W9Exempt` tinyint(3) unsigned DEFAULT NULL,
|
`W9Requester` tinytext,
|
`W9Account` tinytext,
|
`W9Date` date DEFAULT NULL,
|
`W9DifferentBuzName` tinytext,
|
`TriNetStatus` tinyint(3) unsigned NOT NULL DEFAULT '0',
|
`OasisStatus` tinyint(3) unsigned NOT NULL DEFAULT '0',
|
`PasswordResetCode` char(32) DEFAULT NULL,
|
`LatY` float DEFAULT NULL,
|
`LonX` float DEFAULT NULL,
|
`imJob_project_id` int(10) unsigned DEFAULT NULL,
|
`imJob_posting_id` int(10) unsigned DEFAULT NULL,
|
PRIMARY KEY (`ID`),
|
UNIQUE KEY `UserName` (`UserName`),
|
KEY `RecordStatus` (`RecordStatus`),
|
KEY `GroupID` (`GroupID`),
|
KEY `CompanyID` (`CompanyID`)
|
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://federated:XXXXXXXX@XXXXXXXXXXXX:3306/imrepqa0706/users'
|
The actual table is residing in MariaDB Server version: 10.1.20
Create table:
|
CREATE TABLE `users` (
|
`ID` int(1) unsigned NOT NULL AUTO_INCREMENT,
|
`ModifiedBy` int(1) unsigned NOT NULL DEFAULT '0',
|
`ModifiedDT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
`CreatedBy` int(1) unsigned NOT NULL DEFAULT '0',
|
`CreatedDT` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
`RecordStatus` tinyint(1) NOT NULL DEFAULT '1',
|
`LastLogin` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
`UserName` varchar(64) DEFAULT NULL,
|
`Password` tinytext,
|
`GroupID` int(1) unsigned NOT NULL DEFAULT '0',
|
`RepType` tinyint(3) unsigned NOT NULL DEFAULT '0',
|
`MessageBoard` tinyint(1) NOT NULL DEFAULT '0',
|
`CompanyID` int(1) unsigned DEFAULT '0',
|
`Prefix` tinytext,
|
`FirstName` tinytext,
|
`MiddleName` tinytext,
|
`LastName` tinytext,
|
`SSN` tinytext,
|
`EmployeeID` tinytext,
|
`Shirt` enum('-','XS','S','M','L','XL','XXL','XXXL','TBD') DEFAULT '-',
|
`Sex` enum('-','M','F') DEFAULT '-',
|
`Title` tinytext,
|
`CompanyEmail` tinytext,
|
`PersonalEmail` tinytext,
|
`ManagerID` int(1) unsigned DEFAULT '0',
|
`ManagerID2` int(1) unsigned DEFAULT '0',
|
`HomePhone` tinytext,
|
`OfficePhone` tinytext,
|
`CellPhone` tinytext,
|
`Pager` tinytext,
|
`HomeAddress1` tinytext,
|
`HomeAddress2` tinytext,
|
`HomeCity` tinytext,
|
`HomeStateID` int(1) unsigned DEFAULT '0',
|
`HomeZIP` tinytext,
|
`HomeCountryID` int(1) unsigned DEFAULT '0',
|
`MailAddress1` tinytext,
|
`MailAddress2` tinytext,
|
`MailCity` tinytext,
|
`MailStateID` int(1) unsigned DEFAULT '0',
|
`MailZIP` tinytext,
|
`MailCountryID` int(1) unsigned DEFAULT '0',
|
`DOB` date DEFAULT '0000-00-00',
|
`StartDate` date DEFAULT '0000-00-00',
|
`LastDate` date DEFAULT '0000-00-00',
|
`Insurance` tinytext,
|
`InsExpDate` date DEFAULT NULL,
|
`ReHire` tinyint(1) DEFAULT '1',
|
`Exempt` enum('Contractor','NonExempt','Exempt') DEFAULT 'Exempt',
|
`FullTime` tinyint(1) DEFAULT NULL,
|
`Notes` varchar(1024) DEFAULT NULL,
|
`SuperUser` tinyint(1) NOT NULL DEFAULT '0',
|
`Trainer` tinyint(1) NOT NULL DEFAULT '1',
|
`Merchandiser` tinyint(1) NOT NULL DEFAULT '1',
|
`Attribute1` tinyint(3) unsigned NOT NULL DEFAULT '0',
|
`Attribute2` tinyint(3) unsigned NOT NULL DEFAULT '0',
|
`Attribute3` tinyint(3) unsigned NOT NULL DEFAULT '0',
|
`DemoDayRep` tinyint(4) NOT NULL DEFAULT '0',
|
`Hint` tinytext,
|
`PasswordHintAnswer` tinytext,
|
`MarketID` int(1) unsigned DEFAULT '0',
|
`MarketOther` tinytext,
|
`BankName` tinytext,
|
`BankAccount` tinytext,
|
`RoutingNumber` tinytext,
|
`BranchNumber` varchar(45) DEFAULT NULL,
|
`PayRoll` tinyint(1) NOT NULL DEFAULT '0',
|
`OwnCompanyName` tinytext,
|
`EIN` tinytext,
|
`Agreement` tinyint(1) DEFAULT '0',
|
`DistrictID` int(10) unsigned NOT NULL DEFAULT '0',
|
`AccountType` tinytext,
|
`DLicense` varchar(64) DEFAULT NULL,
|
`DLicenseStateID` int(10) NOT NULL DEFAULT '0',
|
`BizLic` varchar(64) DEFAULT NULL,
|
`W9BusinessName` tinytext,
|
`W9BusinessType` varchar(45) DEFAULT NULL,
|
`W9Exempt` tinyint(3) unsigned DEFAULT NULL,
|
`W9Requester` tinytext,
|
`W9Account` tinytext,
|
`W9Date` date DEFAULT NULL,
|
`W9DifferentBuzName` tinytext,
|
`TriNetStatus` tinyint(3) unsigned NOT NULL DEFAULT '0',
|
`OasisStatus` tinyint(3) unsigned NOT NULL DEFAULT '0',
|
`PasswordResetCode` char(32) DEFAULT NULL,
|
`LatY` float DEFAULT NULL,
|
`LonX` float DEFAULT NULL,
|
`imJob_project_id` int(10) unsigned DEFAULT NULL,
|
`imJob_posting_id` int(10) unsigned DEFAULT NULL,
|
PRIMARY KEY (`ID`),
|
UNIQUE KEY `UserName` (`UserName`),
|
KEY `RecordStatus` (`RecordStatus`),
|
KEY `GroupID` (`GroupID`),
|
KEY `CompanyID` (`CompanyID`)
|
) ENGINE=MyISAM AUTO_INCREMENT=15746 DEFAULT CHARSET=latin1
|
MariaDB [imrepqa0706]> show index in users;
|
+-------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+-------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| users | 0 | PRIMARY | 1 | ID | A | 15738 | NULL | NULL | | BTREE | | |
|
| users | 0 | UserName | 1 | UserName | A | NULL | NULL | NULL | YES | BTREE | | |
|
| users | 1 | RecordStatus | 1 | RecordStatus | A | 2 | NULL | NULL | | BTREE | | |
|
| users | 1 | GroupID | 1 | GroupID | A | 6 | NULL | NULL | | BTREE | | |
|
| users | 1 | CompanyID | 1 | CompanyID | A | 23 | NULL | NULL | YES | BTREE | | |
|
+-------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
5 rows in set (0.00 sec)
|
Server config:
[root@centos1 pmonev]# mysqld --print-defaults
|
mysqld would have been started with the following arguments:
|
--port=3306 --socket=/var/lib/mysql/mysql.sock --skip-external-locking --key_buffer_size=8192M --max_allowed_packet=500M --table_open_cache=256 --sort_buffer_size=2M --read_buffer_size=1M --read_rnd_buffer_size=4M --myisam_sort_buffer_size=64M --thread_cache_size=8 --query_cache_size=16M --thread_concurrency=8 --symbolic-links=0 --max_connections=256 --lower_case_table_names=1 --innodb_data_home_dir=/var/lib/mysql --innodb_data_file_path=ibdata1:10M:autoextend --innodb_log_group_home_dir=/var/lib/mysql --innodb_buffer_pool_size=8192M --innodb_additional_mem_pool_size=20M --innodb_log_file_size=625M --innodb_log_buffer_size=256M --innodb_flush_log_at_trx_commit=2 --innodb_lock_wait_timeout=50 --bind-address=10.101.10.38
|
The use of the federated table in my case is not that important so I dropped it just to see if that will stop the server from crashing.
I can bring the table back if you need more information or logs.
|