how i can solve a problem, with slow querys. i've many inserts, and this get slow selects.. i've about 8.000 querys/s

how can i say
where field1 = field2+"string"

guys i'm thinking of a good way to store multiple shipment addresses. the schema is quite clear, just got an addresses table with firstname, lastname, street, etc. but the frontend… i'm not so sure when i should UPDATE the address, or INSERT a new one.. say they only change the street
number because they mistyped.. should i INSERT or somehow ON DUPLICATE KEY over some of the fields….
thinking tihnking…

I am running apache 2.XX php 5 and my sql host 4.1 on win98se however I can't get mysql 4.1 to run, when I installed it it didn't ask for a root password, after opening up a ms dos prompt and cd'ing to the bin directory typing in mysql -u root it askes
for a password since I didn't give it a password leaving that blank it says 10061 error
it used to work if I launched an init txt file with the root and password set, and then opened up a new ms dos window and launched mysqlnt now it doesn't even do that all I et is 10061 errors

Trigger in wrong schema
What's that all about?

"PC Load Letter" What the fuck does that mean?

It should be noted that I'm not a developer and has had nothing to do with triggers, I'm merely trying to rename a database

PC Load Letter means load letter size paper into your printer.

i can't find help on gettext.. gahhh

does anyone know what could be wrong with mysql 4.1 not being connected on win98?

create table exercise_xml (id int(11));

Table 'exercise_xml' already exists

drop table exercise_xml;

Unknown table 'exercise_xml'
This is also a bit strange?
Ah, stray .frm

.

Hi, i have a question regarding character set and collations in MySQL5.2, anyone firm with that?

just ask

5.2?

"Hello World"

ehh… MySql 5
5.0.41-log
I already tried a but with CONVERT and CAST… but then I end up with "Hell? W?rld"

Trigger in wrong schema and get "Empty set" when doing 'show triggers;', isn't that a bit contradictory ?

SELECT CONVERT(_latin1'Müller' USING utf8);
SELECT CONVERT(_utf8'Müller' USING latn1);

in both cases you get "Müller" but I want "Muller"
a UNIQUE constrain on a varchar column makes a check on that…
SELECT 'Müller' = 'Muller'; return 1

how to connect database and web page ?

You'll need a scripting language to go between - PHP, Perl, ASP, whatever.

what about java ?

That can work.

you will have to have a look at JDBC with Java

snoyes pm

You'll need to register your nick and identify to nickserv before you can send a pm.

i dont know much about these things please some one tell me

I have a table of sales. How could I get the Average sale for each day of the week?
average number of sales, i mean

SELECT WEEKDAY(saleDate) AS day, AVG(sales) FROM table GROUP BY day;

oh… I guess you need a subselect for that…

Is there a way to query the mime type of a field using the 'file' program in linux… or some other way that doesn't involve extracting the data into an external script?
that is, of a blob field

maybe something like: SELECT WEEKDAY(saleDate) AS weekday, AVG(cnt) FROM (SELECT saleDate, COUNT(sales) as cnt FROM tb_sales GROUP BY day) AS sub GROUP BY weekday;

mysql -e "SELECT blobField FROM table WHERE id = x" | file -

oh, snoyes, thanks

please help!! what does index mean?

Hello using mysql with php, if I have a remote database, mysql_pconnect is better than mysql_connect?

key

You know what the index in the back of a book does? An index in mysql does the same thing.

still no body here knowing how to turn "Hellö Wörld" into "Hello World"?

what's the difference between group two columns as a unique unit and grouping two columns as a index unit?

the file command has a –mime argument

UNIQUE says that combination has to be unique. INDEX or KEY just means it can look them up fast; no statements about the uniqueness

snoyes it's a category

what's a category?

chaper 1, chapter 2, etc
snoyes i see!!! so after making two columns indexed, should i make that group unique or make each of them unique?

There's no need to do both INDEX and UNIQUE. UNIQUE is an index, plus a unique constraint.

snoyes oops!
i need a unique combo of two columns

Then put UNIQUE(field1, field2)

you can make a unique index on two columns

wich information contain the file db.frm?

thanks! but i don't know why some other people told me to use index for this..
snoyes flung please help one more things simply. i found two other choices: fulltext and primary. why are they simply?

DavidHKMrPowers, unique(.. is an index

archivist just got that

.frm files contain the format of the data

primary is like unique, but doesn't allow nulls. You can only have one primary index on a table. fulltext is for when you need to search text data for keywords.

flung is there a way to repair thi file?

you can try REPAIR TABLE, but I kinda doubt that will work

snoyes i see! thanks
i always set the first column to be primary so it doens't matter

java servlet hosting applet is for web page design is it ?

flung, i have strange entries in db.frm, things from squid and squid reports is that normal??

no
web application

applets can be embedded in web pages, but do not use them for the whole web page.

also applet is for virus too

how are you reading the frm?

flung well i just make a cat db.frm
flung can you take a look of the paste?

it should just be a bunch of garbage
binary

i have front end designed with applet how to connect it to database(mysql)

wie kann ich collation und den character set mit dem mysql client auslesen bei mysql hosting 4.0.24

if you can read it like a text file, then it's beyond repaid
repair

ups

DavidHKMrPowers snoyes ?

with the jdbc link I already gave you.

how do I get the charatset and the collation from my database host with the mysql client in mysql 4.0.24

flung excuse me, what is mean? must i reinstall mysql?

snoyes ok

at least recreate that database

flung, http://www.pastebin.ca/635895

5.0.26 … should "CREATE FUNCTION BEGIN … SQL/PSM code … END" work?

yes

I am getting tons of syntax errors… I can define a 'null' function (just an empty begin end block), but anything else causes an error.

if you know the users and permissions, you can create a new database, then cp your old tables over

Did you remember to change the delimiter?

go on… (I didn't)
what is the delimiter?

http://dev.mysql.com/doc/refman/5.0/en/begin-end.html

flung, you mean create a new database using command line? with root user?

yeah, make sure you back up first

how can i view FK constraints on the table? and, additionally, refering a table?

snoyes thank you meet you later
bye

see ya

comment is not good, it contains only part of that , plus it gets overwritten by "innodb free"

flung but mysql doesnt start…. how can i create new database if mysql dont start

mysql_install_db
or something like that

wow! so I can't use ',''s at all unless I change it?

brr…. how to use utf8 in stored functions? Incorrect string value: '\xFCller' for column 's' at row 1
http://www.paste2.org/p/5234

It's kinda the same concept as having to escape quotes inside strings.
so do DELIMITER anythingHereExceptASemiColon
create function blah blah blah
DELIMITER ;

should this work though… http://www.paste2.org/p/5235

that comma on line 5 should be a semicolon, and you pick a new delimiter to use on line 7.

hrm… a semicolon on line 7 causes an error.

of course, because you didn't change the delimiter before line 3.
http://www.paste2.org/p/5236

ahhhh… thanks
I didn't realise the BEGIN, END block had its own (original) delimiter.
heheh… how to find out what I set the delimiter to?
OK, everything is working nice

You can pick whatever delimiter you like.
within reason

I had 'broken' my prompt by selecting something random.

please can anyone help? how to use utf8 in stored functions? I get the error Incorrect string value: '\xFCller' for column 's' at row 1

CALL randomTest();
sorry, I don't knwo

hi everyone, i'm having trouble getting = to work on a DATE field
i definately have fields in the table which have entries for 2007
but when i do WHERE `joindate` = '2007'
it only returns records from 2006
any ideas please?

i have 2 tables in one DB that look alike(same info), how can i compare them, and look at differences?

2006 is less than 2007…
'2007-01-01' is greater than the string '2007'

yeah, but i have the equals there too, right? 2007 is less than or equal to 2007 yeah?

you could do WHERE joindate '2008', or you could do where YEAR(joindate) = 2007, or WHERE joindate = '2007-12-31'

hmm
see, i'm wanting to be able to do
WHERE joindate = 2007
or this:
WHERE joindate = 2007-03
or:
WHERE joindate = 2007-03-17

hi, good morning
I have a little BIG problem I have a FUll DB backup that I need to import to a empty slave…
the problem is that the whole SQL file is about 50Gbs
and the process takes forever….
there's a way to improve the importing speed?

!man speed of insert statements

see http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html

snoyes, in a similar situation, i do LIKE %2007% or LIKE %2007-03% and it does what i want
any ideas?

I gave you three options.

is there any way to get the current result set row number in a separate column?

but i can't see any of them working once i start to get months and/or days involved

the_wench, I checked it before, most of those tricks aplly only to MyIsam

http://www.xaprb.com/blog/2006/12/02/how-to-number-rows-in-mysql/

but the whole backup is InnoDB

getting error …. "ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)"?

$FILE_NAME

i tried with mysql_safe & but still there

it should be fast ..

I'd take the approach of turning your value into the latest possible date for that condition. If you only have a year, append '-12-31' to it. If you have a year and month, append 31 to it. If you have year, month, and day, you're all set.

snoyes, yeah, looks like that might be the best way to do it. thanks for your help!

snoyes, thanks alot, the described approach works fine

Starting mysqld daemon with databases from /var/lib/mysql
STOPPING server from pid file /var/run/mysqld/mysqld.pid
6 mysqld
[1]+ Done mysqld_safe
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock"?

Hey everyone, how can I query 2 records and switch 2 field values?

anyone know how to compare tables(if they have same fields) and look at diferences
?

toolkit

how to start mysql server in kubuntu

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.

ty

snoyes how to start mysql server

!man unix post installation

see http://dev.mysql.com/doc/refman/5.0/en/unix-post-installation.html

what is that 'benchmark' function?
I seem to remember you can itterate over sql hosting to get some stats…

!man benchmark

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

!man information functions

see http://dev.mysql.com/doc/refman/5.0/en/information-functions.html

heh… easy when you know how!
thanks again snoyes
either mysql is dead or my watch has stopped

I need a query to order some Topics based on "popularity", which would be the volume of votes and whatnot over time, I have a basic query, but I had to scope the time interval to the past 24 hours, but then everything else is not included in the set, and if the site slows down, my Top N list
gets shorter, does anyone know a better way to do this?

is there a way to get mysqldump to dump just table information, routines, etc?

mysqldump -d

ah thanks, I've been looking at –help etc didn't see that

it's between -t, –no-create-info and -N, –no-set-names

are index names supposed to be unique in the whole database and not just in the table?

no, unique within the table is sufficient.

Hmmm… I'm having a weird problem with doing updates. mysql starts thrashing the disk on one update but is fine with update which is almost 100% similar to the one which works fine
*sigh* great sentence there
hold on a sec
So I have a big table which I'm hoping to normalize a bit. So I take two column out of it and give them primary keys and build indices for the datas. Then I'd like to update the key-numbers back to the big table and it's done like this: update bigt, smallt set bigt.key = smallt.id where bigt.data =
smallt.data

toma, OK
and one column works fine and the other doesn't?

You need to specify more complete requirements. I don't think you know what they are yet. What, exactly, do you want this query to produce?

yes
the machine just seems to do a lot of disk-access

and you checked that both columns are indexed?

yes

what are the two datatypes?

varchar( 15 )

(both columns are indexed in both tabels?)
are the datatypes the same in all tables?

there is index for bigt.data and for both of the smallt.data's
yes

sivaji@sivaji-desktop:~$ sudo mysql -u root
Access denied for user 'root'@'localhost' (using password: NO)

toma, I didn't follow your notation.

i am not getting mysql console why ?

toma, you will probably find an error of the kind I am suggesting somewhere.

please someone help i am very new to mysql

check the count(*) query over the join for both columns.
what is your root password?
wait… I mean try -pSomething
or -p then hit return when it prompts

faceface linux

yesss

faceface ?

you just installed mysql?

yes

read about setting up accounts

can I check if a index already exists? (and FK)

ummm, what do you mean?

yyya i read i cant understand /

toma, your update needs a join right?
read more

well, it doesn't use the sql-join

faceface why did you ask my root password ?

toma, I thought I knew what you were doing, but now I don't

because I'm a noob in case you didn't notice

your 'mysql root' is not the same as your linux root

does anyone know how to replicate an INTERSECT in mysql?

faceface that is not my root password

toma you have table "one", with two varchar(15) cols, and you want to normalize the data?
by default mysql installs one user, the so called 'root' user. That user has a 'null' password by default.
now go figure!

Yes.

so you create table "one_a" and "one_b" to hold the distinct values of column a and b?

yup

can I check if a index already exists? (and FK)

and one_a and one_b both have auto-incriment PK,s that you want to update back into table "one" as FK's

how to get mysql console ?

Actually… to get real picky… default installs four users and the password is an empty string, not NULL.

automatically or manually?

exactly

;-) tell that to ksivaji !
toma, so you have to do two update queries, both using joins

hi; i have what should be an easy q

automatically

don't knwo

please someone tell me how to get mysql console ?

toma, so you have to do two update queries, both using joins …
try adding a '-p' to the command you posted

i have some ruby code, but really is just the query here http://pastie.caboo.se/82796

yes. but I don't use sql command JOIN with doing it. I was using update a,b set a.fk = b.pk where a.data = b.data

sivaji@sivaji-desktop:~$ sudo mysql -u root -p
Enter password:
Access denied for user 'root'@'localhost' (using password: YES)

Your MySQL root user has a password on it. You, or someone who installed MySQL, set one up. You have to provide the correct password.
You don't have to do sudo

toma, looks good… I think

did you use the option -p ?

drop the sudo…

roxlu yes
faceface ok

it may be useful to note i have class_uid as a primary key. so here's the issue. when i run this, no course_div is set to 'l' (the letter), eventhough a puts right before it shows that the var. course_div holds 'l'

"using passowrd: yes" implies that…

toma, select count(*) from one inner join one_a on one.a_data = one_a.a_data

access denied access denied access denied access denied what to do ?

or however your real col names translate… you said… "a.data = b.data" - join on that and count(*)
and what did you enter for teh pwd?
i.e. just hit return

ok, a sec

If you don't know the correct password, you will have to reset the root password

he is just bumbling, I bet its still a blank string.

mysql root password or system root password ?

Na. If it was a blank screen, leaving off the -p would have let him in

ok, now try -h localhost

mysql root password.

dang… I didn't anticipate that question
host issues I bet

His error message indicates he is already connecting to localhost.

grrr!!!

faceface i dont remember the password

stop taunting me!!!

Sorry! Just trying to help

;-)
just joking around

seekwill how to change ?
mysql root password ?

If you can follow directions…
!tell ksivaji about reset root

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

Read the WHOLE page first.

wow! smart

Services " i am using kubuntu

Keep reading

I think this will take longer than a sec, the whole machine is bogging down ;P

Like I said, the WHOLE page…

ok

gentoo–

( it's running the update in the background so… )
maybe I'll try to abort it before testing more

toma, are you looking at the join that gave the quick update?
or the join that gives the slow update?

the slow update

and how many rows in each table?
toma, try the join for the quick update… and prolly terminate the update. I think you have a problem with your join.

bind-address = local host or ip i am not going to access any remote database

the big table is around 62 million rows and the small tables are about one million and the other is 8 million. The fast one to update is suprisingly the 8 million row table

Just comment it out for the time being

Okies, I'll test the join-version then

seekwill what do you mean ?

hello all, care to share your opinions on serving 5mb to 15mb file downloads from mysql opposed to the file system (via php)?

images

http://hashmysql.org/index.php?title=Storing_files_in_the_database

Why are you touching that setting?

Both joins should give 64 million. And just to double check, all four 'data' columns are indexed and of the same datatype?

seekwill it is there in config manual

Is it in the reset root manual?
Fix one problem at a time.

yup, they are

seekwill no it is set to local host i suspect my probelm may be because of that ?

no

toma and how fast was the quick update?

Your problem is that you do not know your MySQL root password

50 minutes

anyway, 'explain' both joins and try to figure it out

If that was the problem, you would not be getting that error message

ok

okies, I'll try that. Thanks

toma, also look at the server config… you could boost performance by increasing the amount of memory allocated to certain buffers.
at 64 million rows you have to start thinking about tweeking the server!

any other performance issues with serving large files?

(assuming your server is decent)

already did look at them but I thought that I'd need to know more about how the internals work before starting to do tweaks

toma, if you find any good docs on internals let me know!

4-way opteron / 8G ram

but boosting one or two buffers is all you need typically
by boosting you can safely read 'maxing out'

yeah, I tried to look if there is some generic cache I could use but it looked like that the OS disk cache is all that it needs. So increasing the query cache probably wouldn't help at this point
but I dunno
I'm very much of a newbie with databases

sort_key off the top of my head

Is there a round-to-even in MySQL?

can you use MOD to do it?

key_buffer_size is also critical.

if I want to do something like a != to in my where clause what is the syntax?

WHERE nick != 'will'

'xamox'

seekwill, thx

is preferable (standard).

seekwill, is that case senstive?

Depends on collation.

Xgc, excuse my ignorance, but what is collation?

http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html
http://dev.mysql.com/doc/refman/5.0/en/charset-charsets.html

hi

The collation will determine if it"case insensitive"
The collation will determine if it's "case insensitive" (ci) or not.

Xgc, is collation mysql specific?

"it" == comparisons.
No.
You'll find similar issues in other databases.

it means sort order ish

Xgc, thx

if i run a select and the output is just too long. how do I stop it?

http://dev.mysql.com/doc/refman/5.0/en/charset-collations.html

BlkPoohba, use a better where clause

i know that now and then.

lol

Chapter 10 (in the 5.0 docs) has other useful detail. 10.4 might be a good place to start.

Xgc, alright, thanks for the help.

Bah. Just read all of 10.

how do I show the sql to create a table from the mysql prompt?

!man show create table

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

!man show constraints

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

ty

eh heh

just kill the process
and in future, dont use massive result returns

SHOW COLLATION Syntax !

how can i see FKs?

!man show create table

once, i had a query that wanted to return nearly 10 million rows

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

++
+=10000

needless to say, the server didnt like it

create table ? and what if i need to know what references some table?

Bizzy is the reason MySQL added the –safe-updates option

anyone know of any research or published benchmarks on serving files from the db vs. filesystem?

besides, this is not machine readable really (the show create table)

if I'm doing a sub select, hom can I reference a column in the parent select, simply using the column name doesn't work as expected, but it does run

faceface, bot does not understand your version of the nick++ syntax

what?

snoyes:–

information_schema contains some of that kind of information

thanks

handy feature to prevent queries that return too many rows.

I thought that was the –i-am-a-dummy option

Use a JOIN and refer to the parent table in the ON clause or the WHERE clause, depending on which is more appropriate.

same thing, less insulting name.

'', aID, (SELECT name FROM people WHERE id=bID) FROM reservations

last time I suggested the –i-am-a-dummy option, the user thought I was making fun of him and got insulted.

When is specifying the join criteria in the WHERE more appropriate?
That's the best part!

so there's no way to reference bID? AS doesn't work either

"more appropriate" meant, depending whether this is join criteria or filtering criteria.

That happened once when I was recommending "gone away". He thought I was brushing him off.
Oh

Does bid exist in the reservations table?
and the people table?

i need some info plz

yes

select cod_dist from distritos where distrito = "Aveiro"
select concelho from concelhos where cod_dist = result from 1st sql command here

If so, WHERE id=reservations.bID)

how can i do this ?
simple way ?

oh that's so obvious, thanks

You're welcome.

anyone ?

!man subqueries

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

joins

http://hashmysql.org/index.php?title=Introduction_to_Joins - For more indepth info: http://dev.mysql.com/tech-resources/articles/mysql-db-design-ch5.pdf

or a plain join

the reason it returned that many is because i read a mysql doc wrong on joining, because it wasnt worded right

when using a unique key and there is a duplicate, can it skip that record and keep going?

i managed to join a table to its self, and return every row, for each row

Impossible. All MySQL docs are perfect in every respect.
INSERT IGNORE…

or ON DUPLICATE KEY …

sweet

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
getting this error ?

is this Ok or is there a more optimized way? SELECT * FROM tbl WHERE name IN ('foo', 'bar', …);

++

fritz[]: Given no other information, that's fine.
fritz[]: Having said that, it sometimes doesn't take much digging to uncover a mis-, improper, poor use, even in such a simple example.

how do I set collation_connection from the CLI?
I am having hidous function / collation collions
collisions

i want to install mysql-5.0.45 can i install rhel version for centos 4.5????
bcoz there is no mysql5.0.45 for centos

Will these work in .my.cnf ? "SET NAMES 'charset_name'" and "SET CHARACTER SET charset_name;"

Hello. Mysql doesn't work. Help? I'm trying to migrate to a different server. And any time there's non-ascii data, I get garbage.

And there is one for RHEL?

there are custom rpms for 5.0.45 i have installed on rhel4
one sec
http://remi.collet.free.fr/rpms/el4.i386/
a href="http://remi.collet.free.fr/rpms/el4.i386/"http://remi.collet.free.fr/rpms/el4.i386//a

Any idea what character set I should use to be able to see UK pound signs?

was the only way i could get a mysql server = 5.0.22 and keep my old dbs and their deps

I guess rhel is ok on centos

why 5.0.45 ? whats wrong with 5.0.lowerthan45 if you're not taking an 5.1?

yah no doubt

i want to install mysql-5.0.45 at centos4.5 there is no specific version of mysql for centos at mysql.com … will u pls guide which will compatible for centos4.5 ?

what happens when you try "rpm -i rhel-version"

Well, the point of using something like CentOS is that all the versions stay consistant…

i installed mysql-5.0.45 dynamically liked generic version … but its deamon does't start ..

If it isn't in centosplus (or whatever that repo was called) then I wouldn't install it. Doesn't CentOS5/RHEL5 have MySQL 5.0.x?

so i remove it

I swear it's +m in here…

is 'set character set latin1;' at the cli the same as "character-set=latin1" in .my.cnf?
because "SHOW VARIABLES LIKE '%coll%'" says it aint… which bakes the question, what is?
because "SHOW VARIABLES LIKE '%coll%'" says it aint… which bakes the question, what is?

I think you mean "begs the question"

oh…
I am baking

Why the hell am I getting garbage data?! The character sets are the same, the collation is the same…
It's STUPID!

when I login and issue "set character set latin1;", then "SHOW VARIABLES LIKE '%coll%';" gives me what I want. But I want this to happen every time by default.

It's STUPID!
IT DOES NOT DO WHAT I WANT IT TO DO

the weird thing is that "character-set=latin1" *is* a valid option in .my.cnf

so whats the proper way to repair an innodb table? dump it, drop it, and reimport?

Well, when I copy the raw data files from one box to another and get different results, I think it's pretty bloody stupid.

How do you know it's corrupted?

if you are getting really bad errors that is the best way.

Yeah, except dumping introduces crap data… _

but you may have to drop *every* innodb table!

create table student(name varchar(10));

No database selected
how to select database ?

use the force luke

can anyone answer why "character-set=latin1" in .my.cnf is different from "set character set latin1;" at the cli?
please read a book

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

MNSFDGJDFGHWERQG£!%£5~!£Y5~VG! 5~8~

how can …

same. character sets: same. data files: same. select results? WRONG! ASKDFJDFG @H5~Q%%%%%%%%%%%%%%%%Y~ASD

I don't think you're using the correct irc character set. Everything you type is coming out as black blocks.

everyone on the machine or everyone in the database?

I'm beating up my keyboard.

depends on how you set up innodb

Or do you mean /everything/ ?
How's that

Xgc faceface ok ok but in oracle its different you need not say use database

when ever using a reports db where all the tables are innodb, mysql will crash. I did a check on everything and it says its ok. innodb_table_monitor looks fine.

In Oracle '' IS NULL. I'm not sure this is really significant to MySQL.

its only when the report tries to make a new table

We don't use sqlplus here.

it makes the .frm file but nothing else

so sqlplus is different is it ?

Very.

guys, I'm doing a round robin with two read servers but getting huge amount of stat NULL
any idea?

collation_connection | latin1_swedish_ci
I want "latin1_general_cs" 'by default'
that is what I get when I issue a "SET CHARACTER SET latin1" from the CLI

hello folks

Dont forget the bots!

curse you bot!

what was the easiest method of selecting 10 highest results from a column?

sort by limit 10;

ASC

faceface thank you, trying
oh that too
DESC or ASC
let me try

ASC LIMIT 10; for the 10 highest results

how do I select all things that are not 2,4, or 6.

WHERE field NOT IN (2,4,6);
:s/field/value

cool

whatever you get the picture. sorry, just woke up.

dan__t hm
i just tried that with select and it didn't work

what's the query

can't never rememeber the damn syntax.. will start a diary soon

how can can I get the collation and character set via mysql client of an database in mysql 4.0.24 "SHOW CHARACTER SET" doesn't work ;/

oh boy… character sets and functions … what a pain in the ass!
show variables like …

dan__t select name,time from table sort by time asc limit 10?
why wouldn't this one work?

Sort by?

order by

err

sort by limit 10;

what's the syntax exactlly

i tried to follow that

Try ORDER BY

thank you xgc,seekwill - that worked!

robboplus, That was to you.

my server cs / col == my client cs / col == my connectin cs / col … I believe … I believe my table has the right cs / col and I create a function from the table and a table from the function … then I try to join the two tables and and … implicit col conflict

yes xgc
finnaly got it SORTED BY

Are you using phpMA?

thank you folks, now i will write it down before i forget the damn thing again

so long… thanks for help people
its time for me to go home

btw is it possible to recalculate a result on the fly? like when i'm getting result in seconds and i would like it converted to hours or days/hours/minutes even?
or is that too dumb to ask at all?

Calling Oracle a ho now?

e.g. 557792 seconds isn't really telling much

How many seconds are there in an hour?

seekwill 3600

557792/3600

Xgc just oracle

seekwill yes but is it possible to make mysql count this for me?
and output results in hours?

yes..

that's what i mean

SELECT (557792/3600);

just put that in there

!m robboplus date and time fun

robboplus see http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

seekwill can i substitue the raw value with column name?

Yes

seekwill thank you so much - checking
just had NO idea it was possible lol

Anything is possible

he he
oh my…
that works!
lol nice

Maybe I already know what that is like

OMG REALLY?

yesyes

Therion:

with CASE, is there a way to do something like WHEN (a or b) THEN result ?

seekwill… how about precision?

We don't do precise things here

seekwill if i get 155.0089, i would like only TWO after .

It's all relative here

lol
ok is that rounding up then? my english is really poor..
i'm trying though

Two after?

Every database supports a different (declarative) SQL-like language. There's a standard that no one fully supports and everyone has extensions to it. Almost every database also supports a procedural langauge, which incorporates basic SQL in the language.

i mean 155.00 instead of 155.0089
the default number of digits after dot is 4, how can i make it two?
uthe default number of digits after dot is 4, how can i make it two?/u

Both the declarative and procedural languages each database supports is different than any other database. That's just the nature of the business.

ROUND() or TRUNCATE()

ok

hello everybody
is there a way to get the structure of a table via a SQL statement in MySQL?

hi, i'm attempting query a nested set table by depth, but it is complicated by the fact that many trees are stored in 1 table and i'm having trouble making my query return correct results

like mysqldump

!m lupino3 show create table

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

been reading this information http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

snoyes thank you! checking

mysqldump -d

thanks a lot!

snoyes Xgc bye meet you later

i was able to make the earlier queries work like showing depth by a root node
by adding a few self joins
but it doesn't seem to be as easy to modify to get the depth by subtree

bye, thanks again

Depth in subtree = depth in full tree - depth of root of subtree in full tree.

sure
http://pastie.textmate.org/82827
that's the table + data + query
oops.. forgot pastie doesnt' wrap

I'm serious. If you know the depth of any node in the full tree and you know the target node and the root node of the subtree, simple subtraction is the answer.

that's what is happening, the problem is the result set seems to include other subtrees
or rather data from other subtrees

Quick ?: How do I use the ego (\G) command? Do I write the query first and end it with ego or what?

so the count is off

SELECT * FROM table\G

I'll try that. Thanks.

i've tried various combinations of moving the self joins and ANDing the tree root node attributes
i've been able to get the full tree, leaf nodes, path, total depth

It'll probably look messy, but the idea is simple. I don't have time to dive into this and I don't think you've fully described your intent. What subtree are you talking about? Maybe that's part of your problem.

the problem starting in trying to get part of a tree by depth

rafb.net/paste might work better.

it currently does that in the application code

Oh. Sorry. Ignore that comment.

but it is causing heavy performance problems on sites with large numbers of comments

does CASE not work inside of CONCAT() ?

as in, if you were to set the render depth to say 3, it would grab ALL the comments and then filter out by depth in the application code
or rather grab all the comments for a specific object

It should. Yes.

hello people, how can i create an array and use it inside a stored procedure?

hey I have a question, how do I copy a single mysql table entry to another table?

no array types in MYSQL.

That's what I figured. but can you find what's wrong here? http://rafb.net/p/S2UR2J83.html

!m NeosVortex insert select

NeosVortex see http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

thank you snoyes

Hi to all

I understand that looks useless right now, but I'm just trying to get the output i want for an UPDATE to a table.

I've a problem with a query

basically doing some randomization of some data.
but anyway, mysql is claiming syntax error
I tried adding parenthesis in places, no luck

Pasting the query eliminates 99% of guessing

and it works fine without CONCAT
seekwill, talking to me?

Making a general statement

http://rafb.net/p/S2UR2J83.html

is there a way to get mysql to begin an auto_increment at a certain number?

These are common problems in databases that don't directly support recursive constructs.

Xgc, yes.. read that many times

fedx_, yes. AUTO_INCREMENT=x at the end of the create table statement.
where x is the number you want

in many articles that refer such data data structures

nice. thanks

Xgc, i went through that article i pasted a link to earlier, and it works fine within its simple table
and in fact, it seems to work fine when there are no other trees in the db

what does it mean when all queries states is NULL in show processlist ?

i was able to solve that in most cases with the aforementioned self joins on modid,itemtype,objectid

If you're around later (after work), I'll have time to look at this.

when is that?

4 or 5 hours.

Xgc, i just want to make sure you know that i've quite a few other things before coming in here
tried*

It's obvious you've spent time on this.

i've worked support chat.. so i know how it is
when it is paid for.. you don't expect pre research.. when not tho.. that's when you get pissed

hey everyone, JOIN question here

hi, i was wondering if it is possible to give access to an entire class of ip on mysql ? like 10.10.0.0 to grant access to 10.10.0.1-255

and your post is almost perfect. I can probably figure out what you expected, but a description of the expected output would be helpful. I see the actual output.

into a specific user

10.0.10.%

i'm writing a script to organise a games tournament. i have a table of teams (id | name) and a table of matches (team1_id | team2_id) - how can i write a JOIN statement to get the names for BOTH teams?

snoyes thank you gona test it

What's the best bet version for a new production server? 5.0.41 community?
er, 5.0.45

SELECT team1.name, team2.name FROM matches JOIN teams AS team1 ON matches.team1_id = team1.id JOIN teams AS team2 ON matches.team2_id = team2.id

thank you very much, i'll give that a try now

select software.machine,count(software.machine),count(sw.machine) from software,sw where software.machine = sw.machine group by software.machine; it just sits there and gives no results.

is there a pastebin for this channel?

pastebin

3 children and each of those having 2 children, and choose depth 1, then i want the 3 children

try http://pastebin.ca or http://pastebin.mysql-es.org

err depth 0 that is..

http://pastebin.ca/636103

select machine,count(software) from software group by machine; works and select machine,count(software) from sw group by machine works

How can I do something like that?

worked great, thank you again.

delete quotes2 from quotes2 join quotes on…

that doesn't even look like valid syntax …

try it and see.

delete from quotes2 join quotes on quotes2.quote = quotes.quote; ?

no.
!man delete syntax

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

what is wrong with this? select sw.machine,count(sw.software) as sw,count(software.software) as software from sw,software group by sw.machine;

There's no join condition. You get cross join/cartesian product. Lots'o'rows.

So node.xar_cid = 1 is the chosen root node of your search tree and HAVING depth = 1 indicates that you want the root node and 1 level below?
I just want to be sure I understand your exact conditions.

correct and for every + 1 i want 1 more level
and if depth is ommitted, then i want the whole thing

Right, from the chosen root.

the pid field will be useless if this works out

the manual states that when you use FLUSH TABLES WITH READ LOCK at a global level, it is not released until an explicit UNLOCK TABLES is sent
does this mean I do not need to keep a session open to keep the locks in place

Yes. left and right are meant to replace that.

so i load the article, get the node marked as root for that objectid, and then use it

i.e., I can pipe the command to mysql at the beginning of my script and end?

!man insert select syntax

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

!m NeosVortex insert select

NeosVortex see http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

i.e. echo "FLUSH…" | mysql …; do stuff; echo "UNLOCK TABLES"

Xgc, the current approach is hybrid, and the maintainer quit

tree per object

guess not

^chewie: For sh or bash; (printf "blah;\n"; cat file.sql; printf "end;\n") | mysql -options dbname

how to run the phpmysql

If I have a 'reservation' that contains 'byID' and 'forID', both which point to an ID in the 'person' table, what's the best way, in SQL (not in my code) when doing SELECT * FROM reservation and I need to join against 'person' and return p.Name when byID's join returns a value, and if not,
forID's .Name ?
in otherwords I want a conditional join, I can't choose the data in my application, I have to choose it within the SQL

Just return them both, with two joins against the person table.

like I said, the app needs 'name', I can't alter that
so if forID would return '', then I want the value that could come from byID

I am trying to use group by and order by but it would seem 1 is cancelling the other — any idea's?

rhelik, use the if function

i still haven't quite grasped the concept of the insert select, if I have a table named request and a second table named backup how do I copy the entire entry where the rid = 5?

Your description isn't complete. Include your logic for using byID or forID conditionally. If this is the result of two outer joins, use COALESCE() to pick the non-null id and join that with the person table.

SELECT c.*, d.* FROM `columns` c, `columns_data` d GROUP by d.cid ORDER by d.id DESC

'', (SELECT p.name FROM person AS p WHERE p.ID=forID), (SELECT p.name FROM person AS p WHERE p.ID=byID)) FROM reservation

the d.id DESC does not work ?

rhelik, do you know of table aliases?

forID and byID will ALWAYS have value in the reservation table, but the 'person' entry will sometimes have an empty 'name'
person AS p

If you have two ids, one of which is non-null, COALESCE(id1, id2) will return the non-null id.
Use that in your join with person.

well COALESCE treat '' as null?
will

see seekwill

No.

I'm pretty sure the app inserts ''

Then use CASE / WHEN.

i still haven't quite grasped the concept of the insert select, if I have a table named request and a second table named backup how do I copy a single entry where the rid = 5?

or IF(). Either is fine.

how do i find the ip address of a database?

well do you know the hostname?

yeah, localhost

hah

and the port

where do you want the IP address, in your code, or within a SQL query?

databases don't have IP addresses, database servers do

cute but you know what he meant

but i don't think he did
thus why i said something

i am writing a facebook application, and they ask for it
i will just give them the ip address of my site

yea, it will be the same

I'm assuming its not possible to insert into multiple tables all at once is it?

no
Could try a trigger

I need help with a forum type query…anyone?

if it's possible to insert into a VIEW, you could

seekwill, thanks actually its just for the final transfer of data from the legacy system

but why not just do mulitple inserts

rhelik, actually that's what Im gonna be doing I just wanted to cheat cause its a lot of records

heh final, usually data transfer is at an early stage

and yes I did think about using views also
archivist, unfortunately we have to run the legacy and the new at the same time

then messy scripts ftw

so ive created it a module at a time..that data which the new system isnt ready to create yet..we bring over each night

even if you could, it wouldn't be faster

but this next step will be the last time I have to do it

i have a unique col set to a default value which is obviously not unique… i can't do an auto_increment because it;s not a primary key.. is there any way around this?

rhelik, ah I assumed it would be

nah, if it supported it, it would probably just be a loop of INSERTs
but it wasn't a bad thing to consider, ideally if MySQL supported it, it's because they could do it faster than using 2 or 3 INSERTS by you

well i have been up for a couple days
so my thinking is …thinning
by its not all bad..my belly is getting fatter!

well then backup before you mass insert

rhelik, no need..cause the only data I import is the data they dont change…the stuff they are alrady doing with the new system was deprecated from the legacy prior
just one mod at a ttime…butnow a bunch of modules are ready

how would I eliminate duplicates from a table?

very carefully, I would try looking into a sub select (DELETE actually)

delete dupes

If you have a unique ID and the name may contain duplicates then DELETE t1 FROM table1 t1 JOIN table1 t2 ON t1.idt2.id AND t1.name=t2.name if you have other fields that need to be taken into consideration extend the join as needed

perfect. thanks

ya know that dude always says the right thing..its like he's memorized it

That's the proper behavior. You need to specify the value for each insert. Using yet another auto_increment field isn't really appropriate, even if allowed. Maybe you've chosen an improper primary key.

dude? it's called the_WENCH.

dude is a generic word like she is a generic word
"when god created man
she was only kidding"
stuff like that.

dude is not a pronoun

"Dude" generally refers to a male…

she remembers it in a mysql database

insert if not exists into tbl?

insert ignore
and have a unique key on the thing that exists

and if it exists, will insert_id() give me it's id?

anybody here use the mozilla calendar add on (Lightning) for Thunderbird, man I got beta v.7 and it's really sweet

hey guys anyone know how to show the code used to create a table?

!man show create table

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

you mean you have a table and you want the SQL to (re)create it?

yes

SHOW CREATE TABLE tablename I think

thanks guys

don't forget to thank the bots!

fuck the bots
oh sh.t, I didn't even see that
beat me to it

lol
oo is that against the rules here?
or is that php

has anyone ever tried doing a Mysql+DRDB+HA with replication?

DRDB?
Not sure if replication is considered HA…

has anyone ever tried doing a Mysql+DRDB+Heartbeat with replication?

What's DRDB?

Distributed Replicated Block Device

Have you looked into Cluster/NDB?

yes.. but its till using memory… im waiting for 5.1

can anyone give me some help on why this isn't working? I'm trying to get data from 4 different tables, they all have a forigen key in commmon and i'm trying to get all the data that links the tables together through the foreign key, here's the code used to create and my select statement:
http://rafb.net/p/Qbsr3w78.html

lokieee, check your data

I did and I get lost seeing how they are all communicating with eachother
thats why I thought this might make the connection clear
i think its the connections table that is screwing me up….the referencing of the entityId key I don't understand, zEndpoint or aEndpoint both reference the entityId in entity, thats why i used "or" so if either match

SELECT c.ID,c.name,s.step FROM f3s_cats c INNER JOIN f3s_cat_struct s ON c.ID=s.slave ORDER BY s.slave ???

If any tables may not contain matches, you'll need to use an outer join.

how do i take the average of x number of columns from a particular row

It uses "filesort" and "temporary"

SettlerX, indexing probably

thanks Xgc i'll read up on outter joins

cat_struct table has only 3 fields with "int" values so i don't know if making indexes in this table has any sense
however in "cats" table i have primary key on ID field

Otherwise, the inner join you used will only show results where all joins are successful (they all find matches).

What do you think? 2 queries or one JOIN will be faster?
or subquery? but how?
i have to get "step" from cat_struct and ID, name from cats.

Also, are you sure you have entries where connects.aEndEntityId = connects.zEndEntityId

2 selects is faster than join ?

actually no that will never happen

I have a feeling you're missing a join against entity.
Ah. Then your SQL can never return results.
You need to join entity twice, once for aEnd and once for zEnd
The same entity will never match them both.

yesterday, a 250,000 row update across 4 tables took over 10 hours.. i cancelled it, made a primary key out of the column i hunt for in my UPDATE, and it took less than 2 minutes.

-or- use an OR expression.
With proper ()'s

ahh oaky thanks!

It's always the last place you look.

if I change listen port in mysql coniguration, does it need a complete restart or a reload will do?
configuration*

Probably a restart. It won't hurt to try a SIGHUP.

if you change anything in the configuration you need to restart
the my.cnf

ahh ok..

Not according to documentation.

how do i take an average of, say, colA, colB, colC in one particular row?

according to the source sighup calls reload_acl_and_cache()

That's all? ouch.

it will flush logs, privs and tables
and query cache

how do i say not like ?

not like.

that isn't working.

what is SysDate-(1/24) exactly (ORACLE) and how do I make the mysql version?

where like … or like … or like … and not like …

!man operator precedence

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

hmm I am unable to compile mysql version higher than 5.0.41, everything fails with "make[1]: *** No rule to make target `@MAINTAINER_MODE_TRUE@', needed by `Makefile'. Stop."

is there a way i can limit the amount of records somthing prints out?

there is a limit clause for select statements in mysql
(also for other statements, but they don't print out a lot of stuff)

ahh thanks i see it now

can anyone point me to some good join tutorials?

joins

http://hashmysql.org/index.php?title=Introduction_to_Joins - For more indepth info: http://dev.mysql.com/tech-resources/articles/mysql-db-design-ch5.pdf

SELECT NOW() - INTERVAL 1 HOUR;

the_wench, thx

so? 2 queries or 1 join?

hmm I think I'll file this compile thing as bug report

Hi! I was wondering if InnoDB offers anything like MyISAM full-text?

lucene

Searching through a lot of text? Want to do it like someone who knows what they're doing? Use an independent indexing/searching engine like Lucene. http://lucene.apache.org/

you can use external fulltext search engines, sphinx for example

hmmm, is it complicated to implement?

more effort than myisam fulltext, but better results (faster)

if i'm not searching through a lot of text?
just varchar columns?

how do i take an average of, say, colA, colB, colC in one particular row?

SELECT (colA+colB+colC) / 3 AS avg [..]

well, what if one of the columns is NULL, then i want it / 2

ok that makes the thing more complicated

Can anyone give me a suggestion for retrieving a list of the most recent entry for each user in a log table… if I had an ID field and a date field, I want to show the most recent item for each ID

ah

groupwise max

http://jan.kneschke.de/projects/mysql/groupwise-max/ http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

if i'm not searching through a lot of text?
just varchar columns?

Just use LIKE?

slow.

for simple varchar columns?

yes!

Is it good practice to always use ASC/DESC together with ORDER BY?

you can't use an index with like if you have wildcards on both sides.

It seems really bad to leave it out.

sphinx looks mighty complicated to implement

well nothing is free…
either you get slow performance or you need to invest some time
so if you need 4 hours to build it, it might make more sense to wait some seconds for your data everytime

Or you can get MySQL Enterprise (R) today with a 25% discount!
Is it good practice to always use ASC/DESC together with ORDER BY? It seems really bad to leave it out.

Teh default is well defined by the standard. Leaving it out for ASC is common / acceptable.

Hrm.
I don't like it one bit.

It's guaranteed by the standard and all databases (that matter) recognize it.
You don't need to use the behavior.

I mean it makes the code hard to read.
BTW… ASC/DESC seem reverse in Windows…

Not if you know the behavior. But that's really your choice.

With the arrow pointing down, I would expect A-Z, but instead it's Z-A.

Can anyone give me a suggestion for retrieving a list of the most recent entry for each user in a log table… if I had an ID field and a date field, I want to show the most recent item for each ID… I just can't think of a _good_ way of doing it

already did
groupwise max

http://jan.kneschke.de/projects/mysql/groupwise-max/ http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

Sorry, my client timed out
I couldn't tell if my Q even went through - sorry

excuses excuses

'A' 'Z'. That's why DESC (top to bottom) would be down arrow.

=Z
Makes no sense…

It's an english term.

Descending (DESC), arrow pointing down, should show A, B, C… Z.

lol

Like you're diving into a hole with Z in the bottom.

Do you think of going down when traversing from A to Z?

And A is the beginning point.

Do you think of going down when traversing from 1 to 100?

100, 99, 98…
Hmm… I guess I see your point there.

Correct.

But it looks strange when we have letters…

Would it help to imagine that you're just using base 36, and don't happen to have any values 9 or less?

Yes. It makes sense to me now. I think.
But GRAPHICALLY, it looks wrong.
Visually.

They're just numbers in disguise.
That was to you, Mr. 96.

# You're the numbers in disguise… oh, yes, you are… #

No rule to make target `client.o', needed by `mysqld'. Stop.
funny again

I discovered the first (verified) bug that affected me yesterday.
And not only that, but I upgraded without problems.
Also for the first time.

I hate it.

What are you doing?

compiling mysql
well sort of
not really compiling as there are always errors

Freshly or updating?

freshly

What OS?

Linux

What distro?

Debian etch

Through "ports"/packages or sources?

with source deb from unstable distribution as I want most recent version

Oh… why is that?
I never use anything but super stable myself.
Of any software.

and some special compile flags as there is many stuff in it I don't need

Doesn't sounds like your error has anything to do with your flavor of Linux.

I guess something is wrong in configure

Can I ask on what you people use unstable?

I won't ever use unstable on any productive system
one upgrade and everything is broken
funny thing is, I didn't use the –without-client.o flag…

if i want to take the average among 4 columns in a particular row, am i better off using a select 4 values and reading them in with my PERL DBI and doing the averaging in perl?

Testing. I've actually used Alpha in production, but there was a good reason for that. That was back when 4.1 was alpha and 4.0 and prior was really unusable from a database perspective, unless you NEVER had use for more than the most trivial selects.

The kind of problem you can't resolve via google

Snice 99% (or more) of the typical developers didn't know what a derived table was, it's understandable that people would be happy with 4.0 or less.

I have a field that's of the type 'text'

You will be better off learning about normalization.

hmm why the hell does it use g++ for co9mpiling

But I cant seem to get all of my data into it/out of it
It might have something to do with the newline characters in the text, but I cant be certain

hmm and there is indeed no rule to create client.o

what is the relationship xgc

damn it, whatever I do, it doesn't compile

Then no mattter how many samples you have, SELECT tbl.id, COALESCE(AVG(measure),0) FROM tbl LEFT JOIN measurement ON tbl.id=measurement.cat_id GROUP BY tbl.cat; — will always be right.
Sorry. That should be AVG(measurement.measure) to be clear

ah ty i will look into that. yes, i do expect to have changing number of columns in the future as well, so i woudl definitely profit from that

Using that form, your limitation of 2 or 3 or 4 values per row vanishes and you can now treat them as groups / aggregates the way you should.

thats quite nice

So
Am I using the right column type

Doing things like AVG(), MIN(), MAX() or COUNT() are now trivial.

And I dont understand why I'm having a hard time getting data into/out of it

DAY etc.?!?!?!?!???!!??!!?!?!!!!?!?!??!

?

XFS vs EXT3 - any thoughts?

file a feature request
XFS seems to perform better for most operations, but in mysql sense I think there isn't a large difficulty as you mostly read from large files
from a small amount of large files

I have a field that's of the type 'text', but I cant seem to get all of my data into it/out of it
Can anyone help?

Wow… it must suck to develop on MySQL when you have to document every little change you make.

is it possible to use a spit() type function in mysql on the query line?
ie, select * from blah wehre field=split('user.shit', '.');
so it splits on the period

no
p–: so if the filed is user.shit it should do field='user' OR field='shit'?

I have a field that's of the type 'text', but I cant seem to get all of my data into it/out of it, I get a truncation warning when I select from it, and I cant seem to use the mysql client to see it all.