CREATE DEFINER and Superuser permission

[mobileWeb]

Hi all-
First post here and am thankful to all (Steve) the work on this great piece of code!

I initially installed this on my testbed (WAMP) and all is working fine, my problems started when I installed to my webhosting (HostGator)
I had problems getting past the installation of the database as my host capped the Mem at 64M which as I read other post it needs 256M
min to run the large file. Reading the other post I was able to backup my local database and restore in on my hosting site.
That didnt go smooth either as I received an error msg for the CREATE DEFINER Stored Procedures, basically to run these procedures
my database user needs EXECUTE permission which is clearly stated in instructions but my host doesnt allow this with the current plan I have.
I dont want to upgrade my plan or move webhost, is there another way around this/these issues so I can effectivly run this without dumbing down
the scripts?
Error msg

Code: Select all

CREATE DEFINER = `myDatabase_name`@`%` 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 $$

MySQL said: Documentation
#1227 - Access denied; you need the SUPER privilege for this operation


[kamermans]

Hi mobileWeb, if you don't have the EXECUTE permission on your web host, you can use the MySQL4 database connector. This will still use the same database tables, but will not use the stored procedures (or try to create them). The performance decrease will not be significant if you are not hosting a high traffic site. If you are already limited to 64MB of memory, odds are that you will run into many more bottlenecks before Tera-WURFL becomes a bottleneck in your environment. To change this setting, edit TeraWurflConfig.php and find "MySQL5" and change it to "MySQL4".

[mobileWeb]

Steve, thanks for the immediate response, ok so I went ahead and changed the config to MYSQL4, I did a fresh install to be safe on all.
I again used my local WAMP setup and created DB again this time using the MYSQL4 settings in config, then did a backup.
Then on web host I dropped DB and created it again fresh using the backup from local and ya no error msg...

So the next thing to do is test it out and make sure it works, well using the check_wurfl.php (test script) I ran it and get this error

Code: Select all

Error: Table 'database_terawurfl.TeraWurflCache' doesn't exist

well thats not good so a little looking at some post on the forum I see some others having issues with case sensitive names in DB
so I ran SHOW VARIABLES LIKE 'lower_case_table_names'; on both local and hosting env

Code: Select all

webhost results:
Value lower_case_table_names 0
local results:
alue lower_case_table_names  1


Thought the results should be "The value should be 1 or 2"?
Anyway the error above looks like its looking for table 'TeraWurflCache' when my tables show a 'terawurflcache'
can you explain that one?

further testing I went ahead with Rebuild the device cache to see if that changes anything and yes it did. now when I run
check_wurfl.php (test script) I get this instead of previous error, seems like it might all be related to the case sensitive issue?

Code: Select all

Fatal error: Call to a member function close() on a non-object in /home/myaccount_name/public_html/mysite.com/Tera-WURFL/DatabaseConnectors/TeraWurflDatabase_MySQL4.php  on line 83

LIne 77-85 of TeraWurflDatabase_MYSQL4.php

Code: Select all

77    // Exact Match
78   public function getDeviceFromUA($userAgent){
79      $this->numQueries++;
80      $query = "SELECT `deviceID` FROM `".TeraWurflConfig::$MERGE."` WHERE `user_agent`=".$this->SQLPrep($userAgent);
81      $res = $this->dbcon->query($query);
82      if($res->num_rows == 0){
83         $res->close();
84         return false;
85      }


and here is my tables:

Code: Select all

TeraWurflCache
TeraWurflCache_TEMP
terawurfl_alcatel
terawurfl_android
terawurfl_aol
terawurfl_apple
terawurfl_benq
terawurfl_blackberry
terawurfl_bot
terawurfl_catchall
terawurfl_chrome
terawurfl_docomo
terawurfl_firefox
terawurfl_grundig
terawurfl_htc
terawurfl_kddi
terawurfl_konqueror
terawurfl_kyocera
terawurfl_lg
terawurfl_mitsubishi
terawurfl_motorola
terawurfl_msie
terawurfl_nec
terawurfl_nintendo
terawurfl_nokia
terawurfl_opera
terawurfl_operamini
terawurfl_panasonic
terawurfl_pantech
terawurfl_philips
terawurfl_portalmmm
terawurfl_qtek
terawurfl_safari
terawurfl_sagem
terawurfl_samsung
terawurfl_sanyo
terawurfl_sharp
terawurfl_siemens
terawurfl_sonyericsson
terawurfl_spv
terawurfl_toshiba
terawurfl_vodafone
terawurfl_windowsce
terawurflcache
terawurflindex
terawurflmerge

[kamermans]

Ah yes, the infamous lowercase table name issue! When I said "The value should be 1 or 2" I meant that if it was 1 or 2 the server would store the table names in lowercase and query them in a case-insensitive manner. The problem is that your WAMP installation is on Windows and all the Windows filesystems (like NTFS) do not preserve the case of filenames properly, so the MySQL developers make Windows installs default to lowercase tablenames. When you export the database from Windows and import it into Linux (on your web host), it imports them in the lowercase format like on Windows. The only easy way to get around this is to enable lowercase table names on your Linux web host, but I highly doubt this is a possibility on a shared web host. A better workaround for you would be to use the WURFL Customizer to create a custom WURFL file. This program will let you strip out all the capabilities that you don't need from the WURFL, creating a much smaller version that you will be able to use to update Tera-WURFL directly from your web host.

[kamermans]

...or you could delete your database in Windows and edit the my.ini file and set lower_case_table_names = 0 then restart your server and reload the WURFL.

[mobileWeb]

Yea thats sounds good. I'll give that a go first and report back with results.
Just curious as to why terawurfl db wasnt created with lower case names so this would be more compatible?
Also if its just a matter of php code cant a stringtolower be used to format the db table strings before calling them?

[mobileWeb]

Changed or added settings to my.ini under Windows

Code: Select all

set-variable = lower_case_table_names= 0

Restarted WAMP server (local) and did fresh install of terawurfl db
note on startup of MySQL you get a notice error:

Code: Select all

Server was started.
You have forced lower_case_table_names to 0 through a command-line option, even though your file system 'C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\Data\' is case insensitive.  This means that you can corrupt a MyISAM table by accessing it with different cases. You should consider changing lower_case_table_names to 1 or 2

After fresh install I see the tables are now upper/lower case as they should be, a quick test and all is running good on local
did backup and dropped old tables on Linux webhost, did restore, tested and full joy on that!

Thanks for all the prompt support on this Steve

Dont see a place in your Forum to make suggestions on possible features, bugs or OS specific sticky topics so I'll add here;
Have install.php detect if you are installing on Windows OS and maybe have a error/notice msg about the set-variable = lower_case_table_names= 0 issue.
or some logic to check if db tables are in case that you expect before calling on db table, throw a error that would make sense to user.
In the Admin page not sure how often the database is updated but it would be good to know if there is a update, I see you can subscribe to the latest RSS feed
on Sourceforge for latest but makes more sense to have it on admin page, "A button to see if theres a database update"

[kamermans]

I'm glad you got it working! I will definitely consider your suggestions (btw, here's the feature request page: viewforum.php?f=7). In fact, I've already done some work on the "check for new version" feature for 2.1.3

[mobileWeb]

ah thanks see the feature request topic now
Thanks again Steve!