webERP Forum

Full Version: MRP problem on some servers
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Hi

In a couple of weberp installations i have bumped into a rare situation where this error message shows up when running one of the following reports:
MRPPlannedPurchaseOrders.php
MRPReschedules.php
MRPPlannedWorkOrders.php
MRPShortages.php

Database Error 1054
Unknown column 'Tables_in_testdb' in 'where clause'


The solution for this problem was simple, just wrapped the tablename part with single-quotes like this (showing example from MRPPlannedPurchaseOrders.php line 10)
From:
Code:
$sql="SHOW TABLES WHERE Tables_in_" . $_SESSION['DatabaseName'] . "='mrprequirements'";
To:
Code:
$sql="SHOW TABLES WHERE 'Tables_in_" . $_SESSION['DatabaseName'] . "'='mrprequirements'";

On the same server i also have a db named testdb-ver2
It seems like SHOW TABLES mix similar tablenames for one reason, i'm actually not sure.

The next problem was a bit trickier, maybe this problem have something in common? MySQL version problem?

When using: SHOW TABLES without arguments, all tables in testdb is listing.
When using: SHOW TABLES WHERE 'Tables_in_testdb'='mrprequirements' shows no results at all. (this is tested outside weberp as well)

Changing the query to:
Code:
$sql = "SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = '" . $_SESSION['DatabaseName'] . "' AND TABLE_NAME = 'mrprequirements'";

..solves it all.

Anyone seeing this before?

This is not a bug in weberp at all, but may become a problem on some servers. I have not digged deeper down in this particular server to find out why.
Personally, I never use SHOW TABLES at all in my code. Always using information_schema.tables

JanB

Server Versions:
mysql Ver 15.1 Distrib 10.0.14-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Server version: Apache/2.2.22 (Debian)
PHP 5.4.38-0+deb7u1
Hi JanB,

I just encountered the same issue.
Your solution worked perfectly for me.

Thanks.
I have just upgraded to 4.13.1, and am seeing the error described above, once again.

Does anyone know what might be causing this?

I am using shared hosting with:
PHP 5.6.30
MySQL Community Server (GPL) 5.5.54
Apache/2.4.23 (Unix)

Thanks in advance,

Andy.
Unfortunately nobody applied this fix to the code so when the new release was done the fix you previously applied was overwritten. You need to fix this again and hope somebody will apply it before the next release.

Tim
Andy
Thanks for your submission - applied SVN today.
It probably makes more sense to move this to the ConnectDB_XXXX.php files and create a DB_table_exists() function so that the code is DBMS independent as the code you have committed is mysql specific.

You once accused me of trying to sabotage the project by committing something mysql specific. :-)

Tim