soreana commented on issue #7139: URL: https://github.com/apache/cloudstack/issues/7139#issuecomment-1426058567
@weizhouapache Although the https://github.com/apache/cloudstack/pull/7145 PR fixed the issue for new VMs, we have to update CloudStack's database manually and add an entry in `user_vm_details` for old VMs. I thought it would be nice to share the scrips here just in case someone has the same issue. :-) 1. Get the affected VMs with the following sql query. Save it to a file called `missing.txt` 2. Then use the python code to generate appropriate insert SQLs. `python3 parser.py missing` 3. The python programm will generate the `missing.sql` file. 4. Update the database using the `missing.sql` ```sql MariaDB [cloud]> SELECT distinct(view.id) as vm_id, view.uuid as vm_uuid, view.volume_id, vol.uuid as volume_uuid, \ vol.volume_type , details.value as rootdisksize, CAST(vol.size/1024/1024/1024 AS SIGNED INTEGER) as expected \ FROM cloud.user_vm_view AS view LEFT JOIN cloud.user_vm_details AS details ON view.id=details.vm_id AND \ details.name='rootdisksize' LEFT JOIN cloud.volumes AS vol ON vol.id=view.volume_id WHERE view.state != 'Destroyed' \ AND (CAST(vol.size/1024/1024/1024 AS SIGNED INTEGER) != details.value OR details.value IS NULL) \ and vol.volume_type = 'ROOT' and details.value is NULL ORDER BY vm_id ; +-------+--------------------------------------+-----------+--------------------------------------+-------------+--------------+----------+ | vm_id | vm_uuid | volume_id | volume_uuid | volume_type | rootdisksize | expected | +-------+--------------------------------------+-----------+--------------------------------------+-------------+--------------+----------+ | 1 | aaaaaaaa-bbbb-cccc-dddd-fffffffffff0 | 1 | aaaaaaaa-bbbb-cccc-dddd-fffffffffff0 | ROOT | NULL | 12 | | 2 | aaaaaaaa-bbbb-cccc-dddd-fffffffffff1 | 2 | aaaaaaaa-bbbb-cccc-dddd-fffffffffff1 | ROOT | NULL | 15 | | 3 | aaaaaaaa-bbbb-cccc-dddd-fffffffffff2 | 3 | aaaaaaaa-bbbb-cccc-dddd-fffffffffff2 | ROOT | NULL | 25 | +-------+--------------------------------------+-----------+--------------------------------------+-------------+--------------+----------+ 3 rows in set (0.005 sec) ``` ```python import sys input_file_name = '{}.txt'.format( sys.argv[1] ) output_file_name = '{}.sql'.format( sys.argv[1] ) with open( input_file_name , "r") as f_in: with open( output_file_name , "w") as f_out: for li in f_in.readlines(): if '-+-' not in li and "vm_id" not in li and "SELECT" not in li and 'rows' not in li: print(li, end = '') tmp = li.split('|') print(tmp) out_line = 'insert into user_vm_details (vm_id, name, value, display) values ( {}, \'rootdisksize\', {}, 1);\n'.format( tmp[1], tmp[-2] ) print(out_line, end = '') if 'NULL' in tmp[-3]: f_out.write(out_line) ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
