| Bug #29231 | Try to execute 3000 insert queries, but executed only 960. | ||
|---|---|---|---|
| Submitted: | 20 Jun 2007 6:02 | Modified: | 5 Oct 2007 11:01 |
| Reporter: | Andrey Wolk | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S1 (Critical) |
| Version: | 5.0.41 | OS: | Windows (2003 Enterprise sp2) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | 2003, MySQL, windows | ||
[20 Jun 2007 6:02]
Andrey Wolk
[9 Jul 2007 20:52]
Andrey Wolk
In Addition some information: I tested this problem and has found that insert queries are executing in own _createthread! So...This can cause my problem. Now i think, that ODBC-connector is NOT a reason. The reason is the program's (A) method of using databases. Now i have a source code of the program with exclusive copyrights and can embed a time limits before queries. But what is limits? Why MS Access work fine? Is MS Access more stable for this operations? How can i solve the problem without the time limits in program code?
[16 Jul 2007 9:55]
Susanne Ebrecht
Hi Andrey, thank you for writing a bug report. We need some more informations, to reproduce you scenario. How many transactions do you use for the inserts? Every single insert is a single transaction. Besides you are using a fuction or procedure to do this or you switch autocommit to off and say "commit" at the end of all. Than you only have one transaction. Inserting so many rows at the same time needs lots of time. Are there still Indexe on the table? Insert are much faster, when you don't have Indexe because, when you use Indexe, besides the data storage in the table always the Index must be reorganised too. It is recommendend to don't have any Index on the table when you want to make so many inserts at the same time. Even no primary key or auto_increment col, because these causes automatically an Index. If you have Indexe on the table, please drop them all and try it again. If you need Indexe for select after the inserts, you can create them after the data are stored in the database. Also it could be necessary, that you show us your database tuning. 2GB Ram is not much for making 1 million inserts at the same time. Regards, Susanne
[16 Jul 2007 10:35]
Andrey Wolk
Susanne: How many transactions do you use for the inserts?
Andrey : Program not use transactions, it use scenario:
////////////////////////////////
//at starting the program (starting service)
mysql_real_connect(...);
//
/////every day at 00:00
mysql_select_db(dbname);
for (1 to ~3000)
{
//calculates
//..
_beginthread(thread with calculate result);
}
/////
thread( LPVOID lParam )
{
query_result = mysql_real_query(mysqlconnect,strQuery,strlen(strQuery));
_endthread();
}
// at stopping service
mysql_close();
/////////////////////////////////
strQuery is INSERT INTO users_changes ( id, uname, utcdate, textdate, moneywas, moneynew, moneychange, infocode, managername, paycomment ) VALUES ( null , '%s', %s, '%s', %s, %s, %s, %s, '%s', '%s' );
Susanne: Inserting so many rows at the same time needs lots of time. Are there still Indexe on the table?
Andrey: Yes. There is a Indexes in the table. Also now i have 2'898'341 rows in the table.
Susanne: Insert are much faster, when you don't have Indexe because, when you use Indexe, besides the data storage in the table always the Index must be reorganised too.
It is recommendend to don't have any Index on the table when you want to make so many inserts at the same time. Even no primary key or auto_increment col, because these causes automatically an Index.
If you have Indexe on the table, please drop them all and try it again. If you need Indexe for select after the inserts, you can create them after the data are stored in the database.
Andrey: It is a billing system. I can't drop columns, i can't stop a server, i cant do anything without monthly prepare. Infrequent at 6 o'clock we turn a server for preventions. I can't drop indexes, because my clients will waiting statistics in a web site to very long.
Susanne: Also it could be necessary, that you show us your database tuning. 2GB Ram is not much for making 1 million inserts at the same time.
Andrey: How can i show you my database tuning?
1 million inserts newer happen.
In 2001-2006 program used MS Access. In 6 June 2007 i wrote converter. This converter took lines to the MySQL table. But it was in one thread for all rows. Summary 3000 tables in billing's database have a 6 millions lines. In the migration day I installed Mysql to a ramdisk and waited 35 mins for all convertions. It was successfuly done. I replaced Mysql to physical disk and turn on billing service. But at 00:00 we seen 960 rows, not 3000.
Trable info:
CREATE TABLE users_changes ( id double unsigned not null auto_increment, uname varchar(20), utcdate int, textdate varchar(20), moneywas float, moneynew float, moneychange float, infocode int, managername varchar(20), paycomment varchar(255), PRIMARY KEY(`id`), UNIQUE(`id`), INDEX(`id`), FULLTEXT KEY `uname` (`uname`) ) ENGINE=MyISAM
[16 Jul 2007 11:33]
Susanne Ebrecht
Hi Andrey again, FYI: when you not explicit set the autocommit to off, every Insert is a own transactions automatically. But you didn't tell before, that you use threads. How many connections do you use? Same as threads? The database tuning is, what you have configured in your my.cnf. Maybe I don't understand your problem. That is, what I understand: You have a database and a software written in XY. You use ODBC to connect to the database. Your software add (inserts) until 3000 rows to one table in a periodical sequence using threads. At the same time the software user needs to select data from these table. So you need the indexe and you accept that the insert need much more time. For the inserts: undeclared is, how many connections are used. After all threads are done you count the inserted rows, and the number is always 960 instead of 3000. The connection timed out, because it need to wait too long because there is too much traffic on the database or the network is too busy? That is the error message or? And your hardware has 2GB Ram (it is a question of your tuning, how many RAM you are using). And you use Windows for both, MySQL and software. Is this right? Did I understand you in the right way? What is the system load saying during this processes? Is the software and MySQL using the same machine? Also: please try it with the newest version of ODBC 3.51. It is recommended to use ODBC 3.51 for windows. Susanne
[16 Jul 2007 12:12]
Andrey Wolk
Hot to set autocommit to off? I have changed the program. Now program not use ODBC driver, it use MySql C API. But problem still is present. I have placed Sleep(3 sec) every 50 inserts in night cycle. Now all ok. But it is temporary solution. In the past with ODBC the server have execute 3000 inserts in a 4 second, but now it require 2-3 minutes. It is isunacceptable decision. I use one connection for all threads and for all queries. Mysql monitor show that only one connection at one time. I need only insert and sometime select queries. No update queries use. It is a log table. The program use only insert queries. I need Indexes because select queries executes too long. Connection never is broken to night, never timed out. Yes, I use Windows for both, MySQL and software. Minimum traffic, may be 300-400 kb for all night process. No errors, no broken connections, all log files is empty. Task manager say 1'549'000 MB Memory is available on the server now! Process is short. I can't view system load. I tried to see, but loads has not seen. Current tunning: # MySQL Server Instance Configuration File [client] port=3306 [mysql] default-character-set=cp1251 character-sets-dir="c:/Program Files/MySQL/MySQL Server 5.0/Share/Charsets" [mysqld] port=3306 basedir="c:/Program Files/MySQL/MySQL Server 5.0/" datadir="c:/Program Files/MySQL/MySQL Server 5.0/Data/" default-character-set=cp1251 default-storage-engine=myisam sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" max_connections=4000 query_cache_size=256M table_cache=1520 tmp_table_size=30M thread_cache_size=38 myisam_max_sort_file_size=32M myisam_max_extra_sort_file_size=16M myisam_sort_buffer_size=30M key_buffer_size=256M read_buffer_size=64K read_rnd_buffer_size=256K sort_buffer_size=16M innodb_additional_mem_pool_size=6M innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=3M innodb_buffer_pool_size=249M innodb_log_file_size=50M innodb_thread_concurrency=18 skip-concurrent-insert log-warnings delayed_insert_limit=5000 max_allowed_packet=16M [server] skip-character-set-client-handshake init-connect='set NAMES cp1251'
[16 Jul 2007 16:31]
Susanne Ebrecht
Hi Andrey again, 2-3 minutes for 3000 inserts into a table with round about 3 millions rows is a relativly normal time. You have to consider, that your indexes also have round about 3 million entries and that these always must be reordered for every insert you will do. Of course it was faster in the past because the table didn't have had so many rows. You can try to tune your system more effectivly. Set down max_connections, set the buffer and cash size more high and stuff like this. Please look to the documentation what is possible. I really can't see a bug in the ODBC that occurs your problems. Susanne
[16 Jul 2007 16:43]
Andrey Wolk
Now I know that problem not in ODBC, because with C API it problem is present. I am going to write program, which will emulate the problem. 2-3 days and you'll get emulator. I think we must change bug Category from "Connector/ODBC" to "Mysql Core" Thank you very much, dear Susanne.
[16 Jul 2007 17:03]
Tonci Grgin
Andrey, we are waiting on your test case. In the meantime, please do adjust your buffers so that you do not allocate huge amounts of RAM (as you don't have it). This goes especially for MAX_CONNECTIONS that should go down drastically (try with 40 for a start).
[17 Jul 2007 12:58]
Andrey Wolk
Thank you for waiting for. I have reproduced the bug. I wrote the emulator. It generate 1 thread for every insert. You can see it in the Task Manager if have time to, or use MS Perfomance Monitor. http://www.raswinbilling.ru/getfile.php?distrib=bug29231emulator.zip (Also i can show the source code if you need.) You must create database before you run the emulator. Emulator will create table by itself. Usage: emulator.exe host user pass dbname threadscount Examp: emulator.exe 127.0.0.1 root pass dbname 5000
[18 Jul 2007 10:19]
Susanne Ebrecht
Hi Andrey, please show us the source code too. Susanne
[18 Jul 2007 10:30]
Tonci Grgin
Andrey, please provide sources as we have no use for black-box you sent. If you're concerned about privacy, you can add files with "Private" flag so that only MySQL employee can download it.
[18 Jul 2007 10:41]
Andrey Wolk
I have placed the bug-data-29231-emulator-source_executable.zip file to ftp://ftp.mysql.com/pub/mysql/upload
[18 Jul 2007 12:54]
Tonci Grgin
Thanks Andrey, checking out.
[4 Oct 2007 18:04]
Tonci Grgin
Andrey, please accept my apologies as I was to busy to take care of this report sooner. I am unable to download the file you uploaded. Can you make single file test case and attach it to this report? As I said before, 4000 connections (max_connections) is way too high for your hardware... You need to take in account that there are a lot of per-connection buffers allocating all of your RAM and beyond... Reduce drastically. Also, I fail to recognize the necessity for threaded inserts, why not doing them in the loop? What I think is happening is that your are opening way too much connections to MySQL server in your code and most of them sleep for default amount of time (8 hours) but RAM allocate dfor them is slowing everything down as your machine is probably swapping. Can you please try and lower the wait-timeout to something close to, let's say, 1 minute? In order to prove this I will need output from "show processlist" at the time lock-up occurs. Maybe netstat -a will prove helpful too.
[4 Oct 2007 18:48]
Andrey Wolk
Executable without libmysql.dll !
Attachment: bug29231.7z (application/octet-stream, text), 309.72 KiB.
[4 Oct 2007 18:52]
Andrey Wolk
I was wrong when edit my.ini and set up 4000 connections. Always really only one connection is used.
[4 Oct 2007 19:10]
Tonci Grgin
Ok, I got the first file you posted. Did you confirm how many connections are in use with "SHOW PROCESSLIST"? If not, please do so.
[4 Oct 2007 19:17]
Andrey Wolk
Only 2 connections: 1) My Application connection (localhost); 2) MySql-client (HeidiSql).
[5 Oct 2007 9:28]
Tonci Grgin
Andrey, I used my best box to test this by putting 15000 insert threads (first time I saw all cores got near 100% load) but found no problems... Environment: - MySQL 5.0.50pb-1046 on WinXP Pro SP2 localhost - Your test case (the one I wasn't able to download at first) - test case cl arguments: localhost root **** test 15000 - test done in under 2min mysql> select count(*) from users_changes; +----------+ | count(*) | +----------+ | 14638 | +----------+ 1 row in set (0.02 sec) So, what now? Do you think I should keep filling the table? If so, to what record count?
[5 Oct 2007 10:48]
Tonci Grgin
Andrey, I just can't repeat this problem. Please recheck your configuration, both MySQL server and OS (maybe a HW too), and try again as test case seems correct and I have no problems with it. In this pass, I modified your test case so that it doesn't remove records I already inserted and here's the result (script is generated from first test log): c:\mysql507\bin>mysql -uroot -p test < bug29231script.txt Enter password: ******** c:\mysql507\bin>mysql -uroot -p test < bug29231script.txt Enter password: ******** etc to about 1200000 records. c:\mysql507\bin>net stop mysql5 The MySQL5 service is stopping.. The MySQL5 service was stopped successfully. c:\mysql507\bin>net start mysql5 The MySQL5 service is starting. The MySQL5 service was started successfully. MySql server #29231 bug emulator by Andrey Wolk andy_wolk@mail.ru 2007 http://bugs.mysql.com/bug.php?id=29231 ---------- Usage: emulator.exe host user pass dbname threadscount Examp: emulator.exe 127.0.0.1 root pass dbname 5000 Init OK Connecting to mysql... Reinitialization Init OK Generating 15000 insert threads. waiting for all inserts (threads) end. waiting for 691 threads waiting for 370 threads waiting for 0 threads Terminating Test took about 1 minute, with termination about 1:30 c:\mysql507\bin>mysql -uroot -p test Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.0.50-pb1046-log MySQL Pushbuild Edition, build 1046 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select count(*) from users_changes; +----------+ | count(*) | +----------+ | 1244592 | +----------+ 1 row in set (0.00 sec) mysql> I made sure all 15000 inserts passed: 2 Query INSERT INTO users_changes ( id, uname, utcdate, textdate, moneywas, moneynew, moneychange, infocode, managername, paycomment ) VALUES ( null , '14707', 0, '', 0, 0, 0, 0, '', '' )
[5 Oct 2007 11:01]
Andrey Wolk
I don't understand. How 15000 can be equivalent 14638. I think, after Quoting: " - test case cl arguments: localhost root **** test 15000 " We must see mysql> select count(*) from users_changes; +----------+ | count(*) | +----------+ | 15000 | !!!!! Why 14638 ??? +----------+ 100% load is logistical. Specially for this we bought BOX with 2xXeon(4 cores)= 8 cores + SAS-RAID1
[5 Oct 2007 12:53]
Tonci Grgin
Andrey yes, 14638 as I had to break your test case first time. It is not perfect, like trying to kill threads with negative number etc... That's why I repeated test with 1000000+ records in database and made sure all 15000 updates got through.
[10 Aug 2010 6:00]
ahmed anees
Hi!
i face the problem during insertton from the data from the exel file Step are given below
STEP 1:
i) require_once 'excel/reader.php';// this is the file to parse data from exel
ii)$data = new Spreadsheet_Excel_Reader();
iii)$data->read($fileName)
STEP2:
i) Make the Query on some condition and
ii)One by on get the data and insert into DATABASE
iii)if i simply insert record then all record are added in db and recods are 13000
$QRY1 = "SELECT ratesID,rate,effectiveDate
FROM ".TBL_RATES."
WHERE
routeTelPrefix = '".$routeTelPrefix."' AND
userID = '".$userID."' LIMIT 1";
//echo '<pre/>';
$resOldData = $database->single($QRY1);
if($resOldData['ratesID']!='')
{
$rx= $rx+1;
//echo '<li>Effecitv dateNew='.
$effectiveDate = date("Y-m-d",strtotime($effectiveDate));
if(($rate!=$resOldData['rate']) && ($effectiveDate !=$resOldData['effectiveDate']))
{
$qry1 = "UPDATE ".TBL_RATES."
SET status='expire'
WHERE
ratesID='".$resOldData['rateID']."'";
echo '<br/>';
$database->update($qry1);
$qry2 ="INSERT INTO ".TBL_RATES." (".$qryStr.")
VALUES (".$valueStr.")";
// echo '<br/>';
$database->insert($qry2);
}
}
else
{
echo $rx1=($rx1+1).'<li>else block';
$qry ="INSERT INTO ".TBL_RATES." (".$qryStr.")
VALUES (".$valueStr.")";
//echo '<br/>';
$database->insert($qry);
}
iv) But if i check whethre this record is already exit in db then update and insert it will not insert all record some time 8000 some time 10000 whether recoreds are uniquie one but if i remove the check that check whether the recod are there in db for dublicate or not then it works fine PLZ tell me how can i optimize my code and how i decresae the time of insertion Right now there is no indexes in tables
i send you the code below
for ($j = 0; $j < $data->sheets[0]['numRows']; $j++)
{
$valueStr ='';
for($k=1;$k<=$data->sheets[0]['numCols'];$k++)
{
$celArr = explode('l',$strArr[$k-1]);
$cellVal = $celArr[1];
$strTable = $data->sheets[0]['cells'][$j+1][$cellVal];//exit;
//to see the value contain the date value or not if it return 2 means it contain twoo - thats mean date it is
if( $j>0)
{
$dateVal = str_replace("-","-",$data->sheets[0]['cells'][$j+1][$cellVal],$cont);
if($cont==2)
{ //28-jul-2001
$dateVal = explode('-',$dateVal);
}
else
{
//mm/dd/yyyy
$dateVal = str_replace("/","/",$data->sheets[0]['cells'][$j+1][$cellVal],$cont1);
if($cont1==2)
{
$dateVal = explode('/',$dateVal);
}
}
if($cont>0)
{
$dateY = parseInt($dateVal[0]);
$dateD = parseInt($dateVal[2]);
if ($dateD && $dateY)
$val = date("Y-m-d",strtotime($data->sheets[0]['cells'][$j+1][$cellVal]));
else
$val = $data->sheets[0]['cells'][$j+1][$cellVal];
}
else if($cont1>0)
{
$dateY = parseInt($dateVal[0]);
$dateM = parseInt($dateVal[1]);
$dateD = parseInt($dateVal[2]);
if ($dateD && $dateY && $dateM)
$val = date("Y-m-d",strtotime($data->sheets[0]['cells'][$j+1][$cellVal]));
else
$val = $data->sheets[0]['cells'][$j+1][$cellVal];
}
else
{ $val = $data->sheets[0]['cells'][$j+1][$cellVal];
}
//end code to see whethre date in it
}//if($j>0)
//else
//$val = $data->sheets[0]['cells'][$j+1][$cellVal];
$valueStr .= "'".$val."',";
}//for($k=1;$k<$data->sheets[0]['numCols'];$k++)
//echo $valueStr;
if($j>0)
{
$valueStr .= "'".$username."',NOW(),'".$userID."'";
//$valueStr = substr($valueStr,0,-1);
$srtArrS = explode(',',$qryStr);
//--------------------------Remarks for this code ----------------------------------
//Because he didnt know at which index which colomn is coming so we get index and check if
//theer is exist already record and have date and rate chenge then update the old one
//with pending and active the new one with insert
//--------------------------End Remarks for this code ----------------------------------
$effectiveDateIndex = array_search('effectiveDate', $srtArrS);
$rchangeIndex = array_search('rchange', $srtArrS);
$rateIndex = array_search('rate', $srtArrS);
$routeTelPrefixIndex = array_search('routeTelPrefix', $srtArrS);
$effectiveDate = $data->sheets[0]['cells'][$j+1][$effectiveDateIndex+1];
$rChange = $data->sheets[0]['cells'][$j+1][$rchangeIndex+1];
$rate = $data->sheets[0]['cells'][$j+1][$rateIndex+1];
$routeTelPrefix = $data->sheets[0]['cells'][$j+1][$routeTelPrefixIndex+1];
echo $QRY1 = "SELECT ratesID,rate,effectiveDate
FROM ".TBL_RATES."
WHERE
routeTelPrefix = '".$routeTelPrefix."' AND
userID = '".$userID."' LIMIT 1";
//echo '<pre/>';
$resOldData = $database->single($QRY1);
if($resOldData['ratesID']!='')
{
$rx= $rx+1;
//echo '<li>Effecitv dateNew='.
$effectiveDate = date("Y-m-d",strtotime($effectiveDate));
if(($rate!=$resOldData['rate']) && ($effectiveDate !=$resOldData['effectiveDate']))
{
$qry1 = "UPDATE ".TBL_RATES."
SET status='expire'
WHERE
ratesID='".$resOldData['rateID']."'";
echo '<br/>';
$database->update($qry1);
$qry2 ="INSERT INTO ".TBL_RATES." (".$qryStr.")
VALUES (".$valueStr.")";
// echo '<br/>';
$database->insert($qry2);
}
}
else
{
echo $rx1=($rx1+1).'<li>else block';
$qry ="INSERT INTO ".TBL_RATES." (".$qryStr.")
VALUES (".$valueStr.")";
//echo '<br/>';
$database->insert($qry);
}
//exit;
}
//echo "<br>";
}//for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++)
[10 Aug 2010 6:00]
ahmed anees
Hi!
i face the problem during insertton from the data from the exel file Step are given below
STEP 1:
i) require_once 'excel/reader.php';// this is the file to parse data from exel
ii)$data = new Spreadsheet_Excel_Reader();
iii)$data->read($fileName)
STEP2:
i) Make the Query on some condition and
ii)One by on get the data and insert into DATABASE
iii)if i simply insert record then all record are added in db and recods are 13000
$QRY1 = "SELECT ratesID,rate,effectiveDate
FROM ".TBL_RATES."
WHERE
routeTelPrefix = '".$routeTelPrefix."' AND
userID = '".$userID."' LIMIT 1";
//echo '<pre/>';
$resOldData = $database->single($QRY1);
if($resOldData['ratesID']!='')
{
$rx= $rx+1;
//echo '<li>Effecitv dateNew='.
$effectiveDate = date("Y-m-d",strtotime($effectiveDate));
if(($rate!=$resOldData['rate']) && ($effectiveDate !=$resOldData['effectiveDate']))
{
$qry1 = "UPDATE ".TBL_RATES."
SET status='expire'
WHERE
ratesID='".$resOldData['rateID']."'";
echo '<br/>';
$database->update($qry1);
$qry2 ="INSERT INTO ".TBL_RATES." (".$qryStr.")
VALUES (".$valueStr.")";
// echo '<br/>';
$database->insert($qry2);
}
}
else
{
echo $rx1=($rx1+1).'<li>else block';
$qry ="INSERT INTO ".TBL_RATES." (".$qryStr.")
VALUES (".$valueStr.")";
//echo '<br/>';
$database->insert($qry);
}
iv) But if i check whethre this record is already exit in db then update and insert it will not insert all record some time 8000 some time 10000 whether recoreds are uniquie one but if i remove the check that check whether the recod are there in db for dublicate or not then it works fine PLZ tell me how can i optimize my code and how i decresae the time of insertion Right now there is no indexes in tables
i send you the code below
for ($j = 0; $j < $data->sheets[0]['numRows']; $j++)
{
$valueStr ='';
for($k=1;$k<=$data->sheets[0]['numCols'];$k++)
{
$celArr = explode('l',$strArr[$k-1]);
$cellVal = $celArr[1];
$strTable = $data->sheets[0]['cells'][$j+1][$cellVal];//exit;
//to see the value contain the date value or not if it return 2 means it contain twoo - thats mean date it is
if( $j>0)
{
$dateVal = str_replace("-","-",$data->sheets[0]['cells'][$j+1][$cellVal],$cont);
if($cont==2)
{ //28-jul-2001
$dateVal = explode('-',$dateVal);
}
else
{
//mm/dd/yyyy
$dateVal = str_replace("/","/",$data->sheets[0]['cells'][$j+1][$cellVal],$cont1);
if($cont1==2)
{
$dateVal = explode('/',$dateVal);
}
}
if($cont>0)
{
$dateY = parseInt($dateVal[0]);
$dateD = parseInt($dateVal[2]);
if ($dateD && $dateY)
$val = date("Y-m-d",strtotime($data->sheets[0]['cells'][$j+1][$cellVal]));
else
$val = $data->sheets[0]['cells'][$j+1][$cellVal];
}
else if($cont1>0)
{
$dateY = parseInt($dateVal[0]);
$dateM = parseInt($dateVal[1]);
$dateD = parseInt($dateVal[2]);
if ($dateD && $dateY && $dateM)
$val = date("Y-m-d",strtotime($data->sheets[0]['cells'][$j+1][$cellVal]));
else
$val = $data->sheets[0]['cells'][$j+1][$cellVal];
}
else
{ $val = $data->sheets[0]['cells'][$j+1][$cellVal];
}
//end code to see whethre date in it
}//if($j>0)
//else
//$val = $data->sheets[0]['cells'][$j+1][$cellVal];
$valueStr .= "'".$val."',";
}//for($k=1;$k<$data->sheets[0]['numCols'];$k++)
//echo $valueStr;
if($j>0)
{
$valueStr .= "'".$username."',NOW(),'".$userID."'";
//$valueStr = substr($valueStr,0,-1);
$srtArrS = explode(',',$qryStr);
//--------------------------Remarks for this code ----------------------------------
//Because he didnt know at which index which colomn is coming so we get index and check if
//theer is exist already record and have date and rate chenge then update the old one
//with pending and active the new one with insert
//--------------------------End Remarks for this code ----------------------------------
$effectiveDateIndex = array_search('effectiveDate', $srtArrS);
$rchangeIndex = array_search('rchange', $srtArrS);
$rateIndex = array_search('rate', $srtArrS);
$routeTelPrefixIndex = array_search('routeTelPrefix', $srtArrS);
$effectiveDate = $data->sheets[0]['cells'][$j+1][$effectiveDateIndex+1];
$rChange = $data->sheets[0]['cells'][$j+1][$rchangeIndex+1];
$rate = $data->sheets[0]['cells'][$j+1][$rateIndex+1];
$routeTelPrefix = $data->sheets[0]['cells'][$j+1][$routeTelPrefixIndex+1];
echo $QRY1 = "SELECT ratesID,rate,effectiveDate
FROM ".TBL_RATES."
WHERE
routeTelPrefix = '".$routeTelPrefix."' AND
userID = '".$userID."' LIMIT 1";
//echo '<pre/>';
$resOldData = $database->single($QRY1);
if($resOldData['ratesID']!='')
{
$rx= $rx+1;
//echo '<li>Effecitv dateNew='.
$effectiveDate = date("Y-m-d",strtotime($effectiveDate));
if(($rate!=$resOldData['rate']) && ($effectiveDate !=$resOldData['effectiveDate']))
{
$qry1 = "UPDATE ".TBL_RATES."
SET status='expire'
WHERE
ratesID='".$resOldData['rateID']."'";
echo '<br/>';
$database->update($qry1);
$qry2 ="INSERT INTO ".TBL_RATES." (".$qryStr.")
VALUES (".$valueStr.")";
// echo '<br/>';
$database->insert($qry2);
}
}
else
{
echo $rx1=($rx1+1).'<li>else block';
$qry ="INSERT INTO ".TBL_RATES." (".$qryStr.")
VALUES (".$valueStr.")";
//echo '<br/>';
$database->insert($qry);
}
//exit;
}
//echo "<br>";
}//for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++)
