[mysql5] przesiadka z mysql4

Odpowiedz Nowy wątek
2006-12-28 17:37
:[
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 00:00:00 | |
| 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)

Pozostało 580 znaków

2006-12-28 22:40
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';

It's not just about livin' forever Johny.
The trick is livin' with yourself forever.

Pozostało 580 znaków

2006-12-30 10:45
0

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


- Ciemna druga strona jest.
- Nie marudź Yoda, tylko jedz tego tosta.
Google NIE GRYZIE!
Pomogłem - kliknij

Pozostało 580 znaków

2006-12-30 22:14
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)

It's not just about livin' forever Johny.
The trick is livin' with yourself forever.

Pozostało 580 znaków

2007-01-01 15:20
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.


JKM czy HGW?

Pozostało 580 znaków

2007-01-01 19:08
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


Dwie zasady gwarantujące sukces:
1. Nigdy nie mów nikomu wszystkiego<span style="color: white">

Pozostało 580 znaków

Odpowiedz
Liczba odpowiedzi na stronę

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