logs archiveChat DB / Freenode / #mysql / 2015 / November / 15 / 1
dmarr
I am trying to figure out what format the data in my MEDIUMTEXT column is. its an old vBulletin forum attachment.. The insert looks like: INSERT INTO `attachment` VALUES (1, 5, 1008372298, '2annak1024x768.jpg', 'ÿØÿà\0JFIF\0X...
when I dump the cell to a file and run `file foo.jpg` it shows `data`
danblack
dmarr: keep in mind text fields have a charset. make sure you are in a binary charset when you extract it
or try to alter table it to a blob
dmarr
danblack: i was thinking the altar would be a good thing to try
im not sure how to change to binary charset
is that part of the sql query?
purefan
danblack: This doesnt make sense... somehow this variable is getting polluted, but I guess its better in the #bash channel... the var where I store the show slave status gets a list of the files in the folder
dmarr
also just extracting the data and writing it to a .jpg file doesn't show the image either, so maybe its encoded with something else
danblack
dmarr: either case it or set the session character set
s/case/cast/
dmarr
hexdump foo.jpg | head > 0000000 c3 bf c3 98 c3 bf c3 a0
danblack
purefan: yes, sounds like a bash problem
dmarr
danblack: the creation of the table had these options: ENGINE=MyISAM AUTO_INCREMENT=487 DEFAULT CHARSET=latin1 AUTO_INCREMENT=487
can you help me change the col type?
trying the 'select binary filedata from attachment where attachmentid=1' returned nothing
danblack
a) make a backup. b) alter table vBulletintable modify data blob;
dmarr
Data truncation: Data too long for column 'filedata' at row 1
Anthaas
Im here because I dont know where else to ask - I have an ERD and would like someone to critique it for me?
dmarr
danblack: http://stackoverflow.com/questions/33713993/how-do-i-extract-an-image-from-a-mediumtext-myisam-field
Hopefully the accepted answer isn't "don't store images in the database"
threnody
!t dmarr about images
ubiquity_bot
dmarr: http://mysqldump.azundris.com/archives/36-Serving-Images-From-A-Database.html and http://hashmysql.org/index.php?title=Storing_files_in_the_database
threnody
:-)
dmarr
hah threnody its not the same question though. theirs is "i want images in mysql" mine is "i want images from mysql"
but i do agree with their moral
i wonder is there some limitation of pulling the data back out like i'm trying to do
danblack
have you found the bit of code that pulled it out originally?
dmarr
It was a .sql file from an old web's backup cpanl
*cpanel
phpmyadmin v2.8.1 dump
looking at the file in vim though I see a bunch of ^R and such that might not have come through in the gist
danblack
could be phpmyadmin stuffing it up. use mysqldump on the machine if possible
dmarr
danblack: if i run SELECT BINARY and then save the cell data in DBVis, it does spit out a valid JPEG
vaxxon
Is there a simple way to write, on one line, something which tries one query and, if it fails to generate any results, runs a second query? I found IF, but I'm not sure this does what I want.
Naktibalda
vaxxon: no
danblack
dmarr: glad you got it.
vaxxon
Naktibalda: That explains a lot. Alright. Then I'll just have to make joins to the table until it can all be searched with a single query. Thanks.
danblack
vaxxon: in the very small set of cases where the queries are very close.
dmarr
danblack: sort of stuck trying to read it in php
danblack
that actually sounds like a better approach
reading php makes me want to vomit too
vaxxon: yes restructuring joins sounds like a better approach
vaxxon
danblack: In this case, I'm making changes to my personal mail server. Right now there's an 'alias' table, so you can send mail to some_alias@example.com and it will see I've got that mail going to me@example.com. But I'd also like it to send %.me@example.com to me@example.com if it can't find an alias.
K. Joining I go. Appreciated.
ipfspics-Didier
Hi there, so I have an image hosting website, and have two tables : one for every picture with a unique id and one with all the votes on the different pictures (one vote per row). I've created a SELECT statement that outputs all the hashes with votes on them, with the number of downvotes substracted to the number of upvotes. However, it won't work when I try and put it in a virtual table (CREATE VIEW) and I don't know why. Any
danblack
ipfspics-Didier: a view isn't a virtual table. dont' use views.
ipfspics-Didier
danblack: what should i use instead?
danblack
ipfspics-Didier: its probably a bug, however you are better of not using views.
a select statement like you have
ipfspics-Didier
but the statement by itself doesn't accomplish what i want
I'd like a view so that I could fetch the most popular pictures
danblack
ipfspics-Didier: write a select statement for each purpose. running selects on top of views has huge negative performance impacts. don't do it. (end)
DaddyEric_
jesus
lots of parts and joins
(Action) turns off join/part message
DaddyEric
there we go much better
guys when doing a where statement i am trying to simply display all of the values with the ending string as CUSTOM
threnody
where foo like '%custom';
DaddyEric
Yeah my IDE is kicking it out
Devart for MySQL Studio
threnody
DaddyEric: leading wildcards preclude the use of any inex on the column
index*
DaddyEric: run it in the mysql cli client
"kicking it out" doesn't have a meaning. is there an error message?
DaddyEric
no error it just doesnt pull any values
HAVING jos_virtuemart_order_items.order_item_sku = '%CUSTOM'
threnody
DaddyEric: perhaps your data isn't what you think it is
DaddyEric
CL-BYS-KHAKI-SZ-CUSTOM
threnody
DaddyEric: pastie.org your entire sql, not a segment
DaddyEric
i think that would be caught in that where statement
http://pastie.org/10557621
threnody
DaddyEric: that's not a WHERE, that's a HAving
DaddyEric
ok sorry, i removed the where statement on that
but even when i put that segment in the having, or the where no values get returned
mgriffin
select count(*) from jos_virtuemart_order_items where lower(order_item_sku) like '%CUSTOM';
select count(*) from jos_virtuemart_order_items where order_item_sku like '%CUSTOM';
(probably it was the = instead of like)
DaddyEric
yeah that doesnt like it either
yeah it was the like
instead of the =
thanks, i am having to re-learn mysql
threnody
21:32 < threnody> where foo like '%custom';
DaddyEric
can i do multiple joins such as REPLACE(Field_Name, '{' & '}', ''
Xgc
DaddyEric: That has nothing to do with joins. Your question just didn't make sense.
DaddyEric
ok, can i do multiple replaces for the charachters { } in one replace statement
bascially i have a record column entry that has this in it
Xgc
DaddyEric: REPLACE(REPLACE(...), ...)
DaddyEric
ok
so multiple nested replaces
now, what is the symbol for a carriage return replace
"78":
"1406":
"comment": "32"
}
},
"77":
"1407":
"comment": "32"
}
« prev 1 2 next »