<> SELECT rental.propref, deal.dealnum AS deal,rental.displayaddress as address,deal.dealid, (SELECT SUM(TERM) FROM invoices WHERE dealid = deal.dealid AND who != 'Tenant' and term > 0 ) AS paidsofar,deal.TERM, (SELECT SUM(TERMdays) FROM invoices WHERE dealid = deal.dealid AND who != 'Tenant' ) AS termdays, deal.startdate AS duedate,rental.building AS building,deal.account AS invoice,deal.startdate, deal.dealnames AS tenant,deal.dealid-deal.dealid as offerid,enddate,actualend,deal.FLWONNOTREQ FROM deal INNER JOIN rental ON rental.propref = deal.propref WHERE deal.cancelled = 0 AND deal.shortlet = 0 AND deal.commrate > 0 and account >= 0 CREATE TABLE `invoices` ( `invoiceid` INT(11) NOT NULL AUTO_INCREMENT, `Date` DATE NULL DEFAULT NULL, `Invoice` INT(11) NULL DEFAULT NULL, `Sub_total` DECIMAL(22,2) NULL DEFAULT NULL, `Vat` DECIMAL(22,2) NULL DEFAULT NULL, `Vat_rate` DECIMAL(22,2) NULL DEFAULT NULL, `Total` DECIMAL(22,2) NULL DEFAULT NULL, `Paid` BIT(1) NULL DEFAULT NULL, `Paydate` DATE NULL DEFAULT NULL, `Propref` DECIMAL(15,0) NULL DEFAULT NULL, `Building` DECIMAL(15,0) NULL DEFAULT NULL, `Llord` DECIMAL(15,0) NULL DEFAULT NULL, `Deal` DECIMAL(15,0) NULL DEFAULT NULL, `Who` VARCHAR(10) NULL DEFAULT NULL, `Descript` MEDIUMTEXT NULL DEFAULT NULL, `X_seq` DECIMAL(15,0) NULL DEFAULT NULL, `Remsent` DATE NULL DEFAULT NULL, `Post_seq` DECIMAL(15,0) NULL DEFAULT NULL, `Dealid` INT(11) NULL DEFAULT NULL, `Paytransid` INT(11) NULL DEFAULT NULL, `Term` INT(11) NULL DEFAULT NULL, `Clientpay` BIT(1) NULL DEFAULT NULL, `Statement` BIT(1) NULL DEFAULT NULL, `Paid2date` DATE NULL DEFAULT NULL, `Paidas` INT(11) NULL DEFAULT NULL, `Paidhow` VARCHAR(20) NULL DEFAULT NULL, `Commrate` DECIMAL(22,2) NULL DEFAULT NULL, `Commfee` INT(11) NULL DEFAULT NULL, `Commission` DECIMAL(22,2) NULL DEFAULT NULL, `Rentdemand` BIT(1) NULL DEFAULT NULL, `Duedate` DATE NULL DEFAULT NULL, `Void` BIT(1) NULL DEFAULT NULL, `Exportsage` DATETIME NULL DEFAULT NULL, `Voiddate` DATE NULL DEFAULT NULL, `Contactid` INT(11) NULL DEFAULT NULL, `Offerid` INT(11) NULL DEFAULT NULL, `Paid2landlord` BIT(1) NULL DEFAULT NULL, `Paid2chequenum` VARCHAR(20) NULL DEFAULT NULL, `Paid2bankref` INT(11) NULL DEFAULT NULL, `Transferamount` DECIMAL(22,2) NULL DEFAULT NULL, `Oldstylevat` BIT(1) NULL DEFAULT NULL, `Archived` BIT(1) NULL DEFAULT NULL, `Lastmodified` DATETIME NULL DEFAULT NULL, `Transferdate` DATE NULL DEFAULT NULL, `Billstotal` DECIMAL(22,2) NULL DEFAULT NULL, `Created` DATETIME NULL DEFAULT NULL, `Billstotal2` DECIMAL(22,2) NULL DEFAULT NULL, `Termdays` INT(11) NULL DEFAULT NULL, `Reimbursables` DECIMAL(22,2) NULL DEFAULT NULL, `Receivefrom` INT(11) NULL DEFAULT NULL, PRIMARY KEY (`invoiceid`), INDEX `building` (`Building`), INDEX `propref` (`Propref`), INDEX `llord` (`Llord`), INDEX `date` (`Date`), INDEX `paydate` (`Paydate`), INDEX `paid` (`Paid`), INDEX `deal` (`Deal`), INDEX `x_seq` (`X_seq`), INDEX `clientpay` (`Clientpay`), INDEX `duedate` (`Duedate`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB AUTO_INCREMENT=22166 ; 2019-11-19 9:13:54 99 [ERROR] InnoDB: Clustered record for sec rec not found index `Dealid` of table `dbabsoluteestateagents`.`invoices` Nov 19 09:13:54 server5 mysqld[30807]: InnoDB: sec index record PHYSICAL RECORD: n_fields 2; compact format; info bits 0 Nov 19 09:13:54 server5 mysqld[30807]: 0: len 4; hex 8000012e; asc .;; Nov 19 09:13:54 server5 mysqld[30807]: 1: len 4; hex 8000025a; asc Z;; Nov 19 09:13:54 server5 mysqld[30807]: InnoDB: clust index record PHYSICAL RECORD: n_fields 1; compact format; info bits 0 Nov 19 09:13:54 server5 mysqld[30807]: 0: len 8; hex 696e66696d756d00; asc infimum ;; Nov 19 09:13:54 server5 mysqld[30807]: TRANSACTION 422038467468224, ACTIVE 0 sec starting index read Nov 19 09:13:54 server5 mysqld[30807]: mysql tables in use 4, locked 0 Nov 19 09:13:54 server5 mysqld[30807]: 0 lock struct(s), heap size 1136, 0 row lock(s) Nov 19 09:13:54 server5 mysqld[30807]: MySQL thread id 99, OS thread handle 140562364991232, query id 140116 81.149.181.174 absoluteesta5HV1 Sending data Nov 19 09:13:54 server5 mysqld[30807]: SELECT rental.propref, deal.dealnum AS deal,rental.displayaddress as address,deal.dealid, Nov 19 09:13:54 server5 mysqld[30807]: #011(SELECT SUM(TERM) FROM invoices WHERE dealid = deal.dealid AND who != 'Tenant' and term > 0 ) AS paidsofar,deal.TERM, Nov 19 09:13:54 server5 mysqld[30807]: #011(SELECT SUM(TERMdays) FROM invoices WHERE dealid = deal.dealid AND who != 'Tenant' ) AS termdays, Nov 19 09:13:54 server5 mysqld[30807]: #011deal.startdate AS duedate,rental.building AS building,deal.account AS invoice,deal.startdate, Nov 19 09:13:54 server5 mysqld[30807]: #011deal.dealnames AS tenant,deal.dealid-deal.dealid as offerid,enddate,actualend,deal.FLWONNOTREQ Nov 19 09:13:54 server5 mysqld[30807]: #011FROM deal Nov 19 09:13:54 server5 mysqld[30807]: #011INNER JOIN rental ON rental.propref = deal.propref Nov 19 09:13:54 server5 mysqld[30807]: #011WHERE deal.cancelled = 0 Nov 19 09:13:54 server5 mysqld[30807]: InnoDB: Submit a detailed bug report to https://jira.mariadb.org/ Nov 19 09:13:54 server5 mysqld[30807]: 2019-11-19 9:13:54 99 [ERROR] InnoDB: Clustered record for sec rec not found index `Dealid` of table `dbabsoluteestateagents`.`invoices` Nov 19 09:13:54 server5 mysqld[30807]: InnoDB: sec index record PHYSICAL RECORD: n_fields 2; compact format; info bits 0 Nov 19 09:13:54 server5 mysqld[30807]: 0: len 4; hex 8000012e; asc .;; Nov 19 09:13:54 server5 mysqld[30807]: 1: len 4; hex 8000025b; asc [;; Nov 19 09:13:54 server5 mysqld[30807]: InnoDB: clust index record PHYSICAL RECORD: n_fields 1; compact format; info bits 0 Nov 19 09:13:54 server5 mysqld[30807]: 0: len 8; hex 696e66696d756d00; asc infimum ;; Nov 19 09:13:54 server5 mysqld[30807]: TRANSACTION 422038467468224, ACTIVE 0 sec starting index read these were the initial settings changed in .cnf file but i've also tested with all but first 5 left to default. max_allowed_packet = 1024M lower_case_table_names = 1 max_connections = 500 connect_timeout = 5 wait_timeout = 600 key_buffer_size = 16M thread_stack = 192K thread_cache_size = 8 max_allowed_packet = 1024M thread_cache_size = 128 sort_buffer_size = 4M bulk_insert_buffer_size = 16M tmp_table_size = 32M max_heap_table_size = 32M # # * MyISAM # # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched. On error, make copy and try a repair. myisam_recover_options = BACKUP key_buffer_size = 128M #open-files-limit = 2000 table_open_cache = 400 myisam_sort_buffer_size = 512M concurrent_insert = 2 read_buffer_size = 2M read_rnd_buffer_size = 1M # # * Query Cache Configuration # # Cache only tiny result sets, so we can fit more in the query cache. query_cache_limit = 128K query_cache_size = 64M # for more write intensive setups, set to DEMAND or OFF #query_cache_type = DEMAND <>