W dniu 6 lutego 2018 06:51 użytkownik Rafał Z. zajbox@gmail.com napisał:

Cześć

Rozwiązaniem jest usunięcie zbędnych indeksów z tabeli nodes - nodes_ifbk_5 oraz nodes_ifbk_6 (alter table nodes drop foreign key nodes_ibfk_X) oraz zbędnych kolumn które mają w nazwie "location*".

z tymi "location*" masz na myśli tabele nodes?

MariaDB [lms]> DESCRIBE nodes; +-----------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(32) | NO | UNI | | | | ipaddr | int(16) unsigned | NO | MUL | 0 | | | ipaddr_pub | int(16) unsigned | NO | MUL | 0 | | | passwd | varchar(32) | NO | | | | | ownerid | int(11) | YES | MUL | NULL | | | creationdate | int(11) | NO | | 0 | | | moddate | int(11) | NO | | 0 | | | creatorid | int(11) | YES | MUL | NULL | | | modid | int(11) | YES | MUL | NULL | | | netdev | int(11) | YES | MUL | NULL | | | linktype | tinyint(1) | NO | | 0 | | | access | tinyint(1) | NO | | 1 | | | warning | tinyint(1) | NO | | 0 | | | lastonline | int(11) | NO | | 0 | | | info | text | NO | | NULL | | | chkmac | tinyint(1) | NO | | 1 | | | halfduplex | tinyint(1) | NO | | 0 | | | port | smallint(6) | NO | | 0 | | | nas | tinyint(1) | NO | | 0 | | | longitude | decimal(10,6) | YES | | NULL | | | latitude | decimal(10,6) | YES | | NULL | | | linkspeed | int(11) | NO | | 100000 | | | netid | int(11) | NO | MUL | 0 | | | linktechnology | int(11) | NO | | 0 | | | invprojectid | int(11) | YES | MUL | NULL | | | linkradiosector | int(11) | YES | MUL | NULL | | | authtype | tinyint(4) | NO | MUL | 0 | | | address_id | int(11) | YES | MUL | NULL | | +-----------------+------------------+------+-----+---------+----------------+ 29 rows in set (0.00 sec)

nie mam takich kolumn a błąd jest

Wystąpiły błędy w obsłudze bazy danych! Zapytanie: SELECT c.id AS id, c.lastname, c.name, CONCAT(UPPER(lastname), ' ', c.name) AS customername, c.type, status, full_address, address, zip, 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.value, 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, (GROUP_CONCAT(contact SEPARATOR ',')) AS email FROM customercontacts WHERE (type & 8 > 0) GROUP BY customerid) cc ON cc.customerid = c.id LEFT JOIN (SELECT customerid, (GROUP_CONCAT(contact SEPARATOR ',')) 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 (SELECT SUM(value) AS value, customerid FROM cash GROUP BY customerid ) 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.commited = 1 AND a.datefrom <= UNIX_TIMESTAMP() 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 AND ipaddr <> 0 GROUP BY ownerid ) s ON (s.ownerid = c.id) WHERE c.deleted = 0 AND (CONCAT(UPPER(c.lastname), ' ', UPPER(c.name)) LIKE UPPER('%kow%') OR (UPPER(address) LIKE UPPER('%kow%') OR UPPER(post_address) LIKE UPPER('%kow%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON va.id = ca.address_id AND ca.customer_id = c.id WHERE UPPER(va.address) LIKE UPPER('%kow%'))) OR (UPPER(zip) LIKE UPPER('%kow%') OR UPPER(post_zip) LIKE UPPER('%kow%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON va.id = ca.address_id AND ca.customer_id = c.id WHERE UPPER(va.zip) LIKE UPPER('%kow%'))) OR (UPPER(city) LIKE UPPER('%kow%') OR UPPER(post_city) LIKE UPPER('%kow%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON va.id = ca.address_id AND ca.customer_id = c.id WHERE UPPER(va.city) LIKE UPPER('%kow%'))) OR EXISTS (SELECT 1 FROM customercontacts WHERE customerid = c.id AND customercontacts.type & 8 = 8 AND contact LIKE '%kow%')) ORDER BY customername asc Błąd: Unknown column 'c.id' in 'on clause'

Przed tą operacją upewnij się czy dane zostały dobrze przeniesone do odpowiednich nowych tabel.

Pozdrawiam Rafał

W dniu 4 lutego 2018 23:43 użytkownik Michał mr_cygi@wp.pl napisał:

...

Hej,

dokładnie mam taki błąd:

Napotkano błędy w bazie danych!

Zapytanie: SELECT COUNT(CASE WHEN access=1 THEN 1 END) AS connected, COUNT(CASE WHEN access=0 THEN 1 END) AS disconnected, COUNT(CASE WHEN UNIX_TIMESTAMP()-lastonline < '600' THEN 1 END) AS online, COUNT(CASE WHEN location_city IS NULL THEN 1 END) AS withoutterryt, COUNT(CASE WHEN netdev IS NULL THEN 1 END) AS withoutnetdev, COUNT(CASE WHEN warning = 1 THEN 1 END) AS withwarning FROM vnodes JOIN customerview c ON c.id = ownerid WHERE ownerid IS NOT NULL

*Błąd: Table 'lms.vnodes' doesn't exist*

Jak próbuję utworzyć widok wg doc/lms.mysql to otrzymuję błąd:

mysql> CREATE VIEW vnodes AS

->     SELECT n.*, m.mac,

->         a.city_id as location_city, a.street_id as location_street,

->         a.house as location_house, a.flat as location_flat,

a.location

->     FROM nodes n

->         LEFT JOIN vnodes_mac m ON (n.id = m.nodeid)

->         LEFT JOIN vaddresses a ON n.address_id = a.id

->     WHERE n.ipaddr <> 0 OR n.ipaddr_pub <> 0;

*ERROR 1060 (42S21): Duplicate column name 'location_city'*

Może to coś pomoże?

pzdr

Dnia Piątek, 2 Lutego 2018 15:04 Marcin marcin@nicram.net napisał(a)

Hej Nie mogę tego potwierdzić. jedyne błędy jakie podczas aktualizacji wywala to:

Wystąpiły błędy w obsłudze bazy danych! Zapytanie: ALTER TABLE assignments DROP FOREIGN KEY recipient_address_id_fk2 Błąd: Can't DROP 'recipient_address_id_fk2'; check that column/key exists Zapytanie: ALTER TABLE assignments DROP KEY recipient_address_id_fk2 Błąd: Can't DROP 'recipient_address_id_fk2'; check that column/key exists

dbver 2017030100 <(201)%20703-0100>

A jest to aktualizacja z naprawdę starej bazy :/

W dniu 2 lutego 2018 08:21 użytkownik Michał mr_cygi@wp.pl napisał:

...

Drodzy,

po wczorajszej instalacji lms z git (git clone) mam błąd na głównej stronie po zalogowaniu:

Table 'lms.vnodes' doesn't exist

Czego dotyczy vnodes i jak można ten błąd naprawić?

Pozdrawiam.

---

lms mailing list lms@lists.lms.org.pl http://lists.lms.org.pl/mailman/listinfo/lms

-- Pozdrawiam Marcin / nicraM

---

lms mailing list 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

*@marcin[at]nicram.net http://nicram.net*: Ty masz inny błąd:

*Błąd: Unknown column 'c.id http://c.id' in 'on clause'* Z tym się akurat nie zetknąłem ale spróbowałbym na Twoim miejscu zrobić dropa na wszystkich widokach i pozakładać je od nowa według właściwej kolejności.

Pozdrawiam Rafał

2018-02-06 14:36 GMT+01:00 Marcin marcin@nicram.net:

W dniu 6 lutego 2018 06:51 użytkownik Rafał Z. zajbox@gmail.com napisał:

...

Cześć

Rozwiązaniem jest usunięcie zbędnych indeksów z tabeli nodes - nodes_ifbk_5 oraz nodes_ifbk_6 (alter table nodes drop foreign key nodes_ibfk_X) oraz zbędnych kolumn które mają w nazwie "location*".

z tymi "location*" masz na myśli tabele nodes?

MariaDB [lms]> DESCRIBE nodes; +-----------------+------------------+------+-----+--------- +----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+------------------+------+-----+--------- +----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(32) | NO | UNI | | | | ipaddr | int(16) unsigned | NO | MUL | 0 | | | ipaddr_pub | int(16) unsigned | NO | MUL | 0 | | | passwd | varchar(32) | NO | | | | | ownerid | int(11) | YES | MUL | NULL | | | creationdate | int(11) | NO | | 0 | | | moddate | int(11) | NO | | 0 | | | creatorid | int(11) | YES | MUL | NULL | | | modid | int(11) | YES | MUL | NULL | | | netdev | int(11) | YES | MUL | NULL | | | linktype | tinyint(1) | NO | | 0 | | | access | tinyint(1) | NO | | 1 | | | warning | tinyint(1) | NO | | 0 | | | lastonline | int(11) | NO | | 0 | | | info | text | NO | | NULL | | | chkmac | tinyint(1) | NO | | 1 | | | halfduplex | tinyint(1) | NO | | 0 | | | port | smallint(6) | NO | | 0 | | | nas | tinyint(1) | NO | | 0 | | | longitude | decimal(10,6) | YES | | NULL | | | latitude | decimal(10,6) | YES | | NULL | | | linkspeed | int(11) | NO | | 100000 | | | netid | int(11) | NO | MUL | 0 | | | linktechnology | int(11) | NO | | 0 | | | invprojectid | int(11) | YES | MUL | NULL | | | linkradiosector | int(11) | YES | MUL | NULL | | | authtype | tinyint(4) | NO | MUL | 0 | | | address_id | int(11) | YES | MUL | NULL | | +-----------------+------------------+------+-----+--------- +----------------+ 29 rows in set (0.00 sec)

nie mam takich kolumn a błąd jest

Wystąpiły błędy w obsłudze bazy danych! Zapytanie: SELECT c.id AS id, c.lastname, c.name, CONCAT(UPPER(lastname), ' ', c.name) AS customername, c.type, status, full_address, address, zip, 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.value, 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, (GROUP_CONCAT(contact SEPARATOR ',')) AS email FROM customercontacts WHERE (type & 8 > 0) GROUP BY customerid) cc ON cc.customerid = c.id LEFT JOIN (SELECT customerid, (GROUP_CONCAT(contact SEPARATOR ',')) 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 (SELECT SUM(value) AS value, customerid FROM cash GROUP BY customerid ) 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.commited = 1 AND a.datefrom <= UNIX_TIMESTAMP() 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 AND ipaddr <> 0 GROUP BY ownerid ) s ON (s.ownerid = c.id) WHERE c.deleted = 0 AND (CONCAT(UPPER(c.lastname), ' ', UPPER(c.name)) LIKE UPPER('%kow%') OR (UPPER(address) LIKE UPPER('%kow%') OR UPPER(post_address) LIKE UPPER('%kow%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON va.id = ca.address_id AND ca.customer_id = c.id WHERE UPPER(va.address) LIKE UPPER('%kow%'))) OR (UPPER(zip) LIKE UPPER('%kow%') OR UPPER(post_zip) LIKE UPPER('%kow%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON va.id = ca.address_id AND ca.customer_id = c.id WHERE UPPER(va.zip) LIKE UPPER('%kow%'))) OR (UPPER(city) LIKE UPPER('%kow%') OR UPPER(post_city) LIKE UPPER('%kow%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON va.id = ca.address_id AND ca.customer_id = c.id WHERE UPPER(va.city) LIKE UPPER('%kow%'))) OR EXISTS (SELECT 1 FROM customercontacts WHERE customerid = c.id AND customercontacts.type & 8 = 8 AND contact LIKE '%kow%')) ORDER BY customername asc Błąd: Unknown column 'c.id' in 'on clause'

...

Przed tą operacją upewnij się czy dane zostały dobrze przeniesone do odpowiednich nowych tabel.

Pozdrawiam Rafał

W dniu 4 lutego 2018 23:43 użytkownik Michał mr_cygi@wp.pl napisał:

...

Hej,

dokładnie mam taki błąd:

Napotkano błędy w bazie danych!

Zapytanie: SELECT COUNT(CASE WHEN access=1 THEN 1 END) AS connected, COUNT(CASE WHEN access=0 THEN 1 END) AS disconnected, COUNT(CASE WHEN UNIX_TIMESTAMP()-lastonline < '600' THEN 1 END) AS online, COUNT(CASE WHEN location_city IS NULL THEN 1 END) AS withoutterryt, COUNT(CASE WHEN netdev IS NULL THEN 1 END) AS withoutnetdev, COUNT(CASE WHEN warning = 1 THEN 1 END) AS withwarning FROM vnodes JOIN customerview c ON c.id = ownerid WHERE ownerid IS NOT NULL

*Błąd: Table 'lms.vnodes' doesn't exist*

Jak próbuję utworzyć widok wg doc/lms.mysql to otrzymuję błąd:

mysql> CREATE VIEW vnodes AS

->     SELECT n.*, m.mac,

->         a.city_id as location_city, a.street_id as

location_street,

->         a.house as location_house, a.flat as location_flat,

a.location

->     FROM nodes n

->         LEFT JOIN vnodes_mac m ON (n.id = m.nodeid)

->         LEFT JOIN vaddresses a ON n.address_id = a.id

->     WHERE n.ipaddr <> 0 OR n.ipaddr_pub <> 0;

*ERROR 1060 (42S21): Duplicate column name 'location_city'*

Może to coś pomoże?

pzdr

Dnia Piątek, 2 Lutego 2018 15:04 Marcin marcin@nicram.net napisał(a)

Hej Nie mogę tego potwierdzić. jedyne błędy jakie podczas aktualizacji wywala to:

Wystąpiły błędy w obsłudze bazy danych! Zapytanie: ALTER TABLE assignments DROP FOREIGN KEY recipient_address_id_fk2 Błąd: Can't DROP 'recipient_address_id_fk2'; check that column/key exists Zapytanie: ALTER TABLE assignments DROP KEY recipient_address_id_fk2 Błąd: Can't DROP 'recipient_address_id_fk2'; check that column/key exists

dbver 2017030100 <(201)%20703-0100>

A jest to aktualizacja z naprawdę starej bazy :/

W dniu 2 lutego 2018 08:21 użytkownik Michał mr_cygi@wp.pl napisał:

...

Drodzy,

po wczorajszej instalacji lms z git (git clone) mam błąd na głównej stronie po zalogowaniu:

Table 'lms.vnodes' doesn't exist

Czego dotyczy vnodes i jak można ten błąd naprawić?

Pozdrawiam.

---

lms mailing list lms@lists.lms.org.pl http://lists.lms.org.pl/mailman/listinfo/lms

-- Pozdrawiam Marcin / nicraM

---

lms mailing list 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

-- Pozdrawiam Marcin / nicraM

---

lms mailing list lms@lists.lms.org.pl http://lists.lms.org.pl/mailman/listinfo/lms