Błąd w bazie danych - Klienci/Szukaj
Witam serdecznie, Przy próbie drukowania klientów przez KLIENCI/SZUKAJ, filtr "usunięci od 01/01/2015 do 31/01/2015" i wyborze grupy w której mam ponad 400 usuniętych klientów LMS wyrzuca błąd: Napotkano błędy w bazie danych! *Zapytanie:* SELECT c.id AS id, CONCAT(UPPER(lastname), ' ', c.name) AS customername, status, address, zip, city, countryid, countries.name AS country, email, ten, ssn, c.info AS info, message, c.divisionid, c.paytime AS paytime, COALESCE(b.value, 0) AS balance, COALESCE(t.value, 0) AS tariffvalue, s.account, s.warncount, s.online, c.type AS customertype, cutoffstop, (SELECT uid FROM imessengers WHERE imessengers.customerid = c.id) AS uid, (SELECT uid FROM imessengers WHERE imessengers.customerid = c.id) AS umowa, (SELECT max(cash.time) FROM cash WHERE cash.customerid = c.id) AS lastcash, (SELECT max(dateto) FROM assignments WHERE assignments.customerid = c.id) AS dateto, (CASE WHEN s.account = s.acsum THEN 1 WHEN s.acsum > 0 THEN 2 ELSE 0 END) AS nodeac, (CASE WHEN s.warncount = s.warnsum THEN 1 WHEN s.warnsum > 0 THEN 2 ELSE 0 END) AS nodewarn FROM customersview c LEFT JOIN countries ON (c.countryid = countries.id) LEFT JOIN customerassignments ON (c.id = customerassignments.customerid) JOIN customercash b ON (b.customerid = c.id) LEFT JOIN (SELECT a.customerid, SUM((CASE a.suspended WHEN 0 THEN (((100 - a.pdiscount) * (CASE WHEN t.value IS NULL THEN l.value ELSE t.value END) / 100) - a.vdiscount) ELSE ((((100 - a.pdiscount) * (CASE WHEN t.value IS NULL THEN l.value ELSE t.value END) / 100) - a.vdiscount) * 0 / 100) END) * (CASE t.period WHEN 3 THEN 1 WHEN 5 THEN 1/12.0 WHEN 7 THEN 1/6.0 WHEN 4 THEN 1/3.0 ELSE (CASE a.period WHEN 3 THEN 1 WHEN 5 THEN 1/12.0 WHEN 7 THEN 1/6.0 WHEN 4 THEN 1/3.0 ELSE 0 END) END) ) AS value FROM assignments a LEFT JOIN tariffs t ON (t.id = a.tariffid) LEFT JOIN liabilities l ON (l.id = a.liabilityid AND a.period != 0) WHERE (a.datefrom <= UNIX_TIMESTAMP() OR a.datefrom = 0) AND (a.dateto > UNIX_TIMESTAMP() OR a.dateto = 0) GROUP BY a.customerid ) t ON (t.customerid = c.id) LEFT JOIN (SELECT ownerid, SUM(access) AS acsum, COUNT(access) AS account, SUM(warning) AS warnsum, COUNT(warning) AS warncount, (CASE WHEN MAX(lastonline) > UNIX_TIMESTAMP() - 600 THEN 1 ELSE 0 END) AS online FROM nodes WHERE ownerid > 0 GROUP BY ownerid ) s ON (s.ownerid = c.id) LEFT JOIN imessengers im ON im.customerid = c.id WHERE c.deleted = 1 AND customergroupid=62 AND ((moddate >= 1420066800 AND moddate <= 1422745199)) ORDER BY customername asc *Błąd:* Subquery returns more than 1 row Problem występuje tylko z tą jedną grupą. Mam inne grupy z usuniętymi klientami i na tamtych grupach to działa. Nie mogę dojść co jest przyczyną. Macie jakieś pomysły ? pozdrawiam -- Jacek Brzozowski
Wykonaj wewnętrzne zapytania SELECT i zobacz o które z nich chodzi. Podejrzanie wygląda to do tabeli imessengers. 08-07-2015 08:44, "Jacek Brzozowski" <j.brzozowski@marton.pl> napisał(a):
Witam serdecznie,
Przy próbie drukowania klientów przez KLIENCI/SZUKAJ, filtr "usunięci od 01/01/2015 do 31/01/2015" i wyborze grupy w której mam ponad 400 usuniętych klientów LMS wyrzuca błąd:
Napotkano błędy w bazie danych! *Zapytanie:* SELECT c.id AS id, CONCAT(UPPER(lastname), ' ', c.name) AS customername, status, address, zip, city, countryid, countries.name AS country, email, ten, ssn, c.info AS info, message, c.divisionid, c.paytime AS paytime, COALESCE(b.value, 0) AS balance, COALESCE(t.value, 0) AS tariffvalue, s.account, s.warncount, s.online, c.type AS customertype, cutoffstop, (SELECT uid FROM imessengers WHERE imessengers.customerid = c.id) AS uid, (SELECT uid FROM imessengers WHERE imessengers.customerid = c.id) AS umowa, (SELECT max(cash.time) FROM cash WHERE cash.customerid = c.id) AS lastcash, (SELECT max(dateto) FROM assignments WHERE assignments.customerid = c.id) AS dateto, (CASE WHEN s.account = s.acsum THEN 1 WHEN s.acsum > 0 THEN 2 ELSE 0 END) AS nodeac, (CASE WHEN s.warncount = s.warnsum THEN 1 WHEN s.warnsum > 0 THEN 2 ELSE 0 END) AS nodewarn FROM customersview c LEFT JOIN countries ON (c.countryid = countries.id) LEFT JOIN customerassignments ON (c.id = customerassignments.customerid) JOIN customercash b ON (b.customerid = c.id) LEFT JOIN (SELECT a.customerid, SUM((CASE a.suspended WHEN 0 THEN (((100 - a.pdiscount) * (CASE WHEN t.value IS NULL THEN l.value ELSE t.value END) / 100) - a.vdiscount) ELSE ((((100 - a.pdiscount) * (CASE WHEN t.value IS NULL THEN l.value ELSE t.value END) / 100) - a.vdiscount) * 0 / 100) END) * (CASE t.period WHEN 3 THEN 1 WHEN 5 THEN 1/12.0 WHEN 7 THEN 1/6.0 WHEN 4 THEN 1/3.0 ELSE (CASE a.period WHEN 3 THEN 1 WHEN 5 THEN 1/12.0 WHEN 7 THEN 1/6.0 WHEN 4 THEN 1/3.0 ELSE 0 END) END) ) AS value FROM assignments a LEFT JOIN tariffs t ON (t.id = a.tariffid) LEFT JOIN liabilities l ON (l.id = a.liabilityid AND a.period != 0) WHERE (a.datefrom <= UNIX_TIMESTAMP() OR a.datefrom = 0) AND (a.dateto > UNIX_TIMESTAMP() OR a.dateto = 0) GROUP BY a.customerid ) t ON (t.customerid = c.id) LEFT JOIN (SELECT ownerid, SUM(access) AS acsum, COUNT(access) AS account, SUM(warning) AS warnsum, COUNT(warning) AS warncount, (CASE WHEN MAX(lastonline) > UNIX_TIMESTAMP() - 600 THEN 1 ELSE 0 END) AS online FROM nodes WHERE ownerid > 0 GROUP BY ownerid ) s ON (s.ownerid = c.id) LEFT JOIN imessengers im ON im.customerid = c.id WHERE c.deleted = 1 AND customergroupid=62 AND ((moddate >= 1420066800 AND moddate <= 1422745199)) ORDER BY customername asc *Błąd:* Subquery returns more than 1 row
Problem występuje tylko z tą jedną grupą. Mam inne grupy z usuniętymi klientami i na tamtych grupach to działa. Nie mogę dojść co jest przyczyną. Macie jakieś pomysły ?
pozdrawiam
-- Jacek Brzozowski
_______________________________________________ lms mailing list lms@lists.lms.org.pl http://lists.lms.org.pl/mailman/listinfo/lms
W dniu 08.07.2015 14:57, Maciej Lew napisał(a):
Wykonaj wewnętrzne zapytania SELECT i zobacz o które z nich chodzi. Podejrzanie wygląda to do tabeli imessengers. 08-07-2015 08:44, "Jacek Brzozowski" <j.brzozowski@marton.pl> napisał(a):
Witam serdecznie,
Przy próbie drukowania klientów przez KLIENCI/SZUKAJ, filtr "usunięci od 01/01/2015 do 31/01/2015" i wyborze grupy w której mam ponad 400 usuniętych klientów LMS wyrzuca błąd:
Napotkano błędy w bazie danych! ZAPYTANIE: SELECT c.id [1] AS id, CONCAT(UPPER(lastname), ' ', c.name [2]) AS customername, status, address, zip, city, countryid, countries.name [3] AS country, email, ten, ssn, c.info [4] AS info, message, c.divisionid, c.paytime AS paytime, COALESCE(b.value, 0) AS balance, COALESCE(t.value, 0) AS tariffvalue, s.account, s.warncount, s.online, c.type AS customertype, cutoffstop, (SELECT uid FROM imessengers WHERE imessengers.customerid = c.id [1]) AS uid, (SELECT uid FROM imessengers WHERE imessengers.customerid = c.id [1]) AS umowa, (SELECT max(cash.time) FROM cash WHERE cash.customerid = c.id [1]) AS lastcash, (SELECT max(dateto) FROM assignments WHERE assignments.customerid = c.id [1]) AS dateto, (CASE WHEN s.account = s.acsum THEN 1 WHEN s.acsum > 0 THEN 2 ELSE 0 END) AS nodeac, (CASE WHEN s.warncount = s.warnsum THEN 1 WHEN s.warnsum > 0 THEN 2 ELSE 0 END) AS nodewarn FROM customersview c LEFT JOIN countries ON (c.countryid = countries.id [5]) LEFT JOIN customerassignments ON (c.id [1] = customerassignments.customerid) JOIN customercash b ON (b.customerid = c.id [1]) LEFT JOIN (SELECT a.customerid, SUM((CASE a.suspended WHEN 0 THEN (((100 - a.pdiscount) * (CASE WHEN t.value IS NULL THEN l.value ELSE t.value END) / 100) - a.vdiscount) ELSE ((((100 - a.pdiscount) * (CASE WHEN t.value IS NULL THEN l.value ELSE t.value END) / 100) - a.vdiscount) * 0 / 100) END) * (CASE t.period WHEN 3 THEN 1 WHEN 5 THEN 1/12.0 WHEN 7 THEN 1/6.0 WHEN 4 THEN 1/3.0 ELSE (CASE a.period WHEN 3 THEN 1 WHEN 5 THEN 1/12.0 WHEN 7 THEN 1/6.0 WHEN 4 THEN 1/3.0 ELSE 0 END) END) ) AS value FROM assignments a LEFT JOIN tariffs t ON (t.id [6] = a.tariffid) LEFT JOIN liabilities l ON (l.id [7] = a.liabilityid AND a.period != 0) WHERE (a.datefrom <= UNIX_TIMESTAMP() OR a.datefrom = 0) AND (a.dateto > UNIX_TIMESTAMP() OR a.dateto = 0) GROUP BY a.customerid ) t ON (t.customerid = c.id [1]) LEFT JOIN (SELECT ownerid, SUM(access) AS acsum, COUNT(access) AS account, SUM(warning) AS warnsum, COUNT(warning) AS warncount, (CASE WHEN MAX(lastonline) > UNIX_TIMESTAMP() - 600 THEN 1 ELSE 0 END) AS online FROM nodes WHERE ownerid > 0 GROUP BY ownerid ) s ON (s.ownerid = c.id [1]) LEFT JOIN imessengers im ON im.customerid = c.id [1] WHERE c.deleted = 1 AND customergroupid=62 AND ((moddate >= 1420066800 AND moddate <= 1422745199)) ORDER BY customername asc BŁĄD: Subquery returns more than 1 row
Problem występuje tylko z tą jedną grupą. Mam inne grupy z usuniętymi klientami i na tamtych grupach to działa. Nie mogę dojść co jest przyczyną. Macie jakieś pomysły ?
Może napisałbyś jakiej wersji lms używasz i czy to nie jest czasem jakaś lokalnie modyfikowana?
pozdrawiam
-- Jacek Brzozowski
-- Pozdrawiam Tomasz Chiliński, Chilan
Wyrzuciłem to zapytanie z imessengers i poszło. Dziękuje za podpowiedź. Mam tu małą lokalną modyfikację i to było to. pozdrawiam Jacek W dniu 2015-07-08 o 14:57, Maciej Lew pisze:
Wykonaj wewnętrzne zapytania SELECT i zobacz o które z nich chodzi. Podejrzanie wygląda to do tabeli imessengers.
08-07-2015 08:44, "Jacek Brzozowski" <j.brzozowski@marton.pl <mailto:j.brzozowski@marton.pl>> napisał(a):
Witam serdecznie,
Przy próbie drukowania klientów przez KLIENCI/SZUKAJ, filtr "usunięci od 01/01/2015 do 31/01/2015" i wyborze grupy w której mam ponad 400 usuniętych klientów LMS wyrzuca błąd:
Napotkano błędy w bazie danych! *Zapytanie:* SELECT c.id <http://c.id> AS id, CONCAT(UPPER(lastname), ' ', c.name <http://c.name>) AS customername, status, address, zip, city, countryid, countries.name <http://countries.name> AS country, email, ten, ssn, c.info <http://c.info> AS info, message, c.divisionid, c.paytime AS paytime, COALESCE(b.value, 0) AS balance, COALESCE(t.value, 0) AS tariffvalue, s.account, s.warncount, s.online, c.type AS customertype, cutoffstop, (SELECT uid FROM imessengers WHERE imessengers.customerid = c.id <http://c.id>) AS uid, (SELECT uid FROM imessengers WHERE imessengers.customerid = c.id <http://c.id>) AS umowa, (SELECT max(cash.time) FROM cash WHERE cash.customerid = c.id <http://c.id>) AS lastcash, (SELECT max(dateto) FROM assignments WHERE assignments.customerid = c.id <http://c.id>) AS dateto, (CASE WHEN s.account = s.acsum THEN 1 WHEN s.acsum > 0 THEN 2 ELSE 0 END) AS nodeac, (CASE WHEN s.warncount = s.warnsum THEN 1 WHEN s.warnsum > 0 THEN 2 ELSE 0 END) AS nodewarn FROM customersview c LEFT JOIN countries ON (c.countryid = countries.id <http://countries.id>) LEFT JOIN customerassignments ON (c.id <http://c.id> = customerassignments.customerid) JOIN customercash b ON (b.customerid = c.id <http://c.id>) LEFT JOIN (SELECT a.customerid, SUM((CASE a.suspended WHEN 0 THEN (((100 - a.pdiscount) * (CASE WHEN t.value IS NULL THEN l.value ELSE t.value END) / 100) - a.vdiscount) ELSE ((((100 - a.pdiscount) * (CASE WHEN t.value IS NULL THEN l.value ELSE t.value END) / 100) - a.vdiscount) * 0 / 100) END) * (CASE t.period WHEN 3 THEN 1 WHEN 5 THEN 1/12.0 WHEN 7 THEN 1/6.0 WHEN 4 THEN 1/3.0 ELSE (CASE a.period WHEN 3 THEN 1 WHEN 5 THEN 1/12.0 WHEN 7 THEN 1/6.0 WHEN 4 THEN 1/3.0 ELSE 0 END) END) ) AS value FROM assignments a LEFT JOIN tariffs t ON (t.id <http://t.id> = a.tariffid) LEFT JOIN liabilities l ON (l.id <http://l.id> = a.liabilityid AND a.period != 0) WHERE (a.datefrom <= UNIX_TIMESTAMP() OR a.datefrom = 0) AND (a.dateto > UNIX_TIMESTAMP() OR a.dateto = 0) GROUP BY a.customerid ) t ON (t.customerid = c.id <http://c.id>) LEFT JOIN (SELECT ownerid, SUM(access) AS acsum, COUNT(access) AS account, SUM(warning) AS warnsum, COUNT(warning) AS warncount, (CASE WHEN MAX(lastonline) > UNIX_TIMESTAMP() - 600 THEN 1 ELSE 0 END) AS online FROM nodes WHERE ownerid > 0 GROUP BY ownerid ) s ON (s.ownerid = c.id <http://c.id>) LEFT JOIN imessengers im ON im.customerid = c.id <http://c.id> WHERE c.deleted = 1 AND customergroupid=62 AND ((moddate >= 1420066800 AND moddate <= 1422745199)) ORDER BY customername asc *Błąd:* Subquery returns more than 1 row
Problem występuje tylko z tą jedną grupą. Mam inne grupy z usuniętymi klientami i na tamtych grupach to działa. Nie mogę dojść co jest przyczyną. Macie jakieś pomysły ?
pozdrawiam
-- Jacek Brzozowski
_______________________________________________ lms mailing list lms@lists.lms.org.pl <mailto:lms@lists.lms.org.pl> http://lists.lms.org.pl/mailman/listinfo/lms
_______________________________________________ lms mailing list lms@lists.lms.org.pl http://lists.lms.org.pl/mailman/listinfo/lms
-- Jacek Brzozowski Marton Sp. z o.o. Tel./fax: 56 683 31 91 www.marton.pl Adres korespondencyjny: 87-400 Golub-Dobrzyń ul. Żeromskiego 1/2 ______________________________________________________________________ MARTON Sp. z o.o. z siedzibą i adresem we Włocławku 87-800 przy ul. Brzeskiej 27/3, wpisana do Rejestru Przedsiębiorców prowadzonego przez Sąd Rejonowy w Toruniu VII Wydział Gospodarczy Krajowego Rejestru Sądowego pod numerem 0000274112; REGON 340259542, NIP 503-00-32-757; z pokrytym w całości kapitałem zakładowym wynoszącym 15.595.500,00 zł _______________________________________________________________________ Treść tej wiadomości zawiera informacje przeznaczone tylko dla adresata. Jeżeli nie jesteście Państwo jej adresatem, bądź otrzymaliście ją przez pomyłkę, prosimy o powiadomienie nadawcy oraz trwałe jej usunięcie.
uczestnicy (3)
-
Jacek Brzozowski -
Maciej Lew -
Tomasz Chiliński