Serendipity Forums

Hi,

after relocating the blog to another server my media database is damaged. ok, I've done an export / import of the SQL table, but I've forgotten to move the real images from the uploads dir.

Now is the state of trouble an empty (all records are deleted) table serendipity_images and the directory uploads is emty too.

I want to do fresh uploads and fresh integration in entries.

Uploading images via s9y works, but in the media database couldn't be found anything and I cant select anything while editing. Error messages see below.

How can I repair this awful state. Should we do a fresh initial CREATE for the table? (I haven't any access via PHPMyAdmin or other tools, the only accessible way routes via my administrator.)

Thx, regards, Juergen.




This is the message while "browsing" the media database:



SELECT i.id, '' AS orderkey, i.name, i.extension, i.mime, i.size, i.dimensions_width, i.dimensions_height, i.date, i.thumbnail_name, i.authorid, i.path, i.hotlink, i.realname,
a.realname AS authorname
FROM serendipity_images AS i
LEFT OUTER JOIN serendipity_authors AS a
ON i.authorid = a.authorid
LEFT JOIN serendipity_authorgroups AS acl_a
ON acl_a.authorid = 1
LEFT JOIN serendipity_access AS acl_acc
ON ( acl_acc.artifact_mode = 'read'
AND acl_acc.artifact_type = 'directory'
AND acl_acc.artifact_index = i.path
)

WHERE 1=1 AND (
i.path IS NULL OR
acl_acc.groupid IS NULL
OR ( acl_acc.groupid = acl_a.groupid OR acl_acc.groupid = 0)
OR ( acl_acc.artifact_id IS NULL

)
)
GROUP BY i.id
ORDER BY i.date DESC LIMIT 0, 8

/ Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='




And this is the message in the popup if I want to insert an image while editing:


SELECT i.id, '' AS orderkey, i.name, i.extension, i.mime, i.size, i.dimensions_width, i.dimensions_height, i.date, i.thumbnail_name, i.authorid, i.path, i.hotlink, i.realname,
a.realname AS authorname
FROM serendipity_images AS i
LEFT OUTER JOIN serendipity_authors AS a
ON i.authorid = a.authorid
LEFT JOIN serendipity_authorgroups AS acl_a
ON acl_a.authorid = 1
LEFT JOIN serendipity_access AS acl_acc
ON ( acl_acc.artifact_mode = 'read'
AND acl_acc.artifact_type = 'directory'
AND acl_acc.artifact_index = i.path
)

WHERE 1=1 AND (
i.path IS NULL OR
acl_acc.groupid IS NULL
OR ( acl_acc.groupid = acl_a.groupid OR acl_acc.groupid = 0)
OR ( acl_acc.artifact_id IS NULL

)
)
GROUP BY i.id
ORDER BY i.date DESC LIMIT 0, 9

/ Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='


Both of them are pointing to code problem...

Hi!

It seems your SQL dump did not contain the Collation information for MySQL tables ("Character sets") and now the table contain different table sets.

You will need to use phpMyAdmin to check every column of every table. If your s9y is running with UTF-8 charset set every collation to "utf8_unicode_ci". If you run native charset, set every collation to "latin1_bin".

You can change that by going to the table structure screen in phpMyAdmin, click on the EDIT button right next to each column and then change "collation" ther.e

Without phpMyAdmin you can'T fix this, so you might need to turn to your system admin.

Best regards,
Garvin

garvinhicking wrote:Without phpMyAdmin you can't fix this, so you might need to turn to your system admin.

Yes, s9y is using UTF-8. Ok, I've access via PHPMyAdmin to the old / source system. I'll do the dump again, it's easier for the admin than edit all table properties.

For an complete restore of media database I need the tables serendipity_images and serendipity_mediaproperties AND the upload directory with the same subdirectory structure, right?

best regards, Juergen

Hi!

For an complete restore of media database I need the tables serendipity_images and serendipity_mediaproperties AND the upload directory with the same subdirectory structure, right?

Exactly. You might also need the serendipity_access, _authorgroups, _groupconfig and _groups though if you also want to preserve existing access privileges to the MDB.

Regards,
Garvin

f*ck, with installed PHPMyAdmin 2.8.0.2 I don't see an option to include the charset info in an export dump!
And there isn't a chance to change the collation while editing. I hope at the other system is a newer version installed.

Juergen

Hi!

It might depend on your local MySQL version. MySQL prior to 4.1 did not know about collations. For MySQL after 4.1, default collations might be applied to some tables.

HTH,
Garvin