what are you trying to achieve, exactly?

chadmaynard, thumbs..lemme try

they import but the content managemennt system doesn't see the blob data

is the data identical?

yes

then perhaps the table definition is different?
there has not been many changes from 4.0 to 4.1 in that regard.

in any case, i can't upgrade to another version of mysql. i'll have less problems if my local environment matches the server.
i'll try 4.1 if I have to. I'm expecting it to fail at the server however.

upgrade the server

can't
its the isp's not mine

tell them to upgrade

i've figured out why. i use antoerh table for update and it works.

heh that'll go down well.

*another
i can't use the saem table for UPDATE and SELECT
chadmaynard thumbs ^

that entirely depends on how you do it. You can do it, as a matter of fact.

I don't believe you can update a table that you are simultaneously doing a select on."

i.e. UPDATE tbl1 SET field1 = field2 WHERE field3 …
he's off, yes

what is the function for median?

threnody i didn't got your point

you should run ntpdate
!man median

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

median?

!man ntpdate

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

as opposed to average?

it's not a mysql function.

!man functions group by

median, is the midpoint of a dataset. 1/2 the data is above the median, 1/2 is below

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

!man aggregate

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

stupid

!man math functions

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

it's "functions for use with group by" in the mysql hosting manual.

we all failed, miserably.

aka aggregates

thumbs so..as threnody said…i can't update it..
i would use two queries …

but you tried hard

nah, wench is stupid.
and it IS a mysql function.

oh I believe you.

the_wench hates me
chadmaynard++

chadmaynard–

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

IF you use a subquery that uses the same table, yes, that will not work.
there are workarounds, however.

why
use VIEW thing?

hmm actually, median is not in there. blow me down…

no, you could design the query properly, DavidHKMrPowers

the updates to my main table are mostly updates, with a few inserts

thumbs ok..lemme try..

and I do have a timestamp in one of the columns

of course, you would need to define what you're attempting to do, clearly.

is there something better than replication?

better?

index that col. then you can just update or replace to another table every # minutes. no need for repl.

I think I might have found the median answer here
http://dev.mysql.com/doc/connector/j/en/group-by-functions.html
I will resist the urg to paste the code in chanel, though it is only a few lines

basically, REPLACE INTO tbl2 SELECT * FROM tbl1 WHERE ts = "lasttime"

oh arejn17, I see, you pointed me there already
lol

simplicity is better.

oh, I see your comments, arjenAU, the median code is the first user comment.

and using tools for what they're good at is, too. what you need is not repl, IMHO.

it isn't a predefined function, but can be calculated easily.

indeed. it's a bit sucky you can't yet create aggregate stored functions.

that seems quite a bit easier

if you fancy a bit of C coding, you can however make an aggregate UDF
quite. hence me asking you to take a step back

so basically whenever I want to update my results, I can just call the replace into command?
perfect.

yeps. and the rest of the itme the secondary table is "static" and thus the query cache won't be flushed. easy. however, there are better ways still

I guess I could couldn't I. If I find myself doing this alot, I will do the C programming route.

what are the other options?

consider not doing this inside mysql, but from the app perspective. use caching on the app side, for instance with memcached. then just use stale info for 10 minutes then invalidate and re-do the query.
depends on the complexity of the queries, of course.
RDBMS task is to execute queries, not decide whether you want stale info
hi ebergen

you lost me on the last statement

ha, that code snipper is *SLOW* lol. It is still running

see how it works, and optimize appropriately.

hmmmm, I think I should have run that query with the nohup comman, incase I loose my internet connection. It is still running lol

screen it.

I am abusing the system, there are about 1,000,000 records

mysql's task is to execute queries and provide upto-date info. it doesn't care about the fact that you are happy with 10 minute old info. so, that leads to the issue of "let's not ask the db at all" for caching. in this case.
and you need to recalculate this all the time? probably not.

no, only on very rare occassions, at this point

hi arjenAU

Hi

is ther esome sort of cacheing mechanism in php/apache meant for situations like this?

Can i recover a mysql DB if i only have the /var/lib/mysql of the previous install?

you could cache php, albeit that would yield questionable results.

that formula is fairly inefficient

given the dynamic nature of php requests.

memcached, mcache, xcache, apc.

however, if the php webhosting output is the same most of the time, it could be beneficial.

flawed premise. php requests are not that dynamc. and many page components are often quite static. it's just a matter of designing the app properly. with smarty you can have parts of your page update only every 10 minutes and use a cached version the rest of the time.
there is no such thing as "the output". a page contains different components. don't see it as one thing.

sup arjenAU

hi. dunno. sup?
probably, depending on whether you had innodb tables, and where the my.cnf file is.

I often backup mysql db — just physical file copy over — when it is myisam type — is it correct ? — because i find it is the quickest way ?

just copying? no won't be correct. they'd be corrupted.
not making backups is even quicker, and about as reliable.

i think is myisam type (i installed it by default with the ubuntu package so i think is myisam)

but for the last 3-4 years, never wrong. why ?
i acutally just copy over the data folder

because you're copying potential half-written rows and such. corrupted is a very broad topic. you may not notice but some of your rows may contain effective garbage.
use mysqlhotcopy or a similar tool if you wish to go this way.
feel free to argue. i'm just telling you like it is.

if you stopped the mysqld server process first, you'd be OK. Best to use mysqldump or mysqlhotcopy.

mysqlhotcopy to make the copy or to recover the folder?

actually i m interest to learn today — because i found out this morning that i can not copy over the innodb — so i try to learn backup script
if i would like to backup whole MyDB to file ABC.sql — howto ?
i never use mysqldump or … before
the documentation seems very …
lengthy
I try to : dump DB1 to file.txt ; drop table ; create table ; download data for each table — howto ? thanks !

that's because there are lots of options and considerations. if you're looking at me for giving you a one-line magic answer, it won't happen.

yes, too many option - i m lost — so actually i m looking for one-line script here
come on

you're making a grand effort in not appreciating what i'm actually telling you.

sometime too powerful products — actually does not work for the dummy at all

only for those who brand themselves dummies. it kinda disfocuses the mind.

you are a great guy

my status is rather irrelevant to the issue and the answer.
your question is equivalent to "how to do a full service to a car, I've got 3 minutes and you need to teach me in one sentence". it's unrealistic to the point of delusional.
it's no issue of willingness of the part of the helper

is it possible to setup a master-master replication situation with more than just two servers ? All the 'howtos' i can find only talk about two servers

are you offering car help now?

hope you still glad
I guess this is the reason, why i backup db — always use physical copy the data folder — just do not bother the too-many-ed options — but it does not work today for the innoDB

basic backup and recovery techniques and tools can be taught in about a day, but not from the newbie bais of course.

so i m here looking for some answer - that's all - hope you still can drink your coffee happily

es. and your myisam backups will also be part garbage, even if you never noticed. you still appear to be delusional on that front even though various people here have told you otherwise.

alright - better do some study today

for the last time. it's not about me or anyone's willingness to help. it's about the subject matter being to broad for a short answer. if you are unwilling to take that as a fact fine.
quite.

is mysqldump is the right bluh?

it's a good bluh yes

what is the restore command ? then
from the dump

…. mysqldump?
or mysql
reading both manpages would be beneficial.

loadfile ?

nah, they're too long.
have you considered that the itme you are spending here making up nonsense just distracts from the timeyou could have spent reading up and actually understanding the subject?

LOAD DATA INFILE — found it

hi domas - you're early

hey
I'm in Asia

yep and it's the wrong answer. you're just blundering in the dark rather than actually focusing on learning.

its pretty late for the timezone

oh, wherabouts?

;-)
Taipei

seems like a tough teacher

ah. ye that's a few hrs west of here. indeed fairly late. it's nearly noon here.

10am here

oh youre entirely free to waste your time.

very strict

no, just no bullshit.
randomly sprouting answers doe snot provide a correct answer, or understnading.
sounds about right. same zone as singapore I think
so what are you up to in Taipei?

My C++ teacher never answered my questions - he always to tell me to cout — and i found out he is one of the greatest teachers i had
bMy C++ teacher never answered my questions - he always to tell me to cout — and i found out he is one of the greatest teachers i had/b

at least as hong kong

thanks arjenAU

you're welcome
messy but functional

wikimania

ah. didn't know it was there.

it seems that the magic timestamp still has to be first in the table

ah yes, we're continent-hopping bunch
you specify it nowadays

no, from 4.1 onwards you can define it for anther. as long as there is only one.

domas didn't you just go to bed?

mmm, 8 hours ago, yes

he's in taipei.

hm I guess that was 8 hours

I m dummy - but not really - my project will make your eyes wide

few things do.

:-)

?

i m the creator of php mysql web hosting sidu - do not always treat yourself as God - the dummy can also be great , sometimes

? to what?
first wikimania was in frankfurt, then boston, now taipei

hehe

next one will be in australia!

oh that's an excellent tool, by the looks of it. well done.
where and when?

well - i confirm myself as dummy - never heard of "Load file "

dunno, just not that many unvisited continents remain

hehe - bye - hope not wasted your feelings

you are a dummy
oh

–i-am

I merely try to focus people's minds a bit. saves time. if you wish to regard that as godlike condescention, you're missing the point. but people are free to not take advice and waste their own time. just not others.
ohwell. no hope there apparently.
have you looked at mysql sidu? I'm wondering what it does that phpmyadmin does not

not any further than reading the top paragraph on the home page

the grammar appears to be broken, yes
I'm not sure I would totally trust that software, either.

well the guy is from somewhere in asia.
i'm sure he's better in english than you are in his native language.
just like my english is likely moderately better than your dutch

fun part is that current_timestamp is really fast

hi fl
hi flupps|JP even
still in JP eh?

hi arjenAU!
for another day

hey flupps

heyh domas
how's TW?

howdy, i'm readind about the text types, and the manual mentions tinytext, but i can't seem to find anything describing what it is in the manual, can someone point me to a refernce?

Asia!
my first time here, so I have some sort of culture shock

yeah, I know what you mean
noone speaks English, etc

riiight

you think that you'll always find *someone*, but….

foreigners speak english though
and expats

yeah

that helps, of course

what specificly are you looking for?

just general description.. but i think i found it in the storage-requiements sectiond, cheers

daemon serving sections!

would it be uhh a good optimization to index a column you are sorting by?

depends on the where clause and groupin

when would it be good optimization?
lol
SELECT * FROM table WHERE field = blah ORDER BY otherfield DESC

if there' either no where clause or the where selects on the same column, and same for the gropuing. because the first selective clause decides which index might be used. usually that's the where.

thats essentially it
if indexing otherfield will make it quicker

exactly. in the above, an index on field might be used, not on otherfield.
no.
it just wastes insert time and never uses the index for this.

ah

the reason is smple. by the time the server gets to the order by clause, the data is already retrieved. you're no longer dealing with the original table.
the data is retrieved from the table based on field, so indexing that makes sense.
if field is a single value, with multiple otherfields, then you culd do an index on (field,otherfield) and then it would be true.

heh
kk

however, if you'd have multiple field values in your result set, that doesn't fly.

hm

because otherfield would only have order WITHIN each field value. not globally.

ok
thanks!

i want to do a COUNT() of how many files an IP has downloaded the same file, and how many times the same IP has downloaded different files. is this possible in a single query?

can you give an example of resulting rowset?

group by

resulting rowset?
playing with that atm

result

you mean an export?

hello

http://pastebin.ca/638813 (Yes, I know it looks horrible)

just out of curiousity would you consider 5.1 stable enough for a development environment?
or would everyone still suggest 4 or 5.0?

you said development environment

— sorry not a version whore

so you can take your pick

Fushuing, you pasted the structure and data you have. can you paste what you expected to receive from the query?

stable environment

gah, i also pasted the sql host query there
it should return [IPADDRESS] [no. Samefile] [no. Differentfile]
i got the differentfile good up till now

oh i see
use distinct

distinct?

SELECT ipaddress, COUNT( ipaddress ) AS different, COUNT( distinct(file_id) ) AS same

thank you

np

eeeh…it still returns the same

yea that wont work. distinct is a row basis
you will need to subquery for one or the other
doesn't matter which

how does one subquery?
just plunge another select statement in count()"?

http://flickr.com/photos/brionv/940885077/

hello?

err… right, maybe union is your solution

no, it's not

UNIONISTS HERE

that's for joining tables if i'm not mistaken

why do you need it in one query anyway

Your requirement is still not clear, or you'd probably have an answer already.

Look, it's xgc

For instance, it's not clear what you mean by "no. Samefile". If you could clarify, that would help.
Even if you can't provide a clear english description, it could help to show sample data and the exact output you expect.

my solution with distinct worked
your dataset doesn't have enough data for dispersion

The distinct part of the question isn't the issue, for me. That's was understandable.

he wants to have per dedicated ip hosting address to show total hits and for same files

per ip per file?
SELECT COUNT(*), file, ip … GROUP BY ip, file;

no just number of total files of same name per ip

and then just add the total distinct count as a derived table, if you really needs that.

so for example if IP hits same file twice it would show 1
correct me if i'm wrong

Well, that's the open question, since he didn't seem to ask for that. Is Samefile some specific column in his table?

there is a file_id

i'll let him answer that when he's back
i'm back to my work

I guess I don't know what "same name per ip" means, unless that's per ip, per file.

number of unique file names per ip - sounds better?

he wants to know total the total number of downloads and total number neglecting duplicates per ip address

That's the distinct part of the question.
That's fine. The "same" word wasn't clear.

it would just count how many times it would have hit the same file

i just thought there was some confusion. i am barely listening if we were trying to prove a point. sorry

See? He seems to be asking for a count per file per ip.

that's why i asked him to give example of what he expects as results…
bthat's why i asked him to give example of what he expects as results…/b

There are too may ways to read your question. You may not realize that.

i know

It's called being ambiguous.

fill your table with simple dummy data and experiment with the query i pasted, see if it fits

Show some data and the associated output you would expect.

i did a pastebin a bit ago with that

i think i'm not able to help in this specific case

With exact input and expected output?
I only see the data, table and attempted sql.

bwergh, perhaps i just need some sleep

Usually, in this channel, more than 90% of the problem involves asking a clear/complete question. After that the solution is usually trivial.

i have a PHP script which does exactly what i want, and i wanted to condense it into only SQL

clarity of one's mind is a problem not only in this channel
sleep always helps me - really, try tomorrow

script is 50 lines long

#
SELECT ipaddress, COUNT( ipaddress ) AS different, COUNT( file_id ) AS same
#
FROM `downloads`
#
GROUP BY ipaddress
sorry
trying this IRC soft.. forgot it's got only 1 line at the time…

If you've written a program that already does this, providing a complete/concise description of the expected behavior should be simple.

somehow it doesn't

get some sleep, and try this query with your real data: SELECT ipaddress, COUNT(*) AS different, COUNT( distinct(file_id) ) AS same FROM `downloads` GROUP BY ipaddress
i think it will do what you want

different == same in that querry

What's the ballpark figure for how long it should take to repair a one-gigabyte MyISAM table?

the 50 rows of data you pasted is not enough to see the difference

it actually should be

I guess it depends on what's wrong.

IP -104795347 mainly

But still, I left it running for four hours before giving up and planning on restoring from backup.

insert into downloads (ipaddress, file_id) values (1,1),(1,2),(1,1)

urgh, can barely stay awake @.@
….
wtf

you have an error in your syntax near 'wtf', what the fsck are you on about?

for some reason the production machine it returns different == same
but on my development machines with the exact same data, it returns different things in each column @.@

you had a great idea - go to sleep

this makes no sense!

in the morning it will
believe me

exact same data, but on this machine i get different results than from the other machine

it's known as PEBCAK

.

hey folks
what are the implications of not setting a limit on a string based column? masses of badly indexed data (potentionally)

Uh
All string-based columns have limits

of course

how do i get just the number of rows of a select?

And those that have "high" limits you have to set a limit on the index

i think i found it…

select count(*) where x = y;

Usually there is a function mysql_num_rows()

same == different when the ip didn't download the same file twice

thats PHP…

No, that's C API
Which PHP uses.
And many other languages

msyql_num_rows(query) right?

Define query

heh
no matter. I won't bother to read the docs for something I'll never use

i want to do it directly in mysql,

read my suggestion then

oh okay thanks

so if I don't index a string based column.. and set no limit
the only caveat is db size?

You have to be more specific on what kind of string-based column you're making
But generally, yes.

any idea why it returns same == different when the IP didn't download the same file twice?

Not sure why you're asking

just looking at some legacy schema
wondering if I'll bother to put in limits where they should be
the models suggest they exist, yet they don't on the schema

Hello all - I need to know how to optimize a web based application - I have a huge database and it keeps getting bigger

hello
Is is possible with mysql full index search to search for example for the word mama and mysql match it with maman ?
it search for word no subwords or something alike ?

if i want "pager" always set in mysql, where do i set this
oh… wait nvm.. that might interfere with my DBI/php?

Hello there
Any OSX mySQL users?

yes

Have you ever gotten a cannot connect through mysock? Let me find the exact error message
Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

I believe that's the first time we've ever seen that.
!perror 2

No such file or directory

I see, so somehow I need to create a pipe? hmm
What am I missing I wonder

lol

The server creates that on startup, if you've configured it to do that.
The location is also configuable, but there's also a built-in default.
Sometimes that simply means your server isn't running. There's tons of information on this sort of thing on the net.

It comes up when I do ./mysql
Where is the config file that I need to change to create that? maybe the permissions are wrong

Hi people!
I need help with mysql, the problem is that I didn't dump my database but have the database files.
Is there a way to import the files into a new installation?

Possibly. Backup the files. Copy them into the datadir of your new server and start it. Cross your fingers.

Do you think it would work?
I will give it a try, I hope…..

Absolutely. There's no chance of failure, especially if the old files are from version 3.x and the new server is 5.2. Should be no problem at all. I'm sure there are no internal data structure changes over the past 10 years.

you are such a good citizen. Helping these folks into the wee hours of the mornin

The alternative is to reinstall the original server version and then follow the upgrade notes from start version to destination version.

Guys, where do I change SQL so that it can creat the '/tmp/mysql.sock' (2)
file

you have to change de macro #SOCK_FILE_PATH in the Sock.h file and recompile the all thing with the right compiler
here

should I overwrite the whole content of my new var directory?
Xgc?

thanks

How to LIMIT a SELECT query to exactly the first half of the possible rows?

that means, the old complete folder structure in the new installations VAR directory?

the mysql way of life

count

haha, good stuff

you should be helping them instead
heh

What is the SQL default socket

i want to chop off the last 4 character of a string. how do i do this in mysql?

LEFT(string, LEN(string) - 4)

LIMIT 0,(COUNT(*)/2) ?

ah thanks

http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html

doesn't seem to work to me

tonyacunar:

you might need an IF if the string is 4 chars
i.e.
CASE WHEN LEN(string) 4 LEFT(string, LEN(string) - 4) ELSE string END
and so on.

oh okay. there is no "chop; chop; chop; chop;" equivalent?

no.

Wird, mysql works, only if i start it from the control panel

DCC SEND aiufbauibauiebifkbsifjbsefgijb 0 0 0

you need to determine how long the string actually is, and remove the last characters, from the end, while preserving the rest.
….control panel?

can you elaborate?

OSX

ah, I am not familiar with OSX enough to diagnose your issue.

SELECT * FROM `player` LIMIT 0,(SELECT COUNT(*)/2 FROM `player`); - gives an SQL syntax error

You can't use a subquery like that.

well, how to do what I want to, then?

substring can work from the right, with negative lengths

You don't use limit for that, unless you want to dynamically construct the SQL.

what about "SELECT * FROM `player` LIMIT 0,@var;"? this gives a syntax error too

good point

and rtrim would remove right trailing spaces, so it is more a chomp

it might be simpler than mine.

so I have to do dynamic SQL, you say, Xgc ?

If you insist on using LIMIT, yes.

for a dynamic limit clause you have to build sql in the app or use something like dynamic sql
http://mysqldump.azundris.com, search for procedure execute

Otherwise, you can use aggregation to determine which records to output.

wow, this is cool
can you explain what aggregation is?

It's a way to use groups (GROUP BY) to determine the median primary key (or other ranking mechanism).

I'll stick with dynamic SQL
thanks

heh
group by is a lot easier than that you're trying to write

We extract detail from groups via aggregation, like AVG(field) -or- COUNT(field).
Without direct support for RANK or DENSE_RANK, this procedure can be expensive.

select substring("Aluminum Foil", -length("Aluminum Foil"), length("Aluminum Foil") - 5); Produces "Aluminum"

damn

Look at the first user comment: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
If you're thinking clearly (and can understand what's going on), that'll provide the hint you need.

oouch, that's too much for me

hi guys
I have a problem

thanks Xgc, I'm not so advanced, and I've done it with dynamic SQL already, thanks again

I have a replicated environment with MySQL 4 and I don't have more space on my disk
Any problem if I remove binary logs? What I need to in order to don't crash replication ?

mauzetier!

is there everything (flow control, i/o) to make mysql a scripting language? how would a mysql script take a $ARGV[0] (command line arg)

use stored procedures
that will require version 5.x as well

hi
what is the main difference between the MySQL v4.1 line and the v5 line?

about .9

what do you mean?

you can google for the RELEASE NOTES for mysql 5

i dont suppose there's an undo function in mysql ! !!!

you can roll back a transaction
if you used a commit()

i just did some bad renaming two queries ago
unfortunately, it wasnt stored like that, i just typed it in, and it modified the db

how can you rollback if you committed?

errr I mistyped it… too tired … sorry
I better go to bed

hey megaspaz

test
nuts

g'morn jbalint
get your backup from yesterday and use the binlogs to do point-in-time recovery

hey weigon, made it back ok?

hoi

I've just reinstalled windows…
and I have Apache, PHP, ActivePerl, and my web directory on one partion

hi all
people

I reinstalled apache, and it seems that apache and php are working.
but for some reason, MySQL isn't.
even though I tried reinstalling it…

hi apollonx

what difference between (in stored procedures):
DECLARE a INT;
SET a=5;
SELECT a;
*** and ***
SET @a=5;
SELECT @a;

@a is a session variable.

when im using @var, i do not need to declare them, so what DECLARE do?

it is stored in the session and is global across everything you do in that session

Isotopp, so if i will use @var in my procedure, then after procedure finish i still can see @var?

yes

Isotopp, thanks.

this is of course a no-no. use an INOUT parameter

I spent 10hrs in the London Heathrow (LHR) wait for a "working" plane

Isotopp, ok, i just didn't know what means '@var'.

and changes the runtime config)

weigon, ok.

(well, @@global.var is, @@session.var is a runtime config change local to your session, and @@var is @@session.var by default)

http://forums.mysql.com/read.php?10,164780,164780#msg-164780

the joys of airports

hello,
i have a cost table ProjectCost(projectId,PhaseId,FundTypeId,CurrentCost,Year)
i want to make a pivot table with ProjectId, TotalCostYear1, TotalCostyear2,TotalCostYear3 etcs
how can i do that?
thanks

Use Excel

I'm supposed to add a few functions to an application. Today all sql-functions are in one huge php-class, so I've been thinking of migrating to stored procedures. after reading the book MySQL Stored Procedure programming from cover to cover, I'm still not sure wether I should go stored or stay
with the current design… any opinions?

Depends on the future of the product, what kind of requirements it has, etc.
If your applicaiton will always be in PHP, perhaps the large class will do fine. If you want to make it so other languages can access it, SP's might work.
Or perhaps you don't want to be locked in MySQL and want portability to Oracle or PG.

it will most likely always be php, running on a samp-platform (s=solaris). maybe I should try using stored procedures for these additions to see if it works well..

Truncate ran for over 2 hours… before we ended up terminating the process. Which we continually got lockout time exceeded. Why would truncate take so long to run? Can you force TRUNCATE to only run for a certain period of time and terminate?
As in standard selects?

InnoDB eh?
IIRC, TRUNCATE in InnoDB is still a DELETE

if you got lock wait timeout exceeded then truncate wasn't doing anything, it was waiting for someone else to release a table lock

is there a way to search a utf8 table for only non-latin strings?

There should be row level locking on the table, I would have assumed TRUNCATE to have caused the table lock?
Is that incorrect?

truncate was waiting to acquire a table lock which means it could have been waiting on someone elses table lock or intention lock

Ok, thanks for that. Really helpful this IRC channel .

what else do you want?

I want the day off work .

go find the other connection that is holding the locks your truncate needs

Whats the best way to search for that, just sit and watch the processlist ?

whats the opposite of IS NULL

hi

what

!man comparison

see http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html

guys, i have a table with a index. i get thru PHP an pair (index, score). now i need to "INNER JOIN" the external data, adding the new Store column. how can i do this?

howdy, i have a relatively long query to get nested sets, but i'm having trouble sorting the thread heads by specific conditions (title,date,author)
i'm thinking i might need to do that in a subquery, but i'm not quite sure
http://rafb.net/p/D52qS033.html
i've tried ordering by various combinations of top,parent,node.xar_title

grrrr
What am I missing here…???
[root@smtp tmp]# ls -la plain_emails.txt
7
[root@smtp tmp]# mysqlimport -ppassword support /tmp/plain_emails.txt
Error: Can't get stat of '/tmp/plain_emails.txt' (Errcode: 13), when using table: plain_emails
Hmm… looks like I had to copy file into datadir… weird since it worked before…
(before, as in, on the same file a few minutes ago)
I swear someone is messing with me

hey guys
how's 5.1 looking? =O

Looking great

any guesses on release date? =P

Tomorrow

w00t!
;-p

It's has been released already

GA release
or at least
RC

It'll be sooner if you start reporting any bugs you find

haha well
I'm afraid to move to it
for production

Why?
Well, of course.

Help!
phpMyAdmin tried to connect to the MySQL server, and the server rejected the connection. You should check the host, username and password in config.inc.php and make sure that they correspond to the information given by the administrator of the MySQL server.
Documentation
#1045 - Access denied for user 'root'@'localhost' (using password: NO)

You should always test it in a dev environment even if they did label it production
You need to provide a password

How?
It doesn't prompt me

Ask phpMA

Where to I enter it?

phpmyadmin.net
From the command line, you can use "mysql -u root -p" and it'll prompt you for a password

YAY!
Thanks heaps man.

np

seekwill

See will

hehe seekwill look at jaypipes blog he seem to have borrowed a name thewench

does anyone know what to do if a program freeze at futex_wait (in this case mysql-query-browser which causes this when I try to select a catalog)

I still have a question seek
I cant access phpMA now…
What do I do?
I can only access via cmd

www.phpmyadmin.net
Go ask them
lol

Lapmann, that program is buggy

sure seems like it… do you know of any other options (on the linux platform)?

navicat maybe?
that's non free tho
sadly
in both senses of the word
it does have a free demo tho
otherwise you're gonna have to check sourceforge,gnomefiles, etc yourself
lemme know if you find something
that actually works ..

I'll try the demo. if it works well then maybe my company could buy it for me~~

it seems that the linux version is perpetually behind the windows version
so.. try that on a windows box too
then you could try it in wine maybe..
or in some virtualization image..

Guys, MySQL can perform VALUES() inside SELECT ?

Why?
Or do you mean SELECT inside VALUES?

no no. i need to inject some external data to make SELECT create a new column

External data? Be more specific.

like SELECT a.*,foo.y FROM table as a INNER JOIN (VALUES (1,2),(2,3),(3,4)) as foo(x,y) ON foo.x=a.x

No
Explain your situation

I am trying to speed my database, is there a way to see if query cacheing is enabled?

the software gui didn't work well with my setup.. the fonts doesn't look pretty _

pretty vague q, but, how does performance compare between 5.0 and 5.1/are there any decent benchmarks on this?

SHOW VARIABLES , look for qcache stuff

i have a table with a index. i get thru PHP an pair of numbers (index, score). now i need to "INNER JOIN" the external data, adding the new Store column.

Why INNER JOIN? Use a where clause

the score is not on the table. its calculated outside

If you're not going to be more specific, I cannot help… You can always create a temp table and join against that.

so i need to create a new column with the external data

brmassa, calc in the sql

thanks! ran that dont see anything with qcache, is there a howto somewhere that i can read to turn it on?

!man query cache

see http://dev.mysql.com/doc/refman/5.0/en/query-cache.html

the_wench: perfect! thanks

seekwill, im trying to avoid this. coz the process is going to be repeated many times and 1*create temp table, 2* inner join 3* delete temp is going to hurt performance

Then do it outside of SQL

archivist. i cant calculate on sql.

there's always subquerying..

Like I said, if you're not going to explain your situation with more detail, I cannot help

it creates a temp table automatically..
or so it implies

seekwill, i have a pets and men tables, with phisical attributes. form a given man, i need to calculate trhu neural nets the socre of each pet. but its done outside the SQL. now, with the pet id and the score, i need rank them with a normal ORDER BY, but i need to add the new SCORE
column

It would seem faster to do the sorting outside of MySQL

subquery what? the data is not stored in databases..

then have fun

hmmm that is another problem. coz the API that i use create automatically pagination, and also the "previous 1 2 3 next" navigation.

heh

but i need to give the function the SQL statement

then calcuate the score and put it in sql..

what about select a.*,v from table as a INNER JOIN (select 1 as x,2 as y UNION ALL select 2,3 UNION ALL select 3,4) ON x=a.x
will it work?

uhmm.. if you wrote it.. you should test it

Good idea. But not sure if that will be any faster

some guy told me this

Another guy will tell you a different story. But you won't know the truth until you try it

seekwill… ok ok… ill be back
seekwill! it works!!!! hahahaha
im damn happy!

Now how fast is it?

thanks a lot man.

Seems like you did more of the work than I

i dont know. i didnt tested in a big table. in fact, im doing a generic program to be applied to many situations…
so i dont know yet the size of the tables.

How large is a "big table"?

millions. but the first and immediate aplication wont have thousands

At "millions", I think your UNION solution isn't going to work so well

at thousands?

Don't know

well.. its the only solution
or this or create/use/delete temp tables.

I don't believe memory tables are that expensive

I might store the results with the data
dont need to calculate as often then

not possible. the combination of factors can be huge.
there are maybe bilions of combinations.

is Google using MySQL?

yes

hahaha

link?
I need to convince some co-workers MySQL doesn't suck.

help me!!! mysql is getting slow!

MySQL sucks

mysql does not get slow, stupid sql is slow

hahaa

it might, but if Google uses it, it's good enough for us.
I could use a list of Big Names using MySQL.

see mysql.com for a page with names

http://www.mysql.com/customers/
and that englobes only paid users

… and that want to be listed
There are many bigger, paying companies that are not listed

seekwill. ysp
yes.

Seriously, it's a waste of time to convince someone. Who cares?
If you're trying to convince your boss, don't. Stupid idea. If something goes wrong, it's your fault.

except that's also true if I follow his advice
I'm lead developer here, if something goes wrong, it's my fault anyway.

heads or tails YOU lose

Then leave the company. If you're lead dev, you should pick

nah, they're generally OK, and we'd probably go with MySQL because I insisted.
the other suggestion was Oracle, btw.

Buy a support contract to save your ass

yeah, we might.

Especially if Oracle was in consideration

good idea.
actually, Oracle was brought up because the Enterprise version became Free as Beer
(though of course, if you go Oracle for a growing data set, you plan to spend some money somewhere down the road)

/wn
oops sorry

hi to all

is there a way to change a column in this way, i need to multiply all entrys in one column by 30%

update f set c = c * 1.3 ;

f = table name?
c = column name?

1) i need to import a dump using myisam engine onto a db that has innodb like default engine

!man update

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

ilreds, personally I'd import them using myisam … and then alter each table into InnoDB

can i tell to mysql server to use myisam engine for thata dump?
need i to insert a engine=myisam into the dump for each table?

ilreds, by default it is myisam ..
why are you dumping?
and modifying?
alter table blah engine=innodb;

the default engine is innodb into that mysql server

–create-options .. or something like that with mysqldump
mysqldump –help

ok, sarry if my request is too simple

ilreds, another method is …

yes…

select * from table into outfile '/var/tmp/file.data';
and then load data infile '/var/tmp/file.data' ignore into table ..
but whatever works

mmmmmmmmm

hi peeps
UPDATE basket SET quantity -1 WHERE id = '.$rowno.'
im trying to subtract 1 of the existing quantity, but can get it to work

you tried set quantity=quantity - 1?

mattcrane, set something = somethingelse

UPDATE basket SET quantity=quantity -1 WHERE id = '.$rowno.'

exactly'

hi - still hoping to find an answer to my encoding puzzle
http://forums.mysql.com/read.php?10,164780,164780#msg-164780
anyone?

I converted a database to utf8 by dumping it as latin1, convert it with iconv and then put it back as utf8..

I dumped it as latin1 — what do you mean by convert it with iconv?
the dumping as latin1 seemed to make it readable as urf8 just opening it in a text editor

iconv –from-code=latin1 –to-code=utf8 -o newfile.sql oldfile.sql
or something I gues…

ok I'll give it a shot and see how it goes - thanks

if I have a table with utf8 strings, is there any way to find only those that have non-latin characters?

hmm interesting - well, now I get a "max_allowed_packet" error - picket bigger than
this does not happen when pumping the sql version without that conversion

really? I don't remember how I did it.. just did it quick and dirty because I'm working on converting a php-app to utf8

only gained about a MB in size, but guess it was enough??
hmm - I wonder how I can ask mysql what the current max-allowed-packet is

tibyke please help!
i got to read some rows and update the time field to some value.
is it still impossible becaseu they are in the same table?

*sigh*

DavidHKMrPowers, never request help from a person.. it just pisses people off
ask the channel

and start readning the manual first

hi, in a where clause which is faster, "WHERE foo IN (lots-of-ids)" or "WHERE foo=id1 OR foo=id2 OR …"?

in( will be a bit faster

k, thanks

can any tell me the command to get the current max-allowed-packet size

that hsould be in the manual
should*
or you could look in your my.cnf..

show variables like 'max%';

tibyke johnny

huh?

lemme check

ah its in mysql –help

tibyke is it still the case you told me yestedat?

i told you to RTFM, and i do mean it

doesn't work

Doesn't work is not a helpful statement. Was there an error? Unexpected results? Does it sit on the couch all day eating all your cheetos and ignoring the classifieds? Be specific!

http://www.techonthenet.com/sql/in.php

ugh - mysql commandline woes - sorry to pest but this stuff is so confusing. trying to change the max allowed packet size I've tried variations on: sudo mysql -p –max-allowed-packet=32M; but it keeps logging me into the mysql prompt where obviously I can not set the value
without the -p I get user denied root@localhost

you have to set it in there
or in your my.cnf
you an set vars if you're allowed to..
but you should prolly edit your my.cnf tho

ok
its my server so I have control of the whole ship

tere are prolly other ways
but why waste time
get it done
then you can set it back later

where can I find my.cnf

lol
c'mon dude
where are config files usually stored..

yes

plus.. ibet you have a search tool if nothing else

ThinkMedical heh

what are you trying to do?

don't feed

i needa to get some rows where last_crawl_time = 0 and set it to time();
ui needa to get some rows where last_crawl_time = 0 and set it to time();/u
feed me a fish and i have enough energy to fish

honestly docs on using command line tools around the web are all geared toward people with CS degrees unfortunately - which is why I just ask - so I'll try to find the tuts on using find - but it will take me an hour. But thanks for the info on my.cnf, I do appreciate the help

DavidHKMrPowers, I have allready given you a big enough clue stick

UPDATE `foo` SET `bar` = ' . time() .' WHERE last_crawl_time = 0 DavidHKMrPowers

teach me to fish and i will faint before i can fish

ThinkMedical, thats too good for him as he refuses to think for himself

ThinkMedical not this…
i needa to SELECT rows and get the rows..
i've read http://www.techonthenet.com/sql/in.php, http://dev.mysql.com/doc/refman/5.1/en/update.html

hmm, ill be back in 5

ok
archivist if it's UPDATE only…i can do..