Menu
I have a number of tests that run against a MySQL database which is pre-loaded with schemas and example data from a set of SQL files. Some of these tests, during their run, also create new data in the database.
Normally, tests are responsible for cleaning up after themselves (and thus not polluting the database environment for other tests). However, it appears that some of these tests aren't fully doing so, and thus leaving behind additional/modified records where they shouldn't.
Due to the complex set of code that is being tested, it isn't feasible to have a single transaction running for the entire test, so I can't just have MySQL roll everything back (there are both multiple cursors and multiple replicated DB servers involved, among other factors).
I'd like to have a way of more easily identifying these tests that are polluting the DB, but because it is allowable for tests to write to the DB (as long as they remove things afterwards), I can't just look at all alterations to the DB - I need only the effective changes, with canceling-out modifications removed.
One thought I had was that if there were a straightforward way to compare the contents of one table against another, I could do so after running each test, comparing the contents of a table initialized with the fixture to the contents of the table after the test.
961
To change existing data in a table, you use the UPDATE statement. The following shows the syntax of the UPDATE statement: UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition; Code language: SQL (Structured Query Language) (sql)
The CHANGETABLE function is typically used in the FROM clause of a query as if it were a table. CHANGETABLE (CHANGES...) To obtain row data for new or modified rows, join the result set to the user table by using the primary key columns.
If the value has changed, go through the table row by row using the query: SELECT row_id, BINARY_CHECKSUM (*) FROM sample_table WITH (NOLOCK); And compare the returned checksums against stored values. They didn't happen to put a last-modified timestamp on their rows, did they? For the record, if the version support is SQL Server 2005 or newer.
In this article, we will see, how to update from one table to another table based on ID match. We can update the table using UPDATE statement in SQL. The update statement is always followed by the SET command. The SET command is used to specify which columns and values need to be updated in a table.
Some various suggestions that I've gotten through other channels so far:
CHECKSUM TABLE - this would be almost perfect for my needs, except that it only works for MyISAM tables (we use InnoDB).
SHOW TABLE STATUS - this provides Data_length, which might work as a simplistic comparison. If I can't find anything better, this might suffice.
128
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
