as opposed to varchar i’d have to pass the string

Possibly.

Whoah now, passing numbers to an enum leaves the door open for a world of hurt.

o

Passing numbers means you have to go look up which number is which string.

I normally use CHAR(1)

And if the number to string relationship changes you are in said world of hurt.

it wouldn’t change.
because it’s predefined

So it can be done, but you have to be very very careful.

And you never change the definition.

And when was the last time you had a manager not change something?

never

then surely it’s better to use varchar than enum
as it means i can store any string i want

Yes but it uses more space

yes, but varchars leave you open to denormalized data, and take more storage.

If you have, 10^8 rows,
a varchar containing “active” or “inactive” becoems quite expensive

When your three users enter ‘St’, ‘Street’, ’street’, then you cry.

but its a road

I never said don’t use enum, I said don’t INSERT 1 when you meant ‘Road’
Just because you know that *at the moment* ‘Road’ is in the 1 index of the enum.

And really don’t use enums if they contain numeric values. Do not create ENUM(‘1′, ‘2′, ‘3′).

Of course, you can use a lookup table and a foreign key and be proper.
Of course not Snoyes, you would create ENUM(3,1,2)

no i meant the enums contain string values but i presume u can call them with numbers like an array

And then confuse the hell out of your junior admins.
Yes Saberu, which is why I said ‘bad DBA, no biscut’.

I contend that if you have to look at the table definition to know what values are being inserted, you have done something wrong.
bI contend that if you have to look at the table definition to know what values are being inserted, you have done something wrong./b
ubI contend that if you have to look at the table definition to know what values are being inserted, you have done something wrong./b/u

hmm large book

so I should use enum, but not call with numbers

You *could*.

because it’s more space saving with enum than varchar

Or you could create a lookup table and use a foreign key.
Same benefits, more normalized.

and allows for more modification later

Doing ALTER TABLE on a large table is expensive. Really.

And easier modification. You add a valid value with an insert instead of an alter table.

At the cost of requiring an extra join when quering.

Even changing an enum
A FK constraint is going to put a major slug on inserts, updates etc

It’s a PK join on a unique index.
In other words, pick your poison.
Now SET, there’s a fun one. You can do some really run schizzit with the bitmap nature of that one.
s/run/fun/

We have some very large tables. We use ENUM and SET quite a lot. SET is particularly good as it only uses one bit per thing.
Moreover, indexing sets is particularly advantageous

thanks mark i’ll take a look at that

Whoah now. Either you need ENUM or SET.

Well, if you have only a relatively small number of items, SET is much better than a second table

http://www.vbmysql.com/articles/mysql/the-mysql-set-datatype/
a href=”http://www.vbmysql.com/articles/mysql/the-mysql-set-datatype/”http://www.vbmysql.com/articles/mysql/the-mysql-set-datatype//a

The article barely touches on the efficiency of FIND_IN_SET and an indexed SET column
I know that SETs aren’t in normal form

Yeah, it needs updating.

But they are *waay* too useful to ignore if you really, really care about performance
uBut they are *waay* too useful to ignore if you really, really care about performance/u
buBut they are *waay* too useful to ignore if you really, really care about performance/u/b
If there is an index on the set

!man find_in_set

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

maybe not that one
some other function, I can’t remember which

find_in_set uses bit math on the SET datatype, but I think it still would have to table scan to work its magic.

!man set data

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

!man set type

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

!man enum

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

hey
like ‘%%’ doesnt seem to search by keywords

Hmm… SET is pretty nifty

any way to supply “jesus christ” and have it search both for jesus and christ as keywords?
besides splitting it manually

LIKE ‘%%’ would return all rows… not really “searching”
Full text indexing

so like ‘jesus christ’ ?

!m garbeld full-text

garbeld see http://dev.mysql.com/doc/refman/5.0/en/fulltext-restrictions.html

What about words like on, a, crutch, pogo, stick, sweet, zombie?

Man, I haven’t really touched MySQL since I started…
I don’t know why I hang out here

Nor have I, which explains the shift in my ratio of help to snide comments.

hehe

Not that it wasn’t high to begin with.

WHERE MATCH (title,body) AGAINST (‘database admin’);
would that work fine seekwill?

That wouldn’t find your savior though

hmm I’m sure there is a way using an indexed SET type you can search for a subset

what if it was ‘jesus christ’

using an index

That would work

great thanks

Yet I can’t obviously find it

It’s easy to find a specific set.

Be sure to read about FTI’s, it’s restrictions, etc.
(and make sure to create the FT index)

s/for in//
Fixed that for you.

It’s odd that a database company has a broken database search.

No, it’s karmic fulfillment.

haha

is it possible to change a table from innodb to myisam

its broken xml in the manual

Yes

cant seem to find it in phpmyadmin

As long as you understand what may break

very simple table
what may break?

The manual is always right, the XML host standard is broken.

there are 4 columns

hehe

The manual is a lot like Chuck Norris.

Well, um.. you could lose your job if you did it

im doing only inserts / selects / removes
there is no data in the table
not live table

Do you have anything for me to do?

I was about to ask you the same.

George did tell me to take advantage of times like these

ALTER TABLE table_name TYPE = myisam sounds right?

Times like what? Times of nothing to do?

Yep
I bet I’ll be making up for it at the end of the week, like last week

you can use ENGINE=MyISAM, which is preferred but equivalent

Times like these you beg Therion for a job.

And it will take a long time for a large table

cool well it worked hehe
so now i have to make fulltext indices for each searchable col?

What kind of job!

They don’t have the job I want

No, now you give up and roll this all into Lucene!

Grammar nazi, Mr. Exclamatory question.

lucene?
Can’t find FULLTEXT index matching the column list

garbeld, or write your own search engine

haha

ALTER TABLE `texts` ADD FULLTEXT (‘textname’)

You can create a single fulltext index for several columns
which is usually more desirable, depending on what kind of search you need

For a table with no data, you can just run it and see what happens

so i did that for each column

Goodnight Michael, sleep well, I’ll likely fire you in the morning.

Also create as few indexes as possible, as they use up space

and now when i try to search it tells me Can’t find FULLTEXT index matching the column list

garbeld, fulltext needs some data to work

there is data

You need a fulltext index which contains the exact column set you’re using

two rows

not enough rows

Someone didn’t read up about FTI

really?

LIKE I SAID

stupid fulltext restrictions

seekwill said to me?

Yes

you mean this ? http://dev.mysql.com/doc/refman/5.0/en/fulltext-restrictions.html

lol

so how many rows do i need? not working with 6 either

Restrictions in general, including benefits and limitations
Read the error, read what other people have been saying to you

hello there, is there a way to get the collation of certain field, it will be best to have a a “join” like result with Describe table

seekwill, right i have created fulltext indices for each column. someone mentioned creating it all at once
is this what i need to do? create all three indices at once? then it will work?

No, just one index, all three columns

thanks let me try
hmm but if i MATCH(‘jay christ’); this doesnt seem to match on “jay H christ”
matches on jay christ though
rather MATCH(col1,col2) AGAINST(‘jay christ’)

remember about the rule of 50% of rows
anything which appears in =50% of rows is automatically ignored

and the short words

ah thank you
kinda useless search then isnt it?

how can i select column name based on value in other table? table user: id price_level (values 1 , level1) table goods: id level1 level2 level3 (values 1 200 300 600)

rather for a simpler search its useless

rethink your db structure first
LK7R

easier to write a custom php search
easier to write a custom php webhosting search

that’s what i thought ….
wrong design
damm

so no one knows how to get the collation of certain field ?

Any chance to fix the design?

caotic, desc table

sure

will have to

tibyke, that doesnt tell the collation, (ive been using describe as well)

caotic, show create table foobar;

show create table table_name;

what i want to do is to have some goods table where each goods has more than one price level and i want to give different price level to different users

you need a table GOODS, a table GOODSLEVELS and a table LEVELSUSERS

thanks threnody & tibyke

and a table LEVELS if you have things like descriptions and such

take a moment and read this. http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

caotic, yw

rather no
sry

oh thx will read it trough

I would like to post some mysql… is there a pastebin?

goods keeps the goods, each good has a level right (so like 5), then each user has his own level (5)

pastebin

try http://pastebin.ca or http://pastebin.mysql-es.org

so GOODS (incl level col), USERS (incl level col), and levels (including level description or w/e)

ty

i think that will work for you LK7R

tibyke, would there be a faster way to get the collation of an specific field, or just parsing the “show create table”

then you can just join
oh shoot each good has more than one price level hehe
yeah so the thing i said before i s right

lol

GOODS, GOODSLEVELS, LEVELSUSERS, GOODS
GOODS, GOODSLEVELS, LEVELSUSERS, LEVELDESCRIPTIONS

level4) rows like that

colnames are goodid, levelid

it’s getting quite complicated

nope
thats the easiest way to do what you want

Maybe some more analysis of the problem domain is required.

which domain

what about 3 tables goods users pricelist

The purpose probably is to assign prices to goods depending on the user.

yes

right so you would have goods prices also table
where each good would have a price for each level

hi !
i change my computer

goodid, levelid, price

i want do a backup to all my database and also all my mysql hosting password and user

are the colnames

i uses already mysqdump for backup tables

all_databases.sql , is all my user and password will be the same ?

yup

No.

so i have to create another table where i will put the prices and then do a join select to get the correct price for the current user

ElDiabolo, if a solution exist to do that ?

ok thanks for the consultation

Best thing is to draw an ER diagram now and spend some thinking onit until you are sure it is correct.

i thinked do a copy of my /var/lil/mysql in the news computer

Just do it as (db-) root and it will work.

LK7R put the price, level, productid in one table

ElDiabolo, you speak about the copy of /var/lil/myql ?

ElDiabolo, will read trough the db design document thanks

userid, userinfo, userlevel in another table

No, mysqldump .

i think i may want to use the infomration scheema

ok

Copying /var/lib/mysql will do if OS and db version match.

Elagic, ok
ElDiabolo, ok

ok thx to all
nn

i must also copy the directory mysql-cluster in /var/lib/ ?

OS doesn’t matter

the directory mysql-cluster is empty

OK, thats misleading, Linux 32bit and 64bit is probably not the same.
Never mind, just copy everything.
You do have the same mysql version on both sides, do you?

yes
*same version
i finish my new computer configuration and tested it tomorrow thanks

can I change “ON UPDATE CURRENT_TIMESTAMP” to “ON UPDATE UNIX_TIMESTAMP”?

nope.
would violate timestamp type anyways.
which is way cooler than unix timestamp

what if I change the type to int(25)?

Then you can’t use a function for the default value at all.
And while you’re at it, read this:
zerofill

The optionally defined number after an INT data type represents the display width when used in conjunction with ZEROFILL. Please refer to: http://hashmysql.org/index.php?title=Zerofill

absolutely impossible?

Could use a trigger.

Could hack the code.

A trigger would be simpler.

Got me there.

doesn’t impress the ladies as much, though.

What is it that you want to do?

hi, how can I correct this pls? INSERT INTO table SET `zosnulyso4`=`0` Unknown column ‘0′ in ‘field list’

quotes

Use ` around identifiers (database/table/column/alias names) and ‘ around strings and dates. MySQL does allow ” for strings, but ANSI standard uses ” for identifiers (which you can enable with ANSI QUOTES option) and dont quote numbers

the same as ON UPDATE CURRENT_TIMESTAMP, only with UNIX_TIMESTAMP

qaws, ” not “
around the 0 that is

so how do I do the trigger?

!man create trigger

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

thx, but there is still problem INSERT INTO table SET `zosnulyso4`=’0′ Duplicate entry ” for key 1

And why?

So you already have an entry of 0 in zosnulso4, and a primary or unique key on that column.

so I my php code is simpler when I insert new data

I cant get Mysql to start.. says cant create PID in the logs

So you actually need a time, auto_inc. won’t do, right?

auto_inc?

auto_increment

auto_increment only adds 1
whereas the next time it is updated may be two weeks later
so no, auto_inc won’t do

4 [ERROR] /usr/libexec/mysqld: Can’t create/write to file ‘/var/run/mysqld/mysqld.pid’ (Errcode:
4 [ERROR] Can’t start server: can’t create PID file: Permission
4 mysqld

!perror 13

Permission denied

And why were you too good for a timestamp again?

nice phrasing ^^

Xgc how do i fix it?

I guess the correct answer would be “I’m a stubborn git” or “I prefer to use unix hosting timestamp for everthing” or something “lame” like that

Or ‘I like my data to not be human readable’.

Or “Nothing happens faster than once a second”

You’re still going to have to go with a trigger, unless you’d like to use FROM_UNIXTIME and UNIX_TIMESTAMP with all your queries.

when accessed manually, the data is unreadable, yes
I’ll see what triggers can do
thanks for the help

It’s that or set the value during your insert with something like UNIX_TIMESTAMP(NOW())

I think UNIX_TIMESTAMP() is the unix timestamp equivalent of NOW()

Yes, or you can pass it a parameter
if you want some other time

[ERROR] /usr/libexec/mysqld: Can’t create/write to file ‘/var/run/mysqld/mysqld.pid’ (Errcode: 13)
4 [ERROR] Can’t start server: can’t create PID file: Permission

Fix the file system permissions or the user the process is running as.

im not sure how to do that
it is trying to run as root i belive

You should not do that and if so you would rather not have permission problems.

See chmod, chown or see the -u or –user options at startup of the server.

how do i type a single quote as avalue
is it like in php where u do \’

yep
or you can double the quotes, if you like. SELECT ‘doesn”t this look fun?’
but \’ is more readable in my opinion.

But \’ often needs to be written as \\’

Yeah, and in those circumstances you probably have to write ” as \’\’
So, just use ‘ and mysql_real_escape_string and be done with it.

http://rafb.net/p/vyK8Nf20.html what do you guys think about that
EXPLAIN is read type ALL
no key used, and no extra fields

Ok, so…. trying to do a query which I understand, but I want to link a key from one column of that query to another column to reference the corresponding value in that OTHER column

i was thinking that a covering index on (hits,clicks,user_amount) could be used unstead no ?

arbitrary_identifier

I need it to return that data in one query.

i think we can consider this query AS a GROUP BY `nothing` right ?

My mind is blank right now
how do you do a LIKE on multiple columns again?

Where colA LIKE ‘hdhd’ AND colB LIKE ‘jshdjsh’;

oh okay
nvm

Ok,, maybe an example of what I’m getting at – albeit wrong – can help me explain what I’m trying to do: http://pastebin.ca/657474
So I need data returned as cuid,gv_cuid,total_spent essentially.

Hi I have between 50-60 database and I want to setup replication for all of them, is there in short cuts to doing this so I have to make a dump for each database and copy it to the replication server?

dump them all at once

Use the -A option for mysqldump.
I think I want to use a subquery for this, but I am not sure.

Is the query too slow? May be the optimizer does not know the “covering index” trick.

hrmm
are there any tools to assist in setup replication
automated process ?

n8

Did you read up on it at all?
It’s not that difficult. You set up a slave, tell the slave to accept connections, then specify databases that you want to replicate.

is there a maximum password length?

what should i use instead of boolean?

Why what a ambigous question RichiH
maybe

after setting a 20-char password for root, i can not log in, any more

that’s awsome
maybe it got cut off

passwords are rather closely defined in the context of mysqld
aye, that is what i am asking

why would you make a 20 char password anyway…
make it above 8 and you’re fine.
rainbows tables rarely go past 8 chars.

CHAR(1) with Y and N ok?

Use INT(1)
0 and 1

kk
ty

Sorry, I just realized I am in the MySQL channel
haha

mainly because i will almost never type the password and having 20 is better than 8
that explains something

Well, apparently you’re wrong because an 8 letter would’ve served you better.

RichiH, password id a char(41) so should be ok but check docs
id=is

ta

usename is limited to 16 though

is there any setting that would forbid login with `mysql -u root -p`?

depends where from

and if you have the right password

whay my php.ini-recommended can work with my mysql

can’t you disable just root from everything but localhost?

The configuration file now needs a secret passphrase (blowfish_secret).
why? (mysql on ubuntu linux)

thats a phpmyadmin question
bitwise5, yes

Anyone mind helping me with this? I think the query – even though broken – conveys the direction in which I’m trying to go – http://pastebin.ca/657474

i think that’s the default setting

archivist um… so what do i have to do? :/

So I need gt_cuid,total_spent,and gv_cuid returned.

Kopcap, for blowfish no idea #phpmyadmin ask them

ok thanks

hello, I want to update a field to itself (for replication purposes)… how do I update a field to itself with the actual value?
if that makes any sense

yup as i thought
…. fixed, anyway thanks

update table set col = col;

yes, but I’m trying to fix some replicated data
so if the slave runs that query, it will still have the same incorrect data
do you see what I mean?

so you want to replace erroneous data in your slaves
how did the master and slaves get out of sync?

not too certain about that
but that’s what I’m trying to accomplish

can you start fresh? turn off replication, create fresh slave db’s, restart replication…

no there is way to much data for that

though you might want to figure out what went wrong in the first place.

I just need to fix about 2500 rows

thinking out loud – create a temp table, fill it with the 2500 rows, update the original table using those values…
no, same outcome
interesting
best ask one of the big guns around here…

if i have a set of 4 values
which can be true or false
is the best way to store them as 2 enum columns?
1 with the values and the 2nd one with 0/1 for true/false

Can anyone tell me what may be the problem while tring to create this view http://rafb.net/p/9sQRaZ71.html
i have append the error message here http://rafb.net/p/nBaCPv94.html

or should i just store true/false
then store the strings in the php script in an array
would be easier

thanks for the help, I’ve been trying something similar hmm

actually
ahh that doesnt’ make sense haha
ok so my first idea about storing 2 enum columns seems best

does mysql permit to create a view using unions ?

actually that doesn’t make sense either..
guess i’ll just do 4 seperate columns
ok i realised to do it this way..
`LookingFriends` enum(‘Friends’,”) NOT NULL default ”,
either set the value as the string i want or an empty string
that way i can call it without worrying
and it will either display nothing or the string i want

wtf are you talking about

i need to store 4 values as true or false
thinking of the most efficient way to do it
i realised i can store true as the string i want to call
and it’s faster..
then false can be just an empty string
so i won’t even need to check it

make your life simple and use a tinyint per bool, or an enum

i’m already using an enum like this

null columns are a pain in the butt as null is neither true nor false

`LookingFriends` enum(‘Friends’,”) NOT NULL default ”,

if it’s a simple on/off just use a tinyint, 0 for off, 1 for on

if i did that i would have to store the strings in php
which means extra resources of the php script to make that string array
as opposed to just calling the string from the db
also i’d have to do an if statement to check true/false etc
it’s more work.

hi, can anyone think of a way of combining distinct and groub by so that if I have a table with 5 rows that are the same in each column except 1 row has some different info I would get 2 rows instead of just 1

Anyone mind helping me with this? I think the query – even though broken – conveys the direction in which I’m trying to go – http://pastebin.ca/657474

select count(distinct(row1),row1 from table group by row1 ?

What is best to do for a user login. Option 1) Go to database and do a select id from users where user=blah and pass=blahpass. If it returns something it logs in. Option 2) Go to database do a select pass from users where user = blah and check if what it returns matchs with the pass ?

that would just return a count

SELECT DISTINCT f1, f2, f3, f4 FROM tbl; -or if you wish: SELECT DISTINCT * FROM tbl;
You don’t need group by at all for that.

Xgc what would you recommend on my question?

i did that try that and even if one row with the same id contains some different values in other columns it still returns me only 1 row
oh wait
i know what is throwing it off
im not getting what I except from select dictinct * because the row id is different for each row

That doesn’t match the question you asked.

Xgc what would you recommend on my question?

Ask a different question.

Why is the sky blue?

yeah im trying to get the question right

The answer to the question you wanted to ask is: select f2,f3,f4,f5 from tbl;
Sorry. with DISTINCT
select DISTINCT f2,f3,f4,f5 from tbl;

yes your right

Just don’t select f1 (the primary key).

in deed its what i just realized

thx

You’re welcome.

am i need to set something to field, to show that this field is foreign key
?
or just set field type like foreign key and relax?

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
See also: alter table …; create table …;

ty

whats the best way to store diferent charsets on the same database? uTF8?

that’s one charset
but it contains most characters of the world big languages so yes, use that

different charsets, or unicode strings? UTF-8 will be able to encode just about anything you need

i need to store chinese..russian…english.. all in the same database

UTF-8 should work for that, and it’s fairly well supported/recognized

yeah utf8 can do that. um chinees. not sure, there are different flavors, right?

“different charsets” is the wrong term

at least russion works just fine with utf8
as well as all west european languages
utf8 3

so, as long I use both utf8 on the website and on the database, all will work ok.
Thanks

yeah but i suggest you check the chineese things. i’m not sure if utf8 contains all those

ok
thanks

welcome

excuse me if this is a simple question, I am a mysql rookie. How would I find all names that start with, say A B and C.
I am passing it as dynamic variables, so A, B and C could change

select … FROM …. WHERE `name` like ‘A%’ or `name` like ‘B%’ or `name` like ‘C%’
^^

I was looking for something more dynamic, such as using IN. The amount of searches could change, such as a, b,c, or just a, or just b and c.

well since your original requirement states that it must START with A, B, or C, then you cannot use IN
you’ll have to build your query dynamically.

use LIKE

as I suggested
select … FROM …. WHERE `name` like ‘A%’ or `name` like ‘B%’ or `name` like ‘C%’

maybe IN still can
left(col,1) in (‘a’,'b’,'c’)

perhaps
that’s actually slower than my version.

yes, since no index can be used
another way
name = ‘a’ and name ‘d’

too greedy.

provided abc are continuos
better than the like this way

that remains to be proven, but you might be right.

hmm even with LIKE and OR , if version is before 5.0, full table scan too

hey i’m in trouble
i did a mysqldump and now it’s my db’s writes are taking too long
like
30s or so

your mysqldumb is causing slow writes?
how is that supposed to make sense?

beats me man

s/dumb/sump/

really beats me

bah

is the dump still running or completed

it’s completed

if running, then your write query may have to wait till it completes

i know kimseong
but it’s not running
has been finished since a while
i’ve restarted mysql
and my box
nothing
still takes too damn long

maybe along queue waiting already

there are no correlation between a completed mysqldump and INSERT times.

i would like to generate a formatted report of a database dump. Any quick and easy way to do that?

that highly depends on the contents of your database

its just text.

and what you want in the report

i have some text, and I would just like some sort of formatted report of the columns.
maybe html or pdf.

it depends, as kimseong said, on how you want the report to be formatted

i think msword would be difficult.

so you want to export the standard report to another format then?
is that what you’re really trying to do?

i dont know really.
i’m just learning mysql.

we gathered as much
without knowing what you’re trying to achieve, it’s hard for us to help you

i put some text in the database as now I would like some output besides select * from table;

again, you need to tell us exactly WHAT you’re expecting in the report, and what format you’d like to save the report to.
without those two critical pieces of information, we can’t help you.

ok my apologies. let me be more specific.
i have a database which contains software requirements in three fields.
(i’ll add additional fields later)

you mean one of your table contains three fields.

yess.

not the database itself.

yes. you are correct.

so you want to export the contents of that table?

the table has an ID field which is a auto integer, a text field containing “raw requirments” and a varchar field containing a reference description.

ok. Keep going.

yes, I would like to generate a report which has sections based upon the reference description.
in each section it will contain the raw text ordered by the ID field.

Can a database hold a structure as a field?

well mysqldump allows you to dump a single table, and even specify a query.
so write your query, and use mysqldump to run it, and export it to a file.

mysql_pconnect() [function.mysql-pconnect]: Lost connection to MySQL server at ‘reading initial communication packet’, system error: 111 in /var/www/potterskolen/includes/functions.php on line 253 — how do i fix this..

!perror 111

Connection refused

mysql propmt.

even easier.

ok good.

give us your current query, and we’ll help you tweak it.

ok.
when you use mysqldump, do you generate a space delimited text file which use to import into something else?

sorry, got kicked there

or can it create html?

you can export to a csv file, or use any delimiter you desire.

ok.

hey guys, this may be a dumb question; but is there an “Excel-Like” program that will let me view/alter/create databases in MySQL

see man mysqldump for more options
phpmysql

any others? a little less complex?

Can a database store a structure as a datatype(a field)? Something like struct WorldPosition { unsigned short int CellID; float x, y; };

so, i guess to get html, you use some perl/python/bash in conjunction with mysqldump.

mysqldump generates sql statements used to create databases, tables, etc., and fill them with data. You might want to look at SELECT INTO OUTFILE

that smells of bad design. You should store those in tables, and normalise them too.

normalise them?

hey threnody

I want to do it that way just cause my program will be using that struct and I figure why load each variable one at a time?

thumbs so what should i do?

(don’t start that trilug junk in here.)

12«11thumbs12» is that the correct name? google didn’t find it

err, phpmyadmin

tried that

lol?

i mean like software that is easy
i’ve used phpmyadmin before

a proper database design is the best way to go.
for instance, create a table with CellID, x, and y as columns.

okay, will do that

then you can simply insert your data in it.

yeah, it’s called Microsoft Access

but now I’m curious
is it possible?

your original idea was not possible, no.

fo real? Access is hard

Oh… okay, thanks.

i know hwo to write basic SQL, but I just want to be able to view my DBs and delete lines add lines, etc w/o having to type multiple lines

databases are not for everyone.

I missed your question.

lol
thanks for the support bro =(

mysql_pconnect() [function.mysql-pconnect]: Lost connection to MySQL server at ‘reading initial communication packet’, system error: 111 in /var/www/potterskolen/includes/functions.php on line 253 — how do i fix this..

it would appear that you lost the connection to mysql.

okey..
weird

is this error happening consistently?

yes thumbs
im trying to connect to a new server i just set up..

assuming you’re using php, you might need to tweak php.ini to prevent that
there might be a timeout value, or something similar

hmm..
thats weird..

I assume the initial connection is successful?

how can i check that?

do a connect or die line.
see ##php for more details

oh yeah thumbs… http://potterskolen.dk/ — theres a die line

so, based on that code, is it connecting successfully, initially?

Can’t connect to MySQL server on ‘82.103.140.48′ (115)

!perror 115

Operation now in progress

you’ll need to fix that
can you connect with the mysql console?

hi o/

new server? Include files? Permissions issue? Apache can read them?

thumbs over SSH i can connect, yes

if in doubt, check the mysql error log

i did select sum(if()) and worked well on windows, but linux returns null
???

so your php script is possibly misconfigured, using the wrong username/password, perhaps?
we’d need to see the full query.

hmm weird..
i thought i commented out the #bind = 127.0.0.1

look at the httpd error log, too.

but i didnt..

=] of course
where could i paste it?

paste
(silly robot)

try pastebin.ca

ok

im using php. is there a way I can return a true/false to see if a user already exists in my user table?

select * from user where (name|username) = $user, calculate number of rows returned
for the remainder, see ##php

http://pastebin.ca/657648

there’s something disturbing about your query
I can’t put my finger on it

hey guys… which joins are faster… inner joins or left joins?

is there any setting that would prohibit me looging in with `mysql -u root -p` as (unix) root from localhost?

INNER JOIN

inner is as fast or faster

not providing the right password?

nope

what error are you getting when you’re trying to log in, exactly?

unix root and mysql root are different

Access denied for user ‘root’@'localhost’ (using password: YES)

reset root

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

try that, for a moment.

aye, i am using both linux and mysql root, each in the appropriate place
i just reset it

that was … fast

I’d say

i had reset it earlier

that is quesionable.
questionable*
and you don’t need to be system root to log into mysql as mysql root.

a general question – what are people’s views on using dates as the primary key of a table. For instance, a table containing exchange rates logged once a day?

shady.
not to mention slow.

thumbs, ha! OK, why do you say that?

why would it be slow?

hey guys… im running this query… but seems to take forever… SELECT e.* FROM fw_email e LEFT JOIN fw_service s ON s.customer_id = e.aux_id INNER JOIN fw_user u ON u.person_id = s.person_id WHERE u.login = 441269540258 ORDER BY date_sent DESC, date_created DESC; …

the password is correct. i reset it several times, one time to 123, so that is definately not the case
i used linux root to rule out any port permission issues
erm, socket

thumbs, could you explain why you think it would be slow?
does anyone else have any views on this please?

I’m not trying to be argumentative. You said you changed the password, and you cannot log in using the password. Ergo… Can you stop the mysqld process, and restart with ‘–skip-grant-tables –user=root’? Can you explore the values in the mysql admin database? Perhaps manually change the
password setting? The restart the mysqld process normally?

sorry… i am at work, what is disturbing?
ah! maybe a variable. i am using this on a function

hello

sure
i have no idea why, but doing it this way works

is that the correct format for an insert … select query? http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
good news.

i am 100% positive that i did the second way listed on that page correctly, though
i mean it is extremely trivial
also, it worked for one password reset

sql gremlins.

so no-one sees any issue with using a date as a primary key for a table?

the debian postinst scripts worked just fine with the pass i could not use on the command line

not an issue, but what about possible duplicates
exchange rate per day? what about different currencies? you want to have multiple tables?

kimseong, I would use a composite key for that

then the primary key is not a date

kimseong, is that what you mean by “duplication”?

but a composite key, and this makes a difference now

kimseong, OK, I see your point, The primary key being a combination of the date and currency code

keys work much better if they’re unique. like an id column

maybe even 3 columns, src and target currency

strange

then depends to your type of queries

BayRoot, but what I was thinking was that what is the point of an id column if you can use a date. It makes an autonumber redundant

only if you know for a *fact* that you will only have one entry per day.

so neither the date format, or how it is stored, or the range that the data type can store, will cause me problems long term?
BayRoot, I am sure of that

a single id is good if you need a reference from another table, lets say a transaction that refer to that exchange rate, a composite primary key makes the reference very long

i wouldn’t, but you can… – longterm, well, you could change it, but then how much code might you have to change. too many variables.

kimseong, well, in this instance it would be a date and a 3 character code (for my specific purpose)
does that sound too long?

smaller is faster
too long for me.

not too long, date 3 bytes, 3 char 3 bytes, but having multiple col as reference complicates
if you need the reference, if no reference, not an issue

hi all
can I ask something

threnody,

ask

“I have a question”, Don’t ask: “Is anyone around?” or “Can anyone help?”. Just Ask The Question

I have problems with MySQL on FreeBSD

hi

I cant create md5 passwords which start with $1$

how can i say my mysql client that it shouldnt use sockets even if localhost

-h127.0.0.1
-h localhsot always use socket on Unix

ah

*localhost
maybe there is a protocol option,

i used only localhost alltimes

how to configure mysql to uses $1$ for md5 crypted passwords
?

: thx

select md5(‘$1$’);

my mysql passwords look like this 4c7e384c549f0c6e37ea06531a4e1184 but they must look like this $1$4c7e384c549f0c6e37ea06531a4e1184

you mean your own application password?
select concat(‘$1$’,md5(‘password’));

kimseong, threnody, BayRoot thanks for your advice about the primary key dates!

weird requirement

don’t drag me into your sick web.
;^)

anyone got a link describing character collation?

!man collation

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

thanks

!man chatset

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

!man charset

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

whats required to delete a row that has auto increment and re-insert into its place?
to keep the same auto increment id?

update instead
!man update

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

!m scooby2 replace

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

kimseong?

replace will delete it when you specify to insert the same id (assume this is the pk) update does not make sure you set every other col

thanks

hah! I was thinking of the string function.

what string function?

sorry, text function replace()

oh

Is there a way to select static values as multiple rows instead of multiple columns ?
like select 1,2,3,4,5 gives columns, but is there a way to get it as 5 rows 1 column each ?

why would you need that?
(a union would do)

doing an intersection of subquerys

sounds evil

an intersection between a subquery and 5 static values
how would a union work

select 1 union select 2 union select 3 union select 4 union select 5

ahh
mabye thats it

do you want to join on 5 static values?

yea, an intersect against static values

hmm well don’t know what you mean by intersect
whatever

like, count((select 1 number union select 2 number) intersect (select number from special_numbers))
to see how many static values were in the subquery
yea anyway what u said worked thx

so let’s stick with whatever
I’m going to sleep now anyways

holla
lata

another question – is it good or bad practice to have different information together in the same table, if they are very similar in format (ie all the columns are the same data types etc)? For example would a table that stored both bank rates and inflation rates be a good idea? (a 3 character
code would determine which was which )

no

on the one hand I have a gut feeling that it isn’t a good idea, but on the other, practically, I can’t think of a reason why not. Would you mind explaining your reasons for saying that it is not, kimseong

would you do a select with result of both types of rows in the same result?
you need an extra col to determine the type
smaller table is usually easier than bigger table

kimseong, yes, I agree that you need an extra column for the type

it’s not a good idea because you need extra columns for the type

kimseong, even though there is an extra join?

what’s the point in storing the type over and over again instead of just having the table name denote the type

and select needs more comparison when you usually only need 1 type of the rows
at one time

kimseong, that is a good point….
OK, let me give another example. There are two different ways of measuring inflation (in the UK they are called CPI and RPI, although I don’t know what the equivalent is in the US). Would you say that even this requires two tables?

that depends, and this situation is different
you might have 1 more col
not more rows

another example, a system for a high street bank that wants to keep track of its bank account rates, so it has different rates for its current account, debit account, interest account and gold account, and a new entry would be added when the rates change, so they could see the history of the
rate changes per bank account type.

this depends how you use the data, probably can put into the same table

date, rate_type, rate_percentage. It is difficult for me to think of the practical reasons why they should be split into different tables, although I know logically that classes of data should be seperated

no easy answer to that, all depends to what do you want to do with the data

any information to get me over this mental block would be appreciated

If I want to have one table with a mail user id and information and another with various domains listed under domain ids that link to the user id table, what would my best route be?
– mysql newbie

newbie-hood is good, it’s fun to learn. Read thru this intro to normalization, it’s an important part of designing databases. http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

is there a way to know the row number of a particular query without loading the entire query?
i have a score table, and need to calculate ranks that are constantly updated. and i’d love to cheat.
i have a score table, and need to calculate ranks that are constantly updated. and i’d love to cheat.

i have a users and a projects table. I want to find out when each user signed up for an account and when the user created its first project. A user can have multiple projects.
so this is what I have select users.created, projects.created from users, projects where users.id = projects.id
(created is a date field)
how do I get only the date of the earliest project for each user?

maybe order by
oh, wait
group by and min

group
yeah what he said …

what do i group?

SELECT min(created) …. group by user.id

aha

i have this problem with views and unions (any idea?) http://rafb.net/p/nBaCPv94.html

Hi

`text` is a reserved word. You will need to surround it with ` (backtick quotes)

i keep geting this erorr http://pastebin.ca/657854 when i try this query http://pastebin.ca/657854

You have a typo — BirthLo c.`Location

woo never notcied that
ohh that query dose’nt work
umm i have a table which refers to another table that has data that i need to get but the data i need is stored on serveral rows how could i retrive that data will it have to be multiple querys or can it be one?

also remove the extra () around the (SELECT…) UNION (SELECT…)
can you use a pastebin to provide a sample dataset and example result.

4 limit 10 and id is my primary key, will it always return the first 10 numbers after 4?

yeah just making it

4 ordered buy the index.

http://pastebin.ca/657867

nice i think that’s what i want thanks

Hello folks

Dont forget the bots!

mysql migration tool?

yes the MySQL Migration Toolkit works with Sybase.
It doesn’t do SPs

sps’ ?

stored procedures
I know there is someone in the MySQL Consulting group that has some program to do a partial conversion of Sybase stored procedures to MySQL stored procedures.

Where is it covered in the manual?

boo

it’s not in the MySQL Server manual.
http://dev.mysql.com/doc/migration-toolkit/en/index.html
Haris, I’ve dealt with a Sybase migration recently, There are a couple of bugs in the datatype mappings of the MT

Anyone here know if MySQL still runs terribly on FreeBSD?

Any other pitfalls/gotchas’ ?
why does it run terribly on fbsd?

TINYINT under Sybase is default UNSIGNED, in MySQL it is SIGNED. Also MONEY and SMALLMONEY types map to DECIMAL(10,0) instead of DECIMAL(19,4) and DECIMAL(10,4) respecively
May need to path some of the java code in MT to get the mappings done correctly.
uMay need to path some of the java code in MT to get the mappings done correctly./u

no ideas on my problem?

not yet, that’s ugly structure btw..
er… meant kieran491
http://bugs.mysql.com/bug.php?id=30343

ahh k

Mainly due to threading issues

yeah i did not create it (do you mean the example or how it works) its from another program

It runs, and doesn’t crash, but performance wise Linux is way on top

http://bugs.mysql.com/bug.php?id=30325
Are you talking about the poor threading FreeBSD once had? I think recent versions mostly deal with that properly.

Yeah, that’s exactly what I was referring to I’m researching it again right now, thanks!

Haris… ack… my patch for the TINYINT UNSIGNED thing is hidden…
? paste
!paste
? bot
speak!

so is it doable or am i going to have to make seperate querys?

you might be able to do something like that with a group_concat() + join.

anyone know a good free erd diagramming tool?

it’d be ugly though.
Workbench really is coming along

well i was only using join because i thoughgt that was the only way to do it so i am just wondering if theres another way to tackle it

does that run on linux?

Yes

http://dev.mysql.com/doc/migration-toolkit/en/index.html – Nothing related to sybase. Does it need to be done manually through scripting?

I know the GUI tools team is working on a new release of Workbench shortly.

is that a plugin to eclipse?

TodoInTX, oh thanks a lot sorry i ran out for a while and the pc went in stand by

might want to see if you can check it out from BK.
no, it’s stand alone.
sorry… I have guests now… bbl.

trying to move between servers, anyone know how to move information schema entries to a server with an existing mySQL installation?

mysqldump

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

tks
‘much obliged’

Would it be unusual to have over 200 tables?

not particularly, depending on circumstance
but if you have to wonder …

I’m having trouble making this abstract system. I’m thinking of using a table Types – String, String, String, etc. to tie the name of an object type with a list of types of attributes it has
then I’ll create a table for each of those types
and the records of those second tables will be instances of objects
Also, can one make a field with a type that is another Table?

one wonders if you really have 200 types, or a lot of variations of the same tyje
(ultimately a question of style)

I don’t have 200, but I could easily reach that number
defining different types of objects

is it possible to convert latin1 table to utf8?

yes
a table is not latin1
columns are
a table just has a default for columns
but that has no effect on actual data
if you alter table t mopdify column c1 varchar… charset utf8, modify column …

alright, i am trying that now

mysql will convert the data to utf8
why do you want to doo that?
do you need to store non latin1 data?
or did you put utf8 into a latin1 column?
or does your app use utf8?

this column needs to store peoples’ names of differnt countries (French, Italian, etc.). I was having trouble with Cakephp not return query results properly, so thought utf-8 might solve the problem
it is now latin1

well yes, to store utf8 it needs to be utf8
but to deal with the return cake needs to announce the connection to be utf8 as well
or mysql will try to convert to latin1
so you need to set names utf8 on connect
also php does not do multibyte chars until php6

yeah, i was just googling that one now

so i hope cake uses mbstring functions where needed
havent tried cake myself

mmm, cake.

should i choose utf_unicode_ci?

i think you are talking collations?

Woo!

yes

do you mean utf8_general_ci?

oh, didn’t see that one; will that work?

should work ok

i already have data in that table, will they be converted properly?

if the data matches the label, yes
a latin1 column will be converted to utf8
if the data in the column is latin1
if you lied to the machine it will get its revenge
charset

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

listen to the
bot

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

ok boardin. cu

hi i have tryed to query another database by using this method later on i will be geting two rows out of the database and returning it in the query results that why join has been ruled out here http://pastebin.ca/657892 what i have so fair any
ideas?

What’s wrong with your current approach?
Except that queries in UNION must have same number of columns in SELECT part

But you can SELECT null as columns, hooray \o/

ok, so what are my options when the /var/lib/mysql partition fills up?
I cant use any databases to delete records and clear up some free space

Deletion is overrated

delete usually do not free disk spave
*space

hello

hmmm
so what should I try and do?

can someone help me with a little error Ive been having?

buy a larger disk and migrate the datdir there
*datadir

You should get a bigger harddrive, make that your new /var partition, and copy all of your stuff over

mysql_connect() [function.mysql-connect]: Unknown MySQL server host ‘chrisdi_chris’ (1) in /home/chrisdi/public_html/sign_up.php on line 10
Unknown MySQL server host ‘chrisdi_chris’ (1)

thats pretty much my only option kimseong?
its 500gb

Yeah what kimseong said

ok, thanks guys. Appreciate the help

for some reason mysql doesnt let me connect

a 500GB /var partition?

or check if you have unnecessary things there, logs that are no longer required

500gb /var/lib/mysql

I will give anyone who helps me a cookie

you have so much data?

how does that sound

!perror 1

Operation not permitted

yes, 500gb of data

[1]Chris: Look in your my.cnf for skip-networking and comment it out?

[1]Chris is the hostname correct, try to ping it

ok
Ill try that

thanks again guys, im outta here

[1]Chris is the hostname correct, try to ping it

500gb of mysql data, lol damn

by hostname
do you mean the server address or what?

[1]Chris do you read the error message and try to figure out what it is trying to say

mysql says im an unknown server host
I interperate that as I dont have permissions

the server hostname is unknown not you

Unknown MySQL server host ‘chrisdi_chris’

i guess that name following is the server hostname
ping chrisdi_chris
telnet chrisdi_chris 3306

uhh
what do you mean ping chrisdi_chris
thats just the username on mysql

run it and see

I dont understand

you mean chrisdi_chris is the username ?

yes

but the message point that it is the server name

here is my error exactly as it appears in the internet window
mysql_connect() [function.mysql-connect]: Unknown MySQL server host ‘chrisdi_chris’ (1) in /home/chrisdi/public_html/sign_up.php on line 10
Unknown MySQL server host ‘chrisdi_chris’ (1)
but yes chrisdi_chris is the user

good morning

i will expect that to be the server name instead of username, it say server host, not user

1 at night gere rince so good night!

maybe you mixed up the parameter

*here
hmm
well

[1]Chris: here it is 7:40am

im using php to access and write to the page

check your php api again

Ill be back in a few I need to eyeball my code.

hi
how would a mysql server react to something like 200 new connections at the same time ?

if you have defined max_connections, not a problem

’cause I have weird things happening
they seem to be client problems
but… I just don’t get it

what does your error log on mysql say?

I have this PHP script who creates a couple hundreads pids who connect to mysql, some of those pids work fine
others… I get Access denied for user ‘tv’@'localhost’ (using password: NO)

do you use pconnect or connect?

connect
and I don’t get that error on a mysql_connect

and the user tv doesn’t have a password?

but on a UPDATE query
yes he does, and I provide it
but in the middle of the script, sometimes, I get this error

CY`TV: Don’t forget each connections needs to allocate some memory.

that error message means that you try to connect without providing a password

so I’m thinking… maybe it timeouts or something

using password NO means you did not provide a password

I assure you, I do provide a password, and again, the line from which this error comes is a mysql_query

CY`TV: that error message from the server says that this connection was without password

that should be a connect error

NO) indeicates no password was provided

I don’t know why; maybe your script is too fast or so

sometimes my pids work fine, they connect with no error and save the data they’re asked to
and I use the exact same code for each of them
ok well, I’ve just tried slowing it down
in chunks of 50 pids / 10 seconds

CY`TV: at least somewhere your script seems to “forget” to give out the password

I don’t seem to be getting these errors anymore
but where do they come from exactly ?

or at least mentioning that ther is a password

can anyone debug this line for me
i get an error at LIMIT
$result = @mysql_query(‘SELECT id, name, email, site, date, body FROM Board ORDER BY id DESC LIMIT ‘ .$first. ‘,’ .$last.’ ” ; ‘);

but why Rince ? :/

CY`TV: I don’t know why, sorry. it is your script making these connections

$result = @mysql_query(‘SELECT id, name, email, site, date, body FROM Board ORDER BY id DESC LIMIT ‘ .$first. ‘,’ .$last;

I guess for you it could be better to use pconnect instead of connect since already existing tcp-connections can be reused

$result = @mysql_query(‘SELECT id, name, email, site, date, body FROM Board ORDER BY id DESC LIMIT ‘ .$first. ‘,’ .$last);
there navets, should do the trick

thanks let me try it out
CY`TV: hey it worked great

nothing else will be changed ?

CY`TV: can you tell me what I was doing wrong so i know for next time, I dont see why my code didnt work

well

count your ‘

the end of your query was wrong
why was there a ” and a ; inside mysql_query ?

oh i see, haha my mistake, it must have happened why i was trying to edit it by accident
thanks

could it come from a lack of memory ?

CY`TV: It could – but I would first check for the connections

max_connections you mean ?

if out of connection, the error is too many connections

ok so, what does my error mean ?

it really mean no password, but i don;t know what or where it gone wrong
if you are using the same script and the throughput cause the error

yeah and… the error comes in the middle of the script
I send queries before that, apparently they work fine
and then suddenly, always on the same UPDATE query, *BAM*, Access denied

CY`TV: maybe you can check and confirm you error trapping is correct

uh, how do I do that ?

your mysql_error part
but still weird

Heya!
if I do SELECT * FROM users ORDER BY RAND() LIMIT 0,20 … will that do the same work as if I didn’t have the LIMIT on there?

Sure

except the result send back to client

Depends on what he means by “work”

maybe slightly less, stop after getting the first 20, the rest is does not need to sort

I’m just tring to get 20 random entries, is all, without the database looking through the entire table

random

http://jan.kneschke.de/projects/mysql/order-by-rand/

looks like this is a common problem..

solution in the url
i think sql server has an internal solution to this
maybe a feature request to mysql if this is common enough

I think the article is about how to improve the performance of ORDER BY RAND..

yes
using an alternative method

If i have a table like country(countryId, name, …) and then i want to define which ones are countries i ship to, would it be better to make a table shippingCountries(id, countryId) or just a flag in the country table like country(countryId, name, shipping [tinyint(1)]) ?
i have other tables which references the countries too, i.e: ShopCountries
or 3rdWorldCountries
so there will be more flags..

hmm

i know the tables appraoch would make more sense because i could expand the schema for the sub types…

I think I will do the first thing
SELECT * FROM users WHERE id = RAND() * MAX(id);
five times. Since I really only want five.
and make sure that it doesn’t give the same results back..etc.

depends to the size of your table, if very huge than 5 times is likely to be faster

yeah

please read the full article
it explains what to do, why and when

I will. Thank you

hey if anyone can help me with this line of code
echo ‘a href=”‘ . $_SERVER['PHP_SELF'] .
‘?page=’.$page.’”Next Page!/a’;
i want $page to be $page + 1 but i dont know the syntax

try #php

ok thanks

($page+1)

++$page

pnk thanks guys, i am just not use to the synatx

I have a bunch of fields that were char(1) and now are char(0). is there a quick way to turn all fields back to char(1) while retaining the default values and attributes?

ALTER

I know that but there are a lot

No other way.

any explaination why a field would convert to char(0)

hello, im having some issues with restoring a mysqldump… im going from 4.0.27 to 4.0.27
utilizing myisam and innodb
but cant seem to get around this error:

you listed the same version

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ‘default varchar(255) NOT NULL default ”,
value varchar(255)

Somebody did it manually

that is not good news

Perhaps with phpmyadmin or some other tool clicking at wrong button

that would in this case be very bad

im migrating from one machine to another

ok that is what I thought but very bad news

so i matched the sql versions to prevent errors.

There’s some non-printing char probably

could it be a bug?
in mysql

With same version you can just copy files
MySQL will never alter tables you know

salle, one is 4.0.27-standard and the other is 4.0.27