Procedure Updates on Manual Installation of MySQL doesn't work on LAMP

Question

I'm attempting to create an UPSERT statement using MySQL/MariaDB. The Procedure is being called from a PHP file, which works fine for INSERTs. However, for UPDATEs, INSERTs are always being done.

PHP is receiving the ID of the target, like so c075860fd58b11e6b0a03d5c165b858c (32 chars and Existing Record).

The funny thing is that I've tried running the Procedure on 4 different Servers:

• My Personal Development Server has Windows Server 2016 installed with Apache, PHP and MySQL manually installed.
• My Personal Laptop has WAMP installed on Windows 10 Pro.
• The Workstation in use at my Academic Institution has XAMP installed on Windows 7 Pro.
• The Production Environment has Apache, PHP and MySQL installed on Ubuntu Server 16.04.1.

The following procedure works on my Personal Server, but fails to do so on the rest of the devices. All of which have MySQL 5.7 installed. For the sake of eliminating possible user rights issues, I've executed the following Procedure using the root user on 3 of the 4 devices (Personal Server, Personal Laptop, Production Environment).

Could there possibly be something I'm missing out on?

I've also attempted to run the Procedure directly from phpmyadmin, where no new records are inserted and none updated. This procedure works well on my Personal Server when executed from the IDE (DataGrip).

CREATE PROCEDURE    usp_upsertProperty
                    (
                        IN var_location         VARCHAR(50)
                        , IN var_price          DECIMAL(20, 2)
                        , IN var_type           VARCHAR(50)
                        , IN var_description    TEXT
                        , IN var_id             VARCHAR(32)
                    )
BEGIN

    DECLARE         var_locationID SMALLINT;
    DECLARE         var_typeID TINYINT;

    IF (LENGTH(var_id) != 32) THEN

        SET var_id =
        (
            SELECT REPLACE(UUID(), '-', '')
        );

    END IF;

    SET var_locationID =
    (
        SELECT      L.location_id
        FROM        tbl_locations AS L
        WHERE       L.name = var_location
    );

    SET var_typeID =
    (
        SELECT      T.type_id
        FROM        tbl_propertyTypes AS T
        WHERE       T.name = var_type
    );

    INSERT INTO     tbl_properties
                    (
                        property_id
                        , location_id
                        , price
                        , type_id
                        , description
                        , listing_date
                    )
    VALUES          (
                        var_id
                        , var_locationID
                        , var_price
                        , var_typeID
                        , var_description
                        , CURRENT_TIMESTAMP()
                    )
    ON DUPLICATE KEY UPDATE
                    location_id = var_locationID
                    , price = var_price
                    , type_id = var_typeID
                    , description = var_description;

END;

Answer 1

For anyone who might encounter this issue, I decided to the ditch the INSERT INTO ... ON DUPLICATE KEY UPDATE and put the UPDATE within the IF statement.

The statement looks like this:

BEGIN

    DECLARE         var_locationID SMALLINT;
    DECLARE         var_typeID TINYINT;

    SET var_locationID =
    (
        SELECT      L.location_id
        FROM        tbl_locations AS L
        WHERE       L.name = var_location
    );

    SET var_typeID =
    (
        SELECT      T.type_id
        FROM        tbl_propertyTypes AS T
        WHERE       T.name = var_type
    );

    IF (LENGTH(var_id) != 32) THEN

        SET var_id =
        (
            SELECT REPLACE(UUID(), '-', '')
        );

        INSERT INTO     tbl_properties
                        (
                            property_id
                            , location_id
                            , price
                            , type_id
                            , description
                            , listing_date
                        )
        VALUES          (
                            var_id
                            , var_locationID
                            , var_price
                            , var_typeID
                            , var_description
                            , CURRENT_TIMESTAMP
                        );

    ELSE 

        UPDATE          tbl_properties
        SET             location_id = var_locationID
                        , price = var_price
                        , type_id = var_typeID
                        , description = var_description
        WHERE           property_id = var_id;

    END IF;

END