SELECT * from diary where DATE_ADDdia_due_dateINTERVAL -dia_days DAY = CURDATE and dia_active=1 # even if dia_days=0
hi all!!!
i need to make a database server with HA, what kind of Tech recommend?
Im using sortables on floating divs
left
when they wrap and I try to sort the last item in the list
#mysql!
weird shit happens
oh
wrong tab
how fast should failover be and what's your budget?
nils_, yes!!!
"Would you like tea or coffee?" - "Yes!"
http://rafb.net/p/BYUDWZ18.html
hmmm … the output of GROUP_CONCAT() seems to be limited to 1024 bytes. I s that true?
"What colours are available?" - "Yes! Of course!"
what's wrong with that thing ?
could be
The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the maximum effective length of the return value is constrained by the value of max_allowed_packet.
nils_, I need to develop mysql AH. eg if my server crash, other server incoming very fast! I don't know maybe clustering? or if i buy 2 server hosting an NAS storage? what kind of tech recommend?
nils_,
on the low end: master-master replication with linux-ha
high end: mysql hosting cluster
http://rafb.net/p/BYUDWZ18.html why i can't optimise this GROUP BY ?
The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the maximum effective length of the return value is constrained by the value of max_allowed_packet.
my english very worse… mysql cluster i think so
Thank you. Maybe this is gonna be a task for PHP then 
try KEY(flag, id_gal)
wait, that also doesn't make sense
lol, I should look before I paste
nils_, cluster i think so.
nils_, i tryed
what's wrong ?
well you'll need at least 2 storage nodes and 2 sql host nodes, one managment node recommended.
ALL ?
nils_, humm, that's meaning if i have 2 server and 1 NAS connect over optical fiber?
no NAS needed.
heliostech, it has to scan the entire table to get the result so reading the indexes would slow it down
the problem with shared disk is that the disk is in an inconsistent state when one node dies
nils_, but it's recommended?
OK!
that won't happen with a dedicated filesystem per server, that's why I tend to use replication which has it's own problems.
and, to quote mysqlperformanceblog, MySQL Cluster is a whole other beast
got it
hummm
ALTER TABLE cat3 ADD INDEX (id_gal, flag) BTREE;
memory uses hash indexes by default I think
well my sql is wrong
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_max_seeks_for_key
CREATE INDEX `id_gal_flag_btree` ON `cat3` (`id_gal`, `flag`) USING BTREE;
nils_, actually, i have 20 pc with a program that's connects a database server with ip 192.168.0.254, so if my server crash (i don't know, memory fails, nic fails, etc) as soon as the other server incoming. Do you understand me?
yeah you can achieve that with linux-ha or vrrp
so that the fallback server takes over the static ip hosting of the primary
keeping the servers in sync is the complicated thing
nils_, so what Tech do you recommend me?
I use replication and linux-ha
nils_, the other server had similar conf? you know database structure etc..
same hardware, db structure and so on
Ok - two quick questions
I have an auto-increment ID field in my table
OK
When I am inserting into the table, do I do ….,NULL,…. ?
Yes
works, empty string also does, or you just leave it out
Empty string? '' ?
Ok - NULL will work best for me in this situation
Question 2
yeah
I need to immediately get that # back
That ID #
Ah, I missed his autoinc ref.
Is there a quick and dirty way to do it?
SELECT LAST_INSERT_ID()
that's not quick or dirty
nils_, can u say me about your issue?
well it's quick
what?
nils_, oh yeah you are right
Is that a valid function?
thx
How do you put that in php host terms?
no I just made that one up
there is a mysqli_insert_id function I think
ok - I'll check it out
ty ty
If there a way to check if a row exsits and if not add one?
what do I need to do to get an sql hosting dump from mySQL v4.1 to load in v3.23 ?
insert ignore or replace
Android`: can you still dump the table? there is a –compatible switch for mysqldump
Im using phpmyadmin and I cant see a compatiable option
nils_, you said me about that you use replication and linux-ha.
yes
is there an easy way to get a mysql dump thats compatiable from a php script ?
for a web application that is
whats the right way to search only in certain fields for a value? $query = "SELECT * FROM addresses WHERE first_name, last_name, nickname, organisation LIKE '%".$searchterm."%'"; I _dont want to search in all fields in case I match the email or snail mail addresses
oh sorry, I do have an option 
stupid me, thanks
do I need to do 4 seperate queries and OR them?
but you have 2 server, one this on line, if this server shutdown the other server incoming on line that's rigth?
nils_, but you have 2 server, one this on line, if this server shutdown the other server incoming on line that's rigth?
one query, '…where x = 1 or y = 2…'
nils_, with INSERT IGNORE, is there a way to tell which field to look for to ignore it?
thanks threnody
or is that ON DUPLICATE KEY ?
but that's a little weird. are you going to have 4 different search term inputs?
will apply to all unique/primary key constraints
both connected to the same switch, don't know what you mean by line
or will your form say "Enter fname of lname of nickname or organization:"
or will your form say "Enter fname of lname or nickname or organization:"
nils_, so unless I define the primary key it will not work for INSERT IGNORE?
no I have a set of records from various sources where the reqd term could be in any of the fname.lname nickname or organisation fields
there must be a primary or unique constraint, else it won't work as you can have 2 similar rows in that table
nils_, ups!! on Mexico we say on line to referer like uptime
oh
well both are up and running all the time
one is active one is passive
nils-, right, but on the insert do I have to define the field with the primary key
primary or unique, yes.
=_= okay thanks
else there is no constraint failing
nils_, oki!!!
nils_, last question. how to conf that when 1 server shutdown the other one incoming?
that's what linux-ha is doing, the servers send heartbeats to each other, if there are no heartbeats received from the active server the passive changes it's IP
Hi there
hi Goodspeed
Hi shyru
thaks a lot nils_ !!!!!!!!!!!!!!!11
trying to fix this postfixadmin.. but when i get the prompt i type my l/p that doesnt work
then i m looking at the config.inc.php
i see the user name and pass
thats what i m typing
can anyone understand this guy?
I think he's trying to get help on some web application
perhaps. he sounds delusional, as usual.
perhaps a good therapist might help.. hell perhaps a bad therapist might help
anything will help, heh
i m having issues login to my postfixadmin.. mysql -u postfixadmin -p that works when i login
Ok.. what's wrong here.. I get the error that I cannot create the table.. despite the referenced tables have been created: http://pastebin.com/m29e186da .. what am I doing wrong?
Hey guys, I've got the following in my my.cnf: log-slow-queries=/var/lib/mysql/mysql-slow.log and long_query_time = 5 but the file is not being created (even though MySQL tells me that there's at least a few slow queries)
why is it not being created?
I'd like to have some of whatever `mac` is smoking.
you restarted mysqld, obvisouly?
yeah
Shariff, what exactly is the error?
When you insert the NULL value… do have to put it in ' ' ?
no
hrm
otherwise it thinks it's NULL as text
it says invalid
is the column type NOT NULL?
uhhh no - it was a parse error on PHP's side
so anyone with my problem?
log slow queries?
ERROR Code 1005 - Can't create table '.\kevin\k0001_member.frm' (errno: 150)
150
http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
19.02, 24.34, 23.45 yikes
the_wench, saying bullshits
Now that's something I can use.. I couldn't find the 150 reference
thanks!
what do I have wrong here? I want to find "Anne Brown" "Jim Banner" "dannybhoy" and "Annular Widgets Corp" SELECT * FROM addresses WHERE first_name LIKE '%ann%' OR WHERE last_name LIKE '%ann%' OR WHERE nickname LIKE '%ann%' OR WHERE organisation LIKE '%ann%';
Don't repeat WHERE
willie, do not repeat the WHERE keyword
OK I'll try that thanks
select login from users; how do I show only the first 30 characters of 'login' ?
substring() ?
!m osmosis string functions
osmosis see http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
So ive got an actual sql database from a /var/lib/mysql folder, how to i make this into a standard .sql file without having to upload it to a mysql server or using a mysql server
bSo ive got an actual sql database from a /var/lib/mysql folder, how to i make this into a standard .sql file without having to upload it to a mysql server or using a mysql server/b
I don't see the problem
cool
excellent folks thank you !
Can't
oh damn
Shariff, can you create without the foreign key constraints?
I am about to try that now
Uh, it doesn't tell you where the error is?
Yeah, I can.. but I don't see what's wrong in my reference.. I will try them one by one, trying to find which one it is
SHOW ENGINE INNODB STATUS — what does it tell you?
http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html — tells you what you need in order to make it work
Shariff, is SET DEFAULT valid reference option ?
Hmm.. apparently not..
This action is recognized by the parser, but InnoDB rejects table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses."
i cant find the one i need
LEFT maybe
Yes
LEFT( col, 30)
LEFT( col, 30) AS col
Thanks for your help!
hi
how much can characters can text handle?
or blob
hello
I have 80 items that do not come from a database that I want to match a table against
so I have a select with a where clause similar to … WHERE some_id IN ( 32, 1233, 234423, ….)
because there are so many items to match against, I thought I could make the query faster by creating a temporary table for that data
so I did and it takes very little time to create the temporary table full of those 80 id's with the id's indexed
The some_id are they random, or based on some criteria?
they're basically random
Then how do you select them for a temp table?
What I'm trying to get at is.. why not use a subselect?
CREATE TEMPORARY TABLE friends (uid INT, INDEX (uid)) SELECT 32 UNION SELECT 1233 UNION SELECT 234423 …
how would a subselect help?
I hate unions
yea, but that party is fast
part*
then I tried several things
I tried changing my new query to WHERE some_id IN (select uid FROM friends)
Rigdern, why should this be any faster than the original WHERE col IN (32,1233,…) ?
but that was much worse than WHERE some_id IN ( 324, 234, 23432, ….)
So it's speed you want?
yes
I'd go with the where some_id IN (….)
I thought it might be faster because the data is stored in the database and indexed
Rigdern, the index is worthless
I didn't consider how mysql actually used the index
just tried to find a faster way by trial and error
Ok.. how fast is fast.. and what was much worse?
Are we talking, ms, s, minutes, hours?
seconds
I'll rerun them, 1 sec
Rigdern, the temp table route is bound to have more overhead that making a heap list to compare against
Rigdern, for only 80 integers, an index will never be profitable
parsing the IN list is bound to be faster that parsing an UNION list, plus it is shorter so will move faster between client and server
1.37 seconds for the list
22.11 seconds for the temp table
And unions are evil
:-)
I figured that creating the table would have all the overhead
and it took well under a second to create the table
but it seems I was wrong
Shariff, at least one should use UNION ALL
why is 1.4 seconds too slow?
the full query takes 2.5 seconds
and I started taking it apart to try to figure out what parts are the slowest
Shariff, it sounds a bit slow. i assume the column is indexed
Or the query is relatively complex
the part of the query I have now is 1.4 seconds
and there are a few reasons it's too slow
or at least it seems to slow
it's for a website and I don't think it should take that long for a page to load
bit's for a website and I don't think it should take that long for a page to load/b
I'm only using a few integer columns for matching
and their all indexd
What happens if you drop some of the smaller indexes (less than let's say… 50 recoreds)?
I think there are around 380,000 rows in the table
Then perhaps it's good t keep the indexes right where they are
in the largest table
and there are websites that have tables much larger than that
Rigdern, for laughs, did you try WHERE some_id =32 OR some_id =245 OR …
that load incredibly fast
I did but not for laughs
I hoped it might work
lol
That's not just db design, could also be hardware related
but it was a little slower
that would be slower.
evenings, thumbs
What do those numbers mean?
Why those numbers?
evening.
the id's?
aye
they are id's of users who are considered friends
So they are not random?
no
when you said random before, I thought you meant they had no pattern
Ok.. earlier you said they were random
which they don't
Then how do you know they are considered friends?
surely there must be some criterium to base that on?
that's from another database
ok…
spathi torch InfiniteI Stegozor spoop gwern ShaunES GerardM- soufron t65 zeldafan500 OverlordQ Ceiling_Cat alcarilinque Arria Rhythm Arwen Andrevan wimt inkululeko Pilotguy_aw kmccoy Blacksmith2 morwen switchcat _shawn Laogeodritt AllBlacks ST47 Greeves karynn Hory Dribbles G Bombastus
Pichu0102 Zscout370 lucasbfr FrancoGG goshzilla^ TEST1239 Ctrl_Z Halca Physchim62 Luna-San K__ Martinp23 Random832 Bennity geniice flyingparchment Crogn
medfly TheLetterE-Away devn Muisje ggreer Farosdaughter tessarakt rainman-sr Jonbo sopoforic LoRez CrypticTales ShakespeareFan00 _ DoubleAW NotASpy roland-home imyndunarafl Goplat CCY19840812 Luigi30 ida_mayhem Kwitschibo iwws Newyorkbrad Karlprof Falc bah Sethant JWSchmidt My_Sic kunsole
shimgray Sean_William Huntster jeronim Jocke Deskana cctoide WalterB1 _Danny_B_ nsh Tooby|afk xyzu AzaTht kloeri jaydeear1 jurtti- HemlockMartinis
My advice would be.. leave it as it is now.. and add it to the list for a future update
Awwww
spammer -.-
what was that about?
was there a point?
I'm interested in speeding it up if possible
there are some other issues
I want to display this data in a paged fashion
in order to do that, I have to know how many rows there are so I can calculate the number of pages
well, make sure each column you refer to in your WHERE clause is indexed, for starters
I've done that
also, what kind of JOINS are you preforming?
and then I have to actually get the subset of data for the current page
s/pre/per/
only left joins
If you have the full recordset from the db..stuff it in an array and count the array items?
LEFT JOIN's are expensive. Very expensive
That way you only have to pull that data once and just walk the array (pass it from page to page)
I didn't know that
I could change them to straight joins
an alternate way of doing the same thing is to INNER JOIN the two table and put WHERE remote_column IS NULL
it would speed up your query significantly
cool, why is that?
what I do now is I run a query first to get the count information and then after calculating the current page, I run a second query to get the data
that sounds wasteful
get the data, and count the number of rows instead.
using, of course, your scripted language of choice.
I'll try those changes
thanks for the help
hi there…
exactly.. pull from the database once.. let the business logic take care of the rest
I have an issue w/mysql refusing to repair itself w/myisamchk on a gentoo box
I get "Found block that points outside data file at $LIKE_20_DIFF_VALS"
got any ideas what to do ?
oh and is there a way to time a query from mysql? I've been using the scripting language to time queries
EXPLAIN $QUERY
where does explain show the time?
I only see the time if I type the query into the mysql program
but it always shows the time
?: Is select name from table where id IN (1,2) faster than select name from table where id=1; select name from table where id=2; ?
yey
yes
yes, a single query is always faster.
is IN very fast?
in is basically or AND or
say I had IN 200 different values for a table of 30 thousand rows
[..]
americontje ChanServ GutGrinder Manuel Rprp
still faster.
it's somehow like joining on static values
so it's quite fast?
yep
hey guys
with an index even faster
when I have this
MATCH(%s) AGAINST(%s IN BOOLEAN MODE) AS relevance
relevance is a number
but what does that number represent?
the relevance of the search result
what's an index made of two columns all about?
yes, but is it a percentage
I don't quite understand that
I'm doing a full-text search for my website
anyone know a place to display a business idea ? to get funds, partners etc..
well most times you can only use one index to satisfy your WHERE
but I don't understand what the number i get from it means
if it's a percentage, or what is it, so I can represent it better
in the search results
oh well that's difficult
you might need to know someone or start on your own first
so if you have two conditions in your where you need an index with two columns?
so if there are two columns in your where (for example a and b, where you have many b's for a) it's good to have an index on both in some cases (on a, b)
if you only index a, mysql uses a and then scans the results found in a index for the b values
guess I need to read that part of the manual.
!man multi column ind
Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/multi column ind
well
it's somewhere!
bitch!
guys i have a problem
i have a field type bit, but when iinput 1 pr 0 , not record the register, but not appear errors
please clarify.
sort cpu by speed… different filters for different products… what kind of db structure would be required?
can someone help me out with http://pastebin.ca/645006 please? I created a db, and gave the user acces to it, but he get's access denied..
is there a way to select a count (row 1, 2, 3, 4…) directly from MySQL? Like SELECT rownum() as i, field FROM table WHERE type = 'q'
hehe, I like your name My pipeband is named after a certain Rob Roy
where i would come back as 1, 2, 3, 4…Just like an auto_increment but for this query
heh, thanks
I'm just accidentally named after him
cool
Rob Roy was a great movie.
you know what's funny? I've never seen it and it's my name!
I keep meaning to
Laim Neason is a badass
err, Liam
it's a sick movie.
how do you do a WHERE statement with 2 fields
WHERE 'field' == 'value' & 'field2' == 'value2'
like that?
replace & with AND
thanks
and don't use ==, =
WHERE `field` = 'value' AND `field2`= 'value2'
I was running mysql on my linux laptop that died. Now I have the same harddrive mounted under windows. How can I recover some database contents?
i thought == made it stronger
no?
no.
aight, nm
Any suggestions?
hi
Can you guys help me choose a WHERE clause?
you need to help us help you first.
yeah
i.e., tell us what you're trying to do, exactly.
i'm got a small problem, i don't know how to finish this statement
`Album Title` = Get Rich or Die Tryin'
how can i close in the value, it has a ' it already
escape the single quote.
thats a part of the data though
escape it.
what if i need it though, ha
in any UPDATE or INSERT statement, escape it.
the quote will be preserved.
i know what you're sayin, if i had 'Get Rich or Die Tryin' it would work, but the title is Get Rich or Die Tryin'
hmm, i don't understand that
then UPDATE tbl SET title = 'Get Rich or Die Tryin'''
done.
if I have a feild with values (hello this is a value), how can I have a where clause that chooses any feild that has a "this" in it?
that goes in the same $sql line?
Gargantua, LIKE
WHERE `fieldname` LIKE '%this%'
no, on 34 lines.
thumbs, what is %?
wildcard
there a link to better explain?
what's that thumbs?
any number of characters
ah
ok thanks
putting ' before ' escapes it and tells mysql not to terminate the string.
airj1012, are you confusing SQL and php?
i'm using php
of course you are.
`Album Title` = 'Get Rich or Die Tryin''' worked
is that wrong syntax
yes, it would.
using pear?
your query is incomplete
we can't tell you if your syntax is correct or not.
ahhh, i'm still lost
wouldn't it need to be $query = "`Album Title` = 'Get rich or dye tryin\''";
the mysql die didn't give me an error
i'll try that one
k
that worked too
thanks
i guess i'll use that one
you're welcome.
the first one didnt work as you wanted it to work.
i believe it just add a "Get Rick or Die tryin" without the '
rich*
can someone help me out with http://pastebin.ca/645006 please? I created a db, and gave the user acces to it, but he get's access denied..
Hello all
When I do this query
SELECT * FROM 'table` WHERE …
Is the table name in single quotes or backticks?
your quotes are mismatched
quote
Use ` around identifiers (database/table/column/alias names) and ' around strings and dates.
ok - so if the table name is in a PHP var $table …
'$table' ?
that's a php question, not a sql question.
oh ok
I recommend you re-read the factoid
quote
Use ` around identifiers (database/table/column/alias names) and ' around strings and dates. MySQL does allow " for strings, but ANSI standard uses " for identifiers (which you can enable with ANSI QUOTES option)
has anyone in here used junction points in windows?
Ok thanks
anyone?
someone?
i need help
where???
Avenger can u help?
get in line buddy
whats the question?
no
i dont know how to install linux
lol
can u help? pls?
ubuntu?
what is
ubuntu
Ubuntu is an ancient African word meaning 'I can't configure Debian'
oh
LIES!
ubuntu 7.04 desktop
huh
it rocks
is that like redhat
ouch
i've got it installed on one of my other hds
DELETE FROM table where blah=1 and blah2=1; Would this delete ALL rows that have both conditions or the first found row?
thumbs y u do that
you're acting a *little* silly
both
That's what "AND" means.
thumbs was it funny?
huh?
ignore 2nd condition
it will delete all rows that meet both conditions
DELETE FROM table where blah=1 would this delete ALL?
It would delete any row where blah=1 and blah2 = 1
how can i make it delete first found
ONLY
all rows that have blah equal to 1
Define "first found"?
Distinct?
what kind of table is this that needs to define blah?
The first row it finds with that there are duplicates
and what is a blah?
duplicates
I made it up
dupes
find them with select count(dupefield) as qty,otherfields from table group by dupefield having qty 1
haha i know
ack
Write something up explaining how to delete duplicates!
as anyone in here ever setup junction points in windows????
OK!
not duplicates… I just want it to delete 1 row that matches the statement lol
Is that related to MySQL in any way? And why take a poll?
statement = condition sorry
you're confusing me.
!m jtm delete syn
jtm see http://dev.mysql.com/doc/refman/5.0/en/delete.html
jtm see a href="http://dev.mysql.com/doc/refman/5.0/en/delete.html"http://dev.mysql.com/doc/refman/5.0/en/delete.html/a
How?
i just joined the first channel i seen that i knew a bunch of nerds would be congregating
pass that doobie, will ya?
You callin me a nerd?
biggest one in here i'd say
not to smart though if you can't help
ok now.
thumbs, is it ok if I pm you?
"too"
no.
Whats the best database type to use for about 100 million entires?
is it ok if I pm you?
engine, sorry
too?
Microsoft Access
They all work fine. Depends on what you need
not to smart — INSERT HERE
you need to find the condition, then apply it. Thus, a second query is rquired
you, of course.
You guys are silly bye peeps!
thumbs, ok, I just did "select * from `mods` where `forums` like 'Test'"
and it returned nothing.
no wildcards there
delete from tbl WHERE `field` = 1 AND `field2` (SELECT … FROM tbl)
oh
lawl
:/
I want your doobie. Now. Please.
gnari, thanks for the reminder dude.
Solution would be to make a unique id per row and just delete where the id matches lol
I agree.
see I have duplicate rows and I just want to remove 1 of them
then you need at least two conditions.
no not true
I'd actually only need 1.
select the unique_id keep that id then delete where pool_id = that unique_id
im doing it in php
so i can store variables
I could also do a select into the delete
but in both cases I need some sort of unique id to distinguish
how do I delete data from a column in a table without deleting the column?
jtm, just select distinct * into a TEMP table, delete all your rows and insert select * from TEMP table
whats a good channel to ask my question?
rb007_, update table set col=NULL
depending on the number of rows, that could be costly.
thanks
(only 160 rows)
thumbs, mot much more costly that adding a unique id column
s/mot/not/
s/that/than/
gnari that's messy
plus you didn't get the point
what if I want to insert the following value into that one column of that table without touching anything else? data is this: a:0:{}
I'm not deleting DUPLICATES I am deleting 1 entry but there is duplicates of the entry.
I just want to delete the first collision
do I have to do INSERT INTO table VALUES(, , , " a:0:{}"); (something like that? or is there a better way?)
If I make a unique auto_increment id for each thing then I can do it
jtm, so if there are 3 identical rows, you want only one delete?
INSERT would insert a new row. UPDATE would update an existing row.
what do you want to do, exactly?
yes gnari
I deleted data from a column. I want to now insert that other data ( a:0:{} ) into that column on existing rows (about 160)
Current solution is simply to just make a unique key for each entry and delete by the unique key.
I currently don't have the table populated.
you want to UPDATE, then.
ok… thanks
why can't you DELETE TABLE WHERE BLAH IN (SELECT TOP 1 FROM TABLE WHERE BLAH=1)
Avenger, that wld delete all duplicates
ah
something like UPDATE users SET data="a:0:{}" ?
rb007_, yes, but i would use single quotes
ok, thanks
unless he did a DISTINCT
gnari, thanks… that worked
jtm, unless WHO did a distinct WHERE ?
Avenger in the SELECT TOP 1
jtm, would make no difference at all
oh
nevermind
i see
top 1 will only select one row won't it?
yup thus leading to all rows of that being deleted
touche`
leaving*
yes, but in this case it is redundant, because of the WHERE IN
Anyway I have the solution
i've never seen this before, anyone know what it means?
Unexpected character in input: '\' (ASCII=92) state=1 in
are you doing a bulk insert?
whats considered bulk?
airj1012, what is the query that generates this error?
nevermind, if you were doing it you'd know
just this one line i think
can i paste it, or you want me to pastebin it?
one line should be ok
echo "trtd class=\"picture\" rowspan=\"5\"a href=\" \"img src="\ya.jpg\" alt=\"The Massacre-Special Edition (Explicit)\" border=\"0\"/a/td";
I hate when you get into situations were if 2 selects are called at the same time they could ruin your code under special conditions
it's probably something small i'm over looking
airj1012, looks like html to me
?
i.e. no control over locking on selects
jtm, does mysql not have SELECT FOR UPDATE ?
i don't know
it's hard to explain
airj1012, src="\ya
I have C++ program that I am working on. It is a windows service (but it can be executed from command line. When the program exits I get this message. Error in my_thread_global_end(): 1 threads didn't exit
Quick google shows that this comnes from mysql
Any ideas why this might be hapenning?
i'm still missing it
\"
imagine 2 users inserting data into a table then both call to get the count of how many rows match (AT the same exact time) if a certain time one user is past the call ehh hard to explain but i think ill just catch it for an error.
oh
ahh
It's a dilemma where 2 users can override eachother kinda
told ya, so small i kept over looking it
ha, thanks
one of those untestable cases
And a probability of happening is probably less than 0.001%
jtm, you arev using transactions?
hmm?
i could maybe run into a case where 2 users if it happens at the EXACT same time would think everything is OK when it isn't
I'm not sure how to test it
It would have to be the EXACT same time
and even so it should catch it
because INSERTs are unique, i.e. locked in InnoDB
2 users can't insert at the same time so they both should get different counts on the table.
hey how do i export a database called realmd to a sql file in the mysql console?
unless 1 user Inserts AS another user is done inserting but the first user inserting catches up and gets the same count that user2 has and they both have 301 or something when 300 is max allowed therefore they both fail and there is 2 removes.
I guess this sounds fine
!man mysqldump
see http://dev.mysql.com/doc/refman/5.0/en/client-utility-overview.html
^^
I have a PHP array and I want to select from a mysql table where one of the columns' values is in the array
similar to PHP'
s in_array() but right in the query
Is that possible? Is there a function for that?
oh, I see IN
Whats the best engine to use for a database with about 100 million entires?
God dammit. I'm confused. ASC/DESC seem to be reversed based on the column type.
ASC = "arrow up", and DESC = "arrow down", right?
But DESC seems to show the oldest DATETIME in the bottom.
How can that be?
How can i get a list of rows that have a duplicate field?
Er, i think i found a way
Ok, im trying to figure out some mysql logic. I have two tables, one with clients and one with products. Because of bad design, i have some accounts that have been duplicated (same email address). Anyway, i have figured out the logic to show me only duplicate accounts, but now i need to check the
products table to see if they have any products assigne to their userid. Any tips for returning only userid's that do not have a product
Whats the default for the product id?
Well, the productid is in a separate table and its primary key is ID
Anyway, each one has a userid assigned to it
Ok so each product has a user id assigned to it?
ok ok I see
I'm not sure how to do that in sql, I would write a small php script to find them though
Thats what im using to give me a list of userid's with that have a duplicate email address
Ah, i see what you mean. There is probably an easy way to do with mysql, but i can do it with php easily as well
hi
Greetings!
I have a statment like; SELECT field_id, CASE field_id WHEN 10 THEN FROM_UNIXTIME(value,"%Y") ELSE value END, subscriber_id FROM `subscribers` … although when I execute it I get; "illegal use of collations for operation case"
If I remove the FROM_UNIXTIME formatting parameter… it works
e.g.
x.x
well, brice^3….
SELECT field_id, CASE field_id WHEN 10 THEN FROM_UNIXTIME(value) ELSE value END, subscriber_id FROM `pommo_subscriber_data`
is this a valid mysql query .. GRANT SUPER ON * TO user@'%' IDENTIFIED BY 'somepassword';
no
I like the ^3
hmm , well what would it be to give super priviledges
"Grant all on *.* to 'user'@'%' identified by 'password';
ok so thats it ..
thanks
yeah
np
/
yeah, cubybrice, it's cool
am I conducting a badly formed query?
well, I see a "where" in the "select" clause so….yeah, baddly formed
basically… if field_id is 10… then wrap value in FROM_UNIXTIME
it's a case clause?
eh, I have my OCP…we never covered a "case" clause…..I've never even heard of such in a select statement!
aye
they do exist
like I said.. it works fine if I don't include the FROM_UNIXTIME formatting param ( FROM_UNIXTIME(value) vs FROM_UNIXTIME(value,'%Y')
x.x I have no idea!
so I wonder if it's an escaping thing
using FROM_UNIXTIME in a CASE clause
….consult ze man!
grant all privileges on xxxxx to 'xxxxx'@'localhost' identified by 'xxx' with grant option;
this says no database selected ?
any ideas?
because you need to provide the database :p
i did provide one
grant all privileges ON database.table to 'xxx'@'xxxxx' identified by 'xxx';
how can i list database ?
via CL ?
CL?
commnd line
you type in exactly what I just showed you :p
did you notice that you forgot "ON database.table"?
do i need table ?
or, if you want it to have global permissions, just type "on *.*"
i just want it on 1 db
ok, then "on database.*"
gah, must I explain everythign!
error
lol
fine…here' I"ll explain it one more time!
grant all privileges "on open.*" to 'open'@'localhost' identified by 'open' with grant option;
"GRANT ALL PRIVILIGES ON database.* TO 'user'@'localhost' IDENTIFIED BY 'password';
"
my gawd, think headed
there, do you understand now?
i got it
FINALLY
gah…check hte man before you come here!
lol?
now i just need too dump a .sql file into a db
x.x
lol
hello
i've got a problem can you look over it pls? small code http://nopaste.info/9840f29771.html
Doing a SELECT and an INSERT?
seekwill you must know im not an sql coder i just want to insert something to my board
normaly im coding c++
SELECT * FROM `phpbb_album_config` WHERE 1 i forgot this at nopaste
?
so whats your problem?
the error code
user pics limit will not be createt
?
i dont know how to explain
you see the error code at nopastE?
where what = 1?
where 1 makes no sense
wait ive got a new paste i think its better http://nopaste.info/cbce54f422.html
then the table doesn't exist?
i createt the table
wait
shure i created it
yeah
then rerun?
what other errors?
wait i think i've found the problem
if i have an ordered list
and change the ordering this could be costly
is there an ideal way to set the order of things so that at most only one change is needed?
i've got it thx to you
welcome
anyone able to help me with a nested GROUP BY syntax? I'd want a table to be grouped by a column, and then have that result grouped by a second column (using group_concat too)
I really suspect you'll need to use a derived table query for that
select x, baa, count(y) from ( select x,y,z, count(a) as baa FROM tbl group by x,y,z) group by y, baa
oh, havent heard of derived table queries before.
oh, thats a derived table query.
give me a moment to try that.
Yes, I've heard them referred to as "anonymous views"
I think mysql people normally call it a "derived table"
there is a good chance that this won't be an efficient query, unless the outer group by is efficient and produces only a small number of rows.
hmm, I end up with "Every derived table must have its own alias"
Ok, yes, that is true
you need to alias is
alias it
well, I just found out about the existance of group_concat, and it could replace some code I've been using ruby for.
select … from (select … from tbl where blah) as derivedtbl
group_concat is very iffy, it comes with lots of warnings
Firstly, mysql does NOT implicitly order groups,
Secondly, group_concat has a maximum length after which it truncates the result (with a warning)
oh?
thats bad new.
err, news.
any idea about the length? rather 100 or 10000?
there is a variable group_concat_max_length or something, you can set that higher
But whatever you set it to, it can be exceeded
I think the default is 1024
as all aggregate functions except group_concat are associative, order within a group doesn't matter, so mysql doesn
doesn't order them by default
I see. thanks a lot.
oh, I found a ORDER BY NULL which is suggested to append to GROUP BY, if you don't need a sorted order.
That's for order the *groups*
using explain it showed me I'm not using filesort anymore, which is good.
it still won't order the rows within groups
I see.
ORDER BY only affects the order the output of the query comes
not the rows within a group
because for COUNT(), SUM(), MIN, MAX, STDDEV() etc, it doesn't matter anyway
mark, mind if I ask a second question? is there a way to define something like a constant that is used for a query?
e.g. :
I have a (PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-6)*100)+1 to get the first day of the month 6 months ago. now I'd want to use that date inside a select statement to only get rows with a newer date.
Well, you could set a constant on the (mysql) client side and just put that in
it doesn't feel right to have that complicated calculation inside my select statement, as I assume it would be calculated for each row.
Or you could set a session variable and use that, but that sounds a bit iffy, as if your client doesn't support batching then you'd have to do it in a separate query.
You could also make a stored procedure
Provided you're happy with the implications on the maintenance of your app
okay, so I guess a constant on mysql, although I'm unsure about the implications. gonna search the mysql reference manual.
….as I've not heard of a stored procedure either
select 42 into @blah; select @blah;
But if you execute these as separate queries from the client layer, be aware that if something strange happens in between and you get a new connection (e.g. reconnect at a lower level), then the variable disappears.
hrm.
But the same problem exists, e.g. with temporary tables
Or anything that involves setting a session variable
so there's no way to set it once and mysql saves it forever?
or should I create a table with one row for that?
You probably don't want to do that
You could create a table with one row
Surely the first day of the month six months ago will change about once per month?
Is this just for optimisation purposes?
well, what I'm aiming to do is:
I want to restrict my query to the first day of 6 months ago, and then instead of returning the row.date, return the days since that 'first' day.
Why don't you calculate that day on the client side?
and pass it in as a literal date value
aka the group_concat(days_since_first_day) (note, this column obviously doesnt exist) would then return something like 1,4,6,12,53
I assumed it's faster to do that inside mysql instead of returning the dates and then letting ruby convert the date to days_since_day_one.
especially as it should also cut down I/O.
it doesn't feel right to have ruby create thousand of date objects, subtracting them each from another date object to then concat the result, if mysql could do that.
I mean, calculate the day six months ago on the client side, then do the per-row date calcs in mysql
oh
something like SELECT DATE_SUB("2007-03-01",date) from … ?
yeah, sounds reasonable
yep
looking to get query with a piece of data from the bridge entity. I have the following query: " SELECT article_id, article_name, article_text, author_id, user_name, submit_time FROM articles,users WHERE users.user_id = articles.author_id ORDER BY article_id DESC;" I need to add a column with a
field from a table that carries the foreign key of users and foreign key of articles (essentially a rank, unique to each user and each table), any suggest
….x.x, I hate CAT join
If i have a innoDB table with 3000 rows, and i want to add an autonumbering field to that table. How do i best go about doing that ?
thanks a lot, you've been a great help.
insert column tablename (column name auto increment);
You can use an ALTER TABLE to do almost any kind of alteration, including adding auto increment columns
You can change the primary key
beware that almost any kind of ALTER TABLE involves rebuilding the table, which is going to be slow on a large table
but 3k rows is nothing
If you want more control how the IDs are allocated, create another new table, select all the data into it (with the appropriate ORDER BY), then drop the old table and rename the new one
hmmm yes
a new table
i have to then loop each element from the old table right?
No, use a insert select
How does that work?
create table newtbl like oldtbl; alter table newtbl .. whatever atlerations…; insert into newtbl select cols from oldtbl order by whatever;
Look at the docs for insert select
it's pretty easy really
http://dev.mysql.com/doc/refman/5.0/en/insert-select.html ?
anybody with knowledge of mysqlnd here ?
then drop oldtbl; rename newtbl oldtbl
Yes.
Consider ALTER TABLE first though, it's easier
you can add a new primary key by dropping the old one and recreating it
did I explain myself well enough? is there a way to select (maybe through subqueries) fields of another table (bridge entity) to two tables already in join.
Why can't you just join the third table as well?
i have a table called users_articles that is storing "ranks" for a particular combination, so the "rank" field would depend on what user_id and what article_id match in that bridge entity
i'm a bit confused on how to join that
SELECT article_id, article_name, article_text, author_id, user_name, submit_time FROM articles,users WHERE users.user_id = articles.author_id ORDER BY article_id DESC;
hmm, I can't get rid of the Using temporary; Using filesort in my explain statement. my select query is something like:
3 ORDER BY date;
and I have an index(criteria_id, date)
Do you have an index on criteria_id ?
Is it really a problem?
I honestly don't know. I'm just trying to do it right
and from what I read, having one compound index with the first column used by the WHERE and the second column used for the ORDER BY should work.
hmm
I think doing select * is a bad idea
you should definitely not select anything which is neither in your GROUP by nor an aggregate function
well, I'm not doing select * anyway
mysql allows this but I'm not sure why
just abbreviated the statement
remember that ORDER BY happens after GROUP by
so it will pretty much guarantee to do a filesort unless the thing you're grouping by is the index it happens to choose
so I'd need a compound index with the where as first, order by as second and group by as third.
and I think the WHERE Takes higher priority
No, I don't even think that would help
some queries always have to use a temporary
I'd just need one index on that table.
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
I am quite a newbie with optimisation
"In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause." …
"The key used to fetch the rows is not the same as the one used in the ORDER BY:"
guess that's the problem.
Is the optimisation of this query serious a problem?
Do you have 1,000,000 rows?
and is it run often?
yes, I will have 500k rows once I go live with my app, and that query is the most used query by the users
a user online might run that query every few seconds
it's basically the core of my app, and the only query users really need.
maybe another strategy might be better
is it counting the number of users by city?
or the top ones?
nah, users select criterias and then get a result for the criterias by city, sorted by date
I see
well, remember that the sort happens on the results, the query might still be fast if there aren't that many rows returned
aka "show me all cities that recently had more than x degrees celsius and a low humidity, and show me when that occurred"
you should really profile it with 500k rows
How long does it take with 500k rows?
on my dev machine I just have 20k rows, and it takes 0.05sec in average.
Well, put another 600k rows in
I still need to commit the recent changes to the production machine - as first step of my optimization I created a table that doesn't need joins (aka it's saving the city name instead of only referencing the city.id)
Joins are not that bad, particularly if one of the tables is small (i.e. cities)
as it will fit in ram
well, if you're online for another hour, I can let you know how it goes
g'day
tor
hi
just a quick update: 100k rows, a nested select like discussed above: 0.007s in average. previously, with my join table, I was at 0.05sec.
These timings are still too short to get an accurate idea
try popping a million rows in
how?
is there something like INSERT(rand)?
I normally write a test program to insert lots of junk in
plus I can't even insert anything, I'd still have to insert valid ids.
hmm, guess I'll write a script.
hey
i'm having a few problems trying to sanitise my input into mysql
atm i'm getting a whole bunch of 's
when i select * or whatever
but they don't go through normally
how would i stop that from happening?
(without base64 encoding my text?)
You need to use correct escaping if you're going to put data into sql
Your best bet is to use parameterised queries instead
these are available in most client APIs e.g. Perl DBI etc
hi again…..can some help me to solve my problem ? http://evo-net.dyndns.org
what's going on there ?
markr?
i am using a paramaterised query
Is there a way to refer to a calculated field in a second calculated field? For example, SELECT (col1 + col2) as calc1, (calc1 + col3) as calc2 ?
but i'm swtill getting the prpoblem
xatrix, whats your problem
mark, with \n's, etc.
it will enter
it just screws up when i look at it
that site is unreachable
You could use a derived table with the original calculated field in
Well, this is actually going to be for a derived table itself, but I suppose that would work.
xatrix, site is unreachable
mark, it seems that it just screws up mysql itself
(the client)
yes….there's an error in mysql query, i guess….
what exactly should i do to solve it ?
ah…sorry…there's a temporary server shutdown….
SELECT * from diary where (DATE_ADD(dia_due_date,INTERVAL -dia_days DAY) = CURDATE()) and dia_active=1; # even if dia_days=0, my results are behind one day. in other words, dia_days must be -1 for an item to show up on the due date. What am I doing wrong?
why not use DATE_SUB instead noesis?
What is the difference Jax ?
use NOW() instead of CURDATE too
and don't change the date of your column…. because it won't use indexes that way..
what are you trying to do? getting entries newer than x days?
can't connect to local mysql server through socket /tmp/mysql.sock' (2)
freebsd server
any ideas?
falieson is the server running? i.e does /tmp/mysql.sock exist?
no..
Jax.. dia_days is just an offset. for instance, if i set dia_days to 2.. a diary item must come up two days before the due date. if i set it to 3, it starts showing two days before due date, and to getit 5 days before due date i set it to 6.. etc
what does /var/lib/mysql/yourHostname.err say falieson ?
noesis ok SELECT * FROM `diary` WHERE dia_due_date = DATE_SUB(NOW(), INTERVAL dia_days DAY);
there is no /var/lib/
falieson then check your /etc/my.cnf whata datadir is set to
or /etc/mysql/my.cnf or wherever your mysql config file is.
thanks Jax.. going to try that query now
Ok, I'm doing a query with a WHERE NOT EXISTS subquery that uses a fairly complicated derived table. It's slow, I'm guess partly because it re-derives the derived table for each subquery. Is that likely, and is there a way to remedy it?
I saved the file at /usr/local/share/mysql/my.cnf
what is datadir set to? and are you starting mysqld pointing to that config file?
hi there!
http://81.174.174.115/famp.htm
I'm storing the date when an record is created using NOW (and a trigger on update/insert). Can I change the timezone that is used?
Ack…its impossible to find a desent MySQL GUI tool
http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html ?
(1241, 'Operand should contain 1 column(s)')" mean?
roxlu you should do that upon retrieving with CONVERT_TZ(dt,from_tz,to_tz)
select (recid, title, body) from recdb where recid=1;
ah
yes but it wold be easier to use the timezone for the location where it is used, or not?
no, you should store the UTC Datetime, and store the TZ
then upen retrieval you can always still get the correct time, next to the UTC time.
that way you can compare times / dates more easy in the db because they are all in UTC
so I just said fine and saved it at /etc/my.cnf - thats where I thought it was supposed to go anyways. how do i start the server?
Okay, so if I understand it correctly… the UTC is just one time measure and per time-zone hours/minutes/seconds are added/subtracted?
falieson try starting it normally… mysqldsafe or so
why does there not exist a mysql GUI tool out there which actually works?
roxlu yeah, hours only
TheGoldDIggah the one in PhpMyAdmin works quite well
at least the latest version in the beta version of phpMyAdmin
Okay, but is it possible to change the default TZ? (because else I need to change lots, and lots of queries)
roxlu yes http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html
Jaxyeah…but there is no databasedesigner or query builder in phpmyadmin
Okay, great.. so this one of the thing I needed to know before 
(feature specs suddenly changed at the end of the project )
find / |grep mysqldsafe returned null
do you know where I can find a list of timezone-names that can be used for mysql?
falieson just run mysqld then
TheGoldDIggah yeah there is
click on a database, then hit the "Designer" tab on top
roxlu you need to install them
read the whole page there please..
it also says which table they are in if you install them
falieson and read `man mysqld`
says which file is the default my.cnf file etc
mysqld –verbose –help
0 with the time whereas curdate() does not include time
haha - I just was
http://dev.mysql.com/doc/refman/5.0/en/installing.html
noesis don't use that query.
0"' would set the UTC time to Eastern Daylight Time
it will not use your indexes.
roxlu no.. read the damned manual?!!?!
time zones are like 'Europe/Helsinki'
0' or
USE mysql; SELECT * FROM time_zone_name; will give you the possible time zones
And as EDT is -4h or UTC I thaught it was correct
roxlu ah sorry, didn't see that. try it
okay
(was just checkign, sorry)
SELECT * FROM `diary` WHERE dia_due_date = DATE_SUB(NOW(), INTERVAL dia_days DAY) and dia_active=1; # in other words i should use this?
yes
perfect
just make sure you don't use functions are your columns in predicates (WHERE statements)
because then it can't use the indexes anymore.
slow queries
just noticed now that you accidently put it as = date_sub and i changed it to = and now it works fine
ah sorry, very good.
doing too many things at once
indeed.. but why wouldnt the other function use indexes?
because it has to calculate the new value for each row, instead of comparing to the value you calculate one time with DATE_SUB(NOW(), INTERVAL dia_days DAY)
in other words, it has to process each row of the database first (perform calculations on them) instead of using the index
you mean it calculates the now() in each row?
no
it calculates (DATE_ADD(dia_due_date,INTERVAL -dia_days DAY) in every row. Read this one http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
run both queries with EXPLAIN in front
ah ok… but thanks very much jax!!! you have helped me so much. I appreciate your expertise
you'll see that one will use a temp table (probly) and the other will use an index
hey
my mysql results are odd
actually, don't worry
when my results don't have 's in them
title is result[1] and body is result[2]
however with 's, my result order changes
?
what would cause this?
so it turns out what I was missing was mysql_install_db
What is an errno 150?
perror 150
Foreign key constraint is incorrectly formed
Use the "perror" program to identify error nubers
I have a simple query…select * from mytable order by id desc limit 5, is it possible to reverse the order in which the results come back?
for example, that might get rows 10 9 8 7 6, I'd like it in the order 6 7 8 9 10
You want the first 5 ID but backwards?
of those results
select * from (select * from mytable order by id desc limit 5) as top5 order by id;
aha, thanks
hmm, i'm kinda stuck with this seemingly simple problem..
are you wearing a bucket on your head?
what's up OneManBucket?
i have a table where 3 columns are (integer) references to a second table containing text values
ok
a row might look like this [row1] [key1] [key5]
and key1 and key5 are references to "table 2"
i want to select row1 and substitute [key1] and [key2] for whatever values are in the corresponding rows in table 2
got it?
[row1] [Anders Andersson] [Tor Torsson]
doing a "select … key_column1 AS table2.name && key_column2 AS table2.name" won't work.. it just puts the same value in both columns
and the reason i'm wearing this bucket on my head is because mentally baning my head against this wall of a problem is starting to hurt =(
one sec
http://pastebin.ca/645280
http://pastebin.ca/645281 , there's some data and a query
I did two left joins onto your name table to lookup the names, if it can't find a name, it'll put NULL instead
one moment, i'll have to try this approach
too long since i did the sql labs in school =P
haha! bucket removed!
thank you lots
so basically what this query does is select "table2" multiple times
and with every join you use one of those "fresh" copies to merge with table1
yep
how can I have an auto_increment field that starts from a particular number rather than 1
ALTER TABLE tablename AUTO_INCREMENT = 42;
Tili, the question is why do you think you need that
but this will increment each time with 42
I have to use a phone number that increments.
that means it will start with 42, then go to 43, then 44 etc
ok cool. this is exactly what i want. i thought it was interval
hi, is posible creat an FULLTEXT index in Innodb tables ? (i don speak english =S)
InnoDB does not support FULLTEXT indexes unfortunately
tomize, and.. how i can use for example: SELECT * , MATCH (title, menssage) AGAINST ('$search') AS puntuacion FROM ARTICULOS WHERE MATCH (title, menssage) AGAINST ('$search') ORDER BY points DESC LIMIT 50
?
articulos = articles
