Why is my database name truncated?

Question

I'm using MariaDB v5.x and want to return a long database's name with the query. I can successfully do this with a simple query, but with a more involved query the database name comes back truncated [34 characters]. Why does this happen and is there a way to retrieve the full, un-truncated name?

This example query works:

SELECT DATABASE() as 'database', hour as 'hour'
FROM time
WHERE hour = 1;

However, this type of query returns the truncated database name:

SELECT DATABASE() as 'database',
       t.hour as 'hour',
       d.day as 'day'
FROM   time t,
       day d
WHERE  t.day_pk = day.pk AND
       t.hour   = 1      AND
       d.day    = 22
UNION ALL
SELECT DATABASE() as 'database',
       t.hour as 'hour',
       d.day as 'day'
FROM   time t,
       day d
WHERE  t.day_pk = day.pk AND
       t.hour   = 1      AND
       d.day    = 24;

Thanks for any /all help.

D.

Answer 1

I can confirm that the same behaviour is present even in MariaDB 10.7.1.

The truncation appears to happen when UNION is used.

Here is a workaround - use the substring function to indicate you want the full length:

SELECT substring(database() FROM 1 FOR length(database())) AS "database"
UNION ALL
SELECT substring(database() FROM 1 FOR length(database()));

Answer 2

Confirming what @dbdemon posted this is a bug that is also present in all versions. Using --column-type-info shows a little more insight as to what's happening.

$ db=$(printf 'a%.0s' {1..40})
$ echo $db
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
$ podman run -d --rm -e MYSQL_ALLOW_EMPTY_PASSWORD=1 -e MYSQL_DATABASE=$db -e MYSQL_USER=u -e MYSQL_PASSWORD=p --name m55 mariadb:5.5
1b2c0740bf664b8a3da8071bb96234a86fe8dd5f3eb6348598f5d0c2b59ede56

$ podman exec -ti m55 mysql -u u -pp  --column-type-info -e 'select database() as "database" union all select database()' $db
Field   1:  `database`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     34
Max_length: 34
Decimals:   0
Flags:      


+------------------------------------+
| database                           |
+------------------------------------+
| aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
| aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
+------------------------------------+

$ podman exec -ti m55 mysql -u u -pp  --column-type-info -e 'select database()' $db
Field   1:  `database()`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     34
Max_length: 40
Decimals:   31
Flags:      


+------------------------------------------+
| database()                               |
+------------------------------------------+
| aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
+------------------------------------------+

From the code we can see the 34 comes from the MAX_FIELD_NAME rather than the 64 NAME_CHAR_LEN from various definitions of Db in system tables.

These sorts of bugs are easy to fix. Please report them (whether easy or not).

Update: Bug MDEV-27544 created

Update: Fixed in 10.2.42+, 10.3.33+, 10.4.23+, 10.5.14+, 10.6.6+, 10.7.2+ and 10.8.0+