WITH RECURSIVE product_hierarchy AS (
    SELECT product_list_uuid,
        product_uuid_parent,
        product_uuid,
        product_list_order_priority,
        0 AS level,
        product_uuid AS breadcrumb
    FROM product_list
    WHERE product_uuid_parent = p_product_uuid
    UNION
    SELECT pl.product_list_uuid,
        pl.product_uuid_parent,
        pl.product_uuid,
        pl.product_list_order_priority,
        ph.level + 1,
        ph.breadcrumb || ' => ' || pl.product_uuid
    FROM product_list pl
          INNER JOIN product_hierarchy ph ON ph.product_uuid = pl.product_uuid_parent
    )
SELECT * FROM product_hierarchy ORDER BY product_list_order_priority DESC;

• DEFINER = `skip-grants user`@`skip-grants host`
• DEFINER = root@localhost

Dazu kommen DEFINER mit dem Usernamen des Systems.

Außer, im generierten Dump per „suchen & ersetzen“ die DEFINER zu entfernen, gibt es wohl keine Lösung.

Lösung bei AWS funktioniert:

-- remove
sed -i -e 's/DEFINER=`root`@`localhost`//g' dump.sql
sed -i -e 's/DEFINER=`skip-grants user`@`skip-grants host`//g' dump.sql
-- find & replace
sed -i -e 's/DEFINER=`root`@`localhost`/DEFINER=`masteruser`@`%`/g' dump.sql

mysqlpump

Dann gibt es seit 5.6 mysqlpump:

sudo mysqlpump dbname --skip-definer > /home/allapow/dump.sql
# Dump progress: 1/9 tables, 0/719 rows
# mysqlpump: [ERROR] (1449) The user specified as a definer ('skip-grants user'@'skip-grants host') does not exist
# Dump process encountered error and will not continue.
# Speicherzugriffsfehler

Folgende Funktion ist nicht deterministisch:

CREATE FUNCTION func_count_something(p_someid INT) RETURNS INTEGER
BEGIN

    DECLARE some_count INTEGER;
    SELECT COUNT(*)
    INTO artikel_count
    FROM some_table
    WHERE someid = p_someid;
    RETURN some_count;
END;

Fehlermeldung bei der Erstellung:

[HY000][1418] This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Gleiche Funktion – deterministisch:

CREATE FUNCTION func_count_something(p_someid INT) RETURNS INTEGER
-- this two lines do the trick
    DETERMINISTIC
    READS SQL DATA
BEGIN

    DECLARE some_count INTEGER;
    SELECT COUNT(*)
    INTO artikel_count
    FROM some_table
    WHERE someid = p_someid;
    RETURN some_count;
END;

dev.mysql.com – 25.7 Stored Program Binary Logging

Helfen würde auch ein:

SET GLOBAL log_bin_trust_function_creators = 1;

zur Erstellungszeit einer FUNCTION.

SELECT GROUP_CONCAT(DISTINCT IF(TRIM(fieldname)='', NULL,TRIM(fieldname)) ORDER BY fieldname DESC SEPARATOR '|')

Ärger mit der MySQL Installation

Fehlermeldung könnte sein:

1. This installation of MySQL is already upgraded to 5.7.36, use --force if you still need to run mysql_upgrade

# mache ich:
sudo dpkg --configure -a
# ...kommt Fehler (1) und bleibt hängen
# hilft bei (1) nur scheinbar
sudo dpkg-reconfigure mysql-server-5.7

mehrere Leerzeichen mit einem ersetzen …seit MySQL 8

UPDATE table_name SET column_name = REGEXP_REPLACE(column_name, '[ ]+', ' ');

CREATE OR REPLACE FUNCTION lerp_query_suppliers_for_product_all(p_product_uuid UUID)
    RETURNS TABLE
            (
                SUPPLIER_UUID          UUID,
                SUPPLIER_NO            INTEGER,
                SUPPLIER_NAME          VARCHAR(200),
                SUPPLIER_NAME2         VARCHAR(200),
                SUPPLIER_STREET        VARCHAR(200),
                SUPPLIER_ZIP           VARCHAR(10),
                SUPPLIER_CITY          VARCHAR(200),
                COUNTRY_ID             INTEGER,
                SUPPLIER_TEL           VARCHAR(100),
                SUPPLIER_FAX           VARCHAR(100),
                SUPPLIER_EMAIL         VARCHAR(200),
                SUPPLIER_WWW           VARCHAR(200),
                SUPPLIER_LANG_ISO      ENUM_SUPPORTED_LANG_ISO,
                INDUSTRY_CATEGORY_UUID UUID,
                SUPPLIER_TAX_ID        VARCHAR(20),
                SKR_03_CODE            CHAR(4),
                SUPPLIER_TIME_CREATE   TIMESTAMP,
                SUPPLIER_TIME_UPDATE   TIMESTAMP,
                SUPPLIER_NOTE          TEXT
            )
AS
$$
DECLARE
BEGIN
    RETURN QUERY SELECT s.*
                 FROM supplier s
                 WHERE s.supplier_uuid IN (
                     SELECT DISTINCT po.supplier_uuid
                     FROM purchase_order po
                     WHERE po.purchase_order_uuid IN (
                         SELECT DISTINCT poi.purchase_order_uuid
                         FROM purchase_order_item poi
                         WHERE poi.product_uuid = p_product_uuid
                     )
                 )
                    OR s.supplier_uuid IN (
                     SELECT DISTINCT pr.supplier_uuid
                     FROM purchase_request pr
                     WHERE pr.purchase_request_uuid IN (
                         SELECT DISTINCT pri.purchase_request_uuid
                         FROM purchase_request_item pri
                         WHERE pri.product_uuid = p_product_uuid
                     )
                 )
                    OR s.supplier_uuid IN
                       (SELECT ps.supplier_uuid FROM product_supplier ps WHERE ps.product_uuid = p_product_uuid);
END;

$$ LANGUAGE plpgsql;

ALTER FUNCTION lerp_query_suppliers_for_product_all(UUID) OWNER TO postgres;

…und so ruft man sie auf:

SELECT * FROM lerp_query_suppliers_for_product_all('80ef3210-c6a8-47e4-a50e-0cf5792077d7');

stackoverflow

stored procedure/function stub

CREATE OR REPLACE FUNCTION app_get_foo(P_TEST_UUID UUID)
    RETURNS TABLE
            (
                TEST_UUID UUID
            )
    LANGUAGE plpgsql
AS
$$
DECLARE
BEGIN

END;
$$

Ohne Zeitzone:

extract(epoch from mytimestampfield::timestamp(0))

Die 0, als Argument für timestamp() bedeutet null Nachkommastellen.

SELECT uuid_generate_v4(),
       doc_order_uuid,
       doc_order_filename,
       doc_order_price,
       doc_order_tax,
       user_uuid_create
INTO TABLE doc_order_send
FROM doc_order;

…es wirft aber den Fehler: [42P07] ERROR: relation "doc_order_send" already exists

Funktionieren tut folgendes:

INSERT INTO doc_order_send
SELECT uuid_generate_v4(),
       doc_order_uuid,
       doc_order_filename,
       doc_order_price,
       doc_order_tax,
       user_uuid_create
FROM doc_order;

mysql -u root -p db_name < /path/to/db_dump.sql
Enter password: 
ERROR 1418 (HY000) at line 571: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

…der Datenbank Import war unvollständig.

Beheben kann man das mit folgendem befehl in der MySQL Konsole:

SET GLOBAL log_bin_trust_function_creators = 1;

…oder diesen Wert in der my.cnf anpassen.

$lastId = $this->getAdapter()->getDriver()->getConnection()->getLastGeneratedValue('public.seq_some_name');

stackoverflow

SELECT *
FROM lerp_get_product_list_all(?) AS listall
         LEFT JOIN product ON product.product_uuid = listall.product_uuid
         LEFT JOIN product_no ON product_no.product_no_uuid = product.product_no_uuid
         LEFT JOIN quantityunit ON quantityunit.quantityunit_uuid = product.quantityunit_uuid
         LEFT JOIN product_calc ON product_calc.product_uuid = listall.product_uuid -- can be null

Das passiert nicht wenn man den JOIN mit den NULL Werten an den Anfang stellt:

SELECT *
FROM lerp_get_product_list_all(?) AS listall
         LEFT JOIN product_calc ON product_calc.product_uuid = listall.product_uuid -- can be null
         LEFT JOIN product ON product.product_uuid = listall.product_uuid
         LEFT JOIN product_no ON product_no.product_no_uuid = product.product_no_uuid
         LEFT JOIN quantityunit ON quantityunit.quantityunit_uuid = product.quantityunit_uuid