Questions around the Aimeos bundle for the Symfony framework
Forum rules: Always add your Symfony, Aimeos and PHP version as well as your environment (Linux/Mac/Win)
#1835 by siddharthamahata
01 Sep 2015, 09:39
Yesterday I installed a new Instance of Symfony aimeos bundle. Everything works flawlessly, except the details page . Where aimeos shows an error on details page: A non-recoverable error occured

The error appears in this template block:
{% block aimeos_body %}
{{ aibody['catalog/detail']|raw }}
{% endblock %}

From madmin_log table:
Executing statement "
SELECT DISTINCT mmedli."id", mmedli."parentid", mmedli."siteid",
mmedli."typeid", mmedli."domain", mmedli."refid",
mmedli."start", mmedli."end", mmedli."config", mmedli."pos",
mmedli."status", mmedli."mtime", mmedli."editor",
mmedli."ctime"
FROM "mshop_media_list" AS mmedli

WHERE ( mmedli."siteid" IN (1) AND ( mmedli."parentid" IN (12,13,14,15) AND mmedli."domain" IN ('media','price','text','attribute','product') AND ( mmedli."status" = 1 AND ( mmedli."start" <= '2015-09-01 11:29:00' OR mmedli."start" IS NULL ) AND ( mmedli."end" >= '2015-09-01 11:29:00' OR mmedli."end" IS NULL ) ) ) )

LIMIT 2147483647 OFFSET 0
" failed: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'testaimeos.mshop_media_list' doesn't exist
#0 C:\xampp\htdocs\testaimeos\vendor\aimeos\aimeos-core\lib\mshoplib\src\MShop\Common\Manager\Abstract.php(837): MW_DB_Statement_PDO_Simple->execute()
#1 C:\xampp\htdocs\testaimeos\vendor\aimeos\aimeos-core\lib\mshoplib\src\MShop\Common\Manager\List\Abstract.php(360): MShop_Common_Manager_Abstract->_searchItems(Object(MW_DB_Connection_PDO), Object(MW_Common_Criteria_SQL), 'mshop/media/man...', 'mshop/media/man...', Array, NULL, 3)
#2 C:\xampp\htdocs\testaimeos\vendor\aimeos\aimeos-core\lib\mshoplib\src\MShop\Common\Manager\Decorator\Abstract.php(168): MShop_Common_Manager_List_Abstract->searchItems(Object(MW_Common_Criteria_SQL), Array, NULL)
#3 C:\xampp\htdocs\testaimeos\vendor\aimeos\aimeos-core\lib\mshoplib\src\MShop\Common\Manager\ListRef\Abstract.php(102): MShop_Common_Manager_Decorator_Abstract->searchItems(Object(MW_Common_Criteria_SQL))
#4 C:\xampp\htdocs\testaimeos\vendor\aimeos\aimeos-core\lib\mshoplib\src\MShop\Common\Manager\ListRef\Abstract.php(46): MShop_Common_Manager_ListRef_Abstract->_getListItems(Array, Array, 'media')
#5 C:\xampp\htdocs\testaimeos\vendor\aimeos\aimeos-core\lib\mshoplib\src\MShop\Media\Manager\Default.php(553): MShop_Common_Manager_ListRef_Abstract->_buildItems(Array, Array, 'media')
#6 C:\xampp\htdocs\testaimeos\vendor\aimeos\aimeos-core\lib\mshoplib\src\MShop\Common\Manager\Decorator\Abstract.php(168): MShop_Media_Manager_Default->searchItems(Object(MW_Common_Criteria_SQL), Array, NULL)
#7 C:\xampp\htdocs\testaimeos\vendor\aimeos\aimeos-core\client\html\src\Client\Html\Catalog\Detail\Default.php(517): MShop_Common_Manager_Decorator_Abstract->searchItems(Object(MW_Common_Criteria_SQL), Array)
#8 C:\xampp\htdocs\testaimeos\vendor\aimeos\aimeos-core\client\html\src\Client\Html\Catalog\Detail\Default.php(289): Client_Html_Catalog_Detail_Default->_setViewParams(Object(MW_View_Default), Array, NULL)
#9 C:\xampp\htdocs\testaimeos\vendor\aimeos\aimeos-symfony2\Service\Page.php(62): Client_Html_Catalog_Detail_Default->getHeader()
#10 C:\xampp\htdocs\testaimeos\vendor\aimeos\aimeos-symfony2\Controller\CatalogController.php(43): Aimeos\ShopBundle\Service\Page->getSections('catalog-detail')
#11 [internal function]: Aimeos\ShopBundle\Controller\CatalogController->detailAction()
#12 C:\xampp\htdocs\testaimeos\app\bootstrap.php.cache(3109): call_user_func_array(Array, Array)
#13 C:\xampp\htdocs\testaimeos\app\bootstrap.php.cache(3071): Symfony\Component\HttpKernel\HttpKernel->handleRaw(Object(Symfony\Component\HttpFoundation\Request), 1)
#14 C:\xampp\htdocs\testaimeos\app\bootstrap.php.cache(3222): Symfony\Component\HttpKernel\HttpKernel->handle(Object(Symfony\Component\HttpFoundation\Request), 1, true)
#15 C:\xampp\htdocs\testaimeos\app\bootstrap.php.cache(2444): Symfony\Component\HttpKernel\DependencyInjection\ContainerAwareHttpKernel->handle(Object(Symfony\Component\HttpFoundation\Request), 1, true)
#16 C:\xampp\htdocs\testaimeos\web\app_dev.php(28): Symfony\Component\HttpKernel\Kernel->handle(Object(Symfony\Component\HttpFoundation\Request))
#17 {main}

I couldn't solve the error. Please help .
Thank You
Attachments
aimeos_details_error.jpg
aimeos_details_error.jpg (85.19 KiB) Viewed 2192 times
#1836 by aimeos
01 Sep 2015, 09:56
siddharthamahata wrote:From madmin_log table:
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'testaimeos.mshop_media_list' doesn't exist


You somehow deleted the mshop_media_list table from your database. To recreate it, run:
Code: Select allphp app/console aimeos:setup


Another option might be you've activated PHP persistent database connections. We had problems with this too which resulted in accessing the wrong database. Please check if you have something like this in your config file
Code: Select allresource:
    db:
        ...
        opt-persistent: 1

If yes, replace it with "opt-persistent: 0".
#1838 by siddharthamahata
01 Sep 2015, 10:16
Sir.
This error is a bit wierd. I ran the setup and it worked fine.
But the table mshop_media_list did not get created.
It was not deleted, but somehow not being created during the setup procedure.
The setup lines say OK on checking mshop_media_list in the console window.

I am sorry for the trouble.
Can you tell me the structure of mshop_media_list , so i can create it manually.
Or if you can send me the sql command to create it.
It would be of great help.


The other option is as it should be, "opt-persistent: 0".

Thank You
#1839 by aimeos
01 Sep 2015, 10:42
siddharthamahata wrote:This error is a bit wierd. I ran the setup and it worked fine.
But the table mshop_media_list did not get created.
It was not deleted, but somehow not being created during the setup procedure.
The setup lines say OK on checking mshop_media_list in the console window.


If it's not there, it should output "created" instead.
Can you please run aimeos:setup on an empty database if it happens there too?

siddharthamahata wrote:Can you tell me the structure of mshop_media_list , so i can create it manually.
Or if you can send me the sql command to create it.


You shouldn't create the table by hand as this may only lead to problems later when changes should be applied to the table. If you can help to find the root cause, we can fix the underlying problem. How many mshop_* tables do you have in your database and in the new one? Is there a difference?

Thank you very much
#1840 by siddharthamahata
01 Sep 2015, 11:23
I created a new database and ran the aimeos setup there, but it still misses the creation of mshop_media_list.
It creates 62 tables in total.
I cannot compare my old database with this new one , as i dont have it right now.
It will take a few hours for me to go home and check.

I guess i am missing something. I will surely check my old db and report asap.

Thank you.
#1841 by aimeos
01 Sep 2015, 11:39
siddharthamahata wrote:I created a new database and ran the aimeos setup there, but it still misses the creation of mshop_media_list.
It creates 62 tables in total.


Hm, that's not enough. It should be 71 in total, 4 starting with "fos_user", 4 with "madmin_" and 63 with "mshop_" or is your total for the "mshop_" tables only?

Here are the tables that should be created. Can you please post your table list too?
Code: Select allmysql> show tables;                       
+-------------------------------+
| Tables_in_symfony             |
+-------------------------------+
| fos_user                      |
| fos_user_address              |
| fos_user_list                 |
| fos_user_list_type            |
| madmin_cache                  |
| madmin_cache_tag              |
| madmin_job                    |
| madmin_log                    |
| mshop_attribute               |
| mshop_attribute_list          |
| mshop_attribute_list_type     |
| mshop_attribute_type          |
| mshop_catalog                 |
| mshop_catalog_index_attribute |
| mshop_catalog_index_catalog   |
| mshop_catalog_index_price     |
| mshop_catalog_index_text      |
| mshop_catalog_list            |
| mshop_catalog_list_type       |
| mshop_coupon                  |
| mshop_coupon_code             |
| mshop_customer                |
| mshop_customer_address        |
| mshop_customer_group          |
| mshop_customer_list           |
| mshop_customer_list_type      |
| mshop_locale                  |
| mshop_locale_currency         |
| mshop_locale_language         |
| mshop_locale_site             |
| mshop_media                   |
| mshop_media_list              |
| mshop_media_list_type         |
| mshop_media_type              |
| mshop_order                   |
| mshop_order_base              |
| mshop_order_base_address      |
| mshop_order_base_coupon       |
| mshop_order_base_product      |
| mshop_order_base_product_attr |
| mshop_order_base_service      |
| mshop_order_base_service_attr |
| mshop_order_status            |
| mshop_plugin                  |
| mshop_plugin_type             |
| mshop_price                   |
| mshop_price_list              |
| mshop_price_list_type         |
| mshop_price_type              |
| mshop_product                 |
| mshop_product_list            |
| mshop_product_list_type       |
| mshop_product_property        |
| mshop_product_property_type   |
| mshop_product_stock           |
| mshop_product_stock_warehouse |
| mshop_product_tag             |
| mshop_product_tag_type        |
| mshop_product_type            |
| mshop_service                 |
| mshop_service_list            |
| mshop_service_list_type       |
| mshop_service_type            |
| mshop_supplier                |
| mshop_supplier_address        |
| mshop_supplier_list           |
| mshop_supplier_list_type      |
| mshop_text                    |
| mshop_text_list               |
| mshop_text_list_type          |
| mshop_text_type               |
+-------------------------------+
71 rows in set (0.02 sec)


Can you execute this in your database once (the "set session ..." first please):
Code: Select allset session sql_mode='ansi';

CREATE TABLE "mshop_media_list" (
   -- Unique list id
   "id" INTEGER NOT NULL AUTO_INCREMENT,
   -- media id (parent id)
   "parentid" INTEGER NOT NULL,
   -- site id, references mshop_locale_site.id
   "siteid" INTEGER NOT NULL,
   -- typeid
   "typeid" INTEGER NOT NULL,
   -- domain (e.g.: text, attribute)
   "domain" VARCHAR(32) NOT NULL,
   -- Reference of the object in given domain
   "refid" VARCHAR(32) NOT NULL,
   -- Valid from
   "start" DATETIME DEFAULT NULL,
   -- Valid until
   "end" DATETIME DEFAULT NULL,
   -- Configuration
   "config" TEXT NOT NULL,
   -- Precedence of the promotion
   "pos" INTEGER NOT NULL,
   -- status code (0=hidden, 1=display, >1 for anything special)
   "status" SMALLINT NOT NULL DEFAULT 0,
   -- Date of last modification of this database entry
   "mtime" DATETIME NOT NULL,
   -- Date of creation of this database entry
   "ctime" DATETIME NOT NULL,
   -- Editor who modified this entry at last
   "editor" VARCHAR(255) NOT NULL,
CONSTRAINT "pk_msmedli_id"
   PRIMARY KEY ("id"),
CONSTRAINT "unq_msmedli_sid_dm_rid_tid_pid"
   UNIQUE ("siteid", "domain", "refid", "typeid", "parentid"),
CONSTRAINT "fk_msmedli_pid"
   FOREIGN KEY ("parentid")
   REFERENCES "mshop_media" ("id")
   ON UPDATE CASCADE
   ON DELETE CASCADE,
CONSTRAINT "fk_msmedli_typeid"
   FOREIGN KEY ( "typeid" )
   REFERENCES "mshop_media_list_type" ("id")
   ON DELETE CASCADE
   ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#1842 by siddharthamahata
01 Sep 2015, 12:29
I executed the sql syntax given in the db. but it returns an error.
ERROR:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"mshop_media_list" (
-- Unique list id
"id" INTEGER NOT NULL AUTO_INCREM' at line 1

I guess the mysql dosent match with the version required .
in Php myadmin it shows:
Server version: 5.6.21 - MySQL Community Server (GPL)
Apache/2.4.10 (Win32) OpenSSL/1.0.1i PHP/5.6.3
Database client version: libmysql - mysqlnd 5.0.11-dev - 20120503


The count 62 tables is the complete list of tables.

fos_user
fos_user_address
fos_user_list
fos_user_list_type
madmin_cache
madmin_cache_tag
madmin_job
madmin_log
mshop_attribute
mshop_attribute_list
mshop_attribute_list_type
mshop_attribute_type
mshop_catalog
mshop_catalog_index_attribute
mshop_catalog_index_catalog
mshop_catalog_index_price
mshop_catalog_index_text
mshop_catalog_list
mshop_catalog_list_type
mshop_coupon
mshop_coupon_code
mshop_customer
mshop_customer_address
mshop_locale
mshop_locale_currency
mshop_locale_language
mshop_locale_site
mshop_media
mshop_media_type
mshop_order
mshop_order_base
mshop_order_base_address
mshop_order_base_coupon
mshop_order_base_product
mshop_order_base_product_attr
mshop_order_base_service
mshop_order_base_service_attr
mshop_order_status
mshop_plugin
mshop_plugin_type
mshop_price
mshop_price_type
mshop_product
mshop_product_list
mshop_product_list_type
mshop_product_property
mshop_product_property_type
mshop_product_stock
mshop_product_stock_warehouse
mshop_product_tag
mshop_product_tag_type
mshop_product_type
mshop_service
mshop_service_list
mshop_service_list_type
mshop_service_type
mshop_supplier
mshop_supplier_address
mshop_supplier_list
mshop_supplier_list_type
mshop_text
mshop_text_type
#1843 by aimeos
01 Sep 2015, 12:43
siddharthamahata wrote:#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"mshop_media_list" (
-- Unique list id
"id" INTEGER NOT NULL AUTO_INCREM' at line 1


When using phpmyadmin, you have to execute the "set session ..." statement along with the "create table ..." statement:
Code: Select allSET SESSION SQL_MODE='ANSI';
CREATE TABLE "mshop_media_list_type" (
   -- Unique id
   "id" INTEGER NOT NULL AUTO_INCREMENT,
   -- site id, references mshop_locale_site.id
   "siteid" INTEGER NOT NULL,
   -- domain
   "domain" VARCHAR(32) NOT NULL,
   -- code
   "code"  VARCHAR(32) NOT NULL COLLATE utf8_bin,
   -- Name of the list type
   "label" VARCHAR(255) NOT NULL,
   -- Status (0=disabled, 1=enabled, >1 for special)
   "status" SMALLINT NOT NULL,
   -- Date of last modification of this database entry
   "mtime" DATETIME NOT NULL,
   -- Date of creation of this database entry
   "ctime" DATETIME NOT NULL,
   -- Editor who modified this entry at last
   "editor" VARCHAR(255) NOT NULL,
CONSTRAINT "pk_msmedlity_id"
   PRIMARY KEY ("id"),
CONSTRAINT "unq_msmedlity_sid_dom_code"
   UNIQUE ("siteid", "domain", "code")
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE "mshop_media_list" (
   -- Unique list id
   "id" INTEGER NOT NULL AUTO_INCREMENT,
   -- media id (parent id)
   "parentid" INTEGER NOT NULL,
   -- site id, references mshop_locale_site.id
   "siteid" INTEGER NOT NULL,
   -- typeid
   "typeid" INTEGER NOT NULL,
   -- domain (e.g.: text, attribute)
   "domain" VARCHAR(32) NOT NULL,
   -- Reference of the object in given domain
   "refid" VARCHAR(32) NOT NULL,
   -- Valid from
   "start" DATETIME DEFAULT NULL,
   -- Valid until
   "end" DATETIME DEFAULT NULL,
   -- Configuration
   "config" TEXT NOT NULL,
   -- Precedence of the promotion
   "pos" INTEGER NOT NULL,
   -- status code (0=hidden, 1=display, >1 for anything special)
   "status" SMALLINT NOT NULL DEFAULT 0,
   -- Date of last modification of this database entry
   "mtime" DATETIME NOT NULL,
   -- Date of creation of this database entry
   "ctime" DATETIME NOT NULL,
   -- Editor who modified this entry at last
   "editor" VARCHAR(255) NOT NULL,
CONSTRAINT "pk_msmedli_id"
   PRIMARY KEY ("id"),
CONSTRAINT "unq_msmedli_sid_dm_rid_tid_pid"
   UNIQUE ("siteid", "domain", "refid", "typeid", "parentid"),
CONSTRAINT "fk_msmedli_pid"
   FOREIGN KEY ("parentid")
   REFERENCES "mshop_media" ("id")
   ON UPDATE CASCADE
   ON DELETE CASCADE,
CONSTRAINT "fk_msmedli_typeid"
   FOREIGN KEY ( "typeid" )
   REFERENCES "mshop_media_list_type" ("id")
   ON DELETE CASCADE
   ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


siddharthamahata wrote:The count 62 tables is the complete list of tables.


In your database, half of the list type and list tables are missing:
- attribute list/list type : OK
- catalog list/list type : OK
- customer list/list type : missing
- media list/list type : missing
- price list/list type : missing
- product list/list type : OK
- service list/list type : OK
- text list/list type : missing

Which version of the Aimeos Symfony bundle are you using? 1.1.x?
#1844 by siddharthamahata
01 Sep 2015, 12:59
symfony version - 2.7.3
"aimeos/aimeos-symfony2": "dev-master"

how to check the exact version of aimeos?

I ran the sql statement as you said. i.e along with set session , and it worked .
It succesfully created two tables ,
1. mshop_media_list
2. mshop_media_list_type
#1845 by nos3
01 Sep 2015, 13:42
siddharthamahata wrote:how to check the exact version of aimeos?


The only way I know for dev-master is to look into the composer.lock file.

siddharthamahata wrote:I ran the sql statement as you said. i.e along with set session , and it worked .
It succesfully created two tables ,
1. mshop_media_list
2. mshop_media_list_type


Very strange ...
Can you please execute this statement and tell me what is the result? Please replace "<dbname>" by the name of the database you've installed Aimeos in.
Code: Select allSELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
   AND TABLE_SCHEMA = '<dbname>'
   AND TABLE_NAME = 'mshop_price_list'