Query tells "field is required", but is set

Hello everyone,

I came across a problem with inserting data into the mysql database.

I'm trying to insert a huge bunch a data rows into the database (talking about 98k single rows). My first approach was this one ($data is the huge array):

$sql = "INSERT INTO ".$suppdata['supp_tbl']." ($head_fields) VALUES (";

        for($i = 1; $i <= $suppdata['num_fields']; $i++)
            $sql .= ":tmp".$i.":, ";

        $sql = substr($sql, 0, -2).")";

        $query = $this->modelsManager->createQuery($sql);

        for($i = 0; $i < sizeof($data); $i++)
        {
            $row = array();

            for($j = 0; $j < $suppdata['num_fields']; $j++)
                $row['tmp'.($j+1)] = $data[$i][$j];

            $status = $query->execute($row);

        }

A bit of explaination:

  • there are different data sources, so I had to keep the number of binding values dynamic (is this even possible? I'm basically porting a script which was written in "raw" PHP)
  • the model name comes from $suppdata['supp_tbl']

So what I do is to build a query string with the binding parameters :tmpX:, building up an array for each row, bind it and execute it.

The array which I use to bind the data ($row) looks fine, and so does the built query ($sql)

What I get is an error "Artnr is required". "Artnr" is the first field in the table, and also its primary key. I can say for sure it is provided. I even tried to do the "model way", so create an instance of the model, use all the setters and then just $obj->save() it, but it throws the same error there.

Why do I keep getting this error, although the required data is set?

And by the way, is there a way to see the query the model manager executes, with the data bound?

€dit:

I'm still working on this issue, I even tried it the hard way (just for testing purposes)

$query->execute([
                'tmp1' => $data[$i][0],
                'tmp2' => $data[$i][1],
                'tmp3' => $data[$i][2],
                'tmp4' => $data[$i][3],
                'tmp5' => $data[$i][4],
                'tmp6' => $data[$i][5],
                'tmp7' => $data[$i][6],
                'tmp8' => $data[$i][7],
                'tmp9' => $data[$i][8],
                'tmp10' => $data[$i][9],
                'tmp11' => $data[$i][10],
                'tmp12' => $data[$i][11],
                'tmp13' => $data[$i][12],
                'tmp14' => $data[$i][13],
                'tmp15' => $data[$i][14],
                'tmp16' => $data[$i][15]
            ]);

I checked on the MySQL server, and saw that the query submitted looked like this:

INSERT INTO (table)` (`Artnr`, (other fields)) VALUES (null, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

Seeing this explains at least the error message, but I don't understand the reason; the array of which I bind the data is populated, but it seems not to bind the values.

Thank you.

Good job troubleshooting - you've done everything I would have suggested.

The resulting query looks odd. If you're inserting data, why are all the other fields DEFAULT? Is $data populated as you expect?

My next step would be to output the query and the data for a single value of $i - just to get a glimpse into what the query and the data looks like on the PHP side.



3.3k
edited Apr '20

I already checked on $row, which is populated by $data[$i], its looks fine.

For testing purposes, I got rid of the binding and replaced the placeholders myself with a simple str_replace, to see if the query gets populated this way:

for($i = 0; $i < sizeof($data); $i++)
        {   
            $tmpSql = $sql;

            for($j = 0; $j < $suppdata['num_fields']; $j++)
                $sql = str_replace(':tmp'.($j+1).':', $data[$i][$j], $sql);

            echo $sql;
            $status = $this->modelsManager->executeQuery($sql);

            $sql = $tmpSql;

            foreach($status->getMessages() as $m)
                echo $m."<br />";

        }

Aside from missing quotation marks for the string values, I get a perfectly fine query from it, so there seems to be a problem with the binding, not with the data.

How can I check the query before it gets sent up to the database? $query->getSql() just gives me the placeholder version I generated, but not the filled version However, I am able to see what is sent to the database, I think Phalcon itself wouldn't generate another output for me.

Can you show the placeholder version and an example $row? I know you said you checked on $row already, but maybe another set of eyes will find something - or maybe I'm totally wrong.

You said you tried it the hard way. Have you tried it by binding not with data from $data, but with hardcoded values?

Do you have access to your MySQL server's error log? Maybe something's going funky with the binding, but is somehow being recovered.

Also, an irrelevant-to-your-question note on your code - your first for loop is looping starting at 1, but your third is looping from 0, but then being incremented. That just seems overly complicated.

Why not have your first for loop start at 0:

for($i = ; $i < $suppdata['num_fields']; $i++)

Then you can simplify the third loop:

for($j = 0; $j < $suppdata['num_fields']; $j++)
     $row['tmp'.$j] = $data[$i][$j];

Personally I'd use foreach loops.

And finally, I edited your posts to show syntax highlighting - if you [Edit] your post you can see I added php, and sql after the three opening backticks.



3.3k
edited Apr '20

Oh, I've seen this syntax highlighting here before, but didn't know how to set it, so thank you ;)

The placeholder version looks like this:

INSERT INTO (table) (Artnr, (field2, field3, .., field16)) VALUES (:tmp0:, :tmp1:, :tmp2:, :tmp3:, :tmp4:, :tmp5:, :tmp6:, :tmp7:, :tmp8:, :tmp9:, :tmp10:, :tmp11:, :tmp12:, :tmp13:, :tmp14:, :tmp15:)

This is a var_dump() of $row:

array (size=16)
  'tmp0' => int 100868
  'tmp1' => int 1
  'tmp2' => string 'PRODUCT SLOGAN' (length=14)
  'tmp3' => string 'PRODUCT INGREDIENTS' (length=19)
  'tmp4' => int 9515
  'tmp5' => int 12
  'tmp6' => int 2
  'tmp7' => string 'S' (length=1)
  'tmp8' => string 'ST' (length=2)
  'tmp9' => float 0.99
  'tmp10' => string '2,000' (length=5)
  'tmp11' => int 0
  'tmp12' => string '0001-01-01' (length=10)
  'tmp13' => string '2001-09-21' (length=10)
  'tmp14' => string 'J' (length=1)
  'tmp15' => string '0001-01-01' (length=10)

There are no funky errors in the MySQL error logs.

Hard coded values yield the same error.

And to your suggestion: you're right, thats more simple. Don't know what had me doing that..

Well nuts - I'm not sure what else to look for. The only thing that looks off to me is the fact you've got field2 to field16 in another set of parethesis. I've never done or even seen that. Try changing it to just

(Artnr, field2, field3, field4)

That's really grasping at straws though.

You've successfully executed a query with no binding right? Just putting the values directly in the query?

I've never encountered this problem. Are you running v3 or v4?

Maybe try not using modelsManager and PHQL and instead use the basic DB service, which is PDO, and try it that way. Maybe Phalcon's messing up but raw PDO will work? You'll need to use positional placeholders, but it should be pretty easy to at least test.



3.3k
edited Apr '20

The other set of parenthesis was just to display here, the fields are all in the same set of parenthesis. I just didn't want to have a long line of field2, field3, field4, ... and so on in the code block. So in conclusion, the query looks like this:

INSERT INTO table (Artnr, field2, field3, field4, field5, field6, field6, field7, field8, field9, field10, field11, field12, field13, field14, field15, field16) VALUES (:tmp0:, :tmp1:, :tmp2:, :tmp3:, :tmp4:, :tmp5:, :tmp6:, :tmp7:, :tmp8:, :tmp9:, :tmp10:, :tmp11:, :tmp12:, :tmp13:, :tmp14:, :tmp15:)

Yes, I successfully executed the query without binding, and the model way with like

$entry = new TableInMatter();
$entry->setArtnr(123);
$entry->setField2(321);
...
$entry->setField16('somestring');
$entry->save();

produces the same error. Do the model internals work similiar, with building queries, binding values and then execute them?

Currently, I'm running Phalcon 4.0.5 on PHP 7.4.4 and MySQL 5.7.11 on Windows 10. PHP and Phalcon are fresh installs.

Fun thing is, in another controller, I don't have any problems with binding and executing queries via the modelsManager. Only differences are column count and (far) less variable work.

$query = $this->modelsManager->createQuery('INSERT INTO anothertable (node_id, text) VALUES (:node_id:, :text:)');

for($i = 1; $i < sizeof($data); $i++) // the point of $i = 1 here is that the data comes from a csv file, and $data[0] contains the column names
{
    $query->execute([
        'node_id' => $data[$i][0],
        'text' => $data[$i][1]
    ]);     
}

I'll try out the basic DB service tomorrow.

I would imagine Models use the same phql abstraction layer.

I wonder if, somehow, it's your table definition. Could you post that?



3.3k

Turns out PHQL can't run EXPLAIN, so here is the CREATE statement from MySQL.

CREATE TABLE `supp_table` (
  `Artnr` int(6) NOT NULL,
  `field2` int(3) DEFAULT NULL,
  `field3` varchar(60) DEFAULT NULL,
  `field4` varchar(60) DEFAULT NULL,
  `field5` int(4) DEFAULT NULL,
  `field6` int(1) DEFAULT NULL,
  `field7` int(1) DEFAULT NULL,
  `field8` varchar(1) DEFAULT NULL,
  `field9` varchar(2) DEFAULT NULL,
  `field10` double DEFAULT NULL,
  `field11` varchar(10) DEFAULT NULL,
  `field12` int(10) DEFAULT NULL,
  `field13` date DEFAULT NULL,
  `field14` date DEFAULT NULL,
  `field15` varchar(1) DEFAULT NULL,
  `field16` date DEFAULT NULL,
  PRIMARY KEY (`Artnr`),
  KEY `supp_idx` (`Artnr`,`field10`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='supp_name';


3.3k

Maybe try not using modelsManager and PHQL and instead use the basic DB service, which is PDO, and try it that way. Maybe Phalcon's messing up but raw PDO will work? You'll need to use positional placeholders, but it should be pretty easy to at least test.

This works, so thank you so far for this suggestion. Still wondering what causes the problem with the modelsManager though.

I have no idea. Like I said, I've never encountered anything like this. Maybe it's a bug in v4? If it's possible to create a reproducable test case, I'd encourage you to file a bug report on GitHub.

What's Your version of Phalcon? There was a bug in 4.0.4 resulting in similiar behaviour you're presenting, fixed in 4.0.5



3.3k

I'm running 4.0.5. Do you have a link to the issue?