Description:
I have a query that in most cases return 8 results but time to time returns 1 result. Isn't deterministic! And I am absolutely sure that no other application is writing to the database.
select SQL_NO_CACHE supp.supplier_id, supp.name, supp.address1,
supp.address2, supp.address3, supp.city_id,
supp.county, supp.country_id, supp.postcode,
supp.longitude, supp.latitude,
supp.tax, supp.registration, supp.email,
supp.website,
supp.currency_id,
supp.branch_id,
supp.organization_id,
co.city_exclusive,
sc.NightHour_BT,
HOUR(sc.NightHourFrom_DT) as NightHourFrom_IN,
MINUTE(sc.NightHourFrom_DT) as NightMinuteFrom_IN,
HOUR(sc.NightHourTo_DT) as NightHourTo_IN,
MINUTE(sc.NightHourTo_DT) as NightMinuteTo_IN,
HOUR(sc.MealHourFrom1_DT) as MealHourFrom1_IN,
MINUTE(sc.MealHourFrom1_DT) as MealMinuteFrom1_IN,
HOUR(sc.MealHourTo1_DT) as MealHourTo1_IN,
MINUTE(sc.MealHourTo1_DT) as MealMinuteTo1_IN,
HOUR(sc.MealHourFrom2_DT) as MealHourFrom2_IN,
MINUTE(sc.MealHourFrom2_DT) as MealMinuteFrom2_IN,
HOUR(sc.MealHourTo2_DT) as MealHourTo2_IN,
MINUTE(sc.MealHourTo2_DT) as MealMinuteTo2_IN,
sc.ClosedTransfer_BT,
sc.RadialTransfer_BT,
sc.TransferHours_BT,
sc.TransferHoursDistance_BT,
sc.TransferDistance_BT,
sc.BasicTransfer_BT,
sc.WeekendDayFrom_IN,
sc.WeekendDayTo_IN,
sc.DisposalTimeMin_IN,
sc.DisposalDistanceHourMax_IN,
sc.GarageTransferTimeMax_IN,
sc.GarageTransferDistanceMax_IN,
sc.GenericSupplier_ID,
sc.RateSupplier_ID,
sc.SpecialDateRate_FT,
sc.Unlimited_BT,
sc.RadialCenter_ID,
sc.AirportSurcharge_BT,
sc.AirportSurchargeWaiting_FT,
sc.AirportSurchargePercent_FT,
sc.AirportDisposal_BT,
sc.IntAirportSurcharge_BT,
sc.IntAirportTerminal_FT,
sc.IntAirportPercent_FT,
sc.Gratuity_FT,
sc.Discount_FT,
sc.VAT_FT,
sc.Majorca_BT,
sc.TransferArrivalFee_BT,
sc.ADTransferSurcharge_BT,
sc.ADDeductHours_FT,
sc.ADArrivalFee_BT,
sc.DistanceLimit_BT,
sc.DistanceLimit_FT,
sc.TimeLimit_BT,
sc.TimeLimit_FT,
sc.DistanceB2BLimit_BT,
sc.DistanceB2BLimit_FT,
sc.ProportionLimit_FT,
MinimumRadiumLimit_FT,
tz.name as Timezone_VC,
supp.Status_ID,
es.priority,
sc.MinimumDistanceLimit_IN,
sc.MinimumTimeLimit_IN,
sc.MinimumSegment_IN,
sc.DistanceMeasure_IN,
dist.conversion,
dist.name as NameMeasure_VC,
sc.AsDirectedTimeLimit_FT,
sc.MinimumGarage_IN,
sc.ChargeStructure_ID,
curr.symbol as currencyCode,
(calculateDistance(supp.latitude, supp.longitude, 41.9794, 2.821426) + calculateDistance(41.91735, 3.206692, supp.latitude, supp.longitude)) as b2bDistance,
co.prioritat as priority_organization
from
suppliers supp
inner join suppliercustom sc ON (sc.supplier_id = supp.supplier_id and sc.Compass_BT = 1)
inner join timezones tz ON (supp.TimeZone_ID = tz.id)
inner join entitystatus es ON (es.id = supp.Status_ID)
inner join distancetypes dist ON (dist.id = sc.DistanceMeasure_IN)
inner join currencies curr ON (curr.id = supp.currency_id)
inner join organizations org ON (supp.organization_id = org.id
and org.status_id = 1)
inner join client_organization co ON (co.organization_id = org.id)
where co.client_id = 1501 and supp.supplier_id in
(
78
,
54
,
1918
,
1921
,
2820
,
2846
,
2850
,
2061
,
2373
,
1956
,
1959
)
order by priority_organization , priority , b2bDistance asc;
How to repeat:
delimiter $$
CREATE DEFINER=`root`@`localhost` FUNCTION `calculateDistance`(Latitude1 double, Longitude1 double, Latitude2 double, Longitude2 double) RETURNS double
DETERMINISTIC
BEGIN
return 6378100 * ACOS(COS(RADIANS(Latitude1))
* COS(RADIANS(Latitude2))
* COS(RADIANS(Longitude1) - RADIANS(Longitude2))
+ SIN(RADIANS(Latitude1))
* SIN(RADIANS(Latitude2)));
END$$
delimiter $$
CREATE TABLE `suppliers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`organization_id` int(10) NOT NULL DEFAULT '1',
`supplier_id` int(11) NOT NULL,
`suppliercode` varchar(45) NOT NULL,
`groupcode` varchar(45) NOT NULL,
`external` varchar(45) NOT NULL,
`headoffice_id` int(11) DEFAULT NULL,
`currency_id` int(11) DEFAULT NULL,
`language_id` int(11) DEFAULT NULL,
`timezone_id` int(11) DEFAULT NULL,
`type_id` int(11) NOT NULL,
`status_id` int(11) NOT NULL,
`communication_id` int(11) DEFAULT NULL,
`name` varchar(100) NOT NULL,
`fileas` varchar(100) NOT NULL,
`address1` varchar(100) NOT NULL,
`address2` varchar(100) NOT NULL,
`address3` varchar(100) NOT NULL,
`city_id` int(11) NOT NULL,
`county` varchar(45) NOT NULL,
`country_id` int(11) DEFAULT NULL,
`postcode` varchar(10) NOT NULL,
`longitude` varchar(45) NOT NULL,
`latitude` varchar(45) NOT NULL,
`location` varchar(45) NOT NULL DEFAULT '',
`payeename` varchar(45) NOT NULL,
`payeephone` varchar(45) NOT NULL,
`payeeemail` varchar(45) NOT NULL,
`paymentmethod_id` int(11) NOT NULL,
`paymentterms_id` int(11) NOT NULL,
`phone` varchar(45) NOT NULL,
`fax` varchar(45) NOT NULL,
`mobile` varchar(45) NOT NULL,
`bankaccount` varchar(45) NOT NULL,
`tax` varchar(45) NOT NULL,
`registration` varchar(45) NOT NULL,
`website` varchar(45) NOT NULL,
`branch_id` int(11) NOT NULL DEFAULT '2',
`comments` mediumtext NOT NULL,
`publiccomments` mediumtext NOT NULL,
`login_user` varchar(45) NOT NULL DEFAULT '',
`login_password` varchar(45) NOT NULL DEFAULT '',
`translicense` varchar(45) NOT NULL DEFAULT '',
`insurance` varchar(45) NOT NULL DEFAULT '',
`email` varchar(400) NOT NULL,
`swift` varchar(45) NOT NULL,
`area_id` int(2) DEFAULT '1',
`warning_comments` text,
`nextratedate` date DEFAULT NULL,
`ratesvaliduntil` date DEFAULT NULL,
`active_contract` int(1) DEFAULT '0',
`attachment_one` int(1) DEFAULT '0',
`freelance` int(1) DEFAULT '0',
`super_dispatch` tinyint(1) DEFAULT '0',
`garage` point NOT NULL,
`created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`createdBy` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index` (`supplier_id`),
KEY `FK_SP_OrganizationID` (`organization_id`),
KEY `FK_SP_HeadOfficeID` (`headoffice_id`),
KEY `FK_SP_CurrencyID` (`currency_id`),
KEY `FK_SP_LanguageID` (`language_id`),
KEY `FK_SP_TimezoneID` (`timezone_id`),
KEY `FK_SP_TypeID` (`type_id`),
KEY `FK_SP_StatusID` (`status_id`),
KEY `FK_SP_CommunicationID` (`communication_id`),
KEY `FK_SP_CountryID` (`country_id`),
KEY `FK_SP_PaymentMethodID` (`paymentmethod_id`),
KEY `FK_SP_PaymentTermsID` (`paymentterms_id`),
KEY `FK_SP_BranchID` (`branch_id`),
KEY `Index 15` (`phone`),
KEY `Index 16` (`mobile`),
CONSTRAINT `FK_SP_BranchID` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
CONSTRAINT `FK_SP_CommunicationID` FOREIGN KEY (`communication_id`) REFERENCES `communicationtypes` (`id`),
CONSTRAINT `FK_SP_CountryID` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`),
CONSTRAINT `FK_SP_CurrencyID` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
CONSTRAINT `FK_SP_HeadOfficeID` FOREIGN KEY (`headoffice_id`) REFERENCES `suppliers` (`supplier_id`),
CONSTRAINT `FK_SP_LanguageID` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`),
CONSTRAINT `FK_SP_OrganizationID` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`),
CONSTRAINT `FK_SP_PaymentMethodID` FOREIGN KEY (`paymentmethod_id`) REFERENCES `paymentmethods` (`id`),
CONSTRAINT `FK_SP_PaymentTermsID` FOREIGN KEY (`paymentterms_id`) REFERENCES `paymentterms` (`id`),
CONSTRAINT `FK_SP_StatusID` FOREIGN KEY (`status_id`) REFERENCES `entitystatus` (`id`),
CONSTRAINT `FK_SP_TimezoneID` FOREIGN KEY (`timezone_id`) REFERENCES `timezones` (`id`),
CONSTRAINT `FK_SP_TypeID` FOREIGN KEY (`type_id`) REFERENCES `suppliertypes` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3013 DEFAULT CHARSET=utf8$$
delimiter $$
CREATE TABLE `suppliercustom` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`Supplier_ID` int(11) NOT NULL,
`NightHour_BT` int(11) DEFAULT NULL,
`NightHourFrom_DT` time DEFAULT NULL,
`NightHourTo_DT` time DEFAULT NULL,
`MealHourFrom1_DT` time DEFAULT NULL,
`MealHourTo1_DT` time DEFAULT NULL,
`MealHourFrom2_DT` time DEFAULT NULL,
`MealHourTo2_DT` time DEFAULT NULL,
`MealRate_FT` decimal(10,2) DEFAULT NULL,
`WeekendDayFrom_IN` int(11) DEFAULT NULL,
`WeekendDayTo_IN` int(11) DEFAULT NULL,
`DisposalTimeMin_BT` int(11) NOT NULL DEFAULT '1',
`DisposalTimeMin_IN` int(11) DEFAULT NULL,
`DisposalDistanceHourMax_BT` int(11) NOT NULL,
`DisposalDistanceHourMax_IN` int(11) DEFAULT NULL,
`GarageX_FT` decimal(10,2) DEFAULT NULL,
`GarageY_FT` decimal(10,2) DEFAULT NULL,
`GarageTransferTimeMax_BT` int(11) NOT NULL,
`GarageTransferTimeMax_IN` int(11) DEFAULT NULL,
`GarageTransferDistanceMax_BT` int(11) NOT NULL,
`GarageTransferDistanceMax_IN` int(11) DEFAULT NULL,
`GenericSupplier_ID` int(11) NOT NULL,
`RateSupplier_ID` int(11) NOT NULL,
`SpecialDateRate_FT` decimal(10,2) DEFAULT NULL,
`Unlimited_BT` int(11) NOT NULL,
`RadialCenter_ID` int(11) DEFAULT NULL,
`AirportSurcharge_BT` int(11) NOT NULL,
`AirportSurchargeWaiting_FT` decimal(10,2) DEFAULT NULL,
`AirportSurchargePercent_FT` decimal(10,2) DEFAULT NULL,
`AirportDisposal_BT` int(11) NOT NULL,
`ExtraStandardRate_BT` int(11) NOT NULL,
`IntAirportSurcharge_BT` int(11) NOT NULL,
`IntAirportTerminal_FT` decimal(10,2) DEFAULT NULL,
`IntAirportPercent_FT` decimal(10,2) DEFAULT NULL,
`Gratuity_FT` decimal(10,2) DEFAULT NULL,
`Discount_FT` decimal(10,2) DEFAULT NULL,
`VAT_FT` decimal(10,2) DEFAULT NULL,
`Majorca_BT` int(11) NOT NULL,
`Compass_BT` int(11) NOT NULL,
`DeductDistance_FT` decimal(10,2) DEFAULT NULL,
`TransferTypes_VC` varchar(50) NOT NULL,
`ClosedTransfer_BT` int(11) NOT NULL,
`RadialTransfer_BT` int(11) NOT NULL,
`TransferHours_BT` int(11) NOT NULL,
`TransferHoursDistance_BT` int(11) NOT NULL,
`TransferDistance_BT` int(11) NOT NULL,
`BasicTransfer_BT` int(11) NOT NULL,
`DistanceLimit_BT` int(11) NOT NULL,
`DistanceLimit_FT` decimal(10,2) DEFAULT NULL,
`TimeLimit_BT` int(11) NOT NULL,
`TimeLimit_FT` decimal(10,2) DEFAULT NULL,
`DistanceB2BLimit_BT` int(11) NOT NULL,
`DistanceB2BLimit_FT` decimal(10,2) DEFAULT NULL,
`ProportionLimit_FT` decimal(10,2) DEFAULT NULL,
`MinimumRadiumLimit_FT` decimal(10,2) DEFAULT NULL,
`AsDirectedDistanceLimit_BT` int(11) NOT NULL,
`AsDirectedDistanceLimit_FT` decimal(10,2) DEFAULT NULL,
`TransferArrivalFee_BT` int(11) NOT NULL,
`ADTransferSurcharge_BT` int(11) NOT NULL,
`ADArrivalFee_BT` int(11) NOT NULL,
`ADDeductHours_FT` decimal(10,2) DEFAULT NULL,
`AsDirectedTimeLimit_BT` int(11) NOT NULL,
`AsDirectedTimeLimit_FT` decimal(10,2) DEFAULT NULL,
`DistanceMeasure_IN` int(11) NOT NULL,
`MinimumDistanceLimit_IN` decimal(10,2) NOT NULL,
`MinimumTimeLimit_IN` decimal(10,2) NOT NULL,
`MinimumSegment_IN` int(11) NOT NULL,
`MinimumGarage_IN` int(11) DEFAULT NULL,
`ChargeStructure_ID` int(11) NOT NULL,
`DistanceTime_BT` int(1) NOT NULL DEFAULT '0',
`TransferPricing_BT` int(1) NOT NULL DEFAULT '0',
`FlatRateTransfer_BT` int(1) NOT NULL DEFAULT '0',
`DirectedPricing_BT` int(1) NOT NULL DEFAULT '0',
`DriverMeals_BT` int(1) NOT NULL DEFAULT '0',
`Base2Base_BT` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `supplier_id` (`Supplier_ID`),
CONSTRAINT `FK_SPC_SupplierID` FOREIGN KEY (`Supplier_ID`) REFERENCES `suppliers` (`supplier_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3687 DEFAULT CHARSET=utf8$$
delimiter $$
CREATE TABLE `timezones` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`seq` varchar(5) NOT NULL,
`secdiff` int(10) NOT NULL,
`timezone_code` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=485 DEFAULT CHARSET=utf8$$
delimiter $$
CREATE TABLE `entitystatus` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`priority` int(11) DEFAULT NULL,
`priority_old` varbinary(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8$$
delimiter $$
CREATE TABLE `distancetypes` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`conversion` decimal(18,4) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8$$
delimiter $$
CREATE TABLE `currencies` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`code` varchar(10) DEFAULT NULL,
`external` varchar(50) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`symbol` varchar(50) DEFAULT NULL,
`csymbol` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `IX_CurrenciesCode` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=165 DEFAULT CHARSET=utf8$$
delimiter $$
CREATE TABLE `organizations` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`status_id` int(1) NOT NULL DEFAULT '1',
`name` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
`email` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
`phone` varchar(25) CHARACTER SET latin1 DEFAULT NULL,
`maincontact_name` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
`maincontact_position` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8$$
delimiter $$
CREATE TABLE `client_organization` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`client_id` int(11) NOT NULL,
`organization_id` int(11) NOT NULL,
`prioritat` int(11) NOT NULL DEFAULT '0',
`city_exclusive` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `FK_CLO_clientID` (`client_id`),
KEY `FK_CLO_organizationID` (`organization_id`),
CONSTRAINT `FK_CLO_organizationID` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18781 DEFAULT CHARSET=utf8$$
Description: I have a query that in most cases return 8 results but time to time returns 1 result. Isn't deterministic! And I am absolutely sure that no other application is writing to the database. select SQL_NO_CACHE supp.supplier_id, supp.name, supp.address1, supp.address2, supp.address3, supp.city_id, supp.county, supp.country_id, supp.postcode, supp.longitude, supp.latitude, supp.tax, supp.registration, supp.email, supp.website, supp.currency_id, supp.branch_id, supp.organization_id, co.city_exclusive, sc.NightHour_BT, HOUR(sc.NightHourFrom_DT) as NightHourFrom_IN, MINUTE(sc.NightHourFrom_DT) as NightMinuteFrom_IN, HOUR(sc.NightHourTo_DT) as NightHourTo_IN, MINUTE(sc.NightHourTo_DT) as NightMinuteTo_IN, HOUR(sc.MealHourFrom1_DT) as MealHourFrom1_IN, MINUTE(sc.MealHourFrom1_DT) as MealMinuteFrom1_IN, HOUR(sc.MealHourTo1_DT) as MealHourTo1_IN, MINUTE(sc.MealHourTo1_DT) as MealMinuteTo1_IN, HOUR(sc.MealHourFrom2_DT) as MealHourFrom2_IN, MINUTE(sc.MealHourFrom2_DT) as MealMinuteFrom2_IN, HOUR(sc.MealHourTo2_DT) as MealHourTo2_IN, MINUTE(sc.MealHourTo2_DT) as MealMinuteTo2_IN, sc.ClosedTransfer_BT, sc.RadialTransfer_BT, sc.TransferHours_BT, sc.TransferHoursDistance_BT, sc.TransferDistance_BT, sc.BasicTransfer_BT, sc.WeekendDayFrom_IN, sc.WeekendDayTo_IN, sc.DisposalTimeMin_IN, sc.DisposalDistanceHourMax_IN, sc.GarageTransferTimeMax_IN, sc.GarageTransferDistanceMax_IN, sc.GenericSupplier_ID, sc.RateSupplier_ID, sc.SpecialDateRate_FT, sc.Unlimited_BT, sc.RadialCenter_ID, sc.AirportSurcharge_BT, sc.AirportSurchargeWaiting_FT, sc.AirportSurchargePercent_FT, sc.AirportDisposal_BT, sc.IntAirportSurcharge_BT, sc.IntAirportTerminal_FT, sc.IntAirportPercent_FT, sc.Gratuity_FT, sc.Discount_FT, sc.VAT_FT, sc.Majorca_BT, sc.TransferArrivalFee_BT, sc.ADTransferSurcharge_BT, sc.ADDeductHours_FT, sc.ADArrivalFee_BT, sc.DistanceLimit_BT, sc.DistanceLimit_FT, sc.TimeLimit_BT, sc.TimeLimit_FT, sc.DistanceB2BLimit_BT, sc.DistanceB2BLimit_FT, sc.ProportionLimit_FT, MinimumRadiumLimit_FT, tz.name as Timezone_VC, supp.Status_ID, es.priority, sc.MinimumDistanceLimit_IN, sc.MinimumTimeLimit_IN, sc.MinimumSegment_IN, sc.DistanceMeasure_IN, dist.conversion, dist.name as NameMeasure_VC, sc.AsDirectedTimeLimit_FT, sc.MinimumGarage_IN, sc.ChargeStructure_ID, curr.symbol as currencyCode, (calculateDistance(supp.latitude, supp.longitude, 41.9794, 2.821426) + calculateDistance(41.91735, 3.206692, supp.latitude, supp.longitude)) as b2bDistance, co.prioritat as priority_organization from suppliers supp inner join suppliercustom sc ON (sc.supplier_id = supp.supplier_id and sc.Compass_BT = 1) inner join timezones tz ON (supp.TimeZone_ID = tz.id) inner join entitystatus es ON (es.id = supp.Status_ID) inner join distancetypes dist ON (dist.id = sc.DistanceMeasure_IN) inner join currencies curr ON (curr.id = supp.currency_id) inner join organizations org ON (supp.organization_id = org.id and org.status_id = 1) inner join client_organization co ON (co.organization_id = org.id) where co.client_id = 1501 and supp.supplier_id in ( 78 , 54 , 1918 , 1921 , 2820 , 2846 , 2850 , 2061 , 2373 , 1956 , 1959 ) order by priority_organization , priority , b2bDistance asc; How to repeat: delimiter $$ CREATE DEFINER=`root`@`localhost` FUNCTION `calculateDistance`(Latitude1 double, Longitude1 double, Latitude2 double, Longitude2 double) RETURNS double DETERMINISTIC BEGIN return 6378100 * ACOS(COS(RADIANS(Latitude1)) * COS(RADIANS(Latitude2)) * COS(RADIANS(Longitude1) - RADIANS(Longitude2)) + SIN(RADIANS(Latitude1)) * SIN(RADIANS(Latitude2))); END$$ delimiter $$ CREATE TABLE `suppliers` ( `id` int(11) NOT NULL AUTO_INCREMENT, `organization_id` int(10) NOT NULL DEFAULT '1', `supplier_id` int(11) NOT NULL, `suppliercode` varchar(45) NOT NULL, `groupcode` varchar(45) NOT NULL, `external` varchar(45) NOT NULL, `headoffice_id` int(11) DEFAULT NULL, `currency_id` int(11) DEFAULT NULL, `language_id` int(11) DEFAULT NULL, `timezone_id` int(11) DEFAULT NULL, `type_id` int(11) NOT NULL, `status_id` int(11) NOT NULL, `communication_id` int(11) DEFAULT NULL, `name` varchar(100) NOT NULL, `fileas` varchar(100) NOT NULL, `address1` varchar(100) NOT NULL, `address2` varchar(100) NOT NULL, `address3` varchar(100) NOT NULL, `city_id` int(11) NOT NULL, `county` varchar(45) NOT NULL, `country_id` int(11) DEFAULT NULL, `postcode` varchar(10) NOT NULL, `longitude` varchar(45) NOT NULL, `latitude` varchar(45) NOT NULL, `location` varchar(45) NOT NULL DEFAULT '', `payeename` varchar(45) NOT NULL, `payeephone` varchar(45) NOT NULL, `payeeemail` varchar(45) NOT NULL, `paymentmethod_id` int(11) NOT NULL, `paymentterms_id` int(11) NOT NULL, `phone` varchar(45) NOT NULL, `fax` varchar(45) NOT NULL, `mobile` varchar(45) NOT NULL, `bankaccount` varchar(45) NOT NULL, `tax` varchar(45) NOT NULL, `registration` varchar(45) NOT NULL, `website` varchar(45) NOT NULL, `branch_id` int(11) NOT NULL DEFAULT '2', `comments` mediumtext NOT NULL, `publiccomments` mediumtext NOT NULL, `login_user` varchar(45) NOT NULL DEFAULT '', `login_password` varchar(45) NOT NULL DEFAULT '', `translicense` varchar(45) NOT NULL DEFAULT '', `insurance` varchar(45) NOT NULL DEFAULT '', `email` varchar(400) NOT NULL, `swift` varchar(45) NOT NULL, `area_id` int(2) DEFAULT '1', `warning_comments` text, `nextratedate` date DEFAULT NULL, `ratesvaliduntil` date DEFAULT NULL, `active_contract` int(1) DEFAULT '0', `attachment_one` int(1) DEFAULT '0', `freelance` int(1) DEFAULT '0', `super_dispatch` tinyint(1) DEFAULT '0', `garage` point NOT NULL, `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `createdBy` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index` (`supplier_id`), KEY `FK_SP_OrganizationID` (`organization_id`), KEY `FK_SP_HeadOfficeID` (`headoffice_id`), KEY `FK_SP_CurrencyID` (`currency_id`), KEY `FK_SP_LanguageID` (`language_id`), KEY `FK_SP_TimezoneID` (`timezone_id`), KEY `FK_SP_TypeID` (`type_id`), KEY `FK_SP_StatusID` (`status_id`), KEY `FK_SP_CommunicationID` (`communication_id`), KEY `FK_SP_CountryID` (`country_id`), KEY `FK_SP_PaymentMethodID` (`paymentmethod_id`), KEY `FK_SP_PaymentTermsID` (`paymentterms_id`), KEY `FK_SP_BranchID` (`branch_id`), KEY `Index 15` (`phone`), KEY `Index 16` (`mobile`), CONSTRAINT `FK_SP_BranchID` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`), CONSTRAINT `FK_SP_CommunicationID` FOREIGN KEY (`communication_id`) REFERENCES `communicationtypes` (`id`), CONSTRAINT `FK_SP_CountryID` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`), CONSTRAINT `FK_SP_CurrencyID` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`), CONSTRAINT `FK_SP_HeadOfficeID` FOREIGN KEY (`headoffice_id`) REFERENCES `suppliers` (`supplier_id`), CONSTRAINT `FK_SP_LanguageID` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`), CONSTRAINT `FK_SP_OrganizationID` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`), CONSTRAINT `FK_SP_PaymentMethodID` FOREIGN KEY (`paymentmethod_id`) REFERENCES `paymentmethods` (`id`), CONSTRAINT `FK_SP_PaymentTermsID` FOREIGN KEY (`paymentterms_id`) REFERENCES `paymentterms` (`id`), CONSTRAINT `FK_SP_StatusID` FOREIGN KEY (`status_id`) REFERENCES `entitystatus` (`id`), CONSTRAINT `FK_SP_TimezoneID` FOREIGN KEY (`timezone_id`) REFERENCES `timezones` (`id`), CONSTRAINT `FK_SP_TypeID` FOREIGN KEY (`type_id`) REFERENCES `suppliertypes` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3013 DEFAULT CHARSET=utf8$$ delimiter $$ CREATE TABLE `suppliercustom` ( `id` int(11) NOT NULL AUTO_INCREMENT, `Supplier_ID` int(11) NOT NULL, `NightHour_BT` int(11) DEFAULT NULL, `NightHourFrom_DT` time DEFAULT NULL, `NightHourTo_DT` time DEFAULT NULL, `MealHourFrom1_DT` time DEFAULT NULL, `MealHourTo1_DT` time DEFAULT NULL, `MealHourFrom2_DT` time DEFAULT NULL, `MealHourTo2_DT` time DEFAULT NULL, `MealRate_FT` decimal(10,2) DEFAULT NULL, `WeekendDayFrom_IN` int(11) DEFAULT NULL, `WeekendDayTo_IN` int(11) DEFAULT NULL, `DisposalTimeMin_BT` int(11) NOT NULL DEFAULT '1', `DisposalTimeMin_IN` int(11) DEFAULT NULL, `DisposalDistanceHourMax_BT` int(11) NOT NULL, `DisposalDistanceHourMax_IN` int(11) DEFAULT NULL, `GarageX_FT` decimal(10,2) DEFAULT NULL, `GarageY_FT` decimal(10,2) DEFAULT NULL, `GarageTransferTimeMax_BT` int(11) NOT NULL, `GarageTransferTimeMax_IN` int(11) DEFAULT NULL, `GarageTransferDistanceMax_BT` int(11) NOT NULL, `GarageTransferDistanceMax_IN` int(11) DEFAULT NULL, `GenericSupplier_ID` int(11) NOT NULL, `RateSupplier_ID` int(11) NOT NULL, `SpecialDateRate_FT` decimal(10,2) DEFAULT NULL, `Unlimited_BT` int(11) NOT NULL, `RadialCenter_ID` int(11) DEFAULT NULL, `AirportSurcharge_BT` int(11) NOT NULL, `AirportSurchargeWaiting_FT` decimal(10,2) DEFAULT NULL, `AirportSurchargePercent_FT` decimal(10,2) DEFAULT NULL, `AirportDisposal_BT` int(11) NOT NULL, `ExtraStandardRate_BT` int(11) NOT NULL, `IntAirportSurcharge_BT` int(11) NOT NULL, `IntAirportTerminal_FT` decimal(10,2) DEFAULT NULL, `IntAirportPercent_FT` decimal(10,2) DEFAULT NULL, `Gratuity_FT` decimal(10,2) DEFAULT NULL, `Discount_FT` decimal(10,2) DEFAULT NULL, `VAT_FT` decimal(10,2) DEFAULT NULL, `Majorca_BT` int(11) NOT NULL, `Compass_BT` int(11) NOT NULL, `DeductDistance_FT` decimal(10,2) DEFAULT NULL, `TransferTypes_VC` varchar(50) NOT NULL, `ClosedTransfer_BT` int(11) NOT NULL, `RadialTransfer_BT` int(11) NOT NULL, `TransferHours_BT` int(11) NOT NULL, `TransferHoursDistance_BT` int(11) NOT NULL, `TransferDistance_BT` int(11) NOT NULL, `BasicTransfer_BT` int(11) NOT NULL, `DistanceLimit_BT` int(11) NOT NULL, `DistanceLimit_FT` decimal(10,2) DEFAULT NULL, `TimeLimit_BT` int(11) NOT NULL, `TimeLimit_FT` decimal(10,2) DEFAULT NULL, `DistanceB2BLimit_BT` int(11) NOT NULL, `DistanceB2BLimit_FT` decimal(10,2) DEFAULT NULL, `ProportionLimit_FT` decimal(10,2) DEFAULT NULL, `MinimumRadiumLimit_FT` decimal(10,2) DEFAULT NULL, `AsDirectedDistanceLimit_BT` int(11) NOT NULL, `AsDirectedDistanceLimit_FT` decimal(10,2) DEFAULT NULL, `TransferArrivalFee_BT` int(11) NOT NULL, `ADTransferSurcharge_BT` int(11) NOT NULL, `ADArrivalFee_BT` int(11) NOT NULL, `ADDeductHours_FT` decimal(10,2) DEFAULT NULL, `AsDirectedTimeLimit_BT` int(11) NOT NULL, `AsDirectedTimeLimit_FT` decimal(10,2) DEFAULT NULL, `DistanceMeasure_IN` int(11) NOT NULL, `MinimumDistanceLimit_IN` decimal(10,2) NOT NULL, `MinimumTimeLimit_IN` decimal(10,2) NOT NULL, `MinimumSegment_IN` int(11) NOT NULL, `MinimumGarage_IN` int(11) DEFAULT NULL, `ChargeStructure_ID` int(11) NOT NULL, `DistanceTime_BT` int(1) NOT NULL DEFAULT '0', `TransferPricing_BT` int(1) NOT NULL DEFAULT '0', `FlatRateTransfer_BT` int(1) NOT NULL DEFAULT '0', `DirectedPricing_BT` int(1) NOT NULL DEFAULT '0', `DriverMeals_BT` int(1) NOT NULL DEFAULT '0', `Base2Base_BT` int(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `supplier_id` (`Supplier_ID`), CONSTRAINT `FK_SPC_SupplierID` FOREIGN KEY (`Supplier_ID`) REFERENCES `suppliers` (`supplier_id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=3687 DEFAULT CHARSET=utf8$$ delimiter $$ CREATE TABLE `timezones` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `seq` varchar(5) NOT NULL, `secdiff` int(10) NOT NULL, `timezone_code` varchar(45) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=485 DEFAULT CHARSET=utf8$$ delimiter $$ CREATE TABLE `entitystatus` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `priority` int(11) DEFAULT NULL, `priority_old` varbinary(45) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8$$ delimiter $$ CREATE TABLE `distancetypes` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `conversion` decimal(18,4) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8$$ delimiter $$ CREATE TABLE `currencies` ( `id` int(10) NOT NULL AUTO_INCREMENT, `code` varchar(10) DEFAULT NULL, `external` varchar(50) DEFAULT NULL, `name` varchar(50) DEFAULT NULL, `symbol` varchar(50) DEFAULT NULL, `csymbol` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `IX_CurrenciesCode` (`code`) ) ENGINE=InnoDB AUTO_INCREMENT=165 DEFAULT CHARSET=utf8$$ delimiter $$ CREATE TABLE `organizations` ( `id` int(10) NOT NULL AUTO_INCREMENT, `status_id` int(1) NOT NULL DEFAULT '1', `name` varchar(150) CHARACTER SET latin1 DEFAULT NULL, `email` varchar(150) CHARACTER SET latin1 DEFAULT NULL, `phone` varchar(25) CHARACTER SET latin1 DEFAULT NULL, `maincontact_name` varchar(100) CHARACTER SET latin1 DEFAULT NULL, `maincontact_position` varchar(100) CHARACTER SET latin1 DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8$$ delimiter $$ CREATE TABLE `client_organization` ( `id` int(11) NOT NULL AUTO_INCREMENT, `client_id` int(11) NOT NULL, `organization_id` int(11) NOT NULL, `prioritat` int(11) NOT NULL DEFAULT '0', `city_exclusive` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `FK_CLO_clientID` (`client_id`), KEY `FK_CLO_organizationID` (`organization_id`), CONSTRAINT `FK_CLO_organizationID` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=18781 DEFAULT CHARSET=utf8$$