thats because in my local database the person with priveleges on the database is "root"
and the user in the website is b14_718346
so how do i solve this issue??
basically the question is how to take care of user permissions with mysqldump

/join #mutt

hello people
"Incorrect file format 'proc'"

How do you get this error?

when trying to execute a stored procedure
salle

the cookie monster!

repair table mysql.proc use_frm;
and the problem disappears for an hour
and then it comes back again

Is the site dev.mysql.com down now?
I am unable to access that.
The rest of the internet is fine.
Can anyone confirm this?

fine for me

hmmm

for me too

Do not know what the hell is wrong with my network!!!!

just tested the dns and its ok

Ok.. Any other place from where I can get the MySQL 5 manual?
oh I have it..
along with the installation..
I just added a new user account using the CREATE USER command,
The host of that user is set to as %
what does that mean?

any host. % is a wildcard, like * in Unix

oh.. okay
Thats fine.. exactly what i wanted..

hello

hi, all

is there a way to select a random item in query like SELECT n1, SUM(n2) FROM table GROUP BY n1, where SUM should be replaced by a commend electing random item ?
i tried with RAND(), but it seems to be imposiible
*selecting

i having a problem in a sting that have insert into xyz (f1) values('R-Vision 32' Condor (Unit 5504)')

Why doesn't the following work?: UPDATE `phpbb_users` SET `username_clean` = LOWER(`username`);

What does it do?

how to insert with ' qout

escaped it

\'

whats wrong?

how i applied addslashes($str);
but id not working
its*

It gives an error: mysql said: #1062 -

near where?
lower?

how do i escaped the simpe qout

it doesn't say, I'm guessing so yep

just add a \ before the '

I start my mysql with –log-slow-queries. I was use this sintax "./mysql-server.sh start –log-slow-queries=/usr/local/www/apache22/data/log/mysql.log" This is correct?

but its a dynamic values from csv file i applied addslashes to it
but its not working

or is there any way to select a raandom item for each distinctive field ?

hello, i'm looking for an article to get some informations about how big a mysql table can get (on a standard server, without much optimisation). can anybody give me a hint?

how big you need?
and what storage engine

am i right that a table used almost only for read can get with no problem to 100'000 rows?)

I'm using phpMyAdmin and it doesn't tell me where the error is

for now it's innodb… but i can change to myisam.

index is the key no matter how big the table is
even millions rows is ppossible
you sure? provide the complete error you get

it's about an exchange rate table and it will be searched by two currency ids and the date

yep, http://rafb.net/p/w7SqBz42.html

would you suggest to create an index on that 3 columns?

possible, depends what you search

Hey . Thanks for the reference to 'prepare'! This way it works just as I need it !!

the documentation says error 1064 is: Error: 1064 SQLSTATE: 42000 (ER_PARSE_ERROR) - I have no idea what that means really

a_l_e, make indexes to match the queries

works if you spell 'UPDATE' correctly.

i will search for the exchage rate for one date and two currency ids!

"UDPDATE phpbb_users" must be UPDATE

what is your primary key?

oops, doesn't work with "UPDATE" either - sorry for confusing you
http://rafb.net/p/JgQerb56.html

there is an extra field for the primary key. but i could change to a composed key if needed (but i don't know if a date can be used as part of a primary key)

strange it's the same error though

1064 1= 1062

update and insert may cause index reorganization

oh,

1062 is duplicate key error
you must have a unique key defined on username_clean

hi everyone!

1062 - Duplicate entry

i should never have updates, only delete and insert and those will happen six times a day

eep - thanks guys that solved it

Hopefully there is some sql guru here I've got a table with: name, date_start, date_end. Now I want to get the "names" for a given date.

the goal would be, to have the insert happening in max 2 seconds each and the read being faster than a second.

then you have to benchmark it
maybe you want to take a look at myisam concurrent insert, and merge table for faster delete, keep more records and drop 1 whole table

SELECT names FROM table WHERE $theDate BETWEEN date_start AND date_end;

insert and delete will never be concurrent.
i don't understand which table drop i should look for
… and yes, i think that i'd better just benchmark it… it doesn't have to be that difficult!

thanks, but I incorrectly formulated my question.. sorry… I've got two dates as 'input'.

roxlu_, and the table has two dates as well?

yes

ok. And you're looking for what? Where they overlap the range in the table? Completely enclosed by it?

I'll make a paste with an example
yes

yes to which?

to you
http://paste-it.net/2856

ppl, what is method for db backuping that store unicode characrters ?

2007.01.04 and 2007-08-31 .. Than I want to return "super" and "duper" as names

$start

Thanks

hi,

would it also be possible to get a list with the days and the name into which the given dates belong?

Meaning you want a list of each day across that 8 month period?

each thread made by threads-create("sub"); reads only the subroutine given?
or the whole .pl script?

zOrK, wrong channel?

SELECT * FROM (SELECT n1, n2 FROM table ORDER BY rand()) AS dt GROUP BY n1;

ohhhhhhh
thanks
sorry

oh, thanks
and it's the straghtest way ?
anyway, i'll use it

It's not very efficient, so if the table is big, you might not like the results, but I can't think of a better approach right at the moment.

thanks
my table won't have more than 3000 element, i think
*s

Probably won't make any difference then.

spammed again?

yes

why can't wiki updates be moderated

#2006 - MySQL server has gone away
where did it goto ?

Or at least stick a captcha on there or something

is it possible to create triggers after selects? suppose i want to update a "last_accessed" timestamp on a row when its selected?

captcha is losing effectivness on a daily basis

snoyes latest versions can have a captcha

i think captcha will be the inspiration for true machine literacy
one approch i've heard about is presenting the image on another site for a human to read and then supply that answer

half the $#@*$! captchas i run into *i* can't even read, and last i checked, i'm human.

make sure you have a belly button

has anyone seen a problem before that when you do a select (select * from table where field ='1234') and you can see the data is there, but the query comes up blank?

it's either a belly button or a reset switch. . . .
nope. it's a belly button.

Perhaps some whitespace in the field?

I've even copied the data and used it in the select

Our captcha doesn't have to be one of those 'read the text off the image' things. We could just have fill in the blank SQL questions: SELECT * ____ tableName;
Is your WHERE clause really more complicated than you show? Can we see the real query, and the rows you say should be selected?

hi all

forumtab.sql

probably

can the 'screen' cause any problem
I tried this command and it doesn't produce the forumtab.sql file correctly
the file size created is 0

why use screen? so you don't have to wait? i.e. does it work w/o screen?

are you trying to call the persistent shell session 'screen'?

I using screen because afraid the connection between my pc and server host droped

can you give screen commands to run like that? usually, you would use screen interactively…

so it cannot be done?

hm, yes, it seems you can… but i'm not sure what that will do.

so launch screen first, then your mysqldump

output redirect is redirecting the screen output instead of the mysqldump

hm? sure. type "screen" and enter. on the new (Screen) shell, run your command
or use nohup.

oic

'' AND programnumber = '1000024' AND tdate = '2007-01-01' AND tdate = '2007-07-31' ORDER BY studentname ASC

pilot error

And a row that you have which satisfies all those conditions?

oh man.. sorry had a phone call…

you can the detach from the screen, and later re-attach to it. if the connection breaks while you are attached, you can also re-attach (using some force option, i don't recall the details)

yeap
that trick worked

snoyes are you there?

no wonder I am seeing like matrix

aye

it should. but even when i select just that programnumber is selects nothing
*it

Show the row. Copy and paste into a pastebin or something.

I had backup those tables, and how can I restore it to other existing database?

or use mysqldump and include just the relevant rows.

if I have a datetime type field, how do I select e.g. just the day month and year in a different order
I've troubles finding an example for that

!man date_format

see http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

!man format_date

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

sigh
!man date and time functions

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

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format
there

ok thanks

how can I extract the date only from a timestamp ?

DATE()

I think the url you juse got when joining would have enough information

how do i create a user with all privs on a database?

!man grant syntax

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

snoyes, using that table I pasted I need to get the price for a product in a certain time period.. can you maybe help me with that?

How does that differ from your original query?

I'll show you, i'm making a paste

anyone use freetds and ssl?

i just found the white space (blush)

mysqldump -h localhost -u v2user -p v2 forumtab.sql

what happens?

here is the paste: http://paste-it.net/2857

it tried dumping data to the last table

do you have a couple of minutes?

it should create new tables and put in the data

mynullvoid, mysql to import not mysqldump

archivist?
I had created the sql file uing mysqldump

So you want the price of a house, and depending on the date, it should be tierh 10,- or 20,- ?

now to append my database

Yes, and you use mysql to import it. php mysql web hosting file.sql

indeed, but.. you can rent a house for a given period

ok

ok, and that price is per what? per day?

now, I want to calculate the price for a given 'rent-period' which can overlap
yes per day

Interesting.

so I can rent a house (1) from 2007-01-03 untill 2007-08-01 ..
interesting indeed
I wanted to generate a list with the price as the first step… but I'm not sure it that is the correct way

You could create a table that holds just every date possible from now until the end of the decade or so.
Then join against that.

yes, or maybe use an inner join to create a list with days and use that?
an inner join on the SEASONS table

yes

can you help me with that?

SELECT * FROM dateList JOIN seasons ON dateList.date BETWEEN seasons.date_start AND seasons.date_end

but I mean w/o the extra table

The only way to really do that is to build the table with a ginormous UNION statement, which is excessively tedious.

ah okay.. and can I create a temporary table with the dates?

You could, but I'd just keep a normal table around with them.

okay, so than I need to add new entries whenever a new season is added?

yes, or just keep the table long enough that it doesn't matter.
http://paste-it.net/2857#bottom

and seasons will contain all the days?

Unless you need the daily break down, see that paste

i c, but I don't really understand how the seasons table will look like?

of course, that assumes you can make the price field a normal numeric type (int or decimal), rather than the string shown.
Seasons will be just like you show, except change the price from 10,- to just 10

yes okay
so I don' t need a list with all the dates?

Not unless you need MySQL to return a daily breakdown.
If you just want the final price, there's no need.

whats a daily breakdown?
ah okay
I indeed need the total price

2007-01-01, $10. 2007-01-02, $10. 2007-01-03, $10….

ah okay
but when I use you example, what will happend when a reservation overlaps multiple seasons?

That's what the LEAST() and GREATEST() functions are doing.

so LEAST() returns the 'start' day in the period?

end day, but yes.
We want either the date_start or the $start value, whichever is later
and the date_end or $end value, whichever is earlier

okay, I've to think this over

jan knesche (sp?) has a blog post about this issue somewhere

But thanks a lot. I'm gonna read up on these functions
really?
do you know the address?

I'm looking

#2006 - MySQL server has gone away
please assist?

!man gone away

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

danke
If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server's max_allowed_packet variable, which has a default value of 1MB that was it ;( preciate it wench

I think it vanished when I switched the blogs
http://web.archive.org/web/20060221171213/jan.kneschke.de/blog/38 ?

yep, that one
^

who simplified the query dramaticly:

"

Thanks a lot!

That's why I remember it.
a few days later I realized I could have replaced the IF() statements with GREATEST() and LEAST().

it was really an eye-opener after working on the query on paper for several ours
I'll republish the article in the new blog

so what is/was the merlin project?
Must still be secret.

hello guys, I am trying to create a slightly unsimple query and I was wondering if someone here could help. It's INSERT with ON DUPLICATE KEY UPDATE. Only the UPDATE part should happen only WHERE DATE(created) = CURDATE() and HOUR(created) = $hour : ). Meaning that if duplicate found outside of
these conditions - the new row has to be inserted instead.

http://mysql.com/products/enterprise/advisors.html

in your example, can't I use the "end_date" all the time, instead of using GREATEST?

No, because if they don't stay to the end of the range, you'll be charging them too much.
and GREATEST goes with the start_date anyway.

ahh of course

Store those fields together as a separate field, with a unique constraint on them.

Where's the Coke?

You can use a BEFORE INSERT trigger to populate that field from the created field, instead of having to do it in your application, if you wish.

hmm..we might be fresh out
didnt you bring any with you ?

so this would be correcct: http://paste-it.net/2859

*sigh*

the number of days per period

I'm testing replication, and when I run SHOW SLAVE STATUS\G, I see that slave_io_running is NO. Anything I should be looking for?

snoyes, i added "unique" to all fields which - in combination - have to be uinque.. and I was trying to do INSERT INTO table1 (r1,r2) SELECT (r1, r2) FROM table2 ON DUPLICATE KEY UPDATE r1=r1+VALUES(r1), r2=r2+VALUES(r2) WHERE DATE(created) = CURDATE() AND HOUR(created) = $hour… Am I totally
off course with this? (Doesn't seem like anything that you said)..

looks correct

Thanks; calculations with dates are always hard
do you know a good book where stuff like this is explained?

Since indexes in MySQL don't support functions, you'll need to add a field to table1 for createdDateHour, with a UNIQUE index on that.
Populate this field with the date and hour from created, like CONCAT(DATE(created), HOUR(created))
Not these sorts of calculations in particular. It's all just careful application of the various operators and functions available, which are describe in the manual.

snoyes, i see… This makes sense… This would narrow the scope of uniqueness check only to entries belonging to this hour.

and a general book on sql?

book

http://www.kitebird.com/mysql-book

what?

Suppose to be THE book.

ahh
book

http://www.kitebird.com/mysql-book

ahh hahah a bot

bot

I'm *not* a bot! I'm a real buxom wench

hi how do i kill a particular process?
from the output in show process list

kill pid;
pid being the process number
!man kill

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

okay thanks

I hadn't seen that [CONNECTION | QUERY] syntax added to kill in 5.0. Sweet.

would this be possible? http://paste-it.net/2861 (selecting into a variable and using that)

Is there a way to (actually, I'm sure there's a way…maybe "how do I") pull data based on a conditional? Something like: SELECT if(There are parens in `stlye`){strip parens}else{`style`} as style FROM…

CONCAT(DATE(created), HOUR(created)) — i should do it right on the insert. so if insert the "created" field gets generated on insert (current timestamp) then CONCAT(DATE(created), HOUR(created)) would still work within the same insert?

SELECT REPLACE(style, '(', '') FROM table;
if you replace created with NOW(), then yes.

it needs to be a bit more involved. I need to replace "\(.*\)" but replace doesn't allow for regex

snoyes, my created gets generated with default set to CURRENT_TIMESTAMP, is that sufficient?

For the created field, yes. But for the other two, probably not.

AH , wait, you said to replace created with NOW() within CONCAT?

So strip parens, and everything between them? What if they are unmatched? What if there are multiple? Nested?

snoyes, (that was at you)

correct

that's my point. I got it to do what I want using this: TRIM(CONCAT_WS(' ', TRIM(SUBSTRING_INDEX(`style`, '(', 1)), TRIM(SUBSTRING_INDEX(`style`, ')', -1)))) as style

So I had this dream where MySQL charged licensing fees based on throughput…

But if there are no parens, then it duplicates the contents of style

Yeah, I think you'll end up with something like that, unless you install the regexp udf.

sounds more like a nightmare

Seems like the real nightmare would be billing for that.

snoyes, thanks a lot. you get overloaded here i see. : ) I appreciate your help.

seekwill they would be richer than m$ then

There's a regexp udf? Where I can do something like: SELECT regexp(…) ?

Can't open file: 'entity.MYI' (errno: 144) but what does that actually stand for

hehe

Yep. you'll find it on google.
!perror 144

Table is crashed and last repair failed

I think I switched to PG though!

should i do repair table?

yes

I'm testing replication, and when I run SHOW SLAVE STATUS\G, I see that slave_io_running is NO. Anything I should be looking for?

But there is no way to do a conditional? "SELECT (condition)?opt1:opt2 as Name" type of thing?

issue START SLAVE; see what happens.
Sure, SELECT IF(condition, trueValue, falseValue)
!man flow control functions

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

Thanks. While the regexp might be cleaner, this would support more servers…

oh
!man control flow functions

see http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
see a href="http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html"http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html/a
see a href="a href="http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html"http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html/a"http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html"http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html/a/a

when you get to be my age you'll be more concerned with Flowmax functions.

0 am on the local cable channel? Something to add to your garden hose so that it could also chop
0 am on the local cable channel? Something to add to your garden hose so that it could also chop

hello guys
is there any function available for find a text in a row, and return 1 if found, 0 if doesn't exist
?

threnody, you that old as well?

!man string functions

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

lemme check

0

snoyes, thank you

what do i have to change that my slave gets access to the master? for the moment i get: Slave I/O thread: error connecting to master 'repl@domain.tld:3306': Error: 'Host 'xxx.xxx.xxx.xxx' is not allowed to connect to this mysql hosting server' errno:
1130 retry-time: 60 retries: 86400

you need to use the GRANT statement to allow that repl@domain.tld user replication privileges.
!man grant syntax

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

older.

threnody, but have caught up with me yet

How many times a day do you use the term "whipper snapper"?

kidz these days

i get this error: ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

what statement did you attempt?

GRANT FILE ON database.* TO repl@IP_of_Slave IDENTIFIED BY 'password';

Why are you granting file?
grant replication slave.

11H14ello

ok, now just to get the picture, i have to grant replication slave on the masterserver for user repl@ipaddress_of_slave identified by password, correct?

correct

I'm new to this company and inherited a server that has mysql installed, but the config wasn't documented by my predecessor. How do I recover the root username and password for mysql? thanks.

reset root

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

but what is on the slave? do i just create the user repl with the same pw? or do i have to grant something?

I'm keeping my wording terse to avoid spillover. MySQL 5.0.41, Debian Etch. Colocated box, host says port 3306 is open. my.cnf bind_address is ip of nic. user host connection priv is '%'. nmap and netstat show 3306 closed. I need remote login, what am I missing/doing wrong?

What's for lunch?

open firewall

marc-andre: You'd specify the given user/password in the MASTER_USER and MASTER_PASSWORD fields of the CHANGE MASTER command.

no idea how I got that nic.. anyway, it is open.

try to telnet to the port

good question

make sure the server is started.

by masterhost, can i give there the ip address of the master?

yes

You said that California Tortilla place was good?

Heh - seen Pablo Francisco's impersonation of the governor of CA as a tortilla vendor?

yeah, it's not bad… beats chipotle/qudoba/tijuana-flats

snoyes, could you tell me why this generates an "Operand should contain 1 column(s)" error? INSERT INTO table1 (r1, r2, r3) SELECT (r1, r2, r3) FROM table2 ON DUPLICATE KEY UPDATE r1=r1+VALUES(r1), r2=r2+VALUES(r2); …

drop the () in the select list.
….SELECT r1, r2, r3 FROM

snoyes, ah, ima try now

telnet to 3306 timed out, no connection, telnet to 80 let me in.

probably the firewall isn't allowing inbound connections on that port.

if I do 'nmap localhost -p3306' from the server, it should give me the status of the port as open, regardless fo firewall settings, yes?
Yeah, just did it on my laptop, and it shows 3306 as open.
rather, 'nmap -p3306 localhost'
And a netstat -a shows no listener on 3306.

external

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

so literally comment out hte line bind_address= line in my.cnf?
doing now.

and the skip-networking

freight__ unless you know how to bind it properly
how come i see so many people with these same issues in here, does the installer bind automatically now?

No. People do weird things

distros fsck the standard install

Distros can't bind to the external nic
ip
Well..

some probably see it as a security issue… having a database hosting that is wide open to connections from anywhere by default is not the best of practices

how do i limit the databases to be replicated on the slave? i followed the howto on mysql.com but the slave tries to replicate the whole server

MySQL ships root with no password…

marc-andre: You can either configure the master to only write the relevant db to the binlog, or configure the slave to only read the relevant db from the binlog.

Plus, when a Linux distro binds itself to the external nic…. (instead of lo)

marc-andre: http://dev.mysql.com/doc/refman/5.0/en/replication-options.html#option_mysqld_replicate-do-db

is there some nice way to tell mysql not to die on error? I'm wanting to update all updatable data in a (unique) column, and just ignore when mysql can't update it

UPDATE IGNORE

oh cool, thanks!

-p -h ipaddy. Before I call my colo host and start complaining, is there any settings other than the firewall rulesets that could be preventing me from connecting?

s/is/are/

What distro?

Debian

GOOD LUCK

Etch to be specific.

#1046 - (duplicate)

Heh, thanks. grin

is the goal to get it to allow a remote connection, or to not allow a remote connection?

To get it to allow remote connections.

Personally, I'd juse ssh tunnels…
Especially for administration

Rigth now I jsut need to get the bloody thing to work, but you are correct.

ssh tunnels would work.
Pretty easy

if its at a colo they may not let 3306 in the building

Ok, I'll bite.. where do I start reading?

ssh -L 3306:localhost:3306 host.server.com

how can I know where are my MYD, MYI files stored? (redhat)

/var/lib/mysql
find / -name '*.frm'

thanks seekwill

When's lunch?

is there a way to find that out by doing some query in the mysql cli?

show variables like 'datadir'?

soon?

Whenever you're ready. Do you go with Wez and Luke these days?
Of course, Wez has enough sugar at his desk to last him a.. uh… another hour.

you has drive?

Mike has the keys

can i use more then one ip by bind-address?

no

Hah, do you ever eat with Mike?

hmm… probably you, mike, and luke should go… then i can convert you all to the proper side of the force

hah

"proper side of the force" - holding your pinkies up when electro-shocking some rebel scum.

PINKIEEEEEEEEEEEEEEEEEEEEEEE!!!!!!!!!!!!!!!!!!!

how can i permit the slave to connect without blocking the connections on localhost?

remove bind address completely.
external

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

Greetings Y'all

thx

Mike and I are ready
well, give me a sec

heh
howdy

Did we decide on something?

yes

you have that response on a macro, don't you. grin

bot

I'm *not* a bot! I'm a real buxom wench

Ahh good.
snoyes is a robot.

but enough about my personality…

I met him and recall a distinct whirring sound.

the wench has no macros

Where did I read that most people fail the turing test?

the wench passes

Based on some of the captchas I have seen I would believe it.

turing

Yay I passed the test. I am human after all

i cant post comments in wez's blog because always get defeated by his capatcha system

heh

I figured the laser canon and metallic skin would have been a better giveaway, but perhaps you were distracted that day.

are you related to samus aran ?

Well it was California.
You kinda blended in.

I'm up to try that California Tortilla place
:P

Heh, debian.org's round robin upgrade system keeps sending me to a server hosting in France.

You have a problem with the French?

The bounty hunter? We don't need their kind of scum.

Not in the slightest, acutally.

What's wrong with a free trip to France?

you have to go to france to use it

Aah, good point.

I actually appreciate the Fench attitude, because there are no pretenses, if they dislike you, you know it. It's refreshing.

The airport can't be that bad, just fly in, collect the miles and fly back.
The French don't dislike, they hate.

and their food.. *tasty*
So, seekwill, I've run the line you posted, and am in the server. How do I get mysql-admin, for example, to run remotely and display here?

You connect to localhost

is there a way to use server status variables in a select statement? ie: select 100 - ( ( Key_reads / Key_read_requests ) * 100 );

One of the more recent French shows of affection by Zidane.

Luke coming?

when I issue START SLAVE, it says Query OK, but nothing happens

Now what do you see when issue isse SHOW SLAVE STATUS\G

the same
lemme verify something

Is there an error shown?
Either in show slave status, or in the error log?

nothing in either

but it still shows slave io is not running?

correct
and I don't see any connections via netstat
on master, that is

i have a table of products, and i'd like to record specific details that change depending on the type; what's the best way to capture this?

What does the slave status say in slave_io_state?

Slave_IO_State:

luke gave me some mumbling about lunch with laura

I thought she left. Oh well. Let's go!

but there are some other folks who might join in… more out of townies

ah

for examples, some records in "products" would be of type "car", "boat", "airplane" (made-up example)
and i want to have different details for each
so my first instinct would be to have tables "products", "car_details", "boat_details", "airplane_details"

and nothing in Last_Error?

0
Last_Error:

and the Master_Host, _User, and _Port are all correct?

yes

Depending on the nature of the date, you can do it that way, or you can just create a key/value table of details, that is, product_id, detail_type, detail_value.

yeah, those were the two ways i was considering
any big pros/cons of each?
obviously i'd have to have an established set of types to do the multiple-table way
but i'd have to have an established set of detail_types for your way

or you establish them as you add them

are you still arond?
around

and it's less work to add a product that way

Correct. The later is more flexible in that you can add more information without changing table structure. However, if the details require different field types, that's a problem.

You are supposed to do mysqld –debug to produce a trace file , no?

Example, price is best stored in a decimal field, but color should be a string.

mm, good point
yeah, i def. have multiple datatypes for my details

4 [ERROR] mysqld: unknown option

some booleans, some decs

Hey there

I've got a table with a field "product_type" , when this value is "house" I want to use the field "house_groupid" to join on a table… else I want to use "general_groupid" to join…. is that possible?

could someone explain why my primary key doesn't help this query, and how i could index this to make it faster? — http://www.pastebin.ca/622049 (query, explain, and key posted)

In windows, when i run mysqldump –all-databases -u etc
Where does the dump get saved? I can't find it

JOIN otherTable ON IF(product_Type = house, house_groupid, general_groupid) = someOtherField

grant usage on database.* to 'user'@'localhost' identified by 'password'; still result in this error: warning: connect to mysql server 127.0.0.1: Access denied for user 'user'@'localhost' to database 'database'?

If you don't direct the output somewhere, it just goes to the screen.

Doh

path/to/file

Thanks snoyes
snoyes++

Okay, thanks snoyes
The output looked matrix-like
lol

marc-andre: are you connecting with mysql -h 127.0.0.1 or mysql -h localhost?

Hmm

pizza_biz, ord_type not like '%C%' cant use an index

C:/MySQLdump/

Didn't work
Do i need to create the dir?

Put an index with ord_date listed first (and drop the USE INDEX statement so MySQL can pick that one)
Your WHERE statement doesn't reference the store_num, but store_num is first in your primary key, so the primary key is useless in that query.

i only added the use index to see if it would help, and it didn't. same either way.

Right.. err

You need to give it a file name.

Hmm

so just add an index on ord_date, any other fields?

Ahh
Just out of interest

Probably not for that query.

Why is the syntax -uroot
rather than -u=root

My server keeps crashing on restart

Cause with -u=root, it says "No username, =root"
lol

You can choose -u root, -uroot, or –user=root

-u root doesn't work
-uroot does

I look in the .err log and I see a trace , it give me a link about using a program to read the trace

Okay, so to restore a DB dump..

I've not experienced that with -u. With -p, yes.
mysql /path/to/file.sql

but I don't see an example for how to use the program

Thanks snoyes
Okay, rebooting

in a long import is there any way to tell how far it is through the process? i disabled logging to speed it up

ah yeah, adding indices to this table sucks. over 6 million rows. this is gonna be a while. (poor old slow server.)

the size of the data file?

pizza_biz, best to get rid of the like as well

hmm yeah could use that although that will only tell me that it is still working away

i need that to filter out non-applicable rows though.

hi! how may i add UNIQUE index on two columns simultaneously?

pizza_biz, find a better way or at least get rid of the leading %

UNIQUE(column1, column2)

hi!
i don't want to let similar combinations of some column values…

ok

how are you today btw?

hungry.

time for a dunkin donut
almost worth flying to the usa for

say you have a list of transaction that have a date and a month and you are grouping by month to sum the transactions and display the total.. Is there an easy way to get the query to display 0 for months that don't exist in the result set?

Hey
How do i un-dump my dump
xD

mysql /path/to/file.sql

i did that
But, it says this–
ERROR 1064

You'd need a table with the list of months; join against that.

See, the version i dumped from was 5.0.41-community-nt

snoyes, thanks

"MySQL dump 10.11"

and the version you're importing to?

Err.. the newest one, freshly downloaded
5.0.45-community-nt

There ought to be a — at the beginning of that line in the dump file.

yeah, there is
– MySQL dump 10.11
Aha, i fixed it.. i think
Sorry, i'm migrating from PostgreSQL
Okay, it's populated..
Thanks a lot snoyes
Hey, is it alright if i ask a question or two?
:-)

Hey guys, how do I get "IF NOT EXISTS" for CREATE DATABASE in mysqldump files?
I mean, it's in the file, but it's commented out and is shown as "/*!32312 IF NOT EXISTS*/"
Apparently the numbers are the same all the time for everyone, too…

hi all

If you look it's this
CREATE DATABASE /*!32312 IF NOT EXISTS*/ 'database_name'
:-)

creating a schema is same as creating a table in mysql 5.x ?
or schema is a database?

what is the capacity of a mysql table? how many rows can it handle efficiantly?

/IGNORE #mplayer ALL -PUBLIC -ACTIONS

if i need to search around 90,000 records, like 10000 times per hour

So you're saying I should do a search replace?

Yeah, might as well
:-/

That's a conditional comment.
It means that for version 3.23.12 and later, IF NOT EXISTS should be parsed. For earlier versions, it will be ignored.

That's not a trivial question and requires details about your hardware, OS and application(s), as well as your concept of efficient.

schema and database are essentially the same.

ohh!

The answer is also impacted by your DB design.
What do you mean by "search". Show specific SQL.
In general, 90,000 records is considered very small.

is this possible: http://paste-it.net/2866 ?

probably, but the manual says not to assign and then refrence a variable in the same statement (because the evaluation order is not guranteed.

okay

So just replace @end with r_date_last in your SUM statement.

You might consider reading through some of these: http://www.mysqlperformanceblog.com/

hi, can i do a mysql search with a wildcard
like any record with http:// in it ?

WHERE field LIKE '%http://%'

so % is the wildcard
thx

% and _ for LIKE. There's also some support for regular expressions with RLIKE and other operators.

k thanks

thnx

Hi
One of my users have a strange problem. When he logs in my FreeBSD server via SSH from his Linux pc, and starts mysql client (v4.1.22), after entering the password the client eits with SIG11. If I login to the same server from my Linux box with his user ID, I can start the very same mysql
client. Does anyone have idea what can be the root of the problem?

Any recommendations for crash-proofing a MyISAM-heavy system? Every time the server crashes (it happens frequently), I lose lots of records when the automatic recovery kicks in
like [Note] Found 7657 of 7694 rows when repairing './site/table'

Isn't that a segfauly
segfauly
t
lol

) It is
I have a core file too
but mysql was not compiled with debug sybols

Hmm, the only thing i can think of then is that it's the person's SSH client and/or some kinda network problem

symbol
s

I'm new to MySQL (Postgres convert.. ) but i'm looking at src now
And google )

He even tried to log in from a different pc too. The problem is the same.

kumi, don't use MyISAM if you expect lots of crashes (why does it crash a lot?)

I think the PSU is going bad

heh, sounds like you have bigger issues then

xD
err
What do you use for the backups

just a periodic mysqlhotcopy cron script

Hm

hello all

does binary logging improve recoverability? I have it turned off

can anyone tell me if I can use wildcards to delete multiple tables?

since I don't replicate

dex

Not as such, but you can use information_schema to build a drop table query
It can help, since you can use it to replay updates made since your last full backup.

oh,

hi all
Can I change path /var/lib/mysql without recompiling?

drop all tables

There is no wildcard support for the DROP TABLE syntax. You can use MySQL to generate the list of tables to drop; see http://thenoyes.com/littlenoise/?p=44

all I need is set datadir?

bit of a modification to that ^

or I need to recompile?

ok, thanks

What are "good" uses of MySQL temp tables?

I am a newbie. How do I import a database file into MySQL?

Handling aggregate records that would benefit from an index (hence no derived tables/subqueries), but that are too dynamic to maintain normally.
mysql path/to/file.sql

Ah :-O
So, such as?

I am using mysql query browser, a graphical gui.

Okee

Open Script

Then click "Execute" on the right-hand side
:3

Say you have a table that stores all the hits on your web site, and you want to find out how many of them result in someone actually buying some of your products. With millions of records, querying against that hits table can be unwieldy.

Oh

However, you can create a temp table at run time that loads summary data from that table for just the products you're interested in.

Mhm, and then once you're done.. pow, right in the kisser?
xD
Bang, zoom — straight to the DELETE TABLE ?

One of these days…

:-D

I think there is an error in the query we created (or… i probably made the mistake )

"I'm going to stimulare the economy by buying an American car" ?

, )

still there?

Impossible. All queries created in this channel are automatically verified by an independent consultant, and are guaranteed error free.

:P
I didn't verify it in here

0 and

Cool, snoyes

I continued after you helped me on th eway

Just reading about it on the dev center

the problem with that pasted query, is that it forgets some days when a season overlaps

Can you turn that dbf into a CSV or something like that?

thanks the_wench

can you help me with that?

snoyes-senpai: What's the 6.0/Pidgeon all about?

yes I can load it into excel
which then could make csv

That's the ranking algorithm borrowed from Google.
ok, from there you can use LOAD DATA INFILE.

OH! Really?

!man load data infile

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

I'm going to look at that for sure

Yeah. http://www.google.com/technology/pigeonrank.html

;D
Nah, i mean the 6.0/Falcon

0 because original format is

A new transactional storage engine. Some see it as an attempt to build a replacement for InnoDB, which Oracle bought.

:-D
You're like a IRC Wikipedia bot
With added sass

MySQL can probably handle that if you define it as a TIME field.

?

Can you show the rows that get skipped that shouldn't?

sweet ok I will look into that… thanks very much!

they don't skip, but when a interval overlaps multiple seasons it forgets one day.. I'll paste what I mean

ah, perhaps that expression in the select list should be TO_DAYS() - TO_DAYS() + 1

http://paste-it.net/2868 this is a result
yes, but than it adds 1 to much (for the last overlapping season)

file.sql; how do i load file.sql into new database on new server? thanks

In the example you show, it should be + 1 in both cases. Can you show an example where it should not be + 1?

mysql file.sql

yes
ahh the problem is that I want to count the nights, not days
like this; http://paste-it.net/2869

So, if the s_date_end is 2007-07-31, did they stay the night of the 31 or not?

yes

ok, do TO_DAYS() - TO_DAYS() + (s_date_end = r_date_last)
I think.

okay gonna check

I dunno. I still think it's going to be + 1 in all cases, but map out all the possiblities and check.

that works

need advise concerning union. i have a table with some kind of linked list (means some items have parent by parent_id). to filter for childs or parents, a union would be the key, right?

I think it must be: + (r_date_last = s_date_end) don't you think?
the r_date_last is the reservation end date

I dunno. Try it and see which one gives the right answer.

s_date_end is the season end.

ARK
I've got another problem
I can't RENAME my temp tables
:-(

Maybe. What do you mean by "filter for childs or parents"?

do i use ALTER?

!man alter

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

thanks, what if i dumped only one databases, just add a "create databases whatever; use whatever;" to the beginning of my sql dump? or is there a fancier way? thanks again

Why do you need to rename a temporary table anyway?

Is there a way to force a case sensitive GROUP BY for an alias, since BINARY doesn't seem to work for them?

there is a mysqldump option for that, but you could also just do: mysqladmin crate dbname ; mysql dbname file.sql
s/crate/create/

cool thanks, IRC is fun

xgc, you there?

is mysql more widely used in industry compared to postgre? I'm trying to decide which to use

#mysql has a mandatory sense of humor in the channel topic. That should be enough to make your decision.

snoyes, for example all childs (which don't belong to a specific parent) and the parents themselves.

ever heard of LAMP? linux apache mysql perl?

trees

http://www.dbazine.com/oracle/or-articles/tropashko4 and http://www.sitepoint.com/article/hierarchical-data-database, http://jan.kneschke.de/projects/mysql/sp/ http://jan.kneschke.de/projects/mysql/sp/sp_tree.sql http://www.vbmysql.com/articles/database-design/managing-hierarchical-data-in-mysql/ http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

^

in a deadlock situation, is mysql returning immediately, or is it waiting some timeout period for the locks to be released?

heh ok snoyes convinced me first

That used to be a joke. I'm not so sure anymore.

would;nt lamp in that fasion be an argument against him, since he is using RoR ?

ruby on rails isn't really used much in industry, though
yeah, i was just noticing that contradiction,

I was jus' experimenting, is all
:-)

but coding skills and sql skills are transferrable between languages and db servers somewhat

I'm lost…. I study computational complexity theory for a living but want to learn web app programming…. everything is so fragmented though it seems in the webapp community and everyone has their oppinion

I'm reading the docs here and it says to retry transactions that return the 'deadlock' situation as the error. This however seems quite wasteful to me, if the deadlock exists because of a transaction that may take a few seconds to complete, then I could be reissuing the transaction thousands
of times in that period

http://rafb.net/p/8oIWu055.html

Just pick one at random. You can do most anything you need in either one. After you're comfortable, learn the other one. When you have an important project, you can decide which one is best suited.
A significant portion of "which one is better" is really "which one do you know well enough to make it do what you want"

ok, fair enough

this query is correct right: http://paste-it.net/2870

There's no need to use the IF construct; MySQL doesn't differentiate between the boolean 'true' value and the number 1.

okay, but it would be a correct query?

seems to. The parser and results will tell you better than I can.

yes,but the strange thing is, that it returns 0 for the second season when it overlaps 2 seasons
o days in the second season I mean

Did you run the testcase I gave you?
You can place a SELECT inside the NOT IN (…) clause.

yeah.. i couldn't get it to work. the wrong results kept coming back.

s_date_end)

That's the purpose of the LEFT JOIN I showed.
Not possible.
The testcase I gave you produced correct results.

so i can stick a SELECT in the IN () and it would build the string for me?

Did you change it?
Yes.

awesome!

It doesn't produce a string. It generates a list / set.

how did you build that one test? what app were you using?

Just run it through mysql command line client. I created it wish an editor by hand.
s/wish/with

can you show the result set for that?

mysql test myscript.sql

i need to learn how to do that so i can paste those in here if i ever need help again

;$ ahh is c the problem indeed. its when the last reservation day is on the start day of a new seasons

thanks a bunch for your help!

You're welcome.
Your SQL is not correct.
It has the same problem you were trying to solve yesterday.

it works perfectly

That is, if there are assignments for some photo to another article, this will return that photo, even though it's already assigned to the story in question.

I have a table with parts for a computer I want to xulid. When I do SELECT (unitprice*quantity), it gives me the cost of each row. Now how do i tell it to do that and then add all the rows?

You really need to work on your listening skills and be open to mistakes you may have made.
You can't do what you're attempting without some form of aggregation.

SELECT SUM(unitprice * quantity)

i'm open to mistakes. God knows I am not perfect! But the query i pasted accounts for any assigned items to the parent story in the NOT IN(), so they will not appear in the results.

Thanks.

That's not the issue.

there is a preceding query that's not pasted which populates the NOT IN()

what's the easiest/best way to convert a table from myisam to innodb
?

Show that other query. Maybe that'll help.

ok

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

Are these the photos (in the NOT IN clause) that are already assigned to this story?

yep

That's fine.

nice. it took over an hour to add the store_num index to the table, but the query now runs in 10 seconds instead of over 1000.

I assume you mean the date index.

yeah. that.

ALTER TABLE table_name ENGINE = InnoDB;

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

Question. Don't you want to show all photos that have not been assigned to this story?

yep, i want to show all photos that have not been assigned to this story and that's what the results are coming up as now that i added the NOT IN() to the query

also vastly sped up another regular query on that table. bonus.

I've read that innodb is the only engine for mysql that is safe to backup using lvm snapshots? Is this true?

!man lvm

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

heh, sorry

define "safe"

not likely to cause corruption of a backup is made during an lvm snapshot via tar or similar unix commands and then later restored

That's fine. Note that you will potentially show photos more than once with this query, unless you use DISTINCT or something equivalent.

Make sure you flush tables with read lock first; then it should be ok for MyISAM too.

but innodb doesn't require tables to be flushed?

Assign the same photo to two other stories, but not the target story. See what happens.

ok thanks for the tip. i didn't think of that.

correct, unless you want to know the binary log position, so you can restore from the binary log for all changes made after the snapshot

and DISTINCT won't help you in this case, not with the SELECT list you have.

Hi all. I'm pretty much a noob to web programming in general. In the past week I've been looking through some php & mySQL stuff. One thing that continues to confuse me is how to associate one element of a table with multiple elements of another. For instance, an element of the members
table having a list of friends from the members table, or maybe an element of the members table having a list of watched posts from the forum tabl
n?

shite

If you remove the [a] columns from the select list: SELECT DISTINCT s.ID, s.photo_headline FROM …; will work fine.

You have a members table, and a posts table, and a membersWatchedPosts table, and connect them all with a JOIn
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

Anyone know what a "PMA Database Not OK" error under phpmyadmin means?

ok

I think I've got the correct query, interested?

sure

snoyes and the_wench, thanks much

You're welcome.
I bet the_wench won't say "You're welcome." She's rude like that.

is there a way to request the mysql to use SO_KEEPALIVE on inet sockets at all?
s/the//

http://paste-it.net/2883

should I see any problem changing the engine type on the "mysql" database tables?

please correct me if i'm wrong

I wouldn't do that.

why is that?

I do it from memory to myisam every day
going to/from innodb may be trickier

I'd like everything to be innodb
due to my lvm snapshots for backup

How are you going to alter the db, host, and user tables?

as root?
this is why I am asking the question

Try it and tell us the reesult.

heh
I don't know if I like that answer

Error on rename of './mysql/#sql-6a4_8' to './mysql/db' (errno: -1)

is there a way during install to specify that the mysql database uses innodb?

The rest of the tables (minus the log tables, which have to be CSV) worked fine.

I've already restored a copy of the database that was made using tar and lvm snapshots successfully due to a disasterous situation, but I've heard that it's not a great idea. Could I be hosed since the mysql database is myisam?

There's a huge reason why MySQL couldn't use InnoDB for the mysql DB…. If only I remembered…

looks good to me.

thanks

Probably because you can disable the innodb engine.

Possibly!

the mysql database contains plugin information to load innodb now!

CREATE TABLE chicken ENGINE=egg;

would it be possible to put that query in a stored funct/procedure, so I can get the price for a given reservation?

yep

i'd tried that yesterday, with just a simple function but i couldn't get it to work, though I think it seems quite simple

Can you show what you tried, and what didn't work about it?

i think i removed it
i was just trying
http://paste-it.net/2885
a href="http://paste-it.net/2885"http://paste-it.net/2885/a
yeah.. I know.. didn't had time to read the manual :$

Not the point. The point is the stupid tab autocompletion.

ah

:-

oh, yes, that helps immensly.
lol

haah
I'll read up on procedures… will you be here after 30min?

probably

okay great!
would it be better to use a functions or procedure?

I'd go for a function, unless you want this thing to return a full result set all by itself.

only the price will do
when I create this function that returns the price for a product, and I use it for all the products in my product table, woudl that be hard on performance? i mean harder than just joining everything?

probably. How much difference it makes depends on how big the table is and such.

okay, lets say around 100.000 products
but not all at once..
using LIMIT

No telling. The only sure-fire approach is to try it both ways and see.

ok
hmm but when I use the function(), the query that calculates the price is executing another query for each product, else it will be just one query
or handles mysql this more clerverly?

It's as you describe. Whether that's better or worse is very difficult to say.

i love mysql databases

whew, i think it's finally done, Xgc!
you were right, i did have to use DISTINCT, Xgc.

*nod* You could have better names for the story_assign table. I believe I misread your description. I still don't think, based on the results, you ahve the right answer yet. You seem to return a superset of the photos I think are correct.
s/names/column names/

when I want to return the result of lets say: select id from products where id = 10; how would I put that in a stored functions?

CREATE FUNCTION yourFunction() RETURNS int RETURN SELECT id FROM products WHERE id = 10;

ah okay

You seem to return a list of IDs that contain 21759, for the sample case.

Xgc, if i could get good at running those tests i would be able to past exactly what i'm working with.

I may still have bad data, given that I misunderstood the table descriptions you gave.

if you're still around later i might be able to run the test and paste the whole thing with data

I corrected my SQL and the only difference between your SQL and mine is yours returns 21759 in the result and mine doesn't.

I'm fairly new to MySQL and have a little question. Is it possible to somehow make references in a table to another table?

very odd

Yes, using a VIEW.
or a foreign key, if that's what you mean.

Xgc, are they the same thing?

No.

Okey, i'll look both up. Thanks a lot.

One allows you to create a fake table that refers to 1 or more tables internally, returning data from them all.

And the other?

The other (foreign key) allows one table to have columns that refer to a unique key in a second table.
It's like a pointer.

That's what i want
Cheers, thanks for it.

The term is "foreign key constraint".
I think that's just the standard that requires unique.

standards. bah.

hi guys
i wanna create a search engine with mysql you guy can tell me what do you think about it

Good idea. Grab the new http storage engine, and just point it at Google.

i want to make a scalable architecture , not extremly fast, but scalable
this is not for web search engine, only object (blob) indexing,
I want to index around 10 millions of object and even more
and i know that full text search isn't scalable at all i know how bad it is on big amount of text
so here is my though:

why not use an existing search engine?

because i don't know any scalable search engine

lucene, for example

can we clusterise it ?
i would create 2 deamon, one index one DATA servring
2 tables
1 index table contain (hash of key word (md5), hash of data)
data table would be (hash of data, gzipped blob of my object)
index table has 1 line per keywork contained in the object
data table has 1 line per object
so if i index 10 object that have 10 words in each, i will have 100 rows in index

I'm trying to create this procedure: http://paste-it.net/2887 but I get an error about the return

during a search, i broadcast the keyword query on my cluster of INDEXs daemon
and the INDEXs daemon return's the HASH's of the data matching

?!? wtf is that, google makes jokes ?
woops, bad timing

You refer to the pigeon thing?

yes

Every April 1

SELECT data_hash where kw_hash = md5('games')

do you know why?

what do you guys think, this is actualy the way google worksm but with MySQL it's obvious
Xgc, ?

wrap () around the whole select

For your enjoyment, when you have a moment: http://rafb.net/p/jGB78X22.html

ahh

RETURNS INT RETURN (SELECT…GROUP BYr_id)
or else declare an int variable, use SELECT INTO to populate it, then return it.

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

In your case, I think it's READS SQL DATA

but those values aren't used I read in the docs?

so RETURNS INT READS SQL DATA RETURN (…

sweet, Xgc! and you did this in mysql comman?

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