Announcement

Announcement Module
Collapse
No announcement yet.

Percona, InnoDB, Galera and deleting without a PK

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Percona, InnoDB, Galera and deleting without a PK

    Hi,

    I apologize as I have looked around for an answer but have yet to find one. Please feel free to point me to a site that already has the answer if you already know of its location!

    We (my coworkers and I) have recently installed Percona with the DB cluster. We have read into limitations and the different components that make up this solution, and we have a concern with #2 of the following link - www.codership.com/wiki/doku.php?id=limitations

    DELETE operation is unsupported on tables without primary key. Also rows in tables without primary key may appear in different order on different nodes. As a result SELECT...LIMIT... may return slightly different sets. Don't use tables without primary key. It is always possible to add an AUTO_INCREMENT column to a table without breaking your application.

    In spite of this statement, We've created an InnoDB table in the cluster (two nodes) that doesn't have a primary key and, yet, the delete operation *does* work. That is, I can delete a single record and the change is applied to the other node in the cluster. Terry's going test this further with some tables in the CDS database and I don't know how far he's gotten with that. Nevertheless, we need to know Percona's position on this. Why did the delete work for me? Why is my experience counter-factual to the stated limitation?

    Thanks,
    Jason Cameron

  • #2
    hi,
    perhaps you have this parameter in my.cnf
    ----------------------
    wsrep_certify_nonPK=ON
    When this variable is enabled, primary keys will be generated automatically for the rows that the rows don't have them. Using tables without primary keys is not recommended.
    ----------------------

    In my case with this parameter set to on, when I created a table without a primary key, I see no primary key autogenerated when i do a show create table 'mytable';

    Sebastien

    Comment

    Working...
    X