Driving Myself Crazy With This!

[Hate]

I'm trying to setup my database class so that by default it will create all of the tables and triggers required for my application to run. I've got everything working except for it adding the trigger. Here's the relevant code (slightly obfuscated for security reasons):

 

    private function check_consistency()
    {

        $database_query = <<<QUERY
CREATE TABLE IF NOT EXISTS d2b_users (

    id INT NOT NULL AUTO_INCREMENT,
    obfuscated INT NOT NULL,
    obfuscated VARCHAR(50) NOT NULL,
    obfuscated VARCHAR(32) NOT NULL,
    obfuscated VARCHAR(32) NOT NULL,
    obfuscated VARCHAR(32) NOT NULL,
    obfuscated BOOL NOT NULL DEFAULT '1',
    UNIQUE KEY(id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS d2b_statistics (

    id INT NOT NULL,
    obfuscated BIGINT NOT NULL DEFAULT '0',
    UNIQUE KEY(id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

delimiter |

CREATE TRIGGER d2b_auto_statistics AFTER INSERT ON d2b_users
  FOR EACH ROW BEGIN
    INSERT INTO d2b_statistics SET id = NEW.id;
END;

|

delimiter ;

QUERY;
        
        if(!$consistency = $this->link->multi_query($database_query))
        {

            die("Failed to create/verify the default database tables.");

        }

        return true;

    }

 

I've also tried removing the delimiter and the colon after the INSERT line in the trigger and I still can't get it to add properly. What's annoying is that I'm able to take the code for the trigger from above and go into phpmyadmin and paste it into the SQL and it will add and work correctly. However, I'm trying to get my class to do that automatically so the php application automatically installs itself on other servers.

 

What am I doing wrong?

[Hate]

I've been trying to figure this out for awhile now and I still haven't came up with a solution.  :'(

[fortnox007]

Sorry i haven't worked with that yet, Maybe try in phpmyadmin?, There is an option to make a php query of the stuff you do in phpmyadmin.

[Hate]

Sorry i haven't worked with that yet, Maybe try in phpmyadmin?, There is an option to make a php query of the stuff you do in phpmyadmin.

 

That's the thing by pasting this code (from above) into the SQL part of phpmyadmin it works! :

delimiter |

CREATE TRIGGER d2b_auto_statistics AFTER INSERT ON d2b_users
  FOR EACH ROW BEGIN
    INSERT INTO d2b_statistics SET id = NEW.id;
END;

|

delimiter ;

 

I just can't figure out how to get it t work when I run it from my php script... I've also tried it like this without success:

 

CREATE TRIGGER d2b_auto_statistics AFTER INSERT ON d2b_users
  FOR EACH ROW BEGIN
    INSERT INTO d2b_statistics SET id = NEW.id
END;

 

Edit: I've tried to generate the code for php from phpmyadmin after doing it successfully and it only gives me 1-2 lines of mess that mean nothing.

[Hate]

I still haven't figured out anything that works yet.  :'( I'm really hoping someone is able to help me because I'm completely out of ideas.

[mikosiko]

do you have your error reporting enabled?

 

try to include this lines in your script....

 

 error_reporting(E_ALL); 
ini_set("display_errors", 1);

 

I bet that you will see an error message regarding that trigger grants.

 

to solve it... look for how to grant TRIGGER permissions to the user that you are using in your script.

 

GRANT TRIGGER ON your_database_name.* TO <your_user>;

 

this GRANT  must be executed as a superuser.

[Hate]

do you have your error reporting enabled?

 

try to include this lines in your script....

 

 error_reporting(E_ALL); 
ini_set("display_errors", 1);

 

I bet that you will see an error message regarding that trigger grants.

 

to solve it... look for how to grant TRIGGER permissions to the user that you are using in your script.

 

GRANT TRIGGER ON your_database_name.* TO <your_user>;

 

this GRANT  must be executed as a superuser.

 

I just tried that and got nothing at all error wise. I'm running a local environment and I'm using the root login to mysql.. so I don't think there's any permission problems.

[mikosiko]

did you try to create the trigger manually in your DB?... using MySQLQuery per example?... does it works?

 

and also... are you using mysql or mysqli extension to run that multi-query?

[mikosiko]

For what it worth....  I just run this example in my side without any problem at all... maybe it can help you.

 

<?php

error_reporting(E_ALL); 
ini_set("display_errors", 1);

$mysqli = new mysqli('localhost','myuser','mypass', 'mydb') or die($mysqli->connect_error());

$database_query = <<<QUERY
CREATE TABLE d2b_users (
    id INT NOT NULL AUTO_INCREMENT,
    otro INT NOT NULL,
    UNIQUE KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE d2b_statistics (
    id INT NOT NULL,
    xx INT NOT NULL,
    UNIQUE KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TRIGGER d2b_auto_statistics AFTER INSERT ON d2b_users
  FOR EACH ROW BEGIN
    INSERT INTO d2b_statistics SET id = NEW.id;
END;
QUERY;

$mysqli->multi_query($database_query) or die($mysqli->error());         

$mysqli->close();
?>

[Hate]

For what it worth....  I just run this example in my side without any problem at all... maybe it can help you.

 

<?php

error_reporting(E_ALL); 
ini_set("display_errors", 1);

$mysqli = new mysqli('localhost','myuser','mypass', 'mydb') or die($mysqli->connect_error());

$database_query = <<<QUERY
CREATE TABLE d2b_users (
    id INT NOT NULL AUTO_INCREMENT,
    otro INT NOT NULL,
    UNIQUE KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE d2b_statistics (
    id INT NOT NULL,
    xx INT NOT NULL,
    UNIQUE KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TRIGGER d2b_auto_statistics AFTER INSERT ON d2b_users
  FOR EACH ROW BEGIN
    INSERT INTO d2b_statistics SET id = NEW.id;
END;
QUERY;

$mysqli->multi_query($database_query) or die($mysqli->error());         

$mysqli->close();
?>

 

Thanks! 

 

Apparently in my third post I was close to the final solution that you've provided, but I missed a semicolon after the INSERT statement on the trigger. I thought that the semicolon was used as the delimiter for multi_query to work properly so I only included that at the end of my trigger. I guess that was my problem.