MySQL Forums
Forum List  »  Data Recovery

Altering primary key values
Posted by: Aigini Navaneethan
Date: August 16, 2020 09:26PM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Altering primary key values
142
August 16, 2020 09:26PM
64
August 16, 2020 10:14PM
59
August 17, 2020 08:21AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.