someone that knows about epoch?

http://pasteosaurus.com/264 thanks

jabbadoo_, do you mean in datetime values?
Alex_Gaynor, any particular reason to use bigint(20) for user_id and other primary keys. do you expect this database to outgrow a regular integer?

Hrm, probably not, although I won’t swear to it, can I just up it later if I need it?

Hello community .. I need help concerning an encoding issue

gnari, yes

how can i set a field to 2 digits instead of 1?
e.g: 01 instead of 1, 02 instead of 2….

zerofill

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

I have a website written in PHP, old database’s charset was set to latin1, data were in Arabic and I used windows-1256 in the meta tag for the page encoding .. But I want to convert the DB from latin1 to utf-8 . I tried so converting them but results on page were like ?? ???? ??? ????? … I
changed the encoding of the page to utf-8 but still the same ugly results! I created another database with utf-8 and reinserted data using phpmyadmin .. but still the
same problem .. any tip or help will be appreciated

jabbadoo_, what do you want to do?

Okay. I need some help. How do I query mysql to sort by a certain field descending?

[...] order by fieldname desc

Like, “SELECT * FROM `table` ORDER BY `field` DESC?
Okay.

yes.

Thanks, pizza_biz
pizza_biz++

gnari, transform a float into date/varchar and transform a date/varchar into float from epoch

hello when creating a table is there anyway to set the default to the maximum int of a column?

why not use AUTO_INCREMENT?

its not a pk

I have a website written in PHP, old database’s charset was set to latin1, data were in Arabic and I used windows-1256 in the meta tag for the page encoding .. But I want to convert the DB from latin1 to utf-8 . I tried so converting them but results on page were like ?? ???? ??? ????? … I
changed the encoding of the page to utf-8 but still the same ugly results! I created another database with utf-8 and reinserted data using phpmyadmin .. but still th
e same problem .. any tip or help will be appreciated

are you using a new mysql?

jabbadoo_, you would probably use unix_timestamp() and from_unixtime(), although i do not know about the fractional part

hmm, svn.mysql.com seems to be dead

Hello! Any help people?

charset

http://mysqldump.azundris.com/archives/60-Handling-character-sets.html

read that url

thanks

gnari, that’s exactly what i needed, thx a lot!

is there no column setting where i can set the amount of zeros?

zerofill

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

in a php mysql web hosting statement how can i seperate 2 conditions? like ( name = uname and name 2 = uname2 and gid = gid ) OR ( name = uname2 and name 2 = uname and gid = gid) ?

just like that

thanks

Bear10, in this particular case the parentheses were not needed, though

how do I give a column of a table a default value?

add a default value in the column definition.

Just remember default values cannot be functions
(the output of a function)

i just wanna set it to 0

DEFAULT 0

does the table exist already? you’ll need to ALTER TABLE if you want to add a default.

na, im creating the table
writing the CREATE TABLE thing

[...] foo INT DEFAULT 0, [...]

Exactly

does mysql care if I use integer instead of int

i think they’re aliases.

k

the manual covers the various datatypes.

is there a way to use a wildcard to drop multiple tables w/out dropping the entire db, short of a script?

prefix drop

http://archivist.info/search/index.php/Prefix_drop_table

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

ty
i think this will work just fine
it was about what what i would have written, but saves me the time

hey, quick question…I’m running a massive update on user data in a database…I want to back up stuff in case things go wrong..is there an easy way to use phpmyadmin to back up current db?

pMA has an Export tab.
Some people have reported some issues with the backup it generates, depending on the version.

thx
yeah
that’s my concern
right now, I’ve used it for that database

after you order by a field, how do you figure out the position of a row?

although, I don’t get the “file name template” stuff

It just means that you can use __TABLE__ and the file will be called nameOfYourTable.sql

oh, ok
Thx much!

dam
i’m doing something like ’select id from my table order by score where uid=5′ , now i want to figure how how far down the list, score is…. how?

It’s easiest to handle it in your application. You can use a user variable, e.g. SET @row := 0; SELECT id, @row := @row + 1 AS position FROM…

ok thanks snoyes
in command this works good, but doesn’t seem to work calling as mysql query all in one string
SET @row := 0; SELECT @row:= @row+1 AS position FROM know_summary WHERE uid=8824877 ORDER BY score

mysql_query doesn’t allow query stacking. Make two separate calls to mysql_query, or use mysqli_multi_query

ok thanks snoyes
your knowledge amazesme

Error dropping database (can’t rmdir ‘./social/’, errno: 17) mean? I get it when trying to drop a database

!perror 17

File exists

What do I do about it?

drop the database, then the file won’t exist.

I get that error when I try to drop the DB

You get that error when the directory can’t be removed. Perhaps the user as which mysql is running doesn’t have permissions.
Or perhaps there is some other file that’s been stuck in there that doesn’t belong.

Ok snoyes you type faster.

a) Is it possible to set up multi-master, multi-slave replication (ie, A-BC, B-AC, C-AB)? b) Can anyone point me to a good resource about said configuration?

Not like that, but you can do A-B-C-A

rmdir won’t run if there is a file in the directory.

No slave may serve to masters.

Its empty, I found it on my HDD, can I just delete the folder manually?

I assume it’s in the mysql hosting data dir?

http://dev.mysql.com/tech-resources/articles/advanced-mysql-replication.html

Yeah, I’m on OSX, and its in MAMP-db-mysql

you might stop the server first, the do ‘rmdir -rf social’
you might stop the mysql server first, the do ‘rmdir -rf social’
using the full path to the dir you want to remove

Yeah, I have a little CLI experience
Yay, its gone

im having some trouble setting the mysql root password
i was following the steps of a tutorial

gotta be careful advising the use of ‘-rf’ ;^)

haha, nice snowman

sorry, I just see text.

mysqladmin -u root password ‘foobar’

I don’t know what you did but after ‘-rf’ I see a snowman :/

but i mistakenly typed mysqladmin -u root -password ‘foobar’

; ^ )

and then tried again the original way, but was told access denied
mysqladmin -u root -p
it lets me in
mysqladmin -u root password ‘foobar’
error: ‘Access denied for user ‘root’@'localhost’ (using password: NO)’

either -p or –password

Any oppinions on table types for this: http://pasteosaurus.com/266 I’m thinking MyIASM for all besides the _mod ones which will be InnoDB

but i mean it appears i have no password set, because it doesn’t prompt me for one when i use -p/–password
but if i try to change it, it tells me “access denied”

innodb with logging is safest for disaster recovery. it can be slower on selects than myisam. big players use innodb masters, replicating to myisam slaves, and let the slaves handle the selects from the public.

much appreciated

I’m not that big, I figured the tables that would be mostly writes are the _mod ones, which keep track of user moderation of content ala digg/pligg

if you don’t have to worry about superhigh performance, you’re better off with innodb.

Well, for now its not super important, but presumabely if it ever gets really big the performance might be worth it right?

the difference can be important, but we’re talking eBay size.

Well, I don’t rationally expect to get that big, but I don’t want to plan for failure after all, howe difficult would it be to switch down the road, if I need the performance and have exuasted all other options?

a simple alter table

Oh, you can switch on the fly, i never realized that
So InnoDB on all, now I need to figure out the indexes

far more important for performance, really.

These indexes look ok: http://pasteosaurus.com/267

you won’t know till you match them up with the sql hosting queries and see. remember to use ‘explain’ before your queries and that will tell you what indexes are being used. Indexes are easy to add/drop.

I guess its time to go make the tables, and start coding the app then!

enjoy!

Yep, I’ll be back in a few hours with 100000001 questions
What type should I make my indexes?

trying to migrate an existing mysql install from server A to server B which has an existing install/users, mysqldump will not dump information_schema, any suggestions on how to accomplish?

is there an easy way to divide a TIME field by half?, I would like to hear some tip rather than converting the TIME to hour, second, minute, dividing by 2 and reformat

the default is fine

lo all

So 257 questions?

Has anyone tried to import a compressed SQL file into mysql?
I was trying to do so with a download of wikipedia’s link categories, and even though i ran it overnight – it just didn’t finish!

Haha, sure, I just took my Comp Engineering(ECET-100) Final today

(Size of the file’s about 1GiB)
Is there an efficient way of doing this rather than mysql -u user -p dbname file.sql?

If it is compressed you couldn’t do that anyway.

mhillyer, ah yes, i realized i couldnt so i had to gunzip it. or use bzcat on the sql.bz2

You could try SOURCE file.sql from within the client, but it should be the same. You might bypass some packet limits.

Crud, the description column I supposed to be 400 chars max, thats too long for a CHAR, should I use text instead?

hey, I’m seeing the message [Warning] Could not increase number of max_open_files to more than 65535 (request: 81930) .. then shortly after the server stops taking new connections.. is there a way to fix this?

man ulimit
but you’d better review your system, something must be fishy there

how so?

mhillyer, hmm, I did tweak my my.cnf file to something like this – http://rafb.net/p/s2giPa78.html – but that doesn’t seemed to have helped

one of the (more poorly written) programs that uses it constantly opens and closes connections.. but it should never hit more then 300 concurrent connections (bad, I know.. we are in the process of replacing it)

perhaps there’s something which i’m overlooking then?

Did you try the SOURCE approach?

mhillyer, well, i haven’t yet – i’m just cleaning up the messed up tables right now..

Use the source…

yep, started it now
hmm, so what exactly does this mean?
Query OK, 13716 rows affected, 2 warnings (0.92 sec)
13716 Duplicates: 0 Warnings: 2

open/closing is not a prob, jus tmake sure you have a thread_cache on the server side

it doesn’t seem to be increasing, OR decreasing in number
or is it that each query modifies those many rows?

hey, whats the default value for wait_timeout

do SHOW WARNINGS directly after that

its kind of a problem when it opens a bucnh, then crashes.. so they arent closed cleanly
or at least, thats what I think the problem is

- so it can’t be interrupted at the moment.

I doubt. if a process crashes, connections would be closed.

oor is there a way to send a mysql job to the bg?

righty. well the above would be the result of one insert or load data line. the warnings will be like data truncation, out of range integers, etc.
ctrl-z then bg
standard unix

hey!

ah i see!

the ctrl-z briefly suspends it, the bg will make it a background process. better to use screen though, but you cant do that with hindsight
aloha

arjenAU, yeah, its on my local machine, so i guess i’m ok
so, does anyone have a clue about how long it might take a 1.1G sql host file to get imported into the db?

10 minutes.

given a dual core 2.2gig intel with 2G of ram

depends entirely on a lot of things. “a while” might be the right answer

right.

But that assumes you have a badass RAID backend.

chances are your server settings aren’t optimal for such an operation…

Is this a legitimate query SELECT sum(Value) FROM table WHERE comment_id=7, and this will return the value of all the entries in table where comment_id is 7 from the column value?

mhillyer, hehe, think “laptop”

I can go with that

arjenAU, indeed. i did tweak my my.cnf file a bit based on som forum postings at mysql..

Come to Canada arjenAU, there’s a great Belgian chocolatier here.
They do factory tours.

we have some here but yes I’m aware. an been there. well, as far as Vancouver at least.

mhillyer, which part of Canada?

did a Cadbury factory tour in Dunedin, New Zealand. that was good

Southern Alberta. The guy is in Calgary.
Bernard Callebaut – http://www.bernardcallebaut.com/
Noo doot aboot it eh!

anybody else have issues with skype right now?

i saw something about that on isc.sans.org today, i thought, in the most recent handler diary there

yeah, couldn’t log in myself

I do arjenAU.
Reports say it’s down for about a day.

well has been for about half a day for me, yes.
that’s nasty.

Yes indeed.

apparently it is an internal software issue with Skype ( http://heartbeat.skype.com/2007/08/problems_with_skype_login.html
)

arjenAU, still around?

is there a way to get the actual create statement for a table from a query?

somewhat

hey again. your idea of using source worked, thanks – the system seems to have imported everything well! but, doing a select * from table limit 1; hasn’t exectued even after 5 minutes!

SHOW CREATE TABLE;
tada
SHOW CREATE TABLE foo; to be proper

thanks

hmm, so it looks like this query is taking eons to execute!
does anyone have recommendations for docs or resources to help increase resources granted to mysql?
i did tweak my.cnf – http://rafb.net/p/s2giPa78.html – not sure if i need to add something else?

hello everybody, i am a newbie with mysql, what is the best way to keep a coherant (relationships) database please ? what features should i look for in the manual please ? where or what google keywords should i look for in order to find example queries to clean a database ?
is it possible to use joins in a delete query ? not as far as i understand (probably not very far) the manual for DELETE
i have a class of objects (15k of them) data stored in 17 tables of the database, note that a few of these tables must keep MyIsam engine because of fulltext index … Thank you in advance for any tip, even the shortest of them

how would i go about enabling all privileges for a current account?

can’t
you need to alredy have them to enabled them

well i can login as root shouldn’t i be able ot change their privileges for other accounts from there?

that would work
it’s the GRANT query type you want

ahh okay than thanks

!man grant

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

thank you

have a schema question regarding storing a “large” (500,000+ records) that will be sorted
if I’m storing each record as a unix epoch time, indexing that column, probably isn’t a good idea, right?

only if there are lots of records at the same second

which there aren’t
so, should I then have “two” columns for time?

hmm. this is weird, i’m sure my cnf file has all the relevant innodb enable option but phpmyadmin still shows that engine as disabled!

nod, that’s usually quite unlikely with timestamps,

any ideas why this should be?

one for “date” and one for “time”

naw, that’d use up a lot more space, which would make finding records even slower

Xinil, depends on what queries you want to make

how should I be storing/sorting then?
is unix epoch not a good idea and I should be using datetime?

does anone else find myphpadmin buggy as hell?

Xinil, will you be sorting by this column?

yes
often

keeps logging me out

it’s very buggy

how do I see the queries coming into my mysqld?

like the actual php part of is n’t too good
it keeps logging me out and refusing to work with the correct pass and such

Xinil, will you be querying on this column, like between x and y ?

sometimes
like, count(*) where dayyesterday and daynow

Xinil, if you are going to make a lot of aggregates based on date, you may be better off with separated day and time

hm

Xinil, but i see no real problem on just indexing your epoch column, either

well

how would i go about checking my privileges for a user? It seems my root user has been restricted for some reason?

500,000+ records, that index is going to be massive, won’t it?
because each record is going to be unique
cardinality will be 500,000+

Xinil, on the other hand, the index’s selectivity will be grand
Xinil, 500000 records is not much for an index, i would not be worrying about that

hmm
well, the growth is quick, so it’ll be reaching 1mil in under a month from now

Xinil, i think it is mostly a matter of how your queries will be. if most queries return relatively few rows, then a indexless sort is not a great pain
Xinil, if most queries are by date or a rage of whole days, a day index is more efficient

well, i’ll be using an indexed where statement

Xinil, but i have divided such a column in the past into a day column and a time column, and regretted it when i had to do queries with timexone adjustments

where mem_id=8 AND timethen AND time now

hey guys is it a bad idea for me to edit the mysql for my permissions manually using a gui like cocoa mysql?
i’m just changing the permissions from N to Y then i just figured i’d reload it and it’d be fine?

thanks for the help gnari. I’ll stick with unix epoch for now, it won’t be too hard to change in the event it turn sugly

Xinil, a good rune is not tooptimize prematurely. keep your schema clean, until you know you have performance problems
Xinil, should be easy for you to test it. just populate a similar table with 5 000 000 rows and see how performance is

unfortunately, i often optimize too little, so I’ve recently begun to delve a lot more into indexing and watching mysql take less and less resources
good plan

Xinil, well indexing is not optimizing in my book. that is just tuning, one should constantly do that

haha hey how do i log in using console from 127.0.0.1 instead of localhost?

Xinil, optimizing is making changes to your table schema or modifying your queries

this is going to sound really weird but i somehow don’t have permissions on localhost but i do on 127.0.0.1

anathematic, have you tried just connecting to 127.0.0.1 ?

how do you mean sorry?

anathematic, what is your connection string?

i’m trying to find out which position someone is based on how high their score is… anyone know how to do this?

well i’d been trying to connect using mysql -u root -p
however i don’t know wh at parametres i need to connect to a certain address?

snoop-, count how many have higher score

i guess i need to find the variable page

anathematic, -h 127.0.0.1

it woudln’t let me connect : “ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1′ (111)”

‘t
rewrite /server-status
whoops
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1 mysqld

i am making a website users table and I have a selection list of 100 checkboxes, which may grow, how should I store that? a field for each or do it bitwise with a double or something?

I didn’t know a process can use more than 100% of a processor, hah

snoop-, what about select count(*) from foo where score(select score from foo where name=’x')

is it legal for a foreign key to reference it’s own table?

yes

foo, dual/quad-core

skull–: no, store them in a separate table

Is that a question?

foo, no, its a statement

skull–: checkboxid, state

a separate table with 300 fields?

To me?

delete from table_foo where id_foo not in (select id_foo from table_bar) ? mysql reports an error at occurence of where word

gah. ok, can someone tell me if i should be facing a ridiculously long time in executing a “select * from blah limit 1″ query on a table with about 16,000,000 records?

checkboxid, state, userid right?

I didn’t know a process can use more than 100% of a processor, hah

skull–: yes

ohhh, err, yeah, I kind of thought that.
Thanks
Now to figure out why it is

foo
vIkSiT, select * is stupid, and yes, it will take ages

tibyke, so assuming i wanted to return just one row of the table – what would you suggest?
oh i see – a select * would select all records, and then return just 1?

no, select * return all columns.

vIkSiT, * is horizontal, fields.

and the “all records” probably takes the long time. hmm.

wrong.

vIkSiT, 16.000.000 and limit and order by is the slow part

hmm i see.
so how would you recommend i work with this table?
assuming i wanted to see the first record of the table?

index the field your order by

For some reason a query from the replication thread (this is a master/slave setup) is what appears to be locking the database… so other queries get stacked up, load spikes… any ideas? I can’t figure it out why this is happening all of a sudden
uFor some reason a query from the replication thread (this is a master/slave setup) is what appears to be locking the database… so other queries get stacked up, load spikes… any ideas? I can’t figure it out why this is happening all of a sudden/u

thumbs, sorry, not sure i followed that

foo, show slave status;

index the field/column you use to order your query.

i could do something of the sort “select col1 from foo order by bar” – but how does that help?

Nothing weird there, other than seconds_behind_master gets 10-500 seconds behind master when this happens

then index the `bar’ column vIkSiT
`bar`, too

foo, and what happens then? whats the error msg?

Nothing weird there other than seconds behind master … eg. there is no error message

an index wont help too much with 16M records and a limit/order by, im afraid
foo, and you wait, and what happens later? it “comes” back?

Usually, yes. But right now it’s constantly locking up the database for some reason. A that’s causing processor usage to be 100%
This has been running fine for months… don’t know why this would happen all of a sudden. Database on this slave is 8.5GB

show processlist;?

What am I looking for there? I just see 90 queries, and one insert from the replication thread… all other threads are in state LOCKED

so you got it

I’ve already said/noticed that.. my question is, why are they locked.
This has been solid for months, and all of a sudden this
No reason for them to get locked up. And I checked all tables with check table … extended; 10 hours ago, everything was fine

other insert/update is locking the table

and probably still is, so I doubt it’s table corruption
Right, so it’s taking longer than normal, I wonder why it’d be taking longer than normal

check that very query
my crystal ball is broken

shoot, it is?
hmm
insert into newtable values(LAST_INSERT_ID(), ‘Commodities and banks bear brunt of market slide’, ‘The FTSE 100\’s 4 per cent tumble was the biggest daily decline since March 2003, as intensified credit market fears fuelled a sell-off that hit banks and commodities hardest.’, NULL, 108, 669,
‘3c966145e5a3f10a162dd1886bdea151′, 1187306242, ‘http://business.scotsman.com/markets.cfm?id=1300082007‘, ‘Commodities_and_banks_bear_brunt_of_market_slide’, ”)
the State on that query is LOCKED

how do i reset the permissions for my root account?
i seemed to had slipped and changed them so i can’t do anything =\

Can LAST_INSERT_ID cause the lock up? I’m not the programmer for this project

reset root

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

foo, yeah, that LAST_INSERT_ID() is pretty strange there

Is it possible that that could cause the lockup? I’ll talk to the programmer about this… but, I’m not sure

foo, dunno, but give it a try

thumbs, could you perhaps point me to a resource on table indexing?

thanks

!man indexing

Sorry – I have no idea what function you’re talking about! but try http://dev.mysql.com/indexing

!man index

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

i was just looking online, but i’m not sure if there’s a comprehensive resource which explains what it means, and not just hwo to do it..
thumbs, thanks, will take a look

nah. doubt it. It’s locking up on an update as well.

do you have any locking statements ahywhere in your queries?

Doubt it, but let me confirm. I don’t see it in this processlist at least
Yes, we do. Wouldn’t I see them in show full processlist;, though, if that was causing the locking?

well locking the WHOLE table, or a ROW, could have disastrous consequences on the performance of your system
for instance
if your update takes ~3 seconds, you just delayed every other query that needed to access that table by that much time.

does visual sql creator exist for mysql, like in access db?

and if the other queries want to do locking too, your query queue just got astronomically larger.
try phpmysqladmin or something

is it really that bad to put underscores in table names?

skull–: it’s allowed

but frowned upon?

skull–: to be safe, always use backsticks when referencing a table name
skull–: it’s allowed.

i want to join 3 tables in select statement , i don’t think phpmyadmin allow to create such queries visualy, i found Visual SQL-Designer in google, maybe this is it
i heard you can put one select in another
i wonder what should i use join, select in select or union
i think i said something lame

if you have 3 tables and want to use them all in the same query, you can use joins to combine the results.
figuring out what join to perform, and on which columns, requires design skills.

thumbs, truth!
fulketerraun, selets in another is called a subquery

hey hey

hi

anyone know if there is a php function to check if a key already exists for a table?

is there a way to drop all tables at once?

that question would be better suited for ##php
no. Drop them one at a time

you can in phpmyadmin i guess

heh
yeah

using phpmyadmin is not recommended, however

you can’t truncate database?

script it.

!man grant

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

alright, thanks

thumbs why is not recommended?

because you start asking lame questions about sql syntax ?

In the beginning was the CLI…

concerning phpmyadmin

what will happen if suddenly I get lots of queries that my server can’t handle them?

it’ll queue them

will queries be slower?, crash?, something?

they will line up and take their turn.

nice

for one, it promotes lazyness, and misdesigned queries

without jokes, why phpmyadmin is not recomended?

it’s especially bad when used to import/export data

what should i do if my root@localhost permissions have been changed so it can’t do anything?

it’s known to mangle data

it’s an attempt to guess what you mean and create a result. It makes errors. And it keeps you from learning the right way to do things.

you could reinstall the user tables (rename, and so on)

anathematic stop mysql, start mysql skipping grant tables, reset password

!tell anathematic about reset root

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

i see

i tried that but it didn’t seem to help
the permissions for root@127.0.0.1 are fine but @localhost they are restricted
but it won’t let me log into mysql through 127.0.0.1 =\

anathematic, well, then reset it
are you on unix?

yeah unix
*ubuntu

anathematic, do you know how to stop the server and start it?

yeah but it doesn’t seem to be resetting permissions when i’m starting it using “–skip-grant-tables –user=root”

anathematic, it’s not automatic, you have to reset the password manually

it’s resetting the password but not the permissions
yeah i set the password again
however it’s not reseting the permissions with that command
: i need to reset the permissionf ro root@localhost to all again

hello again
how do I check if a KEY exists on a table using SQL?

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

do you mean check for an index? ’show create table tablename;’

Failed: ALTER TABLE {users} ADD KEY created (created)
because it already exists

http://pastie.caboo.se/88469 it
oops sorry, yeah chuy_max it’s not interested in giving me any permissions to do t hat

you can also query the info schema. http://dev.mysql.com/doc/refman/5.0/en/columns-table.html

so i need to check somehow if the key “created” already exists
key = index?

you’re inventing syntax. read the manual.

sorry the sql is being processed by Drupal so i guess it must do a regex and change KEY to INDEX

I thought Dru Paul was a drag queen?

how is ADD KEY not mysql syntax?

there is create index

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

for alter table, add key is there, same as add index

ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,…)

but has to use inside alter table

right, but i was told that i was inventing syntax and that i should read the manual

there is no ADD KEY without ALTER TABLE, there is CREATE INDEX for that

thanks kimseong, i had posted the following:
ALTER TABLE {users} ADD KEY created (created)
which is failing because the key already exists

the name `created` already exist or the col created already indexed ?
what is the exact error message

so i was wondering how to check if the key already exists so that i can incorporate that into my code before the ALTER TABLE is run

show create table , show index

ALTER TABLE users ADD KEY created (created)

even information_schema
so the problem is the index name, ignore the name and it will be fine
ALTER TABLE users ADD KEY (created)
the name is optional

gotcha
but won’t that create an extra index, i.e., created2?

!man adduser

Sorry – I have no idea what function you’re talking about! but try http://dev.mysql.com/adduser

!man add

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

haha~ mmm functions for adding new users?

show create table , show index are these SQL commands or mysqladmin commands?
nevermind got it

they are sql commands, but you may have trouble using their results in other queries. Look to querying the information_schema db.

not sure if my code will have access to that DB
if it’s a separate DB

there is also the issue of mysql version
http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

it looks like SHOW INDEX FROM users will return rows and i’ll just need to loop through the rows and check if Key_name ‘created’ exists

“It is possible to select INFORMATION_SCHEMA as the default database with a USE statement, but it is possible only to read the contents of tables. You cannot insert into them, update them, or delete from them.”
do the looping from your application code, I believe.
?

right, in php

there is a bias here against using app code, but I don’t share it.

information_schema doesn’t exist in my db anyways
i’m all for doing it within mysql if there’s a single query i can run, but it doesn’t appear to be such a thing

IF EXISTS (SHOW INDEX FROM users WHERE… )

some of the big guns here could do it with variables and stored procedures, I think. Not me.

thumbs YOU ROCK!

no need for a stored procedure
albeit that is bordering on TSQL code
anyway, the principle is easy
if value exists within the resultset, then execute query

hmm

or in your case, do IF NO EXISTS
NOT

yup gotcha

can show be used as subquery?

so start the query with a case?

I thought not

thats what i thought too

it might not. I would try to find a suitable SELECT query to replace it, however.

I think that is where info_schema comes in, if your version supports it.

yes, it definitely cmoes in handy at that point

SHOW INDEX FROM users WHERE Key_name = ‘created’
and if it returns a row i know it exists

!man grant

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

you could.

that’s manageable!

I concur

thanks for all your help!

de nada

http://img136.imageshack.us/img136/5113/52757166gb2.jpg how to ger row that contain: colorSetName, colorName, taste?

thanks wench

It’s been a pleasure serving you, threnody.

*get

I love that

where ?

!man join

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

read that
more specifically, you need an INNER JOIN

0
you means me
but not in thank you

de nada

what port does mysql run through by default? (how can i find out what port mine is on?)

/bin/sh: grp: command not found

3306

so im setting up mysql cluster, and apparently it hates me

I like boobs, and your mom has aids

nice one

lol

so i’ve got 9 linux computers, and i setup mysql storage nodes on 8 of them, but they all get stuck initializing at phase 1..

that’s because you’re using 8 386’s at 4mhz

haha
no
8 p4’s
well, 9 p4’s
though, they only have 256mb ram each

perfect candidates for vm servers

the resources online made it *seem* like everything would be pretty straightforward — and it was
cept, using ndb_mgm shows that the nodes connect, and are stuck at phase 1

perhaps try the mailing list

i take it nobody here has messed with it?

i’ve started using backticks since I joined this channel 2 hours ago, I edited the checkbox table though and took off the state, if there’s a value then it’s true otherwise false, is that a bad idea? (to recap: table was checkboxid, state, userid, now checkboxid, userid)
the state was BIT type and was coming out as 01 and messing with me, I didn’t see a point

silly non-working documentation
actually, its probably just something silly i missed
tnx, im out

what variable controls how long processes can sleep before being disconnected?

wait_timeout?

show variables like ‘%timeout%’;

I was just going to say thta

there are 2 type of timeout for connection

wasnt sure if it was wait_timeout or interactive_timeout

what you tell your gfriend when she’s angry and with aunt flo
sorry

I don’t think my wife will let me have a girlfriend but I will ask

don’t ask that

then you’ll need a wait_timeout

let her bring it up

when i switch from myisam to innodb.. do i need to adjust my mysqldump lines for that? or complete nothing has to be added/changed there?
i regulary backup some tables and i wonder if it will be fine with innodb still
completely*

should not need to

thank you scooby2
just making sure as i heard that innodb is kind of scary

it can be

data corruption etc.
still not sure if i should switch from myisam at all

thats not too bad
the double write is what is killer but row locking is worth it if you need it.

and is it possible to go back to myisam from innodb?

sure is

hm so nothing that i can’t change back, sounds good
row locking.. what exactly do i gain with that?
i’m mostly looking for performance boost

not sure you will get that with innodb
depending on what you are doing
it might be slower

but can it also be faster?
i’m using some specific software that utilizes mysql heavily

when you write to a table with myisam it locks the whole table, does the insert, then unlocks it. thus only one write at a time.

and i wonder if it can be optimized for innodb rather than myisam – does it make sense?
hm.
oh wait, does locking a really BIG table take time?
i mean really big one
with like 140+ columns and 2 rows
2k rows

yeah. i use innodb on our 192gb events table because it does well with lots of inserts

and that table is being updated constantly, 10s of times per second often
damn
so the locking stuff could be evil

i think we went over a billion rows yesterday

damn damn
a billion?
192GB db you mean?

yeah that db is just over a billion rows

congratulations

grows at 4-5 million per day

i could make coffee for everyone 10 times a day just to be able to touch that db lol

i need to break it up but the report people dont want me to break it up by year

i never heard of a bigger mysql db yet
he he
they are always like that lol

yahoo has much bigger i hear

so basically you recommend innodb for any database with big tables that are being frequently being written to
as that takes locking the whole table with myisam each time it happens
and it’s diff with innodb

yeah sounds like its worth a try

btw does yahoo really use mysql?
i thought that such big databases are mostly oracle
glad to hear that mysql does the job for billion rows

i know all their finance.yahoo.com is mysql

http://mysqldba.blogspot.com/2006/11/unorthodox-approach-to-database-design.html

damn nice
hm skull–… i will certainly give it a read – thank you very much
but again, i can imagine a software performing better with innodb
e.g. when there is like hm 1
err
when there is like 1000 updates needed
and when it doesn’t queue them

500gb of mysql data to replicate
damn

scooby2 he he
….so with myisam it would take 1000 locks of the WHOLE damned table

at least $250k in network gear in that diagram i’d think

and that purely depends on the software right? if it tries to update in real time instead of e.g. collecting those 1000 updates, locking once, updating etc.

which might not take long depending on size, memory on the machine, disk

nice skull–

replication is what keeps most sysadmins/dbas sane

i got fired from my networking job last week for telling the receptionist I had my resume on monster, as soon as I walked out the door she called the owner and told him and he called and fired me. Now I got my wish of time to work on my projects

ouch

nah it’s good, i was looking for a reason
I’m using my free time wisely.

going to get unemployment atleast?

oh yeah, applied before 5pm the day i was fired
have to wait a week before it starts though, might have a job before then though, had 2 interviews this week

nice

hello

Hello

i’m not able to read table content
from mysql (console)

is your monitor turned on?

i need to read what i wrote but i dont want to use this information

permissions?

¬¬
i have permissions
im conected
what mor?

charset

http://mysqldump.azundris.com/archives/60-Handling-character-sets.html

how can i read?

Knows what she’s talking about, that Azundris!

So does that wench.

i don’t found the command
to show this information

&?

anyone knows a simple mysql databases viewer, for windows?
I have my .sql db and would like to view it

load it into a mysql server

I can do that locally in my computer, no?

yes

because the .sql was saved in a server with a newer mysql than the one I would like to import the database

how different are the versions?

chaos1 mysqld[17878]: lock: 0×8c913d8: … how can I further troubleshoot this? Or, can I?

the server were I try to import it is MySQL 4.0.25
and the version of the server where it was saved was.. 4.2 I think
MySQL version 4.1.22-standard
I saved the .sql in a server with that version of mysql, 4.1.22-standar
and I would like to import it in a server with mysql 4.0

how do i set all my null fields to 0?
ohwait never mind haha

hello
need a little help! I have a server running a localhost mysql server and another server at 192.168.0.2
the problem is, whatever server I use, the connection is ultra slow
the server load is low
there are just a few processes showing when I type SHOW processlist
what else should I look for?

I am getting a strange icon when I hover the mouse over the table area in MySQL Query Browser. Icon has the word “set” and won’t let me do any of the normal functions like selecting areas of certain tables.

did you click on a SET button on another toolbar?
connection is slow, what server hostname you use to connect? hostname or ip address
try ip address and see if it is faster

kim, ip address
both are slow…
and not a specific query
most queries
I really do not know why

slow on the query or on the connection? i guess on the query

on the query

then you query not optimised of server setting not optimised
s/of/or

the problem is that this happens with any query
even with the simplest you can imagine

I don’t recall clicking on anything. How do I shut this thing off?

but in particular with INSERT and UPDATE queries

select 1;
click the pointer on that tollbar that has a lot of set, where, group by, and so on
check your show status
and looks for hints

Are you referring to the menu bar? I don’t think I know what you are talking about.

not menu bar

What are you referring to?

below the text box
looks like round round things

Which text box are you referring to?

the big one at teh top where you can enter sql

What part of the screen is this in?

below the menu bar, there is a text box, to enter sql statement

I don’t have anything below the box for entering text. I do have a round refresh button to the left of the text box. Is that what you are referring to?

linux?

hey guys, on freebsd, im trying to start the mysqld 5.1 server, and am receiving the following errors, while logged in as root: http://dpaste.com/17086/
how can i allow access to the database files?

I am running MySQL in a Windows environment.

i know linux don’t show that and not sure how to get that
windows, should have

What should windows have?

menu bar at the top
follow by a big text box

What do you want me to do with the menu bar?

then below has a toolbar with many buttons

And what do you want me to do with a big text box?

take a screenshot, post it somewhere

rycole, can you read english text?

screenshot of what. I really have no idea what kimseong is referring to……

let me see if i can find a link

the screen you’re looking at that’s causing you problems

We first need to establish what we are talking about……..

yeah, i asked how to allow access to the database files because the error is obviously telling me it cannot access them. can you read?

that would at least seem to be a place to start

rycole, ls -l, and think

Okee http://www.mysql.com/products/tools/query-browser/main_query_window.png

I don’t have any of the buttons you have provided in your png file. My version is the latest.

i refer to the round button, select from where group and so on

I don’t have those round buttson. Sorry.

try view menu and see if it is disabled

kim, I tried looking at the show data

if you have ’set’ tooltip when you move mouse over the table column, you probably had the SET button clicked

There is nothing in view. I am using Version 1.2.12

the only thing I see that could interest us is that there is a table where many queries are occurring that shows always its status as LOCKED
but that was not the table I was trying my querie on

i don’t have a running version installed, so cannot help much, but I alwasy have that on windows, but don’t see that on linux

Where do I find the SET button?

some query/connection is holding a lock
nevermind, just close and start it again

Well it went away after I closed and reopened it.

kim, would it be a problem if just queries are showing as “LOCKED”? I mean, would it affect other tables as weel
the table has 1000000 rows

there are nice tutorial http://www.mysql.com/products/tools/query-browser/ see the left bar
means someone else has locked or is using the table and this user has to wait

im not sure. i adjusted the permissions of the mysql directory, and no longer get the permission denied error, but still get the remaining errors saying it doesn’t have the ability to issue the ‘create’ command.
am i running mysqld as an incorrect user?
im logged in as root, shouldn’t that work? im new to freebsd.

show grants for current_user();

kimseong, the server isn’t running – i’m trying to get it started

ops, then what does create got to do with starting the server

i think it’s trying to create some initial database file, or something? im not sure. the output looks like this: http://dpaste.com/17087/

rycole, you have the mysql database files?
man mysql_install_db, check the existence of those files

creating the tablespace file
still have the error 13?
!perror 13

Permission denied

that is not the problem

mysql -u user -p -h site.com Why would this not return an error, just idle after I enter the password?

you start it as root, but mysqld usually start with a mysql user of group mysql

I am using BIGINT for a phone number with hyphens between the area code and the number and it is still getting truncated. Any idea what I am doing wrong/?
?

bigint is a number
hyphen is not a valid digits

ok

derekl idle for how long? anything after a few minutes?

Is there a way to put a space between the numbers of an underscore? Or will it accept it?

no
number accepts digits only

yes, i already ran mysql_install_db, i read that on mysql.com docs

then did you see the file and directory permission change ?

so will SELECT DISTINCT return ONE of a value that’s listed several times, or NONE of that value?

How about numbers and spaces between the numbers?

no spaces
space is not a digit

entendu, mysql.com/doc?

yes, i saw that output. it said it created a mysql user, but wasn’t prompted to set a password of any sorts.

no need password, make sure the datadir is owned by mysql os user
chown

Well, i mean I see what it should be doing, but I swear to god it’s axing them rather than selecting just one

compare the result with and without distinct

looks like it’s owned by root.

thats the problem, should be mysql instead of root

mysql group, as well?

yes

cool, appears to be running

really guys, am I going crazy?

This is weird. I am trying to update a field for email and telephone and it won’t return back a result set. I am using the following UPDATE south_databasefinal.vendor
SET Email = ‘mprosser@privaris.com’
WHERE Email = NULL;

is null

I have the following query:
http://pastebin.com/d3f64c8f3
I want to find users though that have a count of 0, how do I do that?

http://pastebin.ca/660674

Delixe, having?

oh…

where count = 0
lorl
also why ‘WHERE 1′ ?

having is for group
i think?

oh okay

d03boy, and group is for count

WHERE 1 is a good way to build dynamic queries

‘a way’

‘a good way’

-good

isnt WHERE more efficient than join?

hehe

+good

having doens’t work

where is not an alternative for join.

They are used for different things

EoN my query will not find users with no count
Oh I know what’s wrong…heh

but join = better practise than using old technique of where id = foreign key etc.

‘practice’

yeah that.
\o/
two words i have spelt wrong since early age are ‘practice’ and ’sentence’
no idea why. one of those dumb ass things.

is it possible to rename a database (a schema) after its created?

unpossible!

:/

actually I dont know. i would assume its possible as long as it isnt a foriegn key or something.
but I dont really know anything..

lol, ok, thanks for being honest

Would anyone like to take a crack and forming a set of Join statements on one of my databases? I can’t figure it out. I would probably have to email you the database.

or… you could just show us the schema

I can try. It is a bit complicated. Hold on and I will try pasting it into a pastebin.
Create a report that shows the Company Name, Order ID, and Total Price of all orders that have a total price higher than $10,000

what are your table naems
names

I was about to get that. Table names are categories, customercustomerdemo, customerdemographics, customers, employees, employeeterritories, order_details, orders, products, regions, shippers, suppliers, territories, usstates

do your own homework.

where does the mysql 5.1 configuration file reside?
i can’t seem to find it

You guys have a way to do a two-way replication yet?

Unknown column ‘Quanity’ in ‘field list’ Error 1054

how to reset mysql root password?

Hey guys
http://pastebin.com/d6feb2534
I have the following query but I want to find the NUMBER of rows returned
GROUP BY occurs and forms its own count
how do I find the count of rows?

is it possible to change mysql to not be case-sensitive on table names?

hello. I’m trying to query mysql via java PreparedStatement execute method. I want to insert multiple records in one statement, is this possible? I have statements seperated by semicolons but it says I have an error in the sql syntax. the syntax works if I only insert 1 row.

I am not an expert on this stuff but if you want to count the number of rows I would think you would use something like COUNT(rows) instead of COUNT(*)

Does each one of your rows equate to a tag?

jwisher, kinda hard to tell unless we see the line

I think you should use comma to seperate those statement

ya, semicolon doesnt make sense

how do i update a value of one field with a value of another within same query?

subquery

hm.. that’s something new to me, can i write one?
like query within query with if or something?

you surely can

hm..
i will explain what i’m trying to do, perhaps it fits that:

what’s returning are 3 rows with a count of how many tags a user_id has

i need to find all fields in some column that have 0 and in that case they should get value of a field in another column

UPDATE …. WHERE column1 = (SELECT whatever FROM blah)

d03boy hmm… wow

Instead of a single COUNT value of how many rows are returned.

well… I’ll paste a line
INSERT INTO `shop_orders_customer_details` (`order_id`, `attribute_name`, `attribute_value`) VALUES (‘8923849′, ‘first_name’, ‘lol very funny computer!!!’);
it is just that line repeated over but with a different values

d03boy that’s really tricky

Are your rows identified by a Primary Key?

yes

I would count your primary keys to get your rows. I did a problem similar to this and it worked.

im going to bed in 5

yeah you can insert multiple records in one line:

SELECT COUNT(ItemTag.id) AS `count` does not work….

anyone have an idea what I am doing wrong with this –http://pastebin.com/d471d2852? I am trying to get the following configuration: http://pastebin.com/d471d2852

INSERT INTO table_name(field1, field2, field3) VALUES (‘val1′, ‘val2′, ‘val3′), (‘val4′, ‘val5′, ‘val6′)

What error message are you receiving?

that’s not your whole statement is it?

no
http://pastebin.com/d6feb2534

what’s the error you’re getting? (or, “What Okee said”)

oh, cool that works, thanks!

Hi noob question here, is it reasonable to have like 30 queries per page? could something like that scale? assuming they are mostly simple select all indexed?

Anyone have an idea how I can do this –Create a report that shows the Company Name, Order ID, and Total Price of all orders that have a total price higher than $10,000 with this –http://pastebin.com/d471d2852??

no .. 30 queries per page is way too much to scale

haha

seems like a lot, but it all depends on a lot of different things

you want to get down to a handful of queries on login

you can make indexes and stuff

One of the problems that I am having is that the OrderID is showing up in more than one table. I am doing something wrong, but don’t know what.

and then maybe 1 or max 2 during normal operations

having ?

ideally none
using caching

/etc/init.d/mysql restart
* Stopping MySQL database server mysqld [ OK ]
* Starting MySQL database server mysqld [ OK ]
* Checking for corrupt, not cleanly closed and upgrade needing tables.

but it depends on just how dynamic your content is .. but usually to scale on a site where you have a lot of reads over writes

dont paste in here

i keep getting the 3rd line, is it ok?

What were you alluding to?

you want to devalidate the cache on write .. and then just have the first reader fill up the cache

depends what you’re doing. you can probably group together a lot of your results for fewer queries, but your sql skills are: fAIL

I can see getting down to 20 querys with major code rewrite

nevermind, i did not see the pastebin

really hard to cache every page is personally generated for every user and if i pregenerate the lifetime would have to be very short

Would you mind taking a look at my code and telling me what I am doing wrong –http://pastebin.com/d471d2852? I am trying to do the following: Create a report that shows the Company Name, Order ID, and Total Price of all orders that have a total price
higher than $10,000

and would only be possible really for a few pages
i mean really hard to pregenerate
that was an option I was thinking about
so whats like the rule of thumb max querys you could have per page? 2 or 3?

you should do your own homework
but i’ll have a quick look
what result are you getting?

I guess everyone else on here should also? Right ?

bit much atm okee, i can have a look later if you’re still looking.. got some work to do

I am going to bed. It is late, but would much appreciate you emailing me your suggestions to xx1@att.net Thanks.

haha

together with the bill

so whats the deal guys 20 query per page is still insane? do you know any type of docs on this? google doesnt give very much

mariorz, i think it totally depends on how complex the querys are and size of the tables

it says flickr does 25-35 per page not much else

you do whatever number of queries required to ge the work done

I understand that man I just want to know if what im trying to do is crazy

maybe you should just ask us then

i am

best is 0 query every page

it was the is having 30 querys per page insane part

nothing insane about, unless redundant

show us the queries. good chance you could group some together
but if not, its not that bad. depends on your traffic.

depends on all sorts of things

exactly

is it better to place all 30 into 1 stored proc and have 1 call from the page?

it’s an option
and probably is more efficinet. not necessarily more maintainable though

option? that is jsut moving the problem around

give me a sec to pastebin the querys, not really a pro just doing it to learn so my traffic isnt big at all just want to do it the correct way

no its not, because its one db connection/call instead of 10
30

ok so i bared it down to basically the querys on the page http://pastebin.com/d28613579

all the same selecting fbid with different where condition ?

dif tables

oh different tables cannot combine to single query, except with union
select … union select … union select … and so on
maybe union all

you mean make those 3 querys into 1?

is that what you want? actually 3 query is fine
even 30

no i want to know if that is not scalable

scalable? what do you mean
scale what?

well woudl it be feasible to have somthing liek that on high traffic site

what difference does it make?
you need the data, do you want to scrifice the output ?

i want to learn how do it right

are those $commafriends and $commanids etc results from another query?
if so, you could be using a join etc.

rest call

as far as those queries, they’re probably ok to stay seperate, as kimseong says
also, only use DISTINCT if you need to. (im not sure of your data)
if you dont need it, dont use it

no actually i dont know whats there i dont need it your right

LIMIT with no ORDER BY does not make much sense

no scratch that

Hi #Mysql’ers… I’m trying to construct a LARGE database of triples, but the insert process has been running for a week now and it’s not even halfway done, so I’m obviously doing something wrong. Anyone care to have a look?

i do on 2 querys but i could do it trough php is that better?
like array_unique

what is ‘triples’?
no no, distinct is fine… if its actually doing something

basically, a table with three columns

it depends on the data you have

if you have a where pk=constant than distinct is not required

also kimseong is right limit is usualyl only used with order

I was thinking limiting the results somehow could save resources?
because i do some other non db stuff with each reusult

think about what data you want, resources come next, you do not want to save resource to get nonusable data

good point
one last thing is it worth I look into a way of getthing those 3 querys into 1, using maybe csv or something and then having php divide the results?
my querys would go back from 40 to 20 max i suppose

I finally figured this thing out.

touugh my db design i feel would be more sloppy?

I have a cluster of 20 opteron machines inserting into the database. First I started a mysqld server on one of the machines and let the others connect to that, but when that turned out to be too slow, I set up each machine to have its own database manager, and insert into its own table, which is
on a shared network disk.

hello. i have a mysql dump from mysql 5, is there any tool that i can run on it, in order to import it on mysql 4 ?

can you redump?

will take a while since the guys sitting on it is at vacation (3 weeks) but if it’s the only option…

mind the ‘compatible’ option of mysqldump

tibyke, thanks

yw

maybe I better rephrase the problem. What would be the optimal way to populate a simple but large data table (just 3 columns) in mysql. Right now it’s about 36Gb in size, and inserts seem to have come to a crawl…
I expect in the end it’ll be about 150~200Gb

ok, seems like this should be easy, but… i have a table with 3 columns: id, date, amount. and i want the set of records that has one row per date, and the row that corresponds to that date, and has the largest id