View Issue Details

IDProjectCategoryView StatusLast Update
0035248mantisbtdb postgresqlpublic2025-02-09 07:34
Reportermaximilian70 Assigned Todregad  
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionfixed 
Product Version2.27.0 
Target Version2.27.1Fixed in Version2.27.1 
Summary0035248: Postgresql Error - db_stats.php - relation "sql_parts" does not exist
Description

When using "Show database statistics" from /admin/system_utils.php (db_stats.php) with an mantisbt installation v2.27 with a postgresql database one gets the following error:

Database query failed. Error received from database was #-18: ERROR: relation "sql_parts" does not exist
LINE 1: SELECT COUNT(*) FROM sql_parts
^ for the query: SELECT COUNT(*) FROM sql_parts.

Environment Details:

  • MantisBT Version 2.27.0
  • PHP Version 8.2.26
    MantisBT Database Information
  • Database Schema Version 213
  • ADOdb Version 5.22.7
    System
  • Postgresql Version 15
  • OS Debian12
  • Webserver Apache2.4 with php-fpm 8.2

With a mysql database the error does not occur.

Upon closer inspection of the error and the db_stats.php file I saw that the tables get retrieved by a call to db_get_table_list() which makes a call to $g_db->MetaTables( 'TABLE' ) from ADOdb. It seems all tables are retrieved correctly but when using postgresql the sql_parts table is additionally retrieved. This does not happen when using mysql. This causes the problem because when mantis executes the query to get the stats of the table it does not exist because to access the table correctly "information_schema.sql_parts" has to be used but only sql_parts is in the tables array. However the table should not get retrieved by db_get_table_list() anyway. I also looked into the ADOdb source code and found out that for the pdo_pgsql driver the following query is executed for the MetaTables function:

select tablename,'T' from pg_tables
where tablename not like 'pg\_%' and schemaname  not in ( 'pg_catalog','information_schema')
union
select viewname,'V' from pg_views
where viewname not like 'pg\_%'  and schemaname  not in ( 'pg_catalog','information_schema')

I executed this query manually using pgAdmin (same user and database as in the config_inc.php) and it only returned the correct tables of mantisbt without the sql_parts table. Therefore I was not able to elaborate what causes the sql_parts tablename to be retrieved by mantis.

I think this could be a bug related to version 2.27 because when I downgrade to 2.25.2 (ADOdb Version 5.20.20, Schema Version 213) the error is gone altough I am using the same database. When I upgrade to 2.27 again I also get the error again.

Steps To Reproduce

The easiest way to reproduce the error is to setup a new mantisbt installation with a new postgresql database.
I testet it in the following environment:

  • MantisBT Version 2.27.0
  • PHP Version 8.2.26
    MantisBT Database Information
  • Database Schema Version 213
  • ADOdb Version 5.22.7
    System
  • Postgresql Version 15
  • OS Debian12
  • Webserver Apache2.4 with php-fpm 8.2

1.) Setup the described environment
2.) Log in and navigate to /admin/
3.) Select the Tab "System Utilities" and click "Display" for "Show database statistics"

Additional Information

I stumbled accross this issue while migrating my mantisbt database from mysql to postgresql. I asked for help in the forums where I was told to create a bug report here. Related forum-post: https://mantisbt.org/forums/viewtopic.php?t=33271

TagsNo tags attached.

Relationships

parent of 0035257 resolveddregad Update ADOdb to 5.22.8 

Activities

raspopov

raspopov

2025-01-18 23:26

reporter   ~0069721

Last edited: 2025-01-24 10:08

It looks like the system table 'sql-parts' is now available as of PostgreSQL version 8.2.

And probably should be also masked out in the ADODB.

In my humble opinion this will not affect the performance of MantisBT in any case, you can ignore this message and move to a new database without worrying.

dregad

dregad

2025-01-20 10:08

developer   ~0069727

Thanks for the detailed bug report.

I believe @raspopov is correct, this seems to be a bug in ADOdb, which should exclude the sql_parts information schema table that was introduced in PostgreSQL 8.2.

Reported upstream: https://github.com/ADOdb/ADOdb/issues/1059

I executed this query manually using pgAdmin (same user and database as in the config_inc.php) and it only returned the correct tables of mantisbt without the sql_parts table. Therefore I was not able to elaborate what causes the sql_parts tablename to be retrieved by mantis.

The metaTableSql query executed by ADOdb differs depending on PostgreSQL version, but I believe the version check in ADODB_postgres64::MetaTables() if ($info['version'] >= 7.3) { is incorrect, it should probably use version_compare() instead of numeric comparison. It looks like you're executing the query for PostgreSQL 7.2 and older:

select tablename,'T' from pg_tables 
where tablename not like 'pg\_%' 
and tablename not in ('sql_features', 'sql_implementation_info', 'sql_languages', 'sql_packages', 'sql_sizing', 'sql_sizing_profiles')
    union
select viewname,'V' from pg_views where viewname not like 'pg\_%'

when I downgrade to 2.25.2 (ADOdb Version 5.20.20, Schema Version 213) the error is gone

That is likely due to how ADOdb retrieves PostgreSQL version information (ADODB_postgres64::serverInfo()), which changed in 5.21.0.

@maximilian70 Could you please provide the output of pg_version(); on your box, so I can confirm that ?

maximilian70

maximilian70

2025-01-23 20:48

reporter   ~0069749

I debugged a little bit and can confirm that it is the ADOdb code and that it has to do with the version check as you assumed.

At first I looked in the wrong driver file (adodb-pdo_pgsql.inc.php). I thought it is used because I use php-psql (PHP module pdo_pgsql) on this system. May I ask why adodb-postgres64.inc.php is used instead? I have no experience with php but because of the filename I thought that it is just a really old driver for PostgreSQL 6.4 so I did not look into it at first.

As you have already mentioned, the ServerInfo() functions of these two drivers are different, which I did not recognise at first because I only looked at the MetaTables() function. pg_version(); returns the following on my system:

15.10 (Debian 15.10-0+deb12u1)

ADODB_postgres64::serverInfo() returns exactly this string. Therefore the if ($info['version'] >= 7.3) gets evaluated to false and the "wrong" query that also retrieves the sql_parts table gets executed.

ADODB_pdo_pgsql::serverInfo() returns 15.10 like ADODB_postgres64::serverInfo() did before it got changed in 5.21.0. Therefore the if ($info['version'] >= 7.3) gets evaluated to true in the earlier version of mantis (2.25.2 with ADOdb Version 5.20.20) and the query I mentioned in my initial bug report gets executed, which retrieves the correct tables without sql_parts.

However in the PostgreSQL page of the ADOdb docs it is mentioned that the PostgreSQL drivers are currently undergoing a major refactoring. I hope this fixes the issue in the future.

For now I will just ignore the error because in production the admin folder is deleted anyway. (Or if I need the functionality I will just patch the code manually so that sql_parts gets removed from the retrieved tables array)
I also think that this small issue should not affect the performance of mantisbt and should not cause any further errors in the future.

Thanks for your help. Kind regards

dregad

dregad

2025-01-24 03:53

developer   ~0069750

@maximilian70 thanks for the feedback.

May I ask why adodb-postgres64.inc.php is used instead?

This is a legacy thing. When PostgreSQL support was added to MantisBT via ADOdb, PDO did not even exist (it was introduced in PHP 5.1 IIRC).

pg_version(); returns the following on my system: 15.10 (Debian 15.10-0+deb12u1) ADODB_postgres64::serverInfo() returns exactly this string. Therefore the if ($info['version'] >= 7.3) gets evaluated to false and the "wrong" query that also retrieves the sql_parts table gets executed.

Exactly what I suspected. Thanks for the confirmation.

that the PostgreSQL drivers are currently undergoing a major refactoring

Correct, see https://github.com/ADOdb/ADOdb/pull/1008

But fixing this regression does not need to wait for the refactoring to be complete, it's a simple and straightforward change, that will be treated as a hot fix and released soon.

Related Changesets

MantisBT: master-2.27 c6726c7b

2025-01-25 06:15

dregad


Details Diff
Bump adodb/adodb-php from 5.22.7 to 5.22.8

Fixes 0035257, 0035248
Affected Issues
0035248, 0035257
mod - composer.lock Diff File