CheapWebHosting.co.nz

Domain Search | www. .com .net .org .nz Check ALL

not much data I guess

~200G

They need to start using E-mail

oh no!
you don't want to dump 200G and restore a ~ 200G dump

i know, but i can't copy the data, the version difference is too large.
(i know that's a bad idea anyway

hehe
well there was a tool I heard of which compares tables and can make a dump containing the differences, just forgot it's name

problem with raw disks is that you cannot touch or manage them, and the database host provides only inadequate tools for doing this.

but for loading 200G data you'll need at least twice the storage and with that, a filesystem comes in handy

best thing is to forget about raw disks.

how can I have a text file and a field in a table completely synced

okay. but i'd feel better about doing that if i knew there wasn't much difference in performance

noesc, I don't think private hosting messaging people in here regarding jobs is permitted.

I didn't do benchmarks, but I think there is no large difference
doesn't seem to work either
and you got klined for that already.

"there is not much difference in performance provided you chose a filesystem that does not suck"
ok, i said it.
now the religious wars can start

FAT32 FTW!
HFS+?

Aha! Yesterday you told me that INTERVAL had aliases for MINUTES, SECONDS, etc. to MINUTE and SECOND... but that is not the case!

reiserfs is a killer-filesystem

1.

global filesystem locks to the rescue
^^^HFS+

indeed, you have to use singular

If I were to add an ID column to a table (INT(11), auto increment), what should the syntax be?

http://blobstreaming.com/

Why? This ruins the illusion that (My)SQL is English-like.
Would be trivial to add aliases.
They have aliases for everything but this!

english like? the default collation is latin1_swedish_ci!
so maybe there are swedish aliases
if not there should be!
and I want some in traditional chinese!
WHERE DATE = 2 YEAR OF THE RAT

VÄLJ * FRÅN tabell DÄR datum NU() - INTERVALL 5 MINUTER;
=D
I really hope that there are no "language packs".

hmm
I think you actually can set a locale and get stuff like day names localized

Noooo!

vba for applications

:[

it was localized like that

great
like excel

Why am I not surprised that it was Visual Basic?

WENN x10 DANN bumm SONST boing ENDE WENN

SUMME() in german

lol
I wonder what foo and bar translate into

:O

I'll tell you... foo translates into this

OUCH!

:P Just kidding

does IS NULL come before or after the column in question
as in WHERE foo IS NULL or WHERE IS NULL foo
?

may lightning strike you while you take a dump!
where foo is null
just as in natural language - actually that's the current hot topic in #mysql

ok thanks

"ALRIGHT COMPUTER GENERATE 3D DIAGRAMS WITH DATA FROM MY MIND"

you're a niece alright

WHERE foo IS NULL
WHERE ISNULL(foo)
the first is syntax, the second is a function call
both are valid.

where ISNULL(NULLIF(column_name, ''))?

but isnull() won't use an index?

NULL and index usage do not like each other very well in many cases

ok, so no way to save my honour, time to leave

but you are right, isnull() is a function call and they never use indices anyway

good night!

threnody, wouldn't that be adding extra overhead?
i thought that was the primary reason to use NOT NULL DEFAULT '' for VARCHAR
so that you don't have to perform the IS NULL check
threnody, cuz you could just say WHERE foo = ''

I only added that to show a way to capture emptry strings if you needed to.

threnody, k

hrmm
wait
why would you use DEFAULT ''
isn't DEFAULT `DEFAULT ''` by default?

does php mysql hosting support multiple sequences?
could i have two columns in a table auto incrementing?

Does it support one?

anyone know of any migration tools other than mysqldump?
maybe something with a gui?

why would you want multiple auto increments?
jaredhoyt migrating to what?

another db

heh

what kind of db?

i need to update a separate temp_id to store votes in an application

mysql... just want to copy all the data and table structures

and i need to have two tables auto_increment

jaredhoyt why can't you use mysqldump for that?

or, two columns

Or just copy the datafiles

n/m... mysql has one on their website... mysql migration toolkit

thanks for wasting our time

if i have a query looking like UPDATE table SET col1=val1 WHERE col2=val2 ORDER BY col3 DESC LIMIT 1, i would want to have col2 on an index, of course. but what about col3, which i'm ordering on...that should be an index too, right? should it be "index(col2), index(col3)", or "index(col2, col3)"

why order by an update query?

there's probably a better way to accomplish what i'm trying to do
i want to update the most recent record for a given id. col3 is a timestamp. although now that i think about it...

why set a relatively complex where condition and then set it to limit 1?

i could also order by 1 desc limit 1
but i still think there might be a better way
where col2=val2 is a complex where condition?

if you were designing a select statement to return the one row you wanted to update, what would it look like?

hi all
i have question
somme one can answer ?

no

jsoftw ?

Why do people persist in asking if they can ask a question.
Just ask the damn question already.

off the top of my head, i don't know. i know that order by x desc limit 1 gets the job done.
but there's got to be a better way

jsoftw think's
jsoftw in mysql data base i have 2 tables

you've asked exactly the right question though

I dont beleive you. I think you actuall yhave 3 tables.

hi i need help with subqueries (i think)

Hi, anyone know what the character "`" is called?

backtick

thanks

i have 2 identique fields in the 2 tables

that might be an informal name though *shrug* it might have a 'real' name

hi
how to bind to 2 addresses?

can do somm think to merge the same fields

bind-address looks simgular, f..
)

select col1 from table where col3 = max(col3);

in order to change the fields contains with one php host request ?

i was trying that, and got a group by error
let me review that

bind-address is restrictive, not permissive.

1111
Invalid use of group function

sorry, dinner, brb

!!! heh

then it's called right. How to make mysqld to listen/bind to several interfaces/addresses?

subquery

lies

fantomas you can't

is that as bad as an order by?

then how to get it listeting everything?

don't specify bind-address

just drown in help files...
oh, really?
ok

really

really-really?

any way easy way to figure out how much bandwidth replication is using? web hosting company wants to downgrade our office connection and I need to measure if we can keep the slave server or not.

the only bad place in cms mysql hosting is its help or how it was indexed, i don't know..

fantomas http://ebergen.net/wordpress/2007/01/25/ive-given-up-on-the-mysql-manual-search/

thats it! )

i want to update a row in table t. anyone see anything wrong with this? update t set c1=v1 where c2=v2 and c3=(select max(c3) from t); i want the update to apply to the most recent row where col2=val2.
i'm getting "You can't specify target table 'admin_access_log' for update in FROM clause" and i'm not sure what it means

I just installed mysql-server-5.0 on Debian Etch. When I try to run it, I get the message "chown: `mysql:adm': invalid group". I've tried to google it, but haven't found anything useful. Anyone have an idea what is going on?

yes.. your system does not have a group named "adm"

So if I made that group, it would fix the problem?

what is the difference between maxdb and mysql?
as i see that sequences are supported in maxdb....
is that a beta of sorts..

ok I have a bunch of users and each one gets to upload an avatar in their profile. Now should I upload the picture itself and save it to some random directory and story teh file name in mysql, OR should I put the picture itself INTO the database. The pictures will be quite small, max 64x64 pixels

sorry, not much of a dba
..

store*

Well adaptr, you were right. One would think the installer would make any necessary groups, but oh well. Thanks for the tip!

when I do "SHOW FULL PROCCESSLIST" why do I see so many Command's as 'Sleep' ?
also, is there anyway (besides a bash script) to have a query repeat itself

because the connections aren't doing anything
and what do you want to repeat?

just testing some things over here
if it outputs 'unauthenticated user' for `User`; 1: Is that bad, and 2: how could I authenticate if that's bad (he's definitly authenticating via username/pass)

if you are rerunning show processlist then use mytop instead
1. now
2. it's going to authenticate in a few miliseconds
s/now/no

cool

Hi! From a datetime column can i somehow simply get only the date? (so for example i' d like to get 2007-05-05 instead of 2007-05-05 17:39:45).

yes, you can

date and time functions

http://pastebin.ca/567314
shown*
which also has an index question at the end

Egy pontos URL sokat segítene..

zzz, this is english, you gut your url, look it up
got*

hi

and there is no date and time functions..

is there some fast way to found nearest date from now with mysql ?

of course... use goo dindexes

zzz, there is a big SEARCH box there, rings a bell?

the people are probably not here at the moment, but I wanted to say thank you for the root password reset assistance I was given earlier

ok.

it was tremendously helpful

actually, check the link i just posted, it has an example select query which will do what you want, i think

Marticus, btw mysql.com/doc has a whole good section about that

to point out, however, the statement suggesting leaving off the --user=root option on the --skip-grant-tables solution was a bad suggestion and did not work

i was not here when you post the link
epswing

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html
(Windows users omit the --user=root portion).
that line caused an hour delay in resolution
anyway
good evening

(oups crash...)
(i don't know if you have repost it but my irc client have crashed right after having ask you the url)

http://pastebin.ca/567314

I'm honestly worried about INTEGER reading its limit. What happens then? The table explodes? Should I consider using a BIGINT or something? What if THAT reaches its limit?

well that's not seem to be what i seek for

Somebody could set up a script to create junk users on my Web site to make it reach its limit.

but i have found an issue for what i search for right now, thanks anyway

may sound stupid, but if I remove phpmyadmin, the database still remains correct? and if I reinstall phpmyadmin will I be able to access the db again? having trouble logging in through phpmyadmin for some reason....

phpmyadmin is just some stupid PHP script.
Has nothing to do with MySQL.

hi, does anyone know why php mysql hosting has not put up 5.0.41 for MAX, only 5.0.37 for download?

And I don't understand why people use it.

towerniece, it's acting real buggy...
figured if I reinstall I wouldn't have the problem
what do you use? the shell?

No.
MySQL Query Browser.
And MySQL Administrator.
Real GUI apps. Official and free too.
[RainMkr]: Did somebody open the door? =Z

hmm.. i have mysql admin... gonna check ti out

i am getting a:

thanks

Both but both of those tools suck.

6 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file (
6 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary
on a replication setup

... no, they don't.

anyone have any info on this?

yes, they do.

the mysql forums suggest that mysql is going to the wrong binary log, but i dont think thats the case here

verify it

verified

hell``: It means you binary.index references a file which doesn't exist on disk.

i checked that
they all exist on disk

I'm having a really difficult time dropping a column that is an index and a foreign key. I finally managed to drop the parent table, but can't drop a column that references that table or another column that's another foreign key and index to another parent table (that's still around).
Even setting SET FOREIGN_KEY_CHECKS=0; isn't helping.
They're InnoDB tables (obviously).

did you drop the foreign key on the parent table?

One of the tables is still serving other tables with that foreign key, so no. The other parent table, i dropped completely.

[RainMkr]: You are wrong.

Um. Lose the nick.

Hardly, but live whatever delusions you like.

I'll pass, thanks.

[RainMkr]: What is so wrong about them?
The only bad thing is the lack of triggers support.

Consistent for same version number across platforms? Nope.

What do you mean?
The Windows version is awesome at least.
Maybe you use the Atari OS version.

Properly reads and write my.cnf file? Nope
How about you pretend I know more about them than you do.

How about not?
Unless you can make rain... =Z
They are very useful for a lot of things.
Power abuse.

lo
l

Did he *not* ask me to make it rain?

[RainMkr]: Dude! It's raining!

does anyone know why mysql has not put up 5.0.41 for MAX, only 5.0.37 for download?

MAX no longer exists

hey all
could someone explain the difference between decimal and float datatypes?

What's max?

really.. so the standard version now includes support for cluster?

erm, MAX?

not MIN

Density?

!m whalesh4rk datatypes

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

!m whalesh4rk data types

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

thanks!

Looks like DECIMAL is precise, whereas float isn't.

[RainMkr]: I still see the max listed on the community server download page and no notation about chagnes for cluster support. The release notes dont specify either; if max is no longer supported where is support for cluster available from? (other then source)

You've got precision issues when dealing with decimal fractions in binary interpretation. For some purposes (usually financial ops) that's not acceptable.
Effectively you're doing something like storing a whole integer plus a decimal shift.

i'm running mythtv and it uses mysql...can someone tell me the command to fix the database?

you'll probably have to clarify. "fix" implies something broke

could you be a -little- more specific

i'm trying sorry

william + amanda? are you a transexual from transylvania?

seems there was a command to check and possibly fix any problems

heh, that would be nice. a "fix me" button.

dkr....maybe a cross dresser

I suspect you need to read the mythtv docs. maybe they supply a script for setting up the dbs in mysql for mythtv to use

go to Staples, they have an "easy" button.

ok guys....i need a break....i'm a recent windows conversion

generally successful requests for help are along the lines of. here is my situation. here is what i have done. here is what I expect to happen. here is what is happening instead or here is the error I get. how do I fix it.
you can't skip to the last step.

i just remember someone from one of the chat rooms supplying a command to check the database
and in the command ...there was a switch to fix any problems

http://dev.mysql.com/doc/refman/5.0/en/table-maintenance.html
sounds like myisamchk referred to inthose docs

can anyone show me a site or lead me on where to start looking in order to optimize the MySQL Query Planner.
?

Can there be no subqueries beyond WHERE?

yes. But your question is phrased awkwardly.

... WHERE id = (SELECT id FROM ...) ...?
MySQL seems to bark at it.

won't that subquery return a list? so you need IN instead of =

Not if it has LIMIT 1? =Z
IN doesn't feel right in this case.
It is not supposed to check if there is an id, but find the id.

what did mysql say exactly when it "barked"?

how did bark

Well... it's kind of tedious to check. Gimme a minute.

lol

Oh. I'm an idiot.
It just didn't return anything... :$

You're looking for an argument?

heh

So THAT's why it refused to show an error...
What do you mean?

Oh. I'm an idiot.

=Z
Well, I'd like an argument about the possibilites of INTEGER reading its limit.
And what happens then, etc.

"reading"?

what is the exact error message

There was no error message. I realized that it simply didn't return anything.

if you assign higher than max value it will store the max value

probably nothing found

I mean an auto_incrementing INTEGER.
Yeah... that's what I mean!
1, 2, 3 ... MAX_SIZE_OF_INTEGER, ???
An evil hacker could set up a script to create users until my integer reaches its limit.

at which point your application will gracefully catch the error message when inserting a new user fails.

how do you check if a table is scanning or seeking ?

Am I supposed to check for this? And what happens after that? No more users can be created?

insert fail with duplicate key when auto_inc hit max

of course. you should aways check for errors, give them a message to notify the admin,

if you want to simulate to test, use a tinyint and easily hit 255

or just set auto_increment to 1 less than amx yourself,
max

I'm not scared of that particular event.
But what happens when it has reached its limit.

if you insert 1 user every second, it takes 130 years to hit the max for unsigned int

Will there be no more users then?
Hrm.

correct, you'd have to alter the table to change the int to a bigint, or delete the bogus users,

What do you mean "or"? If somebody becomes a member in the middle of the flood of bad users, then I can't just delete them.
They will hold up all the slots.
Also, what if this was done to a BIGINT to begin with?

how did you calculate this?

anyone?

detection of precisely which are bogus users is left as an exercise for you.

My point being that once incremented for a real user, any bogus users before that won't matter.
Since their slots are taken already.

4.2 billion / ( 365 days * 24 hours * 60 min * 60 sec)

you can renumber once you remove bogus ones

how do you check if a table is scanning or seeking ?

if you have a database, that is so big, that you reached at one table the limit of bigint, than I suppose, you have other issues ... than you first will need more disc space

maybe explain

erm!?
I don't think so :/

but just repeating himself was working so well

an integer has 4 bytes an bigint 8bytes

yes, so can use 2^32

Hehe...

TaSo what do you mean by scanning and seeking

that are only 2*10^9

Well... I'm a pessemist.

not 4*10^12

!man BIGINT

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

wether the query is doing a scan or a seek

it si 4 if you use unsigned

Unsigned means bigger positive, right?

the other way is that is use the timestamp to check, timestamp or epcoh time reach max from 1970 to 2037 and it is using signed int

I always confuse signed and unsigned.

so unsigned will gives double

Are you distinguishing timestamp and epoch now? Aren't they the same?

they are the same
i use or
you mean full table scan or index scan ?

both

i don;t know what you meant by scan and seek
does scan mean full table, seek mean index scan

however, I never heard from a database with more than 3*10^9 rows
at one table

I need to know the status of MySQL

usigned int is about 4*10^9
what status?

wither or not the query is being SCANNED or SEEKED

i don;t know what you mean byt scan or seek

...
they're way MySQL fetches data
they either fetch data through scans or seeks

how to scan and how to seek

omg
nevermind, someone else will answer this

explain the difference

im having issues with mysql compiling, i installed gcc via yum installer, and im getting a g++ error when make of mysql, ../depcomp: line 512: exec: g++: not found
any ideas?

that's probably because you need g++ and not gcc :P

isnt gcc include g++?

(was a joke)

oh.

hrmm
well it's probably just not searching for gcc in the right path

thats in the config file?

hrmm
good question; suppose someone else can help you with that one

i asked my distro chan, said to get gcc-c++

weird

Does anybody ever use SMALLINT and MEDIUMINT?

yes. but that's a silly question

Same goes for C short and such.

in fact I recently changed a lot of bigint fields to unsigned smallints since they were fields that are never going to have more than 2**16 entries

C short?

c sharp?

it saved me a lot of space

C flat and C and C sharp
or C minor and C sharp

can anyone show me a site or lead me on where to start looking in order to optimize the MySQL Query Planner.
also, how can you tell if your current query is doing a scan or a seek

if you look at explain type column, thats all the way mysql read the tables
!man explain

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

I mean the "short" type in C.
I always use an int in C.
Sadly, C has no boolean.

C99 does

map to char ?

Map to char?
Yes, but _Bool is not pretty.

lies a typedef

boolean in C is probably map to char

Alias for?

hi all
i have a user who is asking for SUPER privs because he wants to use "SHOW INNODB STATUS." I'm thinking there's a more specific permission for this.

is there a reason why he need show innodb status

performance debugging, i guess. is it not typically useful?

usually meant for admin or developer
possible for performance debugging

right, kinda admin, but production db so i'm giving him readonly on everything and specific admin rights. i can't figure out what will give show status though.

can someone please suggest a good app for modeling and generating the code for a database?

ur brane?
ruby on rails?
etc.

show status no need any priv
not sure about show innodb status

yeah, that's the q

easy to test it

Where can I find information about changing a MySQL password?

show innodb status;

Access denied; you need the SUPER privilege for this operation
there we go

yes, it seems to require super, show status no need

hello

and with version 5.0, show status provides a lot of innodb performance counter too

SELECT COUNT(*) from webpage_views
GROUP BY minute(datetime)
what's wrong with that?
datetime is a field

#08S01Bad handshake at

!m SnakeO_ reserved words

SnakeO_ see http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

!m passwords

anyone seen that error before when using Net::MySQL

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

That looks malformed.

so how woudl I resolve that?

you never use the correct syntax

because at this point I *cant* change the field name from 'datetime' to something else

!man password

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

thank you for correcting me.

there are way, read the url, maybe it is there

#1062 - Duplicate entry '9594164' for key 1
wat makes that error when there really isnt a duplicate?

you sure it is not duplicate, try a select

what do you mean?

to confirm the value is not it the table
what makes you so sure this is not a duplicate

because they were exported from another database hosting and i phisically checked
but maYBE im not understanding the error
9594164 is the unique id?

that is the entry, yes

ok so what is key 1 refering to?

What set of options would you guys recommend I use with mysqldump in a cronscript for nightly rsync backups?
(I'm almost completely new to MySQL stuff and the backup script for my homedir must run un-privileged, but some of my apps don't offer "use a serverless DB and store in ~" as an alternative)

Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) at test2.pl line 10
Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
i'm running MySQL on debian

is it hard to port sybase stuff to mysql?
anyone done it...

lol

or tried.

i think i found the problem
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
i neglected that

hmm
set @var = count(*) from sometable where something=something

try to avoid 0 in auto_inc column

that was my problem

wrong syntax

see the dump is huge
and was hanging

what would a right one look like...

so i started copy and opasting

I need to do a trigger, that counts the rows with some ID

but i neglected that line

and changes a certain table row with that number

hmm i installed mysql after compile, i have 3 folders, include, info and lib
shouldnt there be more?

kimseong, what am I typing wrong?

omg
Showing rows 0 - 29 (21,171 total, Query took 0.0251 sec)

you need a full select statement, if inside trigger can use select .. into varname ..
if outside trigger code, use select @var:=count(*) from ...

aha so something like select count(*) as var from sometable where something=something
its inside the trigger code

into var

right
lemme try it
thank you btw

um
how do i make it so backup doesnt timeout?
dump

.

maximum length of created queries?

max_allowed_packet ?

not working

What! not working? Really! did you look in the error log?

select count(*) into @var from xbt_snatched where fid=NEW.fid;
update torrents SET times_completed = (@var + 1) where id = NEW.fid;
error

hi everyone... i am having some problems with mysqldump and a large database (~10GB) and was wondering if anyone can help... connection keeps dropping out when mysqldump gets to a ~9GB table (each row no larger than 80MB)... I have detailed information in a forum post @ http://forums.mysql.com/read.php?35,157548,157548#msg-157548
if anyone could help me out it would be so very awesome

did you use single row insert? for a huge table the multi row insert is likely to hit the max_allowed_packet

filename ? should be before the

umm... i am not sure i follow exactly with regards to the single row insert... is this a parameter you can give to mysqldump when running it? and i wasn't aware that parameters need to be given before the ... they seem to work fine for me, eg single_transaction verbose etc

/dev/null &

[root@localhost bin]# chown: `mysql': invalid user
any way to fix?

is there a way to export directly to another mysql directly?

kimseong are you referring to --extended-insert by any chance?

what type of user does mysql need for the server?

can someone help me? how come i drop user user; but i still can connect to my sql with user account?

opt default the extended-insert, i suggest single row insert if the table is 9GB since that 9GB insert statement will be larger than the max of max_allowed_packet

hey im displaying a date, and if it is yesterday I want it to display 'yesterday', if its today I want it to display 'today' and if its some other day I want the date
how dod I do this?
do*

disable extended-insert=0

can someone help me with that?

!m DavidCraft control flow

DavidCraft see http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

check with select current_user() , user(); after connected to make sure it is using which user

hmm right...

kimseong, can i message you?
wait
let me paste it here , it's not big
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 'select current_user() , user()' at line 2

con select is a reserved word you could try `select`

error on select? what do you have before select ?

the_wench, select with? sorry i'm install sql today, i'm new to it
kimseong, nothing?

try again

i just run the command like you said
select current_user() , user(); ?

prompt?

yeah

try again

same thing kimseong

try this select version();

| 5.0.41 |
oh i got the user con

now try the select current_user(), user() ; again

| con@localhost | con@localhost |

is that the user you drop?

yeah

you have to drop user con@localhost

but it didn't drop
yay, it works, thank you kimseong
kimseong, is it how you add user account
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
that's all right?

!man grant

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

!man create user

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

kimseong if this works i may feel obliged to kiss you...

can you slave off of another slave by making it be a master as well?

it didn't work... looked like it was progressing further but still had the same result...
Error 2013: Lost connection to MySQL server during query when dumping table `trackdata` at row: 1

kimseong, the page you gave me doesn't have grant syntax
how do i grant it
CREATE USER 'con'@'localhost' IDENTIFIED BY PASSWORD 'password'
this is how i create it right?

does mysql support replication from multiple master to one slave

pretty sure it does hey... as long as they all have different id's and the database on both masters is different

I mean the slave connect to two or more masters and do the replication at the same time

might need to test it... i am not sure if you can add two sets of master information into the slave's config file... but if you can add two master-host lines then i don't see why not

su
oop

It seems it doesn't support multiple master configurations, at least in mysql-5.2 branch

should i use http authentication type or cookie people?

just want to make sure...but if i have a foreign key X_id references table(id), i don't ever need to have index(X_id) in that create table statement, yes?
because it's already indexed. in another table.

older version need index to be created explicitly, newer version no need, not too sure about the referenced table

if X_id is not a primary key, it will not have an index by default

multi master (multi-source) is not supported, don;t know when or if it will be supported

i'm using mysql 5 or greater
what are you not too sure about, exactly? about the referenced table?

show indexes from table; will help you

im looking for libmysqlclient.a and mysql.h libs for making proftpd work with mysql, any idea where i can find these libarys?

in the mysql client package? are you looking for a distro pkg or what

i have mysql source install
with client and server

it would be in libmysql directory

i just cannot confirm now if the other table the column you use in the foreign key needs to be index ecplicitly or not
*explicitly

hmm all i see is libmysqlclient.la

thats libtool

previously need to, version 5.0 maybe no longer necessary

it will be in .libs
"man find"

i was under the impression that in order to reference a column from another table, that column HAD to be an index on that referenced table

the referenced column doesnt need to be indexed
although the foreign key will be, the *actual* key

ok, so my initial question, if i have a foreign key X_id references table(id), i don't ever need to have index(X_id)
that's true?

show indexes from table;

because it will be indexed, since i'm making it a foreign key
aye

but if table(id) doesnt have an index, its ok
(but i doubt you would want that)

i don't suppose you have any other ideas for dumping this large table? --extended_insert=0 did not work... still drops out at `trackdata` table at row 0

right

no need for version 5 or starting some later version of 4.1, older version have to
try to add --quick option as well
it create the index for you if it is not indexed

creates what index?

the foreign key column
it need an index for fast checking of foreign key integrity

that should be enabled by the --opt option... but i will give anything a shot

it needs to check the table it references, which doesnt need an index

i though indexes is required on both the referecing col and the referenced col

its not

why not? then it will have to do full table scan to check for fk reference integrity which will be very slow

creating the foreign key adds an indexes on the key column, but not the referenced column
yes, thats true, but nevertheless possible

hi, in a table, if a row with a particular id exists, i want to update it, else i want to insert it
can i do this in one single query? or do i need 2 queries?

!m UrbanMystic insert

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

wtf

Profanity is the inevitable linguistic crutch of the inarticulate blockhead

ok, i see, it does not create index on thereferenced table, but if the referenced table col is not index, fk creation fail

?? why would it fail?

thanks the_wench jbalint

look at the part that says "ON DUPLICATE KEY"

it does fail, i test it

what's it say

errro 150

!perror 140

Wrong create options

!perror 150

Foreign key constraint is incorrectly formed

what'd you type

http://www.pastebin.ca/567723

why arent you using innodb tables?

it is innodb
default to innodb

i was under the impression that in order to reference a column from another table, that column HAD to be an index on that referenced table

you are right there

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

previously the referecing col also need to be index, but it will add the index implicitly if it is not already index

the referenced column doesnt need to be indexed
and it WONT add an index on the referenced column
its prefered by not necessary

fail for me, what version it that
fail on 5.0.27

i dunno . 5.0.40 or so

the_wench, i don't think that completely solves my problem. actually i expressed my situation incorrectly

the_wench is abot

can you check you engine for i9

id(key), menu_id (from another table), language(char 2), translation(varchar)
now if there is an entry say for menu_id=3 and language='en', i want to update the row
but if it isnt there, it shud insert the row

!m UrbanMystic on duplicate key update

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

so i dont think on duplicate key update will work
because the key is not duplicate

do you see the option "on duplicate key update"
then what determine the row is there or not there?

i want to see if the combination of menu_id and language is duplicate
i hope you understand the problem kimseong

create a unique index on (menu_id,language)
you will get dupplicate key after that

u mean i shud make menu_id and language unique
?

since you don;t want duplicat, make it a single unique key of 2 columns

how do i do that?

!man unique

see http://dev.mysql.com/doc/refman/5.0/en/constraint-primary-key.html

anyone know what database proftpd uses for user auth in mysql

!man alter table

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

does proftpd needs mysql
does proftpd needs mysql?

yes
well, not nessisary, but it supports mysql auth

Country, Name, # of people with that name that speak english, # of people with that name that don't ?

thanks guys

don't forget to thank the bots!

i think i should manage from here

then read more from the proftpd product itself

how to reformat the select result?
like the result is %s
i want to get %s/Maildir/

you could use CONCAT(string1, '/Maildir/')

linolium, how to add it into select statement?
select name from table;

qiyong, SELECT CONCAT(colA, '/Maildir/') FROM table;

linolium, is colA a keyword?

it is the name of the column to which you wish to concatenate '/Maildir/'

linolium, thanks

what kinda backup does MySQL Administrator take? It don't take anything from mysqldump, so how can I take a backup?

i though it uses myqldump

The selected file was generated by mysqldump and cannot be restored by this application.

youare trying to backup or restore?

restore
the problem is that the database I am backuping from have only phpmyadmin
so the backup works fine, but the restore gives me a error

then use phpmyadmin to restore

same old kimseong..... always making jokes

Hi there - I'm importing a 350 MB Innodb type database into mysql and it is taking forever. Does anyone know why this might be, and provide a possible solution. Thanks in advance.

sorry, just starting my learning curve for mysql! Googling suggests that dbi can allow external non-mysql db to access mysql tables. Does it work in reverse too? Access external db tables within mysql?

I'm running -- mysql Ver 14.12 Distrib 5.0.32, for pc-linux-gnu (i486) using readline 5.2 -- on Debian Etch

hello
I have a question about postgreSQL. I know this is a MySQL channel, but there is no postgreSQL channel, and I'm hoping some of you may know the answer anyway. Can you define a table such that one of the columns is an array of elements which are composite data types?

no postgresql channel?

4 but I only want the minute value. How do I get it? DATE_FORMAT() didnt

Sqvirt, try #postgresql

not in english, according to the channel list --

SELECT MINUTE(DATEDIFF.....

OK, I stand corrected. Apologies. I wonder why the search function didn't show it
Thank you

Sqvirt, no worries

have you looked at slow query logs?

chadmaynard, no, I'll have a look

!man TEXT

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

chadmaynard, the log is empty

TEXT cannot be told at MySQL level how much it can hold?
It is stuck at 64,000 bytes?

you have to tell it to at startup
!m skas_wk slow query l

skas_wk see http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

chadmaynard, cheers - will have a look.

thx

that is correct

I cannot find the 64 kB mentioned anywhere in the manual, but somebody in here told me yesterday.

that's pretty much the definition of text
did you read the link that the_wench threw back when you said !man TEXT?

manual mention 64*1024 char
but really 64kb

chadmaynard, I reconfigured mysql to default config and the problem seems to have gone away, only took 4 minutes this time.
thanks anyway.
thanks

yuo check the storage requirements?
L+2 bytes, where L 2^16

so this is an odd request, I have a mysql dump here, and all I want is a few rows from it located in diferent areas, is there any way I can perform queries on the actual dump wihout actually having to upload and insert the dump

you can make a new dump file containing the rows you want and upload that instead

I don't understand that...

how do i show what users mysql has in root?

chadmaynard, it's an already made dump from 2004
i dont have the actual database
it's long gone

2^16 is 64*1024 64kb

Oookay... =Z

then just reload the dump

of course you can also open the dump file in an editor and copy those rows

^ that was what i meant.... or use something like grep if possible

its a whole bunch of rows, and it'd take hours for me to find what I'm looking for

search feature available in editor

or grep

i was hoping there was some program that could load the dump into memory as a psuedo database and run SELECT queries on it

it's called MySQL
except for the pseudo part.... but you can drop it when you're done

yeah I know, I didnt't want to install mysql server in windows just to do this one thing

just takes minutes to install

is there anything special i need to do to install a 4.0 database in 5.1?

there may be some oddities to your .sql file. For example mysqldump included with 4.0 didn't put ` around identifiers so if you named a column `default` it will error out

5.1 has no isam engine
probably not much a problem with dump file

no wait, 5.0 is the stable release, I'll just get that

"5.1 has no isam engine" -- ?!?!!?!?!?!?!?!
MySQL 5.1 drops MyISAM?

ISAM, not MyISAM

Oooh.

is this benchmark accurate?: http://tweakers.net/reviews/649/7

Wasn't ISAM deprecated in like 1995?

i thought 5.0 had removed it. apparently not

Was there any point of ISAM?
I thought it was a very ancient engine used in the beginning.

ISAM is a very old way of storing indexed data, MySQL didn't invent it.
MyISAM probably just improved some things in an incompatible way

is there a show statement to learn the default charset for mysql, a database and/or the tables?

Have you ever used MEDIUMINT or SMALLINT?

show variables like '%char%'; ?
or even char%

thanks chadmaynard

What are you doing now?

setting the default charsets to utf8 towerniece

Have you ever used MEDIUMINT or SMALLINT? I can't see any situation in which this would be necessary.

they use less disk space so if you have large tables and numbers that you know won't go over the limit for medium or small ints, you can shrink table size by using them

and possibly speed up retreives and transfer over bandwidth
though the bandwith thing now that I think about it - maybe not

Hmm. I guess.

in .my.cnf instead of entering a plain-text password, is there a way to use a password hash, such as md5 in this file ?

I've never seen a password in a my.cnf file... what is it doing there?

not my.cnf, .my.cnf

ah

Greetings

the answer is no. The file shouldn't be readable by just anyone, right?

help please - I am setting up 5.0.27 on a new PC, under Win2000 - at the end, it can't complete the config, says "can't connect to MySQL server on localhost"
there is no firewall, I previously had it running fine on a WinNT4 PC, I tried changing the port 3306-3307, tried uninstalling and reinstalling - no good

is the service/process running?

yes
the process starts on bootup fine, and I can start/stop it manually

can you connect with the client?

no

netstat -na shows it listening?

what do I look for in the netstat output?
I don't see the port listed
that is, I do not see the IP for this PC listed with the MySQL port

how about 0.0.0.0 ?
and that port

307

looks like mysql is running on port 3307 then...

hmm - if I run mysql.exe from DOS prompt, it does connect
but if I run it from the start menu, it does not
whatever
in any case, I still cannot complete the instance config

the options must be wrong on the start menu shortcut
probably related
do you need to?

well, I want to give it a real password for root (not blank)
also, there was something else in there that I wanted to set, I forget what
there are several things in the instance config that I do not see in the .ini file

you can change passwords from the client
yea the instance config just applies some common configurations. Really dumbs it down

hmm - so how do I change the root pass from the client?
just tried, said it can't find the user

what'd you type?

"set password for root = password('passwordstring')

set password for 'root'@'localhost'

ah

or just set passwoed
since you are changing the password of the current user

that time it went through
ok, at least the password is set :-)
but, will that password work for root access from other PCs connecting on the LAN?

no
not until you tell it tp
to*

how do I tell it to?
do I have to set up root@192.168.0.* ?
(so to speak)

yes

can I use * or do I have to specify each IP?

% is the wildcard

thanks
looks like maybe I have to add the user first?

yup

ok.......

GRANT

ah - I remember reading about that one.....

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.0.%' IDENTIFIED BY 'chadmaynard'; - for example

one thing I don't understand, why did I not have to go through this on the setup on the WinNT4 PC?

i guess the config did it? I don't know much about the windows installers...

hmm - still says can't find matching row in user table

the above grant will create the user and set the password to chadmaynard

success! :-)
I'm running EMS MySQL Manager, which includes User Management, so I ran that on the localhost, which CAN connect, to set up the "global" root user (192.168.0.%), which worked, so now I can successfully connect from the workstation PC as root :-)
thanks greatly, chadmaynard

np

anyone know how you use logrotate with mysql??

alright, so now I'm attempting to use mysql administrator to get data off this table on localhost, but when I try to view a large table, it tells me that it cannot connect to localhost

simple question, how do you show a table via commandline
forgot command

SELECT * FROM table

that would show all the rows and colums for "table" ?

yes
actually you know what
mysql administrator isnt working at all for me for a localhost table
it keeps telling me it cannot connect to localhost, but i can via commandline, what is up
nothing?

hey guys, where do you say how long to wait until a query replicates?

What happens if you have an autonumber of type integer and you run out of values? does it crash? restart using unused numbers?
...Anyone?

ive got 3 rows and 6 coloums, how do i change one of the entrys for one of the coloumns?

!m Kupo24z update

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

/msg NickServ IDENTIFY
/msg NickServ IDENTIFY tashju

what want to say that?
Data truncated for column 'precio_venta' at row 1
i s a error?

hola. Nice topic
I seem to be too dumb for mysql. I have a field with an int and want a query which adds 200 to every int
Anybody got an Idea? I think its too early for me atm

only occur when i update the values of the row

UPDATE your_table SET int_column = int_column + 200;
Basic SQL statement so you better learn ABC of SQL

thank you, I already woke up and got it
I used to work a lot with SQL (MS SQL mostly), but as I said, I was still kinda asleep

If the column is say CHAR(4) and you try to set it to "abcdef" it will be truncated to "abcd"
Lack of caffeine in your blood is always bad

FullAck

thanks, i'm going to check

ok, im here in the mysql query browser, i run a select query, now I want to export the data into an sql dump that i can import into another database, how do I go about this?

Saimilar with INT columns if the number is bigger than the range, but then you get warning "Out of range value"

mm, i think the dates are right
the column have float value

im guessing this isnt possible then

for default 1

i guess i'll need to upload this database to a server with mysql
err
a server with phpmyadmin

i don't know
with the program execute the command and give the data truncated

anyone up for a crazy replication question?

I don't think anyone joined the channel recently
!seen anyone

but however, with mysql query browser, the dates inserted are fine

ok here goes
I have a datawarehouse that is a slave to our master db server. Can I make it also be a master and slave not just the one db but all db's off to another box that could be used for backup purposes.
told you it was crazy

if I want to backup a very large Innodb table. What files should I copy? I am copying the whole data directory en my.cnf. what else?

server needs to be stopped
you also might need the innodb log files

s/might/must

I'm using a mysql frontend to connect to my hosted db, and I can't tell if whoever set this up set up any indexes, is there some sql command I can type to look?

I see nothing crazy in your question, but I don't quite get the question itself.

master sends db1 to slave (datawarehouse) I want it to also be a master and send everything to its own slave

By default all databases are replicated. You have to limit it to particular databases or tables if you want such. If you want to replicate all just get rid of repllicate-* options in my.cnf

that makes sense

SHOW CREATE TABLE your_table;

S2 make sure you turn on log_slave_updates on S1

ok
could the compression be used between S1 and S2?
slave_compressed_protocol

hi all
i have the strangest of problems
http://pastebin.com/929421
basically, it says the "immobili.ID" field in the join ON doesn't exist...

scooby2, the server is stopped. and which innodb log files are these? ib_logfileN ?

i know the query is overly complicated, it's autogenerated by MDB PHP class
but aside from the needless ASes, it should work!

correct. unless you know everything has been committed

no one can help? i have about 20 minutes to find the bug :-)

thanks a ton

i suppose there's someething to do with mixing implicit and LEFT explicit joins

Yep. You must take care of the order of join conditions too

changing all the joins to nested LEFTs could solve something?

Then you change the type of the join.

as i see it, the category and contrat tables are foreign keys on immobili
so using a LEFT would pull the correct value anyway
am i reasoning it wrong?

(immobili LEFT JOIN immagini), categorie,contratti and categorie,contratti, (immobili LEFT JOIN immagini) can be possible solutions depending on what you need

you can do that?

That's one of rare cases when INNER JOIN syntax will make it more readable ...

i'll try it now

I don't know what exactly you need, but note that these are different joins. When mixing inner and outer joins the order of the tables matter
Just because it matters for outer joins

basically, immobili is the "main" table here, which is a real estate list

so try: categorie INNER JOIN contratti INNER JOIN immobili LEFT JOIN immagini and see if that's what you want.

i need to pull all the houses in the table, with corresponding contract and category

Of course you need ON clause for each join above

but not all houses have an image
and i need both those that have it and those that don't
salle, could you be so kind as to show me the exact syntax i should use, with shorter field names ofc

FROM a INNER JOIN b ON (a.c1 = b.c2) LEFT JOIN c ON (b.c3 = c.c4)

no parentheses?
i was thinking of somethin along
((immobili INNER JOIN categorie ON immobili.categorie_ID = categorie.ID) INNER JOIN contratti ON immobili.contratti_ID = contratti.ID) LEFT JOIN immagini ON immobili.ID = immagini.immobili.ID

No real need.
But I think you need: immobili LEFT JOIN immagini first and then INNER JOINS to the other two tables

Specified key was too long; max key length is 1000 bytes
what solution for this error?

The result will be the same actualy.
Don't use suh long keys

the result is not the same :-)
simply remove the image from the join :-)

salle, Don't understanf

i do that query on a LIMIT
and i only pull 3 at a time

hehe

that means i can just pull the 3 images with a second query, and add them in somehow

3 random images?

i'm outputting XML out of that
no, 3 images using an immobili_ID IN() thingis
i pull the 3 houses for the page
get their keys while i'm iterating them
then SELECT * FROM immagini WHERE immobili_ID IN() GROUP BY immobili.ID LIMIT 0,3 et voila :-)
there's prolly a DISTINCT somewhere in there :-)
the performance hit shouldn't even be that big
there's 2 queries but way smaller

As a form of backup "insurance", I am going to dump out everything. When I tried doing this I go "TableX has crashed". The dumped stopped. Is there a command I can run to simply scan all tables in all databases and repair them? Or at least scan all tables in all databases and list broken tables only?
Or at least scan all tables and give output

Specified key was too long; max key length is 1000 bytes

ok, sorted it, thanks a lot!

this is bug

DELETE FROM `users` WHERE `users`.`uid` = 9 LIMIT 1 is giving me error 1604 pls ?

mysqlcheck
It has lot of options, but start with --all-databases

salle. thanks

The options are not rocket science mysqlcheck --all-databases --auto-repair

or just man mysqlcheck

Yancho, don't you have too many backticks in there? i never use them, so i might be wrong :-)

Yancho, look up what 1604 is

salle. hehe. exactly what i did. weird that is doesn't have a man page. at least not on my machine

Always post the entire error message please.

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

Some non-printing character. The syntax is OK. Just retype it

salle im not typing it .. using phpmyadmin .. selecting the row and then delete
DELETE FROM 'users' WHERE uid = 9 LIMIT 1 it is giving me : You probably tried to upload too large file. Please refer to documentation for ways to workaround this limit.

Yancho.
Yancho, do DELETE FROM `users` WHERE ....
make the quote a backtick

Stop using phpmyadmin then .... that error is crazy

Yancho. I can almost guarentee you that you're query will work if you use the backticks

kk trying mysql query
using mysql query worked

or not quoting names at all
You don't need to quote table and column names except if they contain some speical characters
like - for example

oki .. but i wasnt typing the query itself .. i was just checking the box at phpmyadmin and pressing with checked delete
another question pls .. is it possible to reset the autoincrement ids ?

salle, I heard about reserved words. Do I have to quote reserved words?

You? No
Someone else? Yes
ALTER TABLE tbl AUTOINCREMENT=0;

thanks salle

Access denied for user 'centraldns'@'%' to database 'psa'
any idea anyone?
remote logins fail...
after updating mysql

Sorry it's AUTO_INCREMENT=0

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 'AUTOINCREMENT=0' at line 1
ow ok
Query returned no resultset means it worked right ?

anyone have any ideas?

Did you run mysql_install_db during the upgrade?
If yes you lost all your users and their privileges.

well
login from LOCALHOST dose work tho
Not remote logins.
how can i fix this

SHOW GRANTS FOR 'centraldns'@'%';

se
there is about umm
400+ users
this dose not work on :/
one sec sallie
salle***
SHOW GRANTS FOR 'centraldns'@'%';
GRANT USAGE ON *.* TO 'centraldns'@'%' IDENTIFIED BY PASSWORD 'blah';
hrmhmrm... i dopnt nkow :/
this update was on centos.

does mysql run well on a cluster server compared to a normal server?

usage means no priv

?
np priv?
np priv im not msg no one :/

I am talking about the community edition. Or do I need to use MySQL Cluster?

just tring to get mysql working :/

what cluster server?

kimseong, i remember reading something called "MySQL Cluster" or something

yes there is
better read more before you decide to use it

kimseong, wait. i spoke wrong. I wanted to know if Mysql runs well on a "linux cluster"

anyone have a idea?

what linux cluster? linux ha? redhat cluster?

kimseong, probably redhat

shared disk cluster should be fine if you do active-passive

kimseong, is redhat only shared disk?

and this has nothing to do with mysql cluster
i don;t know
drbd works to replcate the disk level synchronously

guess not.

how do i setup a mysql databasse on my local machine

eagles0513875, you install it. sounds reasonable, hm?
*sigh*

i have it installed
lol
just need help configuring it

configure what?

so no one here can help with my access denied problem?
with remote logins?

eddie__, mysql.com/grant, read it once more!

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

configure the mysql server on my local machine
just thought i would let u know his is on kubuntu feisty linux x64
https://help.ubuntu.com/community/Repositories?action=show&redirect=AddingRepositoriesHowto
is that what i should follow to get it up and running
wrong link
https://help.ubuntu.com/community/ApacheMySQLPHP
this link

i ahve tried all this before.

Access denied for user 'root'@'localhost' (using password: NO)

you are not using password, are you?

i hate to say this but... it's pretty self explanatory

i followed this link
https://help.ubuntu.com/community/ApacheMySQLPHP
and it had me set one up

then use the one you set up

so i just type in mysql -u root password

no
you type mysql -u root -p

what do i use
ahhh ok
fyi im total noob to mysql

you should also type 'man mysql'

ill do that

hi, i'm selecting a lot of data and want to make sure it only returns values where a single specific field always has a unique value. How do I do this? Can DISTINCT do that?

yes

i thought it was only for the entire row
group by works, but i can't order the grouped data to get the one i want

distinct is for a row try group by field
groupwise max

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

hmm slow bot time for a server reboot

ooh, ta

excuse

isn't it strange that you have call ALTER TABLE when you want to add a new weapon ?

listen to daddy!!

hmm slowness is also a dodgy network here atm

oh

excuse

"my opposition to using fulltext is because I'm searching on a 30 character model number, so it's not a large amount of text"

better

ha, that's better
excuse

isn't it strange that you have call ALTER TABLE when you want to add a new weapon ?

it hasnt got that many excuses

i wish i wasn't the only one in the company that knows how to create an svn branch

hehe
delagate

it just means everything goes into trunk and trunk is never stable

and get your system screwed

yes, tell your boss you need a week off to prepare svn training, then another week to train the people

we things go live we spend a few hours chopping out the stuff we don't want to go live yet

and another week to fix the shit after they play

heh, i wouldn't get an hour

how do i connect to the database using my sql administrator
i just went throught man mysql that didnt tell me anything

hi
I'm trying to import data into database (in mysql console 'source ...').. they are in utf-8, database connection is utf-8 and the database itself is utf-8 too but still, the imported texts are broken

how do i connect to my database using mysql administrator

I'm not sure, it's been a long time since I used it, but there are some input boxes and they say exactly, what you are supposed to write in

what goes in the server host name the name of the database

host name is probably localhost

username is would that be root@localhost or the other user i created

yes

its not able to connect

thanks for nothing.

?

mysql -u root -p database

ok now what

enter your passswd.

ok i did

507am tme too sleep.

Related Topic: