MySQL privileges / Stored Procedure problem?

[jhodgski]

Hiya,

My teta-wurfl installation works when I set the db as MySQL4, but not MySQL5.

SHOW GRANTS reports:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `mydb`.* TO 'me'

Is there anything missing?

I'd really like to use the MySQL5 setting for performance benefits. Can anyone help?

NB - MySQL Server Version: 5.1.47-rel11.2-log

Many thanks,
James

[jhodgski]

I'm also getting this same problem with another host.

SHOW GRANTS gives:
GRANT ALL PRIVILEGES ON `mwe`.* TO 'mwe'@'localhost'

But admin/install.php gives:
Database Settings
Host: localhost:3306
Username: mwe
Connecting to DB server... OK
ERROR: Could not create MySQL Procedure. Please make sure you have these privileges: CREATE_ROUTINE, DROP, EXECUTE
DB Server Version: 5.1.49-3-log
DB Name (schema): mwe

[kamermans]

Hmmm... CREATE ROUTINE should be enough to create the procedure. Can you see if they were created? Maybe there's a problem with the install script, but it's actually working. You can use "SHOW PROCEDURE STATUS" to see your procedures. You should see something like this:

Code: Select all

mysql> SHOW PROCEDURE STATUS;
+------------+---------------------------+-----------+--------------------------+
| Db         | Name                      | Type      | Definer                  |
+------------+---------------------------+-----------+--------------------------+
| yourdbname | TeraWurfl_FallBackDevices | PROCEDURE | yourusername@yourserver  |
| yourdbname | TeraWurfl_RIS             | PROCEDURE | yourusername@yourserver  |
+------------+---------------------------+-----------+--------------------------+


If you don't have those two procedures, can you login to phpMyAdmin as your Tera-WURFL user and try create the procedure manually by running this command?:

Code: Select all

CREATE PROCEDURE `TeraWurfl_RIS`(IN ua VARCHAR(255), IN tolerance INT, IN matcher VARCHAR(64))
BEGIN
DECLARE curlen INT;
DECLARE wurflid VARCHAR(64) DEFAULT NULL;
DECLARE curua VARCHAR(255);

SELECT CHAR_LENGTH(ua)  INTO curlen;
findua: WHILE ( curlen >= tolerance ) DO
   SELECT CONCAT(LEFT(ua, curlen ),'%') INTO curua;
   SELECT idx.DeviceID INTO wurflid
      FROM TeraWurflIndex idx INNER JOIN TeraWurflMerge mrg ON idx.DeviceID = mrg.DeviceID
      WHERE mrg.match = 1 AND idx.matcher = matcher
      AND mrg.user_agent LIKE curua
      LIMIT 1;
   IF wurflid IS NOT NULL THEN
      LEAVE findua;
   END IF;
   SELECT curlen - 1 INTO curlen;
END WHILE;

SELECT wurflid as DeviceID;
END


If that works, here's procedure #2:

Code: Select all

CREATE PROCEDURE `TeraWurfl_FallBackDevices`(current_fall_back VARCHAR(64))
BEGIN
WHILE current_fall_back != 'root' DO
   SELECT capabilities FROM TeraWurflMerge WHERE deviceID = current_fall_back;
   SELECT fall_back FROM TeraWurflMerge WHERE deviceID = current_fall_back INTO current_fall_back;
END WHILE;
END


If it doesn't work, does MySQL give you a more descriptive error message? Can you use an admin account in the Tera-WURFL config to get the procedures in place, then change back to your normal DB user?

[jhodgski]

SHOW PROCEDURE STATUS returns an empty set.

Having just executed you procedure code (which would only be accepted after I added a "DELIMITER //" line at the beginning), SHOW PROCEDURE STATUS now shows the two procedures. Does that mean there's a problem with your install script? If so, let me know if you need a guineau pig!

Am I now safe to switch over to the MySQL5 connector?

Thanks,
James

[kamermans]

Ah, yes - sorry about the lack of delimiter, I took this definition straight of my MySQL Client (Navicat for MySQL). I'm not sure why you're seeing this problem - very strange. Assuming you defined the procedures under the same credentials that you are connecting with Tera-WURFL, everything should work, otherwise, your Tera-WURFL DB User might not have permissions to execute those procedures since they're not the definer. A quick way to check it would be to clear your device cache from the web interface, then change to the MySQL5 connector and run the test page again. This will force both procedures to be used. You will certainly not get the expected result if it's not working, and will probably get some MySQL errors or an internal server error (it's too time consuming for Tera-WURFL to check for permissions and existence of tables on every detection so it will just fail).

[jhodgski]

Just to confirm, I am using the same MySQL username and password everywhere.

Ran your quick check on my first server. Result was that there was no output (below the horizontal line) when I used the MySQL5 connector, so I've had to change it back again to the MySQL4 connector,

Ran the quick check on second server. This time, below the horizontal line, I get this output:
Fatal error: Uncaught exception 'Exception' with message 'Error in DB RIS Query: Thread stack overrun: 8888 bytes used of a 131072 byte stack, and 128000 bytes needed. Use 'mysqld -O thread_stack=#' to specify a bigger stack.. Query: CALL TeraWurfl_RIS('SonyEricssonK700i/R2AC SEMC-Browser/4.0.2 Profile/MIDP-2.0 Configuration/CLDC-1.1',16,'SonyEricsson') ' in /websites/123reg/LinuxPackage22/xx/xx/xx/xxxxxxxxxx.xx.xx/public_html/JH/tera_wurfl/kamermans-Tera-WURFL-90057fb/DatabaseConnectors/TeraWurflDatabase_MySQL5.php:116 Stack trace: #0 /websites/123reg/LinuxPackage22/xx/xx/xx/xxxxxxxxxx.xx.xx/public_html/JH/tera_wurfl/kamermans-Tera-WURFL-90057fb/UserAgentMatchers/UserAgentMatcher.php(77): TeraWurflDatabase_MySQL5->getDeviceFromUA_RIS('SonyEricssonK70...', 16, Object(SonyEricssonUserAgentMatcher)) #1 /websites/123reg/LinuxPackage22/xx/xx/xx/xxxxxxxxxx.xx.xx/public_html/JH/tera_wurfl/kamermans-Tera-WURFL-90057fb/UserAgentMatchers/SonyEricssonUserAgentMatcher.php(28): UserAgentMatcher->risMatch('SonyEricssonK70...', 16) in /websites/123reg/LinuxPackage22/xx/xx/xx/xxxxxxxxxx.xx.xx/public_html/JH/tera_wurfl/kamermans-Tera-WURFL-90057fb/DatabaseConnectors/TeraWurflDatabase_MySQL5.php on line 116

Does that help at all?

[kamermans]

It sounds like your MySQL resources are limited on this server as well, so it's running out of memory during the procedure execution. Perhaps the first server has the same problem but PHP error logging is disabled.

[Jordan]

Use different user to recreate stored procedures:-
I have the following setup:
• a dev database where changes are applied as they are needed
• a beta (nightly builds) that houses the current (collective) state of the db for the public to test
• a production db that houses the actual db that is used on the real site
Now, the current deployment works as follows
1. Code is created on dev as it is needed.
2. Stored procedures (and Alter commands) are generated in a file and uploaded to the server that houses the beta db.
3. The server then executes an svn update and executes the sql-files in batch mode via cronjob
4. Done! Enjoy your nightly build
5. After enough development is done, the production db is updated manually. Step 5 is irrelevant for the actual problem so I won't mention it further.
Now this all worked well until I added the stored procedures update routine. My specific problem is the DEFINER part of the stored procedure.
What I did on the MySQL side was the following. Since I am aware of security and stuff, I added a special user cron that is only granted DDL Rights (e.g. CREATE, ALTER, DROP) and cannot read the actual content of the db. However, once the script is done, it added DEFINER=cron@localhost to the stored procedure. Consequently, no page is served because cron is not allowed to view anything. I then changed the DEFINER manually to DEFINER=actual_user@localhost to try something out, but then MySQL tells me that cron needs SUPER privileges to do that. After consulting the manual about the SUPER privilege, I am unsure that I actually want to give cron that privilege since it surely does not need that much power.
Another alternative is to let actual_user execute the batch-file but that user is only allowed to view data and edit data. Additionally, I think that a user who only works with content really doesn't need any DDL rights.
It would be awesome if I could just give cron the right to change the definer, but I guess it won't be that easy. Any alternatives to my problem?