[mysql5] przesiadka z mysql4

0

Przeniosłem osCommerce-a z serwera z mysql4 na serwer z mysql5 i przestały działać kategorie. Takie zapytanie:

select count(p.products_id) as total from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id LEFT JOIN specials_to_stores s2s ON s.specials_id = s2s.specials_id INNER JOIN products_to_stores p2s ON p.products_id = p2s.products_id where p2s.stores_id = '1' and p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '4' and p2c.categories_id = '50'

zwraca taki komunikat:1054 - Unknown column 'p.products_id' in 'on clause'
Nie znam na tyle mysql-a, żeby wiedzieć co robi to zapytanie, więc tym bardziej nie wiem, co jest w nim nie tak...
Bardzo proszę o pomoc...

może się przyda to:mysql> show columns from products;
+-------------------------+---------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------+---------------+------+-----+---------------------+----------------+
| products_id | int(11) | NO | PRI | NULL | auto_increment |
| products_quantity | int(4) | NO | | 0 | |
| products_model | varchar(12) | YES | | NULL | |
| products_image | varchar(64) | YES | | NULL | |
| products_image_med | varchar(64) | YES | | NULL | |
| products_image_lrg | varchar(64) | YES | | NULL | |
| products_image_sm_1 | varchar(64) | YES | | NULL | |
| products_image_xl_1 | varchar(64) | YES | | NULL | |
| products_image_sm_2 | varchar(64) | YES | | NULL | |
| products_image_xl_2 | varchar(64) | YES | | NULL | |
| products_image_sm_3 | varchar(64) | YES | | NULL | |
| products_image_xl_3 | varchar(64) | YES | | NULL | |
| products_image_sm_4 | varchar(64) | YES | | NULL | |
| products_image_xl_4 | varchar(64) | YES | | NULL | |
| products_image_sm_5 | varchar(64) | YES | | NULL | |
| products_image_xl_5 | varchar(64) | YES | | NULL | |
| products_image_sm_6 | varchar(64) | YES | | NULL | |
| products_image_xl_6 | varchar(64) | YES | | NULL | |
| products_price | decimal(15,4) | NO | | 0.0000 | |
| products_qty_blocks | int(11) | NO | | 1 | |
| products_date_added | datetime | NO | MUL | 0000-00-00 0000 | |
| products_last_modified | datetime | YES | | NULL | |
| products_date_available | datetime | YES | | NULL | |
| products_weight | decimal(5,2) | NO | | 0.00 | |
| products_status | tinyint(1) | NO | | 0 | |
| products_tax_class_id | int(11) | NO | | 0 | |
| manufacturers_id | int(11) | YES | | NULL | |
| distributors_id | int(11) | NO | | 0 | |
| products_ordered | int(11) | NO | | 0 | |
| minorder | int(4) | NO | | 1 | |
+-------------------------+---------------+------+-----+---------------------+----------------+
30 rows in set (0.02 sec)

mysql> show columns from products_description;
+----------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+----------------+
| products_id | int(11) | NO | PRI | NULL | auto_increment |
| language_id | int(11) | NO | PRI | 1 | |
| products_name | varchar(64) | NO | MUL | | |
| products_description | text | YES | | NULL | |
| products_url | varchar(255) | YES | | NULL | |
| products_viewed | int(5) | YES | | 0 | |
+----------------------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

mysql> show columns from products_to_categories;
+---------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
| products_id | int(11) | NO | PRI | 0 | |
| categories_id | int(11) | NO | PRI | 0 | |
+---------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show columns from products_to_stores;
+-------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| products_id | int(11) | NO | PRI | 0 | |
| stores_id | int(11) | NO | PRI | 0 | |
+-------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show columns from specials_to_stores;
+-------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| specials_id | int(11) | NO | PRI | 0 | |
| stores_id | int(11) | NO | PRI | 0 | |
+-------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

0

Powyższe posty są moje - jak jestem w pracy, nie chce mi się logować. A teraz sobie ładnie na nie odpowiem:
MySQL5 jest nie dokońca wstecznie zgodne z MySQL4 :/
trzeba dodać jeden nawias:

SELECT COUNT(p.products_id) AS TOTAL FROM products_description pd, (products p LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id, products_to_categories p2c) LEFT JOIN specials s ON p.products_id = s.products_id  LEFT JOIN specials_to_stores s2s ON s.specials_id = s2s.specials_id INNER JOIN products_to_stores p2s ON p.products_id = p2s.products_id WHERE p2s.stores_id = '1' AND p.products_status = '1' AND p.products_id = p2c.products_id AND pd.products_id = p2c.products_id AND pd.language_id = '4' AND p2c.categories_id = '50';
0

Ciapek jak piszesz SQLa to dawaj code=sql - bo w tym powyższym ciężko się połapać :)

0

OK, nie wiedziałem, że jest kolorowanie SQL-a.
Teraz taka zagadka:

mysql> insert into administrators values ('','admin',PASSWORD('admin'));
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> select * from administrators;
+----------+------------+----------------------------------+
| admin_id | admin_user | admin_pass                       |
+----------+------------+----------------------------------+
|        1 | admin      | *4ACFE3202A5FF5CF467898FC58AAB1D |
+----------+------------+----------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) as LoggedIn from administrators where admin_user='admin' and admin_pass=PASSWORD('admin');
+----------+
| LoggedIn |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

[???]

// już wiem, po drugim zapytaniu

mysql> show warnings;
+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'admin_id' at row 1 |
| Warning | 1265 | Data truncated for column 'admin_pass' at row 1            |
+---------+------+------------------------------------------------------------+
2 rows in set (0.00 sec)
0

DATA truncated FOR COLUMN 'admin_pass' at row 1
Podaj definicję pola 'admin_pass' - ewidentnie jest ono zbyt krótkie i nie wszystko co zwraca PASSWORD('admin') sie w nim mieści.

0
Qyon napisał(a)

DATA truncated FOR COLUMN 'admin_pass' at row 1
Podaj definicję pola 'admin_pass' - ewidentnie jest ono zbyt krótkie i nie wszystko co zwraca PASSWORD('admin') sie w nim mieści.

cytujesz odpowiedź samemu sobie ciapka bo zagadka rozwiązana
a pole za krótkie o 9 znaków było

1 użytkowników online, w tym zalogowanych: 0, gości: 1