Question Inconsistency in Database?

[Calvin Aachen]

Hello guys!

Currently im accessing my plesk db with ssh. I want a nice CSV where I got everything I need.
The past 3 days I tried to understand your database scheme, which worked. So far the important things for me are the tables: domains, Limits.

I know I can see an overview in the webinterface, there is even an extension which provides the feature of exporting the overview into csv. But the csv does not have the things I need.

My problem now:

The table 'Limits' is inconsistent. For every domain(either it is a child to a subscription or not) should have set limits in this table. This is not the case. When I join 'Limits' into 'domains', for many domains I simply get NULL entries.

select domains.id,domains.name,
REPLACE(sum(DomainsTraffic.http_in)/1024/1024/1024/19/2,'.',','), REPLACE(sum(DomainsTraffic.http_out)/1024/1024/1024,'.',','), REPLACE(sum(DomainsTraffic.ftp_in)/1024/1024/1024,'.',','),
REPLACE(sum(DomainsTraffic.ftp_out)/1024/1024/1024,'.',','), REPLACE(sum(DomainsTraffic.smtp_in)/1024/1024/1024,'.',','),
REPLACE(sum(DomainsTraffic.smtp_out)/1024/1024/1024,'.',','), REPLACE(sum(DomainsTraffic.pop3_imap_in)/1024/1024/1024,'.',','), REPLACE(sum(DomainsTraffic.pop3_imap_out)/1024/1024/1024,'.',','),
REPLACE((sum(DomainsTraffic.http_in)+sum(DomainsTraffic.http_out)+sum(DomainsTraffic.ftp_in)+sum(DomainsTraffic.ftp_out)+sum(DomainsTraffic.smtp_in)+sum(DomainsTraffic.smtp_out)+sum(DomainsTraffic.pop3_imap_in)+sum(DomainsTraffic.pop3_imap_out))/1024/1024/1024,'.',','),
limitsjoin.valuesjoin
from domains
LEFT JOIN DomainsTraffic ON domains.id = DomainsTraffic.dom_id
LEFT JOIN (SELECT Limits.id as idjoin, Limits.value as valuesjoin FROM Limits where Limits.limit_name = 'max_traffic' GROUP BY idjoin) as limitsjoin ON domains.id = idjoin
where domains.id=dom_id AND date>='2019.01.01' AND date<='2019.01.07' GROUP BY dom_id

This query results in this:

(I cut out the left part. But you should be able to know what part of the result this is)

What is bugging me extremly, in the webinterface I got all the subscriptions with the correct limits shown.

Any help is appreciated!!!

 

[Calvin Aachen]

Sorry I can not edit my post anymore so I have to reply, sorry.

The images contained the result. I screenshoted only the field 'limitsjoin.valuesjoin'. This field has multiple NULL values. Whole domains are missing in the limits table.

 

[Calvin Aachen]

No one can help??

 

[IgorG]

Try to ask the assistance of Plesk Support Team.
Note, that in fact, we do not welcome direct manipulations with the database and believe that the work in the Plesk interface is sufficient for all tasks.

 

[Calvin Aachen]

Working with the Interface is not possible. Im writing code that gathers data across several different platforms dynamically. So I need to prepare my statements within that software.

Thanks for the help!