0

MariaDB is inserting unknown rows before the actual data in a single insert statement. Example 

insert into my_table (id_person, id_department, job_date) values (1, 1, '2019-01-01')

when you do a

select * from my_table

you'll get, for example, but it can be other random values

1   1   NULL
3   1   NULL
1   1   2019-01-01
Leandro Jacques
  • 413
  • 5
  • 19

1 Answers1

1

Today I was trying to insert some data from my PHP program to a table on my database. The stragest thing was that, instead of inserting only the row I wanted to insert, it was inserting two other rows with unknown data and the actual row I was wanting to insert. So I tried to execute on a php interactive shell with direct insert instruction instead of prepared statements, more unknown rows were being inserted. I thought it could be a PHP bug, so I went to MariaDB console to execute the insert statement directly to see what would happen, and the problem persisted. So I thought that dropping the table and recreating it would solve the problem, and it really solved.

Leandro Jacques
  • 413
  • 5
  • 19
  • This doesn't really explain your observations above. If you insert a single record, then either zero (in the case of an error) or one (in the case of success) record should be inserted, period. Something else is doing inserts in your application. – Tim Biegeleisen Jul 23 '19 at 01:47
  • @TimBiegeleisen as I detailed on my own answer: so I went to MariaDB console to execute the insert statement directly to see what would happen, and the problem persisted. So I thought that dropping the table and recreating it would solve the problem, and it really solved. The solution was dropping and recreating the table, period. – Leandro Jacques Jul 23 '19 at 01:53
  • This likely would not help anyone else having a similar problem. – Tim Biegeleisen Jul 23 '19 at 01:53
  • @TimBiegeleisen think as you want to think, I know that this case is rare as I didn't find any answer anywhere, hope it helps someone. It was a table corruption or a MariaDB bug, and the only solution that I found was the one I told here. – Leandro Jacques Jul 23 '19 at 01:57
  • You might want to open a bug report with MariaDB or MySQL if this be the case +1. – Tim Biegeleisen Jul 23 '19 at 01:59
  • @TimBiegeleisen The problem is that this problem is not easy to reproduce, as I dropped the table and recreated it vanished. Any other table I create now, the problem will not happen again that soon. Something very, very specific happened during the table creation the first time I created it. The only thing I can do to open this bug report is to wait it happen in a future and send the table files for them to check. – Leandro Jacques Jul 23 '19 at 02:02