weizhouapache commented on issue #7139:
URL: https://github.com/apache/cloudstack/issues/7139#issuecomment-1426662489
> @weizhouapache
>
> Although the #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)
> ```
thanks @soreana for sharing
would it be good do it via mysql procedure?
--
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]
