[MDEV-730] LP:1025254 - Aria: Corrupt tables when storing data with COMPRESS() Created: 2012-07-16  Updated: 2014-03-03  Resolved: 2014-03-03

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: nbrnhardt (Inactive) Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug1025254.xml    

 Description   

OS: Windows 32/64 bit

We store our PDF files along with some other data in following table:

CREATE TABLE `belegliste` (
 
	`belegart` ENUM('Angebot','Kostenvoranschlag','Auftragsbestätigung','Lieferschein','Rechnung','Gutschrift') NOT NULL DEFAULT 'Angebot' COMMENT 'Belegart: 1 => Angebot, 2 => Kostenvoranschlag, 3 => Auftragsbestätigung, 4 => Lieferschein, 5 => Rechnung, 6 => Gutschrift',
 
	`belegnummer` MEDIUMINT(10) UNSIGNED NOT NULL COMMENT 'Belegnummer.',
 
	`positionen` SMALLINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Anzahl der Positionen auf dem Beleg',
 
	`form_anschrift` TEXT NULL COMMENT 'Speichert den Bereich Adresse ab.',
 
	`datum` DATE NULL DEFAULT NULL COMMENT 'Belegdatum.',
 
	`kundennummer` INT(10) UNSIGNED NULL DEFAULT NULL,
 
	`nettobetrag` DECIMAL(10,2) NULL DEFAULT NULL COMMENT 'Nettobetrag',
 
	`mwstsatz` TINYINT(3) UNSIGNED NULL DEFAULT '19' COMMENT 'MwSt-Satz',
 
	`bruttobetrag` DECIMAL(10,2) NULL DEFAULT NULL,
 
	`zahlungsziel` TINYINT(3) UNSIGNED NULL DEFAULT NULL COMMENT 'Zahlungsziel in Tagen',
 
	`datevnummer` INT(10) UNSIGNED NULL DEFAULT NULL COMMENT 'DATEV-Nummer für die FiBu',
 
	`techniknummer` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
 
	`berichtsnummer` MEDIUMINT(8) UNSIGNED ZEROFILL NULL DEFAULT NULL COMMENT 'RepairClient Techniknummer',
 
	`status` ENUM('normal','storniert','KVA bestätigt','KVA nicht bestätigt','on hold') NOT NULL DEFAULT 'normal' COMMENT 'Status für Storno, KVA bestätigt etc.',
 
	`statusdatum` DATE NULL DEFAULT NULL COMMENT 'Datum der Änderung, Bestätigung',
 
	`pdf` MEDIUMBLOB NULL COMMENT 'enthält Beleg als PDF',
 
	`listenkopf` VARCHAR(1024) NULL DEFAULT NULL,
 
	PRIMARY KEY (`belegart`, `belegnummer`),
 
	INDEX `positionen` (`positionen`),
 
	INDEX `kundennummer` (`kundennummer`),
 
	INDEX `datevnummer` (`datevnummer`),
 
	INDEX `techniknummer` (`techniknummer`),
 
	INDEX `datum` (`datum`),
 
	INDEX `berichtsnummer` (`berichtsnummer`)
 
)
 
COMMENT='Liste aller Belege (1=Angebot, 2=KVA, 3=AB, 4=LS, 5=RE. 6=Gu'
 
COLLATE='utf8_general_ci'
 
ENGINE=Aria
 
CHECKSUM=1;

The PDF itself is stored in `pdf` MEDIUMBLOB via COMPRESS(). Uncompressed PDF is about 30 to 70KB in size. The respective program first reserves a number with first doing an INSERT with `belegnummer` and `belegart` (with rest of the fields ZERO or 0), generating the PDF and storing it via UPDATE.

In various situations, we weren't able to do any updates on the table until I did a REPAIR table. Switching the table definition to MyISAM doesn't cause this issue.

The issue arises randomly, so I can't provide you any files.

 Comments   
Comment by nbrnhardt (Inactive) [ 2012-07-16 ]

Re: Aria: Corrupt tables when storing data with COMPRESS()
MariaDB version is 5.5.25

Comment by Elena Stepanova [ 2012-07-16 ]

Re: Aria: Corrupt tables when storing data with COMPRESS()
Hi,

When you weren't able to do updates, what exactly error and/or message were you getting?
Did you have any errors in the log?
Did it anyhow correlate with previous crashes, HW problems or power failures – anything that would cause dirty server shutdown and subsequent restart?

Also, your table update, as you described it, is a 2-step procedure: INSERT, then UPDATE. Which part was breaking?

Thanks.

Comment by nbrnhardt (Inactive) [ 2012-07-16 ]

Re: Aria: Corrupt tables when storing data with COMPRESS()
This morning, the MASTER (Win 2003 32bit) denied to do updates on belegart='Lieferscheine', belegnummer=103432. The entry couldn't neither be deleted nor updated. I had to do REPAIR TABLE to be able to do so.

Error log shows only: Note: Zerofilling moved table: 'belegliste'

No errors, no warnings.

Comment by nbrnhardt (Inactive) [ 2012-07-16 ]

Re: Aria: Corrupt tables when storing data with COMPRESS()
Slave on Win2008/64bit says:

Zerofilling moved table: '.\dbv\belegliste'

(null).(null): Page 178667520: Got error: 176 when reading datafile

Checking table: '.\dbv\belegliste'

dbv.belegliste: Table is from another system and must be zerofilled or repaired to be usable on this system

Seems the db server on the slave tries to zerofill the table, fails and complains it should be zerofilled (or repaired). The table is freshly copied from the master to re-sync the master and slaves. But it should work on the master w/o repairing or zero-filling,

Comment by Elena Stepanova [ 2012-07-16 ]

Re: Aria: Corrupt tables when storing data with COMPRESS()
"This morning, the MASTER (Win 2003 32bit) denied to do updates on belegart='Lieferscheine', belegnummer=103432. The entry couldn't neither be deleted nor updated."

How did it deny it, what was it saying?

Comment by nbrnhardt (Inactive) [ 2012-07-16 ]

Re: Aria: Corrupt tables when storing data with COMPRESS()
It just said: Affected rows: 0 Found rows: 0 Warnings: 0. I issued an update manually via HeidiSQL, which complained that there were 0 rows updated when it should have been 1.

Comment by Elena Stepanova [ 2012-07-17 ]

Re: Aria: Corrupt tables when storing data with COMPRESS()
Could you please quote the exact output of the two queries, run one after another, in the same client:

SELECT COUNT(*) WHERE <your condition>;
 
 
 
UPDATE .... WHERE <same exact condition>;

Comment by Elena Stepanova [ 2012-07-17 ]

Re: Aria: Corrupt tables when storing data with COMPRESS()
Ignore the previous question... HeidiSQL does say so when it doesn't update a row. E.g. if I do this:

use test;
drop table if exists t;
create table t (i int, b blob);
insert into t values (1, null);

update t set b = 'def' where i=1;

/* 1 rows affected, 0 rows found. Duration for 1 query: 0,000 sec. */

update t set b = 'def' where i=1;

/* 0 rows affected, 0 rows found. Duration for 1 query: 0,000 sec. */

So, in both cases it says 0 rows found, and in the second query it says 0 rows affected, because nothing changed. So, your manual check only shows that you either didn't have a match, or your table already had the fields populated.

So back to the question – why do you think the update had been denied, and how did it look, exactly?

Comment by Rasmus Johansson (Inactive) [ 2012-07-18 ]

Launchpad bug id: 1025254

Comment by nbrnhardt (Inactive) [ 2012-07-18 ]

Re: Aria: Corrupt tables when storing data with COMPRESS()
The database got stuck in a state when the program had made the placeholder INSERT ("dummy") with belegnummer and belegart, the rest of the fields NULL or 0.

I tried to manually do an update with HeidiSQL on the dummy row, with no luck. SELECT * FROM belegliste still showed me the row as it was made with INSERT.
I know that I have to change a value in order to make MariaDB sends back "1 row affected".

Also, I tried to DELETE that dummy row, with no luck either. SELECT * FROM belegliste still showed me the row as it was made with INSERT.

A REPAIR TABLE belegliste helped, but this way I don't have any copy for you. Sorry.

Generated at Thu Feb 08 06:30:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.