Aktualizacja 25.103 > 27.48

[taihen]

Opis błędu Po aktualizacji z wersji 25 to 27 lista klientów generuje błąd o nie istniejącej kolumnie extid.

Zapytanie: SELECT c.id AS id, c.lastname, c.name, UPPER(lastname) || ' ' || c.name AS customername, c.karma, c.type, c.deleted, status, full_address, post_full_address, c.address, c.zip, c.city, countryid, countries.name AS country, cc.email, ccp.phone, ten, ssn, c.info AS info, extid, message, c.divisionid, c.paytime AS paytime, COALESCE(b.balance, 0) AS balance, COALESCE(t.value, 0) AS tariffvalue, s.account, s.warncount, s.online, (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 customerview c LEFT JOIN (SELECT customerid, (array_to_string(array_agg(contact), ',')) AS email FROM customercontacts WHERE (type & 8 > 0) GROUP BY customerid) cc ON cc.customerid = c.id LEFT JOIN (SELECT customerid, (array_to_string(array_agg(contact), ',')) AS phone FROM customercontacts WHERE (type & 5 > 0) GROUP BY customerid) ccp ON ccp.customerid = c.id LEFT JOIN countries ON (c.countryid = countries.id) LEFT JOIN customerbalances 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 WHEN a.period = 0 THEN 0 ELSE ( CASE WHEN a.period <> 0 AND t.period > 0 AND t.period <> a.period THEN ( CASE t.period WHEN 5 THEN 1/12.0 WHEN 7 THEN 1/6.0 WHEN 4 THEN 1/3.0 ELSE 1 END ) ELSE ( CASE a.period WHEN 5 THEN 1/12.0 WHEN 7 THEN 1/6.0 WHEN 4 THEN 1/3.0 WHEN 2 THEN 4.0 WHEN 1 THEN 30.0 ELSE 1 END ) END ) END ) * a.count ) 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.commited = 1 AND a.datefrom <= EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))::integer AND (a.dateto > EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))::integer 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) > EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))::integer - 120 THEN 1 ELSE 0 END) AS online FROM nodes WHERE ownerid > 0 AND ipaddr <> 0 GROUP BY ownerid ) s ON (s.ownerid = c.id) WHERE 1 = 1 ORDER BY customername asc, c.id ASC LIMIT 100 OFFSET 0
Błąd: ERROR: column "extid" does not exist LINE 4: extid, message, c.divisionid, c.paytime AS p... ^

W czasie aktualizacji jedynie wystąpił błąd:

upgradedb.php
(C) 2001-2022 LMS Developers
Using file /etc/lms/lms.ini as config.
Fatal error: encountered corrupted correction note chain for document #11185!

który poprawiłem

UPDATE documents SET type = 6 WHERE type = 3 AND id = 11185;

Ponowne wywołanie upgradedb obdbyło się bez błędów.

Środowisko:

• Debian Buster (10), PHP 7.4
• Wersja LMS+: | 27.48
• Wersja LMSDB: | 2021072926
• Wersja PostgreSQL: | 9.6.24
• Wersja PHP: | 7.4.33
• Wersja Smarty: | 4.2.1

Informacje diagnostyczne

• [ ] Brak błędów w dzienniku zdarzeń bazy danych (dotyczy PostgreSQL). W przypadku błędów prosimy o ich przesłanie.

  2022-11-21 13:31:42.918 CET [17950] lms@lms ERROR:  column "extid" does not exist at character 318
  2022-11-21 13:31:42.918 CET [17950] lms@lms STATEMENT:  SELECT c.id AS id, c.lastname, c.name, UPPER(lastname) || ' ' || c.name AS customername,
                      c.karma, c.type, c.deleted,
                      status, full_address, post_full_address, c.address, c.zip, c.city, countryid, countries.name AS country, cc.email, ccp.phone, ten, ssn, c.info AS info,                                                             
                      extid, message, c.divisionid, c.paytime AS paytime, COALESCE(b.balance, 0) AS balance,
                      COALESCE(t.value, 0) AS tariffvalue, s.account, s.warncount, s.online,
                      (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 customerview c
  
                      LEFT JOIN (SELECT customerid, (array_to_string(array_agg(contact), ',')) AS email
                      FROM customercontacts WHERE (type & 8 > 0) GROUP BY customerid) cc ON cc.customerid = c.id
                      LEFT JOIN (SELECT customerid, (array_to_string(array_agg(contact), ',')) AS phone
                      FROM customercontacts WHERE (type & 5 > 0) GROUP BY customerid) ccp ON ccp.customerid = c.id
                      LEFT JOIN countries ON (c.countryid = countries.id) LEFT JOIN customerbalances 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 WHEN a.period = 0 THEN 0
                                  ELSE (
                                      CASE WHEN a.period <> 0 AND t.period > 0 AND t.period <> a.period THEN (
                                              CASE t.period
                                                  WHEN 5 THEN 1/12.0
                                                  WHEN 7 THEN 1/6.0
                                                  WHEN 4 THEN 1/3.0
                                                  ELSE 1
                                              END
                                          ) ELSE (
                                              CASE a.period
                                                  WHEN 5 THEN 1/12.0
                                                  WHEN 7 THEN 1/6.0
                                                  WHEN 4 THEN 1/3.0
                                                  WHEN 2 THEN 4.0
                                                  WHEN 1 THEN 30.0
                                                  ELSE 1
                                              END
                                          )
                                      END
                                  )
                                  END
                              ) * a.count
                          ) 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.commited = 1 AND a.datefrom <= EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))::integer AND (a.dateto > EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))::integer 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) > EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))::integer - 120
                              THEN 1 ELSE 0 END) AS online
                          FROM nodes
                          WHERE ownerid > 0 AND ipaddr <> 0
                          GROUP BY ownerid
                      ) s ON (s.ownerid = c.id)  WHERE 1 = 1 ORDER BY customername asc, c.id ASC LIMIT 100 OFFSET 0

• [x] Brak błędów w dzienniku zdarzeń serwera www lub daemona php FastCGI (zwykle plik error_log lub ssl_error_log w przypadku Apache/httpd).
• [x] Wykonałem polecenie: rm -f templates_c/* userpanel/templates_c/*.
• [x] Wykonałem polecenie: composer update --no-dev.
• [x] Wyczyściłem pamięć podręczną przeglądarki www lub przeładowałem zawartość www z pominięciem pamięci podręcznej (w Google Chrome skrót klawiszowy SHIFT+F5).
• [x] W konsoli deweloperskiej przeglądarki www nie ma błędów. W przypadku wystąpienia prosimy o ich przesłanie.

[taihen]

@chilek coś gdzieś poszło nie tak, będę wdzięczny za pomoc

[chilek]

@taihen: wykonaj zapytania SQL:

DROP VIEW customerview;
CREATE VIEW customerview AS
    SELECT c.*,
        cc.consentdate AS consentdate,
        cc.invoicenotice AS invoicenotice,
        cc.mailingnotice AS mailingnotice,
        cc.smsnotice AS smsnotice,
        cc.einvoice AS einvoice,
        a1.country_id as countryid, a1.ccode,
        a1.zip as zip, a1.city as city,
        a1.street as street,a1.house as building, a1.flat as apartment,
        a2.country_id as post_countryid, a2.ccode AS post_ccode,
        a2.zip as post_zip,
        a2.city as post_city, a2.street as post_street, a2.name as post_name,
        a2.house as post_building, a2.flat as post_apartment,
        a1.address as address, a1.location AS full_address,
        a1.postoffice AS postoffice,
        a2.address as post_address, a2.location AS post_full_address,
        a2.postoffice AS post_postoffice,
        ce.extid AS extid
    FROM customers c
        JOIN customer_addresses ca1 ON c.id = ca1.customer_id AND ca1.type = 1
        LEFT JOIN vaddresses a1 ON ca1.address_id = a1.id
        LEFT JOIN customer_addresses ca2 ON c.id = ca2.customer_id AND ca2.type = 0
        LEFT JOIN vaddresses a2 ON ca2.address_id = a2.id
        LEFT JOIN customerconsentview cc ON cc.customerid = c.id
        LEFT JOIN customerextids ce ON ce.customerid = c.id AND ce.serviceproviderid IS NULL
    WHERE NOT EXISTS (
        SELECT 1 FROM vcustomerassignments a
        JOIN excludedgroups e ON (a.customergroupid = e.customergroupid)
        WHERE e.userid = lms_current_user() AND a.customerid = c.id)
        AND (lms_current_user() = 0 OR c.divisionid IN (
            SELECT ud.divisionid
            FROM userdivisions ud
            WHERE ud.userid = lms_current_user()))
        AND c.type < 2;

[taihen]

No i po sprawie, zadziałało poprawnie, dzieki.