Migrating from 4.0 to 4.11: `default for column "bugtrackertype" cannot be cast automatically`

[seanm]

Running install.sh to upgrade from 4.0 to 4.11 we got this message:

In Connection.php line 838:
                                                                                                                  
  SQLSTATE[42804]: Datatype mismatch: 7 ERROR:  default for column "bugtrackertype" cannot be cast automatically  
   to type bugtrackertype (Connection: pgsql, Host: localhost, Port: 5432, Database: cdash, SQL:                  
              ALTER TABLE project                                                                                 
              ALTER COLUMN bugtrackertype                                                                         
              TYPE bugtrackertype USING                                                                           
                  CASE                                                                                            
                      WHEN bugtrackertype = 'GitHub' THEN 'GitHub'::bugtrackertype                                
                      WHEN bugtrackertype = 'Buganizer' THEN 'Buganizer'::bugtrackertype                          
                      WHEN bugtrackertype = 'JIRA' THEN 'JIRA'::bugtrackertype                                    
                      ELSE NULL                                                                                   
                  END                                                                                             
          )                                                                                                       
                                                                                                                  

In Connection.php line 584:
                                                                                                                  
  SQLSTATE[42804]: Datatype mismatch: 7 ERROR:  default for column "bugtrackertype" cannot be cast automatically  
   to type bugtrackertype                                                                                         

The migration seemed to finish anyway, and the resulting site seems usable so far, but the error message above seems like cause for concern nonetheless...

[seanm]

@zackgalbreath @williamjallen would it help if we update 4.0 -> 4.1 -> 4.2 etc? Either to pin down this bug, or as a way to not encounter the bug?

[williamjallen]

@seanm What's the default value for bugtrackertype pre-migration?

would it help if we update 4.0 -> 4.1 -> 4.2 etc? Either to pin down this bug, or as a way to not encounter the bug?

That would help you get as far as you can, but you'd still be blocked by the same migration. Given that we haven't seen this error on any other systems, I suspect that this is an artifact of a a prior MySQL->Postgres migration.

[seanm]

What's the default value for bugtrackertype pre-migration?

Pre which migration? Before we went to 4.0?, or before we tried to go to 4.11? Or before the MySQL to postgres migration?

I'm pretty ignorant with SQL, but an LLM suggested the \d+ project command. Here's the result from the current prod server running 3.10.x (but after we migrated from MySQL to postgres):

         Column          |          Type          | Collation | Nullable |               Default               | Storage  | Compression | Stats target | Description
-------------------------+------------------------+-----------+----------+-------------------------------------+----------+-------------+--------------+-------------
 bugtrackertype          | character varying(16)  |           |          | ''::character varying               | extended |             |              |

[williamjallen]

Yeah, this is likely an artifact of your past MySQL->Postgres migration. Running ALTER TABLE project ALTER COLUMN bugtrackertype SET DEFAULT NULL; manually should fix it.

[seanm]

OK, will try that now... Maybe you could take a quick look at this full output, anything look amiss?

                                                                        Table "cdash.project"
         Column          |          Type          | Collation | Nullable |               Default               | Storage  | Compression | Stats target | Description
-------------------------+------------------------+-----------+----------+-------------------------------------+----------+-------------+--------------+-------------
 id                      | integer                |           | not null | nextval('project_id_seq'::regclass) | plain    |             |              |
 name                    | character varying(255) |           | not null | ''::character varying               | extended |             |              |
 description             | text                   |           | not null |                                     | extended |             |              |
 homeurl                 | character varying(255) |           | not null | ''::character varying               | extended |             |              |
 cvsurl                  | character varying(255) |           | not null | ''::character varying               | extended |             |              |
 bugtrackerurl           | character varying(255) |           | not null | ''::character varying               | extended |             |              |
 documentationurl        | character varying(255) |           | not null | ''::character varying               | extended |             |              |
 imageid                 | integer                |           | not null | 0                                   | plain    |             |              |
 public                  | smallint               |           | not null | '1'::smallint                       | plain    |             |              |
 coveragethreshold       | smallint               |           | not null | '70'::smallint                      | plain    |             |              |
 testingdataurl          | character varying(255) |           | not null | ''::character varying               | extended |             |              |
 nightlytime             | character varying(50)  |           | not null | '00:00:00'::character varying       | extended |             |              |
 emaillowcoverage        | smallint               |           | not null | '0'::smallint                       | plain    |             |              |
 emailtesttimingchanged  | smallint               |           | not null | '0'::smallint                       | plain    |             |              |
 emailbrokensubmission   | smallint               |           | not null | '1'::smallint                       | plain    |             |              |
 emailredundantfailures  | smallint               |           | not null | '0'::smallint                       | plain    |             |              |
 emailadministrator      | smallint               |           | not null | '1'::smallint                       | plain    |             |              |
 showipaddresses         | smallint               |           | not null | '1'::smallint                       | plain    |             |              |
 cvsviewertype           | character varying(10)  |           |          |                                     | extended |             |              |
 testtimestd             | double precision       |           |          | '4'::double precision               | plain    |             |              |
 testtimestdthreshold    | double precision       |           |          | '1'::double precision               | plain    |             |              |
 showtesttime            | smallint               |           |          | '0'::smallint                       | plain    |             |              |
 testtimemaxstatus       | smallint               |           |          | '3'::smallint                       | plain    |             |              |
 emailmaxitems           | smallint               |           |          | '5'::smallint                       | plain    |             |              |
 emailmaxchars           | integer                |           |          | 255                                 | plain    |             |              |
 displaylabels           | smallint               |           |          | '1'::smallint                       | plain    |             |              |
 autoremovetimeframe     | integer                |           |          | 0                                   | plain    |             |              |
 autoremovemaxbuilds     | integer                |           |          | 300                                 | plain    |             |              |
 uploadquota             | bigint                 |           |          | '0'::bigint                         | plain    |             |              |
 showcoveragecode        | smallint               |           |          | '1'::smallint                       | plain    |             |              |
 sharelabelfilters       | boolean                |           |          | false                               | plain    |             |              |
 authenticatesubmissions | boolean                |           |          | false                               | plain    |             |              |
 bugtrackernewissueurl   | character varying(255) |           |          | ''::character varying               | extended |             |              |
 bugtrackertype          | character varying(16)  |           |          | ''::character varying               | extended |             |              |
 viewsubprojectslink     | smallint               |           | not null | '1'::smallint                       | plain    |             |              |
 ldapfilter              | character varying(191) |           |          |                                     | extended |             |              |

[williamjallen]

At first glance, some of the default values look like they might cause similar issues, but I'd have to spin up an old version of CDash locally to tell for sure. Migrations are atomic now that Postgres is the only database option, so if something fails you can fix the failure and re-run the migrations to try it again without fear of getting into a broken intermediary state.

[seanm]

So in our test VM, we were able to update from 4.0 to 4.9 without this default for column "bugtrackertype" cannot be cast automatically error. Then going to 4.10 gives the error.

Running ALTER TABLE project ALTER COLUMN bugtrackertype SET DEFAULT NULL; did not seem to help.

[seanm]

So although the 4.0 to 4.9 update seemed to go fine, playing around the cdash UI everything looked correct yesterday, but this morning there are no new builds listed (dozens were submitted overnight).

Looking in the cdash log, I see many Datatype mismatch: 7 ERROR, the first being:

root@server:/usr/local/etc/apache24/cdash.example.com/CDash/storage/logs # cat cdash.log
[2026-04-23 03:04:30] production.WARNING: Failed to process MyProject_-__-_f93d78d3-9521-4b5a-8378-026237c19931_-_c3e109a0f3c3a2894bbc7893699a2628.xml with message: PDOException: SQLSTATE[42804]: Datatype mismatch: 7 ERROR:  column "done" is of type boolean but expression is of type integer
LINE 1: update "build" set "done" = 1 where "id" = '1570085'
                                    ^
HINT:  You will need to rewrite or cast the expression. in /usr/local/etc/apache24/cdash.example.com/CDash/vendor/laravel/framework/src/Illuminate/Database/Connection.php:609
Stack trace:
#0 /usr/local/etc/apache24/cdash.example.com/CDash/vendor/laravel/framework/src/Illuminate/Database/Connection.php(609): PDOStatement->execute()
SNIP
#81 {main}

The logs from the builders have similar:

   Upload file: /Users/builder/somepath/20260422-2359/Configure.xml to https://cdash.example.com/submit.php?project=blah-blah Size: 1931
   Server Response:
{"status":1,"description":"SQLSTATE[42804]: Datatype mismatch: 7 ERROR:  column \"recheck\" is of type boolean but expression is of type integer\nLINE 1: ...ldid\", \"numfiles\", \"recheck\") values (1570412, 0, 0) on conf...\n                                                             ^\nHINT:  You will need to rewrite or cast the expression. (Connection: pgsql, Host: localhost, Port: 5432, Database: cdash, SQL: insert into \"pending_submissions\" (\"buildid\", \"numfiles\", \"recheck\") values (1570412, 0, 0) on conflict (\"buildid\") do update set \"buildid\" = \"excluded\".\"buildid\", \"numfiles\" = \"excluded\".\"numfiles\", \"recheck\" = \"excluded\".\"recheck\")"}

Help! :)

[seanm]

We tried poking at the db with:

ALTER TABLE pending_submissions ALTER COLUMN recheck DROP DEFAULT;
ALTER TABLE pending_submissions ALTER COLUMN recheck TYPE integer USING (CASE WHEN recheck THEN 1 ELSE 0 END);
ALTER TABLE pending_submissions ALTER COLUMN recheck SET DEFAULT 0;

ALTER TABLE build ALTER COLUMN done DROP DEFAULT;
ALTER TABLE build ALTER COLUMN done TYPE integer USING (CASE WHEN done THEN 1 ELSE 0 END);
ALTER TABLE build ALTER COLUMN done SET DEFAULT 0;

and now a submission works.

But we're not at all sure if this is the correct solution... @williamjallen should those be boolean or indeed integer? Thanks.

[williamjallen]

@seanm It looks like we expect the done and recheck columns to be smallint (any integer type should work). This is a good reminder that these need to be converted to boolean columns eventually though.

[seanm]

This is a good reminder that these need to be converted to boolean columns eventually though.

You mean by cdash developers like you, right? Not sysadmins running cdash?

[williamjallen]

Correct, this is something we'd need to write a migration for in a future version of CDash. For now, you should convert those to an integer type.

[seanm]

Thanks for clarifying.

So today our dashboard has some colour at least, but there's a new error:

[2026-04-24 09:56:48] production.WARNING: Failed to process MyProject-__-_5b03328a-e0d7-4d56-b3ed-3914b137092f_-_465c14e6c682e90f7a4047b41d32fd0c.xml with message: PDOException: SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "type" of relation "configureerrordiff" violates not-null constraint DETAIL: Failing row contains (1571010, null, 1). in /usr/local/etc/apache24/cdash.example.com/CDash/vendor/laravel/framework/src/Illuminate/Database/Connection.php:609

[williamjallen]

Can you please paste the output of \d configureerrordiff?