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

http://pastebin.ca/645139

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