Greška u bazi sa Joomfish komponentom i jos_jf_languages tabelom

[Sanitarac]

Ovako,

napravljen website lokalno i sve funkcionira, no tokom preseljenja baze dobivam error u jos_jf_languages i potrebna su mi super admin prava da tu tabelu prebacim....

Kako riješiti problem?

Error:

Kod [Odaberi] Expand

CREATE ALGORITHM = UNDEFINED DEFINER =  `sanitarac`@`%` SQL SECURITY DEFINER VIEW  `jos_jf_languages` AS SELECT  `l`.`lang_id` AS  `lang_id` ,  `l`.`lang_code` AS  `lang_code` ,  `l`.`title` AS  `title` ,  `l`.`title_native` AS  `title_native` ,  `l`.`sef` AS  `sef` , `l`.`description` AS  `description` ,  `l`.`published` AS  `published` ,  `l`.`image` AS  `image` ,  `lext`.`image_ext` AS  `image_ext` ,  `lext`.`fallback_code` AS  `fallback_code` ,  `lext`.`params` AS  `params` ,  `lext`.`ordering` AS  `ordering`
FROM (

`jos_languages`  `l`
LEFT JOIN  `jos_jf_languages_ext`  `lext` ON ( (
`l`.`lang_id` =  `lext`.`lang_id`
) )
)
ORDER BY  `lext`.`ordering` ;

MySQL said:

#1227 - Access denied; you need the SUPER privilege for this operation

[Guardian]

Probaj backup database (.sql) otvoriti sa nekim text editorom (DW) pa izmjeni username od databaze iz "sanitarac" u "root".

Možda, ne znam. Pojedini mysql serveru si mi nekad zahtijevali da to mora odgovarati, iako uglavnom ne.

[Sanitarac]

Probaj backup database (.sql) otvoriti sa nekim text editorom (DW) pa izmjeni username od databaze iz "sanitarac" u "root".

Možda, ne znam. Pojedini mysql serveru si mi nekad zahtijevali da to mora odgovarati, iako uglavnom ne.

Sorry ali ovo ne funkcionira... šteta... ima netko neku ideju?

Kod [Odaberi] Expand

CREATE ALGORITHM = UNDEFINED DEFINER =  `root`@`%` SQL SECURITY DEFINER VIEW  `jos_jf_languages` AS SELECT  `l`.`lang_id` AS  `lang_id` ,  `l`.`lang_code` AS  `lang_code` ,  `l`.`title` AS  `title` ,  `l`.`title_native` AS  `title_native` ,  `l`.`sef` AS  `sef` , `l`.`description` AS  `description` ,  `l`.`published` AS  `published` ,  `l`.`image` AS  `image` ,  `lext`.`image_ext` AS  `image_ext` ,  `lext`.`fallback_code` AS  `fallback_code` ,  `lext`.`params` AS  `params` ,  `lext`.`ordering` AS  `ordering`
FROM (

`jos_languages`  `l`
LEFT JOIN  `jos_jf_languages_ext`  `lext` ON ( (
`l`.`lang_id` =  `lext`.`lang_id`
) )
)
ORDER BY  `lext`.`ordering` ;

MySQL said:

#1227 - Access denied; you need the SUPER privilege for this operation

[Guardian]

Probaj napraviti export databaze u nekoj starijoj mysql verziji. Imaš tamo opcije za kompatibilnost.

Znaš već da stare tabele moraš izbrisati, prije ponavljanja Import.

Ili odi u phpmyadmin pa napravi istog root usera kao na localhost. I daj mu baš sve ovlasti. Nekad se tamo lako zeznuti pa u onim opcijama ne dati sve ovlasti.

------------------

Imaš i ovo:

Steps:
1. Edit your exported SQL file and search for word ALGORITHM or simply move to the end of file as more chances of these queries are at the end of file.
2. In those CREATE queries set DEFINER username with your cPanel username. E.g. DEFINER='root'@'localhost' will become DEFINER='cpanel_username'@'localhost'
3. Save and upload to import in your cPanel's PhpMyAdmin

[Guardian]

This has nothing to do with the technology you use to backup nor restore your database. This is a uc_view design issue related to MySQL specification.

When the MySQL View (not the drupal) is created, it inherits the rights of the MySQL user who created the view, for instance developper@localhost. If you dump the database and restore it on another MySQL server where developper@localhost does not exist or has not the MySQL rights to access you tables, you will get the problem. Typically, if you backup on your development server and restore on the operational server.

The problem is due to the way the MySQL View is created, in uc_views code. MySQL allows, basically, two options :
create the view with the rights of the user who creates it (SQL SECURITY DEFINER), the default option
create the view with the rights of who uses it (SQL SECURITY INVOKER)

I suppose uc_views uses the first option, explicitly or not (it's the default). It's a design choice, not a bug. I wouldn't say it's the best choice (see further) but it seems to be the developer's one. If it really is a design choice then there is a lack of documentation. The warning about "uc_view using mysql views", in the README.txt file, if far from being enough. But what should be added ? At least a warning about this backup-restore issue. It's not easy to get into details since the way to overcome the problem depends on the mysql interface you use and the rights you have on the server. If we accept this design choice, it's more a MySQL than a uc_views issue : "how to change SQL SECURITY from DEFINER to INVOKER of some (or all) views of a database."

Anyway, I would suggest to modify uc_views code so that the views are created with "SQL SECURITY INVOKER" setting. This is what most MySQL basic users want. The users who really want to execute the views requests with the rights of a specific user, different from the invoker, know what they do and most probably know how to deal with views properties on their system. There might be a compatibility issue due to the "late" introduction of this option in MySQL. But since it's been introduced in version 5.0.16 (released on 21 Nov 2005), this seems acceptable...

Meanwhile, how to solve the problem ? The best is to refer to MySQL lists and forum (like this post). On a Linux system, here is a quick and dirty way to solve the problem :
1. backup the database through a mysql dump (Virtualmin server backup default)
2. remove the lines which contain the SQL SECURITY settings
3. restore the database on you target system.

Step 2 can be accomplished using sed :
$ sed '/^\/\*\!50013/d' backup.sql > backup_without_50013.sql

[Sanitarac]

Hej guardian, hvala na info...
Ja sam ipak išao onom shemom, oko kera pa na mala vrata....  ;D

Ono što sam napravio je bilo sljedeće:
- na server prebacio web
- instalirao i izbrisao bazu
- injektirao bazu bez tabele jos_jf_languages
- sve radi ok, al odabir jezika ima greške
- deinstalirao komponentu Joomfish
- instalirao komponentu Joomfish

Sve radi normalno, i ima ta tabela u bazi.... Joomfish samo deinstalira skriptu ali ne i bazu...  :P