0

I have the following code running since 2 years already, it basically duplicates a vacancy. So it copies its row from the database to a temporary table, where I put some counters back on 0 and put the ID on NULL (because it is an auto increment).

This is the code:

        $this->db->query('CREATE TEMPORARY TABLE tmptable SELECT * FROM vacancies WHERE vacancy_id = ' . $vacid);
        $this->db->query('UPDATE tmptable SET vacancy_id = NULL');
        if (!empty($banner)) {
            $this->db->query('UPDATE tmptable SET banner = "' . $banner . '"');
        }
        $this->db->query('UPDATE tmptable SET create_time = now()');
        $this->db->query('UPDATE tmptable SET watch_counter = 0');
        $this->db->query('UPDATE tmptable SET contact_info_counter = 0');
        $this->db->query('UPDATE tmptable SET status = 0');
        $this->db->query('UPDATE tmptable SET reminder_mail_sent = 0');
        $this->db->query('UPDATE tmptable SET extend_code = NULL');
        $this->db->query('INSERT INTO vacancies SELECT * FROM tmptable');
        $newvacancyid = $this->db->insert_id();
        $this->db->query('DROP TEMPORARY TABLE IF EXISTS tmptable');
        return $newvacancyid;

This code has been running for well over 2 years now. And all of a sudden it starts throwing the following error:

Column 'vacancy_id' cannot be null

UPDATE tmptable SET vacancy_id = NULL

I haven't touched the vacancies table in a long time either, and especially not the vacancy_id (PK) auto-increment.

How do I go about debugging this issue why it is happening all of a sudden? I'm a bit stuck here on what to do next

EDIT: managed to solve it by modifying the temp table before inserting, as suggested in this post MySQL: How to copy rows, but change a few fields? . Would still like to know why all of a sudden this error started appearing?

Dennis
  • 3,044
  • 2
  • 33
  • 52
  • 1
    "This code has been running for well over 2 years now. And all of a sudden it starts throwing the following error:" i don't buy that..`CREATE TEMPORARY TABLE tmptable SELECT * FROM vacancies ` would copy the table definition into the temporary table.. So if it stopt working something has changed in table vacancies definition – Raymond Nijland Feb 25 '19 at 20:26
  • 1
    When look at the error `Column 'vacancy_id' cannot be null` i have a feeling the column `vacancy_id` in the table `vacancies` is ALTERed to `NOT NULL` ? – Raymond Nijland Feb 25 '19 at 20:30
  • This happened to me when I changed servers. Or maybe you did some change in your mysql instalation. Just add "alter table tmptable modify column vacancy_id integer" and it will work fine. – Gustavo Rodríguez May 03 '19 at 10:42

0 Answers0