[ https://issues.apache.org/jira/browse/DERBY-4348?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kristian Waagan updated DERBY-4348:
-----------------------------------
Issue & fix info: [Repro attached]
Verified in trunk with the provided repro database.
Observations:
- database is in 10.4 format
(experiments below run with trunk)
- consistency checks pass
- exporting data, importing and repeating the repro procedure seems to work (at least the
exact row is the not corrupt). I didn't create any indexes.
- dropping and recreating STRINGRANGENEW_ without indexes works without corruption
- dropping and recreating STRINGRANGENEW_ with primary key works without corruption
This issue needs further qualification.
When I ran the repro, 42 rows got corrupted.
The attached sql script executes the steps required to reproduce (download and unzip database,
then run script with ij - 'ij d4348.sql').
I don't have time to follow up on this issue (finally some vacation :) ), but maybe it would
be good to figure out if any of the tables are corrupt, or if it is a single index or a combination
of indexes that causes the corruption.
Maybe the reporter can post the SQL that created the indexes for completeness?
> Copy table content with "INSERT INTO table SELECT FROM (...)" statement leads to corrupt
data
> ---------------------------------------------------------------------------------------------
>
> Key: DERBY-4348
> URL: https://issues.apache.org/jira/browse/DERBY-4348
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.1.1
> Environment: Derby: embedded driver 10.5.1.1 - (764942); testet with 10.4 and
client driver also
> OS: Windows XP
> SQL statements executed using SQuirrel SQL Client, but behavior is the same with ij
> Reporter: Stefan Huber
> Priority: Critical
> Attachments: d4348.sql, rmdb.zip
>
>
> I had to change a the primary key of a table and used ddlutils to do so. Ddlutils recreated
the table to perform this task.
> After the schema conversion the row data of the changed table were corrupted.
> The values of the last table column were filled with values from other rows!
> After performing a few tests I could break down the problem to the SQL statement "INSERT
INTO table SELECT FROM (...)"
> To reprocude the effect do the following:
> 1. unpack attached database 'rmdb.zip'
> 2. connect to the database with embedded driver
> User: IGEL
> Password: test
> 3. read data of an example row from database
> select * from stringrangenew where classname = 'x.xserver%.colordepth';
> result is
> x.xserver%.colordepth 2 [16] [24] [32]
> 4. now copy the data to the second table (column INSTANCENR has been added to this table)
> INSERT INTO STRINGRANGENEW_ (CLASSNAME,FIRMWAREID,RVALUE) SELECT CLASSNAME,FIRMWAREID,RVALUE
FROM STRINGRANGENEW;
> 5. select data of example row from second table
> select * from stringrangenew_ where classname = 'x.xserver%.colordepth';
> result is
> x.xserver%.colordepth 2 -1 [CCW][CW][XX]
> -> value of last column is not the same as in orignal table!
> Here some additional information i worked out during my tests:
> * if you change the copy statement to include the additional column INSTANCENR, the copied
data are correct.
> delete from STRINGRANGENEW_;
> INSERT INTO STRINGRANGENEW_ (CLASSNAME,FIRMWAREID, INSTANCENR, RVALUE) SELECT CLASSNAME,FIRMWAREID,
-1, RVALUE FROM STRINGRANGENEW;
> * if you select the rows only 'SELECT CLASSNAME,FIRMWAREID,RVALUE FROM STRINGRANGENEW',
the result shows correct data
> Note:
> The effect is not restricted to this row but also applies to other rows. But it's always
the same rows, that get corrupted.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
|