Hi,
I have a few tables that have the fields ‘day’, ‘month’ and ‘year’ as PRIMARY KEY.
This is the structure of one of the tables :
mysql> describe lz_stats_aggs;
+----------------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+----------------------+------+-----+---------+-------+
| year | smallint(4) unsigned | NO | PRI | 0 | |
| month | tinyint(3) unsigned | NO | PRI | 0 | |
| day | tinyint(3) unsigned | NO | PRI | 0 | |
| time | int(10) unsigned | NO | MUL | 0 | |
| mtime | int(10) unsigned | NO | MUL | 0 | |
| sessions | int(10) unsigned | NO | | 0 | |
| visitors_unique | int(10) unsigned | NO | | 0 | |
| conversions | int(10) unsigned | NO | | 0 | |
| aggregated | int(10) unsigned | NO | MUL | 0 | |
| chats_forwards | int(10) unsigned | NO | | 0 | |
| chats_posts_internal | int(10) unsigned | NO | | 0 | |
| chats_posts_external | int(10) unsigned | NO | | 0 | |
| avg_time_site | double unsigned | NO | | 0 | |
+----------------------+----------------------+------+-----+---------+-------+
This is the data for the month of JULY in the same table :
mysql> select * from lz_stats_aggs where day >= '16' and month = '7' and year = '2020';
+------+-------+-----+------------+----------+----------+-----------------+-------------+------------+----------------+----------------------+----------------------+---------------+
| year | month | day | time | mtime | sessions | visitors_unique | conversions | aggregated | chats_forwards | chats_posts_internal | chats_posts_external | avg_time_site |
+------+-------+-----+------------+----------+----------+-----------------+-------------+------------+----------------+----------------------+----------------------+---------------+
| 2020 | 7 | 16 | 1595235228 | 3557100 | 416 | 416 | 0 | 1595235227 | 0 | 982 | 987 | 1316.7668 |
| 2020 | 7 | 17 | 1595005244 | 10273000 | 409 | 408 | 0 | 1595005243 | 0 | 719 | 614 | 2075.2298 |
| 2020 | 7 | 18 | 1595091680 | 49116400 | 0 | 0 | 0 | 1595091680 | 0 | 0 | 0 | 0 |
| 2020 | 7 | 19 | 1595178102 | 97573600 | 1 | 1 | 0 | 1595178102 | 0 | 0 | 0 | 7859 |
| 2020 | 7 | 20 | 1595385572 | 99382700 | 0 | 0 | 0 | 1595385572 | 0 | 0 | 0 | 0 |
| 2020 | 7 | 24 | 1595670196 | 56234500 | 0 | 0 | 0 | 1595670196 | 0 | 0 | 0 | 0 |
| 2020 | 7 | 25 | 1597584414 | 1487900 | 0 | 0 | 0 | 1597584413 | 0 | 0 | 0 | 0 |
+------+-------+-----+------------+----------+----------+-----------------+-------------+------------+----------------+----------------------+----------------------+---------------+
7 rows in set (0.00 sec)
This table is part of an old DB called chatDB. I did a full restore of chatDB into a new server, which supposedly had data only until 16/7. As the name suggests, it was a DB for a chat website. However I noticed that there is information even after 16/7 in some of the tables. I believe this was because at these times, I even had the old chat website running for testing purposes, after switching to the new chat DB after cutover, which was on 17/7.
So now, when I try to insert the actual data from the 17/7 onwards into the db using a .csv file and the SQL LOAD statement like this, I get an error :
mysql> LOAD DATA INFILE '/var/opt/rh/rh-mysql57/lib/mysql-files/lz_stats_aggs07.csv' INTO TABLE lz_stats_aggs FIELDS TERMINATED BY ',';
ERROR 1062 (23000): Duplicate entry '2020-7-20' for key 'PRIMARY'
What I want to know is, if I delete information from the table above dated 17/7 - 25/7, will it cause problems for the DB, since the day, month and year are PRIMARY KEY?