delete client huge inconsistency, what to do!?

Hi there, long time (10+ years) user of ispconfig which I love! i even bought the billing manual, even though i have never used the billing module! My hosting company [world's oldest green web host!] has been treading water since my MS diagnosis... Just getting ready for a comeback after having verified all this time that ISPconfig works without being too complicated.. i know the database structure pretty well by now too. But now there's a problem I can't explain, and I don't know what to do next, without making everything worse. The versions are a bit older, which I will list at the bottom, but....

I was doing some clean-up, deleting clients that are disused. First set the 'locked' and 'cancelled' flags from the UI so the ones to drop would be easy to spot. Then I went to delete one, and it showed me a page of resources it was about to delete. Luckily I noticed the client identifier was wrong -- it identified a different client name, while all of the other resources looked correct. I tried it again with a different one, and the same thing happened. Further analysis revealed that it seemed to be choosing clients with client numbers two whole numbers higher than the one chosen for deletion; trying to delete client 11 showed client 13's name, and if the client two numbers higher had already been deleted, it showed that name anyway! ok, i thought, lets try something that should be safe and show me what happens if i continue: i found two clients marked cancelled that were 2 numbers apart. logic told me that EITHER it was somehow only DISPLAYING the wrong name but would delete the right client anyway, OR it would delete the wrong client but all of the resources of that one would remain, leaving the name of the intended client in the client list. I was aghast when the action removed BOTH client's names from the list, and there I stopped! I have had experiences with the db (dbispconfig) before and found it's structure to be refreshingly straightforward, so this is an upsetting show-stopper just when I am preparing for an influx of new clients.

So, I got myself connected to the database in a workbench like app, so I could easily see what's wrong. But no luck: in table "client", although sys_userid and sys_groupid are usually one off, but not two! then i looked at web_domain and sys_user, and all of the references are fine. so it seems i have no logical path to determine why any of this can be happening.

That creates a lot of issues for my multi-server installation. First is the issue, can or should i add any new anything? then, if I upgrade things, even after backup snapshot, will it change stuff on other servers so that I can't roll one back? We're live, so I can't just roll back, especially the mail server, since emails would disappear! I can't even trust in the migrator to a new installed ispconfig, because which client's assets is it going to move and then delete?? this is quickly become my own personal hell, with lots of time spent getting no answers. please, can someone help me make sense of this, and find a path out of this confusion?? THANK YOU!

ISPConfig Version: 3.2.7p1
"mysqld Ver 10.1.48-MariaDB-0+deb9u2 for debian-linux-gnu on x86_64 (Debian 9.13)" so it also doesn't have current php: "PHP 7.4.13 (cli) (built: Nov 30 2020 20:40:50) ( NTS ) Copyright (c) The PHP Group Zend Engine v3.4.0, Copyright (c) Zend Technologies with the ionCube PHP Loader + ionCube24 v10.4.5, Copyright (c) 2002-2020, by ionCube Ltd. with Zend OPcache v7.4.13, Copyright (c), by Zend Technologies"

 

Just stop any service and then upgrade the systems. When other mailservers can't connect to you, they will hold the mail in queue and try to deliver the mail at a later stage.

 

Thanks, but that doesn't really help, maybe you didn't read it all..? I am quite well-versed in what happens if a mailserver is down for a short time; it wouldnt be the first time, lol!

Talking about the mail server (a separate VM), I am saying, if it DOES receive mail but I have to roll back changes from an upgrade, those emails would disappear.

I am talking about updates I don't know about being applied to ANY of the other servers, particularly while the control panel is totally confused about which client owns what. That is what MUST be solved before I can fearlessly run update or resync jobs. At the very least, I need to understand what is causing this behavior, otherwise I can't trust that any next step will not make things worse!

I am NOT a newbie... This is a serious situation, unlikely to be solved with any short answer that starts with "Just..." because it's a deep problem, much deeper than I expected before I examined the database and found no inconsistency or error!! I feel safe doing literally nothing, including adding any new client, domain, or service, for fear the system will think the wrong client owns it.

Thanks for your reply of course, but please, take in the entire context first if possible.. I THINK i described it well enough, and nobody ever accuses me of giving too LITTLE information, lol! cheers...

 

Can you check if this returns anything?

Code:

SELECT su.userid, su.username, su.client_id, sg.groupid, sg.client_id
FROM sys_user su
JOIN sys_group sg ON su.default_group = sg.groupid
WHERE su.client_id != sg.client_id;

 

Thanks for the more serious approach
it gives: Empty set

I appreciate checking using SQL.. but like I said, I am looking at the numbers in the mysql browsing GUI (like mysql workbench) and I just don't see anything where numbers don't match. That would have been the "A HA!!!" moment I so desperately need!

 

Well so the only Issue that you have is that when deleting a client the wrong name gets displayed?

This is the relevant part of the code that puts together the name:

PHP:

            $this->dataRecord = $app->tform->getDataRecord($this->id);
            $client_id = $app->functions->intval($this->dataRecord['client_id']);
            $client_group = $app->db->queryOneRecord("SELECT groupid FROM sys_group WHERE client_id = ?", $client_id);
            $table_list = array();
            $client_group_id = $app->functions->intval($client_group['groupid']);
            if($client_group_id > 1) {

                $client = $app->db->queryOneRecord("SELECT CONCAT(IF(client.company_name != '', CONCAT(client.company_name, ' :: '), ''), IF(client.contact_firstname != '', CONCAT(client.contact_firstname, ' '), ''), client.contact_name, ' (', client.username, IF(client.customer_no != '', CONCAT(', ', client.customer_no), ''), ')') as contactname FROM sys_group, client WHERE sys_group.client_id = client.client_id and sys_group.groupid = ?", $client_group_id);

                $app->tpl->setVar('contactname', $client['contactname']);


As far as I understand that just does the following:

• Retrieves the client record based on the internal object ID
• Fetches the corresponding group ID for that client
• Uses the group ID to retrieve a nicely formatted contact display name -> Company :: Test Company (TComp, 1234)
• Sets the string (contactname) into the template, so the confirmation page shows the right person/company

Maybe there is a misunderstanding here because some fields are missing/empty and the displayed name is similar to another client?

 

ok... hopefully i cover everything in this one!

hey, thanks for answering, but... no, no misunderstanding. no similar names. always two different clients, always two client_id's apart. as i wrote, i first tested whether this was just a problem in displaying the name. in case something else was happening, made sure both the intended client and the incorrect client (whose name i now expected to see, offset by two, along with the intended client's assets) were both clients i wanted to delete. this way, i could live with the results if either of them got deleted.

sure enough, when trying to delete client 11 and it displayed name of 13 plus resources of 11, i confirmed that at least that part was predictable, if horrifying.

so, this time, i continued with the deletion. i made "either" bold above, because the result was BOTH CLIENT 11 AND 13 DISAPPEARED FROM THE CLIENT TABLE. geez, imagine if i hadn't been that careful in building my test!? luckily, i'm 51, life-long tech, running live hosting servers for over 20 years, so i tend to be properly careful not to disrupt experiences of users (especially with possible events, nearly untraceable, like the disappearance of an email that came in during a moment of a server being online that then gets rolled back after a failed test). regardless, since it didnt follow any sort of logic in a system where its not even possible to select several clients for deletion at the same time, it was clearly time to gently panic.

i have little doubt that this is an edge case involving some kind of perfect data storm, but i assure you, it is really happening as described.

I'm not sure how i can say this more clearly.... do you follow now?? THIS is why i stopped, THIS is why i am upset because I can't form a hypothesis from this data to tell me why it could be happening, how it might be fixed, how it might be prevented in the future, how far the mismatching goes, whether it is safe or a good idea to add new clients (or any new resources whatsoever), really. And, this is why I came running to this forum for help, like a small child running to its mother after being stung by bees it hadn't even imagined the existence of yet! oh, the pain and cruelty of the world, right? Honestly and candidly, i only think i've cried slightly less than such a child since the discovery of this on Friday, yet another workday including alcohol before it's end time, lol. i feel literally paralyzed.

Even worse: After examining the DB (and running your select, which basically showed the same thing), i can't conjure any logic that results in this, because i don't see any mismatched whatever_id's -- plus, heavy searching in bugs [both open and closed] yielded nothing even close to this problem. And with that, comes heavy doubt that an upgrade will have a (positive) effect, let alone a resync of dubious action, as what exactly that does doesn't seem to be described anywhere. it seems super unwise to try either to possibly land myself in bigger trouble with possible client data loss, and/or a more messed up database.

COULD there be unlisted bugs in 3.2.7p1 that have since been silently fixed? i THINK my next moves are things like: reboot the whole hypervisor; clone the cp server away from the rest of the cluster; try doing upgrades and resync in that vacuum.. but besides being a time-consuming pain, its the last flailing gasps of someone who still has no idea whats going on. even assuming i can make it stop, only knowing the how and why would give me procedure and assurances on it not happening again next week.

thanks in advance for any further ideas, ideally which acknowledge all the aspects of this case which i hope i have now clearly laid out!

 

.....damn! well, i have restarted the hypervisor while doing the closest thing to prayer that i do, lol. sadly, zero change.

i am watching this forum like a hawk in the hopes that someone..anyone can help identify whats going on. meanwhile i guess i will clone the VM and try upgrading it to see if that makes a difference. then i will try the resync.

i was just hoping for some answers, you know? without them... how does this not happen again, and to anyone whose situation, by chance, fits our edge case situation? this isn't some momentary problem never to be seen again! so i hope you all will help me identify wtf is going on, so we can enter a proper bug report with hopes it's resolved. cheers....

 

thank you till i will clone the server so as not to interrupt anything else, try an upgrade, pray again, and see what happens.
cheers!

 

hey folks, im back with some less-than-encouraging news....
i cloned the server away from its slaves to ensure nothing horrible can happen, and then i ran the update script. it seemed to go okay, all the way to "restarting services". then it said, "PHP Warning: Packets out of order. Expected 1 received 0. Packet size=30 in /tmp/update_runner.sh.88w3bK96JR/install/lib/mysqllib.php on line 209", but then said "Update finished."
When I go look at the server statuses under monitoring, it shows the others at 3.2.7p1 and the cp at 3.3.0p2, so, it worked....?
whether or not it did, the problem persists exactly as before. loosin my mind ova here...;/

the only next step i can think of is to upgrade a step at a time, deb 9 to 10 to 11 to 12, which will allow for more modern versions of, well, just about everything, and then see what happens. should THAT not work, i will try the stepwise upgrade first, and then the cp upgrade. and if THAT doesn't work, i will be at a loss.

please let me know if you have any other ideas about what could be wrong, ways to discover it, or ways to recover it!
THANK YOU again...

 

thanks, i doubt debian update will help much either. but besides being pretty good at db analysis (which ive already done without finding anything that even might be the problem), im pretty sure i dont have the budget for that...
okay, i guess back to "the work," alone :/