Music on Hold Bug - Incorrect data for application submission

[BlazeStudios (Tom Ray)]

The update function needs to be fixed. I’d put a PR on github but since everything is a mess and seems to be being ignored and we don’t have an issue tracker anymore here is where this goes. @lgaetz What is the status of everything?

When you try to add an application for Music on Hold, you end up with this following error:

SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: ‘’ for column asterisk.music.random at row 1

Not sure where the invalid datetime format error is coming from since the music table doesn’t have a datetime entry. However, the incorrect integer value is the real issue here. As you can see in the current code snipper from Music.class.php, the $random variable is set to ‘false’ if no value is given.

The problem with that is the table structure has random as a tinyint with a default value of 0 and is expecting a correct integer at this point. In fact, it’s been expecting an integer since I can see going back through the code.

To clarify, any FreePBX version (v14-v16) running with MariaDB 5.5.x (so any Distro installs for sure) this is not an issue, the issue only exists on FreePBX v16 systems I have on Debian 12 which is using MariaDB 10.11.4.

I am pretty sure, like most all other issues with MySQL/MariaDB versions greater than 5.x, that this is a permissions issue as every release beyond 5.x has much, much stricter policies and rules for the database and data handling.

Based on quick looking, I see the forms sending “yes|no” as random options instead of an integer base and I can’t see where the “yes|no” is being converted because I changed $random = false to $random = 0 in the function variables but still got errors. I had to override with adding $random = 0; to the function and everything saved right.

public function updateCategoryByID($id, $type, $random = false, $application = '', $format = '') {
                $sql = "UPDATE music SET type = :type, random = :random, application = :application, format = :format WHERE id = :id";
                $sth = $this->db->prepare($sql);
                $sth->execute(array(
                        "type" => $type,
                        "random" => $random,
                        "application" => $application,
                        "format" => $format,
                        "id" => $id
                ));
                needreload();
        }

[jfinstrom (James Finstrom)]

@BlazeStudios

SQLSTATE[22007]: Invalid datetime format

So apparently this is a php quirk and has nothing to do with datetime.

To the relevant developer:

The documentation suggests that boolean values (true/false) should automatically map to integer equivalents (1/0). However, it has been observed that this mapping does not always function as expected. Specifically, the condition $value == 'yes' fails to produce the desired outcome.

To address this issue, it is proposed to convert the variable $random to an integer representing either 0 or 1. This solution maintains backward compatibility and is expected to resolve the inconsistency across different MariaDB/MySQL installations.