hi there!
Can I use a "if" in a trigger?

Can anyone give me an idea of where to look to figure out why a query with a subquery is something like 3x as fast as the same query rewritten with joins? Also, the one with joins says "Using temporary; Using filesort" while the subquery does not.

how large is sha?
for a char?

is it at all possible.
to select all distinct words from a column in a table
and store them in a php array lol
the mysql part i have no clue about how to do though

SELECT DISTINCT words FROM table ?

to?

nah
theres a column in table called TITLE
TiTLE

ah. no idea

godam

grrr

how do people do AUTO complete search boxes then
without extracting all the words from the database

I cant use MATH-AGAINST querys in Innodb ?

Maybe with these? http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

I shouldn't use MATH-AGAINST querys in Innodb ?

innodb does not support full text indexes
only myisam does ..
and without a full text index, you cant do match against

can someone help me out with http://pastebin.ca/645006 please? I created a db, and gave the user acces to it, but he get's access denied..

in Innodb exist something similar than match-against ?

the only thing you have is LIKE
however what you can do is use a trigger to replicate all data from your innodb table to a myisam table
and then you simple join the myisam table to do the match against

but.. Match Against work different than Like… dont exist the score -.-"

can I create 1 trigger for both "before insert" and "before update" ?

did you fix your problem? I see what it is
you typed \u snort; not \u snort

Ah, hehe, oops

hey

my bad

could mysql cause a server crash?

hi
anybody awake here?

yes

cool, do you also happen to know a bit of postgreSQL?

not as such

i am trying to find out, what would be the equivalent of this command under mysqladmin, createdb -U postgres81 -E UTF8 connector_development
specifically the -E UTF8

that statement translates to two mysql commands: create database, and grant permissions query

ut the -E UTF8 ? i mean, i understand MySQL is Latin1 by default…
wdear god, Internationaliztion suxors

create database connector_development character set utf8; grant all privileges on connector_development.* to 'user'@'localhost' identified by 'password';

what is the diference betwen Latin1 and UTF 8?
ok, tomize, i went to /usr/local/mysql/shared/mysql/charset/ and i have no utf8 charset in there

latin1 and UTF8 are different text encodings, latin1 is older and contains only some european characters, UTF8 is supposed to be able to represent all characters of the world

ok, this is wierd
i'm using the same query in mod_python and mysql client
in mysql hosting client i get a result

show character set; sure, it's not on that list?

with mod_python it hangs
and i get no result

how do issue taht command from within MySQL?

type "show character set;"

what do you know, is there
if i needed to invoke that command from the shell using mysqladmin, how would that command look?

how do I start mysqld from the cli in mac os x?

do you mean mysql (not mysqladmin)?

mysqladmin -u root create connector_development
but i am missing the charset part

http://pastebin.ca/645330 is the interactive version

archivist you around mate?
ah nevermind
love you

to run commands from the command line, use -e, like mysql -u root -p -e "use mydb; show tables;"

thanks

hello
is there a way to cancel a delete with a trigger ? like throwing an exception, for example

how can I start mysqld from the cli in mac os x, and also make it go into the bg

how can i find out what the charset is for a database host if it is already created?

how can I start mysqld from the cli in mac os x, and also make it go into the bg

madprog *cancel* a transaction ? erm.. no, make sure it doesn't happen in the first place, with sensible constraints

i would like to check that a table still have at least a line

you want to prevent a delete from removing the last unique ?

Is there a way to insert a row and retrieve the autonumber ID of the just inserted row in 1 sql host statement?

erm.. unique, but in a non-unique way, then

yes, last_insert_id
in fact i have a key on three columns
and i would like to keep one line for one of the three columns

and only the three are unique ? then set a constraint on that column as a foreign key and enforce it as long as it still exists in its own table
then the delete will fail when the value in that column is the last one left
it does require a second table, of course
but if that is your requirement, you should strongly consider the possibility that your original design can be optimized
to not need the three columns as unique ID

yes, maybe…

it's hard to say without seeing actual data

would you like me to explain in detail ?

better show create tables

http://pastebin.com/d3ededd17

that didnt' help at all what do these words *mean* ?

heh ^^

you shoulda said you're French

excuse me ^^
this is a table for the price of shipping
there are three zones, one for France, one for Europe, and one for the rest of the world
and some insurances for each zone
and weight quotas
for each tuple (zone, insurance, weight), there is a price
and i would like to keep at least one line per zone
but your idea to "set a constraint on that column as a foreign key and enforce it as long as it still exists in its own table" looks like be what i'm looking for
but i don't know how to do it

why does the following NOT return an error when foo@bar.net does not exist in my DB ? mysql -u $DB_USER –password=$DB_PWD -D $DB_NAME –exec="DELETE from users where email='foo@bar.net' LIMIT 1"

!m madprog foreign key

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

madprog well, for one, you're sorely missing some essential normalisation - separate out the actual values for Zone and Insurance, and put zone insurances to a crosstable; this yields only one value for an insurance in a specific zone, without having to include all that redundant data in
every row; now do the same for the weight quota, and you end up witha finite set which can only be unique for each entry
since it *is* only one entry
you're trying to copy your paper data into tables
you don't do that
!normalisation

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

!normalization

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

sheesh girl

hmm, yes, you're right

I know

i'm stupid because i had a course on normalization ^^

If you pre-set the combinations of zone and insurances, there is no way you could falsify the data, which is what rdbmsen are for
if ever you think you need to de-normalize aghain, this will depend wholly on th eamount of data, and the implied performance benefits of doing so
but in general, normalise to 3NF

yes, i normalized the database, but i had to add this table later, and did not think about it any more

zones, insurances, and quotas, the latter two being crosstable references to zones

and now i had problems to use it

that's quite unlikely…
the zones will have popped up before now
so you did not normalise those at all

the zones are in their own table

I also hope you mean you normalise the *data set* - normalising a database would be mending after it broke…
no, they're not - you include a full varchar column for them in the table

ah
yes
so you tell me to use auto_increment keys everywhere
(or quite)

did I tell you that ?
sigh.. what's the use of making the zones into a table and then not using them ?
bsigh.. what's the use of making the zones into a table and then not using them ?/b

hello!
I'm having a small headache here with my table, probably because my knowledge of SQL is far too little to achieve this goald
id, subject_id, user_id, teach(bool), learn(bool)
teach means that users can teach a certain subject, learn means he wants to learn it
i'd like to fetch users who can exchange their knowledge
so pick best matches with as much learn-teach rows as possible
what is the best hosting way to start with this task/?

LoPMX, you are going to have to do a self join

well yeah i suppose
but the worst thing is to actually calculate the best matches

SELECT * FROM subjects_users AS teachers INNER JOIN subjects_users AS students ON teachers.subject_id=students.subject_id AND teachers.teach = 1 AND students.learn = 1

yup

then if you want to count them, you add a GROUP BY and the COUNT and then you can ORDER BY the COUNT(*) to find the best
SELECT teachers.id, students.id, COUNT(*) AS matches FROM subjects_users AS teachers INNER JOIN subjects_users AS students ON teachers.subject_id=students.subject_id AND teachers.teach = 1 AND students.learn = 1 GROUP BY teachers.id, students.id ORDER BY matches DESC LIMIT 10;
that'll be the 10 best matches

ok, gonna try that out
ok, and
em
what if i'd like to find matches for particular user?

as a teacher or student?

that should not be specified, just find users which can exchange most subjects with

GROUP BY teachers.id, students.id ORDER BY matches DESC LIMIT 10;

how to reset root-pw?

!man reset root

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

!man reset root password

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

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

i copied the value of mysql.user.password from host=localhost to hostname.network, restarted mysql-server and get access denied :s
thanks

uh ;]

mysqladmin -u root -h paddy password 'mynewpw' (where paddy is my hostname.)

paddor, why would copying the value help if you don't know it?
blank it out to reset it to nothing
UPDATE mysql.user SET password ='' WHERE user='root';
restart, and then set the new one

the passwort of localhost i changed before. then i want to change the pw for network-user (paddy.home). was that wrong?

ok
but

i cannot execute any sql-queries. i can't login, even not with localhost.

This query gets two user_ids, i'd like to get only the best users to match specified user
so what table should i use then?
currently i have like:
SELECT teachers.user_id, students.user_id, COUNT(*) AS matches FROM subject_users AS teachers INNER JOIN subject_users AS students ON teachers.subject_id = students.subject_id AND ((teachers.teach = 1 AND students.learn = 1) OR (teachers.learn = 1 AND students.teach = 1)) AND (teachers.user_id =
1 OR students.user_id = 1) GROUP BY teachers.user_id, students.user_id ORDER BY matches LIMIT 10

paddor, okay, then follow the steps from the page I linked

when INSERTing a date, do you have to put the surround it in #'s ?

LoPMX, what did you do to my ON clause?!
SELECT teachers.user_id, students.user_id, COUNT(*) AS matches FROM subject_users AS teachers INNER JOIN subject_users AS students ON teachers.subject_id = students.subject_id AND (teachers.teach = 1 AND students.learn = 1) WHERE (teachers.user_id = 1 OR students.user_id = 1) GROUP BY
teachers.user_id, students.user_id ORDER BY matches LIMIT 10
oh, you mean you want just a single user_id column returned?

gah, does anybody have a php based gettext interface or so?

yup

SELECT IF(teachers.user_id = 1, students.user_id, teachers.user_id) as uid, COUNT(*) AS matches FROM subject_users AS teachers INNER JOIN subject_users AS students ON teachers.subject_id = students.subject_id AND (teachers.teach = 1 AND students.learn = 1) WHERE (teachers.user_id = 1 OR
students.user_id = 1) GROUP BY uid ORDER BY matches LIMIT 10

OR students.user_id = id) GROUP BY uid ORDER BY matches LIMIT 10

btw, this would have been much easier with all separate teach/learn tables

can't I specify these "–skip-grant-tables –user=root" options in my.cnf? (I just wonder.. i don't want to let them in there)

paddor, yes, you can
paddor, i normally do it just for a single startup by putting them in there

hello
mysql has a variety of character set settings. is there a way to make them all utf8 without having to recompile mysql?
database, server and system are utf8, but the rest are latin1

Do you know the best ways for moving categories in nested tree? It's so difficult.

init-command="SET NAMES utf8;"
that will set it for all users without the SUPER privilege

yeah. i do that now
everything works OK, i'm just wondering mostly
what those settings are for (results, for example)

it says what character set your client will send queries in and what character set it wants them returned in

ok, how is that different from the 'client' setting?

corp, check out http://www.mysql.org/doc/refman/4.1/en/charset-connection.html
corp, it lists what each of those variables are for

ok, thanks

strange thing is that i get metches = 1 all the time

gotta rtfm now and then

character_set_client and character_set_connection are kinda odd together imo

Do you know why matches = 1?
it should be more

you are doing something wrong?

no i just pasted ur code ;]

then you are telling me wrong, hehe

what is it counting here?
it should count amount of subjects in common

Hi guys.

or
just reverse the order ;]

Will I see a performance difference between ENUM('alargewordhere', 'alargerwordhere') and ENUM(0,1)?

and how? doesn't work if i just strip off the leading "–"

Oval, no, i would use the largewords personally, that is the entire point of ENUM
paddor, it should, what error are you getting?

Hmm
Anybody have any idea why this won't work? UPDATE TABLE lols SET score = score + 1 WHERE id = 1

LoPMX, you removed my DESC from my original query

/etc/conf.d/mysql: line 81: skip-grant-tables: command not found

devlin`, remove the TABLE word

ah
ty

and user=root seems to work not, too. the server runs under mysql

paddor, that is gotten when you add skip-grant-tables under the [mysqld] section?

still nothing :s

Thans a bunch.
*thanks

no, i putted it in /etc/conf.d/mysql (gentoo)

false alarm, fixed it.

paddor, no idea how gentoo does it, but it looks like it is trying to execute it or something

ok.. so i put it in my.cnf..

is there such thing as delta mysqldump?
i mean i make a backup every 10 mins just to store files that differ in several bytes, sometimes nothing is changed even
would it be possible to only store the diff from the last backup?

how can I remove a whitespace or eol from a text field in mysql?

robboplus, you can create incremental backups using the binary logs

ok, it worked. i changed the pw. then i removed the 2 lines from my.cnf and now i can't start the mysqlserver again! it fails! :s

WildPikachu, RTRIM or TRIM should work

ooooo
sorry, i can't get to the manual from here … is there a mirror?

oh ok, i got it. the new created logfiles were root:root. i changed to mysql:mysql, now it works.
thanks!

my favorite mirror is http://mysql.he.net/

i use that one too

but i have a few questions.. does it make a difference if i define bind-address to 127.0.0.1 or to localhost?

HarrisonF hm..
HarrisonF is bin log the only option in this case?

WildPikachu, a list is available at http://www.greatlinux.com/mysql/downloads/mirrors.html

can i define more than one bind-address?

he works 100%
thanks

robboplus, pretty much, you could kinda do a diff on the backups from mysqldump, and then use patch to apply it to the original mysqldump, but that seems pretty pointless

HarrisonF hm.. would it be ok to use diff/patch on non-txt files like .sql?

paddor, no, only 1
robboplus, mysqldump makes text files

HarrisonF in fact..
and i would love it more than binlog which i disabled…

robboplus, but you aren't gaining anything at all from doing that, since you need to generate the entire dump each time
the binary log is the way to go

HarrisonF, trim/rtrim doesn't seem to pickup the \n
or \r\n

HarrisonF i would have to do the dump but i could then diff it and remove it after

and what if i want my mysql-server to be reachable from other hosts AND have local applications running, which need the mysql-server?

HarrisonF and space is all i care about, dumping for nothing is still fine
HarrisonF i do a 5MB dump every few mins and i store it JUST to have those several bytes that differ stored…
it takes lots of space during day

paddor, localhost is always bound in addition to your other ones, it's all good
robboplus, why do that rather than use the binary log?

oh, ok..

substring_index(Domain,'\n',1) worked

and.. do i have to specify "machine" or "machine.home" as host in the user-table?

HarrisonF that's because binarylog creates tons of traffic on my hdd
several gigs a day
and besides that i found it totally useless
unless i could narrow it down to only binlog certain table changes

Around string you have to put 'string' What about datetime?

Is there a preferred method of storing images n databases?

how would it generate gigs of traffic if you only have a few bytes change ever few minutes?
TheGoldDIggah, yes

Or is that just an inherently bad idea?

thank you for your advice
bye

see ya

yes what?

TheGoldDIggah, yes you need to put quotes around a datetime

1',0,0,7,6) a row is inserted, but the CreationDate gets the value : 0000-00-00
what could be the cause?

I'm installing a PHP application which requires the execution of a script which creates various tables and fills them with a few rows of data. The script doesn't specify to use a database though. So I can do mysql -u user -p; then use databasename; then from the interactive mysql program how
would I execute the script.sql which is sitting in my home directory?

how do you format a date from a field
like you can do echo date('l dS \of F Y h:i:s A');
but how would i change $row['Date']

DATE_FORMAT
!man date and time functions

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

never mind I found it I think

how do I show a list of what users are connected to the mysql server?
my best method is show processlist;

can i have two querys?

you can have as many as you want

SELECT yourDate, DATE_FORMAT(…yourDate…) AS specialDate, another one, another one…
if that's what you're asking….

hmmm, that might be good too

i do that for php..

i haven't done that in a good while

SELECT yourDate, UNIX_TIMESTAMP(yourDate) AS yourDate_uts so i can use it in the php time functions

but currently i'm doing $sql = "SELECT * FROM `50 Cent` ORDER by `Order` DESC "; how can that be added easily?
because i'm selecting all, and need all

TheGoldDIggah, you aren't inserting the datetime in the proper format, it is YYYY-MM-DD HH:MMS
TheGoldDIggah, so use '2007-04-08 17:25:31'

I have a db with an innodb table, that has over 2.5 million rows, the selects are extremely slow. Last night i converted it to myisam, and its running like a champ now. Does anyone know why innodb is so slow?

airj1012 you can do *, DATE_FORMAT(..) AS eh

oreonix many reasons, clustering, locking, storage space usage etc

oreonix, did you properly configure InnoDB?

It really depends what kind of queries you're doing

ya, i just figured it out, $sql = "SELECT * FROM `50 Cent`, DATE_FORMAT('$row['Date']', '%c-%d-%Y') AS Date_Format ORDER by `Order` DESC "; look good?

MarkR42, they are simple queries

One of the main things is that innodb generally uses more space than myisam, especially if you have a big PK

no joins

I assume you have all the same indexes

MarkR42, indeed

I'm trying to make some of my queries faster. In one query, when I changed a left join to an inner join it went from taking 1.5 seconds to run to taking 19 seconds

however, MyISAM and InnoDB have very different implementations of PK

I thought inner joins were supposed to be faster

Inner joins are the kind you normally want to use where possible
Do you have examples of timings of specific queries?

MarkR42, yes

yea, an inner join has the same effect as the left join in this case
but apparently it's much slower

Some kinds of select count(… querys are a lot slower in InnoDB, because myisam stores the key cardinality and in some cases it's sufficient just to use that
Basically the bottom line is, InnoDB tables take up more space hence you need more IO to read them in when doing (e.g.) a full table scan, or a scan of a given key

9'
when set to innodb it runs at 1.0 on return
myisam .09

Does EXPLAIN output different things for them?
You may want to turn off the query cache if you're trying to do benchmarks

MarkR42, no they show the same
for explain

The only really valid timing is on an idle, freshly booted machine that has had no other queries executed since reboot

MarkR42, query caching is disabled on our dev box

oreonix, did you tune InnoDB?

where i have been doing the testing
HarrisonF, what kind of tuning? like setting key buffers?
in my.cnf?

setting innodb% parameters in the my.cnf, yes

can anybody see whats wrong with this?

nothing innodb specific

$sql = "SELECT * FROM `50 Cent`, DATE_FORMAT('{$row['Date']}', '%c-%d-%Y') AS New_Date ORDER by `Order` DESC ";
syntax somewhere, i'm just overlooking it

your from

?

heh

you have stuff after the from, that should be before it

u cant just make it up?
its not smart enuf 2 no?

seekwill, im sure you can,
i think mysql should be smart enough to read my mind
actually

could you paste once again what u have said to me? I didn't have logging enabled and i can't see it anymore

$sql = "SELECT *, DATE_FORMAT('{$row['Date']}', '%c-%d-%Y') AS New_Date FROM `50 Cent` ORDER by `Order` DESC ";
?
think that worked

eh
airj1012, get that * out there
out of there*
specify what you want

innodb_buffer_pool_size may be far too small

"select *" is a waste of resources
MarkR42, interesting

"select *" is actually not as bad as people think, it depends really what other columns you have
if they're small, you are probably ok
on the other hand, "select keycol from sometbl where etc, order by keycol" can be optimised in some cases so it doesn't read the table contents at all

MarkR42, are selects from innodb more expensive then myisam

Hello

I don't know. They shouldn't be vastly so, but there are some overheads. InnoDB tables typically are bigger than myisam ones so the I/O overhead is greater.

yea
I am using a vps, where there is shared disk

oreonix, the default tuning settings for InnoDB are very poor

so its probably better to just keep it at myisam

Also InnoDB's MVCC means it has to consider rows which belong to different transaction IDs (and ignore them, but still read them from disc)

whereas the defaults for myisam are pretty decent

ahh
k
well now i want to see what happends
hehe

if you tune innodb_buffer_pool_size, it will make a huge difference

HarrisonF, I will try it right now
:-D

Question, i would have some help on database structure to use… I have 1 object which can below to several types, i've a reference, a name, and i don't how to structure my database

what are the types? (just to help)

humm it's type about guns

I shouldn't have made "dev" one of my highlights.

i mean 12/65, 12/70, 12/7, what kind of guns, etc..

HarrisonF, what is the default innodb_buffer_pool_size?

8M

Mine is 8M

i am trying to gauge what i should set it to
8m!
….
lol

how much memory do you have on the system for use by MySQL?

2 gigs

would like some help putting a postcode search on my site?

and it is all for use by MySQL?

is it possible to get true rednundancy with mysql cluster running 2 computers, both has ndbd, ndb_mgmd and API client?

HarrisonF, no
512
for mysql

terver, no
oreonix, i'd set it to ~400M then

HarrisonF, even if managment nodes are on both computers?

The minimum number of physical machines is three

terver, correct, you need to have a management node on a third system

damn, that's bad. and if i have only two, and i want some backup server i would need to use replication?

Use replication with caution; understand how it works fully.

and in case of "master" server failure, after it starts again i would need to do some script that will change his master status to slave, to not lose those INSERT's which were made while it was offline?

HarrisonF, no real change after the buffer was setup
in the query speed

If you want redundancy use master/slave replication with at least two slaves. Have fail-over happen manually, be sure that the master is dead before you fail over and make sure all your client processes only ever write to the master.

anything else?

MarkR42, bad thing that i have only two computers.

In which case, you probably can't sensibly set up a rendundant system. If you use master/slave replication, and the master fails, your slave will have to become the new master, but you now have no way of making an online backup to restore the old master as a slave.
(without downtime)
Because we've found that taking a consistent backup is possible only really by having mysqldump take a global read lock (unless you use transactional tables exclusively)

hm, what's about master-master replications?..

If you use 100% myisam, you can do a "flush tables with read lock" then copy the data/ directory. If you use 100% innodb, you can use –single-transaction
master-master replication is not suitable for general purpose work
because there are too many ways of breaking it easily

second server will be only backup server. he will just wait for master server to fault.
no activity with mysql on it.

You will want to take backups of a slave probably
because you can take a consistent backup while the slave is stopped, without locking the master our
out
locking the master will have severe impact on users, so unless it's something like a mostly-readonly database for reporting or something, it's likely to be unacceptable

and what i want it's when master to up, all those inserts to slave happen on master automaticly. it could be possible with cluster, but as people said having TWO managment nodes won't safe situation

'Can't connect to local MySQL server through socket '/path/to/socket'
where is the problem i use a macbook

You are confusing replication with ndb-cluster, they are separate features

check either mysqld is running, either it has socket= option in his cfg.

where can i find the cfg file

MarkR42, i understand, i was asking about cluster before, but people said "no", so i asked about replication as a solution for 2computer system.

Basically, you probably want more than 2 machines

snuki_mac_de, well, depends on your system, for me it is /var/db/mysql/my.cnf

However, you can do master/slave replication
*provided* your clients only ever write to the master
and if you need to failover to make the other one the master, your clients know about the change

i will use CARP
so they don't need to know about master\slave..

I don't know what that is

if one machine fail, backup one gets its IP

You must not have that happen automatically
There are also other problems - what does "fail" mean?

why?.. i see no pitfails

There's "fail" and "fail"

shutdown in case of hardware problem

You may actually not WANT the other machine to become the master automatically
and you DEFINITELY don't want the master switching willy-nilly back and forth
i.e. after it's failed over, you can't failover again until the other machine has been brought back up as a working slave

So, I have a complicated query that I want to create a view of. If I create the view and run select * from my_view where id = 1111, it is very slow (1.0 seconds). If I append the where clause to the select statement used to create the view, it is very fast (0.01 seconds). Why is this and what
can I do to remedy it?

:tr

automatic IP takeover is a really bad idea, it may failover when the box isn't ready for it

:terver I found it the my.cnf , what shell i change

this ip takeover will happen only if first machine is really fail, and don't answer for ARP requests although yes, it increase complexity, i agre.
snuki_mac_de, place socket = /tmp/mysql.sock string to the [mysqld] section.

"fail" is a non-exact status
How do you know when a machine has failed?

it won't broadcast itself like a master for a few ms..

Right, but what happens if the broadcast continues but the machine has failed in a way which stops it working?
And moreover, what fi the broadcast stops but the machine is still otherwise working
Failure is not a boolean state
We find that in practice, machines often "fail" in ways other than a hard crash

yes, that good point. i think i need to monitor mysqld somehow..

terver, if you want an active/passive setup, i would look into DRBD

yes, i was looking at it too

terver, DRBD/heartbeat work well with MySQL for a shared nothing setup for HA purposes

but i didn't know how it works with mysql
my first aim was to built such system on freebsd (there is no such thing like drbd for it), but now they said to build it on linux..
well, thanks a lot MarkR42 for explaining all pitfalls with two machine redundancy, i'll definitely took this to my mind
and thanks HarrisonF for reminding about drbd
HarrisonF, in case of master fail, after it reboots, and some modification of slave filesystem has been made, will their be automaticly redistributed to master? (drbd)

Is a CHAR(10) field any worse than INT AUTO_INCREMENT as a primary key, assuming that we were going to perform lookups on them?
And in terms of speed, in that case.

thanks it works the server is runing

terver, yes

anybody know whats wrong with this query?
SELECT *, DATE_FORMAT(`Date`, '%c-%d-%Y') AS New_Date FROM `50 Cent` ORDER by `Order` DESC
all i'm getting in my New_Date is NULL

I'm not sure whether the comma after * is doing anything bad, but at any rate its unneceessary.
*it's

ok, i'll try that

Oh, sorry, no, nevermind me.

got an error
ha

I'm a bit weird today.

its cool, i can't figure out the problem

What error are you getting airj1012?

airj1012, what is `Date` normally?

just a field in my database with dates in it
just my new field is Null and i thought it should be a reformated date

is it an actual DATE or DATETIME type?

09-11-2007 is the format
and I have some February 6, 2003 as Date too

that is the problem, DATE_FORMAT only understands dates in the format MySQL expects

which would be?

YYYY-MM-DD

iso-8601

is there something that would understand February 6, 2003

you can use STR_TO_DATE to convert it

same as Date_Format, in the query?
hmm
worked on a few, but still not on the Month date, year like March 3, 2005

I recommend you convert the date on the client side to an internal format, then reforamt it in iso-8601

so go back to Date_Format with YYYY-MM-DD
?
i could just make 2 fields and do it that way too, hmmm, which to do, so many choices
ha

Parse it using client side code

que?
lost me

On mysql client side code, you can parse these dates before you insert them

wow i feel dumb, i don't even know what parse is

does mysql select distinct ignore case?

It depends entirely on the collation

how do I tell that? I am jsut doing the query: select distinct (name) from reports;
but it doesnt seem to return everything

Every table has a collation, and every column has a collation (usually the same as the table's)
you can probably see the collation in SHOW TABLE STATUS

i'm having a problem with populating a table with dates, can anyone help?

each database has a default collation which is used for new tables if theydon't specify one

it is InnoDB

It is irrelevant what engine you use, any collation can be used with any engine

oh, sorry, it is latin1_swedish_ci

Right, the _ci means case insensitive

hmmm, I see
that is weird, that is the default ?

Yes. Swedish collation can be used with some other lanaguages too
i.e. it is not *specific* to Swedish
But MySQL is a Swedish company

i have an opening date and a closing date. I need to create a table containing all the in between dates - is there any easy way of doing this?

hmm, even though they are swedish, they should set the default to english

Hi, is there a way to convert some tables in my db from latin1 to utf8 and only to use MySQL (no iconv, php or something)?

or unicode

sudoer, swedish sorts english properly
sudoer, hence the default, there is no 'english' sorting set
Jeena_, yes, see ALTER TABLE … CONVERT TO …

ah ok

is the default to be case insensitive though? doesnt that seem not correct?

latin1_swedish_ci

that is correct

the ci stands for case insensitive

wouldn the default make more sense to be cs ?

why?

hi

because car and Car are different
they are distinct

not to most people
SELECT DISTINCT(name COLLATE latin1_general_cs) FROM reports;

ok, thanks for the help guys

need some help with cursors…
a cursor within a cursor, i land up in an endless loop

hello
i'm using mysql's fulltext search
and i was wondering how can do to make it return results even for a 3 characters query
like 'asd', 'one', '233'…
any ideeas?

how do i change an entry in mysql

insert/update/select/delete

insert only works if there is no entry?

Psyche-, change ft_min_word_len to 3
and rebuild the index afterwords

HarrisonF, so that's the only method?

yes
why would there be any other method?

HarrisonF, I'm asking because I don't have access to mysql config files
and how can i rebuild the index
?

drop and readd the index or do something such as repair table

Do text goes up to 32 or 64K or is it 256?

HarrisonF, ok, thanks

What should be averge lenght for name feild having varchar!
?

there is no average value.
set it so that it will contain enough characters for your worst case scenario.

thumbs, What generaly you would keep it?
32 or 64?

it depends on the requirements.
if I need 50 characters, I use 50 characters
if I need 100 characters, I use 100 characters
clarify your requirements, and come back here

hi
can anybody help with multiple cursors in multiple while loops?
im trying to populate a table with a field from table A repeated for the amount of times of a field in table B which will be repeated for the amount of times in a field in table C

1-2-1, 1-2-2, 1-2-3, 1-2-4

if I get a message that says "hostname is not allowed" to connect to the server… how can I add that hostname for that database?

!man grant

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

^^

cool… I had it set up originally as "hostname" but then I changed the server name to "hostname.domainname" and now its refusing connection

anybody?

I don't know enough about cursors, sorry

:-/
thanks anyway man

strange… I see my record in mysql database under users table… but it says N N N N for Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv
even though I told it to GRANT Select,Insert,Update,Delete,Create,Drop on the right table
do I need to grant those permissions to the user itself as well? a bit confused here as it looks same as the other user I have minus the N N N for select,insert,update etc.
well I did an update on the privs to say Y even though I know thats not supposed to be how it works.. but it worked

when i specify a column as unique, that makes it an index as well right?

if i set "foreign key(user_id) references users(user_id)"… why can i still create a user_id in table2 that doesn't reference any real key in the users table?
it seems like there's no key constraint when there definitely should be

Foreign key constraints are only honoured by certain engines
notably InnoDB
you can't create a FK constraint from one engine to another

hwo can i tell what engine this db is?

show create db

s/db/table/

oh, it says MyISAM
i guess i need to convert all of these to InnoDB?

If you want FKs to work, you need to convert them to InnoDB
do not do so lightly, as it will change the behaviour of your application sigificantly
particularly performance

guys.. can someone help me with this problem.. is it mysql? php or what? .. have a look if you want. www.bujanoci.tk
on the right site somewhere down.. it's not parsing a piece of code!

what?

on my website.. bujanoci.net.. there is some code not being parsed… where does the problem rely?

it is not in a human language
i see nothing strange

true
it supposed to view.. last gamers… but it isn't showing
look down.. at the right side
is there a mysql error or… a lib problem. I don't really know

your page is a link, not more
ah, so you have to enter to see the crappy part

it's just a forward.. but it's running on this server Im now connected from

the problem has nothing to do with #mysql

but..?
I deleted the table arcade_games , created again.. still same error

The performance could improve. Be careful.

it is not a mysql issue. do you understand that?

I understand that.. but what could be then?

lack of php/html knowledge, ability of solving, overcoming problems alone
disability

seems to work okay
heh

this website is actually a friends of mine.. I script my website my own.. don't use php-nuke or e107 templates.. it worked though into another server

even after converting both tables to innodb, i can still insert rows with foreign keys that don't exist in the table they refer to…

Hi, i am getting Error 2003 while connecting to mysql server on a host name. I have checked that skip-networking is commented in my.cnf

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 and make sure no overrides on the mysqld commandline

perror 2003

!perror 2003

ah

Post the appropriate create table statements and insert statements that show the behavior.

or i thought…

Also note the version of mysql you're using: select version();

ty, one sec

the_wench, I did it and now i am getting error access denied for user@my-ip addr!
the_wench, The password is right!

oh, now it works as expected… apparently converting from myisam to innodb didn't preserve the foreign key part of the definition, so i had to re-add it

the_wench, Any idea?

Mishu, the_wench is a bot

thanks

Why is it called BINARY instead of like… uh… "ANAL"?
Maybe not "ANAL", but why "BINARY"?

huh?

You know… the case-sensitivty keyword.

gnari, any idea why am i getting the error/

WHERE BINARY foo = 'bAr'
!man BINARY

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

What the hell is a "binary" string?
I know in practice it's case sensitive. But why is it called "binary"?

it does a binary rather than a lexicographical comparison

Mishu, no idea

gnari, hmm i got it i added a user for my ip i.e in Grant i used my ip add instead of localhost. How can i add a user which allow to connect from any ip?

Shouldn't BINARY be faster, on top of being more accurate, then?

Catnip96, accurate?

Yes. Accurate.
"Foo" vs. "foo".

Any one having any idea!

So, if I have a column which will always need to be exact, should I make that BINARY instead of adding WHERE BINARY = … in every query?

that isn't accuracy, they each do 100% what they are supposed to do

Or should I do both?

Catnip96, you should add it to the column attributes

Only?

Catnip96, so that indexes are stored in that fashion you want

Yeah…

Catnip96, there is no need to do in the query if the column is declared that way

Well, I also do ORDER BY … ASC etc.

Catnip96, btw, BINARY is depreciated in favor of collations

I feel it's good to be clear.
Are you serious?

in mysql i added a user for my ip i.e in Grant i used my ip add instead of localhost. How can i add a user which allow to connect from any ip?

Wait… what is a collation?
!man COLLATION

see http://dev.mysql.com/doc/refman/5.0/en/collation-character-set-applicability-table.html

That manual is no help as usual…
What's this nonsense about BINARY being deprecated? Surely you're not serious now?

Catnip96, http://dev.mysql.com/doc/refman/5.0/en/charset-general.html

Doesn't seem to mention how they are done or that BINARY is deprecated…
Is this manual written by a single employee working part-time or something?

Have you been drinking?

Yes. But only Coke.
Whenever somebody says that something that I use is deprecated, I feel very, very uncomfortable.

Catnip96, http://dev.mysql.com/doc/refman/5.0/en/charset-binary-op.html
it isn't really deprecated, just used for what it should be used for now, binary data

Hrm.
How often do you store binary data?
Like… never.

Catnip96, quite a bit actually.
For instance PDF files

Hrm.
The old "file system vs. database storage" thing.

also things like MD5 are really binary data and not strings

we send letters to patients, insurance companies, vendors and doctors
these letters are pretty small…20k in a pdf

Does that mean I should make my VARCHAR(32)s used for MD5 BINARY?

so they can go in a table with engine = archive

yes

I personally will never understand why anyone would want to use PDF, a propritary, crappy format which only works in a terrible reader software.
I suppose if you deal a lot with physical documents that need to be digital, it's good…
But that's a weird approach anyway…

actually there are many programs that can read and write pdf
even the FSF uses pdf
well the good thing with it being in an archive table..is that i cant be changed
no updates or dletes, just inserts

So… if BINARY is deprecated, how do you do a "WHERE BINARY foo = 'bAr'" properly?
I understand that if you always want case sensitivity, you must change the column collate.
But what about when it's only sometimes?

WHERE foo = 'bAr' COLLATE latin1_general_cs

Well, that ain't pretty.

or whatever collation you want to use

Very non-general.
Why must it be like that?

cause the SQL standard says so

Stupid SQL standard.
So it's bad of me to use BINARY?

WHERE foo = 'bAr' COLLATE binary
that technically works too, but only if your character sets are the same already

:/
OK. This is the first time this has happened, but MySQL Query Browser doesn't give me any error, but still refuses to update my table's column X from non-BINARY to BINARY.
Very strange.
WTF?
What is going on here?
I check BINARY, and update settings. It gives me no error, but doesn't actually change this.

turn that away notification message off, please

what's an easy way to flatten a list of lists?

define flatten

one list with all the sub items

how do I tell if a db is innodb or myisam?

er, wrong channel.

huh?

SHOW CREATE TABLE tableName

yeah, but it just said "default"
the default is myisam, correct?
but I'm just wondering if there is a way to get the explicit database type
I know there is
but I forgot the command and google isn't being very helpful

default is usuallly MyISAM, yes. indicated by 'table_type' variable
a mysql database can have tables with different table types

ahh, ok yeah

show table status;
will show what they currently are

that's the one
thanks

or query information_schema

is there a way to 'pause' a source file??

while it is loading or with a command in it?

with a command in it
like the dos pause command

OK. I'm very confused now.
Why does Query Browser behave like this all of a sudden?

It hates you

Seriously.
This is eerie.

It seriously hates you.

Can I change mysql password from phpMyadmin?

using the console is recommended.

Ohm{}nis, can you help me change the root password and the user thumbs ?

!man reset password

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

reset root

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

this link should give you pointers

Does that bot use the Web site's search engine and grab the first result or something?

oh thanks thumbs

no, some specifc entries are pre-entered

hey whats the best way to store dtae and time? timestamp or datetime?

datetime is a good start.

or maybe i should just keep a unix timestamp? but will sorting work ok with that?

I recommend datetime

Does it have a fallback?

could someone please take a look at the following case statement and tell me what i am doing wrong? http://rafb.net/p/5bDmQZ24.html

I can't say for sure.

Basically, TIMESTAMP doesn't have one as long range.
Also, TIMESTAMP requires you to convert it before output.
so TIMESTAMP should only be used for internal, "short" time tracking AFAIK.
But TIMESTAMP is probably faster or something…

do mathmatical operations work on datetime fields?

ge2x, datetime is best if you will be using the program for years to come.

I don't know. What sort of math?

date and time functions do.

addition, subtraction, etc

I really don't know. Wouldn't surprise me.
Don't see the purpose of that, though.

select me + angeline jolie;

you can add/subtract day/minutes/hours/etc

or do you mean more text stuff:
concat_ws("",me,angelina_jolie);

INTERVAL is such a nice feature… but probably non-standard.
What does _ws mean?

Catnip96, with separator
but I use it to prevent NULL issues

What does OPTIMIZE really do in mysql?

MINUTE etc.

if I did concat(me,angelina);

Been asking myself that as well.

and angelina was NULL…then it would return null
whereas with _ws it will return the non-null value

datetime NOW() - INTERVAL 7 MINUTE

iirc it has something to do with fragmentation but don't quote me on that

and the ("" can be anything you want

Hrm.

I use ("\n" a lot

I assume it has something to do with… optimizing indices and shit.

using php my admin, is it possible to make default table type innodb

Catnip96, please dont say shit

"Updating" internal stuff.

in here we say poopoop
er
no
dung
ok i am confused

snoop-: I wish people would stop using that horrible software.

sorry I thought I was in an ubuntu chat room for a while, and thus was supposed to be telling people what to do

Why do people have this crazy urge to use Web "apps" for everything.
s/./?/

bingo - if you use aliases for fields make sure you place them OUTSIDE of the case statement

the question is, why arent they optimized by default? Are we just designing bad db's? How often should it be optimized, etc? Can you optimize to much?

Who?

Catnip96, cause you can change how things look on the fly?
you can run the app anywhere

Exactly my questions. :/

your report writer and your app maker are exactly the same program

myself; i was having problems with my case statement

thats all my host offers me
i prefer using ssh but oh well

i have a query that's around 20 lines long; it's a bit complex

Your host doesn't allow you to connect remotely to the MySQL server?

guru, ah I thought you meant the stored proc case statement

Switch host.

i like web apps because i can use them from anywhere and i dont have to worry about my storage failure locally
I run RAID5 on my servers

Hrm.

well if someone can recommend some good hosts

no, but i really should make these queries stored procedures

Lol, i actually run raid1 on my desktop as well =P

guru, thats all I do now

When I become a millionaire, my servers shall have RAID as well.

guru, in fact I have stored procs, which call functions, which I can call from the command line, which will then generate code for stored procs..so i dont have to write the code much of the time now

i'm interfacing through php though and i don't know for sure if stored procedures can be created via php

Your not already running RAID in at least some type fo form?

guru, actually they are created in mysql

Ouch, i would never do that on a server

Um… no.

guru, and called from php ..and yes yo ucan call them from php5

Why not?

you don't want to create stored procedure left and right.

Irresponsible IMHO

thumbs he might..he might not

Had driver issues with the RAID I tried to set up.

depends on what he is doing

at best, php should execute them

Irresponsible? I backup regularly via FTP.

i know that they are created in mysql but what i'm saying is i don't know if the interface to mysql which is provided by php will allow you to create procedures
php can and does execute existing stored procedures just fine

guru, ah you cant use mysql console from your host?

So, i dont know about you, but i dont want to lose any data, even 24 horus worth of it

You can't call me "irresponsible" for being too poor to afford it.

no, this is being installed on a hosted environment where the mysql command line client is unavailable

Why doesn't MySQL run OPTIMIZE regularly by itself?

guru, gotcha…do you have ssh that you can use and then get console?

nope

guru, i will test creating a proc with php right now…brb

Catnip96, RAID is not expensive

no, but for the heftier queries it certainly does help with performance
especially if the query will be executed over and over again

I can be, but RAID itself isnt

Golden toilets are not expensive.

Er, it can be

my point was that you don't create them on the fly. You create them first, then execute them.

agreed, but without access to mysqc then there is no way i can create them if php doesn't support it

!man OPTIMIZE

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

well, creating procedure is a simple SQL query itself.

Eh, whatever, your missing my point.

i.e. CREATE PROCEDURE ….

guru, it works

ahh, excellent

guru, i tested via phpmyadmin
typically you say something like
DELIMITER $$…
but phpmyadmin choked on that

ick, i use the mysql query browser

so I removed the DELIMITER statements
guru, ah thats built into it
guru you can use the query browser to copy/paste your stored proc
right click in the right pane with the SCHEMATA tab chosen
and you will see the option to create stored routine

yes i know, and on my development machine that will work perfectly fine but i'm not going to manually deploy this solution each time

you can also create triggers that way
ah
its a shame they dont give you ssh access

i'm familiar with stored procedures and the tools; i work for a billing company

hehe
ok sorry bout that

no problem

most of the time people have no idea and ive got to evangelize

never use a tool you know nothing about

Hello all

this query is hideous

guru, but if you are able to access via the query browser, shouldnbt you be able to get to it from a mysql console on your machine?

I need some help building a query
Basically it needs to do

of course. i have unlimited access on my dev machine

God this fucking manual sucks.
I can't even find the section for "OPTIMIZE".

Catnip96, please do not swear ok?

words like that are offensive

SELECT ID WHERE rental (a text field) = value OR WHERE frental (text field) = someotherthing

specifically "manual"

www.mysql.com/optimize table

whether in the context of "manual labor" or "read that fuckin manual"
Cpudan80, SELECT ID WHERE rental = value OR where frental = something;

looks like you answered your own question

Cpudan80, dont put the word WHERE in twice

Ok - I just wanted to confirm the or part

want to see this query?

guru, sure.

so WHERE rental = $foo OR frental = $bar ?

Cpudan80, yes.
Cpudan80, well the $foo part is a php question

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

well yeah - I was just sayin though

and keep in mind that query is the smallest one in this application

Now — why would you use LIKE instead of = ?

guru, ah yeah stored procs

Is there any downside from running OPTIMIZE TABLE regularly?

Cpudan80, for this:

i run optimize table each time i delete

Select firstname, lastname from customer where firstname like "TOM%";

this ?
oh

Are you serious?

or I could do
Select firstname, lastname from customer where firstname sounds like "TOM%";

If that were needed, shouldn't MySQL do that itself?

yes, but generally i'm deleting round 10k rows

which would get me TOM, TIM, TAMMY etc

it should imho but i can see cases where i wouldn't want it to

Hrm. Why is there no OPTIMIZE SCHEMA? Must I select all the tables by name?

no it wouldn't

Catnip96, use mysqlcheck

guru, sounds like would.

like 'TOM%' would not match TIM, TAMMY, etc

guru
sounds like would
notice I posted 2 queries/.

Bleh. I will just do it manually regularly from MySQL Administrator.

there is like and there is sounds like

ahh i misread that as just like

aye

http://www.pennergame.de/ref.php?uid=2851

Catnip96, i have a nice little stored proc that will take a command and apply it to all tables in a schema

You can use SOUNDEX or something for "sounds like" regexp.
Hrm.

specifically right now I do it by creating a function which contains the exact command I want to run for a table, then I just pass that function the schema and table name..the stored proc on the other hand will call that function using the values in information_schema for the schema handed to
it, and process it for all the tables in that schema

I must try to not pre-maturely optimize so much.

i dont know if Ive ever optimized
but our db is small like 117mb

117 millibits IS small.

ah the current version is 166

You can almost store true/false in that.

hehe

OK. So BINARY has been deprecated for doing case-sensitive comparisons?

Catnip96, dunno, it worked for me

why in some querys i dont need ' ' but others i do around the WHERE variable=' ' —-

Catnip96, but if I were to do case sensitive i would just do select bla from bla where upper(bla) = upper("hey");
jtm, if it is a number you dont need ' '

it is…

quote

Use ` around identifiers (database/table/column/alias names) and ' around strings and dates.

^^

jtm also if you are needing to put things around your field names you want `
like wench said

weird

Um… of course it worked for you. It can be deprecated and still work.
Just look at the incorrect HTML that people throw out.

jtm, why is that weird?
Catnip96, ive never seen any bad html

im soo confused
because it's a INT field and i've been passing $fid to it
it works if i use ' '

jtm, if you use `database`.`table`.`column` it makes sure mysql understand it to be one of those

but $fid is an INT

jtm, thats a php issue not mysql

"ive never seen any bad html" — HAHAAHAHAHAHAHAH!
Funny joke.

aoirthoir ok…

Catnip96, i close my eyes

you can use single quotes with INT fields.
they are not required, however.

thumbs, aye..and double.

ok

but if it isnt working when you dont use the quotes, then it something on php's end

but why are they not considered INTs?

if the column is INT, the column is INT
I am not sure what you mean by 'not considered'

jtm, why is what not considered INT?

the variable i am passing
I don't know?!

jtm, its a php issue.

I suppsoe this is a php technicality

Do I have to cast it? Maybe it is.

mysql does not care.

mysql datatypes

but if i ask in there they'll probably redirect me here

no they will probably kick you
they are notorious for that

if you're as vague as you were, sure.

all I can say is, if your script works with '$val', then use ' '

sorry i had someone yelling at me
mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in Cwamp\www\lib.php on line 66 if i don't use ' '

im always sorry when I have someone yelling at me

But the variable I am passing is an INT

echo the query, and run it in the mysql console.
see what it says.

I do it throughout the whole code and never had a problem

echo the query, and run it in the mysql console.
see what it says.

ooo underworld evolution is coming up
for those that think it wasnt as good at the first I have one word
leather.

if the mysql console throws an error, find the error, then make sure your php constructs the query properly.
are you still here?
or am I talking to myself?

thumbs, im listening
and yer right

sorry thumbs
it's too hard to do that
it might be more of a php issue
im not sure

it IS a php issue.
and that is extremely simple to do.

well i was doing $cat = mysql_real_escape_string($cat); with INTs throughout my code

if you have no idea what queries your php code is generating, don't run them.
TEST THEM FIRST

but those queries were working fine

apparently not any more.
find out why.

Anyone know a good Windows alternative to phpMyAdmin?

and i wasn't using '' around my id

STOP for a second.

cat*

echo the query instead of running it, and debug it in the mysql console.

ratonnn, navicat
ratonnn, its non-free
but i liked it a lot

can i use a default value of NOW() on a datetime field?

well it fixed it when i got rid of mysql_real_escape_string but it wasn't a string

mysql query browser is pretty good too

sigh
do whatever you want

im just saying it works now

I wasted enough time trying to help you

You didn't waste time, you did help.

jtm, you need to really study up on php then if you arent goig to use real escape tstring

I was told to use it on any variable before a query lol
It worked for 95% of my code, but no it seems like something changed.

well if you have no idea when to apply a given function, you should read up on it before applying it.
blind coding is not productive

thanks

ratonnn, yw
ratonnn, mysql query browser is free btw
thumbs, yes

for your reference, mysql_real_escape_string removes/excapes characters that might be used in conjunction with user input and sql injection
it should always be performed wherever user input is used.

it is
I was using it on ints
I know I shouldn't but I was making sure and I never had an issue.

you should validate that input.
i.e., make sure the number is a NUMBER

i do

ok.

is_numeric

ok are we done?

But that's not good enough

how so?

Finds whether the given variable is numeric. Numeric strings consist of optional sign, any number of digits, optional decimal part and optional exponential part. Thus +0123.45e6 is a valid numeric value. Hexadecimal notation (0xFF) is allowed too but only without sign, decimal and exponential
part.
tahts why itsnot good enough

mysql datatypes

thumbs someone said to use ctype_digit()

mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in Cwamp\www\lib.php on line 66
ok here is the issue…
it doesnt have to do with the data you are passing mysql

as long as the value is not 0–DROP DATABASE mysql;

The query didnt have a result

its got to do with the variable NAME you are requesting FROM mysql

then find out why
run the query in the mysql console/

ok

fetch_association returns a series of variable names

well i am only getting 1

again
denug it
debug, rather

http://us.php.net/manual/en/function.mysqli-fetch-assoc.php

if you expect more, look at your query, and fix it.

you need to read that
what mysql_fetch_assoc does is to take a row, and turn all the columns and values returned into a PHP array

aoirthoir I wasn't using * I was just doing a single select i.e. SELECT blah from blah2 where blah3=10;

so something that you are suplying to mysql_fetch_assoc isnt a valid item