Bug #67437 for Dancer-Plugin-Database: Blobdata destroyed, not sure if it's a bug

Hi Michael, On 2011-04-13 19:33:10, ms@2scale.net wrote: Show quoted text
So, the data as shown in the log is fine, but the data stored in the actual database is truncated/corrupted? I'll need to give this more thought (I just got back from the pub, so now isn't a great time for troubleshooting), but a few thoughts: * The mediumblob field is able to hold this data, yes? * Do you have charset: UTF8 enabled in your Dancer app config? * Can you rock up a quick test script to insert the same data using DBI directly? If so, does that work normally on the machine where you're seeing the problem? Also, it occurs to me that, in MySQL's default settings, it has an annoying habit of munging data to fit the schema, rather than refusing to insert it - it may be worth enabling strict mode so that this doesn't happen, for instance, adding the following to your config.yml: plugin: Database: [...] on_connect_do: ["SET sql_mode='traditional'] It also occurs to me that it might perhaps make sense to change Dancer::Plugin::Database to either do that by default, or add a new option for it to automatically check for warnings from MySQL and report them. Cheers Dave P

Hi David, thank you for your answer so far. Show quoted text
Yes. Show quoted text
Yes, there is already data there in some other rows which were imported into mysql. Show quoted text
Yes i configured: # when the charset is set to UTF-8 Dancer will handle for you # all the magic of encoding and decoding. You should not care # about unicode within your app when this setting is set (recommended). charset: "UTF-8" and (as you told me in your mail) dbi_params: RaiseError: 1 AutoCommit: 1 on_connect_do: ["SET NAMES 'utf8'", "SET CHARACTER SET 'utf8'", "SET sql_mod e='traditional'" ] Show quoted text
Yes, this script: ... my $dbh = DBI->connect($db->{connect}, $db->{user}, $db->{pass}, {PrintError => 1}) or die "$DBI::errstr"; my $filename = "/home/tcsuite/iphone_4.jpg"; my $image = read_file($filename); print substr($image, 0, 20), "\n"; $dbh->do("UPDATE device SET image = ? WHERE id = ?", undef, ($image, 672)); $dbh->disconnect; Works ok. So the database and DBI and DBD::mysql basically should be able to handle this. In the application currently use this: warning "Imagedata " . substr($imagedata, 0, 20) . "..."; # database->quick_update("device", { id => params->{id} }, { # image => $imagedata, # image_mimetype => $upload->{headers}->{"Content-Type"}, # }); # update device set image = ?, image_mimetype = ? where id = ? my $sth = database->prepare($SQL{UPDATE_IMAGE}); $sth->execute($imagedata, $upload->{headers}->{"Content-Type"}, params->{id}); $sth->finish; As you can see, i changed it from quick_update to prepare/execute, but this didn't change anything. That warning line is printed correctly, so the imagedata should be intact at this point. I'm investigating this further now. Cheers, Michael -- 2scale GmbH, Karlstr. 88, 40210 Düsseldorf Amtsgericht: Düsseldorf HRB 50718 Geschäftsführer: Georg von Zezschwitz, Dirk Vleugels USt-IdNr.: DE 210936505

On 2011-04-14 08:27:12, ms@2scale.net wrote: Show quoted text
Ah, bingo :) That's what I was going to suggest, if you had charset: 'UTF8' enabled, to try turning it off and see if the problem goes away, as I suspected it was something along those lines. Glad to hear it's working now! Show quoted text
You're welcome!