DBD::MariaDB 1.23 not updating cardinality in MariaDB 10.6.5

[reachvrpp]

It was observed that DBD::MariaDB 1.23 seems not updating cardinality in MariadB 10.6.5. Once we replace 1.23 binaries with 1.21, everything seems started working.

[choroba]

Could you provide more details? Code sample demonstrating the problem would be the best.

[reachvrpp]

sorry for late reply. Sample Perl script you can try: Please change the username,password and port as needed. basically create any table and insert data then check cardinality.

#!/usr/bin/env perl

use warnings; use strict; use DBI;

#change and with db credentials use constant dbuser => ''; use constant dbpassword => ''; use constant noofmessages => 100;

my $mariadb_socket = "<mysql.sock>"; my $dbname = 'testdb'; my $tablename = 'messages';

my ($cardhash, $sth, $stmt ,$dbh, $sql);

#create db createdb();

#create table with no. of messages createtable();

#getcardinality before inserting messages getCardinality();

#insert number of messages addmesseges();

#getcardinality after inserting messages getCardinality();

#drop the db dropdb();

#get cardinality of specified table sub getCardinality { my $dbh = dbconnect($dbname);

eval {
    $stmt = "show indexes from $tablename";
    $sth = $dbh->prepare($stmt); 
    $sth->execute() or die "error executing query: " . $sth->errstr();
    while ( my $row = $sth->fetchrow_hashref() ) {
        $cardhash->{'id'} = $row->{'Cardinality'} if($row->{'Column_name'} eq 'id');
    }
};
if ($@) {
    $sth->finish() if $sth;
    print "failed: " . $@ . "\n";
}
$sth->finish();
$dbh->disconnect(); 
print "cardinality of id column: $cardhash->{'id'}\n";

}

#insert messages sub addmesseges {
$dbh = dbconnect($dbname);
$sql = "INSERT INTO messages (message_text) VALUES (?)"; $sth = $dbh->prepare($sql) or die "Failed to prepare insert statement: " . $dbh->errstr(); for(my $i=1;$i<=noofmessages();$i++) { $sth->execute("Test Message $i") or die "Failed to insert message: " . $dbh->errstr(); } $sth->finish(); $dbh->disconnect(); print "messages inserted successfully.\n"; }

#create table sub createtable {
$dbh = dbconnect($dbname); $sql = "CREATE TABLE IF NOT EXISTS $tablename ( id INT AUTO_INCREMENT PRIMARY KEY, message_text TEXT )"; my $rows_affected = $dbh->do($sql) or die "Failed to create table: " . $dbh->errstr();

if (defined $rows_affected) {
    print "table $tablename created successfully.\n";
} else {
    print "failed: " . $dbh->errstr . "\n";
}    
$dbh->disconnect();    

}

#drop the db if it exists sub dropdb { $dbh = dbconnect($dbname);
$sql = "DROP DATABASE IF EXISTS $dbname"; my $rows_affected = $dbh->do($sql) or die "Error dropping db: " . $dbh->errstr(); if (defined $rows_affected) { print "Database $dbname dropped successfully.\n"; } else { print "failed: " . $dbh->errstr . "\n"; } $dbh->disconnect(); }

#create the database sub createdb {
#using the default database (mysql) for the connection to create a new database. $dbh = dbconnect('mysql');
$sql = "CREATE DATABASE IF NOT EXISTS $dbname"; my $rows_affected = $dbh->do($sql) or die "Error creating database: " . $dbh->errstr(); if (defined $rows_affected) { print "Database $dbname created successfully.\n"; } else { print "failed: " . $dbh->errstr() . "\n"; } $dbh->disconnect();
}

#connect database sub dbconnect { my ($dbname) = @_; my $dsn = "DBI:MariaDB:database=$dbname;mariadb_socket=$mariadb_socket"; $dbh = DBI->connect( $dsn, dbuser(), dbpassword(), { RaiseError => 1, AutoCommit => 1, PrintError => 0 } ) or die $DBI::errstr; return $dbh; }

[choroba]

I'm not sure it's Perl related. I tried the following bash script:

#! /bin/bash
{
    cat <<EOF 
USE test;
CREATE TABLE messages (id INT AUTO_INCREMENT PRIMARY KEY, message_text TEXT);
SHOW INDEXES FROM messages;
EOF
    for i in {1..100} ; do
        echo "INSERT INTO messages (message_text) VALUES ('message $i');"
    done

    cat <<EOF 
SHOW INDEXES FROM messages;
DROP TABLE messages;
EOF

} | mariadb

The output shows

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	CardinalitySub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
messages	0	PRIMARY	1	id	A	0	NULL	NULL		BTREE		NO
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	CardinalitySub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
messages	0	PRIMARY	1	id	A	4	NULL	NULL		BTREE		NO

i.e. the cardinality is 4, which is the same as what your Perl script shows me. Manually inspecting the cardinality in mariadb shows 100, though.