Deal with empty sysinfo_uuid values

[emanuel-b-q]

Current Behavior

When starting daemon, the data imported from VCenter containes the same value for different systems, but constraint prevents insert into DB.
When running: /usr/bin/icingacli vspheredb daemon run --trace --debug
I get the error message:

Task hostSystems failed: Storing host_system[q"_h
۳Ɵ] failed: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '00000000-0000-0000-0000-000000000000' for key 'sysinfo_uuid', query was: INSERT INTO host_system (uuid, vcenter_uuid, host_name, product_api_version, product_full_name, bios_version, bios_release_date, sysinfo_vendor, sysinfo_model, sysinfo_uuid, service_tag, hardware_cpu_model, hardware_cpu_mhz, hardware_cpu_packages, hardware_cpu_cores, hardware_cpu_threads, hardware_memory_size_mb, hardware_num_hba, hardware_num_nic, runtime_power_state) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) {array (
'uuid' => 'q"_h
۳Ɵ',
'vcenter_uuid' => 'yi6G7%S',
'host_name' => 'HOST.LOCAL.DOMAIN',
'product_api_version' => '6.0',
'product_full_name' => 'VMware ESXi 6.0.0 build-10719132',
'bios_version' => 'I27',
'bios_release_date' => '2015-08-16 00:00:00',
'sysinfo_vendor' => 'HP',
'sysinfo_model' => 'ProLiant BL460c G7',
'sysinfo_uuid' => '00000000-0000-0000-0000-000000000000',
'service_tag' => NULL,
'hardware_cpu_model' => 'Intel(R) Xeon(R) CPU X5660 @ 2.80GHz',
'hardware_cpu_mhz' => 2800,
'hardware_cpu_packages' => 2,
'hardware_cpu_cores' => 12,
'hardware_cpu_threads' => 24,
'hardware_memory_size_mb' => 196597.0,
'hardware_num_hba' => 1,
'hardware_num_nic' => 7,
'runtime_power_state' => 'poweredOn',
)}
Loading existing VirtualMachine from DB
Got 0 existing VirtualMachine
SOAPClient: got 13.43 MiB response in 3056.77ms
Got 5287 VirtualMachine from VCenter
Ready to store VirtualMachine
Task virtualMachines failed: First parameter must either be an object or the name of an existing class

Possible Solution

Remove constraint?

Steps to Reproduce (for bugs)

Even after droping DB, after restart/reinstall/recreate error persist.

Your Environment

• VMware vCenter®/ESXi™-Version: 6.5U2
• ESXi Hosts: 6.0U3
• VSphere Client : 6.5.0
• Version/GIT-Hash of this module: tried both: v1.0.3 and master
• Icinga Web 2 version: 2.6.2
• Operating System and version: RHEL7.4
• Webserver, PHP versions: httpd-2.4.6-88.el7.x86_64; rh-php71-php-fpm-7.1.8

[emanuel-b-q]

OS-Updated to lastest patch level

Did the queries from issue #76 :

SELECT COUNT(*) AS cnt FROM object AS o INNER JOIN virtual_machine AS vc ON o.uuid = vc.uuid
-> 0

SELECT COUNT(*) AS cnt FROM object AS o
INNER JOIN virtual_machine AS vc ON o.uuid = vc.uuid
INNER JOIN vm_quick_stats AS vqs ON vqs.uuid = vc.uuid
LEFT JOIN host_system AS h ON vc.runtime_host_uuid = h.uuid;
-> 0

SELECT COUNT(*) FROM object WHERE object_type = 'VirtualMachine';
-> 5287

SELECT COUNT(*) FROM virtual_machine;

-> 0

SELECT object_type, COUNT(*) AS cnt FROM object GROUP BY object_type;

-> 12 Entries

[emanuel-b-q]

QUICK DIRTY solution to get the ESX-HOSTS into the DB was:

ALTER TABLE host_system drop index sysinfo_uuid;

But I suspect that wasn't a good solution.
Still the error remains:

Undefined property: stdClass::$config.hardware

[Thomas-Gelf]

Hi @emanuel-b-q,

having a unique System UUID per physical Host is a hard requirement, dropping this constraint would be a problem. Could you please check what permissions have been granted to your vCenter user? The missing hardware property seems to be our main issue her, I guess this then leads to 00000000-0000-0000-0000-000000000000 being the UUID for multiple hosts.

Cheers,
Thomas

[emanuel-b-q]

The user has read-all permissions
I got from the VCenter Admin the information that the G7 always had an issue with the UUID.
If the UUID could be read or not was always a miracle!

[emanuel-b-q]

There are two system where the sysinfo_uuid='00000000-0000-0000-0000-000000000000'

[Thomas-Gelf]

I got from the VCenter Admin the information that the G7 always had an issue with the UUID.
If the UUID could be read or not was always a miracle!

Honestly, that's not what I wanted to hear 🤣 I'll try to come up with a workaround for this, like generating a moref/vCenter-based fake UUID with a custom prefix. Fortunately we do not use this to identify the Host System.

You can keep going with the removed constraint unless this has been fixed, afterwards it should be re-enforced. After restarting the daemon, is the error complaining about :$config.hardware still there?

[emanuel-b-q]

Yes, you're right.
I got this error message and:

Ready to store VirtualMachine
First parameter must either be an object or the name of an existing class

Thanks

[emanuel-b-q]

And in icingaweb2 the tab with "Virtual Machines" stays empty:

[emanuel-b-q]

Feb 11 14:40:34 icinga2-test sudo: Admin1: USER=root COMMAND=/usr/bin/icingacli vspheredb daemon run --trace --debug PWD=/root
Feb 11 14:42:09 icinga2-test icingacli: First parameter must either be an object or the name of an existing class
Feb 11 14:42:38 icinga2-test icingacli: Undefined property: stdClass::$config.hardware

And in addition I get the error message :
Feb 11 14:42:52 icinga2-test icingacli: The operation is not allowed in the current state.

Message of the day:
Lucky is, who has a test system and its not the same as production!

[pcasis]

I've the exact same issue.

[Thomas-Gelf]

@emanuel-b-q: please try the current master and the DDL/schema modification in #95.

[ttb1913]

Perhaps the following additional info may be useful to some people seeing a similar issue:

I also had this issue however it was not with an empty sysinfo_uuid value, it was trying to insert '4c4c4544-004e-4610-8033-c8c04f465131' as the value of sysinfo_uuid. That would make me think that there is a duplicate sysinfo_uuid in the table, however the host_system table was EMPTY, as in 'SELECT * from host_system' returned 0 entries. I tried the migration in #95 which didn't work. Removing the UNIQUE constraint (the quick and dirty solution above) allowed the table to populate.

I have done this twice now by doing the following - removing the db resource, stopping the module & service, dropping the database, re-creating the db, creating a new resource, re-adding the vcenter server (basically completely re-configuring vspheredb from scratch)

HOWEVER, I was running into issue #75 which I added some information to. Once I resolved that issue in the manner I documented then completely rebuild the config (per my process above) with the constraint still in place in the DB then it completed successfully without a hitch.

[jwilliams-shadowsoft]

@Thomas-Gelf I had this problem on my small VMware lab system. I have some pretty old hosts and could only install up to vSphere 6.0, but the sysinfo_uuid was coming out all zeroes.

When I dropped the constraint on that column in the host_system table everything started working.

(vSphereDB v1.1.0)