Integrity constraint violations & sidebar.html.twig
Replies: 12   Views: 2417  Subscribers: 0

Posted by testuser · 20-07-2012 - 19:21

What a brilliant set of bundles!

I'm having problems with integrating the Bundles so working from first principles decided to use the Sandbox instead.  However I've hit 2 challenges:

1) Integrity constraint violations with any associated entities that are persisted from the owning side of the relationship.  For example, when registering, an error is thrown on the attempt to create the profile.  Deleting the foreign key relationships from the associated tables seems to alleviate the symptoms, but I know isn't the solution.  
2) The Sidebar.html.twig is reported to be missing across a number of bundles (for example when you go to the profile section).

I've obviously missed something, so any pointers would be great.

Thanks very much!­

Posted by reece · 20-07-2012 - 20:23

Edited by reece · 20-07-2012 - 20:54
Ok, i will deal with the second issue first because i know what is causing this.

Some time ago, when i added the sidebar folder across the bundles, i accidentally committed it with a lowercase folder name. Many servers operate with a case sensitive file system, naturally i changed the folder name to have an uppercase 's'. Essentially it started out as 'sidebar' and then noticed the error and changed it to 'Sidebar'. This is basically because i decided good naming conventions for folders should start with first character being capitalised. 

Unfortunately, git by default is not case sensitive and i had to change this. In some instances the folder remained and git refused to notice the changes. The only way to solve the issue was to move the folder out, do a commit, put the folder back and commit again. 

I had hoped i had got all the instances of the repo that was causing that issue unless you are using an earlier version of the bundles and not the latest. I will take another look to see if i missed any and update the repos.

I am working on another major update that is due to be pushed out in next day or 2 and its just a matter of getting the unit tests done before pushing out. If your still in early dev, and are not worried about the unit tests then i can push this out early if you like, and it should resolve the issues of the Sidebar at least. You will need to use master though until the unit tests are done and it has a new version tag.

­ Integrity Constraint Violation­

As for the integrity constraint violation, are you updating from an older version of CCDN bundles or is this a fresh install? If it is an update then you need to update your DB as some time ago a major update changed some of the database column names for better SQL naming conventions.

If it is however a fresh install, then i will need to know a bit more.

Can you please state wether you are using master or the latest tagged version.

I need to know:
1) Which tagged version are you using.
2) Which version of symfony are you using?
3) Please paste the error you are getting here.­

Posted by reece · 20-07-2012 - 21:40

Ok, i have pushed a large number of backlogged updates that i was working on that weren't pushed for some time.

The unit tests still have not been done yet, however i worked over every bundle to make sure the sidebar issue is solved, i found it still lingering in a few bundles and i removed the directory, committed it and re-added it and committed it again. This seems to have solved the issue, so you shouldn't be having this issue anymore.

So feel free to try again on master and hopefully it will fix the issue.

I will add a new version tag soon once i get the unit tests done.

Please try it again and let me know how it goes. And then we can see about solving this integrity constraint violation issue you are having.­

Posted by testuser · 20-07-2012 - 22:06

Thanks al lot Reece.

I had originally used the sandbox repository on github which uses Symfony 2.0.11 and v1.1 of all the CCDN bundles.

I then tried upgrading to the latest release of Symfony (2.0.16), again with the same issues.

I'm developing on OSX using MAMP 

I'll go ahead and upgrade to master for the CCDN bundles with Symfony 2.0.16 and give you feedback on progress

If you need any help testing then please let me know.

Thanks again.­

Posted by reece · 20-07-2012 - 22:38

I recommend using Symfony 2.0.11 as that is what i have been developing on myself. I hope to have the unit tests done very soon and then i can move up through the versions testing them from 2.0.11 to the current release 2.0.16. 

Of course, sometimes you get minor BC breaks between these versions and that is why i have stayed on the 2.0.11 until the unit tests were done.

I am hoping that not too much has changed that will cause a BC break through the next versions of Symfony as i have more work to be done on the BBCodeBundle, which needs another rewrite as the latest version of PHP has issue with the use of references. 

Though most servers out there are likely many many versions behind the latest PHP rollout as they like to often stay on a certain version they know works before upgrading and breaking their clients projects.

BC Breaks on php versions are quite common and the php API does seem to change a lot. I can rewrite the BB code engine in a way that will work on the older and latest versions though.

Chances are you won't have issue with it. I am also using MAMP on Mac OS X, and recommend using the older versions of MAMP like 2.0 and avoiding the update to 2.1.1 incase PHP causes issue for you.

If you follow the bundles on the github channel you will see when i update the BBCodeBundle to resolve that issue.­

Posted by reece · 21-07-2012 - 04:47

Edited by reece · 21-07-2012 - 05:14
Just wondering when you say update, i hope you never used the --update option on the vendors install because that can mess up your deps.lock file. Always use --install though i am sure you are i just wanted to eliminate that possibility. If you have used --update though please delete your deps.lock and put a fresh one in and recheck your deps.

Also, i notice you said that the table name was 'tmf_forum', there is no such table with that name in the CCDNBundles. So it seems you have renamed the table. I would not recommend that you do that, it is the likely cause for the integrity constraint violation.

My advice is do not edit the bundles to begin with, just test them as is and see if everything works. Then you can make your changes later. But i see no need to rename the tables. So try the defaults first and if that does not work, then maybe we need to take a look at your schema because it might not be getting setup properly.

So just test it with unmodified table names.­

Posted by testuser · 21-07-2012 - 15:00

Edited by testuser · 21-07-2012 - 20:00
tmf_forum is the name of the db ( i haven't edited any of the table names).  To be sure, I've created a db called "code consortium" as given in parameters.ini.dist

Here are the last steps I followed:
1) Clean install from Sandbox on db name codeconsortium only using bin/vendors install --reinstall.
2) created tables from doctrine:update:schema --dump-sql
3) Updated deps so that master for the bundles are used (error thrown as bb_lexar not found, but I commented the 3 lines out for now) and ran bin/vendors install --reinstall.
4) register new user and given error - SQLSTATE­[23000]­: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`codeconsortium`.`cc_user`, CONSTRAINT `FK_957A6479CCFA12B8` FOREIGN KEY (`profile_id`) REFERENCES `cc_user_profile` (`id`))

The user is created, but the profile isn't.  

Database collation type is UTF8 default collation.  

On first glance, it seems that I get the error when an associated entity is persisted i.e. on creation of a user , a profile is created and persisted.  On your dev environment. are the tables InnoDB?­

Posted by reece · 22-07-2012 - 00:28

Edited by reece · 22-07-2012 - 07:13
Oh ok, yeah the database name would not matter, i thought it was a table name. So thats fine.

If your having an issue with BBCodeBundle then you can turn it off in the app/config/config.yml. It should be under:

        enable: false

Hopefully that should prevent the error your getting, though im not sure why your getting that error as i don't. I am only aware of the issue with php references in the bb code bundle atm (only for the last update to php which imposes restrictions on the usage of references) though it seems this is not an issue for you.

This issue with the profile bundle is bewildering though. I am using the same project powering this website and yet i don't get the same issue. I am wondering what else it could be.

You say you are using MAMP, so that must mean your using MySQL and not any other db right (like Oracle etc)?

Few more things we could try. 

1) Drop the database (not just the tables but the whole db), 
2) Manually delete the cache using the rm utility or in your file browser, do not use the SF2 CLI for this as it tends to miss stuff. 3) Manually delete your vendors directory.

Then when you try setting up again, use the --install option instead of --reinstall. I am thinking maybe something is missing there. 

Also, please check the output status when setting up your database, it could be that one or more constraints are failing during setup.

You can disable the validation of constraints during setup by starting your queries with:

set foreign_key_checks=0;

And then at the end of the table creation queries running:

set foreign_key_checks=1;

Hopefully you don't get any errors during table creation. If you do you need to work it out and manually add any failed constraint you find.­

Posted by testuser · 22-07-2012 - 09:52

It appears that there's something wrong with my MAMP setup.  MAMP is running locally with MySQL version 5.5.9 and PHP 5.3.6. 

I've installed on my server and all seems fine and the FK problems have disappeared (MySQL version is 5.1.63-0+squeeze1 and PHP is 5.3.14).

Can you confirm which versions of MySQL and PHP you have running please?


Posted by reece · 22-07-2012 - 11:16

For Dev, I am running:

MAMP 2.1.1
PHP 5.4.4
MySQL 5.5.25

The 5.4.4 is according to MAMP, but due to a downgrade (to resolve the issue with references in newer versions of php i get, php -v reports PHP 5.3.10) Though i believe this to be erroneous. I believe that when i replaced the MAMP app (2.1.1) with the older one, it kept the newer version of php which was causing me issues (still is, which is why in dev i have disabled the bbcode engine until i can re-write it).

You could try using MAMP 2.0 but i think its best to run the installer, i think my mistake was dropping the old in place of the newer one which was causing me issues.

So try 2.0, otherwise switch to 2.1.1 and disable the bbcode engine. (A rewrite of bb code engine will be pushed in the next few days to resolve this issue [bc break caused by changes in php api/etc]).

BTW when you do get it working, will you please write back what was causing the issue, and how you managed to solve it, could come in handy if myself or anyone else encounter the same/similar issues in the future.­

Posted by testuser · 22-07-2012 - 17:08

Ok. Have found the issue

MySQL 5.5.9 (and higher it seems reading some of the comments) on OSX has problems with case upper cases which is apparent when working with foreign keys on InnoDB.

As the tables have names such as CC_****, it throws an error when you try to persist an entity and an associated entity as MySQL doesn't recognise the associated table name.

The fix is to add this to your my.cnf under ­[mysqld]­


You can find out more about the option above here, which stores all table names in lowercase (will not affect existing schema although I haven't tried doing that, as I recreated all tables) and makes all comparisons in lowercase.

Phew!  I think we can put this issue to rest, but it's something to watch out for for anyone working with MySQL on OSX.­

Posted by reece · 23-07-2012 - 12:06

Nice catch. I don't think Symfony2 offers any features though for configuring doctrine to use lowercase table names. Maybe you need to file an issue on the Doctrine github so that they can have an option to force/translate table names to lowercase.

I hope it won't be long now before i get all the unit tests pushed out.­