picky plesk problems

I recently needed to move some domains from an older reseller account which my hosts manage to my VPS which I'm responsible for (hosted by the same firm).

As I do not have root access to the reseller account I was not able to use the plesk migration manager myself so the lovely people at my hosting company (34sp) moved the domains for me.

All seemed to go well and all the domains were up and running as they should (databases, email, web etc). It was only a couple of weeks later when I needed to change one of the domains that I realise there was a little problem.

It seems that the plesk migration manager had created a new reseller on my VPS (which makes sense) but it failed to add a "plan" for the reseller. This meant that when it tried to add the domains under that reseller it was unable to do so as there was no plan for them to be added to. So, not wanting to be defeated the migration manager added the domains with a reseller ID for a none existent reseller (id 0).

I also found that when I tried to add a plan to the reseller that I got an error in plesk (I forget what it was now but it was red and angry looking)

So, good news, they all transferred, bad news I can't do anything with them.

The solution....
(normal; it worked for me, your mileage may vary, backup first, if it goes wrong it isn't my fault rules apply).

Logon to your vps and run mysql as a user that has access to everything (root or admin probably). I'm not giving details of how to do this, if you don't know go away and work that out before even thinking about this)

Rather than using select * statements I have included the fields that I found handy to help work things out. Some or all of the fields may not be required.

Select the psa database
use psa

Check the clients table for resellers
select count(*) from clients where type='reseller';
select id, parent_id, vendor_id, type, cname, pname, account_id, status from clients where type='reseller';

Check the subscriptions table for clients
select count(*) from Subscriptions where object_type='client';
select * from Subscriptions where object_type='client';

There should be the same number of record returned by both. In my instance the new reseller was not in the subscriptions table.

Generate a new uuid for the record
select uuid();

Insert a new subscription record for the new reseller. Use the ID from the clients table and the uuid you generated
INSERT INTO Subscriptions (object_id,object_type,locked,synchronized,custom,uuid) VALUES (<CLIENT ID>,'client','false','true','false','<THE UUID')

By this point you should be able to give the reseller a plan but will still have missing domains. For me clicking on the reseller showed no plans, customers or domains in the general section but 7 domains used in the resources section.

Find one of your missing domains
select id, name, displayName, status, vendor_id, cl_id from domains where name="wffsystems.co.uk";

Take the cl_id from the above query and use it in the next one. In my instance the "missing" domains were owned by a cl_id of zero so this gave me a shorter list. Fingers crossed this will list all the ones that are missing.

select id, name, displayName, status, vendor_id, cl_id from domains where cl_id=<YOUR CL_ID>;

Last but not least, update each domain to have the cl_id of the reseller you want them to belong to. I did this one by one but with a bit of thought I'm sure I could have done it as a one liner.
update domains set cl_id=<YOUR RESELLER ID> where id=<THE DOMAIN ID>;

I'm not totally convinced that this has totally fixed things as my new reseller still has 7 domains listed as used even though there are none actualy in that reseller account but at least I can get to all my domains now!

Hope this helps someone.

***UPDATE***

In the domains table there is a cl_id and a vendor_id. No idea what the difference is but changing vendor_id to be the same as everything else seems to have resolved the issue.