codefog/contao-news_categories

Database error in a new installation

Closed this issue · 20 comments

Hi,
i have several problems with this bundle at a new installation. This is what i am doing:

Install codefog/contao-news_categories at contao 4.9.7
Migrate the database with the contao console

Now i can add categories at the news module.

The first issue is when i try to limit the categories at tl_news_archive. I select categories inside the popup, but on apply i get an SQL error

An exception occurred while executing 'SELECT IFNULL(translation.id, tl_news_category.id) AS id, IFNULL(translation.language, tl_news_category.language) AS language, IFNULL(translation.lid, tl_news_category.lid) AS lid, tl_news_category.pid, tl_news_category.sorting, tl_news_category.tstamp, tl_news_category.cssClass, tl_news_category.image, tl_news_category.hideInList, tl_news_category.hideInReader, tl_news_category.excludeInRelated, tl_news_category.jumpTo, tl_news_category.published, IFNULL(translation.title, tl_news_category.title) AS title, IFNULL(translation.frontendTitle, tl_news_category.frontendTitle) AS frontendTitle, IFNULL(translation.alias, tl_news_category.alias) AS alias, IFNULL(translation.description, tl_news_category.description) AS description FROM tl_news_category tl_news_category LEFT OUTER JOIN tl_news_category translation ON tl_news_category.id=translation.lid AND translation.language='en' WHERE (tl_news_category.lid=0) AND (t1.id IN (1))':

SQLSTATE[42S22]: Column not found: 1054 Unknown column 't1.id' in 'where clause'

The next issue is when i click to edit the news archive to add news items:

An exception occurred while executing 'SELECT DISTINCT `categories` FROM tl_news WHERE pid='3'':

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'categories' in 'field list'

There is no column categories at tl_news and the sql statement is missing at the dca

Is there something i dont see right now or something is missing?
Thanks for your help
Martin

Did you run the Contao Install Tool and update the database (or run contao:migrate), after you installed the extension?

Yes.. the migration does the following:

Pending database migrations
---------------------------

 * CREATE TABLE tl_news_category (id INT UNSIGNED AUTO_INCREMENT NOT NULL, pid INT UNSIGNED DEFAULT 0 NOT NULL, sorting INT UNSIGNED DEFAULT 0 NOT NULL, tstamp INT UNSIGNED DEFAULT 0 NOT NULL, title VARCHAR(255) DEFAULT '' NOT NULL, frontendTitle VARCHAR(64) DEFAULT '' NOT NULL, alias VARBINARY(128) DEFAULT '' NOT NULL, cssClass VARCHAR(255) DEFAULT '' NOT NULL, description LONGTEXT DEFAULT NULL, image VARBINARY(16) DEFAULT NULL, hideInList TINYINT(1) DEFAULT '0' NOT NULL, hideInReader TINYINT(1) DEFAULT '0' NOT NULL, excludeInRelated TINYINT(1) DEFAULT '0' NOT NULL, jumpTo INT UNSIGNED DEFAULT 0 NOT NULL, published TINYINT(1) DEFAULT '0' NOT NULL, language VARCHAR(5) DEFAULT '' NOT NULL, lid INT UNSIGNED DEFAULT 0 NOT NULL, INDEX pid (pid), INDEX alias (alias), INDEX language_lid (language, lid), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB ROW_FORMAT = DYNAMIC
 * CREATE TABLE tl_news_categories (news_id INT UNSIGNED DEFAULT 0 NOT NULL, category_id INT UNSIGNED DEFAULT 0 NOT NULL, UNIQUE INDEX news_id_category_id (news_id, category_id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB ROW_FORMAT = DYNAMIC
 * ALTER TABLE tl_content ADD news_categoryImgSize VARCHAR(255) DEFAULT '' NOT NULL
 * ALTER TABLE tl_content ADD news_module INT UNSIGNED DEFAULT 0 NOT NULL
 * ALTER TABLE tl_content ADD news_categoryFilterPage INT UNSIGNED DEFAULT 0 NOT NULL
 * ALTER TABLE tl_content ADD news_filterPreserve TINYINT(1) DEFAULT '0' NOT NULL
 * ALTER TABLE tl_content ADD news_filterDefault LONGBLOB DEFAULT NULL
 * ALTER TABLE tl_content ADD news_includeSubcategories TINYINT(1) DEFAULT '0' NOT NULL
 * ALTER TABLE tl_content ADD news_relatedCategories TINYINT(1) DEFAULT '0' NOT NULL
 * ALTER TABLE tl_content ADD news_filterCategories TINYINT(1) DEFAULT '0' NOT NULL
 * ALTER TABLE tl_module ADD news_categoryImgSize VARCHAR(255) DEFAULT '' NOT NULL
 * ALTER TABLE tl_module ADD news_categoryFilterPage INT UNSIGNED DEFAULT 0 NOT NULL
 * ALTER TABLE tl_module ADD news_categoriesRoot INT UNSIGNED DEFAULT 0 NOT NULL
 * ALTER TABLE tl_module ADD news_forceCategoryUrl TINYINT(1) DEFAULT '0' NOT NULL
 * ALTER TABLE tl_module ADD news_showEmptyCategories TINYINT(1) DEFAULT '0' NOT NULL
 * ALTER TABLE tl_module ADD news_resetCategories TINYINT(1) DEFAULT '0' NOT NULL
 * ALTER TABLE tl_module ADD news_filterPreserve TINYINT(1) DEFAULT '0' NOT NULL
 * ALTER TABLE tl_module ADD news_filterDefault LONGBLOB DEFAULT NULL
 * ALTER TABLE tl_module ADD news_enableCanonicalUrls TINYINT(1) DEFAULT '0' NOT NULL
 * ALTER TABLE tl_module ADD news_filterCategoriesUnion TINYINT(1) DEFAULT '0' NOT NULL
 * ALTER TABLE tl_module ADD news_includeSubcategories TINYINT(1) DEFAULT '0' NOT NULL
 * ALTER TABLE tl_module ADD news_relatedCategoriesOrder VARCHAR(10) DEFAULT '' NOT NULL
 * ALTER TABLE tl_module ADD news_relatedCategories TINYINT(1) DEFAULT '0' NOT NULL
 * ALTER TABLE tl_module ADD news_filterCategoriesCumulative TINYINT(1) DEFAULT '0' NOT NULL
 * ALTER TABLE tl_module ADD news_filterCategories TINYINT(1) DEFAULT '0' NOT NULL
 * ALTER TABLE tl_module ADD news_customCategories TINYINT(1) DEFAULT '0' NOT NULL
 * ALTER TABLE tl_module ADD news_categories LONGBLOB DEFAULT NULL
 * ALTER TABLE tl_news_archive ADD categories LONGBLOB DEFAULT NULL
 * ALTER TABLE tl_news_archive ADD limitCategories TINYINT(1) DEFAULT '0' NOT NULL
 * ALTER TABLE tl_news_feed ADD categories_show VARCHAR(16) DEFAULT '' NOT NULL
 * ALTER TABLE tl_news_feed ADD categories LONGBLOB DEFAULT NULL
 * ALTER TABLE tl_page ADD newsCategories_param VARCHAR(64) DEFAULT '' NOT NULL
 * ALTER TABLE tl_user ADD newscategories_default LONGBLOB DEFAULT NULL
 * ALTER TABLE tl_user ADD newscategories_roots LONGBLOB DEFAULT NULL
 * ALTER TABLE tl_user ADD newscategories VARCHAR(32) DEFAULT '' NOT NULL
 * ALTER TABLE tl_user_group ADD newscategories_roots LONGBLOB DEFAULT NULL
 * ALTER TABLE tl_user_group ADD newscategories_default LONGBLOB DEFAULT NULL
 * ALTER TABLE tl_user_group ADD newscategories VARCHAR(32) DEFAULT '' NOT NULL

And did it not execute those migrations?

The fields are there.. i can add categories too
But as you can see, there is no field tl_news.categories at the migrations

to answer your question: the migrations were performed correctly, the fields are present in the database

The first error looks like DC_Multilingual issue and I will have a closer look at that.

The second one, however, I am not able to reproduce. Are you 100% sure you are on the latest version of the extension (3.3.1)?

If you need more information on that multilangual issue, just tell me.

It's 3.3.1:
composer.lock:

            "name": "codefog/contao-news_categories",
            "version": "3.3.1",
            "source": {
                "type": "git",
                "url": "https://github.com/codefog/contao-news_categories.git",
                "reference": "65b8aeefd627a89397d2bb9dd01d29babc558f63"
            },

Can you tell me how the tl_news.categories field will be inserted without the sql definition in the DCA?

It will not because the data is managed in relation table created by codefog/contao-haste dependency. Thus I hardly wonder where that SQL query comes from… are you able to post the backtrace?

What do you mean with backtrace?

I have removed the bundle, deleted the database entries and added everything again. Now i don't get the tl_news.categories error anymore. I had tried this several times before and it did not help. But now I can't reproduce it anymore.

The first error looks like DC_Multilingual issue and I will have a closer look at that.

True. If i remove DC_Multilingual it is working.

I don't know what was wrong with the second issue, but the multilingual incompatibility should be fixed in 3.3.2 👍

Hm, I just had the same error:

Doctrine\DBAL\Exception\InvalidFieldNameException:
An exception occurred while executing 'SELECT t1.id, t1.pid, t1.sorting, t1.tstamp, t1.title, t1.frontendTitle, t1.alias, t1.cssClass, t1.description, t1.image, t1.hideInList, t1.hideInReader, t1.excludeInRelated, t1.jumpTo, t1.published, t1.lid, t1.language, IFNULL(t2.title, t1.title) AS title, IFNULL(t2.frontendTitle, t1.frontendTitle) AS frontendTitle, IFNULL(t2.alias, t1.alias) AS alias, IFNULL(t2.description, t1.description) AS description FROM tl_news_category t1 LEFT OUTER JOIN tl_news_category t2 ON t1.id=t2.lid AND t2.language='en' WHERE (t1.lid=0) AND (tl_news_category.id IN (9,10)) AND (tl_news_category.published=1) ORDER BY tl_news_category.sorting':

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'tl_news_category.id' in 'where clause'

  at vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:79
  at Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException('An exception occurred while executing \'SELECT t1.id, t1.pid, t1.sorting, t1.tstamp, t1.title, t1.frontendTitle, t1.alias, t1.cssClass, t1.description, t1.image, t1.hideInList, t1.hideInReader, t1.excludeInRelated, t1.jumpTo, t1.published, t1.lid, t1.language, IFNULL(t2.title, t1.title) AS title, IFNULL(t2.frontendTitle, t1.frontendTitle) AS frontendTitle, IFNULL(t2.alias, t1.alias) AS alias, IFNULL(t2.description, t1.description) AS description FROM tl_news_category t1 LEFT OUTER JOIN tl_news_category t2 ON t1.id=t2.lid AND t2.language=\'en\' WHERE (t1.lid=0) AND (tl_news_category.id IN (9,10)) AND (tl_news_category.published=1) ORDER BY tl_news_category.sorting\':SQLSTATE[42S22]: Column not found: 1054 Unknown column \'tl_news_category.id\' in \'where clause\'', object(Exception))
     (vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:182)
  at Doctrine\DBAL\DBALException::wrapException(object(Driver), object(Exception), 'An exception occurred while executing \'SELECT t1.id, t1.pid, t1.sorting, t1.tstamp, t1.title, t1.frontendTitle, t1.alias, t1.cssClass, t1.description, t1.image, t1.hideInList, t1.hideInReader, t1.excludeInRelated, t1.jumpTo, t1.published, t1.lid, t1.language, IFNULL(t2.title, t1.title) AS title, IFNULL(t2.frontendTitle, t1.frontendTitle) AS frontendTitle, IFNULL(t2.alias, t1.alias) AS alias, IFNULL(t2.description, t1.description) AS description FROM tl_news_category t1 LEFT OUTER JOIN tl_news_category t2 ON t1.id=t2.lid AND t2.language=\'en\' WHERE (t1.lid=0) AND (tl_news_category.id IN (9,10)) AND (tl_news_category.published=1) ORDER BY tl_news_category.sorting\':SQLSTATE[42S22]: Column not found: 1054 Unknown column \'tl_news_category.id\' in \'where clause\'')
     (vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:159)
  at Doctrine\DBAL\DBALException::driverExceptionDuringQuery(object(Driver), object(Exception), 'SELECT t1.id, t1.pid, t1.sorting, t1.tstamp, t1.title, t1.frontendTitle, t1.alias, t1.cssClass, t1.description, t1.image, t1.hideInList, t1.hideInReader, t1.excludeInRelated, t1.jumpTo, t1.published, t1.lid, t1.language, IFNULL(t2.title, t1.title) AS title, IFNULL(t2.frontendTitle, t1.frontendTitle) AS frontendTitle, IFNULL(t2.alias, t1.alias) AS alias, IFNULL(t2.description, t1.description) AS description FROM tl_news_category t1 LEFT OUTER JOIN tl_news_category t2 ON t1.id=t2.lid AND t2.language=\'en\' WHERE (t1.lid=0) AND (tl_news_category.id IN (9,10)) AND (tl_news_category.published=1) ORDER BY tl_news_category.sorting', array())
     (vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:2021)
  at Doctrine\DBAL\Connection->handleExceptionDuringQuery(object(Exception), 'SELECT t1.id, t1.pid, t1.sorting, t1.tstamp, t1.title, t1.frontendTitle, t1.alias, t1.cssClass, t1.description, t1.image, t1.hideInList, t1.hideInReader, t1.excludeInRelated, t1.jumpTo, t1.published, t1.lid, t1.language, IFNULL(t2.title, t1.title) AS title, IFNULL(t2.frontendTitle, t1.frontendTitle) AS frontendTitle, IFNULL(t2.alias, t1.alias) AS alias, IFNULL(t2.description, t1.description) AS description FROM tl_news_category t1 LEFT OUTER JOIN tl_news_category t2 ON t1.id=t2.lid AND t2.language=\'en\' WHERE (t1.lid=0) AND (tl_news_category.id IN (9,10)) AND (tl_news_category.published=1) ORDER BY tl_news_category.sorting', array(), array())
     (vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1163)
  at Doctrine\DBAL\Connection->executeQuery('SELECT t1.id, t1.pid, t1.sorting, t1.tstamp, t1.title, t1.frontendTitle, t1.alias, t1.cssClass, t1.description, t1.image, t1.hideInList, t1.hideInReader, t1.excludeInRelated, t1.jumpTo, t1.published, t1.lid, t1.language, IFNULL(t2.title, t1.title) AS title, IFNULL(t2.frontendTitle, t1.frontendTitle) AS frontendTitle, IFNULL(t2.alias, t1.alias) AS alias, IFNULL(t2.description, t1.description) AS description FROM tl_news_category t1 LEFT OUTER JOIN tl_news_category t2 ON t1.id=t2.lid AND t2.language=\'en\' WHERE (t1.lid=0) AND (tl_news_category.id IN (9,10)) AND (tl_news_category.published=1) ORDER BY tl_news_category.sorting')
     (vendor/contao/core-bundle/src/Resources/contao/library/Contao/Database/Statement.php:274)
  at Contao\Database\Statement->query()
     (vendor/contao/core-bundle/src/Resources/contao/library/Contao/Database/Statement.php:248)
  at Contao\Database\Statement->execute(array(1))
     (vendor/contao/core-bundle/src/Resources/contao/library/Contao/Model.php:1075)
  at Contao\Model::find(array('column' => array('tl_news_category.id IN (9,10)', 'tl_news_category.published=?'), 'value' => array(1), 'return' => 'Collection', 'order' => 'tl_news_category.sorting', 'table' => 'tl_news_category', 'limit' => 0, 'offset' => 0))
     (vendor/contao/core-bundle/src/Resources/contao/library/Contao/Model.php:946)
  at Contao\Model::findBy(array('tl_news_category.id IN (9,10)', 'tl_news_category.published=?'), array(1), array('column' => array('tl_news_category.id IN (9,10)', 'tl_news_category.published=?'), 'value' => array(1), 'return' => 'Collection', 'order' => 'tl_news_category.sorting'))
     (vendor/codefog/contao-news_categories/src/Model/NewsCategoryModel.php:271)
  at Codefog\NewsCategoriesBundle\Model\NewsCategoryModel::findPublishedByNews('1053')
     (vendor/contao/core-bundle/src/Framework/Adapter.php:44)
  at Contao\CoreBundle\Framework\Adapter->__call('findPublishedByNews', array('1053'))
     (vendor/codefog/contao-news_categories/src/EventListener/TemplateListener.php:60)
  at Codefog\NewsCategoriesBundle\EventListener\TemplateListener->onParseArticles(object(FrontendTemplate), array('id' => '1053', 'pid' => '5', 'tstamp' => '1601450625', 'headline' => 'Mein Fazit nach über 500 Webmeetings: Nur 3% machen das richtig!', 'alias' => 'mein-fazit-nach-ueber-500-webmeetings-nur-3-machen-das-richtig', 'author' => '8', 'date' => '1601447040', 'time' => '1601447040', 'subheadline' => '', 'teaser' => '<p>Spätestens jetzt sollten Sie wissen, wie Sie einer der größten Hindernisse von Webmeetings überwinden. Lesen Sie hier 3 effektive Lösungen - von der einfachen bis zur Profilösung.</p>', 'addImage' => '1', 'overwriteMeta' => '', 'singleSRC' => '�if�����Ԗ' . "\0" . '' . "\0" . '\'F', 'alt' => '', 'imageTitle' => '', 'size' => 'a:3:{i:0;s:0:"";i:1;s:0:"";i:2;s:0:"";}', 'imagemargin' => 'a:5:{s:6:"bottom";s:0:"";s:4:"left";s:0:"";s:5:"right";s:0:"";s:3:"top";s:0:"";s:4:"unit";s:0:"";}', 'imageUrl' => '', 'fullsize' => '', 'caption' => '', 'floating' => 'above', 'addEnclosure' => '', 'enclosure' => null, 'orderEnclosure' => null, 'source' => 'default', 'jumpTo' => '0', 'articleId' => '0', 'url' => '', 'target' => '', 'cssClass' => '', 'noComments' => '', 'featured' => '', 'published' => '1', 'start' => '', 'stop' => '', 'relatedNews' => null, 'cssStyleSelector' => null, 'languageMain' => '0', 'trainerData' => null, 'trainerId' => null, 'productData' => null, 'productIds' => 'a:1:{i:0;a:1:{s:4:"uuid";s:0:"";}}', 'gradientOpacity' => '5', 'hps_MED' => '0', 'hps_FOL' => '0', 'hps_SOF' => '0', 'hps_SIT' => '0', 'hps_STO' => '0', 'hps_STR' => '0', 'hps_SOU' => '0', 'noIndentedMedia' => '1', 'shareTextarea' => null, 'description' => null, 'pageTitle' => '', 'trainingDuration' => null, 'hps_price_notice' => null), object(NewsListModule))
     (vendor/contao/news-bundle/src/Resources/contao/modules/ModuleNews.php:222)
  at Contao\ModuleNews->parseArticle(object(NewsModel), false, ' first even', 1)
     (vendor/contao/news-bundle/src/Resources/contao/modules/ModuleNews.php:272)
  at Contao\ModuleNews->parseArticles(object(Collection))
     (vendor/contao/news-bundle/src/Resources/contao/modules/ModuleNewsList.php:153)
  at Contao\ModuleNewsList->compile()
     (vendor/contao/core-bundle/src/Resources/contao/modules/Module.php:215)
  at Contao\Module->generate()
     (vendor/contao/news-bundle/src/Resources/contao/modules/ModuleNewsList.php:69)
  at Contao\ModuleNewsList->generate()
     (vendor/codefog/contao-news_categories/src/FrontendModule/NewsListModule.php:42)
  at Codefog\NewsCategoriesBundle\FrontendModule\NewsListModule->generate()
     (src/LanguageSwitcherBundle/FrontendModule/NewsModuleTrait.php:64)
  at LanguageSwitcherBundle\FrontendModule\NewsListModule->generate()
     (vendor/contao/core-bundle/src/Resources/contao/elements/ContentModule.php:77)
  at Contao\ContentModule->generate()
     (vendor/contao/core-bundle/src/Resources/contao/library/Contao/Controller.php:575)
  at Contao\Controller::getContentElement(object(ContentModel), 'main')
     (src/HpsBundle/FrontendModule/ThemeArticleModule.php:61)
  at HpsBundle\FrontendModule\ThemeArticleModule->compile()
     (vendor/contao/core-bundle/src/Resources/contao/modules/Module.php:215)
  at Contao\Module->generate()
     (vendor/contao/core-bundle/src/Resources/contao/modules/ModuleArticle.php:74)
  at Contao\ModuleArticle->generate()
     (src/HpsBundle/ContentElement/ContentThemeSection.php:55)
  at HpsBundle\ContentElement\ContentThemeSection->generate()
     (vendor/contao/core-bundle/src/Resources/contao/library/Contao/Controller.php:575)
  at Contao\Controller::getContentElement(object(ContentModel), 'main')
     (vendor/contao/core-bundle/src/Resources/contao/modules/ModuleArticle.php:221)
  at Contao\ModuleArticle->compile()
     (vendor/contao/core-bundle/src/Resources/contao/modules/Module.php:215)
  at Contao\Module->generate()
     (vendor/contao/core-bundle/src/Resources/contao/modules/ModuleArticle.php:74)
  at Contao\ModuleArticle->generate(false)
     (vendor/contao/core-bundle/src/Resources/contao/library/Contao/Controller.php:515)
  at Contao\Controller::getArticle(object(ArticleModel), false, false, 'main')
     (vendor/contao/core-bundle/src/Resources/contao/library/Contao/Controller.php:377)
  at Contao\Controller::getFrontendModule('0', 'main')
     (vendor/contao/core-bundle/src/Resources/contao/pages/PageRegular.php:168)
  at Contao\PageRegular->prepare(object(PageModel))
     (vendor/contao/core-bundle/src/Resources/contao/pages/PageRegular.php:48)
  at Contao\PageRegular->getResponse(object(PageModel), true)
     (vendor/contao/core-bundle/src/Resources/contao/controllers/FrontendIndex.php:339)
  at Contao\FrontendIndex->renderPage(object(PageModel))
     (vendor/symfony/http-kernel/HttpKernel.php:158)
  at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), 1)
     (vendor/symfony/http-kernel/HttpKernel.php:80)
  at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), 1, true)
     (vendor/symfony/http-kernel/Kernel.php:201)
  at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
     (web/index.php:31)
  at require('/web/index.php')
     (web/app.php:4)

codefog/contao-news_categories: 3.4.0
terminal42/dc_multilingual: 3.0.8

Although may be that's a different, new error?

You gotta update to DC_Multilingual 4.x, which should be a requirement since news-categories 3.3.2.

Maybe I should add a conflict there, lemme do that 😉

I have just pushed 3.4.1, sorry about that. You should either downgrade news_categories to 3.3.x or update DC_Mutlilingual to 4.x.

Ah I see 👍

May be you should delete the 3.4.0 tag though, that would prevent incompatible updates in the future (otherwise 3.4.0 will always be installed, if you use DC_Multilingual in version 3.x).

Good idea, I have just dropped it. 3.3.2 is also faulty but I guess most of the cases will update to 3.4.1 anyway.

Obviously not… I dropped 3.3.2 as well 🤦