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

Grouping categories must be done in PHP, true? However, are all rows downloaded in correct sequence do to it? How about changing the sequence of categories?

hehe. i found that in a different irc channel. it's always been zippier than other pastebins for me

I'd like them one at a time….

Sorry. Change that COUNT to SUM.

Perhaps I'm going about this the wrong way. How about how do I change all the column names to lowercase?

does it matter ? is mysql case-sensitive in entity names ?

one at a time, using LATER TABLE syntax

fatpelt, i've liked pastie.textmate.org

ALTER TABLE*

I'll start up the FreeNode Campaign to Swap the A and the L
and change my name to ldlptr

did you try to implement possibility of changing sequence of categories?

well that being the case I'd like to put it in a nice programmatic loop hence the earlier question

no
but it is implemented in another module
the categories module
i'm not quite sure how
it uses nested sets as well

i think the change of "left" and "rights" of all lower categories is required when we change the sequence. We can sort by name as well.
I know when i looked at example of tree with left and rights marks, i thought getting higher categories is impossible. However, articles say it's possible.

well, you could cobble something together in an app like php or perl, but by the time you got it tested and finished, it'd be easier to just do it manually.

mysql internally returns the number of rows available when you do a query, is that correct?

ya

know how you can grab that in Python or php by chance?
looking for a few references to get me a head start

mysql_get_num_rows() i think it is in php
that is more of a #php #python question

ya I realize that
hrm i think that just gives the length
i can literally get the entire result set size though, even if i only select 30 of them, right?
w/o doing an extra count() query

ya.. you never said you did a limit
SELECT SQL_CALC_FOUND_ROWS bla1,bla2 FROM table LIMIT 0,30;
SELECT FOUND_ROWS()

that will give it then?

it will run the query to return the 30 rows.. then the second query will return what could of been found if no limit was there

hrm, found_rows() returned 1

5

ya?

well of course it will return 1 if you do a count()
since count returns 1 row

the count is aggregating
its returning 1000+ rows

no

yes it is
1091

yes it is.. but in sql.. its getting 1 row
the 1091 from count()

Showing rows 0 - 29 (1,091 total, Query took 0.0944 sec)
no its not
or i wouldnt have this issue

sigh

jY, im aggregating the results, its returning count()'s but for each row
so its giving me 1091 results, but i want to the total number of results, without doing a subquery
and if i can find a way to not have to do select count(*) on every query for pagination (by using this) that'd be a huge help as well

yes.. you do pagenation like i did
there should be no count(*) in your sql query
ELECT SQL_CALC_FOUND_ROWS bla1,bla2 FROM table LIMIT 0,30;
returns the first 30 rows

ya, that seems ok, and sounds right according to the documentation

SELECT FOUND_ROWS() returns the total rows that would be returned if no limit was in there

its just not working for this query

cause you have a COUNT() in there
so sql returns 1 row

its not returning 1 row

yes it is
well you are grouping
so its not gonna return them all

exactly, im grouping
so theres 1k+ rows, w/ count values in each
i need to know the 1k+ value
phpMyAdmin seems to display it fine
my assumption is they were using this method

what field type do you use for a number?

what sort of number?

a number between 1 - 10
inc decimals

inc decimals?

including decimals
1.24 etc

crazyryan1 a float ?

How about the DECIMAL field?

what sort of precision do you need?

Really don't want to use FLOATs

2 dp

decimal it is

ok

I'd really like to know a real-world use case for FLOAT/DOUBLE

what would i use for id?
int?

Sure

okay, finally

seekwill not in a db but in electronics design

Well, I mean in DB

for the decimal field type, i put the limit as 2, does that mean numbers that are 3 digits won't go in?
decimal(2,0)
probably but i dont have time to read them

Yes you do

hehe idle sh…

jY, it was phpMyAdmin messing it up

oh you were doing them in myadmin
no wonder
ya that next query needs to be the next query run to work

hey guys. say i have a table with id and file, all i know is id…how do i return a count of all records that have the same file as the id i know?
do a join on the same table?

better do a subquery if you only need one ID to start with

i only *know* 1 id, but only need a count to know if the file is in use by more than 1 record
but i dont know the file

well a self join is ok use an alias

that's what I mean, so you need something to determine the filename from the ID
and then a simple select on filename
but since you'll be inputting just a single ID to that first step, a subquery might be more efficient

http://pastebin.ca/642282 so that should work, eh?

not sure what you mean with the dots, but syntactically, yes

yea, ignore the php in there.

no, not php - the *dots* around your id in the second select

that would be escaping the string in php.
but yea, thanks for the help, i think it works.

I came up with SELECT COUNT(*) FROM table1 WHERE filename = (SELECT t2.filename FROM table1 AS t2 WHERE t2.id = yourvalue), since I think you dohn't need to alias the first table occurence in a subquery (no chance of ambiguity)

hrm, it was working….
you're probably right. i always alias all if i alias one. just habit

but you'd better make sure the text field you're selecting on matches (no whitespace, no case sensitivity etc)

they'll be identical or very different.
but it stopped working….strange…it worked the first go round.

have you tried the self join ?
you should probably test and EXPLAIN both to see how it optimises each

is there a way to see how many clients are connected to mysqld?

well, yes

why is count returning ''
shouldn't it return = 0?

could anyone tell me how to stop the 2nd query using filesort? http://nopaste.com/p/akzIgOXPF the first doesn't use it yet the 2nd is the same but without a WHERE.. i don't understand why it can't use the listname index even after reading the
order-by-optimization page on the MySQL site

megasquid SHOW GLOBAL STATUS

madriss, add table definitions to the pastebin

how do i get the table definition?

adaptr, thanks, which stat is it? Threads_connected?

best guess, yes.. I thought connections, but that turns out to be connections since startup
it's 24 on my box, which seems high to me

madriss, show create table tablename;

ok thanks, 1 mo

adaptr, alright thanks

or else perhaps
!man status

see http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html

http://nopaste.com/p/abd5BW4oz

ermm.. not that one
!man stats

Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/stats

!man global status

Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/global status

pfft

don't understand why the query with the WHERE in it uses the listname index, but the other one can't use it

probably needs a compound index

could you advise what exactly i need to index?
i think i tried "listname, rateid" and "rateid, listname" but that didnt help
i guess its not because of the JOIN, as the first query on there doesnt need filesort for some reason

madriss, how many rows in 2007caws

2394

heh 50%

hmm
actually thers only 2305
but autoincrement is higher i guess some have been deleted thats why
what u mean 50%?

Class 'mysqli' not found in

run analyse table then retry
derekl, fix your php setup

ok
analyze status Table is already up to date
still uses filesort

madriss, how many rows in the result 1?

you mean the first query? there was about 29 rows i think.. ill check
the one with the where in it

no the second
if all 2305 then use a better data set

it should be all 2305
im just wondering why it doesnt use the index
there is no WHERE in the 2nd one.. its just selecting all rows and ordering by listname, but ignores the index and uses filesort

It has to read the entire table, reading the index as well is overhead

oh right
so it's not possible? :s

no a waste of time, hence the comment about a more sensible data set eg 50% of a table

ah i see
ok that makes sense
im trying to optimise all the queries on the website.. and one of the pages allows you to view a list of everything in the table
so i guess i should not have that feature

never allow a full table slow download etc

maybe ill learn how to have it split into multiple pages

offsets

than the id's already passed.

is there a tut for how to show a whole results set on multiple pages?

google "paging"

ok thanks
thanks wench

It's been a pleasure serving you, madriss.

Is it possible to combine two columns of a result set into one column and then DISTINCT it?

makesa bit more sense now

afternoon shift has arrived, hello snoyes

ill try paging it then, thanks everyone who helped

CONCAT() to combine them, DISTINCT operates across all values in the result set anyway.
howdy archivist

orf home time

soon

#quit

hi, I've got a test server, and mysql hosting is running 10 processes, how do I make it launch fewer processes ?

can someone please tell me what is wrong with this query? http://pastebin.ca/642331

aarcane, best not to worry about the first few some are background worker threads

Probably need quotes around the email
and you should be doing some escaping of that posted value, to avoid sql injection. At least use mysql_real_escape_string()

archivist, I'd prefer to only run 5 or so instead of a full 10 though, esp since between them they're using 114 MB of RAM

the quotes didn't work

swimrr, and learn to echo your sql to see what you are doing

$query = 'SELECT email, password FROM users where email = "' . mysql_real_escape_string($_POST['password_signin']) . '"';
else echo mysql_error() . " in query " . $query;

let me try
thanks, why do I need to escape the posted value?

What if I go to your site and submit my user name as something like ' OR 1 –

I don't know - i'm new to this

thenh he could break in
is his point

figured as much - how so? what does entering ' or 1 do ?

well how do I get memory usage down ? 114M is an aweful lot of RAM to dedicate to a database for testing purposes only

Without escaping, your query becomes SELECT email, password FROM users WHERE email = '' OR 1. Which means I can pull out any email host I want.
That's not so scary. But with a little more work, I could make your query return passwords in the email field, which you print out a few lines later, and then I can log in as anybody.

huh - interesting….where can I read up more on mysql security?
I know there are lots of risks I need to be aware of

http://dev.mysql.com/doc/refman/5.0/en/security.html
although that's more server installation related

this is hardly mysql security - it's basic web site security, and law one of same: NEVER pass unescaped values to scripts

can I upload a schema and import it to an actively-working DB so that new columns / tables are added but the information that is already there stays in place?

http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php

thanks again

If you need to alter table structure, you'll need to use ALTER TABLE, rather than creating a new table. You can insert new data all day long without any problems.
xaprb's toolkit has something to help create the required alter table statements if you have two dissilimar tables, I think.
toolkit

xaprb's MySQL Toolkit (http://sourceforge.net/projects/mysqltoolkit/) includes tools to compare databases across servers (such as master to slave) and bring them back into sync, profile queries, and other handy features.

What I mean though is can I do an sql export from my new schema, sans the actual rows of data, and import it into another source where the schema is all that is used and the data stays the same?

No, the export will probably create CREATE TABLE statements, and you need to know which fields are different and create ALTER TABLE statements.

Can anyone tell me why no indexes are being used in this? http://pastie.caboo.se/84183

How do I merge the values in two mysql columns into one column?
As in
SELECT (`A`,`B`) AS `C`

concat

concat is adding the values together
well combining as strings
which i don't want

so what do you mean by merge the values?

concat

Basically
If A has 1,2,3 and B has 1,2,6
I want C to have 1,2,3,1,2,6
then using DISTINCT i get unique values

concat(a,",",b)

……..
I already said I can't use concat it's not right

whats wrong with concat?

Because it literly adds `A` to `B`

and you want?

I want two seperate values in the same column from the sources A B
not one value added together

hola!
so I've got a mediumblob field w/ a bunch of text in there (UTF8) and I want to do a case insensitive search on it.
any ideas?

HighAkujin… according to what you wanted ….. concat gives you what you want
select @a:="1,2,3",@b:="2,3,4",concat(@a,",",@b);
instr(blobfield,'what you looking for')
^^TimLaqua

that's case insensitive?

its all case insensitive

well, I did a old_text LIKE '%string%'

Can anyone tell me why no indexes are being used in this? http://pastie.caboo.se/84183

old_text is a mediumblob - and it was case sensitive

i don't know ….. try fulltext indexes

iratik, it's f/ MediaWiki / boxed. tryin not to go all reindexing fancy on them

so I have 10 processes each using 108MB of ram, how do I get that down to fewer processes ?

the instr(mediumblobfield,'string') method is case sensitive.
I even tried LCASE(medblobfield) - no dice.
or another Idea I had was the REGEX matching - is there a way to specify a REGEX pattern as case-insensitivei n MySQL?

!man pcre

Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/pcre

shoot

SELECT DISTINCT `B`.`friend_id` as `id`
How can I add the values of `A`.`friend_id` to the column `id` ?

High_Akujin, both number fields?

yes both are ints
they are origionally the same column technically

you want to add them mathematically in the output?
like… with a plus?

No, I would like to merge them
Imagine them as two arrays

how can I now how many inserts I have per day? in the whole DB?
I mean use something like show….

with a current value in id that you want to maintain?

show status

12«11threnody12» I don't really understand that question.

any resolution to Too many keys specified. Max 32 keys allowed

update table set id = concat(id, friend_id) where …

it occurs when i perform INNER JOIN
anyway around it?
different type of join maybe?

Anyone know how to do a case-insensitive substring search on a mediumblob field?

No I'm simply grabbing all the Friends of Friends of user X in a very simple table
to achieve that I do a query for all the friends of the user X and then I do INNER JOIN on the result doing the same exact query to grab all their friends
the problem is the result set needs to contain all your Friends AND Friends of Friends
if there happens to be a friend who isn't another one of your friend's Friend then they don't get put in the result
so……..
I need A.friend_id combined with B.friend_id into one column
then i use DISTINCT
I would like to achieve this with one query and not have to have an extra query for php webhosting to work with

you need to do a full (cartesian) join then

it sounds like you've taken more time finding the answer in mysql hosting than just implementing it in php

It has to be efficient, I don't want to put more load on mySQL then I have to

and you think running a full cartesian join instead of two small ph pqueries is *more efficient* ?
buh

I don't really know what a full cartesian join is…..
But I would like to learn if you have some reading material?

a huge advantage of using php is that you control the queries
!man full join

Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/full join

!man join

see http://dev.mysql.com/doc/refman/5.0/en/join.html

it can take some rerading to understand the differences, but you should be able to see that a full backreference (generating 1 x N x N results) needs a full join
full join all friend_id's on all id's and then select distinct on one id - it's a relatively simple query, but rather expensive to run

It seems rather weird that it's not possible to simply combine the already created result of two columns…….

the result is not two columns - it cannot be
if you're doing this with one table, by the way - good luck !

lol thanks

friends has to be a cross (or reference) table linked to people ids for this to work *at all* in SQL

ftp://akujin.mine.nu/friends.sql
here's the query
or what i have now atleast

that's not too interesting - what's the *data* look like ?
inner join ain't gonna cut it here, since you will only get full matches on the first ID from the second table - i.e. his friends

ftp://akujin.mine.nu/fof.png - some data

huh ? how about show create table for any tables involved ?

Is replication for the entire MySQL instance, per database, per table?

on a mysql master you can execute the command SHOW BINARY LOGS to have it assert the file sizes of binary logs on disk. I'm wondering how I can do this operation for relay logs on a slave. Anyone know?

just look in the file system?

that screenshot enough or you still want a create query?

High_Akujin what I thought - full join it is

wow, ftp links are awesome in kirc. i just opens the file in the associated application. i imagine that's a security risk, however.

that relates all freinds of X back to their friends, so you get a user * friends * friends join

yes, I can do that… but in order for me to be flexible I have to open and parse the mysql config file to find the location of those files

/var/lib/mysql in all intances ive seen.

unless there's a way to query for the location of those files from within mysql

Is replication for the entire php mysql web hosting instance, per database, per table?

well, in my setups I put my relaylogs in /mounts/mysql/[mysql instance name]/relaylogs

i'm pretty sure mysql has something for this (specifically mysl 4.2.1), but to be sure, is it possible to specify a start and ending row in sql, like return resulting rows 30-45

you can include/exclude specific dbs and tables
there's even a wildcard thing for the tables
at least, that's how it is in 5, I don't know what 4 lacks, or what new there is beyond v5

High_Akujin the table itself is already the user * friends "join" (as a crosstable), so all you need is the selected friends as users in the join, yielding all "friends of friends" for all users, and then select DISTINCT WHERE userid = "billyjoebob"

is replication setup from the mysql CLI or in the my.conf ?

but as already stated, it's incredibly expensive when compared to two separate queries

maybe you dont have to open and parse the conf file, .. cant you just run a query to get the mysql conf options?

lol wow
that would be fairly rediculous

datadir

how do i reduce the number of processes that mysql has running ? I currently have 10 processes running, I'd like to get it down to 5 if I could since it's only a test server hosting and I'm the only one connecting to it

again though i gotta complain, the damn column i want is being used in the join anyway, how the hell is it that i can't simply get that result set too without all this fuss

show variables;
show variables WHERE Variable_name='datadir';

your comment 4 ago was precisely what I suggested, I was wondering how I could do that

i see

I have relay_log = /mounts/mysql/shego-slave/relaylogs/mysql-relay.log, yet SHOW variables doesn't seem to have it in the list

mysql 5 ?

yeah
5.0.41 if my memory serves me
yeah

i dont see it either

well this is hilarious

I asked a question in the PHP room, the suggested I come in here and enquire about "views"
I wish to take the results of 3 queries, and place them into a table, so that I can select distinct from the final table

Billium, look at create view

the column B from table C with id = 1?

what kind of join do i use? do i put the conditions in the on clause or in where?

c.b and id = '1' ?

so i don't use a join?
sweet

that's an implicit join.
but yes

'…a,c..' == '…a inner join c…'

I am looking at the create view, that seems to be ok, for a 1 query result set. I wish to use 3 queries, and place the results of all three into the same table.. using a temp table perhaps? and an insert? then I can select distinct record from that temp table
or maybe an array?

hello everyone, just a quick question, it i were to perform a search for an occurance of a word within a varchar field that contains around 150 words, would i be best off using a FULLTEXT search ?

good evening

Hey guys I've got a collation error here,
Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'UNION'
Please don't tell me to upgrade I'm running 5.0.19 on this server.

I try to recover a inno database (5.0) without the main tablespace, mysql was configured with the option innodb_file_per_table, so I have all the files of the database, but now I am unable to access the tables (table not found).

I just dumped a customers database converted it to utf8 recreated and reimported the sucker, now the customer is getting an exception on their website due to this.

shocking

I'm at a loss here.

can someone point my to some documentation, how to recover from this situation
anyone a idea?

hi all!

Hi one!

what's up the_wench !
anybody know about mysql HA?
anybody know about mysql High Availability or mysql master-slave?

http://rafb.net/p/tt6WyP61.html
I would really appreciate some help

labuser, I'm sorry about your problem i don't know

:/

labuser, but i answer you very fast or not?

I have a procedure that insert a row to a table with auto_increment. I tried to set autocommit=0; start transaction; call my_procedure(…); rollback; a few times, and it seems like nothing is written to the database, but the id increases
is this how it's supposed to be, or am I doing something wrong?
ok, according to http://lists.mysql.com/mysql/178603 that's how it's supposed to be

what specifically do you need to know?

hi all, im returning some values from a query - the value of one field is CONCAT(table.field,'/', table.field) - this is in the actual table as a row. When it returns this, is it possible to get it to parse it as part of the query?

"parse it as part of the query"?

well it returns the above as data in a row, but obviously i want it to return value/value instead of CONCAT(field,'/',field)

I'm still confused… you don't want it CONCATed?

FOREIGN KEY (nid) REFERENCES news(id) ON DELETE CASCADE, in a create database, what does it mean?

what is wrong with this query? echo '$_POST['email']' $_POST['password']';

No i want it to

that's not a query.

but im not entering that in the query, its returning that as data from the table

Examples?

sorry, I meant code

##php?

perhaps the question is better suited for ##php

gotcha…thanks

In the row i have the following, id: 1, attribute: 'speed', value: CONCAT(user.up,'/',user.down)

oh
Dynamic SQL. I don't think you can do that, and it's VERY dangerous.

hm

how can it be dangerous if you can't do it?

does mysql not support a FULLTEXT index on text type data fields? it is searching every field i have specified using MATCH() except is does not seem to be searching the TEXT type field i have in my table
is there some quirck im missing? like it only indexes text types if…

I believe SQL Server can do it…
It cant
haha
It can…
No idea where that 't' came from

uhmm yeah.. it does support a fulltext index..
read..

I was finger happy

no, thumbs happy
that would be the second time you do that to me…

i have, read 4 tuts on it, i just keep thinking im missing some quirck as i have done everything exactly, and it works for every field but the text type field, strange

You must like it. You never said stop

you're right!

Hitting the 50% mark?

anyway

50 mark ?
*50%

50% threshold

nope, not even only 2 rows i am querying, and both are 100% unique, even made both entries longer than 3 words so they will be indexed

There are only 2 rows?

heh well for right now, there will be thousands later on, im just trying to get this set up and havent entered a bunch of test data yet

50% threshhold…

DELETE * FROM mac WHERE address = (’alias@domain.tld’);

is it 50% or =50%

is that wrong?

50%

i see the table in there..

!m `mac` delete syn

`mac` see http://dev.mysql.com/doc/refman/5.0/en/delete.html

thank you

Why did you put ( ) ?

right, so then if my rows are 100% unique, then it should be returning a row

Insert one more row

the example said it like that seekwill

I don't see an example…

not on that page

What page?

i m using freebsd; and i m following this page..
kewl it worked

how can i tell DISTINCT to only look at one column instead of all of them

DISTINCT applies to every selected column.

ok but is it possible to do what i just said?

perhaps you want to use GROUP BY instead

don't help me

!man grouping

see http://dev.mysql.com/doc/refman/5.0/en/operator-precedence.html

!man group

see http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html
see a href="http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html"http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html/a
see a href="a href="http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html"http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html/a"http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html"http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html/a/a

If you have two rows, A and B. You want DISTINCT A, but B has different values, which value do you want?

hey! give it back

A DISTINCT with the first possible value of B
but w/e
i know it's impossible
or annoying to do

Define "first"

"first"

Well A is just an INT and B is a timestamp

I'm having a really dumb problem with getting mysql up and running…

Which timestamp do you want?
Don't say any…

there's only one so….

There is not only one.. if there is only one, DISTINCT would work
uThere is not only one.. if there is only one, DISTINCT would work/u
And you wouldn't be here

it's my first look at sql.. anyhow, I have installed (from repositories) mysql-client and mysql-server, but it won't let me access the administrator thing.

He specifically said "DO NOT HELP ME"

$ mysqladmin -u root password mysqldata
connect to server at 'localhost' failed
'Access denied for user 'root'@'localhost' (using password: NO)'

owm-seabrok: maybe you already set a root passwd?

chadmaynard no, this was happening right off the bat

owm-seabrok: do 'mysql -uroot -p'

does anyone know if binlog replay on 5.1 shows the proper binlog positions after a crash and restart?
innodb related, I mean
in 5.0 it just shows 0 173 as the position all the time

$ mysql -uroot -p
Enter password:
Access denied for user 'root'@'localhost' (using password: YES)

reset root

See http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

If I import a mysql backup

owm-seabrok: ^^^ that's for you

will do, thanks!

do I need to have 2 times the size of the backup in free HD space?

I am trying to do a database of credit card transactions. They have an unsigned integer id, that is 23 digits long. For example: 24445007211915555639550, this is too long for a big int. whose maximum value is 18446744073709551615. Who would you guys store this integer
?

if my query is $query = 'SELECT email, password FROM users'; how do I echo the email addy?

you use php. And ##php

perhaps as a decimal with 24 digits?
or maybe a string?

if I was asked to decide, I'd try to pack it into a binary structure of just the right width
I know there is redundant/checksum information buried inside cc numbers, and I'd try to remove it and store only what I need

hmmmmmmm

get on with it

|

just use strings
IMO thats an excessive length

encrypted strings

and i used to work at a credit bureau

Is it really that bad to store one or two extra characters?

base64 encode it. That'll fool them!

or generate a new id to map to the long one
and then send them back the new id with the mapping file

ye, I willl just use strings, that is the most portable way
I have to read this stuff into other languages
and print it and sort it and stuff
lol @ Julian yes that would

seekwill - achieved what i wanted to do with multiple subqueries and UNION

How?

with multiple subqueries and UNION

yes, you said that already

(select id, username, 'some_attr' as attribute, CONCAT(val,'/',val) as value FROM table) UNION (select etc)
and so on

huh?

hi all!

Hi one!

I must have misunderstood your question

hi all! I got some newbie questions
can anyone help me out n_n?

not if you don't ask your question

xD okey
i just installed ubuntu, mysql and mysql admin, i wanna know, how to add services, users and tables? for some reason it wont do the work nor display the function

hello

its ok - it took me a while to get my head around it - i was trying to retrieve 4 unique rows of data, by using a single query - so I can send the proper accept packet via freeradius

once again, you need to formulate a clear question.

im sorry for my english, im good but not that much… so basically I just want to add services using MySQL Admin, since im going to start setting up the gaming server.

add … services?
what kind of services, exactly?

mmm not a service im sorry, a new database

!man create database

see http://dev.mysql.com/doc/refman/5.0/en/create-database.html

you should use the mysql console instead, and read that man page

what is your native language?

mine? Spanish o

me hablas espanol

haha
obviously, thumbs

well, enough to get around
chadmaynard–

lol "me hablas"

hey I make my own sentences

does someone feel like helping me with (what i thought should be) a simple query?

me isn't even a spanish word

I know that, obviously.
you should try asking your question.

asko el questiono

wrong person : )

lol

ok then
hablo un poco espanol

ok… well, im trying to inner join 4 tables and COUNT() 3 fields

muy un pococito

count the fields? # of rows * 3

it'll work with just one COUNT(), but when i try to count another field from another table, the resulting data is incorrect
well, i want like… clients.id, count(referrer.id), count(autos.id), etc

you are also grouping, correct?

yeah, i have a GROUP BY clients.id

offer him 5 pesos to show you the query.

haha

demostrarme la pregunta por favor

ok, want me to send u the query?

what about the pesos??

uso el pastebin, por favor

in here or in a pm?

RyukZilla is the Spanish speaker.

deseas tener cinco pesos

oops

Isn't there an #mysql.es

hey, I was practicing my spanish

!manes select

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 3

vea http://dev.mysql.com/doc/refman/5.0/es/select.html

pastebin isn't in /topic

I'm just trying to create a table and put 1 field in

good evening everyone

try pastebin.ca

I'm in trouble
http://pastebin.com/d109f139

ok?
ok

nvm got it

and yes, my spanish *is* terrible

the only thing I change it's the ID and there's TWO different explain..
haha

i f u

and f u i on the first one

apparently, it was good enough for p_orange. He understood it

it's really strange

\o/

I don't understand why it's not getting the same order/ number of rows

ok. here ya go: http://pastebin.com/m79665a4d

are you trying to understand the optimizer?

awesome site btw

i'm trying to understand why my query is slow

that query looks sane. How are the results not satisfactory, exactly?

i see

do you have a clue chadmaynard ?

the data is incorrect

looks like it'd be quick from the explain. How slow?

show me how. Show me data how it should be and how it's really returned.

explain is always quick ! Slow like about 3 sec
sometimes 5sec

im sure left joins have lots to do with it

id=6 : #3 sec and id=530 : #5sec
i have to deal with INNER JOIN ?

well, explain should run under a second, regardless

If I have too much idx on a table can it slows the table ?

thumbs… ok, gimmie a sec

Are there any fixes in the mysql-proxy trunk regarding 'lost connection (..) reading initial communcation packet' errors?

indexes makes the insert/update slower and disk usage higher, but it shouldn't slow down the rest

and if I shouldn't use LEFT JOIN, what i must use ?
ok thanks

well im not saying you shouldn't use left join but if you are and you aren't using it correctly the result set can grow exponentially

okay. But what's the good use ? Because my query is really simple

why did you pick the type of join that you did?

because I have to mix up all the data and the only way for me is LEFT JOIN.
I have to select all the i.id by comparing 2 dates on 2 tables

you want this data in a pm thumbs?

I think he wants you to paste it in the channel

join.intcol1 AND intcol join.intcol2"

ok, i'll give it a shot
Client Name | c_id | cars_sold | total_members
lujack | 1 | 55 | 55 |
test dealer | 2 | 2 | 2 |
Client Name | c_id | cars_sold | total_members
lujack | 1 | 11 | 8 |
test dealer | 2 | 2 | 1 |

Why do you think MySQL cannot optimize it?

i guess irc recognizes "//"

Try a pastebin then

explain in the BETWEEN case shows join type of range est. rows of 4 where it is join type ALL est. rows of 62 (number of rows in table)
i should be add that i'm self joining here

http://pastebin.com/d54a82588

What happens when you run both queries?
Any real-world perf diff?

probably not right now, but if i'm doing ALL there would be once i get a bit of data in there

There have been times where EXPLAIN was wrong.
It only estimates what it thinks it will do.
Purple Otterpops melt slower!
I was wrong in my physics somehow

how much slower?
I think you are just being ridiculous

25%

no way

It has been consistent over two trials.

stop breathing on the red ones

Silly, I don't do that. That would obviously change the defrost rate. I just hold the red ones.

lol

is there a good way to clear the query cache on just one query/table?
(on myisam)

anybody knows how to configure a mysql-replicate-client to start auto. the slavemode after a server reboot?
evertimes i reboot the slave he tells me that he doesnt find the binlog

how can I get to the Mysql console?

I am getting a syntax error when trying to run a subquery. Any idea what the problem is?

hmm. in the nested sets system, does there always need to be a "root" of the tree? if not, how do i know if a given node is a top-level node or not?

SELECT ProductName, SupplierID,
FROM northwind.Products
WHERE northwind.Products
IN (SELECT SupplierID FROM northwind.Suppliers
WHERE CompanyName IN
('Exotic Liquids', 'Grandma Kelly''s Homestead',
'Tokyo Traders'));

use a pastebin please

I am a little dense. How do I do that?

your problem is that IN compares to a column not a table

Are you referring to the first IN?

y

can someone take a look at this query for me por favor? http://pastebin.com/d14503c88

How would you change it?

how can I get to the Mysql console?

run mysql from an ssh/terminal session

okey

was that a serious question?

it says access denied and ye.. am a newbie so u can laugh about it

If c_id is the primary key for clients, the SQL seems reasonable. To explain the behavior, I'd need the data as well.
One possible answer is you aren't planning for the effect of N-M or several 1-N relationships.
One possible answer is you aren't planning for the effect of N-M or several 1-N relationships.
Try this change… COUNT(distinct autos.id) AS cars_sold, COUNT(distinct referrer.r_id) AS total_members

Xgc _ can u help me?

type mysql and press the enter key

Just a guess. Without knowing your data, I can't tell if that is correct.

it says "access denied for user xxxxxxxxx@xxxxxxxxx (using password: 'NO')"

mysql -uroot

i'll give that a shot Xgc

same error

The other approach is to use derived tables to provide the counts for autos and referrers per client.

are derived tables like stored procedures?
like, predetermined queries?
or someothing

Same error !

No. They are subqueries used on the FROM clause.

oh… ok, i heard that subqueries are not as good of an approach to most things as JOINS
oh S&*! that distinct worked!

*nod* You're welcome.

you think i might not have formatted my tables well?
i THOUGHT my stuff was properly normalized

This isn't an indication of a normalization issue. But I haven't lokoed at your schema. This is a simple JOIN misunderstanding. Here's the derived table form of the solution: http://rafb.net/p/ao2R5646.html
I did the best I could without having the schema to test against. I may have typos in that.
It also wasn't clear to me that a referrer match is guaranteed for each client.
So I used a LEFT JOIN.

Any idea what I am doing wrong here http://pastebin.com/d2bd03c9b ?

is ) a special character that has to be escaped?

you referring to me?

No.
The first thing you did wrong was you forgot to ask a clear question. What behavior dno't you like? What error did you see?

I don't think so. But I am a newbie. Only thing that I know has to be escaped it the apostrophe

Single and double quotes are special.

I am getting a syntax error. It says you have an error in yoru SQL syntax; check the manual that corresponds to yoru MySQL server version for the right syntax to use near 'ProductID' FROM northwind.Products WHERE SupplierID IN (SELECT SupplierID 'at line 1

Not possible.
Post the exact SQL and the exact error. Copy/paste. Don't retype it.
The SQL you posted can't produce that error.

The error message is posted here http://pastebin.com/m5c2a0d65

Now post the real/exact SQL.
The SQL you posted did not produce that error.

hi
guys, tell me how I can change a table field definition

I figured out the problem. It was an unneeded comma at the very end of the first line. It now works.

ALTER TABLE …;

Hello. May I know is there a function I can do on MySQL to monitor if there are any queries that are being executed for longer than it should be?

thanks Xgc

I would love to have that monitoring so that I can optimize my app.

See: Slow query log.

hello someone in ##php told me to ask here for help on how to grant my user access using GRANT
how would I do that

Xgc, cool. But may I know where the log is?

I think I will learn MySQL again

http://dev.mysql.com/doc/refman/5.0/en/grant.html

thanks

Xgc, found it. thanks

You have to enable it. The location is configurable. Could be in /var/log/mysql or the mysql datadir.

Thanks Xgc.

is there any way I can list all the users on my SQL server
with a query or with phpmyadmin

Hello all

I'm using PHP to upload values such as '0255' to a mysql database into an value in the table. But, the info only comes through as 255. How do I make it keep the 0?

You shouldn't let people stand on your server.

?
i dont understand

Do you want those connected or users that have been granted access?

granted access
i want to be able to view all the names of the users

SELECT * FROM mysql.user;

oh

SELECT user FROM mysql.user;

#1142 - SELECT command denied to user 'rave_'@'mysqladmin2.secureserver.net' for table 'user'

Find a better user that has access to that information.

the only user
darn
oh its because my webhost uses one server to manage all the users

anyone here an expert at creating queries that utilize index optimally

(not a dedicated server)
hope that doesn't affect me in the future

You're in the right place. 428 people. Many mysql developers/employees and other experts. Just ask and hope someone can offer advice.

xgc I have a site that is basically on its knees at least twice a day now.

oreonix are you sure the db is the bottlekneck?

ChrisPartridge, it feels like it
23 queries per second
when i type status in the console
i have a data table with a little over 2 million rows in it
and I dont think my indexes are doing anything for me
data_id | dtstamp | listeners | song | stream_id | is my structure
actually
let me put this in paste bin
brb

!man create table

see http://dev.mysql.com/doc/refman/5.0/en/create-table.html

http://www.pastebin.ca/642609

hehe. http://dev.mysql.com/doc/ has broken links to the documentation….

I am not if my index helps at all

Which links are broken?

the ones that are the html online

sure8
sure*
man i am having problems typing tonight
:-D

they show up as https://docsrva.mysql.com/docs-translations/en/refman-5.0/html/manual.html here
^^

hmm

So I have 16GB of RAM on this Sun Fire 4600. And I've given MySQL really high settings for key_buffer, sort buffers, etc.
But when I run top, MySQL shows only a few hundred megabytes being used, and most of the memory is coming in the VIRT column
the real mem is like 650M, and VIRT is 2900M

dont set key_buffer higher than 4G (4096MB)

does that mean MySQL is swapping out of disk?
key_buffer is like 2GB

im not positive but i think if the ram isnt actually used yet linux wont actually allocate it out of real memory, i've noticed that before, feel free to look into that properly.
so it show sup as virt
but thats a mostly guess from me

wonder how you can find out if your programming is being swapped to disk

does "free" show swap being used? ?

yeah a bit
28228
Around 27M
So I configured top to show SWAP, and it shows a 2.2GB SWAP

interesing. mine shows 1G
(yet i've only got 2M swap used)

boohoo, see how many blocks are used versus not used.. show global status like 'key%';

83240 vs. not used : 1631496

so it's saying it aint really using all your blocks anyways, lower the key_buffer_size variable and increase other ones
I've noticed that too about my key_buffer_size, rarely ever uses the full amount
or even 1/2
maybe cause it's accessing so many different tables and only caches what it decides will be used in the future.. who knows

I wonder if it'll help to increase the query_cache_size to like say 4GB

boohoo, don't!
each update will invalidate all those queries… heh

k, any reason why?
hmmm…. well it's mostly static

someone wrote about a bug with query cache recently anyways .. i can't remember what it was but it was just like 1-2 nights ago i read it
if it's mostly static, do like 128MB
and then monitor how much is used vs how much is free.. and then increase it if you need oto
too

hmmm yeah i guess no point wasting it

boohoo, for me .. I'll use a lot of memory tables .. mysql will show like 1.8GB of ram used but really it's not…

At this point I'm repairing tables worth 23.1GB

also I run with swapiness at like 10
ohh.. myisam_key_buffer_size=1800*1024*1024;
is it 32 or 64?
err.. is it myisam_sort_file_size…
one of those … will help with fixing the tables

myisam_key_buffer_size=1800*1024*1024 ? is that KB?

Hello, while EXPLAIN shows how a statement is process, may I know how can I know the time it takes to process a statement?
a query, i mean

buffer_size is like 128M for me

boohoo, sec lemme see which it is
set global myisam_sort_buffer_size = 1800MB ;
it won't recognize the MB .. so you gotta do :
set global myisam_sort_buffer_size = 1800 * 1024 *1024;
reconnect, repair table
should go faster
there may be a few more variables that help but myisam_sort_buffer_size helps bigtime

shit, 1800MB?

May I know what is the best way to index a DATETIME column?
I tried indexing it but the query still doesn't use it

uzyn, read the part of the manual that says "how mysql uses indexes"
that may reveal the solution … I don't know offhand tho

ok Tapout. thanks

uzyn, you get the time of the query at the end of the execution.. that's the only way

got it. thanks Tapout.
i thought there's an easier way by using some built-in mysql timer or something

myisam_sort_buffer_size should be like in two digits of Memory

why?

well I wonder what 1800MB will achieve
1800MB per thread?

Hello friends
Y need some help

whoever broke the mysql.com/doc … forcing it to go to the intranet site.. fix it pls
boohoo, hopefully this pastes properly
myisam_sort_buffer_size
The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.
http://forums.devshed.com/mysql-help-4/slow-to-add-an-index-186951.html

Friends i need your help urgent please :'(

uggh.. your urgent isn't anybody elses
just ask your question

Thanks
Modifing my file my.cnf
Y modify the size of the Innodb log file
When i did that my tables where corrupted
A messages of erroneous data in .frm files appear
I go to the help in http://dev.mysql.com/doc/mysql/en/repair.html
Creating a new BD with the exact tables schema of my corrupted tables

you're right

But my .MYD files are deleted
Is there any possibility to restore my information
?

no
if you hav myisam tables and deleted the .myd your data was deleted
if innodb then you don't have .myd

The engine is Innodb
But when i follow the procedure moving the new .frm and .MYI files to the old ones and execute an REPAIR TABLE and follow an SELECT
The table is empty

innodb stores data in ibdata files

CREATE TABLE wil_aliases (
alias_idnr bigint(21) NOT NULL auto_increment,
alias varchar(100) NOT NULL default '',
deliver_to varchar(250) NOT NULL default '',
client_idnr bigint(21) NOT NULL default '0',
PRIMARY KEY (alias_idnr),
INDEX alias_index (alias),
INDEX client_idnr_index (client_idnr)
) ENGINE InnoDB DEFAULT CHARSET=utf8;
This is the SQL CREATE of my tables

plz, use pastebin
:P

Any suggestion?

i know this is kind of a stupid question, but is there a way to control what number an auto-incremented field starts with?

yep
ALTER TABLE t2 AUTO_INCREMENT = value;

oh
does that effect performance at all starting with a larger index?

no

sounds cool to me

you can do the same with CREATE TABLE. It goes at the end with ENGINE IIRC

oh
thanks for the help chadmaynard

Hi Greetings!
If i take the backup of .frm, .MYD and .MYI … will I be able to restore it to the DB or tables later ?

Friends thanks a lot
"Googling" i found the solution
And is more easy that i think

yes assuming you lock everything or stop the mysqld process while you copy the backup files

Thanks

yeah I lock all the tables and once copying is done I unlock

sure then

thank you )
*wrong smiley forgive me for that!

lol

Possibly stupid question, how can I insert an .sql file into a running database?

what OS?

Debian Etch.

cartesian mysql -uUSERNAME [-pPASSWORD] DATABASE_NAME /path/to/sql/file.sql

Thank you.
hmm, I'm not sure if openvcpd wants me to make the database or no. How would I make the database first and set a password?

CREATE DATABASE name;

Heh, thanks.

GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost' IDENTIFIED BY 'password';

funny thing…
Lost connection and the transaction is committed anyways

nils_ you must have 5.0.2_magic

indeed
only that it is 4.1.11

hey all… hopefully a real quick one, im looking for a client application i can run here to edit my servers mysql database direct.. any bright ideas?
windows, preferably open source

sqlyog
not opensource maybe, but free

good enough i suppose
im unlikely to actually change anything in the source, i just like to use OS where possible
wow.. this thing looks like a way bigger hammer than i was expecting

that's what she said!

Anyone who consults or whatever. What do you use to generate invoices? Any decent tools/web apps out there?

I use to con people, and I use to insult people, but not in combination

invoices? quickbooks
nils_++
good humor there

for the 1 to 10 invoices a month I use openoffice
especially for the small business there doesn't seem to be any software which makes life easier

hmmm.

hmm I think it's time to switch to mysql 5 soon

I've never used it, but I know MS is offering Microsoft Accounting Express 2007 - http://office.microsoft.com/en-us/accountingexpress/FX101729681033.aspx

I thought Microsoft only builds Mouses and Keyboards?
alright, that was pretty dumb. Just