Início / user-24328

Van's questions

Martin Hope
Van
Asked: 2014-01-24 09:07:00 +0800 CST
  • 1

Windows Server 2008 R2 x64
Servidor MySQL 5.6.14 x64

Alguém mais está tendo um problema em que algumas tabelas são marcadas como travadas em um servidor Microsoft Windows? Temos alguns servidores Windows (não por opção) e meia dúzia de servidores Linux rodando MySQL. Por alguma razão, as tabelas nos servidores Windows são marcadas como "travadas" de vez em quando. Nunca tivemos esses problemas em nossos servidores Linux (Ubuntu).

2014-01-23 10:11:42 1868 [ERROR] MySQL: Table '.\elite_prod\ffcont' is marked as crashed and should be repaired
2014-01-23 10:11:42 1868 [ERROR] MySQL: Table '.\elite_prod\bldr' is marked as crashed and should be repaired
2014-01-23 10:11:43 1868 [ERROR] MySQL: Table '.\elite_prod\frtfwd' is marked as crashed and should be repaired
2014-01-23 10:11:44 1868 [ERROR] MySQL: Table '.\elite_prod\histry' is marked as crashed and should be repaired

Parece que temos que lidar com esse problema a cada duas semanas. Muito frustrante.

mysql windows
Martin Hope
Van
Asked: 2013-11-13 11:18:59 +0800 CST
  • 0

SO: ubuntu 12.04 amd64
DB: MySQL 5.6.14-community amd64

Não consigo entender por que essa consulta está usando o índice errado e fazendo uma verificação completa da tabela na tabela ar_batch (ab). Talvez alguém possa ver por quê!?

Consulta

SELECT
  DATE_FORMAT(ab.deposit_date, '%m/%d/%Y') AS paymentDate,
  FORMAT(pc.received_amt, 2) AS paymentAmount,
  pc.check_no AS paymentReference
FROM
  TRANSACTION ar 
JOIN payments py 
  ON (
    ar.transaction_id = py.ar_tranaction_id 
    AND py.ar_tranaction_id NOT LIKE '%AC'
  ) 
JOIN ar_batch ab
  ON (ab.batch_id = py.batch_id)
JOIN payment_checks pc 
  ON (py.payment_check_id = pc.id) 
WHERE ar.transaction_type = 'AR' 
AND ar.cust_no = 'SEACOR0003' 
ORDER BY py.id DESC 
LIMIT 1;

EXPLICAR ESTENDIDO

"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "filtered"  "Extra"
"1" "SIMPLE"    "ab"    "index" "PRIMARY"   "Deposit_date_idx"  "4" \N  "34836" "100.00"    "Using index; Using temporary; Using filesort"
"1" "SIMPLE"    "py"    "ref"   "Batch_Id,Ar_Tranaction_Id,Payment_Check_Id"    "Batch_Id"  "5" "eci_lw_prod.ab.Batch_id"   "10"    "100.00"    "Using where"
"1" "SIMPLE"    "ar"    "eq_ref"    "PRIMARY,cust_no_idx,Trasns_type_idx"   "PRIMARY"   "4" "eci_lw_prod.py.Ar_Tranaction_Id"   "1" "100.00"    "Using where"
"1" "SIMPLE"    "pc"    "eq_ref"    "PRIMARY"   "PRIMARY"   "4" "eci_lw_prod.py.Payment_Check_Id"   "1" "100.00"    \N

Abaixo estão os CREATE TABLEs....

CREATE TABLE `ar_batch` (
`Batch_id` int(11) NOT NULL AUTO_INCREMENT,
`User_id` varchar(30) NOT NULL,
`Date` datetime NOT NULL,
`Deposit_date` date DEFAULT NULL,
`Total_Amount` decimal(10,2) DEFAULT NULL,
`Bank_Account` varchar(20) DEFAULT NULL,
`Notes` varchar(200) DEFAULT NULL,
`Status` varchar(15) NOT NULL,
`Reconciled` varchar(1) DEFAULT NULL,
`Applied_Amount` decimal(10,2) DEFAULT NULL,
`Balance_Amount` decimal(10,2) NOT NULL,
`On_Acct_Amount` decimal(10,2) DEFAULT NULL,
`Prepay_Amount` decimal(10,2) DEFAULT NULL,
`BankAccount_Desc` varchar(200) DEFAULT NULL,
`Gl_Account` varchar(50) DEFAULT NULL,
`batch_type` varchar(1) DEFAULT NULL,
`Adjust_Amount` decimal(10,2) DEFAULT '0.00',
`Using_By` int(11) DEFAULT NULL,
`direct_gl_account` tinyint(1) DEFAULT '0',
 PRIMARY KEY (`Batch_id`),
 KEY `Deposit_date_idx` (`Deposit_date`),
 KEY `status_idx` (`Status`),
 KEY `batch_type_idx` (`batch_type`),
 KEY `applied_amount_idx` (`Applied_Amount`)
) ENGINE=InnoDB AUTO_INCREMENT=44953 DEFAULT CHARSET=latin1;


CREATE TABLE `payments` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Batch_Id` int(11) DEFAULT NULL,
`Batch_Date` date DEFAULT NULL,
`Cust_No` varchar(10) DEFAULT NULL,
`Check_Date` date DEFAULT NULL,
`Payment_Amt` decimal(10,2) DEFAULT NULL,
`Payment_Type` varchar(10) DEFAULT NULL,
`Charge_Code` varchar(10) DEFAULT NULL,
`Ar_Tranaction_Id` varchar(15) DEFAULT NULL,
`invoice_no` varchar(100) DEFAULT NULL,
`Bill_Ladding_No` varchar(30) DEFAULT NULL,
`Adjustment_Amt` decimal(10,2) DEFAULT NULL,
`Check_no` varchar(30) DEFAULT NULL,
`adjustment_date` date DEFAULT NULL,
`userName` varchar(20) DEFAULT NULL,
`Payment_Check_Id` int(12) DEFAULT NULL,
`notes` varchar(200) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `Batch_Id` (`Batch_Id`),
KEY `Cust_No` (`Cust_No`),
KEY `Ar_Tranaction_Id` (`Ar_Tranaction_Id`),
KEY `Payment_Check_Id` (`Payment_Check_Id`)
) ENGINE=InnoDB AUTO_INCREMENT=4118743 DEFAULT CHARSET=latin1;

CREATE TABLE `transaction` (
 `Transaction_ID` int(10) NOT NULL AUTO_INCREMENT,
 `Bill_Ladding_No` varchar(42) DEFAULT NULL,
 `Charge_Code` varchar(20) DEFAULT NULL,
 `Transaction_date` date DEFAULT NULL,
 `posted_date` datetime DEFAULT NULL,
 `GL_account_number` varchar(20) DEFAULT NULL,
 `Transaction_amt` decimal(10,2) DEFAULT NULL,
 `Currency_code` varchar(100) DEFAULT NULL,
 `invoice_number` varchar(100) DEFAULT NULL,
 `Cheque_number` varchar(150) DEFAULT NULL,
 `Subledger_Source_code` varchar(20) DEFAULT NULL,
 `Journal_Entry_number` varchar(20) DEFAULT NULL,
 `Line_Item_number` varchar(20) DEFAULT NULL,
 `cust_name` varchar(60) DEFAULT NULL,
 `cust_no` varchar(30) DEFAULT NULL,
 `Transaction_type` varchar(10) DEFAULT NULL,
 `Balance` decimal(10,2) DEFAULT NULL,
 `BL_Terms` varchar(30) DEFAULT NULL,
 `Sub_House_BL` varchar(30) DEFAULT NULL,
 `Voyage_No` varchar(30) DEFAULT NULL,
 `Container_No` varchar(500) DEFAULT NULL,
 `Master_BL` varchar(30) DEFAULT NULL,
 `Vessel_No` varchar(30) DEFAULT NULL,
 `Status` varchar(20) DEFAULT NULL,
 `Due_Date` date DEFAULT NULL,
 `Bill_to` char(1) DEFAULT NULL,
 `Customer_Reference_No` varchar(500) DEFAULT NULL,
 `Originating_Terminal` varchar(50) DEFAULT NULL,
 `Destination` varchar(60) DEFAULT NULL,
 `Saling_date` date DEFAULT NULL,
 `Fwd_name` varchar(50) DEFAULT NULL,
 `Fwd_no` varchar(10) DEFAULT NULL,
 `Cons_name` varchar(100) DEFAULT NULL,
 `Cons_no` varchar(10) DEFAULT NULL,
 `Third_Pty_name` varchar(50) DEFAULT NULL,
 `Third_Pty_no` varchar(10) DEFAULT NULL,
 `Agent_name` varchar(50) DEFAULT NULL,
 `Agent_no` varchar(10) DEFAULT NULL,
 `Credit_Hold` varchar(20) DEFAULT NULL,
 `correction_flag` varchar(4) DEFAULT NULL,
 `Credit_Terms` int(4) DEFAULT NULL,
 `bank_no` varchar(10) DEFAULT NULL,
 `bank_name` varchar(50) DEFAULT NULL,
 `bank_account_no` varchar(30) DEFAULT NULL,
 `check_date` date DEFAULT NULL,
 `cleared` varchar(1) DEFAULT NULL,
 `cleared_date` date DEFAULT NULL,
 `reconciled` varchar(1) DEFAULT NULL,
 `reconciled_date` date DEFAULT NULL,
 `confirmation_number` varchar(30) DEFAULT NULL,
 `void` varchar(1) DEFAULT NULL,
 `void_date` date DEFAULT NULL,
 `reprint` varchar(1) DEFAULT NULL,
 `reprint_date` date DEFAULT NULL,
 `Balance_In_Process` decimal(10,2) DEFAULT NULL,
 `shipper_no` varchar(20) DEFAULT NULL,
 `shipper_name` varchar(50) DEFAULT NULL,
 `booking_no` varchar(30) DEFAULT NULL,
 `quotation_no` varchar(20) DEFAULT NULL,
 `Ap_Batch_Id` int(11) DEFAULT NULL,
 `Ar_Batch_Id` int(11) DEFAULT NULL,
 `pay_method` varchar(20) DEFAULT NULL,
 `drcpt` varchar(30) DEFAULT NULL,
 `Created_On` datetime DEFAULT NULL,
 `Created_By` int(6) DEFAULT NULL,
 `Updated_On` datetime DEFAULT NULL,
 `Updated_By` int(6) DEFAULT NULL,
 `Paid_By` int(6) DEFAULT NULL,
 `Approved_By` int(6) DEFAULT NULL,
 `Closed_Date` date DEFAULT NULL,
 `ach_batch_sequence` int(7) DEFAULT NULL,
 `manifest_flag` varchar(4) DEFAULT NULL,
 `correction_notice` varchar(10) DEFAULT NULL,
 `owner` int(6) DEFAULT NULL,
 `paid_on` datetime DEFAULT NULL,
 `emailed` tinyint(1) DEFAULT '0',
 `seal_no` varchar(1000) DEFAULT NULL,
 `eta` date DEFAULT NULL,
 `vessel_name` varchar(500) DEFAULT NULL,
 `steam_ship_line` varchar(35) DEFAULT NULL,
 `removed_from_hold` tinyint(1) NOT NULL DEFAULT '0',
 `search_invoice_number` varchar(100) DEFAULT NULL,
 `ap_invoice_id` int(11) DEFAULT NULL,
 `ap_invoice_status` varchar(30) DEFAULT NULL,
 `ap_invoice_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`Transaction_ID`),
KEY `cust_name_idx` (`cust_name`),
KEY `cust_no_idx` (`cust_no`),
KEY `Trasns_type_idx` (`Transaction_type`),
KEY `bill_ladding_idx` (`Bill_Ladding_No`),
KEY `Invoice_number` (`invoice_number`),
KEY `Status_IDX` (`Status`),
KEY `check_number_idx` (`Cheque_number`),
KEY `ap_batch_id_idx` (`Ap_Batch_Id`),
KEY `cleared_idx` (`cleared`),
KEY `reconciled_idx` (`reconciled`),
KEY `void_idx` (`void`),
KEY `void_date_idx` (`void_date`),
KEY `pay_method_idx` (`pay_method`),
KEY `drcpt_idx` (`drcpt`),
KEY `balance_idx` (`Balance`),
KEY `voyage_no_idx` (`Voyage_No`),
KEY `charge_code_idx` (`Charge_Code`),
KEY `container_no_idx` (`Container_No`),
KEY `transaction_amt_idx` (`Transaction_amt`),
KEY `balance_in_process_idx` (`Balance_In_Process`),
KEY `search_invoice_number_idx` (`search_invoice_number`),
KEY `ap_invoice_id_idx` (`ap_invoice_id`),
KEY `ap_invoice_status_idx` (`ap_invoice_status`),
KEY `created_by_idx` (`Created_By`)
) ENGINE=InnoDB AUTO_INCREMENT=1251542 DEFAULT CHARSET=latin1;

CREATE TABLE `payment_checks` (
 `Id` int(11) NOT NULL AUTO_INCREMENT,
 `Batch_id` int(11) DEFAULT NULL,
 `Batch_date` date DEFAULT NULL,
 `Cust_id` varchar(10) DEFAULT NULL,
 `Check_no` varchar(20) DEFAULT NULL,
 `Check_date` date DEFAULT NULL,
 `Received_Amt` decimal(10,2) DEFAULT NULL,
 `applied_amount` double(10,2) DEFAULT NULL,
 `On_Acct_In` decimal(10,2) DEFAULT '0.00',
 `Pre_Pmt_In` decimal(10,2) DEFAULT '0.00',
 `Invoice_Out` decimal(10,2) DEFAULT '0.00',
 `On_Acct_Out` decimal(10,2) DEFAULT '0.00',
 `Pre_Pmt_Out` decimal(10,2) DEFAULT '0.00',
 `Charge_Code_Out` decimal(10,2) DEFAULT '0.00',
 `Invoice_In` decimal(10,2) DEFAULT '0.00',
 `Adjust_Amt` decimal(10,2) DEFAULT '0.00',
 PRIMARY KEY (`Id`),
 KEY `Batch_id` (`Batch_id`),
 KEY `Check_no` (`Check_no`),
 KEY `Cust_id` (`Cust_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=203272 DEFAULT CHARSET=latin1;
mysql-5.6