Disk used error size

[Mikelandjelo]

during moving site to new hoster i lose many pic and now shows wrong disk used size ~40Gb
but really ~2Gb

How to fix?

 

[Rodolfo]

You will need to get rid of the affected image rows then you can update the stats.

 

[Mikelandjelo]

manually or automaticaly?
i deleted about 10000 pics..

 

[Rodolfo]

You will need to write a custom script for it. System keeps a record of disk usage based on the images table. If you deleted files on disk directly then you have 10000 records in the image table that won't show an image, you will need to take all of them, calculate the size and update your stats table.

 

[Mikelandjelo]

which row's table can delete safety?

 

[Rodolfo]

To put it simple:

1. Detect which rows have missing images and then delete those rows
2. Run this query to rebuild ALL stats:

TRUNCATE TABLE `chv_stats`;

INSERT INTO `chv_stats` (stat_id, stat_date_gmt, stat_type) VALUES ("1", NULL, "total") ON DUPLICATE KEY UPDATE stat_type=stat_type;

UPDATE `chv_stats` SET
stat_images = (SELECT IFNULL(COUNT(*),0) FROM `chv_images`),
stat_albums = (SELECT IFNULL(COUNT(*),0) FROM `chv_albums`),
stat_users = (SELECT IFNULL(COUNT(*),0) FROM `chv_users`),
stat_image_views = (SELECT IFNULL(SUM(image_views),0) FROM `chv_images`),
stat_disk_used = (SELECT IFNULL(SUM(image_size) + SUM(image_thumb_size) + SUM(image_medium_size),0) FROM `chv_images`)
WHERE stat_type = "total";

INSERT INTO `chv_stats` (stat_type, stat_date_gmt, stat_images, stat_image_views, stat_disk_used)
SELECT sb.stat_type, sb.stat_date_gmt, sb.stat_images, sb.stat_image_views, sb.stat_disk_used
FROM (SELECT "date" AS stat_type, DATE(image_date_gmt) AS stat_date_gmt, COUNT(*) AS stat_images, SUM(image_views) AS stat_image_views, SUM(image_size + image_thumb_size + image_medium_size) AS stat_disk_used FROM `chv_images` GROUP BY DATE(image_date_gmt)) AS sb
ON DUPLICATE KEY UPDATE stat_images = sb.stat_images;

INSERT INTO `chv_stats` (stat_type, stat_date_gmt, stat_users)
SELECT sb.stat_type, sb.stat_date_gmt, sb.stat_users
FROM (SELECT "date" AS stat_type, DATE(user_date_gmt) AS stat_date_gmt, COUNT(*) AS stat_users FROM `chv_users` GROUP BY DATE(user_date_gmt)) AS sb
ON DUPLICATE KEY UPDATE stat_users = sb.stat_users;

INSERT INTO `chv_stats` (stat_type, stat_date_gmt, stat_albums)
SELECT sb.stat_type, sb.stat_date_gmt, sb.stat_albums
FROM (SELECT "date" AS stat_type, DATE(album_date_gmt) AS stat_date_gmt, COUNT(*) AS stat_albums FROM `chv_albums` GROUP BY DATE(album_date_gmt)) AS sb
ON DUPLICATE KEY UPDATE stat_albums = sb.stat_albums;

UPDATE `chv_users` SET user_content_views = COALESCE((SELECT SUM(image_views) FROM `chv_images` WHERE image_user_id = user_id GROUP BY user_id), "0");

Please note that Chevereto support doesn't cover this, you have to do this on your own.

 

[Mikelandjelo]

if missing data in
' image_md5' (in chv_images table)
this means that the picture is physically removed?

 

[Rodolfo]

No, it doesn't. Md5 should be always there, any image uploaded by Chevereto stores the md5.

 

[Mikelandjelo]

some rows dont have md5 values

so without which values i need to delete rows?

 

[Rodolfo]

I don't know why you have missing md5, Chevereto has always stored that data.

Missing md5 or any data doesn't tell you of the image file exists or not because database and files are not connected. You will need to find a way to detect those files, like an script that iterates all your images and detect the missing ones based on your database rows.