0

recently I received a task to insert all data in a xls table into a mysql table. so I changed the excel sheet headers to match what was in the mysql table and used csvkit - csvsql to insert the data into the table. now, the excel sheet had a few columns I did not need and the existing table had also some columns I did not want to fill with anything. so I converted the excel sheet to csv data and issued a command like this:

csvcut -d ";" -c 2,4,5,6,7,8,9,11,13,14,15,16,17 -x -S  Downloads/excel_sheet.csv| csvsql --create-if-not-exists --tables contacts  --insert --blanks  --verbose --db mysql://root:"password"@localhost/testdb

I should mention that the mysqlserver version located on my working station is:

Server version: 8.0.23-0ubuntu0.20.04.1 (Ubuntu)

and the target machine on where the data were needed is:

Server version: 5.7.33-0ubuntu0.18.04.1 (Ubuntu)

now the unfortunate thing is that after running the command I only did some select to see if the data has been added, I did not bother to check if the table was altered in any way. a few days ago I was told that the table had lost all the other info and columns that were there before. I checked and indeed something happened, because I knew the columns that were there and some are now missing.

Question is can I see what happened ? is it because of different mysql server versions ? and more importantly can we get the data back ?

Thanks in advance

John11
  • 437
  • 1
  • 6
  • 16
  • You can only get the data back if you have a backup of the missing data. – Shadow Mar 08 '21 at 10:01
  • ok, how about understanding what happened ? – John11 Mar 08 '21 at 19:15
  • If you have query logs, then you can see what happened. If not, then you can only guess. – Shadow Mar 08 '21 at 19:29
  • query logs ... somehow I suppose that should've been my responsability ... but the query was generated by csvsql .... isn't there any log that mysql holds automatically ? – John11 Mar 09 '21 at 13:23
  • No, you have to turn logging on manually, since it has a performance impact. Generally, it is not advisable to log queries on a production database. – Shadow Mar 09 '21 at 13:49
  • Please add the above answer and I'll mark it as accepted answer. – John11 Aug 27 '21 at 14:59

0 Answers0