List Creation tries to ALTER TABLE

[rsd]

Hello,

I am trying Fabrik again after a long time, so this could be obvious and I am not seeing it.
This is my Hello World.

Joomla 3.3.1, Fabrik 3.1

I have a table like this:

CREATE TABLE IF NOT EXISTS `pessoa` (​

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,​

`nome` varchar(255) NOT NULL,​

`dataNascimento` date DEFAULT NULL,​

PRIMARY KEY (`id`)​

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;​

I first created a new connection (different schema on the same server) for this db, user and pw.

Second, a new list:
- A name,
- Data -> Connection -> Select the created connection.
- Database Table -> pessoa

It can be saved fine.

When I come back to the list, the primary key is already chosen (id), which Fabrik detects fine.
Now, when I save the list (even if not changed) I get the error:

1025 Error on rename of './test/#sql-532_177' to './sbee/pessoa' (errno: 150) SQL=ALTER TABLE `test` CHANGE `id` `id` int(11) NOT NULL AUTO_INCREMENT

Basically it is taking out the 'unsigned' part.

The reason is that there are some foreign keys pointing to this PK.

So, the question is: Why Fabrik is trying to change the PK?
Is it sane to alter a table struture without notice?
Why does Fabrik was a SIGNed PK?

If this a bug or am I missing something?

thanks
-rsd

 

[rsd]

After studing a little more of the code I start to understand.
So, here goes a few suggestions:

1 - In the options Alter field types, add a suggest changes option. This would make the suggestions for better compatibility than blindly changing them.

2 - Fabrik is using INT(11) 'int(11)' as a default Primary Key. May I suggest to change this to 'int(10) unsigned'.
The perception of 11 being better than 10 is because of the sign character. In practice I don't think anyone uses negative key values. So the range is doubled.

-rsd

 

[troester]

Usually you will create a db table via Fabrik list and elements with Fabrik defaults. If you are linking a Fabrik list to an existing db table it's up to you to choose the elements and set the settings you need.
I don't think int(10) is anything special recommended.

gesendet mit Tapatalk

 

[rsd]

Signed vs unsigned

 

[Sophist]

Fabrik doesn't normally create the foreign key constraints in MySQL - it does that sort of stuff internally - so the change from unsigned to signed would normally succeed. And of course it would normally crate the autoincrement as (signed) INT(11) already.

 

[cheesegrits]

I think I agree on the UNSIGNED part, I don't see any reason for a PK to be signed. To be honest I thought that was something we'd fixed a couple of years ago, I remember messing around with that code. What I thought I'd done was make it SIGNED/UNSIGNED and INT size agnostic, so all it did when we set the PK was check the actual type, make sure it's an INT of some sort (INT, BIGINT, etc). I'll have a poke aorund in that code next time I have it open on the slab.

The INT(10) vs INT(11) thing is, afaik, irrelevent. The size arg is only used to define the display width of integers, in things like the mysql command line client, primarily for use with things like ZEROFILL, so 12345678 with INT(10) would render as 0012345678, INT(11) would render as 00012345678. It has no effect on how an INT is stored, or whether it is signed or unsigned, etc.

Also NOTE - using Innodb is very much "at your own risk" in Fabrik, as it's quite possible to wind up with FK constraint errors when using joined data, as Fabrik basically polices FK constraints itself, and may well do things in an order that Innodb's constraints object to, like deleting the parent row before the child rows. We reccomend using MyISAM.

I think there's also some issues with how we do full text searching which don't work very well in Innodb, but I may be wrong.

-- hugh

 

[rsd]

I agree with you.
The whole point was to make sure that Fabrik could be used as a joomla frontend to a existing database.
Then again, I had not seen the option to prevent db changes.
-rsd