Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Incomplete
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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.
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>;
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?
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.
Launchpad bug id: 1025254
Re: Aria: Corrupt tables when storing data with COMPRESS()
MariaDB version is 5.5.25