Query not able to list string fields, need to LIMIT or SUBSTRING the field

1

Hi,

I am facing severe issues after I moved a website from a shared server to VPS. I am not sure if its due to more secure settings or so.

This App build on MySQL + PHP + Angular 9 fails to retrieve simple queries. It works find on my localhost, old server, etc. But on the new VPS, I need to substring the field to 20 chars or LIMIT output to 500 rows etc.

OS Linux
Apache Version 2.4.57
MySQL Version 5.7.43
PHP 7.4
Slim Framwork 4.4

Sample:
FAILS: SELECT item_Id, sales_Description AS sales_Description FROM item_master

SUCCESS: SELECT item_Id, SUBSRING(sales_Description, 1, 20) AS sales_Description FROM item_master

SUCCESS: SELECT item_Id, sales_Description AS sales_Description FROM item_master LIMIT 400

Actual Code:

            $ItemsSource = "SELECT `item_Id`, SUBSTRING(`sales_Description`, 1, 120)  AS sales_Description 
                FROM `item_master` 
                WHERE `StatusId1` <> 4 AND `Is_Assembly` = 'No' 
                AND (`sales_Description` IS NOT NULL AND `sales_Description` <> '')  
                ORDER BY `sales_Description` LIMIT 500 ";            
            $stmt = $this->db->prepare($ItemsSource);
            $stmt->execute();
            $count = $stmt->rowcount();
            $assembly_specification = $stmt->fetchAll();
            if (!empty($assembly_specification)) {
                $payload = ['status' => 'ok', 'list_data' => $assembly_specification];
            } else {
                $payload = ['status' => 'fail', 'msg' => list data not found!'];
            }
            $response->getBody()->write(json_encode($payload));
            return $response
                ->withHeader('Content-Type', 'application/json')
                ->withStatus(201);

Please help me out.

Thank you.

2

Your issue seems related to the configuration or resource limitations on the new VPS.
It might be running out of memory or CPU which can lead to different failures.

Check the VPS php.ini for memory and execution time limits:

memory_limit = 256M
max_execution_time = 300

You might need to adjust these values based on the data size you’re working with

Make sure error reporting is turned on for PHP. Add these lines to the beginning of your PHP script temporarily:

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

Enable Exceptions for PDO to see what was wrong with the query.

Add this PDO flag:

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION

Example:

$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_PERSISTENT => false,
    PDO::ATTR_EMULATE_PREPARES => true,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"
];

$pdo = new PDO($dsn, $username, $password, $options);

If you don’t want to enabled Exceptions for PDO then immediately after the $stmt->execute(); line, check for errors in the statement execution:

if ($stmt->errorCode() != "00000") {
    $errorInfo = $stmt->errorInfo();
    die("SQL Error: {$errorInfo[2]} (Code {$errorInfo[0]})");
}

Also note:

  • $count = $stmt->rowcount(); is not for SELECT queries. It is only for INSERT, UPDATE, DELETE statements.
  • There is a syntax error here: $payload = ['status' => 'fail', 'msg' => list data not found!'];
3

Dear Odan,

Thanks a TON to you! It is working.

As instructed, I enabled exceptions for PDO. Copied the options variable and included same in new PDO connection. I would be also happy to know, where can we view the errors displayed.

Thank you

4

As soon as a (database) error occurs, the exception error message should be displayed.
If there is no error with the query itself, the problem is somewhere else.