1
flymirco
Custom module installation problem
  • 2009/7/14 2:11

  • flymirco

  • Just popping in

  • Posts: 42

  • Since: 2006/9/20


Hi all,

I am using XOOPS 2.3.3 on a local XAMPP web server (version 1.7).
I am developing a custom module but I got an error when I try to install it.
The error is _MD_AM_SQL_NOT_VALID:
Quote:
is not a valid SQL!

I find this error coming from file modules\system\admin\modulesadmin\modulesadmin.php at line 197

Starting the debug I can see that the table are created and, after module error dropped. Why I have this problem? The sql is correct and the file is in UTF-8 charset. Can you help me to the right point?

Where I can find a module development tutorial for XOOPS 2.3.3 and the changes between it and the old 2.0.18 release?

Thanks in advance.

flymirco

2
iHackCode
Re: Custom module installation problem

is there a specific line in the sql file that is causing the issue.? (it should show you the query that is causing the issue)
CBB / LatestNews / Publisher / XM-Spotlight

(ノ◕ヮ◕)ノ*:・゚✧

3
flymirco
Re: Custom module installation problem
  • 2009/7/14 2:32

  • flymirco

  • Just popping in

  • Posts: 42

  • Since: 2006/9/20


Hi Bandit-X,

no sql lines gives errors. I tried all the table creation and there are no errors. Only one doubt, I set the Engine to INNODB for all the tables in the script.

4
iHackCode
Re: Custom module installation problem

i want to make sure, in the sqlFile the sql commands are without the prefix, correct?
CBB / LatestNews / Publisher / XM-Spotlight

(ノ◕ヮ◕)ノ*:・゚✧

5
flymirco
Re: Custom module installation problem
  • 2009/7/14 7:47

  • flymirco

  • Just popping in

  • Posts: 42

  • Since: 2006/9/20


Hi Bandit-X,

you are right, no prefix in sql file. That's seems to be the problem. I don't know why, but it can't add the prefix or some errors come when he tried to add.

flymirco

6
culex
Re: Custom module installation problem
  • 2009/7/14 9:15

  • culex

  • Module Developer

  • Posts: 711

  • Since: 2004/9/23


I was just reading up on Xampp server and it seemed innodb is only enabled automaticlly after 1.7.1, check my.cnf to see if innodb settings are unmarked.
Programming is like sex:
One mistake and you have to support it for the rest of your life.

7
flymirco
Re: Custom module installation problem
  • 2009/7/14 9:27

  • flymirco

  • Just popping in

  • Posts: 42

  • Since: 2006/9/20


I found it the problem: it was the comment at the end of all CREATE TABLE statement that causes the module installation fails.

It also doesn't support a CREATE INDEX statement. To do that you have to put the index creation in the CREATE TABLE statement as a UNIQUE KEY <index name> (<field list>).

flymirco

8
flymirco
Re: Custom module installation problem
  • 2009/7/14 15:16

  • flymirco

  • Just popping in

  • Posts: 42

  • Since: 2006/9/20


Hi culex,

you have seen what has come after I solved the problem.
Now I can't install my module due to foreign key creation problem. This is the error I got:
Quote:
CREATE TABLE svy_domande ( id INT NOT NULL AUTO_INCREMENT , riga INT NULL , attributi_id INT NOT NULL , valore INT NULL , alternativa SMALLINT (4) NULL , PRIMARY KEY (id), INDEX fk_domande_attributi1 (attributi_id), CONSTRAINT fk_attr1 FOREIGN KEY (attributi_id) REFERENCES svy_attributi(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE = INNODB
Error number: 1005
Error message: Can't create table 'unipolls.xup_svy_domande' (errno: 150)

I setted up correctly XAMPP by changing my.cnf file. Now I think that problem arise from tables prefix during foreign key creation.

Is there a way to tell to the query processor to add prefix also on REFERENCE <table name> (<field>)?

flymirco

9
culex
Re: Custom module installation problem
  • 2009/7/14 16:49

  • culex

  • Module Developer

  • Posts: 711

  • Since: 2004/9/23


Phew I wish this was about MyISAM wich is more my alley. Just pointed out where to look for information about your problem. Now this is said I of course read a little more about hte INNODB engine and from the user comment on this page (the example with error 150)

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

I think, and correct me please if I am wrong, that you're missing an index svy_attributi(id) before FOREIGN KEY (attributi_id) ?
Programming is like sex:
One mistake and you have to support it for the rest of your life.

10
ghia
Re: Custom module installation problem
  • 2009/7/14 18:29

  • ghia

  • Community Support Member

  • Posts: 4953

  • Since: 2008/7/3 1


Quote:
It also doesn't support a CREATE INDEX statement.
No, but ALTER TABLE is supported.
Could you not rephrase your SQL statements to use this?
eg
ALTER TABLE mytable ADD INDEX myindex (col1, col2);
ALTER TABLE mytable ADD CONSTRAINT myconstraint PRIMARY INDEX (col1, col2);
etc.
Quote:
Is there a way to tell to the query processor to add prefix also on REFERENCE <table name> (<field>)?

Try by adding this in function prefixQuery of /class/database/sqlutility.php
$matches[0] = preg_replace($pattern$replace$query);
// start hack ghia
$pattern3 "/
(.*REFERENCES)(s)+([`]?)([^`s]+)\3(s)+/siU"
;
if (
preg_match($pattern3$query))
{
$matches[0] = preg_replace($pattern3$replace$matches[0]);
}
// end hack ghia
return $matches;

Limitation: it works only for 1 table reference per statement, so if you have multiple foreign keys, you have to add them one by one by separate alter table statements.
Quote:
I think, and correct me please if I am wrong, that you're missing an index svy_attributi(id) before FOREIGN KEY (attributi_id) ?

It seems good to me. I think the only problem he has, is that the statement executes without prefixing the referenced table and then fails for an unknown table.

Login

Who's Online

206 user(s) are online (137 user(s) are browsing Support Forums)


Members: 0


Guests: 206


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Mar 31
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits