DatabaseTestCase and Master-2-Master Replication

Discussion of testing theory and practice, including methodologies (such as TDD, BDD, DDD, Agile, XP) and software - anything to do with testing goes here. (Formerly "The Testing Side of Development")

Moderator: General Moderators

Post Reply
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

DatabaseTestCase and Master-2-Master Replication

Post by VladSun »

It's me again :)

Today I've encountered another issue wit DB test cases. I just prepared my DB servers for master-to-master replication (cross master-slave config) and all of my DB tests failed. The reason is obvious - I changed the autoincrement_increment setting to 2 (it was 1), because I have 2 DB nodes and I need each of them to have a separate autoincrement values set.

I looked at the PHPUnit database test case class and the manual, but couldn't find anything helpful about it.
I am already thinking about some hackish workarounds but I still don't like them.

Anyone solved this?
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: DatabaseTestCase and Master-2-Master Replication

Post by requinix »

Your test cases failed because... they tried to predict the next auto_increment value?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: DatabaseTestCase and Master-2-Master Replication

Post by VladSun »

Well, I use XML DataSets for both initializing the DB for the current test and for comparing the actual (DB DataSet) result against the expected (XML Dataset).
In some cases the autoincement PK id is the only way to identify a record - e.g. N-to-M relationsships.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: DatabaseTestCase and Master-2-Master Replication

Post by requinix »

Right, but the only way the +2 thing should break your code is if it tries to guess what the auto_increment value was. No code should ever do that. If you need to set up relationships then insert the record in the new place and grab the ID with mysql_insert_id() (or whatever).

The alternative to doing it right would be modifying all the code so that it guesses $num+2 instead of $num+1. Which will break if you ever rearrange databases again.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: DatabaseTestCase and Master-2-Master Replication

Post by VladSun »

Well, it was the obvious workaround. I am trying not to use this approach because it will introduce low level code and hi level code mixture.
Also, as I said - I am using "DB snapshots" in XML format - no simple way to dynamically create it and I don't want to do that.
A typical test case would be:

Code: Select all

class UserOrmTest extends DatabaseTestCase {

  /**
   * @var UserOrm
   */
  protected $object;

  protected function setUp() {
    parent::setUp();
    $this->object = new UserOrm();
  }
  
  /**
   * @return PHPUnit_Extensions_Database_DataSet_IDataSet
   */
  public function getDataSet() {
    return $this->createXMLDataSet(dirname(__FILE__) . '/init.user.data.xml');
  }

  public function testSave() {
    ...
    $this->object->save();
    
    $expected = $this->createXMLDataSet(dirname(__FILE__) . '/after.save.data.xml');
    $actual = $this->getConnection()->createDataSet(array('user', 'role'));
    $this->assertDataSetsEqual($expected, $actual);
  }
}
the XML files look like these:

Code: Select all

<?xml version="1.0" ?>
<dataset>
  <table name="user">
    <column>id</column>
    <column>username</column>
    <column>password</column>
    <column>role_id</column>
    <row>
      <value>1</value>
      <value>user1</value>
      <value>password1</value>
      <value>1</value>
    </row>
    <row>
      <value>2</value>
      <value>user2</value>
      <value>password2</value>
      <value>2</value>
    </row>
  </table>
  <table name="role">
    <column>id</column>
    <column>name</column>
    <row>
      <value>1</value>
      <value>admin</value>
    </row>
    <row>
      <value>2</value>
      <value>operator</value>
    </row>
  </table>
</dataset>
It's clean and clear what the test is doing and I want to keep it this way.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: DatabaseTestCase and Master-2-Master Replication

Post by requinix »

There is no middle ground here. Not that I can see. Either you fix the test so that it doesn't need to know the ID, or you fix the XML so it has the right ones. Saying you don't want to change it is great and all but you keep talking about how it isn't working anymore.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: DatabaseTestCase and Master-2-Master Replication

Post by VladSun »

So ... the only solution is to have "n+1 autoincrement" based tests (testing & staging environment) and don't care about "production"environment tests. Sounds a kind of reasonable, though there might be some naive code trying to guess last_insert_id based on n + 1 logic ...
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: DatabaseTestCase and Master-2-Master Replication

Post by requinix »

Where did I say "only run the tests in testing/staging and not in production"? I can't find it. Perhaps you could reread everything I've said and point it out to me.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: DatabaseTestCase and Master-2-Master Replication

Post by VladSun »

It is my conclusion ...
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Benjamin
Site Administrator
Posts: 6906
Joined: Sun May 19, 2002 10:24 pm

Re: DatabaseTestCase and Master-2-Master Replication

Post by Benjamin »

This is a community of friends, so let's stay nice please.

Vladsun, I never run tests such as this. Can you explain to me, in dumbed down terms, why the tests need to be ran in such fashion and also why a test would need to confirm the auto_increment id?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: DatabaseTestCase and Master-2-Master Replication

Post by VladSun »

Needed for ORM/DBAL/SQL class tests - no DB mocks allowed.
The AI values need to be predicted for M-to-N, 1:1 and N:1 relationships.
There are 10 types of people in this world, those who understand binary and those who don't
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: DatabaseTestCase and Master-2-Master Replication

Post by Eric! »

Color me stupid but that didn't help me much. It doesn't help I've never done this before. Why do you care about the auto increment numbers?

If you have to have different numbers then you'll need some ORM to remap them right? So doesn't that mean your XML needs to be dynamic? Is this what they call an impedance mismatch?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: DatabaseTestCase and Master-2-Master Replication

Post by Weirdan »

VladSun wrote:The AI values need to be predicted for M-to-N, 1:1 and N:1 relationships.
In fact they don't need to be predicted as surrogate keys are just technicalities. If you take a step back and think about it in a more general way, for any relation you really only care about proper entities being linked, not about ids that facilitate that linking. For DB tests it means that you're better off asserting results of joins where you omit AI ids altogether.

Consider this case:

Code: Select all

insert into companies (name) values
("ACME"),
("Google");
insert into employees (name, ssn, company_id) values
("John Doe", "123456", 1), -- company_id is taken from companies sequence automatically
("Jane Doe", "654321", 2); -- ditto
Here, instead of asserting contents of both tables you could make your assertion work on the result of

Code: Select all

select employees.ssn, companies.name
from employees inner join companies on employees.company_id=companies.id
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: DatabaseTestCase and Master-2-Master Replication

Post by VladSun »

In general you are right :)
But not in my case - I have some DB related code which performs only WRITE operations. The READ operations are performed by 3rd party software (daemon) which doesn't use even a single code of my codebase. I.e. the DB is the mediator. That's why I need to ensure the DB snapshot is just what I expect it to be.

Also, I should have mentioned that it is a legacy code that needs a lot of refactoring and it's a little bit scary even to change simple things there.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: DatabaseTestCase and Master-2-Master Replication

Post by Weirdan »

What kind of queries the daemon makes? If the database is just a mediator, all you need to worry about is that the daemon gets what it needs - and thus you'd probably want to run assertions on daemon query results.
Post Reply