Dotaz: přetypování v mysqli

[Milan Uhrák]

Dobrý den, už dva dny se snažím přijít na to, proč dostávám chybu v jednom dotazu, který jsem s vaši pomocí uplácal.

Abych trochu popsal problém : v jedné tabulce (SmartValues ) jsou uloženy smart hodnoty raw a value

nadřazená tabulka (Collections ) obsahuje údaje o odečtu : číslo disku, datum .. bla bla bla

ID odečtu je pak součástí smart řádku, jasnačka ...

abych měl klíčové smart hodnoty rychleji poruce, tabulku collections jsem dovybavil sloupečky obsahujícími ... klíčové hodnoty.

spustím tedy dotaz alter table Collections a pak update, a dostanu error.

#1292 - Truncated incorrect INTEGER value: '3381h+07m+45.900s'

Různé nálezy na webu tvrdí, že to není error, jen warnings, ale dotaz vrátí false, a sloupečky se neaktualizují

Teď přijde to co nechápu.

přes phpmyadmina apustím dotaz

ALTER TABLE Collections 
	ADD SV5_raw mediumint(5) unsigned AFTER DiskID, 
	ADD SV197_raw mediumint(5) unsigned AFTER SV5_raw,
	ADD SV198_raw mediumint(5) unsigned AFTER SV197_raw,
	ADD SV9_raw int(6) unsigned AFTER SV198_raw,
	ADD SV9_val int(4) unsigned AFTER SV9_raw;

UPDATE Collections 
	SET 
		SV5_raw=( SELECT Raw FROM SmartValues WHERE Col_ID=Collections.ID AND SmartID = 5), 
		SV197_raw=( SELECT Raw FROM SmartValues WHERE Col_ID=Collections.ID AND SmartID = 197),
		SV198_raw=( SELECT Raw FROM SmartValues WHERE Col_ID=Collections.ID AND SmartID = 198),
		SV9_raw=( SELECT CAST( Raw AS INTEGER ) FROM SmartValues WHERE Col_ID=Collections.ID AND SmartID = 9),
		SV9_val=( SELECT Value FROM SmartValues WHERE Col_ID=Collections.ID AND SmartID = 9

a dostanu tu chybu ( ten CAST jsem tam vložil, abych chybě předešěl, ale drží se, mrška ..) chybu asi způsobuje zvýrazněný řádek ... údaj, který se mysqli nelíbí jsem našel pomocí dotazu ( a hledání ) :

SELECT ID, Raw, CAST( Raw AS INTEGER ) AS RawINT, `Value` FROM `SmartValues` WHERE `SmartID`=9 ORDER BY `SmartValues`.`Raw` ASC 

a dostal jsem :

 
2021 	3381h+07m+45.900s 	3381 	97
2039 	3381h+08m+03.770s 	3381 	97
2057 	3381h+08m+27.660s 	3381 	97
65467 	3382 	3382 	97

Tady žádný problém, CAST se provede bez potíží. Takže CASTem to nebude ... ale pak čím ? Prohledal jsem tabulku, a nikde jinde kromě těch tří řádků se 3381h nevyskytuje...

Takže fakt nevím v čem je problém. pokud někdo z vás má představu, budu rád za popostrčení. Předem díky Milan

---

---

Odpovědi

[Filip Jirsák]

Za prvé, u takovýchto dotazů je vhodné uvádět struktury jednotlivých tabulek. Za druhé, text „3381h+07m+45.900s“ opravdu na číslo převést nejde. Jak byste ten text převedl na číslo vy, ručně? Je to 3381? Nebo 33810745,900? Nebo 45,900? Vůbec mi není jasné, o co se tam vlastně snažíte – ten text je evidentně nějaké textové vyjádření času. Pokud s tím chcete v databázi dál pracovat, musíte to před uložením do databáze převést na typ TIME. Dalo by se to parsovat i v databázi, ale databáze není nejlepší nástroj na parsování textů.

[debian+]

Za prvé, u takovýchto dotazů je vhodné uvádět struktury jednotlivých tabulek.

To dal. To ALTER TABLE.

Vůbec mi není jasné, o co se tam vlastně snažíte – ten text je evidentně nějaké textové vyjádření času.

Cas asi uklada ako int cislo vyjadrujudruce pocet sekund od "zaciatku".

[Filip Jirsák]

To dal. To ALTER TABLE.

Dal alter table Collections, ale problém je se selectem ze SmartValues.

Cas asi uklada ako int cislo vyjadrujudruce pocet sekund od "zaciatku".

Právě že kdybychom měli strukturu tabulek, nemusíme se dohadovat, jaké typy tam asi jsou. Jinak text „3381h+07m+45.900s“ opravdu jako „int číslo“ nevypadá.

[debian+]

Aky problem "3381h+07m+45.900s" konvertovat na sekundy.

function cas_zo_stringu($text)
{
	$casti=explode('+', $text);
	
	$ret_cas=0;
	foreach($casti as $c)
	{
		$cas=substr($c, 0, -1);
		$hodnota=substr($c, -1);
		
		switch ($hodnota)
		{
			case 'h':
				$ret_cas+=$cas*3600;
				break;
			case 'm':
				$ret_cas+=$cas*60;
				break;
			case 's':
				$ret_cas+=$cas;
				break;
			default:
				echo "neznamy cas\n";
				break;
		}
	}
	
	return $ret_cas;
}

echo cas_zo_stringu('3381h+07m+45.900s');

[Filip Jirsák]

Tohle nevypadá jako SQL…

[Milan Uhrák]

dá se to přetvořit na mysql proceduru, Filipe. Nepruď pořád. Nebudu to ale dělat, protože mne nezajímá, kolik sekund disk pracuje, eviduju hodiny, kterýžto údaj disky většinou poskytují.

Ty tři hodnoty, kdy namísto hodin disk poslal nějaké zvláštní vyjádření času jsou asi z nějakého exotického disku, možná problém jednorázově odstraním jeho vymazáním z databáze. ( + načtených z něho hodnot ) ... ale tady jde o ten princip, abych pochopil co je špatně, když se mi to zdá ok. M.

[Filip Jirsák]

Ale psát to v MySQL není dobrý nápad – MySQL je hloupé úložiště, veškerý kód by měl být v aplikaci. Pokud byste chtěl programovat v databázi, je lepší na to použít nějakou pořádnou databázi.

Já neprudím, jenom se snažím vysvětlit, jak to udělat správně. Pokud se na něco ptá úplný začátečník, není rozumné poradit mu sérii nějakých hacků, kterým nebude rozumět. Pak to dopadne jako u vás, kdy vám přijde normální, že se text „3381h+07m+45.900s“ dá univerzálně převádět na číslo.

ale tady jde o ten princip, abych pochopil co je špatně, když se mi to zdá ok. M.

Špatně je to, že se pokoušíte převést na číslo text, který rozhodně žádné číslo nereprezentuje. A také to, že se vám to zdá OK.

[debian+]

Toto je PHP.

[Milan Uhrák]

Nevím, co řešíte. Collections jste viděli, jak vypadá, raw z SMARTD je varchar. Převést "3381h..." na integer ( počet naběhaných hodin ) JE MOŹNÉ, JAK UKAZUJE DRUHÝ DOTAZ, který korektně převede ten údaj ze SMARTu tak, že jakmile skončí číslice, přestane to řešit, a z číslic vytvoří číslo.

Prostý SELECT s CASTem se chová správně, přetypovaný údaj ale nezapíšu (asi) do INTEGER pole. Teď mně napadá v php prozkoumat, jaký datový typ má ten výsledek CASTnutí ... no uvidíme...

Teď mám dost práce okolo, možná zítra se najde pár minut. Jinak díky za snahu ... všem

Milan

[Filip Jirsák]

Nevím, co řešíte. Collections jste viděli, jak vypadá, raw z SMARTD je varchar.

Přičemž jak vypadá Collections je úplně jedno, podstatné je právě to smartd.raw.

Převést "3381h..." na integer ( počet naběhaných hodin ) JE MOŹNÉ, JAK UKAZUJE DRUHÝ DOTAZ, který korektně převede ten údaj ze SMARTu tak, že jakmile skončí číslice, přestane to řešit, a z číslic vytvoří číslo.

Ne, nepřevede to korektně, převede to nějak. Takhle funguje MySQL – bez ohledu na to, jak velké dělá programátor chyby, snaží se vždy to nějak zpracovat. Pak přijdou trochu jiná data, než jaká testoval programátor, ty implicitní konverze budou dělat něco jiného, než by si uživatel představoval, a všichni se budou akorát divit, co se to děje. Třeba vám tam jednou nějaký disk pošle 140d 1h 23m 15.0023s a vy budete mít radost, že ten disk naběhal jenom 140 hodin.

Pokud to chcete dělat správně, ten vstup zpracujte už v tom PHP a do databáze si vedle té surové hodnoty uložte i zpracovaný údaj jako číslo. Pokud to chcete co nejvíc naprasit a nezajímá vás, že to nebude fungovat, řešte si to sám.

[blondak]

On bude problém v tom, co to CAST("3381h+08m+27.660s" AS INT) udělá, ono to vezme ten string "3381h+08m+27.660s" a snaží se z něj udělat integer takže bere postupně čísla a když narazí na h tak skončí, ale ví, že to nezpracoval celé, proto ta chyba, každopádně to neudělá to co chcete, aby to převedlo na počet sekund.

[blondak]

ono teoreticky by šlo něco jako

SELECT UNIX_TIMESTAMP(ADDTIME('1970-01-01 00:00:00', TRIM(TRAILING ':' FROM REGEXP_REPLACE("3381h+08m+27.660s", '[^0-9.]+', ':'))))

ale narazí se na jiný problém a to je max velikost TIME, která je 838:59:59 což je cca 840 hodin, takže opravdu nezbývá než napsat nějakou funkci, která to rozseká a udělá aritmetiku za vás (viz výše).

[blondak]

Viz: db-fiddle

[Peter Golis]

max velikost TIME, která je 838:59:59 což je cca 840 hodin

A čo presnosť a rozsah DATETIME. nestačilo by ak nechce mať číselnú hodnotu interne rátanú v (mili)sekundách tak ako to ráta zdrojový HW?

[blondak]

problém je v té funkci addtime, která prostě nepřidá víc než je rozsah TIME

[Peter Golis]

Čiže problém je v návrhu schémy a zbytočnej konverzii čísla udávajúceho vek v ktorom nastala udalosť do časovej položky. Ja osobne by som tam nechal číslo, a skonvertoval to na dátum alebo čas až keď to naozaj (ak vôbec) potrebujem.

[Milan Uhrák]

problém je v tom, že 99% disků udává power-on-time v hodinách a jeden disk to udává v jiném jakémsi podrobmějším jakoby human readable formátu , který jsem zprvu vůbec neošetřil, ale nyní bych ten jiný formát rád převedl na formát obvyklý u jiných disků, t.j. písmeno H a vše za ním odseknout ... no a tady problém způsobuje podivné chování toho CASTu, kdy při SELECTu hodí warnings, ale při UPDATE a INSERT error. jinak nic hroznýho. Linux a FS obecně mívá volbu cesty - když je jedna cesta rozbahněná, jdeme jinudy... jen se musí ta lepší cesta najít... používat závity a ... diskuze

Ještě jednou všem díky, M.

[Filip Jirsák]

no a tady problém způsobuje podivné chování toho CASTu, kdy při SELECTu hodí warnings, ale při UPDATE a INSERT error

Na to jste přišel jak? MySQL se sice ve spoustě případů chová divně (např. už jenom tím, že převádí na číslo texty, které obsahují něco jiného, než číslice, a jenom vypíše varování), ale jsem přesvědčen na 99 %, že se ten CAST nechová různě při SELECTu a při UPDATE/INSERT.

Jak už jsem psal dříve, pokud vám data chodí v různých formátech, dokonce ve formátech, které předem neznáte, zpracovávat ty formáty až v databázi, navíc v MySQL, mi připadá jako velmi divoký nápad. Ale pokud si to děláte jen pro sebe a je vám jedno, že vám to nebude fungovat, je to vaše věc.

[Milan Uhrák]

jo .. je to tak ... ten CAST to ořeže, ale hodí Warnings Phpmyadmin mně s tím neotravovval, ale dnes jsem to ladil v PHP a warnings tam je a výsledek správný. Pokud ale výsledek takového CASTu použiju pro aktualizaci polí, hodí to error. Je to tak ...

použil jsem nakonec REGEXP_SUBSTRING "^\\d+" .. a prochází to ... nicméně teď je úkol zařídit přes php, aby se do databáze dostávali jen data správného typu, tedy ošetřit to pořádně před zápisem ...

V počátcích jsem to nijak neřešil, a teď se s tím ořu a trápím vás ...

Děkuji za všechny rozumné poznámky, za vaši snahu s tím pohnout, společně se to povedlo ....

Milan

[Milan Uhrák]

když se na problém podívám z větší perspektívy, tak je jasné, že s prostým zápisem dat ( zejména raw dat) do databáze nevystačím. VĚTŠINA disků vrací v raw hodnoty, které si významem odpovídají, ale některé famílie disků mají hodnoty zapisované jiným způsobem ( viz 99% disků uvádí hodnotu "9" ( power on hours ) jako počet nalítaných hodin, ale máme tu nějakou řadu, která čas zapisuje jinak. Tady se samo hodí ty konverze dělat přes oop, ale zase mně to tolik netlačí, abych se s tímhle mořil.

takže mám hodnoty "3381h..." kterou

SELECT CAST(raw as int)

převede korektně na numero bez hlášení tak, jak popisuje manuál ( při prvním nečíselném znaku zbytek řetězce zahodí ), a pak UPDATE, kde stejný převod hodí error.

Soustřeďme se na tento problém. Vše ostatní je podružné.

Děkuji tedy za všechny ty regexy a jiná navrhovaná řešení, Filip má pravdu, že jednoho dne bude nějaký disk poskytovat údaj zase úplně nějak jinak, a tam už to bude na oop, ale nyní mně fakt zajímá ten CAST ..

Milan.

[Filip Jirsák]

ale nyní mně fakt zajímá ten CAST

A co vás na tom CASTu zajímá? CAST převede vstup na jiný datový typ, pravidla jsou popsaná v manuálu, sám jste je tu citoval.

Pokud chcete řešit problém, budete muset problém nejprve popsat. Vy jste zatím napsal jenom svůj názor na řešení. Nevíme jak jste přišel na to, že by CAST mohl způsobovat problém, pořád nevíme strukturu té tabulky; nevíme, jak vypadají data, na kterých to pouštíte; nevíme, jaký je vámi očekávaný výsledek ani jaký je skutečný výsledek. Nevíme nic. Píšete, že dotaz vrátí false a sloupečky se neaktualizují. V příspěvku ale žádný dotaz není, je tam update. Navíc dotaz nic neaktualizuje, vy píšete, že očekáváte aktualizaci sloupečků. Takže asi myslíte ten update. Jenže update zase nevrací logickou hodnotu, vrací počet aktualizovaných záznamů. Prostě nevíme nic.

Takže pokud chcete opravdu poradit, nevymýšlejte, v čem by asi mohl být problém. Místo spekulací napište informace, které máte a které je snadné zjistit. Struktury tabulek (nejlepší je dát sem create table od obou tabulek). Asi máte jeden konkrétní záznam, který se podle vás chová špatně – tak sem dejte select s výpisem toho záznamu v tabulce, kterou chcete aktualizovat, a selecty příslušných záznamů z toho updatu. Možná už při tomhle dávání informací dohromady zjistíte, že vám ten select vlastně nic nenajde… No a pak sem dejte výstup toho příkazu update. Sice to asi bude „0 rows updated“ nebo jako to vypisuje MySQL konzole, ale aspoň uvidíme, že to je opravdu výstup toho příkazu a ne nějaká vaše interpretace.