Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Dropping tables to reset
02-08-2018, 09:48 AM,
#1
Dropping tables to reset
We are doing some intense testing creating dummy items, vendors, suppliers, transactions.

Now we need to do some cleaning and because of forks constraints, tables can't be dropped in random order.

my question: what would be the table list and dropping order:
- to delete all items
- to delete all customers
- to delete all vendors

thank you!

Reply
02-08-2018, 08:47 PM,
#2
RE: Dropping tables to reset
To be honest I would suggest starting with an empty database and copying over the tables that you do want is easier than trying to clear out the data table by table. Though YMMV

Tim
Reply
02-09-2018, 01:49 PM,
#3
RE: Dropping tables to reset
(02-08-2018, 08:47 PM)falkoner Wrote: To be honest I would suggest starting with an empty database and copying over the tables that you do want is easier than trying to clear out the data table by table. Though YMMV

Tim

We will be doing a lot of importing/testing/emptying so this might be very tedious
We created a tool in perl that render an sql files from existing csv files that the company used in the past with all our items data in it
It starts with a series of truncate before injecting everything (see attached file to see what I will be doing. Hence my question about the order in which these TRUNCATE should be

Let me know if that makes sense.



Attached Files Thumbnail(s)
   
Reply
02-16-2018, 11:47 AM, (This post was last modified: 02-16-2018, 12:35 PM by TurboPT.)
#4
RE: Dropping tables to reset
The three tables that you mentioned in the first post do not appear to have any FK relations to each other, so I would say the order should not matter.

items (stockmaster)
customers (debtorsmaster)
vendors (suppliers)

If you are getting FK complaints, then those are likely to other tables, and would need to know what the error message(s) say.

=====

Also, know that all but three tables in webERP use INNODB (the three that do not use INNODB do not have any FK relations), and this is what MySQL says about the INNODB engine type in the MySQL 5.7 TRUNCATE docs:

MySQL 5.7 docs Wrote:Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements. To achieve high performance, it bypasses the DML method of deleting data. Thus, it cannot be rolled back, it does not cause ON DELETE triggers to fire, and it cannot be performed for InnoDB tables with parent-child foreign key relationships.

The 5.7 doc was found here.

However, the TRUNCATE behavior in older MySQL docs differ, so if you have, say, MySQL 5.1:

MySQL 5.1 docs Wrote:For an InnoDB table, InnoDB processes TRUNCATE TABLE by deleting rows one by one if there are any FOREIGN KEY constraints that reference the table. If there are no FOREIGN KEY constraints, InnoDB performs fast truncation by dropping the original table and creating an empty one with the same definition, which is much faster than deleting rows one by one...

The 5.1 doc was found here.

So, as Tim implied, YMMV as to how/what you'd like to accomplish. Based on the information differences here, what might have been possible in an "older version" may no longer be possible in a "newer version".
Reply
02-16-2018, 08:58 PM,
#5
RE: Dropping tables to reset
Another probably easier way to achieve this is to dump the data into a text file using mysqldump, go through it in a text editor deleting out the data you don't want, and then build the database using the data that remains in your text file.

Tim
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)