Search code examples
phppdomariadb

I have a table with 247 columns which is difficult to handle. How can I change its structure?

I am changing my SQL request to PDO in order to integrate more security against SQL injecting. I was using before procedural msqli and everything was working ok. Now I updated server to PHP 8.2 and I am using PDO and prepared statements.

The BD I am accessing is old MariaDB and has a table with 247 fields most of which are NULL on a single row. (It is sections for a description of mushrooms, but only some of each are really described in a single row (mushrooms)).

Most of the 247 fields are set to mediumtext because they can content up to a couple paragraphs.

My connection script is :

try {

    db = new PDO("mysql:dbname=$dbname;host=$servername;charset=utf8mb4", $username, $password );

    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (Exception $e) {
    echo 'Erreur de connection: ',  $e->getMessage(), "\n";
return;
}

When I select one row of that table in order to echo the not NULL descriptions column, as soon as I execute the statement I run into a full memory. The error is at the stmt->execute() line, not even at the fetchAll line.

$description = 'someTag';

$sql = "SELECT * FROM description WHERE tag = :tag LIMIT 1";

$stmt = $db->prepare($sql);
$stmt->bindParam(':tag', $description, PDO::PARAM_STR);
$stmt->execute();
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
$result = $stmt->fetchAll();
$stmt->closeCursor();

The error :

Fatal error: Allowed memory size of 67108864 bytes exhausted (tried to allocate 262144 bytes) in xxx on line (**the $stmt->execute() line)

I tried to use :

while ($row = $stmt->fetch()) {

Instead of fetchAll() but it changed noting, I used it on every other request from the same page and it changed noting. I flushed variables that were no more useful to NULL on the rest of the page and it changed nothing. I even isolated the script on an empty page and it exceeds the memory limit.

I tried to add this to my connection script and it changed nothing :

db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

I tried to select only one field instead of * and this worked, but I don't have an easy way to find NOT NULL columns in the table for a specific tag (row).

I tried to find a SQL statement to restrict my SELECT to NOT NULL columns but I was not successful.

I know I could get add memory_limit = 256M to the phpini file, but that is not what I want since the website has a high usage and for now its on a multihosted server.

The best solution would be to reengineer the MariaDB database using a table with description sections labels and linking only used sections to the main description table for each row, but I have limited access to the db since it is online with the old website and the old website needs to stay online and up to date for 1-2 years after new deployment. I though of creating a daily cron job to do that but I would like to avoid for the moment.

Any Idea how I can get this to work? It was working fine using msqli on php 8.1.

To answer the worries of contributors that think it's related to another part of the page code, here is the complete code of the isolated test page.

function convert($size)
 {
    $unit=array('b','kb','mb','gb','tb','pb');
    return @round($size/pow(1024,($i=floor(log($size,1024)))),2).' '.$unit[$i];
 }


$servername = "localhost";
$username = "myUser";
$password = "myPass";
$dbname = "myDB";


try {

    $db = new PDO("mysql:dbname=$dbname;host=$servername;charset=utf8mb4", $username, $password );

    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    //$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

 } catch (Exception $e) {
    echo 'Erreur de connection: ',  $e->getMessage(), "\n";
    return;
}   

$query = (array_key_exists("query",$_REQUEST) ? $_REQUEST["query"] : "");


if($query != ""){

    $tag_desc = 'Aegerita candida';
    // Code works if I limit to "SELECT Titre FROM description WHERE tag = :tag LIMIT 1";
    $sql_description = "SELECT * FROM description WHERE tag = :tag LIMIT 1";

    $stmt = $db->prepare($sql_description);
    $stmt->bindParam(':tag', $tag_desc, PDO::PARAM_STR);
    
    echo convert(memory_get_usage(true));  // returns 2mb
    $stmt->execute();
    $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
    
    $i = 0;
    
    while ($row = $stmt->fetch()) {

        if($i == 0) {

            ?><table style="border-collapse:collapse;border: 0;table-layout: fixed;color: #063850;background-color: #DDDDDD;text-align: left"><thead><tr style="border: 2px solid #063850;"> <?php
            foreach (array_keys($row) as $key) {
                //print_r($key);
                echo ("<th style='padding:5px;border: 2px solid #063850;position:sticky;top: -2;z-index:10;background-color:#e5a045'>$key</th>");
            }
            echo ("</tr></thead>");
        }

        $i++;

        echo ("<tr style='border: 2px solid #063850'>");
        foreach ($row as $value) {
            echo ("<td style='padding:2px;border: 2px solid #063850'>$value</td>");
            // <img src=\"$value\" height=\"400\" >
        }
        echo ("</tr>");
    }
    
    if ($i > 0) {
        echo ("</table>");
        echo ('<div style="margin-top:400px">&nbsp;</div>');
    } else {
        echo "Aucun résultat";
    }

}

$db = null;

Has mentioned it automatically work if I limit my sql to a couple fields.

The error is still on the stmt->execute() line.

Solution

  • Could it be that the issue is your data model?

    If you have a table that has a lot of nulls, then the data is most likely not normalized.

    Instead of having 256 columns in your table, maybe just have the ones that are always there, and then relate it to a separate table with three fields: foreign key to the main table, a tag field and a value field.

    If you are rewriting your application to USE PDO anyway, some long-term considerations could be included.

    Example table

    Layout like yours (all data in one table):

    id name email someotherdata tag1 tag2 tag3 tag4
    1 Jaques j@lullaby.fr blablabla NULL a NULL 100
    2 Allouette a@lullaby.fr blébléblé c a NULL 400
    3 Mogens m@lullaby.dk blæblæblæ NULL NULL 5000 NULL
    4 Birthe b@lullaby.dk bløbløblø NULL a NULL 100

    Suggested Tag-Value table layout

    Main Table

    id name email someotherdata
    1 Jaques j@lullaby.fr blablabla
    2 Allouette a@lullaby.fr blébléblé
    3 Mogens m@lullaby.dk blæblæblæ
    4 Birthe b@lullaby.dk bløbløblø

    Tag-Value Table

    id tag value
    1 tag2 a
    1 tag4 100
    2 tag1 c
    2 tag2 a
    2 tag4 400
    3 tag3 5000
    4 tag2 a
    4 tag4 100

    Usage

    Instead of selecting just your row, you WILL need to either join the results together, or make two separate calls. I would suggest two calls, especially since you are rtunning into memory issues.

    SELECT 
  id, 
  name,
  email,
  someotherdata
FROM
  MainTable
WHERE
  id = 1;

-- and then the tags
SELECT 
  id, 
  tag,
  value
FROM
  TagValueTable
WHERE
  id = 1; -- reference to the main table

    Advantaages of this normalized approach

    • Less bloat (not so many nulls)
    • Simpler structures
    • REST ready
    • More resilient
      • Add tags without changing the DDL of the database (ALTER)
    • You can still get the original layout by making subselects and transpositions in mysql
    • You can easily make statistics on the tags related to each other

    Disadvantages

    • Complexity of the data model increases
    • You need to do a lot of extra SQL gymnastics to get EXACTLY what you had before