Mysql – addition in datetime column during insertion in thesql

mysql-5.5

i have a table with below 5 columns..timestamp is data type for timedate related column :

 Region     event_time     start_time     mid_time   end_time
 India   2013-11-03 13:00:00        
 America
 Holland
Switzerland

I have source from where data for above column is coming at a particular interval..now while insertion i need to add time .

For ex. if in csv file i have event_time of 2013-11-03 13:00:00 , then for India time gets added by 5:30 ( due to GMT ) before insertion..and final inserted values should be 2013-11-03 18:30:00..same applies for rest of Regions and datetime columns..how should i do this…Also daylight saving will include for other Regions that do have this

I tried adddate , addtime functions..but this doesn't work…i tried updating after insertion , but this is not a solution i want…..any help ??

Best Answer

I doubt what are you trying can be done in a single go.You could do it specifying timezone for all inserts for a particular country. Or something like this after insert:

UPDATE....
  CASE WHEN Region='India' THEN event_time = event_time+INTERVAL 330 MINUTE 
  CASE WHEN Region='America' THEN event_time = event_time+INTERVAL someother MINUTE
  CASE...

SUGGESTION #1

If your machine has multiple cores, you need to increase the following:

[mysqld]
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 5000

What are these?

innodb_read_io_threads - The number of I/O threads for read operations in InnoDB.
innodb_write_io_threads - The number of I/O threads for write operations in InnoDB.
innodb_io_capacity - An upper limit on the I/O activity performed by the InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the insert buffer.

SUGGESTION #2

In order for separate data and indexes from the system tablespace (ibdata1), you need to do perform a complete restructuring of InnoDB. Sounds complicated, but is very straightforward. I wrote about this in the DBA StackExchange (Aug 29, 2012) and in StackOverflow (Oct 29, 2010). The basic steps are

Run SET GLOBAL innodb_fast_shutdown = 0;
mysqldump all data to a SQL dump
service mysql stop
Delete the following files
- ibdata1
- ib_logfile0
- ib_logfile1
service mysql start

Before you run service mysql start, add this line to my.cnf

innodb_open_files=32768

That way, there will be file handles dedicated to the each individual table. The default is 300. File handles have been known to get cached. There will be a slowdown if you set this very high and hit the ceiling quickly. This should not be the case if you are working a small number of tables.

Mysql – “Lost connection to MySQL server during query” error

I am running MySQL 5.7.26 on Windows and when doing just a simple query from the MySQL command line client I get that same error you describing. I found out it only occurs when the query length is exact 64 characters! Just by adding a space fixed the problem for me.
It doesn't matter if it's a select or insert/delete/update query.
I have run 66.000 queries on my server and only the queries with exact 64 characters failed.
I checked the database for corruption. But I could also reproduce same problem when making a new database and table on that server.
I could only reproduce this problem on that production server. I created a new server with same version with exact same settings as a VMWare machine but could not recreate. Used the same MySQL client.

Best Answer

Related Solutions

Mysql – InnoDB insertion faster

SUGGESTION #1

SUGGESTION #2

Mysql – “Lost connection to MySQL server during query” error

Related Question