Please do NOT post requests for help here. Use the Help forum for that.
  • Page:
  • 1

TOPIC:

innodb to myisam 3 months 1 week ago #1

  • doctorsubtilis
  • doctorsubtilis's Avatar Topic Author
  • Offline
  • New Member
  • New Member
  • Posts: 11
I need to convert tables from inndb engine to myisam one, so that I could be able to share my webtrees db in several localhost (on different PC).
But I get an error when I try to convert:
#1217 - Cannot delete or update a parent row: a foreign key constraint fails
No way to fix this with:
SET FOREIGN_KEY_CHECKS=OFF;
or
SET FOREIGN_KEY_CHECKS=0;
What should I do?

Please Log in or Create an account to join the conversation.

Last edit: by doctorsubtilis.

innodb to myisam 3 months 1 week ago #2

  • doctorsubtilis
  • doctorsubtilis's Avatar Topic Author
  • Offline
  • New Member
  • New Member
  • Posts: 11
1) solved, a) exporting, b) replacing tables engine with kate and then b) importing the database.

EDIT
2) I was wrong, not solved. Indeed conversion to myisam is only temporary.

EDIT
3) this maybe is a definitive solution: not only the step 1), but also change in config files of webtrees the default engine. So far it works!

Please Log in or Create an account to join the conversation.

Last edit: by doctorsubtilis.

innodb to myisam 3 months 1 week ago #3

  • doctorsubtilis
  • doctorsubtilis's Avatar Topic Author
  • Offline
  • New Member
  • New Member
  • Posts: 11

Part of the message is hidden for the guests. Please log in or register to see it.

Please Log in or Create an account to join the conversation.

Last edit: by doctorsubtilis. Reason: delete this last post

innodb to myisam 3 months 1 week ago #4

  • fisharebest
  • fisharebest's Avatar
  • Away
  • Administrator
  • Administrator
  • Posts: 14491
MyISAM does not support foreign key constraints.
MyISAM does not support transactions.

webtrees 2.0 requires these features.

So, one of the database upgrade scripts converts existing MyISAM tables into InnoDB.


I do not understand why you need MyISAM.
Greg Roach - This email address is being protected from spambots. You need JavaScript enabled to view it. - fisharebest.webtrees.net

Please Log in or Create an account to join the conversation.

innodb to myisam 3 months 1 week ago #5

  • doctorsubtilis
  • doctorsubtilis's Avatar Topic Author
  • Offline
  • New Member
  • New Member
  • Posts: 11
Thanks! I said: "so that I could be able to share my webtrees db in several localhost (on different PC)".
What do you mean by "transactions"?

Please Log in or Create an account to join the conversation.

innodb to myisam 3 months 1 week ago #6

  • fisharebest
  • fisharebest's Avatar
  • Away
  • Administrator
  • Administrator
  • Posts: 14491
> I said: "so that I could be able to share my webtrees db in several localhost (on different PC)".

I do not understand this.

Why does InnoDB prevent this? How does MyISAM allow this?
How are you "sharing"?

> What do you mean by "transactions"?

Transactions mean that you can make several updates, which either all complete - or none complete.

So, if you update two records, then you can never be left in a situation where one completes and one fails.
Greg Roach - This email address is being protected from spambots. You need JavaScript enabled to view it. - fisharebest.webtrees.net

Please Log in or Create an account to join the conversation.

innodb to myisam 3 months 1 week ago #7

  • fisharebest
  • fisharebest's Avatar
  • Away
  • Administrator
  • Administrator
  • Posts: 14491
PS - if you did convert your tables to MyISAM, then this would have deleted the foreign keys.

Converting back to InnoDB would NOT re-create them. They are permanently lost.
Greg Roach - This email address is being protected from spambots. You need JavaScript enabled to view it. - fisharebest.webtrees.net

Please Log in or Create an account to join the conversation.

innodb to myisam 3 months 1 week ago #8

  • doctorsubtilis
  • doctorsubtilis's Avatar Topic Author
  • Offline
  • New Member
  • New Member
  • Posts: 11
I see. But what it matters? So far I can use webtrees...

Please Log in or Create an account to join the conversation.

Do you need a web hosting solution for your webtrees site?
If you prefer a host that specialises in webtrees, the following page lists some suppliers able to provide one for you: 

innodb to myisam 3 months 1 week ago #9

  • fisharebest
  • fisharebest's Avatar
  • Away
  • Administrator
  • Administrator
  • Posts: 14491
> But what it matters

Some features will not work.
Your database will keep growing because old data is not deleted.
If there is a server/network error, then you may get corrupt data.
etc.
Greg Roach - This email address is being protected from spambots. You need JavaScript enabled to view it. - fisharebest.webtrees.net

Please Log in or Create an account to join the conversation.

innodb to myisam 3 months 1 week ago #10

  • doctorsubtilis
  • doctorsubtilis's Avatar Topic Author
  • Offline
  • New Member
  • New Member
  • Posts: 11
I see. But with Phpgedvierw I used to have MyISAM tables and they remained always light and correct.
My problem with InnoDB is that, usign lampp (that is in /opt) I have innodb (common/main) files in /opt/var/mysql, while my (db) data are in another partition, symlinked to /opt/var/mysql. And innodb can't be symlinked. Therefore share data between different PC with innodb seems to me too complicated. At least so far.

Please Log in or Create an account to join the conversation.

Last edit: by doctorsubtilis.

innodb to myisam 3 months 1 week ago #11

  • norwegian_sardines
  • norwegian_sardines's Avatar
  • Away
  • Elite Member
  • Elite Member
  • Posts: 1989
webtrees was completely rewritten to take advantage of modern features of MySQL/MariaDB, PHP and other 3rd party functions.
Ken

Please Log in or Create an account to join the conversation.

innodb to myisam 3 months 1 week ago #12

  • Sir Peter
  • Sir Peter's Avatar
  • Offline
  • Junior Member
  • Junior Member
  • Posts: 153

doctorsubtilis wrote: ... able to share my webtrees db in several localhost (on different PC).


What exactly are you trying to achieve? One does NOT share a database by sharing database files. Hundreds of people all from different locations can access webtrees at the same time because webtrees uses an API to access the database.

About database transactions: Think of money that needs to be transferred from one bank account to the other and for simplicity let's assume both accounts are in the same bank. That would require 2 database statements, the "removal" of the money from one account AND the adding of that exact same amount to the second account. Before executing those 2 statements a BEGIN TRANSACTION is required and after both statements have been executed a COMMIT TRANSACTION would be required. Only then the database can ensure that both changes would be applied consistenly. Otherwise the money could get lost between the two accounts in case an interruption (application failure, server failure, network failure, etc.) occurs.
Peter

Please Log in or Create an account to join the conversation.

innodb to myisam 3 months 1 week ago #13

  • doctorsubtilis
  • doctorsubtilis's Avatar Topic Author
  • Offline
  • New Member
  • New Member
  • Posts: 11
My goal is share my genealogical db not with other people on the internet, but between my PC (in localhost not in remote).

Please Log in or Create an account to join the conversation.

innodb to myisam 3 months 6 days ago #14

  • norwegian_sardines
  • norwegian_sardines's Avatar
  • Away
  • Elite Member
  • Elite Member
  • Posts: 1989
If you are on a local network, you should be able to type the IP address in your browser to the computer hosting the database.
Ken

Please Log in or Create an account to join the conversation.

Last edit: by norwegian_sardines.

innodb to myisam 3 months 6 days ago #15

doctorsubtilis wrote: My goal is share my genealogical db not with other people on the internet, but between my PC (in localhost not in remote).

That's fine. I assume all these PC's are on the same local network, right? Only one of them needs to host the database and run webtrees -- the rest can still connect to it through a web browser.

The normal use case would be all PC's on a home network behind a NAT router distributing local addresses to your computers. The trivial way to do this would be to note the raw IP address of the PC that hosts webtrees (say 192.168.0.10) and type that into the browser URL. As long as you do NOT port-map the webtrees host through the NAT gateway, nobody on the outside can access it.

Please Log in or Create an account to join the conversation.

innodb to myisam 3 months 6 days ago #16

  • doctorsubtilis
  • doctorsubtilis's Avatar Topic Author
  • Offline
  • New Member
  • New Member
  • Posts: 11

drblam wrote:

doctorsubtilis wrote: My goal is share my genealogical db not with other people on the internet, but between my PC (in localhost not in remote).

That's fine. I assume all these PC's are on the same local network, right?

No! I have different PC in different places, not sharing the same (local) network.

If any of you can explain me an easy way to share innodb tables between PC not sharing the same local network, I will thank you. So far I didn't find one.

Please Log in or Create an account to join the conversation.

innodb to myisam 3 months 6 days ago #17

  • bertkoor
  • bertkoor's Avatar
  • Offline
  • Platinum Member
  • Platinum Member
  • Greetings from Utrecht, Holland
  • Posts: 2268
I think you need to put the database on a server. Not just share the files, but run it as a proper DB server. That is how it's designed, so it's easier to use it as designed.
stamboom.BertKoor.nl runs on webtrees v1.7.13

Please Log in or Create an account to join the conversation.

Last edit: by bertkoor.

innodb to myisam 3 months 6 days ago #18

  • fisharebest
  • fisharebest's Avatar
  • Away
  • Administrator
  • Administrator
  • Posts: 14491
How does creating a symlink enable you to share the database between the computers?
Greg Roach - This email address is being protected from spambots. You need JavaScript enabled to view it. - fisharebest.webtrees.net

Please Log in or Create an account to join the conversation.

  • Page:
  • 1
Powered by Kunena Forum