dumper in perl, i can see where my problem is

'1' };

it's putting MAX(IN_PROGRESS) as the hash instead of IN_PROGRESS
i don't suppose anyone knows why… (that's probably a #perl question…)

SELECT MAX(IN_PROGRESS) as IN_PROGRESS

ah, so it's actually a sql issue …. i guess i'm showing my sql ignorance

No biggie.

k, well i sure appreciate everyone's help
cya

I've spent days trying to optimize this query, a simple X BETWEEN Y AND Z is taking 8s on a dual xeon 2.8Ghz with 2Gb ram, even though Y & Z are indexed …. can anyone help? http://www.pastebin.ca/665719

i had a pebkac, the tm-screenshots relations where inconsistent .. thanks again

hehe

can someone point me to some info on INT length? I'd like to make sure I make the field long enough to grow with the site
how big a number can 5 go to?

int is int (5) is a zerofill its still the same size

zerofill

The optionally defined number after an INT data type represents the display width when used in conjunction with ZEROFILL. Please refer to: http://hashmysql.org/index.php?title=Zerofill

bigint ftw

oh….I thought it had to do with how large the number can be

an unsigned int can hold 4.2 billion
That is the case the string types (char, varchar), float, and decimal types. But not int types.

snoyes..thank you

It's kinda our secret handshake. You are now allowed to join the "knows what the (number) after int fields really means" club.

Now that you work for them, hack the source code to change that.

so it doesn't really matter how big you make it? INT(2) or INT(5)

The only difference is if you have zerofill on, then '1' will display as '01' or '00001'

hmmm

Hello, I'm trying to query the database for some statistics, they are stored on a hour basis,, "date + hour" , what I would like to do is extract this information and display it in the format "date" meaning, "date + hour1" + "date + hour2" + "date + hourN" will become "date" ..Is this
possible through some nasty looking stored procedure or something?

snoyes++

A kick in the right direction would be most appreciated.

what about for strings, float and decimal types…..the # restricts how much you can fill it?

Yes.

so should userID be int() or something else?
in best practice, of course

how many users do you expect?

3 [ERROR] Can't start server : Bind on unix socket: Permission

lol - to start - 5 - down the road…we'd luv 2 million
;p

If you have separate date and hour, then it's just dateField + INTERVAL hourField HOUR;
mediumint unsigned holds 16 million

any reason to not use int instead of mediumint?

you save a byte per row

when do you use varchar as opposed to txt - actually, any good articles on this stuff so I can read about them? I'm kinda winging it now

bigint! \o/

Use varchar when it's under 255. Use text when it's over.

snoyes, I'm not quite sure I follow you. In order to reduce the amount of data retrieved from the database I would very much like to do some addition on the values stored in the statistics table. so I would only get one row per day, instead of the maximum of 24.

thanks again

svearike, sum() etc

Oh. So you want SELECT dateField, SUM(values) FROM table GROUP BY dateField;

snoyes, yes exactly, the only problem is , I haven't got two separate fields for this, also I would like to be able to SELECT weekField (if you get my drift)
I'm thinking along the lines, temp-table + loop, but that seems a bit overkill considering I don't even know how to do loops, hehe

whats standard (if there is one) dd/mm/yy or mm/dd/yy ?

What is the field type of your date + hour table? Is it a datetime, or something else?
yyyy-mm-dd

really?
sez who?

Yep. In USA, mm/dd/yy. In Europe, dd/mm/yy

snoyes, it's a timestamp with the minute + second part stripped out (substr during creation)

I had no idea

The date field MySQL uses is yyyy-mm-d

I like YYYYMMDD

yy-mm-dd is the best. Don't know why we don't all use it

but mysql does use yyyy-mm-dd

You can SELECT DATE(dateField) AS myDate then.

but you just said yyyy-mm-dd is standard

MySQL doesn't much care about the delimiter, so you can use yyyymmdd or yyyy|mm?dd or yyyy:mm%dd if you like.

it is for mysql
doesn't mysql return dates as YYYY-MM-DD?

yes

is there a way to change the delimiter?

snoyes, I'm not quite sure that I follow you there….

SELECT DATE_FORMAT(theDate, '%Y?%m:%d')

hi

snoyes, aah, you mean DATE() will remove the hour part?

yes, it will return just the y-m-d part

is it possible to force php_mysql to use networking for localhost connection instead of a socket?

127.0.0.1

Why don't you want to use the socket?

snoyes, ok, that's great, but that's just for selecting 1 day more or less ? I would like to retrieve a resulstset containing several days (am I making any sense?)

Then you just change the function you use to whatever is appropriate. If it's a whole week, something like SELECT WEEK(datefield). If it's not a standard function, you'll have to build it yourself, maybe using some combination of MOD() or something.

snoyes, aah.. now I understand. select DATE(date) as dd, sum(total_views) from ad_stats group by dd; that worked like a charm for the "on a day-basis"
snoyes, thanks alot for the help

You're welcome.

hey friends

Hi Rick!

would someone care to help me exploit my database, injection attack?
http://www.ryanpartington.com/format2.php?id=99 the page, explotation the game
i don't understand why if I put ?id=''OR 1 this does not fireback all my rows, I have a repeat region setup

Is that your site?

yes

Proof?

one sec
http://www.ryanpartington.com/img/1111.JPG

lol
To answer your question, you need to look at the code to see how it treat GET variables.
What should happen is that it checks to make sure id is a numeric value

does anyone know since when MultiMaster replication is possible in MySQL ?

ahhhh I think it does yes http://pastebin.com/m2764ca4
but I do use text values else where, hummm

Well, I'll let you figure out the coding part of it. I'll just help you with the overview

that is fair

require_once('Connections/feelthelove.php'); ?

When you receive GET or POST, you should also check that data as well. If you're expecting an abbreviated state, the text should be two characters
If it's more, maybe someone is doing something bad.

it's possible from 5.0

tought so

A username in your database is VARCHAR(20) perhaps. So when people log in, make sure the username is no longer than 20 chars

good practice stuff, i like
for my articles I use text queries, to help with search engine rankings, ie http://ryanpartington.com/article/linuxvswindowscomic/ - linuxvswindowscomic/ is dynamic, any tips here?
ping

you spelled 'depends' wrong.

echo response

i know, HEHE it's more of a proof of concept
anyone else fance a shot at exploiting?
i can give away some details free of charge
today only speical offer
ci*
:O

Hello, i have a table called company, where i'm trying to select company_type and company id. I have made this (SELECT… WHERE company_type = 1) but i need to extract also the company with id = 1 (and that company have not company_type = 1)

do you mean WHERE company_type = 1 OR id = 1

snoyes, no i need where company_type = 1 + the company_id = 1

thehcdreamer, OR ?

snoyes, but + the other company, not OR

so AND

gnari, no i need booth

thehcdreamer, what is wrong with OR then ? in what way does that return wrong results ?

snoyes I know you don't care, but could you spend a single seconds looking @ http://pastebin.com/m2764ca4 and just let me know if im WIDE OPEN for explotation ?

Are you just those rows where both company_type = 1 AND company_id = 1, or after all the rows that have company_type = 1, or have id = 1, or both/

snoyes, i need company_type = 1 and also i need the company with company_id = 1

lol

in other word, i need to have 2 conditions

So in other words, where company_type = 1 OR compand_id = 1

seekwill, no, i will make an example

I don't see any obvious attack openings. I do see a hint at some unexpected behavior in the case that you want those rows where id is NULL.

i thank you

comany, that have this records: orange, pink, yellow, white, assumingthat orange have company_id =1 and white have company_type = 1, i need to extract orange and white

im going through it all now line for line. this is just a demo form which is similar to my live stuff.. so the null issue wont be a problem, thanks

How about try the suggestions given to you?

seekwill, AND?

No, OR

thehcdreamer, OR

Do you know what "OR" means?

ok i'm trying
seekwill, my english is bad I know
:;

;:

thehcdreamer, bad english is OK, but if you do not undserstand "OR" you should not be in computing

gnari, i thinked Or means that select orange OR white but not both

should we have a "maybe" for the user who is not sure

IIRC, that's XOR

seekwill, thanks i'm going to learn something about basic english thanks to anyone
useekwill, thanks i'm going to learn something about basic english thanks to anyone/u

That's not basic English.
Logic operators

seekwill, I know, i was confused with XOR

||, U, OR

do you need anything like mysql_real_escape_string for a date field?
or does date() take care of that?

is there a way to output a list of all tables and the engine that they use?

$a = ') OR 1 IN (1'; SELECT * FROM table WHERE datefield = DATE($a);

THANKS GUYS

don't forget to thank the bots!

does a while() have to have an else?
nm

!MAN JOIN SYNTAX
user syntax inner join in mysql ?

!man join syntax

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

!man show table status

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

^

any suggestions on how to delete 3GB of records from a 15GB MYD table without mysql locking/slowing down at some point in regards to handling requests for other dbs/tables? I've been putting off the maintenance since it always interrupts live service

delete some, wait, delete from more …

dkr use a script with a delay so it spreads the deletes over time

create a table w/ records you want, point everyone at that, drop the old table?

hey
why is mysql only running as 1 process?
and how can i get it to multithread?

hrm, won't that mean it has to keep rebuilding index for each chunk I delete?
I'll have to experiment with that. probably just as much I/O creating the new 12GB MYD, but might be more friendly to the other mysql threads

dkr some yes but depends how much you delete at a time

I am speculating aloud, I cannot predict if it would be helpful.

ombling, you want both multiple processes and multithreading? is that not just greedy?

do I need the {} on this? echo "{$row['event']}";

mysql does not have echo

it doesn't?
then why does it work like print()?

is this #php?

oops, sorry
;p
can you order by date()?

but the answer is yes anyway
and yes

gnari i just want more then one process
gnari so i can use all cores and speed up mysql

multi threaded query execution?

hello
does anybody know any problem about mysql 5.0 into debian ?

http://paste.lisp.org/display/46504

i have just install it and it don't run fine

there are only a handful of rows, i.e. well under 240,000, that correspond to the three facility IDs in the where clause
… so why do i seem to be losing data that's not in the facility table?

marcfp, what version debian?

avium, there is data your where is excluding

lenny

marcfp, that is testing, right?

yes
yes, it's true

what packages did you install?

hmm I was unable to even compile the packages in testing for mysql
but maybe that's because I'm still on (old)stable

archivist, that makes sense..
i'm not sure how to fix that, though.

those : libdbd-mysql-perl, libmysqlclient15off, mysql-client, mysql-client-5.0, mysql-common,mysql-navigator, mysql-server, mysql-server-5.0, php4-mysql, php5-mysql

i need to go through my mysql tables and replace a funky character–is there a simple way to do a find-and-replace like this/

marcfp, what do you mean by "it don't run fine" ?

mysql don't run when i start my computer

GROUP BY facility.facilityid; would help to explain the results.

what's your first language?

(facility.facilityid NOT IN (1000,1001,1002) OR facility.facilityid IS NULL) — seems to work

killall -9 mysqld && /etc/init.d/mysql start, then it runs well

but i don't like that at all

catalan

que es tu problemo?

for which reason you ask tu me ?
a
mmm

curious

Perhaps that facility.facilityid IN (1000…) belongs in the join condition (the ON) instead of the WHERE.

snoyes, ah, that's an interesting idea
thank you

cuando inicio el servidor, el mysql no me funciona, tengo que matar-lo con un killall -9 mysqld i luego volver a iniciarlo con /etc/init.d/mysql start i luego funciona bien … si no lo hago asi, no hay manera de que funcione

http://bugs.debian.org/cgi-bin/pkgreport.cgi?pkg=mysql-server-5.0;dist=unstable

i'm using testing …
lenny is testing, no ?

marcfp, yes, but that is the same version, i think
marcfp, does this look like your bug? http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=418685

hmmmm

one moment, i look …

http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=411672

the first one i don't see anything

hi

hi wfq

I am executing a query to know what emails are duplicate in my table so that I get and put them into another table

marcfp, maybe you should try the debian mailing lists. personally i do not use testing , because things break once in a while.

insert into duplicate_email(email) select distinct email FROM signup;
but this is copying even the emails which appear only once

http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=411672 -here there are my error

marcfp, for a server use debian stable. for your own workstation, use ubuntu, which releases 2 times a year

this is caused for the distinct clausule. Does anyone know how I could do that?

hi, whats the usual mysql port number?

3306

thanks

hi

i don't like ubuntu
i'm using debian since 6 years ago

in phpMyAdmin, if i specify INT with size of 10, is it same as BIGINT?

marcfp, debian is great, if you can stand getting a bit out of date in between releases

sorry

debian comes in 3 flavours…

if you can stand getting a bit out of date in between releases

rusty, stale and broken

marcfp, i meant debian releases a new stable very infrequently

every 30 years or so

for this reason i use testing

nils_, stale maybe, but not broken

ever tried running the unstable distribution?

nils_, i have considered it, but i am not hacker enough
nils_, in fact, i think i would rather use unstable than testing

yeah, well, back in the old days when I was employee at a small company, they ran unstable on some servers. And there where days when someone installed something or upgraded something, everything broke. But back then, woody was stable

no

nils_, if things get broken, they are more likely to get fixed faster in unstable. it takes time to propagate to testing

int(10) jus erfers to the "display size" not the actual size of the number

indeed. I rather run stable though and backport backages. In fact I even have sarge on some hosts because there is no need to upgrade (only more to break)

does anyone know how I could get the duplicate rows for a given field so that I can drop them into another table?

nils_, of coure i would never use unstable or testing on a production server

!tell wfq about dupes

wfq find them with select count(dupefield) as qty,otherfields from table group by dupefield having qty 1

thank you for all guys
bye

see ya

why does the wench's example mention otherfields in this situation? why not select dupefield,count(*) as qty from table group by dupefield having qty 1

snoyes, i'm still having trouble with this query; the problem is that there are rows in external_facility that don't exist in facility, so if i modify the LEFT JOIN condition, it doesn't make a difference insofar as the number of results; all that happens is i get NULLs in some columns
instead of acutal data

gnari, because your definition of dupe may mean a combination of fields

… and i don't see how i can modify the JOIN condition effectively

hello. I have a couple of dozen thousand sentences. how could I list all the sentences that have specific word(s) in them? I want it to be efficient

readme, use a fulltext index

I'm not sure how to store each sentence. if I store it as text and do LIKE won't that do linear search?
archivist how efficient is that

avium, is the paste you made earlier still current?

yes gnari

readme, reasonable for you data size

is it linear search?

no
proper index

avium, and what is the result you want?

when configuring my MySQL server, it asks for a Port Number to enable TCP/IP networking. what port number do i put in? or do i leave it as default (3306)?

avium, more the "AND facility.facilityid NOT IN (1000,1001,1002)" condition into the ON clause

leave as default unless you have reason not to

SELECT COUNT(1) FROM metric_oscar_data1 JOIN external_facility USING (fpn) LEFT JOIN facility ON (external_facility.fpn = facility.fprovidernumber AND facility.facilityid NOT IN (1000,1001,1002)) WHERE metric_oscar_data1.metricdate BETWEEN '2002-02-28' AND '2004-02-29'

is mysql 5 backwards compatible with 4 by any chance?

ok

test

'tést'

SeaHawks7, use #test for that

are there known issues when dumping a 4.1 base out to a sql file then trying to load 'er up in 5 with mysql empty-dbase dbase.sqlfile ?

sorry

gnari, i want data for every row in external_facility whose facility ID is not 1000, 1001, and 1002

heh that was an old charset test in the wench from months ago

the problem is facilities in external_facility don't have facility IDs

i'm getting error #2003

facility IDs only exist in facility

do I need to forward post 3306 on my router?

i'm thinking i may have to do a subquery

or just disable windows firewall?
**port

I keep getting syntak errors when I try to load up the database

!error 2003

avium, did you try my suggestion?

the_wench help

!error 2003

flung perror is only low numbers
GBenemy, whats the other number

mysql also supplies error messages with the 4 digit codes.

gnari, yes

I never understood why mysql has two error numbers

10061

thats a mysql permission error

the LEFT JOIN tells SQL that the leftmost table takes precedence, and that results should be returned even if corresponding rows don't exist in the right tables

Error No. 2003
Can't connect to MySQL on server 'localhost' (10061)

… so i still get every row… the only difference is the rows i don't want have NULL data

avium is that not what you wanted?

interesting

!tell GBenemy about external

GBenemy remove bind-address= and skip-networking from my.cnf and grant permission to the external 'user'@'host' and remove any firewall rules blocking port 3306 and make sure no overrides on the mysqld commandline

mysql only has one rror number

avium, then change the LEFT joint to INNER JOIN

gnari, but then i only get facilities in the facility table

huh?

oh wait, that's only partially right.

… and the facility table contains a subset of those facilities that are in the external_facility table
so i lose rows that i want

there is one MySQL error number for the SQL Server and one for the storage engine.

so do i skip this error? or retry?

ah, ok

do i forward port 3306 on my router?

are there known issues when dumping a 4.1 base out to a sql file then trying to load 'er up in 5 with mysql empty-dbase dbase.sqlfile ? The errors I get are always whatever the first line is.

GBenemy, or that yes

ok

I've tried deleting the comments out. I'm at the CREATE TABLE line now and it to is throwing the error

i suppose i should have made it clear that there's a 1-to-1 relationship between facility and external_facility, but it's not "ONTO"
i.e. not every facility in external_facility has a match in facility

avium, do you also want the rows in external_facilitythat do not have a match in facility ?

gnari, yes, but i don't want the rows in external facility that have a match in facility whose IDs are the ones i mentioned above

Hey guys, where are the logs located for mysql-server5 on feisty? The server doesnt want to start up.

Is mysql capable of telling my how many times a letter occurs in a string in a field?

how this standart name from um foreign key?

!man string f

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

/var/log/mysqld.log perhaps

nyk2005, yeah. /var/log

avium, so if a row in external_facility has 2 matches in facility (with facilityid 1000 and 2000), do you want one of there in your result or none?
s/there/them/

gnari, a row in external_facility will never have 2 matches in facility; there's a 1-to-1 relationship between external_facility and facility, but it's not onto, meaning that every row in external_facility will have either one corresponding row in facility or no corresponding rows in
facility

I think I've possibly hit a bug in mysql, but it's really obscure and hard to reproduce, except in my setup… I'm willing to shoot some questions your way to eliminate possible ignorance on my side.. So here goes…

if a row in external_facility has no corresponding rows in facility, i want that in my result
if a row in external_facility has one corresponding row in facility, i want that result UNLESS the row in facility has the IDs specified above

SELECT SUBSTRING(col1, col2, col3 - col4) JOIN .. JOIN — all columns are from different tables added in a JOIN

avium. ok can you give me a concrete example of a row that is missing from the output of my suggestion (IN () in the ON())

how is the standart name from foreign key when i not set the name from relationship ?

so I have this situation where col3 - col4 is the right sum, except when col3 and col4 are the same number, and the sum is 0

gnari, no rows are missing in the output of your suggestion, but i am getting extra rows

avium, ok. can you give me a concrete example of such a row?

Can i tell Mysql to remove only one of two o more duplicated rows?
I mean the first one that Mysql is able to find

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

gnari, hang on, i'm working on it

is there any way to avoid temporary tables when a join uses an ORDER BY with columns from multiple tables?
doesn't really seem like it, but i thought id ask

why do you ask shadfc?

because i've inherited an app that uses a query all over the place that does just that

gnari, see http://paste.lisp.org/display/46507

hmm I don't think so
you might benefit from allowing larger temporary tables in ram

gnari, as you can see, the top query contains a row whose fpn belongs to a facility whose results i want omitted

joins should be very fast on their own. have you looked at the processing time of the SQL? is it really slow?

nils_ is that just tmp_table_size? i thought i read somewhere that max_heap_table_size has an effect too (mysql 5.0.45

avium, i will be away for a few minutes. i will be back

yes. max_heap_table_size affects every table created with memory_engine, tmp_table_size affects temporary tables in ram and is limited to max_heap_table_size

I think I must be having a senior moment… can someone explain this to me? http://www.pastebin.ca/665890

stpierre, what version

5.1.20
MySQL Cluster — the table is using NDB storage

anyone played around with blob streaming recently?

stpierre, I'm guessing career_commit is not a varchar

thank you.

you're welcome

SELECT fpn , facility.facilityid FROM ….

stpierre, how do you like using cluster?

career_commit, it turns out, is an enum. does anyone know if that would ahve worked in a previous version? this app was functional (AFAIK) on MySQL 4.something

hi, how do i add remote access to mysql?

it's still a little fragile, and has some quirks that need to be worked out
but it's a helluva lot cheaper than Oracle RAC

SELECT facilityid, fprovidernumber FROM facility WHERE fprovidernumber ='f182760b'

hehe for sure. I haven't used cluster yet, but I've been meaning to try it out

i'm not sure how useful it is (yet) as a general-purpose high availability solution, which is too bad

stpierre, if it was me I'd make a backup of the data and then convert the column to a varchar. could be a simple solution if it worked and retained the data (it should). not sure whether you could ever use string comparison wildcards on an enum.

it's a custom app (written by a long-graduated student, wheeee), so I just hacked the app

gnari, it returns one row from facility

hi, how do I tell mysql to return all records in the last half hour? I have this code: select sum(download),sum(upload),IP from accounting where date group by IP;

ok

stpierre, "before MySQL 5.0.2, ENUM and SET columns do not provide true constraints on entry of invalid data"

guys

!man date

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

thanks, trix

So, is mysqldump from a 4.1 dbase not going to load in a 5.x database? I read the mysql site and saw that people didn't have too much trouble.
I can't, however

hi, how do i add remote access to mysql? i cant find it in the man :/

my guess is 5.0 is translating those wildcards to literal percentage signs

!tell ttt- about external

ttt- remove bind-address= and skip-networking from my.cnf and grant permission to the external 'user'@'host' and remove any firewall rules blocking port 3306 and make sure no overrides on the mysqld commandline

I have installed phpMyAdmin, but when i run it, it says "Cannot load mysql extension. Please check your PHP configuration" on the phpmyadmin.net website, under this error, it says "This function allow you to access MySQL database servers. More information can be found at » http://www.mysql.com/." so i came here. I've been in the phpmyadmin IRC channel for about 15 minutes but no one has answered
please help

ok thanks

Is it a php problem, or mysql?

avium, but what about the other thing i asked?

that is a php problem
(GBenemy)

it says it's a php function
but it says i have to download a mysql package

read your php install documentation

ok

Is mysql capable of telling my how many times a letter occurs in a string in a field?

u probably need php-mysql installing, what OS is it?

?

XP

avium. i will be back in 30 minutes

gbenemy, I'm sorry

i have php and i have mysql, both of which are installed and running
ui have php and i have mysql, both of which are installed and running/u
why?
oh you weren't talking to me lol

cos u run xp

you need the mysql module of PHP.

sorry
oh
yeah i know
i want a mac
but it's my parent's computer i'm using atm
so shhhh

did you compile PHP, or install from a binary?

installed from binary
##php said it was a bitch to compile in xp
and i don't have c++

I'm sure it is

where can i get it from?

GBenemy, its just a php.ini setting

really?
so if i add a line into that, it will work?

I have no idea. I run PHP and MySQL on sensible OSes.

e

http://uk.php.net/mysql

which ones? i need a decent os aswell?
linux or mac?

gnari, it produces the fpn and a NULL in the facilityid column

uncomment the mysql extension and restart apache

i went there
ok

it should work on XP, I just haven't a clue how to make it work if you're using binaries
go ask in ##php how to get the mysql module working on a binary install of PHP on XP.

"MySQL is no longer enabled by default, so the php_mysql.dll DLL must be enabled inside of php.ini. Also, PHP needs access to the MySQL client library. A file named libmysql.dll is included in the Windows PHP distribution and in order for PHP to talk to MySQL this file needs to be available
to the Windows systems PATH"

ok

GBenemy, http://www.php.net/manual/en/install.windows.extensions.php
just what archivist was talking about.

this is what i found in php.ini ";extension=php_mysql.dll"
but it's not commented

yes it is

and I can't find a php_mysql.dll
; = comment?

yes

that's strange

howabout libmysql.dll

absolutel everything in my php.ini file starts with a ;

I need to get something like [ SELECT f1,f2,IF(type1, f3 as name, f4 as name) ]. How?

nope

defaults are good enough in most cases

don't have that either

Just like that.
except put the AS name after the )

do a search of your drive

snoyes, thank you

can you write mulitline strings in mysql?
multiline

sure why not

anyone know how to subtract 30 min from date()?

arthas, DATE_SUB( field or now() , INTERVAL 30 minute)

"Table 'mysql.proc' doesn't exist". How can I create it?

or just 'NOW() - INTERVAL 30 MINUTE'

stpierre that's nice simple syntax, but that looks frighteningly 5.0+

i think that works in 4.1

interesting

thx stpierre, thats cool

trix like how do you do a multiline string?
i dont want to go by \n

heck, it even works in 3.23

stpierre, nice. I've always used INTERVAL in date_sub/add etc
lbologni, what do you mean? can you give me an example of what you're trying to do? just describe it?

trix for some unfortunate reason the sw i'm hacking on has html code in the database
i want to clean it up and place it in the create db script

so you'd prefer if there were no line breaks in it?

INSERT INTO foo VALUES ('This is a multi-
-line value', 'This is also multi-\nline, but written on one line');
you can split lines at will in mysql shell. or you can add \n to put newlines in on one line of code

cheers man
trix i dont care as long as mysql can swallow it

lbologni you might save some time using phpMyAdmin or another interface to edit those values

trix guess you're right but can't even get it to create the db

ohh.. that sounds nasty.

brb

if a select statement returns more than one value, do this, else do that? ( http://rafb.net/p/u1UYcL42.html )
(it's mssql but i'm not getting a response elsewhere)

I would use a script to run mysql queries. No idea how to do it in mysql alone

fles, more than one record?

could someone tell me whats wrong with this pls? select sum(download),sum(upload),IP from accounting where date='NOW() - INTERVAL 30 MINUTE' group by IP order descending limit 10;

drop the quotes around NOW…MINUTE

still doesn;t like it

how can I list all stored functions?

hrm. what does it do?

order by ….
mysql.com/select, read it.

heh, i missed that too

reading

hi everyone, i have a problem w/ my SQL: http://pastebin.ca/665947 — can someone smack me and tell me what is wrong with it?
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
KEY `expires` )' at line 15

key definition missing?
KEY `name`(field1[, field2 ..])

greetings all. first time here. quick question. im looking to run this command: ALTER TABLE access CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; to all tables in my database, from within phpmyadmin. is there an easy way to do that? sorry if posting a command is taboo…

`expires` DATETIME, — isn't taht done by

what is the actual difference between char and varchar?

KEY `parent_pid`,
this is wrong.
KEY (parent_pid)
and KEY(expires)
or KEY `parent_pid` (`parent_pid`)

ah, thanks

thetank, I'd make sure you're using a recent version of pma and then use the "database view" to select all the tables, then modify.
js_, http://dev.mysql.com/doc/refman/4.1/en/char.html

looking for some insight on mysql deadlocks … anyone had experience with them here?

how can a function return multiple rows?

britt, yes

A little, what table handler?

it can't. a function returns a value

hmm EC2 is slow.

thanks nils_

just increment me

We're basically doing a large number of updates to one table (a simple linker) that eventually throws an exception as a deadlock.
What's the best way to mitigate this?

linker?

restart transaction

yea what the heck

use transactions and/or select .. for update

Yeah, thought transactions would be the answer.

It really depends what engine you're using, and what operation causes the deadlock?

it's an update of a 2 col table;

What engine? InnoDB?

hi,

InnoDB can deadlock a transaction of a single insert if you have more than one index on the table (and are unlucky)

I have this table where some email are NULL

What do I have to change in this function ("CREATE FUNCTION substrCount2(x varchar(10), delim varchar(1)) returns integer return (length(x)-length(REPLACE(x, delim, '')))/length(delim);") that works in mysql5 to use it in mysql4?

I have tried to delete as email=null | email=NULL | email='' | email=' ' but I am not able

Nothing, it will not work in mysql4, stored functions aren't supported under 4

yes

I've stretched my SQL talents to the end again
so I've come back to the well for guidance and light!

a pity… too bad my debian server used mysql4…

ok

how many bytes can a tinytext field hold?

You need to design your application for a platform that you can support

sami-boy: hi

hi abstrakt

or maybe that doesnt exist

I have a table of business names and addresses

js_, another question that can be solved by reading the manual

I would just have liked a function that can count the occurrence of letters in a (text) string… But I can probable just as well do it in a select statement, maybe

yeah, i used to find it instantly, but i cant find it anymore

!m js_ string type

js_ see http://dev.mysql.com/doc/refman/5.0/en/string-type-overview.html

theeere
thanks

sorry I was not using is null

The problem is that it isn't an unlucky scenario I think … we're able to reproduce it.

I have a table of business names and addresses
and I have a link table thta tells me which company belongs to which category of business

Well, there are some things you can do to reduce it, but it's not going to go away completely.

(we have over 200 right now)

Ideally your application should be capable of retrying transactions if they fail with deadlock

yes they retry 3 times by default

I thought it would be a striaght forward JOIN to look up a list of businesses in a given list of zip codes AND which of these are in a given category

You can use a named lock, or a table lock to avoid it in some cases

sami-boy: What makes you think it isn't?

This is of course far from an ideal situation as it reduces concurrency

well
if I run my query I get over 17k records returned
I only have about 14k in total!

sounds like you're missing a where clause

if I add DISTINCT to the query, then I get 34 records

and getting a cartesian product

I'm doing a left join and I would like for if t1.field exists then return it otherwise return t2.field how can i do this?

yeah.

I should only be getting 34 records
do I really ned DISTINCT tomake this work?

Athfa1, CASE() ?
Athfa1, or COALESCE()

it is null but what is COALESCE doing??
I am looking at it
SELECT COALESCE(NULL,1); ?? those aren't fields

sami-boy: No, you're probably missing a where clause

I have an ON, WHERE and 2 AND clauses

Athfa1, COALESCE(t1.field,t2.field)

COALSCE(t1.field, t2.field) ??
:P
thanks!

sami-boy: Yes, but you are presumably missing some part of your WHERE clause
hence ending up with an unwanted cartesian product

mmm
like I said, I thouht is was pretty straight forward
I have no idea what I could be missing
does the order of the ON, WHERE, AND matter?

sami-boy: Seems like you're after something like this: SELECT * FROM business JOIN categoryMap ON business.id = categoryMap.business_id WHERE business.zipCode IN ('90210', '12345') AND categoryMap.category = 'NuclearSecrets";

!man explain

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

yep!
right as usual!
thx

normalize
normalization

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html and http://datamodel.org/NormalizationRules.html and http://mysqldump.azundris.com/archives/20-Nermalisation.html and some here http://www.keithjbrown.co.uk/vworks/mysql/

good evening

Ping

I want a mysql cluster with multiple master servers - as I noticed there are possibly 2 ways to handle this. Number one is Master-Master replication which seems to be relatively easy in setup but not transaction-safe when having very quick connection-intervals. Number two would be nbd-clustering
which seems to be more complicated in setup but doesn't have this problem. What would you r
ecommend, or any other ideas?

that depends on your needs
easiest way is still r/w master and r/o slave, failover via promoting slave to master.

that's already prepared as my last possibility
i tried around with master-master

master-master replication is a nonstarter in almost every case
master-master replication is VERY iffy

yeah master-master has some catches

the problem is, when doing insert statements on both masters between milliseconds, the second request arrives sometimes before replication is complete

master master is cool, what can possibly go wrong?

and then the whole system breaks

autoincrement-offset?

was configured
for example:

but one writes only odd rows, the other only evens.. whats wrong with that?

i send in the same second a useradd statement and a userdelete statement

suppose if u get 2 requests to change the same thing it might not be good

if for some reason replication stops, you run into trouble

via 2 consoles, the add statement received at master1
and before add was replicated on master 2, the delete arrived there.

replication broken

also, locking is local to the server

even with innodeb, correct.

There are heaps of conditions which can cause master-master replication to fail, it is difficult to fix and a fault causes further cascading problems as they become further out of sync

I use master-master in one setup, with only writing to one master and failing over to the secondary master.

That sounds somewhat safer

yes that's the way it works for me,too but than master-slave is nearly the same, because changing slave to master is only one command

yeah with the difference that they can sync each other automatically.

You can get away with master-master replication if you can guarantee that you're never going to get an index or key violation problem
That can only really be done in very well controlled environments

whats a key violation?

so if broken master (sounds like drunken master) comes back it catches up with the secondary

and only the possibility that replication may break is enough not to use it

like foreign key or something

i c
what about nbd-clustering?

aww.. it's soo cool tho

well cluster can also fail and also has some catches

ndbcluster is sufficiently clever it can cope with these problems, but it has a lot of its own limitations
specifically, when we tried it, its performance was pretty awful

would you say that the transactions are safe?

speed and latency is an issue, you need more ram as indexes *must* be stored in ram and there are some problems with large transactions

ndbcluster is transactionally safe, but it needs to take distributed locks and stuff, which means that things are slow. Really slow.

at least for writes.

compared to a non-clustered database, it's very slow. We found problems even with reads.

(also taking into account select for update)
I agree

that doesn't sound any better than master-master replication