what's the query to retrieve all keys on a table?
it's something like..
show create tbl_name;
but that's not working.

I don't know. But I would think it should be in the right click mouse area. I am using MySQL Administrator to set the field to null. MySQL Administrator has a fiew bugs and shows up as having both null and not null at the same time for the first row regardless of what is entered.

Sorry Okee it was an inside joke, *I* wrote that manual.
Administrator does not show row content.
And yes, it has a few bugs.

mhillyer…
If I want to do a replace…
is it sufficient to do
alter table tbl_name add unique table_unique (col1, col2) ;

SHOW INDEX FROM foo;

Does it matter which table is created first with mysql hosting Browser?

I want to make sure "uid" and "eid" are unique
and want that to make the replace work

You can create tables using Administrator or Query browser.
Do you want the combination to be unique or each to be unique?

I want the combination to be unique.

How do you create tables with MySQL Query Browser?

But the individuals can repeat?

alter_table tbl_name add unique unique_key (uid,eid)
The individual can repeat.
Is my query correct?

Then yes, use a combined index.
Like that.

s/alter_table/alter table/

How do you set the default to allow a blank field in MySQL Query Browser?

okee Right click on the database and choose create table.

yeah.

Okee read http://dev.mysql.com/doc/query-browser/en/gui-table-editor.html
Ok, work tomorrow, need sleep. Have a good night/day.

Should every table have a primary key?

Every row in every table should have a primary key.

Is there a way to change the order of tables as they appear in MySQL Query Browser? I think I would like my tables in alphabetical order. Or does this affect the Schema?

hi all
is an option so i can override the mysql default-storage-engine in mysql database database.sql ??

edit the .sql file and change the create table statements

cat file.sql | sed s/

ToBee is a big file ….and it does not have the ENGINE= options

what regex?

so i can change that one only
after PRIMARY KEY …. there is no ENGINE= i must add them, chadmaynard is not so simple with sed, but it is possible

it isn't simple????
i am very simple

ok then tell me how to add a line after a specific line match

its true

insulting me on my birthday, what a bastard

what regex?

oooh. happy birthday!
chadmaynard++

chadmaynard–

chadmaynard started a regex and didn't finish it

this is true. it was unterminated

so after PRIMARY KEY (id) ) ..will be PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

hmmm. damn his eyes, then.

you have PRIMARY KEY (id) in every table?

chadmaynard, yes in every CREATE TABLE statement

well then
you are quite creative with field names aren't you?

are there line breaks in there?

well is not my database hosting that way was cretead
i dont have admin mysql rights to change the default storage

you could assume that ") ;\n" is probably always the end of a create statemtn
if you don't have ENGINE = and stuff

yes you are right

so replace that with ) ENGINE = ……… ;

is there an instance when count(*) should return more than row?

ENGINE = BLACKHOLE;

only if its in a group by statement

in one?

part of one

chadmaynard, ok thanks

or in conjunction with one

that's the only instance it should?
let me check my view for one

that's true for any aggregate

hrm, but not an order by?

can anyone help me get mysql server installed on Ubuntu dapper? I've started installing it, but I think that it might have already been installed and I didn't realise it. Now, I'm worried that I might have a mixed version. How do I correct the problem?

ok I don't think I have an aggregate in my view, but when I select count(*) from it, I get a crapload of rows in count() which are all equal to the same value

erm… I think we're going to need to see the actual sql hosting on this one

should I just paste the view in here?

and the create view

paste it up on pastebin.ca along with a sample of the results

ok hang on

DaTa-MaN: did you use apt to install it?

No, I downloaded the .deb files myself, and use dpgk

are you going to try to push gentoo/emerge on him?

I wasn't going to but now that you mention it…

Please don't. I have no intention of switching distros. I do however…need a good bit of help

http://pastebin.ca/656561

that'll be 0.76 cents a minute

is that expected behavior?

So first, I need to verify that I don't have two versions of mysql running at the same time.
Then, I need to know how to correctly install it.

which version, seems like a bug

well I'm not as familiar with apt/dpkg… I usually just use synaptic on my ubuntu laptop

14.12 distrib 5.0.45 darwin/i686

I tried that. But the the server containing the mysql-srver-5.0 files didn't work

I just grabbed it and compiled it

compiled yourself?

yup

any error or warning

I don't trust distro guys to handle database updgrades right
uhm
hang on
I'll recompile and let you know
I didn't look for them before

can you get a precompiled somewhere?

that would be a weird build time error to cause that ^

What site did you download your packages from with synaptic?

Yo! Anyone up for a 4.0 question?

can I just grab the mysqld binary and fire it up from /tmp or something?

I'm trying to do –master-data with –single-transaction on 4.0 and it's not behaving as the manual says (holding read locks)
is this normal, or am I lead to believe that behavior was only functional in 4.1?

maybe need more than just the mysqld binary

DaTa-MaN: none. I just check the "install" checkbox and let it do its thing. Pulling from the ubuntu repos

uuuuuggggggghhhhhhhh
I don't want to replace my existing instalation with a precompiled one

that is for innodb only

the database is 100% innodb

can I get away with out doing that?

try to rename and replace the mysqld and test

unfortunatly, that is the first thing that I tired. it failed. It's says "404 file not found"

I do this on 4.1 and 5.0 without consequence

how about I give you a sql file that can reproduce it locally, and you try it somewhere?

i don't have 5.0.45

ok I'm downloading the official mysql prebuilt binaries

DaTa-MaN: weird. I'm downloading them right now. But as for your multiple installs… I guess I would do a full removal in synaptic and a dpkg –remove on the .deb file and start from scratch

Can you look into synaptic menus, or something, and tell me what site they are comming from? That would solve all of my problems.

DaTa-MaN: looks like maybe http://us.archive.ubuntu.com/ubuntu/pool/main/m/

Sweet… thanks. I'll try it

have you synced up your apt sources recently? maybe the packages on your machine are old and pointing to obsolete files

No, I haven't. I don't really know what that means.

you can do it from within synaptic too. The little "Reload" button on the left
it just downloads the most recent list of available packages from the repositories
although usually ubuntu tries to do that automatically every few days unless you turn it off

yes there are errors and warnings on compile (quite a few) but the problem remains in the official builds
I replaced my bin/ with the one from the official sources, then linked libexec to bin/
and started mysqld with safe_mysqld
s/official sources/official builds/
ha I noticed!
haha nice

can you set the default for a field to be now()?

is there an open source tool to monitor all my mysql servers?

there are quite a few, but no rules specific to mysql provided, nagios, cacti

well im looking for something that would tell me the qps, current queries etc..
mysql advisor does this
but its a non free app

for 1 server, mysql administrator graphs

got 1 master
4 slaves

run 4 copies

hehe

is innoDB availible on every standard hosting?

was kinda tired of doing that
yeah
most hosting providers use centos

how can you select current datetime?

pay few k a year and get a nice tool from mysql ab

thanks wnorrix

yeah planning on buying that

the latest still in beta can even monitor the replication

but wanted to see if i can get a "free" alternative before "non-free"

there seems to be an alternative monyog, but may not be free

oh
duh

A child of five could understand this! Fetch me a child of five

now()

still there

how long does a query stay in query cache?

until the table is modified or purge when out of space

Hello, I have a very trivial problem, but I can't figure it out. In my DB, there are ip addresses converted to long using ip2long(), now I've founded MySQL functions INET_ATON() and INET_NTOA(). How can I convert usigned long to signed long without data loss? I need something like
CAST(ip_address AS UNSIGNED)… Thanks for your suggestions

you will need unsigned, signed is not long enough
assuming long datatype is 32bits

kimseong, right, so I'm going to use unsigned INT(8) for ip address host addresses converted with INET_ATON()

i thought it should be unsigned
int(8) does not mean 8 bytes, it is still 4 to mysql
so unsigned is correct
what is the original datatype?
bigint?

kimseong, signed INT(10)

note that int(10) still 4 bytes, mean your data could have lost

127.x.x.x

128.0.0.0

kimseong, btw. that's true, thanks, but the problem is still the same. I can't figure out how to convert from signed INT(10) to unsigned INT(8). IP address can have max. 4 bytes, because 0.255 / 0.255 / 0.255 / 0.255

note that the 10 and 8 makes no special different
you use what function previously?

kimseong, I've used PHP function ip2long() but now I'd like to convert the data using mysql

is there another long2ip() ?

kimseong, is MySQL able to convert signed INT to unsigned INT without truncating of the unsigned part of value?

how not to truncate?
keep it as binary ?

kimseong, no, just convert. For example when I got signed 16bit integer, it's -32768..32767 and when I'll convert it, it will be 0..65535 (without data loss), so how to convert in mysql?

0-0 1-1 10-10 ?

what

kimseong, What do you mean?

i mean if you have 1, if should be converted to 1 right?
if you have -1 what does this convert to?
since the new range has no -1

32 768 dec)

kimseong, You aren't a programmer, not?

r4f431 i am , and singed int is stored as 2's complement, you know this?
-1 is stored as ffffffff
in hexadecimal
not 10000000 00000000

kianm, I'm not interested in some specific storage engine, man…

so in 2 bytes, -1 is 11111111 11111111
that is not storage engine, but integer negative is 2's complement, to simplify arithmetic
try this, select hex(-1), hex(-2);

kianm, you know what? I'll simply use php web hosting function long2ip() and the output will be converted with MySQL's INET_ATON(). Maybe it's not the best solution, but I'm a slacker (with Slackware), you know THX anyway

hi, if i have a table where only selects are done, can this table then be opened by 2 different mysql processes? imagine the table being on a shared storage

myisam yes, innodb no
better set the file as read only too

kimseong, thank you
kimseong, will this have big impacts on performance if the table is not on a local disk?

don't know, but nfs has problem with mysql
if read only, why not duplicate a copy

kimseong, well the data comes into the db somehow
so through replication

and that is going to cause trouble
since it is not purely read only now

well readonly for some host
only one writes to it, and all the other read from it?

those host may read corrupted data when a write is going on
there is this thing call external locking, enable it and it may work

cjk: if only one writes, then lock the tables on write and write the data. then unlock them again. replicate on change.

kimseong, btw. I need to convert -1 to 32768 and so on (-2 to 32769)… That's conversion from unsigned to singed and it's AFAIK correct

make sure the writes only happen to the "master" server

hello everyone! I have a problem right now with my database.. since last week I always get this error from the mysqld.log "[ERROR] /usr/sbin/mysqld: Can't open file: 'cron.MYI' (errno: 145)" what seems to be the problem with my database?
thanks in advance

thank you guys

hi!
what is the difference from find_in_set and "in" ?

do you mean -2 to 32767

it is a little hard to find the term "in" in google

kianm, no, I don't, 32767 is still the first byte of word

-1 to 32768 and -2 to 32769 ? this does not make sense to me
-2 is always 1 unit smaller than -1

kimseong, and -3 to 32770, -4 to 32771, …

oh, let me see again
you can just remove the sign and + 32768 ?
you can just remove the sign and + 32767 ?
if(value0,abs(value) + 32767,value)

kianm, no, I can just separate word into 2 bytes and remove sign
kianm, I'll try your function

wait
you ahve 4 bytes now right? not 2 bytes
do you have a long2ip() in php?

kianm, The reality is I have 4 bytes, but the method remains the same // yes

try long2ip(-1) and see what does it give you
try long2ip(-2) and long2ip(1)

kianm, long2ip uses unsigned long

and you said you have signed now?

kimseong, oh, excuse me, I've confused it

!php lon2ip

php.ini variable name= default= http://php.net/

MySQL is a bit lame, without ability to convert unsigned int to signed int, if you'll one day see the assembler, than you could remember how it works for real… Bye bye

it is not cannot, but you need to define your requirement

i need a little help understanding my own DB lol, i have looots of tables now and im trying to make it as "normal" as i can, atm i have tbl_shipments which has shipment ID, description, and lots of other stuff, i wanna normalize description because in the spreadsheet the description has many
lines, so i THINk i need a 1 to many, but im not sure how i make my desctiption table
anyone?

hello everyone! I have a problem right now with my database.. since last week I always get this error from the mysqld.log "[ERROR] /usr/sbin/mysqld: Can't open file: 'cron.MYI' (errno: 145)" what seems to be the problem with my database?

you could just have newlines in the description.

!normalization

Blush, an unexpected wench error, manual section !normalization not found

normalization

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

!perror 145

Table was marked as crashed and should be repaired

im sorry to ask this… but how can i repair the damage table? any howtos?

!man repair table

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

many thanks.. sorry again..

morgen

guten morgen haptiK

wie ghets the_wench :p
botsnack

ooooh let me bend over and lift my skirt for that

try a botsmack then

ah good. glad to know the bot was written by an adolescent.

I think Im somewhat older

lol, not if "ooooh let me bend over and lift my skirt for that" is your work, you're not.
lol, not if "ooooh let me bend over and lift my skirt for that" is your work, you're not.

are there any DB programs that will automaticly generate ERD's?

archivist did you see my comment yesterday regarding the server time on the hashmysql.org server… im not sure if you did or if it matters or if you care

Giddion55 MS sql host

I replied

i think i had to run to a meeting in the afternoon i missed your response sorry.
hence why i am asking

Therion runs that

ok

any mysql programs?

there's something alpha, yes

somebody said a few days ago the latest phpmyadmin has something
and one day …
erd

comments here http://www.archivist.info/search/index.php/Erd and beta test here http://www.archivist.info/wench/erd.php

ive installed wamp
and observed that theres a database mysql, with db and users table… how can i have a wizard like on cpanel, to create users and associate it?
instead of inserting rows

!man create user

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

gui

GUI tools can be found at http://dev.mysql.com/downloads/gui-tools/5.0.html phpMyAdmin at http://www.phpmyadmin.net/ and even navicat or "http://www.webyog.com/en/">http://www.webyog.com/en/

ems cant create users?

never used before, don;t know, even from that list, never used most of them

hm
what u use
because im running local

mysql cli

u create at hand?

or mysql administrator

ok

is storing files in a database very unefficient?

images

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

not a good idea

the external caches point doesn't hold though, as that's up to the script/program feeding the file to send the correct headers
but i see my mysql query cache being filled with crap
so i'll put it in files
i'll just name them after the ID :p

Bheam, if you have a lot of them, note that some filingsystems start performing badly with 10000 files in one directory, so you may want to distribute them in a subdirectory hierarchy

right.. what's the general limit on a unix filesys?
recommended

store a copy on the filesystem for general purpose with a reference to it in the database, but i also store the origonal in the database for redudancy, i do not use this file unless what im looking for doesnt exist or i need to create different versions (sizes, images for example)

not gonna keep it in db at all
there's no reason it shouldn't exist

ok
well if your filesystem corrupts or a directory is erased accidentally dont come crying to us because your data is lost!
hehe

actually, all (linux) filesystems handle more than 10000 files in a single directory nicely but ext2 in default mode

did he he say he was using XFS?

(but even backward ext2/ext3 can handle this situation when you enable dir_index)

10,000 files is fine - 10,000 directories less so

why not just hash the filename and separate them into ~100 different directories

because that is so 1970ies?

hi i dont know if im doing right dont know much about mysql i have a block on my site that pull member name and date joined this member have a personal photo that is in another table how i can call it together example in the table Photo,name,joined this is the code "http://www.pastebin.ca/656647">http://www.pastebin.ca/656647

why not leverage the power of b-tree data structures ?
which you automatically do by using any contemporary filesystem.

gar punctuation, have ya heard of it ?

Isotopp, join ##php and ask in there instead

wrong nick

my english is not really good

oh sorry, gar join ##php
not Isotopp

thats a mysql query or not i was thinking this was the good channel

oh sorry, I just saw PHP and stopped

hehe
otherwise i posted in the php channel to

Hi, I have 2 tables, events and events_per_news_item, I want a list of all events, with an extra field that tells me if they are associated with a particular news item
I've tried http://rafb.net/p/kuPtvd82.html
but it only returns the rows with a match to that id, and I want all rows from events, with null for the extra field if there is no match in the associative table

so HarryR what you think about that..
HarryR i did what you told me but it say i have a error on line 2 can you quick check http://www.pastebin.ca/656661

m.pp_thumb_photo - you're selecting the wrong column
need to use pp.pp_thumb_photo instead as it's from the ibf_profile_portal table

ah
Thanks You V Much

oh damnit, I've done query optimization on the test database, but on the real database it craps out for 5 minutes doing a temporary table sort
uoh damnit, I've done query optimization on the test database, but on the real database it craps out for 5 minutes doing a temporary table sort/u

Hello all I had a major hardware failure on a server and had to recover mysql database with livecd which is fine however in my has to set it back up again I ended up setting a password for debian-sys-maint @ localhost I have removed it via phpmyadmin however when I restar tmy sql it complains
about using password yes
has/haste

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

thanks
dc2447, hmm still getting this error can access with root and my web mysql user "/usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: Y

so you can connect as root but not debian-sys-maint?

dc2447 yes I can connect as root, and I can connect as debian-sys-maint with out password its just when I restart mysql /etc/init.d/mysql restart the error occur

your earlier message says with password = yes for debian-sys-maint - I'm afraid I don't know enough about bonkers debian packaging to take a view why debian-sys-maint needs to access mysql on boot . . . - I guess you can start mysql_safe& without a problem and start the server
manually

dc2447 thats fair enough I was more concerned that it might affect updating system etc and I have changed the password to no via mysql commandline and also via phpmyadmin also flushed privillages
dc2447, I basically rebuilt a new system and then copied over the old recovered files from the old server to the new replacing all files in /var/lib/mysql with the old server ones, I dont know if this has a bearing on my issue. i am going to build another server and copy files over and try agian
but this time I will not set a password on the debian-sys-maint user thru phpmyadmin which is what i feel caused the issue to start with
dc2447, the site is working for now and it does not appear to have lost functionality thanks for your help

NP

bye all

hi
Is there a way to use to use names given by AS in the WHERE-clause?
SELECT zip AS foo FROM bar WHERE foo='12345';

have you tried it?

es
yes

and what happened

"Unknown column 'foo' in 'where clause'"

well there you go then

the problem is that my SELECT does some Stringoperations and I dont want to put all that again in my WHERE-Clause

ellion what you are trying to do?

get your where right
think about index usage

I have to work with an existing database. In this case my query looks like this:
SELECT SUBSTRING(zip, INSTR(zip, '-') + 1) AS zip, LOWER(LEFT(zip, 1)) AS country FROM dealers WHERE country="d" LIMIT 10;

ok

This is just a starting point. There will be more string-blah and I want avoid putting that in my WHERE-clause

ellion like zip 'd%'
ellion where zip like 'd%'
allows zip index to be used

that would include any country-prefix starting with D … so DE or DA (whatever those might be) would be included too

yes

ellis what you actually like to retrive
ie what is your conditiond
*conditions

can i use alter table to set the primay key?

eusto, yes

i can't find an example

or add other indexes

well yeah in this case, the query will change. I just started and that was just for testing purposes… so there is no possibility to use SELECT zip AS foo FROM b WHERE foo= …?

no thats to slow as it would need to be a having clause

ellion,use sub query in the where clause

okay, thanks!

its more important to think about index usage than repeating some code

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

thank you

np
alter table [table name] add unique ([column name]);

Hello, is mysql5 server faster than mysql4 ?

if you run mysql4 on a quad xeon with 6 gigs of ram and mysql5 on a 486 then no
:p

5 has more in it, could be slower, 5 has better optimisation, could be faster

hi all
how can i clear id . i meam make it start from 1 as in newly created table ?

thanks, I meant on the same configuration

Hi people!
I have a problem in SQL with the MySQL 5.0 DB. Can somebody help me?
DEFAULT_TIMESTAMP). If I update only one column with the default value MySQL updates all other columns with the default value too. Is this normal?
http://bugs.mysql.com/bug.php?id=30369

ive had my headphones on since i got in this morning and i just realized i havent actually put any music on… ;(
thats like 4 hrs ago
rofl

hi
how can I preven Kanj characters from being entered in my database?

how about chinese or korean chars ?

weigon, yes
what happen if someone tries to insert that characters in my database

if the DB is utf8, they will just store them

and this is the problem, mine is in utf8
and this is what I would like to avoid
japanese and english

you have to filter unicode sequences before they hit the DB
if you have access to the pcre, it is unicode capable, just write a filter-regex

yes, I am doing a javascript function

no, not javascript, in the application
not in the browser

but I am able to check the box's text before putting these data into the database it will be better if I allow the user write them and make a server-side check, isn't?

hello

no point adding the extra overhead to the client, put it in the app

i have some brain eclipse today guys i have a simple question
when i have a table with userId | groupId
how to select all userId that do not belong to lets say groupId=6

hi guys, i have a question maybe someone can help me
when i start my server with Xammp (test server) my unicode words (hebrew and arabic) look fine…but when i start the page on my main server it looks all weird

_Ergo_, where groupId!=6

archivist no, because user can be in groups , 1,2,3,4,5,6
so he will still be listed
ive dont that before but i just dont remember how i did that

groupid6
_Ergo_,

hey

methinks _Ergo_ should fix his question
or try it

who are u

archivist, ur rite

there may be multiple rows for lets say user 1 , and various groups so things like or ! = groupId wont work, the user still will be present in the listing

how old r u???

u want list of all user but 1 user may belong to any number of groups
rite?

tell me how old r u???
bye babe

the solution is sonething like this
1

shut up i noe

select u.* from users u left outer join your_table t on (u.id = t.user_id and t.group_id = 17) where t.group_id is null;

r u a real person
bye bye
i need to go

select distinct(user) from table where group_id6;

hello

hi

it will still list a user because engine will find that he belongs to other grous that are not id=6
ill post a solution in few moments
ill just have to test it

tell me r u using one table or what

I wonder how mysql compares to MS Access or Oracle?

paste the table structure

not that MS Access compares to Oracle

_Ergo_, go try you are not understanding what != means etc

lol, all queries you posted would return the rows with user id that dont belong to group 6
this is incorrect

thats what you asked for

nope

then

when you have pairs 1|1 , 1|2 , 1|3 , 1|6

_Ergo_ how to select all userId that do not belong to lets say groupId=6
as I said restate your question

elect distinct(user) from table where group_id6 - will still list user 1 , because even when he does not belong to 6 it belongs to other groups that satisfy the where condition
just wait ill post a solutoin that does what i need ok ? maybe i asked wrong question , my english is far from perfect

_ergo_, if u ask wrong question then u get wrong answer

no problem guys the answers were ok, they jsut wont work when you have user with multiple groups, thanks
no problem guys the answers were ok, they jsut wont work when you have user with multiple groups, thanks