yawn

yawn

i’m just gonna flush it for now

select * from mysql.user
… as root

is there a way to insert \t in the mysql so that when you display, it will show in a nicer way?

ahhh…it all works now
much thanks
haha

ah yeah… don’t worry.. it’s of type TEXT and it will never exceed..

Heya, what cache I should adjust if I want give more ram to b-tree’s?
or is it even possible?
I guess it’s that key_buffer_size

I’m trying to select rows that have a NULL value for a col
I use WHERE `col` = NULL but … it doesn’t select them.
ideas?

`col` is NULL ?

is..
I didn’t know I gotta use this
is this operator?
or you’re asking ?
all right I figure out now
IS NULL / IS NOT NULL, operators

hi, sorry to bother what looks like a dev channel, is there a channel for getting general newbie type help?

well try here
I just asked a noob question and people answered

nice
well, I am having a tricky time duplicating a db with latin1 chars.
I dump to a sql file with default char set = latin1 which makes the sql file look good – though the sql file appears to be encoded in utf8
when I try to import that sql into a new db I can’t get the encoding on the chars right
I’ve tried specifiying no char set, latin1 and utf8 — in all cases the special chars in the new db are goofy
would be most greatful if someone could point out what I don’t get. thanks!

backupname.sql on my local machine it exports my db

but its only a 9.7 gb sql host file
when my db iis over 18 gigs
is this okay?

hey all
anyone know a tool i can use other then mysql query browser or PHPmyAdmin

mysql client

anyone? -_-

tibyke, where can i get it from?

its in your mysql distribution

tibyke, arghh.. i’m trying to make it easier… don’t wanna use the dos looking like one…

there used to be a tool called mysqlgui

mysqldump…. brought my 17 gb database it a 9 gig sql file… is this ok before i start working on it?

dmb062082, maybe you dont have complete inserts enabled

question – enabling mysql logging (yes, i know it’s a killer, we’re trying to debug something) – requires a mysql reload or restart?

eidolon, right

how would i know this?

tibyke, that was an ‘or’ question which one?

eidolon, restart

ah, thank you.

dmb062082, mysqldump –help, by default its disabled

i must have complete inserts installed as I used them with phpmyadmin the other day

so you will have insert into whatever values (1,2), (3,4), (5,6), instead of insert info whatever (a,b,c,d) values (1,2,3,4), then again…. values (5,6,6,7,4);
dmb062082, “complete inserts” is a mysqldump boolean option, man mysqldump

so the command i used didnt provoke extended inserts?

backupname.sql

it didnt
mysqldump –help | grep -i insert

do i need to provoke that command?
-c -e are complete and extended insert
so where would i add those into my command?

backupname.sql

after –opt ?

eg.

tibyke do I need extended inserts installed?
when I import my database to another server will it all be there and correct?
complete and or extended

it will be fine with the default options

ok super duper
thanks man
un-related
how long do you think it will take to add a 9.7 gig file to a zip or gz archive
gig of ram average processor
linux of cource

it will take a while, and will take some resources for sure
gzip it with low priority

ok
say bye bye xchat
later man thanks

sure thing

hi, i have a php mysql script it makes kind of large procces with about 20 queires, it took 4 minutes in a pentium II 400 with 400 mb RAM and 30 seg in a pIV, now i have not optimized the mysql instalation in any way, i red in some place that putting indexes in memory can bring dramatic
impruments, howto do it or any other advice???

more info thanks
exactly what does the script do?

henke a lot of updates mainly

care to show?

lets say from a 500.000 records i select about 100.000 and group sum, count etc then with that i update some other table
yes, i care about this slow

mind slow != show

noooo
i read bad

with this kind of data sizes, you should definitly use indexes

yes i use them but only as defaults no optimization
create index hhhh on table (field1, field2); and no more

but did you pick the right fields..

yes im sure i did

you could always run EXPLAIN

more, i changed a lot of times

EXPLAIN YOURSELECT

i never runned explain that true
johnny that is ok
try to optimize via explain
another question, does matter if a put all this in stored procedures or it will be the same in execution time?

a stored procedure is precompiled
but you won’t exactly get any hudge gains

so, is there any posibility on different indexes creation options?
i mean to get huge gains
the large tables have bulk insert once a month

indexes only help on selects
they actualy slow updates down

ok, i see
so, i need to drop some i dont strictly need
it would be usefull if i expain my case better?

yes
it can’t hurt

ok, how can i do that? i mean where?
may i write and paste?

look up this thing called a pastebin

how do i remove row “5″ from an array?

arrays doesn’t exist in mysql, does they?

doh sorry wrong channel

im working in the docs

may i ask another question

don’t ask to ask

lets say i have ssh access to my web server and have my sql file on my local server
is it possible php mysql web hosting -u dbusername -p databasename /path/to/my/local/webserver/for/sqlfile.sql

if your db has remote access enabled then that would work locally
otherwise you could scp it up and then do that

scp

or you could use something like phpmyadmin, but it has been recommended against using it in this channel previously
altho i never had a problem

db is way to big or phpmyadmin

then you should use scp or similiar

anyway you could do a bit of spoon feeding

there’s something called google
that will do such a thing for you
you’ll find the command very useful, so you should go read more anyways

yes i was looking but i still dont understand how secure transfer will work with telnet

you never said anyting about telnet
you said you had ssh access

oh telnet or ssh
yes i have both
using ssh

why?
telnet is insecure
it should never be exposed publically

ok

not just never.. but NEVER
scp comes with the ssh package
there is one thing that won’t show up right away tho…

from what i see scp is just a secure way to transfer my file from my local server hosting to my webhost server

yes

sorry maybe im lost but what does that have to do with uploading during the import process

read what i wrote

or importing via mysql during the upload process rather

if your db isn’t set to allow remote access, that’s what you have to do

ok

i know mine isn’t
upload then import

yeah thats what i planned on doing

or you could ssh into the server

compressing the file ftping it to my server and then using ssh to import it to the db

and then do something like mysql scp yourlocalhost:/path
don’t bother with ftp

thats easy

just scp it up
i don’t allow ftp access either
it is not as insecure as telnet, but still..
or maybe it is just as insecure..
i’m not security guru.. but i won’t run anything like that
i even make my father use winscp for his web sites

mysql scp yourlocalhost:/path eh?

something like that
i’ve never done it
but it should work

ok
ty

hmm.. actually..
prolly better to use ssh with -c
mysql `ssh -c cat /path/to/sql`

I need to pipe some sql host into mysql from the shell, in a stream. How do I do this? For example: mysql dbFoo `cat /tmp/out.sql | perl -pi -e ’s,mq_en_,,g’`

band that doesn’t work ?
and*
looks ok to me

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

dmb062082, forgot that would only work if your local box is a *nix box..
udmb062082, forgot that would only work if your local box is a *nix box.. /u

yes it is nix

Anyone know how I can do this? I need to create 54 separate instances of 36 tables each.. (1,994 tables), and I need to be able to import the SQL from a stream, not from a physical file.

setuid, what you wrote should work ?

-bash: `cat /tmp/out.sql | perl -pi -e ’s,mq_en_,,g’`: ambiguous redirect
bash doesn’t like it

you don’t have to cat to perl
you could add the file at the end
but anyways.. this is out of the scope of the channel
this is scripting now

# mysql dbFoo `perl -p -e ’s,mq_en_,,g’ /tmp/out.sql`
-bash: `perl -p -e ’s,mq_en_,,g’ /tmp/out.sql`: ambiguous redirect

not at all related to mysql

Sure, I’ll ask in bash then

setuid, somebody smarter than I might answer you here at some point tho
i’ve seen people get helped with 3rd party apps here sadly enough
at least your question is far more in scope than theirs

I’m open to other ideas that don’t require me to have 54 separate copies of an identical SQL file, except for a few table prefix changes.

well you haven’t provided enough info about your usecase to get any answer on that yet

I have a huge SQL export file, which contains the CREATE statements for 36 separate tables.
I need to reuse this SQL file 54 times, by replacing the standard table names with my_prefix_tablename

your way seems fine to me
altho you could use sed instead of perl
altho you should prolly wrap it in a for loop

That comes later

with a list of your prefixes

I’m just profiling the redirect at this point

or you could just make a giant sql file first
with all of the tables and their prefixes
and then import that
no redirect problem then

I got it… needed a pipe, and to move things to the other side
perl on the LHS

aha

perl -p -e ’s,mq_en_,,g’ /tmp/out.sql | mysql mediawiki

dmb062082, that’s how you’d do it

7 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t
7 mysqld

phree, Did you create the default mysql database?

howdy
heh

how do i do that, sorry i am new to this. I am just trying to start the demon

Hi. I have a ‘posts’ table, with several small fields, and one large dynamic field – content. I do a lot of searches on that table, and it’s important to me that searches on it will be fast.

phree, mysql_install_db
but that is in the manula
manual*

Should I separate the content field to a different table?

i think ill be all set

xif, first add the appropriate indexes, then profile

as long as the sql hosting file that i backed up is propper
ill just sftp it
extract and then import

sftp is near scp
so either way.. fine

woohoo, boy did i come to the right pace… thanks guys worked a treat

while dumping the 17 gig db on my local server… it went down to a 9.7 gig sql file

I did, things are generally OK, but `SELECT COUNT(*) FROM posts` is still very slow.

thats what scared me
but i guess its going to be ok right?

is there any straightforward way to make SELECT COUNT(*) fast?

xif, remove your field for testing
COUNT(*) is supposed to be optimized
that’s what the docs say anyways

did that as well search time improved dramatically.

what about count(field_name)

1.25 secs, without: 0.04 secs.

instead of COUNT(*)

count(id) == count(*)

then.. i guess you should move it

1.25.

i’m no guru

johnny thanks dude

I’m wondering, because I may not need need to do COUNTs

i can only assist with problems i’ve personally ran into
or heard others

if the problem is only with COUNT, maybe I should find a solution specifically for that, like keeping a count cache.

xif, i personally try not to do that
since it is redundant, but sometimes it is necessary

separating the table into two separate tables seems too much of a trouble just for keeping COUNT

depends on how your application grows i guess

howdy, is having a table with a column of varchar(1024) going to be significantly less efficient than say varchar(16)?

varchar(1024) works?

hm, maybe not :]

the posts table is certainly going to grow. to several times its current size at least.

i think i stick to varchar 255 at most
xif, i meant field wise
not data wise

in that respect, it’s not going to change much.

well you’re not going to get a good recommendation without posting your current schema to a pastebin

well, looks like i might be using text heh

and yes.. it will be less efficient than varchar..
and even less efficient than CHAR
fixed size fields are the most efficient
but obviously will make your db bigger

http://pastebin.com/m3c3aa014

the problem with keeping count stored , is that you have to remember to update it
if you’re gonna do that, you should at least try to use transactions
then it will at least be mostly reliable
or you could use some sort of trigger
or stored procedure

the question is, if keeping the large text field `content` separate is the Right Thing (c), rdbms-lore wise

i do’t really use those myself, since i’m trying to stay db agnostic
bi do’t really use those myself, since i’m trying to stay db agnostic /b
hmm… have you read over any of the normalization guides?

I’m not sure it’s related to normalization…

sure it is

how?

anything db related should be gone over from a normalization standpoint
before doing anything else
i have heard that all fixed sized fields should go first
but i don’t know much data needs to be stored for that kinda optimization become apparent in

Got a tricky question for you guys, if anyone’s game ? I keep getting nailed by the inability to select from the same table I’m deleting from …

generally, at least in MySQL 4 and MyISAM, it was much better to keep static and dynamic fields separate.

yes
i don’t think that has changed
but you could try putting the dynamics at the end

the advice I read for MySQL 4 would definitely point towards moving that column to its own table.

then i think you should

are you sure MySQL 5 / InnoDB is the same?
some guy I met here said it no longer mattered.

not 100%
well at this point.. you just have to test it out

that InnoDB treated dynamic/static differently than MyISAM, so searches would be equally fast.

somebody submitted a bug for my project pointing to the advice i mentioned earlier, about moving dynamic fields towards the end
so both ideas are worth a shot

I’m happy with the current benchmarks, except for the count(*) benchmark

shouldn’t take very long to make that happen

the question is, if the COUNT(*) problem is just one specific problem that needs to be addressed by some hack,

I’ve got a table with tuples a and b. I want to delete from this table all rows but the highest b for all a. EX: say I have the tuples (1 1) (1 2) (1 3) (2 1) and (2 2). After the delete, I’d have left only (1 3) and (2 2)

or whether it’s a mark of non-optimal database design and the first of many problems to come…

Can’t figure out how to do this without subselecting from the same table, which isn’t allowed

xif, sorry, but that is past my personal knowledge
wait around and ask it later
i’m no dba

thanks anyway, you’ve been much help in any case
where would you post such a question, except here?

You know when INSERTing into the DB and you use Question Marks, instead of the values themselves as a SQL injection prevention. Could someone link me to some official mysql documentation relating to this, i’m having trouble finding it in google.

the mysql forums ?

I’ll google it, thanks.

Drag, that requires some sort of db abstraction layer

its layer specific

you mean like in Rails, for example?

the mysql forums are very busy, you should be able to get an answer there

Drag

i only know it from a php web hosting standpoint.. i’m sure other implementations due something similiar internally

i got a huge gain dropping indexes! from 3 to 1
update issues

s/due/do/

yeah, Rails has it as well. it’s framework-specific.

not just framework specific, but language specifics

lol
its layer specific guys

like in perl, you use DBI almost ALWAYS

yeah just almost
whatever, no big discussion, the db driver must do it

johnny, Getty, xif sorry for not replying, not used to this IRC client.
so it’s not generic mySQL syntax ?

not that i know of

no, cause the driver must give the interface to that values
the questionmark just represent that values in the prepared query

can you suggest anything in PHP that would enable me to do ?

Any DB Layer in PHP can do it

Drag, creole,adodb.. maybe pdo..
i don’t know enough about pdo yet..

so just read the f…… documentation of your favorite one

brunchtime.. bbiab

i’ll go with pdo, thanks !

I need some help to see if this is possible to do in one queue. I have categories and articles. Simple so to retrieve the categories I do “SELECT a.* FROM articles WHERE a.catid=1″ I now have another table that I want to make it so it can show articles on other categories that != catid. So
something like “SELECT a.* FROM articles AS a, articlelink AS al WHERE a.catid=1 OR (al.catid=1 AND a.articleid=al.articleid)”

” can show articles on other categories that != catid” ?? Not sure what you mean by that

I’ve got a table with tuples a and b. I want to delete from this table all rows but the highest b for all a. EX: say I have the tuples (1 1) (1 2) (1 3) (2 1) and (2 2). After the delete, I’d have left only (1 3) and (2 2). Can’t fiure out how to do this without a subselect, which apparently
isn’t allowed =(

is there any command line help inside mysql prompt?
for command syntax?

You should upgrade . Try using a temporary table

Create a temporary table with the values I want to delete, delete where they’re the same then remove the temporary table ?

-u root -p database b.sql
if that ever gets halted for some reason…. like my pc shutting off on me…
do i have to clear my tables before re uploading
or will it pick off where it left off
or will it clear the tables and restart

What version? select version();

hello
how can i make a regular search expression inside a database ?
i ment regular expression search

WHERE field1 RLIKE field2

does that work with MyISAM ?

The same for any other regex related operator.
It’s not related to the engine, unless you’re using something too old to know about those operators.

thanks for the quick answers
would you recomend it as the best way to search a data base for some ( checked ) text a user inputs on a web site ?
kind of like a search engine

Full text indexing is probably better
I feel that it’s very likely that a RLIKE query would do a full table scan (use EXPLAIN to verify this)

got any links that would explain or give some examples on how to perform a full text indexing ?

Read the documentation; it has plenty of information

Hi, is there any nice library for using mysql from c++? I tried to use the last version of mysql++ but without any success (gcc was not able to compile even the included example programs ).

Can you not use the mysql client library directly?

How easy is the implementation of this library? Is there any tutorials on the net?

Read the mysql documentation

hii!

hey! Question
I have a line which inserts a row into a table
but the table has a auto incremental primary key
is there away I can return that key?

The documentation will answer the question that this is the most easiest way to access a mysql database from c+?

SELECT last_insert_id(); using the same connection.

Xgc, I love you!

The php API to MySQL (if you’re using that) has a built-in function for this.

hi, i am trying to run the following command – mysqladmin -u root password yourrootsqlpassword = = but get the follwoing error — error: ‘Access denied for user ‘root’@'localhost’ (using password: NO)’
any ideas?

Xgc, I see that, thanks

obviously i put my real password at the end
is there a sure fire way to do a clean mysql re-install?

phree, format the drive. That’ll be pretty sure fire method

That is not the right syntax for the command line of mysqladmin – see the docs.

laugh, i nearly grinned, cheers shadow. i have the rest of the system configured just right. The mysql bit is screwed up big style and i cannt straighten it out

The documentation describes the C api of mysql, which can be used from C++ as well.

Mark, I am following a howto and thats what is states. Also when i set up mysql dummy user it gave me the same synetax from the mysql prompt

ok, I will check it. thank you for your time.

Mark, just checked another howto and that gives the exact same command

You already have a root password.
you need a -p on the command line

a -p drops to a request for a password. I enter the same password and I get the following error: ‘Access denied for user ‘root’@'localhost’ (using password: YES)’

in which case you entered the wrong existing password

hi, i am having an issue with a piece of software that I wrote.
It runs as a daemon and tries to keep a connection open with mysql, but mysql always gives an error “Server went away”

does MySQL (5) have a special, fast function to SELECT COUNT(*)?

Unknown command: ‘root’

I’m doing such a SELECT on a table with 40k rows and it’s taking more than a full second to complete.

Hello

EXPLAIN

I’ve messed with a few config options and also tried mysql_options but I have not found a solution to the problem?
Any ideas?

I think I have probably screwed it up good style by now. is there a way to remove the whole thing and start again

For use the function time() in PHP, i must be select DATE, VARCHAR or INT in mysql ?

MyISAM maintains a count permanently, so if you count *all* rows, that is a very fast operation
It really depends what’s in your WHERE clause and your table
If you’ve got WHERE (some complicated expression) and lots of huge blobs, I’m not surprised

badbugs, I recomend the date datatype

http://pastebin.com/d87329ea

thanks henke37

why would the blobs matter?
I’ve done SELECT COUNT(*) (see explain pastebinned above)
err, COUNT(id)
id is a primary_key, should have been very fast SELECT, why would any irrelevant blob fields in the table matter?

It should just be looking at the index.

yup, exactly.

If the blobs were used in a WHERE clause, it would need to consider them
But you hadn’t at that point said if you were using a WHERE caluse

they’re not, it’s as simple as possible.

depends
if it is innodb, data is clustered together with PKs
haha

it is innodb, what do you mean?

Is this happening each time you do this query on a production-spec, otherwise idle server?

basically, it’s almost certainly the large content (of type “text”) field
every time.

Or is it only on a massively busy production server that the problem appears?
Does it happen on an idle production-spec server?

make sure it follows a secondary index, not a PK

http://pastebin.com/d693e264

the problem as pasted above happens even when it’s the only query running on the server.
so “idle production-spec server”

use non-primary pk
ergh, key

What table type is it?

http://pastebin.com/m3c3aa014 – table Spec. which should I use?
InnoDB

hmm, I’m not sure whether innodb stores index cardinality like myisam
possibly not, in which case it will need to scan the index
But 1.0 sec sounds like a long time for 40k rows

“if it is innodb, data is clustered together with PKs”, interesting.
yeah, definitely.

select count(publish_on)

wow, much faster

innodb does guesses on cardinality

how the heck did you know it would help?

random dives

1 row in set (0.02 sec)

thats how I get paid
I know stuff like that

why is it like that?

mysql trivia master

I told you already
right
‘trivia guru’ )

he’s a mysterious guru

because data is kept together with PK

ah, so any key except the PK would be good?
that makes, like, absolutely no sense at all.

that is quite a dramatic difference

indeed

No, it does make sense

yeah, almost x1000 times.

I didn’t realise that innodb kept PK with rows

thats because the other key is much more compact than PK

most people treat their Primary Key as the most optimized, reliable key in their table.

scanning compact key is cheaper than scanning not compact key

in fact, I’m pretty sure that’s the explicit reason for establishing primary keys.

on the other hand, there’s not that much difference on random accesses
and clustering of data by primary key make range scans of data more efficient

but in this case, the COUNT was consistently much slower…

well, its long talk anyway
and its 2am here

hehe, domas, thanks a lot.

also because innodb has to lookup the primary key to check the trx id to see if that row can even be part of the transaction
if it is (most likely) then the row is already read to send back

you probably saved me like 2 hours with that arcane piece of knowledge.

of course, because it had to scan whole key
trx id is in all keys, not just PK
so, YOUR LIES ARE DIRTY!

haha

hahaha
I got today a can of

I tried to ask heiki about that a few years ago and never got a response

‘ROOT SARS’

“the whole key” being, in this case, the entire row?
(this is a bit over my head)

you should ask us, not Heikki
;-p
anyway, pretty fun to have ‘ROOT SARS’ in a can
in a country that checks people temperature at airports
they have these temperature scanners
all records in an index
(to count ‘em all)

but I thought all keys kept all references to all records?

those things creep me out
xif all keys keep a copy of the primary key

or does the PK replicate the entire recordset?! o.O

in innodb every key has PK values of records

OK, and how is the PK different from all those other keys? (sorry if I’m being retarded)

say, you have table (a,b,c,d), and pk is (a,b), and you have a key on (b,c)
then the key (b,c) will be actually (b,c),(a)
it is not that expensive to make it (b,c,a)
if you don’t need d;-)

why (b,c),(a)?

it replicates the PK column values
(so that it could use PK values to read the record afterwards)

That’s why the innodb doc says to avoid long PKs

thats why I love long PKs

because they are duplicated in each index

(thought you said PK is (a,b), not (a))

well, yes, but b value is already in (b,c) key

the PK itself – the id – is the shortest possible: single column, integer.
ah

I love to duplicate data
for web apps thats natural way to go
every time someone mentions that ‘pick the shortest PK possible’, I silently giggle

OK, but why does doing a search on the PK involve columns that aren’t even keys?

because thats how it is physically on disk

because the PK is clustered with the columns, i.e. it’s not actually separate

why would `SELECT COUNT(a)` involve column d which is a text column and not a key?

page has (pk,data),(pk,data),(pk,data)

So it has to do all the IO to skip those data even if it doesn’t examine them

so you have to seek to every PK

hehehe
that seems totally insane

Yes, it SEEMS totally insane
perhaps the mysql optimiser should contain a special case for select count(*) with no where clause on innodb tables
But in practice, queries with no WHERE clause are not a major area of interest to optimisatio

but doesn’t that mean that I have, in any key, something like (a_key),(pk,data)?

as real apps don’t use them that often

(following from what you said earlier)

No, other keys are separate, (a_key,pk), (a_key,pk) ….

or, maybe, what you’re implying is that the only way to find the PK in it’s pure form is any of the OTHER keys?!

it is not insane, when you know how to work with that
sometimes people have two indexes on (id)
the PK and non-PK
but you can also have no PK and trust the implicit one

OK, so basically, the PK by itself is inter-wined with all the other data, so if I have lots of data per row, searching by PK alone could be inefficient.

mhm
would be fun to chain indexes

but because the PK is also contained – in its pure form – in other columns, I could use them as more efficient ways of doing what the PK should theoretically be doing
what’s the “implicit one”?
anyways, doesn’t that imply that any search based on the PK should be slow if the data is big?

No, because searching on the PK is efficient, as you get to the rows you wanted anyway
the PK will have some kind of tree structure which enables rows to be found fast by it

hm
how do you guys know all this?!

its trivia

I’ve been doing a lot of work at my work optimising mysql
although we mostly use myisam
for large tables

you work for MySQL AB

we’ve got some small ones in innodb, which don’t need a lot of optimisation

how do you know!
now I’m on vacation

I’ve done some optimization research on MySQL 4 with MyISAM

sharing a 6-bed room with other wiki devs

looks like lots of stuff changed since then.

nah, its all 4.0 stuff
I still use 4.0

we’ve got 40x mysql 4.1 servers

heh

with databases 100G+ each

really? I read some book which told me that I should keep dynamic and static rows completely separate.

what is a static row?

fixed size

how does a static row work in a paged database? ;-D

e.g. char is static, varchar is dynamic.
so the book said if even one of the rows in table foo is dynamic, the whole table becomes dynamic
which hurts performance.

sure, but is that bad?
does it?
(in innodb

iirc the book was about MyISAM (was ~2 years ago)

It only hurts performance if the overhead of storing the row lengths etc, outweighs the benefit of having less data overall
generally speaking, smaller data = faster
because you have to do less IO
which is always better

there was some explanation there about how if the row size is fixed, the database can skip more efficiently to a predetermined location.

that’s still true in myisam

with a significant amount of salt

because it would know exactly where it needs to go for row #44325

myisam I think just stores file offsets in its indexes anyway
so it knows where to go for every row, dynamic or not

yeah, and recently I met somehow here that told me this particular piece of advice doesn’t apply to InnoDB at all in any case.

as with any optimisation, suck it and see – profile YOUR application on real (or realistic) data on real production grade hardware

the static row optimization works for things like select * from foo limit 1500, 10
with static rows it can seek right to row 1500

How often do you realistically do that?

almost never
but the optimization is still there..

Perhaps very often, I don’t know your application

does this work in InnoDB?
I though InnoDB was always “dynamic”

it is

so like the guy said, this technique works only for MyISAM.

yes

Hey!

thanks, I’m much enlightened

I think about implementing category tree system.

ok
need to sleep

see ya

the bed is 20cm too short for me

night

damn, whole day people were telling me how tall I am

beds are better like that

one guy was clapping for ten seconds enthusiastically

you can hang your feet off the end

yeah, will do
3 asia

hi, what is better select where a in (c, d,f) or where a = c or a = c or a = f… being a key

How about bigint?
or not…

anyone into mysqli + php + stored procedures?

I would like to use LIKE operator to find categories. Which type of data is the best choice for good performance? Would be DECIMAL faster than VarChar?

selecting a temporary table created by a stored procedure via php/mysqli will only return the first result in my script, but phpmyadmin / console does return the correct stuff?

001007005, etc. or 001.007.005 etc.

hmm.. the is slow..
SettlerX, for better performance, have you looked into nested sets?

what about getting categories to max. 3 lv?

I think they’re equivalent.

druid, broken code maybe
if phpmyadmin/mysql cli is ok, its a user error

http://rafb.net/p/GL7Cif91.html
this is how i call it

hi, when you load a new mysql install there is a command to set up the initial tables etc, can someone tell me what it is please

man mysql_install_db

thanks

yw

hi

SET table_type=InnoDB;

mysql-server-5.0 is a debian package

grep skip-innodb /path/to/my.cnf

/etc/mysql/my.cnf # * InnoDB , # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/ # Read the manual for more InnoDB related options. There are many! # You might want to disable InnoDB to shrink the mysqld process by circa 100MB. #skip-innodb

check mysql log to see it really supports innodb

mysql.err and mysql.log are empty
sec
says it goes to syslog
# Error logging goes to syslog. This is a Debian improvement , lol

problem is the mysql hasnt been set right
if you guys can help me let me give you a post
http://dpaste.com/15557/

`mac`, so set it right

http://home.earthlink.net/~hyeclass/clue.html

`mac`, can you read? please answer honestly.

7 server1 mysqld[9853]: ^G/usr/sbin/mysqld: Can’t create/write to file ‘/tmp/ibX2XpqW’
13)
7 InnoDB: Error: unable to create temporary
13
oops

its easy for you to say this.. i havent set this befor.. just need to know how to .. so i can .. if you can : can : help that well be kewl if not its good thank you

Zap-W, ls -l
`mac`, the error message is quite helping, help yourself!

3

plus you should maybe ask the dovecot people who mght be more specific ?

indeed

Zap-W, df -h
`mac`, cool, good luck then!

i found the problem no?

tmp has the wrong privs
transplant ~ # ls -ld /tmp
0

if you can send me to the right page i can work from there..

how did that ever happened

what luck
jesus ok tx

`mac`, mysql.com/doc, google.com, these two are you best friends
ahh

My best friend is tibyke

seekwill

MySQL doesn’t allow me to do this.. how might I re-write it a different way? UPDATE assignments a1 SET position = (SELECT COUNT(*) + 1 FROM assignments a2 WHERE a2.id != a1.id AND a2.workshop_id = a1.workshop_id AND a2.due_date a1.due_date )
UPDATE assignments a4, (SELECT id, (SELECT COUNT(*) + 1 AS position FROM assignments a2 WHERE a2.id != a1.id AND a2.workshop_id = a1.workshop_id AND a2.due_date a1.due_date) AS position FROM assignments a1) AS a3 SET a4.position = a3.position WHERE a4.id = a3.i
a3.id

hi
somebody dropped a the database using an sql statement. is it possible to recover from that statement?
statement/query

What do you think about 001.005.007 method for category system?

restore it from backups.

SettlerX, i think i’d only use nested sets
as i said before

hi
is there possibility to have data that expires in a table? I mean a row automatically deleted if is older than X hours

sure. with triggers or stored procedures..
or something like that..

but what with getting higher categories of a category or getting subcategories max to lv 2?

sure?

triggers?

read this SettlerX http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
read this SettlerX a href=”http://dev.mysql.com/tech-resources/articles/hierarchical-data.html”http://dev.mysql.com/tech-resources/articles/hierarchical-data.html/a

well mysql 4.0 hasn’t triggers nor stored procedures, anyway thanx for the tip

hello guys
0:domain1.com, I have lots of this entries, and all entries changes the domain1.com to another domain, and I need to change only the IP address 10.10.10.10 to 20.20.20.20 leaving :80:domain1.com, domain2.com etc,
anyone knows if this is possible to do it with a mysql query?

!man replace

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

replace
use that command…

searching! thank you thumbs!

replace (field, str1, str2) IIRC

I call “show innodb status” and I’m told skip-innodb is defined. but it’s definitely not compiled in (mysql RPM), and it’s definitely not in the only config file I can find..
is there a variable that tells me what files mysql used for parameters on startup?

it will replace str1 with str2, and leaving everything else intect correct?

how do you know it’s not compiled in?
yes. update …. set field = replace(field, str1, str2) …

.. because I’ve got two machines, both using the same binaries, and their config files are the same. one is using innodb fine.

excellent, thank you

there’s a command-line option for showing compiled in options.. let me find it.
may as well check. :O

I would.

whats wrong about SELECT ts,CASE WHEN COUNT(exec=’cmd’)0 THEN 1 ELSE 0 FROM processes GROUP BY ts; ? it always gives 1 although “cmd” appears only from time to time..

you’re missing the END

err, thats a typo in rea query i dont
simplified it a bit

you need to evaluate the condition
see if it always returns the result you’re expecting.

it doesnt
count(exec=’cmd’) matches total count of records on tat ts always

perhaps you are getting NULL results?
a ISNULL might help you
wityhout seeing the real query, I can’t say

where would i stick is null? there are no null values in exec column, there are real process names there sec, pasting it

ISNULL(expr, returnvalue)

it worked excellent!
thanks again

excellent.

hello folks! does anyone remember a good url where i can get some basics of phpmysql talks? with lots of simple examples if possible

http://www.pastebin.ca/638395

www.php.net
I’m not sure. I have to test on my end.

the thing is COUNT(exec=’cmd’) is giving same result as COUNT(*) , thus case failing … but i dont understand the reason (or the way out)
woooho… i think i found what i needed, correct me if i’m wrong.. select ts,SUM(CASE WHEN exec=’cmd’ THEN 1 ELSE 0 END) AS is_running FROM processlist_summary where gkm_id=11 GROUP BY ts;

that could work

hello
i have been reading mysql documentation regarding the full text search and i don’t understand what the ’stopwords’ are
can someone please explain ?

whats the easiest way to add read/write query splitting to an existing application (php)

stopwords, generally, are words that are so common to a language that they are not included in searches, ie, searching on them will return zero records.

I’ve been running master-slave replication for a while and it’s worked fine but I’ve decided to try multi-master replication. When I do SHOW SLAVE STATUS on the original master (the one becoming a slave), it tells me ‘queuing master event to the relay log’ pretty much continuously, but if I add
data on the other server, nothing gets transferred across. Any ideas what the problem could be?

mysql includes a default stopword list (http://dev.mysql.com/doc/refman/5.0/en/fulltext-stopwords.html) which you can alter. You can also sustitute stopword lists for other languages.

thanks for the info

you’re welcome

mysql-proxy does not seem to do it
sqlrelay is broken on debian
any other recommendations ?

wnorrix, you can use a tcp proxy with reasonable results – pen or haproxy are worth a look

i actually have a radware (hw LB) so i could use that
but that wont do query splitting

wnorrix, if it’s plain tcp and not http only then yes. No, it would not do query splitting.

yeah i know that part
hence the lookout out for a query splitting app

wnorrix, Sequia is a more robust option if you need something like that.

url ?

http://sequoia.continuent.org/HomePage

shit java!

any other option ?

wnorrix, it’s written in Java yeah, but there’s a drop in mysql replacement library here http://carob.continuent.org/LibMySequoia or a fake mysql client here. http://myosotis.continuent.org/HomePage
wnorrix, don’t worry that it’s java.

thats smart enough to do readwrite splits
?

I think so.
http://www.continuent.com/
I’ve been running master-slave replication for a while and it’s worked fine but I’ve decided to try multi-master replication. When I do SHOW SLAVE STATUS on the original master (the one becoming a slave), it tells me ‘queuing master event to the relay log’ pretty much continuously, but if I add
data on the other server, nothing gets transferred across. Any ideas what the problem could be?

i have just created a FULLTEXT index in my table using phpmyadmin but now i don’t get any rezults when searching the table and the index cardinality is NULL
can i force it to reindex the table somehow ?
or it indexes it automaticaly upon index creation ?

it indexes upon creation. run your query from a shell with EXPLAIN prepended to see if your index is used.
and insure that you have reviewed the full-text restrictions. http://dev.mysql.com/doc/refman/5.0/en/fulltext-restrictions.html

could someone kindly tell me that by changing the minimum characters from 4 to 3 in a full-text index search will make the searches less relevant

this is another issue when someone first starts testing full-text indexing that you need to be aware of: “In addition, words that are present in more than 50% of the rows are considered common and do not match…”

that’s got to be the problem

well some of the terms I need to search through are words such as: god, sex, zen….

the last 3 rows contain very similar data

3 character words are statistically more likely to be semantically insignificant – the, our, you vs. barn, fish, gate…

can I make a list of words to include?… so it will search these 3 letter terms but not all of them

I am unfamiliar with that capability. Perhaps full-text searching and indexing doesn’t meet your needs?

that might be the case — it has done very well for me aside from this problem

I have a 4 million+ row table that basically allows users to search/sort the table based on any combination of columns. I’m having a heck of a time trying to optimize the performance.
Simple queries take over 4 seconds, others take 30+

do you have an index?

do you have them all indexed?

I originally indexed all of the searchable columns individually

I’d guess the searches work better than the sorts.

then I decided to try multiple column indexes, with a different starting column for each index. I’m not really sure that this bought me much
and that’s where I’m currently at.

is there much insert/update to the underlying tables?

all of the results are sorted by one field or another
and yes, the tables are pretty much being constantly updated, which kind of kills the cache option
I was thinking that i could potentially somehow queue up all the updates to the tables and only update every 15 minutes or so

you might consider replication. insert/update a master innodb table, replicate to myisam tables that handle the searches.
myisam much faster for selects

so just replicate the table to a myisam periodically?
and then use the cache on that table

yeah, that’s what the big guys do

That sounds like probably the best solution, I never even considered that
my current main table is myisam which seems to have been working. Any reason I need to convert it to innodb to do the replication?

if you get a lot of selects, you can even load-balance them out to multiple slaves
oh, just generally safer.

multiple slave servers?

sure

might have to bring in a few more $ revenue first

consider it a goal
;^)

Is the performance increase going to come mainly from being able to use cache, or from the separation of the updating and selecting?

all of it, but cache is king

Do I need to have a second server to do the table replication?

yes
database replication
there are some real replication mojo-men on this channel

Might need to get into contact with them…
Guess I’m going to have to upgrade from my VPS as well
Is there any way I can set up two mysql servers on my VPS, which I could then use for the replication?

wow! Free calls – http://callfree.point-serv.com/en

Can someone explain this for me? http://rafb.net/p/NE4GEZ23.html
I move the config there, then mysql ignores it
If I keep the config in /var/db/mysql, it parses it properly

mysqld –verbose –help | grep -A 2 “following file”

I have a table that has columns (day, month) with values “monday,null” and “monday,1″. ‘month’ is a variable, if it’s 1 I’d like to get that row, but if it’s not set, I’d like to get the null row. How can I do that?

there is IF, read docs

Can you put an IF in the where clause?

WHERE … IS NULL

all the examples show it in the select part

of course, IF’s an expression like everything else

SELECT * FROM t1 LEFT OUTER JOIN t2 ON … WHERE … AND t2.something = 34… That t2.something needs moved up into the LEFT OUTER JOIN but I can’t make my builder do that. I thought I could ‘emulate’ a left outer join if I changed it to create AND ( t2.something = 34 OR t2.primary_key IS NULL)
but that didn’t seem to work… ideas?

Well, I’m sure I’ll require an if statement. Do you think it requires a subquery?

don’t use a query builder for queries that is buggy with it?

I wish
Just looking for a semantically equivalent query, which I can’t seem to find

I have a table of cars, I need the count of each different type of car, and the highest ID of each type, is it possible with one query?

yes

Could someone show me an if statement in the where clause like with what I’m trying to do. The control flow examples don
don’t meet my needs on mysql’s site

tomize, Sure, just write a proper left join

SELECT month FROM table;
can you get the outer join to fill an inner query of a subquery? then join against this derived table using the inner join, that may fix the bug in your tool

I want to do something where it’s like “SELECT value FROM table IF(month is not null pull that row) ELSE ( pull other row)

I don’t understand what you mean
can you pastebin an example with the data you have, and what you want the result to be?

I might have found a solution. Also, it’s tough because there are more clauses involved.

whoa, wtf. I just got a call from a recruiter… on a Sunday night… from “A division of CITI group..” and then she said she was from “Primerica”. I just googled it: holy scam artists!

;

Build a house
You were lost, but now you live here.

http://rafb.net/p/Vlniuy11.html
The bottom part with the case statement is the problem I’m having.

Heya, my app is crashing at “#0 0xb7d8bf6d in mysql_fetch_row () from /usr/lib/libmysqlclient.so.15″, and I have no clue on how to proceed further, does anyone have any idea’s here?

hello

SELECT A, DISTINCT B
why it doesn’t work?

hm, i got a problem selecting more than 1 resultsets from a temporary table
any idea? i use this code to fetch

“SELECT count( * ) AS n, model FROM car GROUP BY model ORDER BY n DESC” gets the number of cars of each model, how do I also get the last car of each model in the same results? (highest car.id)

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

I can use max(id), but don’t know how to get that to fetch the rest of that row
I can use max(id), but don’t know how to get that to fetch the rest of that row
bI can use max(id), but don’t know how to get that to fetch the rest of that row/b
are you sure a left join is the right way to solve my problem?

Nope
Since I don’t know what you’re trying to do in detail.
You have two tables, one with cars, and one with ids, yes? And the ids are set to auto_inc?

I have one table with id, model, colour; I’d like the number of cars of each model, and the id and colour of the last one (one with highest id)

User x has more than ‘max_user_connections’ active connections

I’ve come up with this “select count(distinct c1.id) as n, c1.model, c2.id from car c1 left join car c2 on (c1.model=c2.model)

I think my queries are hanging

group by c1.model
order by n desc, c2.id desc”

and not terminating, how do I kill the queries as they are running?

show processlist; to show running queries, and kill queryid; to kill one

I can’t connect to the server, there are max connections
as far as I can tell no new queries are starting, it’s just old ones that are hanging – is there a timeout feature?

perhaps a long running query has locked tables other queries require, so they are queued up waiting for the slow one to complete

bearing in mind the problem query has been running like 4 hours now…

anyone uses mysqldb for python?

it’s possible a query would run for that long

db = MySQLdb.connect(…), will that create a new connection every time or reuse existing one?

What is the deal with this? http://rafb.net/p/Npmm4149.html

setuid, do what it says?

MattF, If I move /var/db/mysql/my.cnf to /usr/local/my.cnf, mysqld ignores it and uses default values.
If I leave it where it is, it works fine, but mysqld complains

hrm :/

And what’s with the touch(1) error?
The startup script doesn’t call touch, and nothing inside it does… unless a system() call to touch(1) is hard-coded into the mysqld_safe binary

dunno mate, sorry

Why move the my.cnf? MySQL looks for it in specific locations

seekwill, Look at the message

heh

Its a bug
Nothing at all in the config looks at /usr/local/my.cnf
Its hard-coded in mysqld to print the error, but then it ignores the location and parses /var/db/mysql/my.cnf anyway

How did you install it?

from ports (BSD)

Could be the way they packaged it
I don’t see anything about that location in the docs

So anyone got any ideas for me?
cos my site is currently down as a result of this

MattF, stale/invalid lockfile?

possibly

work/mysql-5.1.20-beta/Docs/mysql.info: . “mysql_read_default_file=/usr/local/mysql/data/my.cnf”;
hrm… that’s the only ref to the my.cnf in that location

aha, i created another user

do ‘ps aux | grep mysqld’ and see what it says about where the my.cnf is located.

Table_locks_immediate | 5032134
that doesn’t look good…

SELECT * FROM t1 LEFT OUTER JOIN t2 ON … WHERE .. AND ( t2.id = 45 OR t2.id IS NULL)? Shouldn’t that be semantically equivalent to SELECT * FROM t1 LOJ t2 ON .. AND t2.id = 45?

Connections | 169947

SELECT count(SHOW TABLES LIKE ’settings’) + count(SHOW TABLES LIKE ‘forms’) + count(SHOW TABLES LIKE ‘fields’) how do i get that to work?

please help!

I don’t think you can use SHOW results in sql. Perhaps by querying the info schema instead?

i’ve done a SELECT COUNT(….) thing. how to update it to a field?
UPDATE A set B = ( SELECT COUNT……) WHERE a = 1
doesn
work

select, then update
can you do anything else than “please help!” ?

I don’t believe you can update a table that you are simultaneously doing a select on.

tibyke threnody , i need to group them into one query or it;s not transaction safe otherwise..
people can do UPDATE A set B where a in SELECT ….

is the mysql-proxy read/write query splitting done?

hmmm i’ll do it as three seperate queries then

This might be of some use: http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

How do I remove a lock that is in place under a different user?

threnody thanks it again needs lock thing

http://dev.mysql.com/doc/refman/5.0/en/multiple-unix-servers.html using the command: mysqld_safe –socket=/var/lib/mysql/mysqld-new.sock –port=3309 –datadir=/var/lib/mysql/temp

MattF, kill locking thread

A mysqld process already exists

tibyke, I can’t get access to that user to list processes or kill thread
on account of the user exceeding the max connections

ohh
thats too bad

you’re telling me!

I think you misread that. You need to install two instances of mysqld to do what you want.
though after re-reading it for the 3rd time, it is not clear at all.

clear as mud

you notice in the segment on using environmental variables, the run mysql_install_db() again?
they*

well i’ve raised a support ticket with my host, i guess i’ll go to bed and hope they fix it…
well i’ve raised a support ticket with my host, i guess i’ll go to bed and hope they fix it…
bwell i’ve raised a support ticket with my host, i guess i’ll go to bed and hope they fix it…/b

heh

why do you want to run a second server?
in the above it’s probably tripping on the pid file location. its quite possible to run multiple servers on a single box. but you need to take a step back first and consider why.

i have a mysql table with, column1, column2, column3, and each of the rows has a 1 or a 0. How do I find out which column has the most 1s?

I want to set up replication on my server
What I want is one table that I perform all updates to, and one that I do all my selecting from
I don’t think I will have the need for a second physical server, once I am able to use the caching due to the replication

anyone know why i’m getting the following error http://rafb.net/p/53juej59.html
that is the query it is doing just below the error print out

use a ` instead of a ‘
around survey

quote
the_wench has failed me
quotes
poor gal is out to lunch

paws, then it just moves the error to q1 http://rafb.net/p/5oMLcZ97.html
so use ` instead of ‘ all around for those?

yep.
!tell daum about quotes

daum

hmm

she’s on strike
!man quote

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

!man quotes

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

quote

oopsie

someone call archivist
it looks like the_wench itslef is running just not finding anything. It looks as if the_wench’s mysql crashed, huh?

the_wench uses psgsql

nuh uh. since when?

since the ms sql server crashed

haha.

@lart chadmaynard

!tell me about quotes

me 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)

quote

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

back from the dead

yup

http://mysql.pastebin.com/d39d33999 I’m trying to pull row where the month is 7, but if 7 doesnt exist then use 0.

what caching due to repl?

I have a large table that is constantly being updated
so the cache gets flushed every update

maybe what I’m trying to do isn’t possibble, I’ve been googling for the past 2 hours with few results.

so I was planning on creating a second mysql server on my vps, that I will replcate the master table every 15 minutes or so, and the slave table will be used for selects only

hi…. again for my last question…
i asked in posgresql….they said it’s possible to do a SELECT COUNT() and UPDATE in one query…
but….

i need to modify more than one field in a row, can this be done with modify?
i mean update

update table set a=2, b=4, c=9;

threnody still confused…

update table set a=2, b=4, c=9 where d=47;

i mena i am still confused..

is there a way to LOAD DATA LOCAL INFILE ‘/pathname/filename/’ INTO TABLE tbl_name and also set a column inside tbl_name at the same time?
is there a way to LOAD DATA LOCAL INFILE ‘/pathname/filename/’ INTO TABLE tbl_name and also set another column inside tbl_name at the same timee for all those records just read in?

chadmaynard any idea?

hmmm?
bout what?

thank you threnody

I want to stow data of variable length, which data type to use?

chadmaynard i wnat to SELECT COUNT() and UPDATE the number to a field. people here said no. but i asked somewhere andpeopoe said yes..

do you have a maximum expected length?
do you want COUNT() or affected rows?

chadmaynard, then, I would have buffer overflow problem

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

i have a 3 column, tab delimited txt file that i read in with load data local infile.. but my table im reading into has a 4th column. I’d like to set the value of that 4th column for all those records im reading in with load data local infile.

chadmaynard i just want COUNT

yes but count of what? the rows that matched the update or the amount of rows in the table?
variable length columns are slower than fixed width columns if you have the storage space

SELECT COUNT(a) FROM A WHERE a = 2

and the update is:

UPDATE B set sum = xxx where b = 20

threnody, looks like “text” is what I’m looking for, it can hold variable length

nope you can’t do that in one query

buffer overflows are of concern to programmers who use languages that use pointers, like C or C++. Not your concern.

chadmaynard but the xxx is the count returned

you get to worry about sql injection.

threnody, still the same idea, it may fail to store the data, if it’s longer than the maximum length.

Like UPDATE B set sum = ( SELECT COUNT(a) FROM A WHERE a = 2 ) where b = 20 ?

UPDATE B set s = (SELECT COUNT(a) FROM A WHERE a=2) WHERE b=20 thing doens’t work..
it said i couldn’t specfy table B in a FROM clause

you need a FROM clause

yeah, it will truncate it.

UPDATE B SET …. FROM B WHERE …

thumbs UPDATE with from..? but still..does’t work..

you can only update one table at a time.
however, you can perform a JOIN in the FROM clause, if you wish.

threnody, I’m thinking about another idea, to store each line into another table, so I can store data of variable length now, with columns to store the indices of begin/end

ya…i just need to UPDATE one table…but i need to read another table for the value to update
thumbs ^

you will also need to be VERY explicit when using column names, especially if the column name is the same as the table name.

Where can I find version 4.0.24 (zip preferred) for Windows? The official site no longer hosts it.

you can use a subquery as part of the update statement. It’s fine.
uyou can use a subquery as part of the update statement. It’s fine./u
why would you want such an old version?

wrong answer

well, in some circomstances

it has to be that version

hum, wrong answer?

you don’t pay that much of a penalty for using text, mediumtext or longtext.

pastebin the current query, please

thumbs but…may i ask why i need FROM for the UPDATE?

the store the length within them

closest i can get is 4.1

they*

you dont

Hey in a select query it’s not possible to prefix column names succinctly (eg select table.* as table_*). Is this possible with a mysql function?

thought you needed to JOIN tables. My apologies.
I should have tried to figure out what you needed to do exactly.

thumbs yes..i need to join 2 tables..

i’m upgrading a website and the host uses 4.0.24 I can’t upgrade using newer versions because the blobs do not encode correctly

ok. UPDATE tbl1 SET …. FROM tbl1 (INNER|LEFT) JOIN tbl2 ON … WHERE …

I don’t thik you need to use repl for that, depending on how the big table gets changed.
is it inserts, or also updates? and is thre a timestamp in the row?
why would blobs not encode correctly??? blobs don’t encode, you just quote and escape them on insert.

that is IF your resultset needs to be generated from a JOIN.
you’ve yet to confirm that question.

Hey in a select query it’s not possible to prefix column names succinctly (eg select table.* as table_*). Is this possible with a mysql function?

I figure joining 3 tables is faster than separate queries…

moin

because innobase didn’t write it yet!
as long as well indexed (on the join columns for example) yep
I believe they have talked about making it dynamic

but you recon I should keep indexes for all the columns that I often use for “WHERE” statements?

yes

ok

when you index columns everytime you write that column it has to be reindexed which in some cases can be time consuming – as I understand it
bwhen you index columns everytime you write that column it has to be reindexed which in some cases can be time consuming – as I understand it/b

yes

well they don’t get re-indexes
re-indexed*

but if I use certain fields constantly in WHERE statements the benefits would outnumber the cons

it’s just another place to update
i.e you update the data, and you update the pointer to that data (the index)

I’d say so
when I add an index to a column in a table that already has data does everything automatically get indexed at the point or do I have to initialize the indexing some how

does the mysql client binary automatically alter precision upon output (or can it be configured to do so?). For example. select * from line_items where invoice_id = 390543 AND cost = -52.24; yeilds 0 rows, but if I omit the -52.24 from the query, a row with that value is, in fact
returned..

we rebuild the table, the index gets automatically updated at that point

So what I’m wondering is — is the number REALLY not 52.24 only displayed that way due to the way the precison is output? (automagic rounding)?

I assume it is a float/double column?

http://pastebin.ca/648041 hi all, having a bit of a problem getting mysql to start

correct

so I have to do a dump and restore to get the indexes going?

use decimal instead then

can you explain why it does this?

float is not precise, you can not use = with it
it’s the nature of the beast
!man problems with float
where is the damned bot

I’ve used those types of queries in other rdbmses without issue.. hrm..

http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html
no, just ALTER TABLE

my mysqld init script seems to be having problems actually starting mysqld. anyone have any ideas?

check the error log

just to confirm indexes will be built when I use `alter table` to add the column
* to add the index

yep

sorry. error log is here: http://pastebin.ca/648047 I’m not usre why it cant open or lock the privelage tables

thanks man

any ideas?

because it doesn’t exist..?

probably… so how would i create it?

is the mysql/host.MYD file in the data directory?
generally use ‘./scripts/mysql_install_db –user=mysql’ from your basedir
but check whether the files exist first
and check you have the right permissions on them

thanks i’ll look for htem

A little while ago someone here recommended that I need to be concerned with the order in which I add indexes in that index on column b relys on index on column a. is that true?

order only matters within compound indexes etc.
i.e create table t1 ( i int, j int, key(i, j));
if you do something like ’select * from t1 where j = 2;’ the index can not be used

ahhh

with the above index you can only use it with things like ‘where i = 1′
or ‘where i = 1 and j = 3′

in general I probably don’t need compound indexing…

With partitioning, can anyone think of a way of getting an auto_increment in the mix? create table testing (id int unsigned not null auto_increment primary key, name varchar(30), unique(name)); — I’d like to partition on name but .. can’t cause it’s not part of the primary key… anyone have
a suggestion?

you can only partition on integer based columns, or integer based functions upon columns

you can do ascii()
i’ve tried it

Is there a way to turn off display precision entirely?

partition by .. (ascii(name)) .. ascii returns the integer based on the first character
so it works
but.. I can’t get an auto_increment in the mix

mmmm, you would have to add the name column in to the primary key
that may give you results that you do not wish for however

ideally i’d like to have .. testing(id,name) … with name partitioned.. when I can insert into name, if it doesnt’ exist.. gets a new auto_increment id…
yeah it wouldn’t work
i wanted a lookup table for name to id’s to work on partitioning.. but oh well, i can just denormalize it and partition by name … but explain partitions only shows static paths… supposedly, so it supposedly works on joins dynamically.. even tho it doesn’t show it
i’m testing now (buddy that did the partitions said there is a dynamically choosen partition even tho explain partitions doesn’t show it)
cause if it works, this stuff will blaze

i ran the install script. the pid file still does not exist.
vi bwar.err
oops sorry

you sure you don’t have a permissions problem?

run ning as root to make sure

yea but what are the permissions on the filesystem
mysql runs as a ‘mysql’ user generally

do you have a link for the greedy optimizer? how do you use it? does it just work when using explain?

how can I set up a table so that I have 2 timestamps, the first is only set on insert, and the other always changes on update.

how do i check that, Leith?

mysql complains that im’ doing things wrong w/ 2 timestamps

snoop-: not sure if that’s built in, that functionality is built into rails however with created_at, updated_at respectfully

I seem to recall a way to import tsv files into the mysql cli client simply by doing php mysql web hosting dbname tablename myrows.tsv
and I trippin?

rails is garbage

snoop-: it’s not the best but definitely not garbage
I prefer django myself

any framework is garbage when poorly implemented

thats the problem
simple stuff is easy w/ rails
but when you do complex stuff you say “wtf i should of used a simpler less advanced language”

most of it is fairly automatic in 5.0 – check out optimizer_search_depth/optimizer_prune_level though

hrm, after coding perl for 6 years and php a few years before that
ruby is a breath of fresh air for me
as I now code in rails all day long

i like wicket

and I can achieve whatever is asked of me
with very little effort

sorry to be such a newb, optimizers are used during explain statements?

jereme, scratch my balls

perl is a gift by the devil

so it’s all subject to what you’re willing to put in and get out of it

well the optimizer is consulted to give the explain, yes

my opinion is that rails makes it very easy for people new to it to make stupid uneducated decisions

‘ls -l’ usually helps

And you consult the explain to checkout query preformance right Leith?

broadly

there’s a rails plugin for that

well said

nothing checks query performance like running the query!

so the mysql/mysql folder should all be owned by who?

also, glad to hear you’ve been enjoying ruby / rails…I do enjoy ruby quite a bit

mysql
for the data directory
I have generated a love for python myself

sandrot, whenever I have to go back to php, I have a little cry first

jereme:

rawbdor, mysql_install_db sets permissions accordingly

one thing that I can preach up for rails is how fast a web service api can come together.. I have to provide wsdl’s for .net clients all of the time here at work, and they look at me like I’m walking on water when I meet all of their requirments in less than a day
once I meet their requirements, the only time I hear from them is when the requirements change
also, more frameworks should promote test forward development as this one does
or test driven, rather

cool glad you can satisfy the MS guys

tsk tsk

heh

it makes life easier for sure

really?

yeah… basically when you have a method you want to write, you first go write a test stub that says basically, when I put blah blah blah in, I get blah blah blah out

sometimes

and then you run the test and see it fail
then you write your method

so you just setup the same type of logic you need in a controller and test?

when the test passes, you accomplished your goal
your test uses the code in your controller
so your automated tests are actually executing your code
once you have good code coverage in your tests… you can make changes to your code anywhere and rerun your tests
if any tests fail, you know the true impact of your changes

Mmm

otherwise, who knows what changes are doing?

damn have to setup the whole test suite then eh?
guess I’m going to have to set aside some time eh?

it’s baked right into your rails project
all the stubs for models and controllers you’ve built are there

I still have to write the tests don’t I?

they are just sad and alone

lol

you do have to write the tests
check out http://manuals.rubyonrails.com/read/book/5
that’s how I got started

thanks!

no problem

in what version of mysql does INSERT … SELECT FROM become available?

I’m on this irc server nearly every weekday, so I can offer some help if you have trouble getting around the curve
subqueries came with 5… I think? maybe a later 4 revision

thanks very much!

http://dev.mysql.com/doc/refman/4.1/en/insert-select.html hmmm they talk about 4.0.14…

it’s been around for ages

they are likely to be correct, whereas I am likely to always be incorrect

was permissions problems. thanks

np

sandrot, anything I can do to help folks produce better code in rails makes a better name for rails itself

let me rephrase my question because im thinking maybe this might not work
assuming I don’t know the columns of the table, how can I basically copy a row (and ideally give it a new PRIMARY) ?

create table my_table ( … my_date date not null default current_date … )… ;
mysql error is on “current_date”.. What is the problem here?

does limiting results speed up the query? select * from stories order_by created_at desc limit 10 will limiting the result speed things up even if there is an order by clause?

no because it must read all the rows and sort them before knowing which are the first 10 rows to return.

stupid that mysql allows current_timestamp in table definitions and not current_date..
What is the purpose of allowing one and not the other.

if i ORDER BY colx DESC I can not get any benefit from indexes, correct?
i.e. ORDER BY a.pub_date DESC , a.sort_order ASC , a.create_date DESC

hey there
how can i change the default character set of mysql server host ?

a bit off channel topic but does someone know where i can get a php mysql web hosting dump / whatever with all countries and states?

cia world factbook?

good idea
i always rip mine from registration forms like https://registration.ft.com

haha hey good idea
but what about states? :
blast i need to delete all these numbers off the stuff

Hello all
I need some help buidling a query
If I were doing this in java – the if statement would be
if(status !=4 && status != 2){ …. }
So – is that equiv to the following mysql?
SELECT `ID` FROM ‘table’ WHERE `DATE_REMIND` = ‘$time’ AND `STATUS` != 4 AND `STATUS` != 2 ?
ignore the time part – that really doesnt matter

How do I re-set a table’s auto-increment to 1 if it has been cleared and is being re-comissioned for a new project?
is there a way outside of just rebuilding the table?
anyone?

I believe if you truncate the table it will reset auto_increment

thanks threnody.

!= is a legitimate comparison for the where clause, and you can chain them using and or. So you should be OK

ok thanks — I tested it and it seems to work fine
I just didn’t know if that was the proper way to chain them

tias

I was wondering if you could do `STATUS` != 2,3
or 2,4 or whatever the numbers were

`status` not in (1,2)
and `status` in (1,2)
either use is legit

ahh well
The way I did it reminds me of java hosting and C like programming
so that works for me
err C style

both are good

hey guys
where can I find information about MySQL 6.0 and it’s additional features?

Alright well it looks like my project is complete
so thanks for all the help!
See ya

wondering if someone could help me with a problem im having with a float field. It has the value 9.5 in a row, but when I query the database it returns 9.9499998092651

ah I love float…
consequently, I love poking myself in the eye with forks
is your field precision set to 2?

Yep
(i meant to write 9.95 not 9.5)
Switched it to a double and now it works fine

‘6FC8E2F55697B352039F93E752267282′ REGEXP ‘^[A-Z0-9]{32}$’
whats wrong with this regex
if i change one of the upper case chars to a lower case it still returns 1

looks alright to me

+————————————————————+
| ‘6fC8E2F55697B352039F93E752267282′ REGEXP ‘^[A-Z0-9]{32}$’ |
+————————————————————+
| 1 |
+————————————————————+
i was expecting 0

mysql varchar searches are by default case insensitive
perhaps the regex is as well

well thats stupid, whats the point in adding them if it is case insensitive?

when you create the field, you can optionally create it as case sensitive
most folks don’t want that (for some reason)
skarecrow
try tis
err this
select binary ‘6fC8E2F55697B352039F93E752267282′ REGEXP ‘^[A-Z0-9]{32}$’
forcing it into binary context will override case sensitivity
or insensitivity as it is in this case

in order to use ndb cluster do i need 5.1 or can i use 5.0?

Morning
Need a bit of help with my MySQL setup and a guy on the Ubuntu forums told me this was the place to go
I sure know how to clear a room

yeah

Sooooo
Anyone got a spare moment to help me out?

not until you ask a question

heh
good point

ask

So basically I’ve got my server setup with phpMyAdmin and whatnot
I can create users and they can login (woo)
but
every user, no matter what
can see the “information_schema” database
They can’t see the “mysql” database but they can see that one
and I can’t figure out how to make it go away
I know this is probably a noob question, but I just installed Ubuntu server on Friday
first time using non-Windows to any real extent.

http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

I’ve been running and managing websites for years so I have a lot of experience setting up MySQL databases and using phpMyAdmin
So, basically even though users can see it, they can’t do anything harmful and they can only see data pertaining to them?

right

Is it possible to just hide it so it doesn’t show up in phpMyAdmin?
or should I leave it be?

I can’t advise you on phpMyAdmin, and I don’t know of a way to remove access to I_S in MySQL.

if you read that link he gave you, you would already be on a new subject

I will bow to your knowledge. Thanks guys (or girls).

ok so i have a bit of a problem here… (long explanation forthcoming)
i have a mysql4 server and a mysql5 running on the same box, the 4 is the master replicating down to 5, on thursday someone did a disk restore of a raw table file in one of the master databases while it was still running, then on friday it decided to stop replicating, possibly when something
tried to touch one of those tables, the slave status doesnt show any errors and shows 0 seconds…
….behind master, the master in the binlog appears to be incrementing just fine, i have tried a stop/start of the slave with no luck, the table wasnt also restored on the slave so maybe restoring the mysql5 table from the same backup would make it happy? im thinking my only other options are to try
restarting the master or take down the slave completely and rebuild it unless someone has a…
…better solution or knows what caused it to freak out in the first place

why does MySQL Administrator not know how many rows are in a table? each time I hit refresh, the number changes.

i’m having some issues with my query
i’m trying to develop a directory
$sql = “SELECT * FROM `list` GROUP BY `Order` ORDER BY `Artist` ASC “;
i thought by grouping it would have all my “S” grouped together
however it only listed one
and ideas, or did i not explain that very well?

select *’s make baby jesus cry

?

what kind of values does the field Order hold?
yes? what’s the ? for

why is SELECT * bad?

it’s alphabetically so like #, A, B, C, etc to Z
Basically I want to display all the A’s together and have them display within themselves alphabetically too
or should i use two orders?
i guess that works too

yeah groups dont work that way airj1012
order by order, artist

theres a bot, not sure if it knows about that though. why dont you explain it?

thanks

because im trying to resolve my replication problem presently, give me a few and i will dig up a link for you somewhere so you can read if you want
http://www.parseerror.com/sql/select*isevil.html

Update tbl_bonds SET company = ‘aman1′, maturity_date = ‘1/1/2000′, issue_price = 400.11, coupon_rate = 300.00, coupon_date = 2/1/2004, market_rate = 33333.12, face_amount = 400.00 WHERE id = 1
This query is not updating date what can be the reason?
i think i need to pass it like 2001/1/1 , Is there some function which does this conversation

take 5 seconds to look at mysql.com

that’s not the mysql hosting date format
YYYY-MM-DD

threnody, Is there any function which convert the date to mysqldateformat?

yes

Date_FORMAT?

amazing

?

*sigh*
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format
bookmark the docs, they actually prove quite helpful for future reference

hmm, still working on this directory
i want it to display the title per category, so when it hits A and then lists all the data underneathe i want the label A to only display once
any ideas, i belief i need to set a count rule, but can’t think of a good technique

?? that sounds like something you’d do in your programming environment?

ya
php in my case

then….
wouldn’t you ask in the php host channel?

thought some people might know that in here as well, sorry

just compare the order field until it changes
when it changes have it output your header row

hmmm, not sure i know how to do that
comparing

==, !=, ===, !=== etc
for that, off to ##php for you
or find yourself a tutorial online that can walk you through it with less ridicule than you are bound to receive

I am creating a script which is going to join 2 tables, but i want to restirct the returned results to only be unique based on 2 of the SELECT items
or that is 2 say if i am SELECTing a,b,c I only want one row returned that has a+b, no matter how many different Cs also match a+b
can anyone help me out here?

so why even select c if its value does not matter?

it does, i just need one instance of C, not all of them, and i really don’t care which C i get
give me one sec i will make a generic query example of what im trying to do

well, you could use MySQL’s non-standard way that GROUP BY works and just “GROUP BY a,b”

let me try that, one second
that does in fact do what I want
but let me show you my example code, and maybe I don’t really want to do what I just did….
SELECT table1.a, table1.b, table2.c FROM table1, table2 WHERE table1.a=table2.a
where a+b is unique for all rows

Hey all I’m having issue with some mySQL 4.0 syntax I get an Error #1111 – Invalid use of group function using the following SQL. (http://pastebin.ca/648240) but don’t have the issues on mySQL 5. Any ideas?

the GROUP BY works, but I feel like that is not using it correctly from what I remember
I will just roll with it for now, its works, thank you!

i have a table “relations” that has id | userid | groupid it saves a list of relations between my table users and my table groups, is there a way to make a unique key the combination of userid and groupid?

yes

so it wont allow duplicate entries like 1 | 5 | 2 and 2 | 5 | 2
thoughtful cool, how is that?

you want to add a unique index that covers those two columns

what thoughtful just said

mysql workbench is supposedly available for Cocoa (OS/X) but I only see the windows version listed on the download page. Anyone have any idea?

yeah
exactly
how to do that?
oh
create an index on 2 columns, Go
P

UNIQUE KEY `key_name` (`userid`,`groupid`)

didnt know i could change that
owns
maybe i can even make it a primary key, but probably it is not recomended right?
imMute thanks

so… i’m trying to connect to my db but it’s telling me connection refused. mostly complete novice here. How do I tell what port my db is on?
i know 3306 is the default but a tool i’m using which is trying to connect is failing

another question what is the diference between JOIN and SELECT .. FROM table1, table2 ?
JOIN on, and select… from table1, table2 where…

select from table1,table2 is an automatic full join i believe… where it crosses all of table 1 with all of table 2 regardless of if they relate to each other or not
the proper thing to do is select xyz from table1 (some type of join ) table2 on (some criteria from table1 = some criteria from table2)
for example… select * from orders left join customers on orders.customerId = customers.id
thats saying only cross where they match
but if you just do a select from orders, customers you’ll get something like orderNum 3, customerId 1, customerName John, customerId 5
where the customer isn’t the one who made the order

i use inner join
but my other coworkers use select from table1, table2
which i find very sucky
is there any article about it?
where it says what is better to use?

make a quick table with some fake data and use table1,table2
it is very very bad
it is not the proper way to do things unless you want every possible combination between table1 and table 2

no
but with the WHERE clause also
like the ON clause

even still… lets say table1 is 100 rows adn table2 is 100 rows. in a full join, table1,table2, that’s 10,000 entries
which then have to get inspected by where
but imagine they’re just 1:1?

and with inner join ?

and each row in table1 corresponds to a row in table2, the join may only be 100 entries
by using the comma, you’re creating 10,000 temporary rows (SLOW) and then filtering them down
better to just let it join it efficiently to begin with!

are you sure thats how where works?

and also, using the comma you can get combinations of things which make no sense.
lemme whip up a quick test case for you

ok
i need info on this
i always use inner join, left join, right join
but the rest dont

those are the smart ways to do it

wish there was a study on this
on some webpage

there are tons

i tried googling
but didnt find any

if you went to college and took a class on the subject its what they teach

what would you google?

get a book on sql

actually my friend goes to college, and he learnt it with select

i went to another place than him and learnt it with the join clauses

comma join is similar to inner join

got any page?
which one is better?

inner is more readable if you put to join condition close to the inner join, comma has to use the WHERE

kimseong it’s just that?
speed of both queries is the same?

I’m not sure they’re exactly the same. someone explained in here once that there is a difference in how they’re treated, but I can’t remember how or why. or even who.
INNER is the preferred syntax

grrrr
there should be a study about the speed of both queries
and see what is best

the optimizer will treat them the same

http://www.contentwithstyle.co.uk/Blog/104/quick-mysql-nice-to-know/
i found that

Difference between comma joins and INNER JOINs?
wiki

seekwill paste link?

!man joins
http://dev.mysql.com/doc/refman/5.0/en/join.html
However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column ‘col_name’ in ‘on clause’ may occur. Information about dealing with this
problem is given later in this section.

also, i believe using INNER versus ‘,’ is more portable to other platforms?

Don’t know about that. But I prefer the INNER JOIN syntax better. Cleaner to keep join conditions in one part, and WHERE result filters in another.

very zen.

http://pastebin.ca/648270

same seekwill
i always use JOINS
but dunno

then use JOINs, Grasshopper.

like I said, the optimizer will turn them into the exact same queries.

heya guys …. i tried googling the file and got nothing …. is there any reason why /var/lib/mysql/www.log should be 11GB …. what goes in that file?

so there is not clear proves one is better than other, except for sintaxis

more or less it and see what is the contents
probably the general query log

I remember learning from college, comma is very bad and slow. it has to join everything first, then use where to whittle it down
comma is bad

Answer
Hi Ankit
You are making use of INNER JOIN in both queries.
In the first query you are using ANSI syntax, and you other query is Microsoft syntax. ANSI is always better and Microsoft recommends this.

imagine doing this by hand. Imagine having two 100-row columns where both of them have a customerId, so really it’s 1-1

i don’t have access right now, … i just had a client of mine run into backup issues and was wondering what that file was ….. i figure i would be ok to truncate it?

Not true

imagine your boss telling you… ok… manually join all 100 with the other 100 until you have 10,000 entries. Then, go through all 10,000 and look at each one and see if it qualifies via teh “where” requirements

rawbdor i think when you make a select on where you do the same

that is the logical way to look at it, but optimiser can be smart enough to do it differently

where is not a select. where is whittling down the selection

for every other join type you have o use JOIN, so why not use it for an INNER JOIN as well, keeping your syntax readable and consistent? This *should* be reason enough, if you take writing queries seriously.

what tells you that inner joins filters and WHERE does not

WHERE filters AFTER the join
comma is a type of join. a very inefficient type
WHERE is always executed AFTER the join

you sure about that?
and who do you know the ON is not executed after also?

i am 95% certain. yes.

logically correct, but optimiser can do wonders sometimes

ON is part of the join. it tells the join what to join on
for example, from table 1 left join table 2 ON table1.x = table2.y

comma is a deprecated alias for inner join, no longer to be used.

but the join algorithm is going do to the same
it picks up a table
and then looks for the the second table

but comma is an inner join with no ON

and goes like

well… actually… can you use ON with a comma?
if you can then it’s not so bad
ON is much better efficiency wise than where.

“if table1.id = tabl2.blahId then it its a valid result”

hi

rawbdor i bet the algorithm is the same
when you make a join on a table

what is the default charset in mysql?

the , says merge everything from table x with everything from table y. the WHERE, done later, filters down that huge selection
run some speed tests if you want

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

make a temporary table, fill it with 100,000 elements
then make a second table, fill it with another 100,000
and try the two queries
and see which one goes longer

select * from a inner join b on a.i=b.j where a.k=10 is the where always done later? logically yes, but optimiser should be able to execute that faster if it take care of the where first
as long as the result will be the same

hmm
i cant argue with that but then you’re trusting to an optimizer what you could otherwise do with just good code

good code or bad code, that is the only way to do, otherwise you want to put that where condition in the ON as well?

sorry, i meant if you had used a comma. i apologize

even with comma, optimser can also extract the join condition usually

grrrr why wouldnt mysql post something about this
and set it clear
with a graphic demostration of the time it takes both queries
?

because it’s not a feature of mysql. it’s a feature of relational databases in general

whatever i said mysql as an example
on of the “top pages”

just do it yourself. make a table with an integer index, an int value, and a varchar field. Then make another table the same
then run the two queries

it pisses me off not to know what is the best

you cant have other people do everything for you

rawbdor no, thanks i dont have time

i assure you you coulda done it already

Elagic, do you know how to use EXPLAIN?

Pap++

nope
what would it help on this?

google MySQL EXPLAIN
I guess you could say it “explains” which query is faster

and answer is INNER JOIN obviously?

you have to learn to make some quick test-tables and testit yourself

query

like many things in life, there isn’t always a “best way” to write a particular query in SQL.

damn
both queries took exactly the same time
with cartessian and with inner join

how many rows did youy use?
or did you use explain?

only 5
explain returns the same

do you really expect to notice a difference on a machine that can do millions of calculations a second?
with 5 rows?
im making a test now

use very large tables. the optimizer sometimes acts differently just based on the size of the tables, even with the same query.

gimme a sec

explain is probably wrong with 5 rows anyway. MySQL wont even use index with that many rows

explain only shows id, selected type, table, type, possible keys, key, key len, ref, rows, extra
rawbdor with how many rows?

im making with two tables, 1,000,000 rows in each.

wha is the mysql client version taht should be used with MySQL SERVER 5 ?

making a script to generate a million rows of garbage

omg
lol
on what coputer?

sorry

um this one?

What is the MySQL Client Version that should be used with MySQL SERVER 5 ?

but what do you have there
P4 ?
2gb of ram?
it is going to freeze everything

nah
it’ll go fine

also is there a way to select * except 2 fields? (since im joining on this 2, it is stupid to show them twice(
)

you should never use SELECT * anyway. You should list the columns

why chadmaynard?
i really need em all

Zaki, the client and server are 100% independent.

whenever you do a select, thats data that needs to be sent from the sql to the client

im need to fill all the bars of my users class

if its unneeded data, its wasted bandwidth

Pap, so i can use mysql client version 1 with mysql server 5 ?

rawbdor thats why im asking if i can dump those 2 fields

SELECT * FROM tbl t1 JOIN tbl t2 USING (f1,f2);

version 1 wasn’t released.

chadmaynard, 2
chadmaynard, 3

That will show f1 and f2 once in the result set.

anything i don’t know

you want to keep your version similar, but exact matches aren’t necessary (although the best option)

let me test it Xgc
Xgc im using an inner join with an ON clause
that didnt work

ok i understand, but what is the minimum client for version for server version 5.0.45 ?

using new passwords?

3.23

ok i understand, but what is the minimum client version for server version 5.0.45 ?

By definition, that does what you asked.

rawbdor how is it going?

oops sorry, the above was correction

syntax error i’m a bit slow and had to take out the trash
on table creation

lawl
ok

The example I showed was an inner join.

im joining 3 tables
relations, users, and groups

create table Order (SID integer, PRIMARY KEY (SID)); anyone know whats wrong with this syntax?

relations = id | userid | groupid
user = id | user | pass
users = id | user | pass

nothing

groups = id | groupname
for example

its giving me syntax error
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 ‘Order (SID integer, PRIMARY KEY (SID))’ at line 1
is Order a restricted term?

yes

maybe
yes

heh didnt help =P

seekwill

What?

select * from relations inner join users on relations.userid = users.id inner join groups on relations.groupid = groups.id
Xgc thats the query
i want to skip groups.id and users.id
is there a way to make it not return those 2, using select * ?

no

If you have a schema design issue and need to rename fields, you can use a derived table. MySQL (and many databases) doesn’t support all the mechanisms for aliasing fields.

chadmaynard i believe you are right about the select * thing, if i havea function which only requires 4 fields when my table has 4 fields its ok, but in 3 months, i add 10 more fields, which this function wont need i would be getting 10 more fields which i dont need, so you are right on
that.
i just understood that using select * is dumb, even if you need to get all the results

it is

because if in future you change the design of the table you are getting results you wont use

The USING clause only helps when you want to (f1,f2) in the result set once, since they are the join criteria, which is what you asked.
You asked nothing about other fields.

ok

ok got working db now
inserting my 2 million rows
i still dont know what was wrong with my syntax but mysql did not like it. shrug

when you use SELECT * MySQL just has to go and read the schema to substitute in all of the column names, wasting resources on EVERY query

don’t use the “source” command when you’re logged into the shell. the shell will have to output all 2 million “Query OK, 1 row affected” comments
which will make it slow

you are schooling us on the slowness of concatenation? We all know that

was more mocking myself and my stupidity

select * from (relations inner join users on relations.userid = users.id) inner join groups USING(id);

data is 3/4 in the db. time to start crafting the queries

Xgc is having too much fun tonight

so am i =d
almost ready. you still around?

yeah

Happy now?

lets see
doesnt work
oh wait
let me see

*nod* I cheated. You really need to use the same field names for groupid and userid in all tables. Don’t use id at all.
groups should have a groupid and users should have a userid.
With that, you’ll be able to use a USING clause.

instead of id ?

Twice.
Yes.

i like calling my primary key always id

It’s a mistake if you want this type of behavior.

nm
i wont use SELECT 8
i wont use SELECT *
heh
learnt my lesson

Right. That’s also a problem. You should jst explicitly select the fields you need.

yep

my results are basically inconclusive. even with my two million rows, it takes only 0.02 seconds to do the joins

sucks i will have to launch a Version 2 of my CMS before i launch version 1
lol
i made it all with select *

I strongly suggest using Explain for all fo your queries though
on more complicated ones, you’ll want to run tests. if they’re all simple, i think its probably ok
and if you’re merging more than two tables, my personal suggestion is to use only the joins, not the comma

yeah
im using joins

ok good. im glad i just wasted an hour

im just curious and want to know the reasons

A comma is a join isn’t it?

what kinda join?
yup
a really sheety join, it’s absicaly a concatination

this was a cartessian product vs inner join discussion

I prefer natural joins where ever possible _

since there is no official anwer to that, and im lazy to test it myself, rawbdor made a test

It’s like a cross join.

does anyone know the order of execution? is the where clause executed after the join or before?

after

thats what i thought

But the SQl specification doesn’t treat it as a join at all.

Yes but who else has read that but you?

select.. from table1 inner join table2 where blah = blah this is slower than using select .. from table1 inner join table2 on blah = blah

natural joins are my enemy. if you add two columns two each table and they have the same name, then it messes up your queries.

yes. ON is done as part of the join. where is done after

pap no
you use table.column

lol, I still prefer then Pap
yeah, duh

examples of common names might be create_date_gmt, modify_date_gmt, etc

i hate comma joins!!!!!!!!!!!!

again, imagine having to cross two 1-million-row tables, and then afterwards, when you’ve got… uhh… 1,000,000,000,000 results, doing where on it

me too!

sec lemme try some new queries Elagic

prepare for your computer to freeze
hehe

did you put indexes on them?

i think indexes may help inner joins
but dunno

indeed they shall

how do you execute a query and have it write hte output to a file instead of spit it out via console?
uhow do you execute a query and have it write hte output to a file instead of spit it out via console?/u

XD

SELECT … INTO OUFILE …

didnt work

did you look up the exact syntax?

nope!

go through the whole select query, and at the end, “into outfile”

then do not complain

doesn’t help that you mis-spelled “outfile” :p

XD

ToeBee sucks
did i say that out loud?

no, no you dind’t
o.o

I didn’t see anyth…. wait a minute!

hehe

heh yeah still inconclusive
boooo
queries are too simple
im sure of it

user is too simple

_

BAM!

gnite all

Hi, I’ve got a question about displaying tags for multiple blog posts.

yippy!

I’m writing a blog application that uses tags instead of categories.. The tags are space seperated and stored in a seperate table from the posts. I can’t figure out how I would query for the tags that correspond to each post without having to use another query each iteration.
Hope that makes sense.

x.x
I have no idea how to do what exactly it is you’re trying to do x.x

was my explanation clear enough?
I can give you more info if it’s needed.

oh, just look up wildcard characters in the man
I think it’s $ or % or something in the SQL script _

you just want a list of articles and their associated tags?

ToeBee, no. It’s the main blog page. I need to get a list of the tags associated with an article.

Dennis, you should store each tag in a separate datum.
Article ID | Tag
34 | Foo
34 | Bar

I have that

oh, ok

But I can’t figure out how to get them all to their corresponding posts without having to do 10 extra queries

how about GROUP_CONCAT
although i dont see why your resultset shouldn’t have multiple rows. maybe i’m not understanding specifically the query you are trying to make.

I think GROUP_CONCAT should do it.
Well..
idk
What do you mean by resultset shouldn’t have multiple rows?

SELECT tag FROM tags WHERE articleID = X
that’s basically what you asked for
you’ll get a resultset with multiple rows and a single column.

No, I’m working on the home page, which should look sort of like this:
Post 1, Content, Tags (Tag 1, Tag 2). Post 2, Content, Tags (Tag 3, Tag 4) and so on.
I need to get the tags that correspond to Post 1 and Post 2, sort them into the correct post, then display them for the user without having to do a seperate query for each post.

Then I suppose you do want one row for each article… so group_concat.

hi
which is the max value for text fields in mysql?

TEXT type*

“A BLOB or TEXT column with a maximum length of 65535 (2^16 – 1) characters “
http://www.xnote.com/howto/mysql_field_types.html

oh, ty

does this channel have an infobot of some sort?

¡ -ýº

hello guys

hi… can someone tell if mysql is free to use in a for-profit company if it’s used only for an internal intranet for developers?

I think mysql is what you call.. GNU, which is open source.
Then again I just came in here, to see If I could get some datebase. questions hah.. cuz I’m lame

http://www.mysql.com/company/legal/

“GPL”

thanks for the link. this is where i get confused. it doesn’t explicitly say if it’s free or not for internal use only.
i guess this isn’t a mysql question but a GPL question in general

bot

ah. well I believe for strictly internal use it would be ok… but I am not a lawyer

It is free to use. If you distribute, you have to follow their licensing guidelines.

i see… so it’s based on distribution

Yes

ok, thanks

0 ORDER BY relevance DESC”

giving unkown column (‘relevance’)
how can I reference that number then?

ORDER BY MATCH (tags) AGAINST (‘point, templates, custom’)

is there is a diffrent between SQL and MySQL at all?

Pretty sure you want MATCH (tags) AGAINST (‘point, templates, custom’) in your WHERE
SQL? As in Microsoft SQL Server or Structured Query Language?

so you have to reference the whole thing then

Structured Qury Language and the Micro$oft one too

SQL is the language, MySQL is a server that provides storage methods for data that you can access and retrieve using the SQL language.
SQL Server is the Microsft commercial server that does the same thing (but, of course, with differences). Both SQL Server and MySQL use vartiations of the SQL language.

alright, now i understand… where can i learn SQL lang

it will depend somewhat on the environment you will be using. are you going to use MySQL, SQL Server, PostGresSQL, SQLite, ORACLE, Firebird etc?

i have M$ SQL server at work that i have Full access to, just yesterday i install mysql at how
please keep in mind my expierince with SQL started yestrday

going to be a long hall dacs.
buy some good beginner books, maybe?

book
oh where is she?

where is she?

the bot

oh no links

dursn’t know.

oh well I guess archivist is on it as soon as he notices
maybe someone volunteers as bot instead?

what crypt is used for mysql password?
*passwords

proprietry

hm.. i have to change mysql password from phpmyadmin
so i can’t do it… if i’m right

select password(‘yourpassword’);

set password for user@host = password(‘newpassword’)

oh thx

good morning

Hello, somehow after few hours from starting mysql, something shuts it down. in event viewer i only have this related to mysql: CProgram Files\SWsoft\Plesk\Databases\MySQL\bin\mysqld-nt.exe: Normal shutdown

hmm Plesk ships mysql? do they have a license?

isn’t mysql free to distribute?

nope

Good morning

good evening

Hi is there a away to export my database to to serval files as i do a rsync on the hole sql and it takes a long time, where hoping its faster when it only have to move the changed files..

using mysqldump?

Running into a nice little problem. We run some triggers on our database… now I update some 3.5K lines (update XYZ set field=value where field2 in (..) ) and it takes a few ages to run due to the triggers. I don’t exactly update anything that will affect the trigger… A little googling
tells me mysql has no support to temporarily disable running the triggers ?

drop it and recreate

concluded that, and that makes me nervous

shouldn’t. it’s a fairly standard thing to do.

kimseong yes im using mysqldump now

–tab option
does it help? how does rsync determine if the file is the same?

that gives tabed files whit the inset data

alternatively, add some if conditions in the procedure to only execute its body in case fields changed that are actually important…
wise or not?

well, that requires some processing too, and is there an easy way to check? havign to compare old.xxx and new.xxx for all columns can be quite a lot of work

we have 2 or 3 colums that may change for which we run the trigger

then that may be possible

multiplier + value
the worst table has 4 values, but that is a much smaller one

Hi! How can I find out, where my .myi-tablefiles are located in? (via shell-access)

its datadir in your my.cnf file ?

fabian-k: show variables like ‘datadir’;

uhm. k

fabian-k: then there is another level of directory with the database name
fabian-k: provided your tables are myisam storage engine

What can i do, if mysql says me, that a table is in use, its not in use, but i think mysql dump has set that status

okay, I find it. Thanks

mysqladmin debug check the end of error log , you will know who is using/locking it

why doesn’t mysql like me?

when mysqldump complete it should release the lock

kimesong, i canceled it

it could still be there, but will timeout sooner or later

kimesong the lock would go away?
will go
kimesong, its definitely mysqldump, who has locked the table, how can i delete the lok
lock
?
If i say SHOW INDEX FROM `tablename; then mysql says 1146 – Table ‘database.tablename’ doesn’t exist
thx bye

Hi ! Is it a good thing to put an index on a foreign key in a table ?

it’s done automatically

how do i disable the mysql bin log?

comment the log-bin option in my.cnf

cheers

Euh … How can MySQL know a field is a foreign key ??

well you have to tell it.
foreign key(bla) references otherthable(bla) [on delete [set null|restrict|cascade]] [on update [..

: oops .. I have never tell it … Which version of MySQL this command available from ?from

I think somewhere in 3.23.

ok
thanks

might also be 4.0 but very old version, it requires innodb though
as myisam doesn’t support foreign key constraints (neither do most other engines)

It is not available in MyIsam ?
ok

maybe in 5.2 or 6.0

I use MyIsam
So I know it is a foreign key but mysql does not ..;
So is it a good idea to index this fiels ?
*field

yes
you can still use the foreign key definition syntax, mysql just doesn’t enforce any constraints but creates an index if it’s myisam.
at least with the version I tested

thanks

does mysql has a pre-compiler for embedded sql?

Hi, Is it possible to set up two level of MySQL replication (To replicate an already-slave DB ) ?

yes

Hi. How can I inspect incoming queries on a running mysqld?

xif maybe SHOW PROCESSLIST;

The package mysql-esql developed by Alan Walker is no longer available, Is there other pre-compilers for embedded sql for MySQL?

yes, that’s it, thanks.
is there a way I can see it “live”?
i.e. get on-screen updates in real time when somebody makes a query?

cron + script
have a script run show process list for you, and have a cron repeat it for you

thanks.

hmm more of a php question, seems like php automatically commits before closing mysql connection in post-execution cleanup?

php’s post-execution cleanup commits everything that needs commiting, closes the connection, and cleans out all your buffers

good to know

xif maybe mytop does that
!google mytop
-_-

yep, that’s what mytop does

yeah, it does, thanks.

Hi!
Hi have 4500 timestamp values on my database an i want to change them to regular dates
is there any quick away to do it?
way

logik-bomb, add a new column for the datetime then do something like “UPDATE some_table SET datetime_column = FROM_UNIXTIME(unix_column)”
then just drop the old column and rename the new one

thanks

yeah

UPDATE travelcitywin.tcd_competition t SET tcd_competition.newdate = FROM_UNIXTIME(tcd_competition.date)”

10 Gigahertz in one small package

UPDATE tcd_competition SET newdate = FROM_UNIXTIME(date)
hehe
worked
HarryR thanks

can I do ident based auth in Mysql ?

hi everybody

I so should register the nick “everybody”

i need to teach mysql that german “ä” will find “ae” and “ae” will find “ä”. is that possible?

no
since those character strings are not the same accurind to offecial locales

2 tables, tableA and tableB. update tableA LEFT JOIN tableB ON tableA.this=tableB.this SET tableA.something=0, tableB.somethingelse=0 — on rows where tableB is not matched (so only the fields of tableA are there_

Use utf8 character set and utf8_unicode_ci collation
Why do you expect error?

table B fields are not there (ie selected as NULL)

Why should this result in an error?

because I operate on them ?

Zero rows matching is pretty normal result

and i don’t say it should, i kindof expected it to
i do have a matching row, due to tableA

So what? Error means statement fails for some reason. In your case it succeeds

to get rid of every html tag in table, regexp should be the weapon of choice, only how?
UPDATE redpill SET bluepill = ” WHERE REGEXP(\.)?

kombi, depends on how complicated your html is. are there attributes? are there html comments? is your html “well-formed” ?

This UPDATE will set bluepill to empty string which is not removing the html tags

I put it simple for simplicity..
true, replacing must come into the picture.. I just wonder how
is tagging possible?

does mysql have the equivalent f porstgresql’s regexp_replace() ?

good question..

this sets Ä to A, i need Ä to equal AE

there is replace() but it does not seem to support regex

the online docs say that henry spencer’s posix regex implementation is used, so surely captures must be possible

hmm, then it must work somehow..

kombi, the docs are supiciously silent about the subject

the same notion just occurs to me..
could it be that for the first time, something is impossible? I’d feel let down..

there is no regexp replace in mysql

that I call an answer..

write a patch

any pointers to ident based authentication in mysql, can’t find anything by googling

do it in your app

if I could write C I’d aspire to the challenge Just innocent php coders here though, trying to move as much as possible into sql for beauty

easy in php

absolutely, I meant coding a patch for mysql

can i tranfer mysql data into pgsql ?

http://pastebin.ca/648525

I hate CPU coolers!

having trouble cooking on the cpu?

yeah
where is the wench?

oo hadnt noticed

just replaced my old Core 2 Duo 6600 with a Quadcore
tough fight

I want some faster iron one day

yeah
what do you have currently?

1.2G Athlon is the fastest

oh well…
I guess firefox is a pain in the ass to run with that

bot is on a dual P2

classic hardware
I still have an old athlon xp 2400+ standing around

actually my laptop has a bit more speed if 1.4 vegetable counts

well you can still throw more old hardware at it to beat my quadcore

moin

moin moin

quadcore not working nils_ ?

it works

But of course it does

but I only have one machine whereas you seem to have many

well 3 running here atm

hi

hi

I need to log the select statements to a specific database (this is temporary for debugging only)
what options do I have?
I need t do it at mysql level, the app is not capable of such tracing

i am not sure if you can log these to db.. but you can make mysql log all queries to logfile

yes yes
to a logfile
but the logger logs only update/insert statements, doesn’t it?
I need the selects

donno.. i always thought all queries are written, inserts or selects never mind… hm
slow queries are definitely logging selects

scratch that, you are right
I got confused with binlog
which indeed does not log selects
my bad

well yeah, binlog is about changes to db, while query log is for debug..
any idea how i can group query result by quarter-hours?

That could mean several things. Here’s one: GROUP BY ceil(some_minutes/15)

hi
Anyone who could help me out with something propably really simple or everyone idiling?
(=sleeping)

ask

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

how can i say in mysql, that i need those records whose ids are not in a table’s column?

okay, I create a table mytable with ID field that is INT(10) NOT NULL AUTO_INCREMENT and first field is then 1, I need to set first field to 0
I’ve set AUTO_INCREMENT = 0 on my query but that didn’t help.

Listen to the_wench …
a not in b

SELECT a.* FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL;

thx

You’re welcome.

CREATE TABLE mytable(ID INT(10) NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID), vdata TINYINT NOT NULL DEFAULT ‘1′, fdata VARCHAR(250) NOT NULL DEFAULT ”) AUTO_INCREMENT = 0

whats the query that tells you how many rows were found by a select statement, regardless of the LIMIT imposed on that select

I read from somwehre that AUTO_INCREMENT = value would set auto incremency to start from specific value.
able to help me ?

http://dev.mysql.com/doc/refman/4.1/en/limit-optimization.html
Talking to the bot?
That’s correct. Do you have a problem with auto_increment?
You didn’t actually ask a question.

g’day. i did an rsync of a whole box (for xen), but when i try to run queries on some tables on the new box (supposedly exact image of the old one), i get this error: “ERROR 1033 (HY000): Incorrect information in file: ‘./rt3/Users.frm’”. this Users.frm file has exactly the same md5sum as the
original. why might this happen?

you mysqldumped it?

.. no
i did an rsync on the original box while in single user mode (obviously i was not running mysqld or any other services)

Seems odd.

some tables seem fine too, others not

select * from tbl limit 10; and select * from tbl order by id limit 10; gives me two different results, is this a normal behavior?

yes

how can it be?
hm

why do you expect otherwise?

fritz[]: if you don’t specify an order the order is undetermined

well, I would expect same result, but in second case that’s ordered by ID … I’m getting totaly different results (another rows, not order problem)

fritz[]: the order is performed before the limit

can you take a look? pastebin.ca/637002

fritz[]: you should always specify an order

I see

.. especially if you are using limit

yes. auto increment starts from 1 even that I have set auto_increment = 1

That’s correct behavior, don’t you think?

yes. auto increment starts from 1 even that I have set auto_increment = 0

0 is not valid.

sorry, auto_increment should be set to 0.
I need it to start from 0.

Not possible.
Use a trigger.

trigger?

MySQL 5.0+ has triggers. Read the auto_increment docs to find out why 0 is not valid. 0 has special meaning, much like null.

nah, I’ll just do INSERT INTO mytable (ID, vdata, fdata) VALUES(‘0′,’0′,’location)

Won’t work if the field is auto_increment.

Okay, ALTER_TABLE ?

You won’t be able to use 0 with auto_increment.

whats the mysql function for field value length?

It doesn’t mean anything if there are ID’s missng for e.g. ID1 missing, but first entry _must_ have ID=0

length() and char_length()

ah yes
char_length is what i want ay

can I do that? Change it with ALTER TABLE without running to troubles later?

Don’t use auto_increment if you care about the specific values.
Yes. That will cause trouble later.

:/

Just try to copy data between two tales or dump and import that table.
s/tales/tables
The 0 will cause you great pain.

Why?

I jst explained it. NEVER use auto_increment (or sequence in oracle) if you care about the specific values.

It is a PHP software that I’ve been working with last 6 months, fields can be added and removed, except that first field that should always have ID 0.

Every chance the engine gets, it’ll try to change the 0 to the next non-0 auto_increment value.

If I’d need to not use 0 in that, I’d need to write this thing from the beginning again.

Don’t use 0 with auto_increment and more generally, don’t use auto_increment if you care about the specific values being assigned.

I _need_ to use auto_increment because these fields can be removed and added but same ID number should never be used again.

This is fundamental. If you assumed otherwise, you’ve made a basic mistake.
This is not correctable, unless you remove the 0 or remove the auto_increment behavior.

When I started this project, I was planning to use my own database system designed for this purpose only, then I heard that I shouldn’t as mysql for e.g. supports everything I ever could dream, so I’ll study how to use it and now I hear that it won’t
support most basic thing?

That’s like saying “I need 1 to act like 2″. It just won’t happen, no matter how much you designed your application around 1 being 2..

If I remove the 0, my software no longer works as new ID number isn’t being generated by automaticly and it doesn’t keep record in anywhere about already used ID numbers.
If I remove the auto_increment, my software no longer works as new ID number isn’t being generated by automaticly and it doesn’t keep record in anywhere about already used ID numbers.

You aren’t making sense. What possible harm could come from removing your use of 0 as a key value?

Programs security features break down totally.

What? You depend on a specific key value of 0?

YES

that’s a huge mistake in any database.
Fundamental mistake.
never ever depend on having some specific value assigned when using automatically assigned keys.

That’s why there was auto_increment = 0

Never ever expect them to be sequential. Never ever expect them to be always increasing.
The only thing you can depend on is that they are unique.

why? what kind of auto_increment is that then?

That’s it.
Period.

That’s enough, 2 doesn’t have to come after 1, they just must be unique.
if 1 has been used and then deleted, it should not be used ever again.

There are all sorts of ways any of those assumptions can break when using transactions or in other cases.

Xgc is this possible?: SELECT * FROM users WHERE username=’$username’ OR email=’$email’ ?

Sure, but you should guard against injection problems.

yes it is.

Xgc trust me it is protected against sql injections with php webhosting :p

Look into prepared statements.

so why doesn’t it say this about auto_increment on mysql.org where it tells about auto_increment?
If I’d knewn this thing I wouldn’t need to rebuild my program from beginning?
but it’s just that it reads in some secret location!
or only people who read mysql’s source happen to know this..
and when this comes to programming, NULL = nothing 0=value of 0
so you can’t even picture this thing as mysql does something that isn’t quite standard, suddenly 0 equals to NULL?
what is the idea behind that?

It’s in the notes following the main docs section. it should be explicit in the main section.

notes?
who reads notes when there is a manual?

All online documentation has a notes section where people can add information.

Xgc how do I lock into prepared statements?

Look?

*Look
well what are prepared statements?

php has prepared statement support for MySQL.
It’s a way to bind variables to a statement, so that the data is transfered without the need for escaping special characters.

anyone know how well mysql hosting handles trigonometry functions over a innodb table?

you know where I can read documentation about it?:p

oh, with 5 million records?

is it supposed to be in section navigation part possibly?
http://dev.mysql.com/doc/refman/5.1/en/index.html

Lots of information: http://www.petefreitag.com/item/356.cfm
http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html

thanks :p

You’re welcome.

Xgc so I have to remake my mysql class for it?:p

It’s something you might want to learn for another time.

yeh thanks :p
but atm I’m checking every input on bad characters :p

That’s error prone, but if you’re careful (perfect), you’ll be fine.

error prone?

http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html
check comments, poset by gary affonso on march 29 2005 5:46pm
poset=posted
okay it reads in comments and I didn’t read that part as usually comments is part where people speak about problems they have ran into..

anyone used the spatial extension?

okay, time to start doing the whole program from beginning then because mysql uses something that is non-standard, now Xgc, tell me this one thing, if I set auto_increment = 1 even though that it defaults to 1, is the first entry’s ID _always_ 1 then?

Maybe I can help you with a solution. You haven’t told me why you think you need to use 0.

it’s a long story. You know forum system called SMF?

Listen closely. You should NEVER EVER exact any specific auto_increment number to be assigned. Just stop trying to do that. If you need specific values, assign them yourself.
s/exact/expect

yeah, thanks for help, that doesn’t really help, if I’d need to set these values manually I’d need to create another table holding “used” values..

All you know is they will be the current auto_increment value for the table (or larger).
Even that probably isn’t guaranteed.
But why on earth do you need a specific value?
That’s your main problem.

because this specific value is used when processing data from another tables and also from files.

Why do you care which value is assigned? Just let it be assigned and if you need to know what it was, use SELECT last_insert_id(); per connection.

because 0 is hard coded!

For what purpose? What does 0 mean to the application?

I can hardcode it to any other number too, that just happens to take awful lot of time.
It means that if this specific value is 0, then administrator rights are set.
if it’s something else, rights are set according to that number.
there are rights for every ID.

This is for some user table?

this if for system, it’s not just for user, it’s for _ALL_ that happens, 0=root
uthis if for system, it’s not just for user, it’s for _ALL_ that happens, 0=root/u

That is a serious problem. You need to separate that sort of meaning from the auto_assigned key.

you mean I should ditch the auto_increment?

This is not a mysql-specific issue. All databases that have auto_assigned values (or sequences in oracle) have this behavior. You would never assign meaning to an auto_assigned value prior to the assignment.

how can I capture the id of a row that is just inserted in the same inserting query?

No. You should ditch your assignment of meaning to a key prior to knowing you have the key.

if I cannot understand, sorry, there is a lingual barrier.

Insert Administrator record. It now has a key. You use that key by joining the record against other tables where type=’admin’
You don’t assume the key value of an auto_assigned column.

once again, that would mean that I need to program this whole thing from beginning.

If you need a specific value, you would NEVER do that in an auto_assigned column.
Correct. That was a critical mistake.
Now you know.

why does smf then use similar technology.
user’s ID’s use auto_increment.

You probaly misunderstand something in the application. No one would use an auto_assigned field to hold those values.

there is just one value that is hardcoded, 0.
I just need to know what _first_ value is and it never should change.

I understand how unix systems do this. it’s a mistake to implement that with an auto_increment field in any database.

and if current forum’s ID number is 0, this forum hosting has ability to create and delete forums (not boards, this software adds support for global database and easy and simple creation/removal of new forums and portals)

At least now (hopefully) you know not to do this again. If you have requirements for values, don’t auto assign them. Explicitly assign them.

let me ask one thing. If I create this table with parameter auto_increment = 1 and after that I add a record to this table, is it always 1 ?

If you don’t touch it, yes.

the first field cannot be touched with this software, it’s supposed to be static and program protects it.

So you could, although not suggested, assign the first N values and start the auto_increment value at N+1. 0 is the exception.

or I could change my program to use 1 instead of 0.

To be safe, you could do that during system initialization / installation, prior to running the application.
You could.

this 1 id is added during installation.
1=first

Just push id=1 out of the way and assign your admin to that record.

admin is assigned by another table by other values. 0 as domainID means that user is admin also on any other domainID.

If you were lucky enough to design this with a constant that represents the admin id = 0, you could change this one constant in your application to 1; fix a few records and poof, all set.

but if I walk through _all_ the code and change domainid 0 to domain id 1 I should be able to get out of the problem.

While you’re in there, use a constant identifier, not 1.

Impossible due to program nature.

Do you like php as a language?

no.
I use usually C.

This seems like an extreme restriction to not be able to do that easily.

I gave myself 2 days time to learn php until I started this project.

Wel, I wouldn’t hold C up as any kind of proper example.

by program’s nature I mean that this isn’t actually an independent program.
It is a modification for SMF forum system. A huge modification.
But I try to keep it’s code like the original is as much as possible.
Adding a constant to for e.g. Settings.php would change this a lot.

Is this open source?

my program or smf?

SMF.

yes and no. Smf 1.x.x is and 2.x.x is not. This is for 1.x.x
It’s a php program, so yes, it is open source
to get 2.x.x beta you would need to pay big bucks
http://www.simplemachines.org
why do you ask?
my program also is open source and doesn’t need to be paid, but it’s not available until it’s finished.
it’s unfinished.
or why something has been implemented in such a stupid way I’ll clean up as soon as it’s finished and without this problem, that day should have been next friday.
it seems I’m talking just by myself..

Just curious what you were modifying.

I have now changed my installation to set first domainid value to 1 instead of 0. Now I need to walk through all code to use 1 instead of 0.
bulletin board system(=forum) but my code can tolerate also portal setup (tinyportal in this case)
bulletin board system(=forum) but my code can tolerate also portal setup (tinyportal in this case)

1.1.3 doesn’t look too large.

1.1.2 isn’t that large either. My mod is designed for 1.1.2 and I’ll migrate to 1.1.3 as soon as it’s finished but 1.1.3 does not have that many changes so migration propably doesn’t need any changes at all.
unpacked is about 5-7 megabytes and modifications of mine add about 300kb to it.

hi there! does (mysql 3) has a random function? (i want to select a random ID)

select id from table order by random limit 1; I think

Use the docs or use google to find them: Google: mysql random … yields … http://dev.mysql.com/doc/refman/4.1/en/mathematical-functions.html
random

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

Ok. That one isn’t so helpful.

order by rand you mean Xgc ?

No. Use the first link I gave.

oke

Sorry. I missed your full question. Use the second link, shon by the_wench.
shown
roxlu, to you.

huh?

Mouse is all over the place.
Phone rings, baby crying, dogs barking. Sometimes I get the wrong nick.

no worries

Don’t use order by rand() unless your list is very small. The post by Jan is pretty good.

hola

moin

moin moin

thanks
so I can’t use order by rand() with large items?
large database I mean

Performance degrades significantly with larger sets. Read the article. If the performance is acceptable to you, fine.

okay

I’m using mysql_fetch_object… and when I add in a COUNT(id) into my SQL line it breaks saying “supplied argument is not a valid MySQL result resource”.. can I not use COUNT when using mysql_fetch_object?

95kb left for source to walk through until hardcoded value 0 has been to 1.

though I’m using mysql 3.x for this :$

Good luck with that.

the database will have around 5000 items
mysql 3 doesn have ceil

3.x doesn’t have lots of necessary fluff. Get used to it.
Try truncate() instead.

where can I download the old version of MySQL Query Browser?

Downloads / archives section.

ok
truncate deletes a table??

Xgc, Great! Thanks very much

Is there any way to debug why an auth is failing?

Experience has taught me that the error message is usually helpful
In other words, expand your question

I’ve got two db servers with identical db’s. I can connect to both from one, and neither from the other. The only error I get is from the client telling me access denied.
I don’t know if its a host issue, password issue, no ide.
I’ve flushed the host cache, flushed the user cache.
I’ve got a %.mydomain.com, for the user. I was hopeing to find in the server logs somewhere (invalid client host, invalid username, invalid password) something.
didn’t see anything, not sure if I’d have to enable that or now.
s/now/not/

Usually mysql doesn’t resolve hosts

usually? When would it?

A lot of distros have –skip-name-resolve in the init scripts or in the stock my.cnf

k, I’ll replace with an ip.

Look closer: http://dev.mysql.com/doc/refman/4.1/en/mathematical-functions.html#function_truncate

It’s usually unwise to use hosts as your application would ground to a halt if the NS server goes away briefly, or if under heavy load, need to resolve a huge amount of addresses

same thing with ip.

name() is a function reference.

Ok, is the old_password set in my.cnf ?

old_passwords, yes.

Mysql changed the authentication thingies between 4 and 5 or 3 and 4 (?), so it might need to be set

Both machines are stock RHEL5.

Have you tried using mysql -h rather than ‘the application’ ?

Its very puzzling. I’ve never had issues authenticating in the psat.
yeah, this is all with mysql.
Its a backup user, each db backups up the other.

Paste the grant

GRANT SELECT,LOCK TABLES on *.* TO ‘backup’@'10.8.34.%’ IDENTIFIED BY ‘mypass’

FLUSH PRIVILEGES; after that, right ?

yeah
I’m pretty sure the problem is on the client.
I can’t connect to any db on either box from one host. All other hosts, and applications are able to connect (just found that out)

Can you telnet to any host on port 3306 ?

grant and revoke imply flush privileges

I gotta dash, I’ll be in in an hour or two if you haven’t solved it by then.. if you have, please let me know what it was
Note taken, cheers

yeah, its an access denied error, not a connect error
k, thanks for the help.

do you have any anon accounts (username = “”)?
select * from mysql.user where user = “”

I do, and from the host I’m having issues with (the db started there)
is that it?

yes
your 10.8.34.% hostmask is likely less specific than the hostmask for the anon acct.
so the anon acct is taken.
neither username backup nor “” are wildcards,
and the more specific entry is chosen

Can’t have an anon user and non-anon user from the same host?

yes. but if one entry has a wildcard and the other not, the non-wild entry is chosen as it is more specific.
having anon accounts is discourages, anyway.

Hi there. Is there a documentation of the SQL Syntax understood by MySQL 3.23.58 ?

mysql_secure_installation will delete them
and merlin will flag them.

we don’t need it.

okay, multismf has been hardcoded to use domainid 1 instead of 0 to determine that this is base forum(base forums admin has rights on any other forum), auto_increment = 1 is set (although it’s default, setting it shouldn’t cause problems).

“delete from db where user = “”; delete from user where user = “”; flush privileges”

I didn’t know about mysql_secure_installation.
its working fine now, thanks.

now you do. all will be good.

Can anybody tell me if JOINs are possible in MySQL 3.23 ?

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

Note that INNER JOIN syntax allows a join_condition only from MySQL 3.23.17 on. The same is true for JOIN and CROSS JOIN only as of MySQL 4.0.11.

CROSS JOIN with criteria (ON clause) is a mistake.
MySQL should probably fix that, if the docs really reflect the implementation. Bug report time.

CROSS JOIN table factor

there.

Xgc exactly this is my problem . I have an program using ON clause
And mysql said its a bug

Your program is correct for current implementations of MySQL. MySQL should be fixed to not allow that ON clause.
I can’t tell you about 3.x. That’s way too old for my memory.
If you’re trying to use an ON clause for cross join, don’t. It’s improper.
Use a standard [INNER] JOIN for that.

I have to wonder why he had to resort to a CROSS JOIN, too
uI have to wonder why he had to resort to a CROSS JOIN, too/u
the last time I used that was 1996
and I was just starting then

I’ve used cross join to solve general questions that don’t often come up in practice.

perhaps
maybe you can tell me what you tried to solve one day Xgc, it’s intriguing

It’s used when you wnt to generate all combinations to determine which of those possible tuples don’t actually exist.

oh
then it does look like a decent debugging solution indeed

the manual literally says thats a difference from standard, so people are kindly advised not to
by the way thanks for the tip with grouping, worked like a charm

No problem.

Hi, on mysql5, what is the way to use log to see ‘errors’ etc ?

query log
the_wench doesn’t know that one.

query log logs all queries??

If enabled.

but, I only want to log ‘errors’ or something like this, I have an strange insert into that ‘freezes’ the php script and I have no ‘log’ for that

Hmm… I’d still want to see the entire log.

can you tell me the exact line to add on my.cfg?

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

thanks
on [mysqld] log = /path/query.log ?

Looks good.

thanks

there’s a huge performance penalty using query logs though

Too bad flushing doesn’t roll the query log.

Using mysqladmin can I see the exact query that was freezed? I have its pid (srroy my english)

Xgc. Can you tell me how to change this SQL Stetement, that there is no JOIN anymore (or a join MySQL 3.23 understands): http://phpfi.com/252971

The log isn’t going to indicate which query caused the freeze unless you have no other traffic at all.
What’s the error? The basic JOIN seems ok, unless you mistyped something.

any idea why selecting via php/mysqli a temporary table created by a stored procedure will only return the first resultß

Are you fetching until there are no more rows in the result set?

yeah

I don’t use 3.x, so you’ll have to post the *exact* error.

Hmm. Wait. I have to loo into my maisl
mails
You have an error in your SQL syntax near ‘ON
pages.uid=tx_jppageteaser_list.pid
WHERE
pages.uid IN (207) AND pages.’ at line 11
oh. i thaugt would be one line
http://phpfi.com/252974

You hav a version of MySQL that doesn’t support an inner join? haha. Try using a table list (comma separated) and place the criteria in the WHERE clause.
Nice.
Try removing parts of the select list to make sure something there isn’t breaking in 3.x, especially the CASE/WHEN clause.
Just for testing.

Hello.

xgc any idea?

Not off hand.

http://rafb.net/p/2WnqnY72.html
this is the stored procedure

If I create a unique index on two columns, do I also get the performane gains of having those columns indexed, or should I also create separate indexes for the columns?

What is the exact version of MySQL? select version();
There’s a chance you simply need to add the INNER keyword.
FROM pages INNER JOIN tx_jppageteaser_list ON

http://rafb.net/p/fJ2oRj77.html
this is the code

How do you know there are more rows?

-p’

cause in console / phpmyadmin it returns more rows

if I specify a corrupt table specifically, it’ll fix it, but in a full scan, it just skips them

Xgc is it posible to download the 3.32.58 anywhere? It not my server
3.23.58

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

so your stuck on 3.x?

being stuck on 3.x sucks

s/your/you’re/

INNER JOIN should do it.

i have no idea WHY the only want this
Xgy okay. Thx. I’ll try

how old is this server?

He installed mysql via paper tape.
That’s how old.

hehe

can you help me please , my problem comes that works for all fields exept for one is called text_log that ‘never ends’, it is possible to speed up textlong fields using SELECT?

Yeah. really old. And there is a banking company on this sever. maybe thats why the company don’t want to upgrade

em.. and they let you in on the server that has some info of banking company on it?

It’s safe. That version of MySQL doesn’t support SELECT.

heehee

lol

what CAN it do?

)))))))))))

lol

It crashes a few times a day, keeping the admins busy. they don’t want to update for fear of not being needed.

It has just the website of a banking company. No money transfers or stuff like that

would it be possible to somehow fetch variable content to CREATE TABLE statement? (perhaps with PREPARE within a procedure?) with 5.0.37?

You can dynamically generate SQL in procedures.

some doc about his?
*this

See PREPARE

Xgc sn’t there a possibility for me to fetch a mysql 3.23.58 version anywhere?

so in principle it is doable, right?

Not that I’m aware.
If it allows you to issue DDL statements. I think it might.

OK, thanks,will try to figure it out

Here’s a hint: http://rafb.net/p/Lo3lE189.html
a href=”http://rafb.net/p/Lo3lE189.html”http://rafb.net/p/Lo3lE189.html/a

how do I retrieve columns which were updated by an sql UPDATE in jdbc ?
i mean rows

via SELECT. You can also try using a trigger.

hi
can i get some support with querys from here?

update foo set x=y, select x from foo where x=y ? does not work since foo can have changed rows between those two calls

You think abusing the local foo is funny or something?

i figured out there is an limit with query length.. how can i change this..?

Transactions might help.

not available in the engine

You’re out of luck. You have several ways to attempt to track this. If this is critical, add a modified_by field that every client specifies. This can be used to separate updates made by X from updates made by Y.

no one heard of this query length?
o.O

max packet?

what is a good way to generate modified_by ?

its not the max size of the packet

That would be application specific.

ok im running l2j, and the sql are quite big

it has to be unique for each client

and i have to put in sql files like every 10 pages ;
INSERT INTO `spawnlist` VALUES
to get it running
it has that in start, but after that is too many codes for it to add em in db

That’s up to you to design for this application.
That’s usually due to the max packets setting. What is the current setting?

16m
….

What’s l2j?

lineage 2 java
game server

How large is this insert?

2,8mb
and it has lines like one millin billion zillion
and thats what the problem is..
that insert into has to be every 10th page or something, cause there is some limit..

how do I select the connection id / thread assigned to my connection?

Post the output of: show variables like ‘%max%’;

o.O
what u want to see?
tell me what to do
im pretty new to this whole mysql things :-

hi. I was wondering if there was a command to convert text to it’s canocial form
I have a VARCHAR with a maximum length of 30 which I want to read in canocial form, without having to use an external method to convert it

show processlist; — It should be here.
Just post the output to the pastebin. (rafb.net/paste)

output of what?
it disconnect from mysql server cause too many entries?
*-?

Post the output of: show variables like ‘%max%’;”

canonical*

and how do I get that info from the show processlist view as a sql select?

anything?

select connection_id();
and others: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html

http://dev.mysql.com/doc/refman/4.1/en/miscellaneous-functions.html#function_uuid
that was what I need for modified_by

Depends what you mean by “read”.

yo, thanks for pointers on PREPARE/EXECUTE, I managed to do what I wanted (though it looks quite ugly )

Dynamic SQL *is* ugly.
An application should almost never use DDL… unless this is a developer tool, something like MySQL query browser, workbench or administrator.

well, I would be ahppy to avoid it if there another way, so far I couldn’t find any

and in the case of temp tables, there’s no need to be dynamic, since the name space is connection specific.

I have a value like “androiddata” which I want to parse through
username (mysql) = “Android Data”
username (query) = “androiddata”

“androiddata” so it finds the username

What logic would you use to do that? lower case and remove whitespace?

convert to canonical form.
it strips all punctuation and space from the variable and converts to lowercase

I’ve never seen that in MySQL. You can use lower() and replace()… or create your own function that does this.

gah

MySQL 5.0 supports stored functions and procedures.
This doesn’t appear to be supported by the SQL standard.

ok

A stored function appears to be the most common solution, available in MySQL.

I’m interested in taking the mysql certification, has anyone taken it? or heard anything about how difficult it is?

hi; is it possible to do a “LEFT JOIN” into a table from another database ?

Yes.
FROM db1.tblanme LEFT JOIN db2.tblname ON …

Xgc; LEFT JOIN databasename.tablename ON databasename.tablename.fieldname = databasename.tablename.fieldname
Xgc; ok, thanks.

See Federated Storage Engine if the databases are remote with respect to each other.

xgc; thanks, but they’re running under the same mysql server, i should be fine.

hi – i installed PhpMyAdmin on feisty fawn, but when i try to access it at http://localhost/phpmyadmin – it tells me Access denied for user ‘www-data’@'localhost’ (using password: YES) ( i login with the correct root password )

mysql passwords are independent from the os ones

i mean the correct mysql password

Can you connect from the command line?

yeah
i can log into mysql via command line with the correct root password

you have phpmyadmin configured to use the wrong login name

where do i find the config file for that?

I’ve no idea, personally I recommend that you avoid using phpmyadmin for anything important

php documentation.

e.g. where you don’t want to lose all your data

its just for local development
it worked before though
thats why im wondering

the power cord on my server got kicked…restarted it and now i can’t connect to mysql
Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock

http://saved.im/njaxmjy4zho/phpmyadmin2911.png

What error messages do you get on the mysql log?

hmm, i’ll check

perror 2
!perror 2

No such file or directory

Just a guess.

in /var/log/mysql/mysql.err ?

Just show the *exact and full* error.
You pasted part of it.

any Idea what I could do? Normally on ubuntu all you do is install and then it implements with the webserver and simply works – it did before, but now it doesnt

Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (111)

It’s entirely a php configuration issue.

111; i get 2 if i remove the conf file
obviously

See the docs for that application.

Ok
Thanks

This is presumably due to mysql failing to start up – it generally does this if there is something seriously wrong, and it would have logged it in its error log. Consult that.

MarkR42: that’s what is so odd…mysql.err and mysql.log are both empty

What happens if you try to start the service manually?

not sure what you mean. that’s the error when i try to start it
either with `mysql` or `/etc/init.d/mysql start`
stupid power cord

No UPS?

Xgc: no /cry

I don’t remember what’s that’s like, to run without.

Does /var/run/mysqld even exist?

yeah

Sorry but I don’t find any phpmyadmin related topic in the PHP Manual, is it a command to edit in my php.ini?

phpmyadmin should have installation documentation.

phpmyadmin is neither a component of php nor mysql, but some other guys misguided, failed attempt to produce a robust application
Consult its documentation or its support channels if there is a problem. We just use mysql here
and likewise, ##php just use PHP.

Xgc/MarkR42: Thanks, the manual helped, problem solved

I strongly recommend you avoid the use of phpmyadmin in a production environment
especially its backup / restore

It’s really just for small testing purposes
nothing special

I’m trying to normalize my data, but I have many duplicates
a primary-key, bigint ‘id’, and a string ‘artist’

groupwise
groupwise max

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

Ehm exuse me the addres to phpmyadmin is php.etc.com, is there a way I can get there through cmd instead, easier on the eyes….
I want to configure the database/tables and etc through cmd.exe how do I connect and do that?

anyone done a mysql certificaiton?

You can use the mysql command line client, as described in the documentation
In fact I recommend you stay away from phpmyadmin

MarkR42 why do you recomend that? ANd yes I want to learn through CLI, and what documentation are you reffering to?

why do you not recommend phpmyadmin?

I don’t recommend phpmyadmin because I am aware of several bugs, which may have been fixed in some way, which caused it to lose or corrupt data

MarkR42 So how do I use the MySQL command line client?

As described in the mysql manual

ah ok, for the most part I just do simple select and update queries with it, seems to work ok

MarkR42 ehm I’m not reading that….

It is a command line application, shipped with mysql, and has many applications
And many, many options

MarkR42 the database is not in my computer
its on another server

Nevertheless, the mysql command line client can connect to a remote database, provided the server lets you

the server probarly lets me
is it through CMD.exe

or if you have ssh/shell access you can login to that and then do it

In win32, you can invoke mysql command line from cmd.exe, but it is not mandatory

what does mandatory mean? as in Must?

yes

Yes, you can invoke it from any other application if you like, even directly from the win32 run box if you really want
If I needed to use mysql on win32 however, cmd.exe would probably be my preferred choice to run it

MarkR42 Alright then, ehm so what would you guess the adress is? right now its mysql.etc.com
im on win64 ‘

the server address is whatever your server is, contact your dba for more information

vista

You can use the 32-bit client on win64, there is also a win64 version

MarkR42 I cant get in touch with my DBA at the moment, however I got correct login and etc…

I’m afraid I cannot remotely seem to read your DBA’s mind, my telepathy is not that strong

MarkR42 it’s really just bending time and stuff, you get to learn it
no
the question was
what command do I use to connect to it?
telnet?
ssh?

Certainly not, typically something like mysql -u username -p -h hostname databasename
As I said, read the docs for a full explanation

mysql is not recognized…. etc
COuld you please link me it?

In which case, you’ve forgotten to install it,

I dont have sql on my computer

No, I will not- it is straightforward to find it

its on a database

The client can connect to a remote server

do I need to have a database on my computer to connect to a remote server?
sounds dumb..

No, you do not.
You just need the software

And what is that called?

It’s called mysql
Or rather, the client parts

http://dev.mysql.com/doc/refman/5.0/en/
is that the manual you were reffering to?

Is it possible to have MySQL output headers in a query ? I would like my gridview to display the column titles.

Yes, that is the manual
I’ve no idea if the windows installer allows you to install just the client parts, but if you do a complete install but don’t run the sevrer, that will definitely give you what you need.

I have two left joins on my query to the same table, but I want the same field name for the table joined to have two differente names (one per left join), how would I go about doing that?

aliases

not familiar with that,

select `field` AS foo1, `field` AS foo2

select x.x as y, x2.x as y2

ok
oh yea
but the problem is, this is a single field that I need to call twice

backticks doesn’t look that good

so alias the remote field

Akuma, maybe you realy want to use an union and/or a subquery?

better yet, pastebin your query

or it might be as simple as the if function

sure

ok

IF/CASE/WHEN

one sec
http://rafb.net/p/DfSClk25.html
basically, I have two fields in projet that call fields in organisations
what I want to do is access a certain field on organisation (same for both left joins) with different names

ok

basically a different name for the same field on organisations for each left join I have

you need to alias your tables
i.e.

you could use a subquery and an union all
or an if function

LEFT OUTER JOIN organisation org1 …. organisation org2
then org1.fieldname, org2.fieldname
you could even alias those fieldnames, if you like.

is this two different queries I need to do?

no
you can reuse the same query.

hmm… I might have to read a bit more doc on left joins

SELECT projets.* FROM projets LEFT JOIN organisations org1 ON org1.oid = projets.oid
then
LEFT JOIN organisations org2 ON organisations.oid = projets.manda_asso
err
LEFT JOIN organisations org2 ON org2.oid = projets.manda_asso
SELECT … org1.fieldname AS foo1
then

Oh!

then org2.fieldname AS foo2

so I can basically do the aliasing before defining the table aliases?

you need to define the table alias as you left join them.
follow what I wrote.

Is there a command for mysql to decrease or increase stored value or is it better that I use select in php to get that value first and then update it?

SELECT projets.* FROM projets LEFT JOIN organisations org1 ON org1.oid = projets.oid LEFT JOIN organisations org2 ON org2.oid = projets.manda_asso

jake81, you can just use field +
field + number

eh.. with update?

but then I only get projets.*

I pretty much spoon fed you there
of course, modify the SELECT statement as needed.

Ok

you can select from projects, org1 or org2

ok
that was what I wasn’t clear on
Thanks

jake81, correct

no problemo

you mean like this: UPDATE settings SET value+11 WHERE VARIABLE=’myvariable’ LIMIT 1 ?
if I want to increase it by 11.

note that I corrected your orignal LEFT JOIN statements. They were incorrect.

how can i get a unix timestamp from a timestamp field using jdbc?

VARIABLE and VALUE where VARIABLE is name of variable or other identifier and VALUE is where numeric value is stored?
I might have ran into trouble with it. value seems to store contents in format text. For this specific value anyway, it only uses numbers, but field type is text. Will it work?

CAST it as INTEGER
or INT

eh, CAST?

it should still work
i think

CAST the VARCHAR field as INT
just to be safe.

okay, so I should do this:
eh sorry..
I don’t know how to use it with update..

pastebin your current query, please

my current query has not been created yet, I am just trying to figure it out

I did not follow your requirements. What is your current goal?

UPDATE settings SET CAST(value AS INT)+11 WHERE VARIABLE=’myvariable’ LIMIT 1

anyone know what this error could be “‘Unknown column ‘RoleId’ in ‘field list” “?

I have a table called settings. It contains 2 fields, variable and value. variable is tinytext and value is text.

that looks OK. Of course, if the field is VARCHAR, you might have to cast the result back to VARCHAR(length)

one of the fields is called for e.g. as “amount”
or in my example I called it myvariable.

the column RoleId does not exist in that table.
solution: don’t select it

hello, i recently installed mysql on my ubuntu box and it’s up and running, however i can’t connect to it remotely, even though i forwarded port 3306 through my router…is remote access disabled somehow by default?

if they are int ot tinyint, no casting is necessary

argh.. I’ll try to find myself an example with google about using update with cast..
they are text.

pastebin the output of DESC tablename; please

infamouse, possibly ubuntu did that

I cannot as it’s not created yet

please create the table first.

my program is not in state where it could be executed.

you prolly shouldn’t use tinytext for a variable name
and at least use varchar

It’s not my idea, I cannot change that as this is modification to existing product.

will check a thing, can i create a role id then it should work?

well you’re throwing a lot of stuff in here, without anything to rely on. I need to know the specifications.
if you alter your table, sure.
!man alter

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

^^

johnny, how can i determine that and if so, allow remote connections?

perhaps skip-networking in /etc/mysql/my.cnf
that’s how i always run mine
if i wanna query remote db, i ssh in first

there is records already, then it should work?

sure.

just tell me what you want to know? table: settings contains 2 fields: variable(tinytext) and value(text). there are records, I’m interested in this one that has variable=totalMembers and value=something. Value is always numeric, I need to increase this number by specific value calculated by my
program.

you can add columns to existing tables with records.
ok, this is much better already. I have actual data to work from.
bok, this is much better already. I have actual data to work from./b

yeah, sorry. It was hard to explain when you do a lot of things at same time
uyeah, sorry. It was hard to explain when you do a lot of things at same time /u
buyeah, sorry. It was hard to explain when you do a lot of things at same time /u/b

oooh, innocent bystanders

UPDATE tbl SET value = value + 42 WHERE variable = #number AND value = ‘value’;
that would do it.

will it?
value’s type is text? Oh.. You made there a small conversion
thanks. I hope it works, as currently I have no way to test it

I used single quotes for value, as you said it’s text
I did not use it for variable, as you said it’s int

eh..

as far as updating the variable column, it’s int, so no conversion is required.

value is a field in table which has TYPE text.

yes, I covered that already.

okay, guess what.. I’ll try it in phpmyadmin.. Then I should know if it works or not without trying to execute my software..

oh shit, it’s text. Sorry/

yeah

then you do need conversion. Hold.

means that it get’s it from field value

UPDATE tbl SET value = CAST(CAST(value AS INT) + 42 AS TINYTEXT) WHERE variable = #number AND value = ‘value’;
UPDATE tbl SET value = CAST(CAST(value AS INT) + 42 AS TINYTEXT) WHERE variable = ‘#number’ AND value = ‘value’;

indicate a value passed as an argument.

as per the man pages.

now it looks better to me too although I’m far from being expert.

this version will work

should it be TEXT instead of TINYTEXT?
value’s type is text and variable’s type is tinytext

I’ll leave that up to you to match them
the idea is the same.

you have error near ‘INT) + 42 AS TEXT) WHERE variable = ‘totalMembers’ AND value=’value’ at line 1
I forgot prefix..

i need to try some more, don´t really know the prob

nope.. It’s still wrong..

i have a table with two cols.. user_id, subscribers.. each user has a list of subscribers.. i want to get the list of id’s
ordered with highest number of subscribers
select count(user_id) as cid, user_id from subscribers_table group by user_id order by cid asc; doesn’t seem to work
anyone konw how to fix this?
actualy i think i fixed it.. nm
thanks

pastebin your current query

http://pastebin.com/d29d7c82f

johnny, i’m not seeing anything regarding skip-networking in my.cnf…do i insert that into it?

variable(tinytext) and value(text) are fields in table smf3_settings

I just lost data. Please bear with me

any solutions?

I am still recovering my data

for some reason, i can’t remotely access my mysql server, i have port 3306 forward through my router, but still no luck…is there a setting in mysql that’s preventing this?

remote

remove bind-address= and skip-networking from my.cnf and grant permission to the external ‘user’@'host’ and remove any firewall rules

that was for you, infamouse

i want to store 2 time values in a db to later calculate the difference. do i use datetime or timestamp or just time?

[bonobo]: I’d use datetime

thanx, Davey

I’ll be back later, wife insists that it’s my turn to cook so I need to go do my duty. I’ll come back later. If you happen to know what causes problem, you can always put it in a query for me..
uI’ll be back later, wife insists that it’s my turn to cook so I need to go do my duty. I’ll come back later. If you happen to know what causes problem, you can always put it in a query for me../u

its my first direct contact with mysql. came to the conclusion that no cms has what i want

create temporary table foo — is this threadsafe? i.e. is “foo” only visible on same connection, or should i generate unique table names for this ?

foo is visible to the world, may it be known!

hah

foo, not you
i meant my temporary table
lets call it bar then

Is that a threat?
hehe

so, should i use unique names for those tables or i can use something like “bar” or “baz” in the application and it would suffice ?
oh
found an answer in the manual

imagine that! :p

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other
or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporar
(quote)
ok, that is very good

Hello, I am Gene Ray. I have a doctorate in cubicism and I am the wisest human and inventor of the Time Cube. www.thewisesthuman.com www.timecube.com
I have become interested in SQL databases.

the wisest human, eh? good story

I feel I can use a database such as MySQL or Postgresql to communicate my Time Cube theory to the word animals that are educated stupid (professors).
I will have four computers (a cube has four sides) placed around the earth.
These computers will record the position of the sun related to the earth by communicating with a central database.
This will prove my Time Cube in terms understandable by the educated stupid.
Any person, even the most educated stupid, will clearly see the MySQL table shows time four the four different days per rotation.
What Life Force is more powerful than the Sun? The masculinity Sun and the femininity Earth form a binary of Opposites at Center of Universe – Greater than either Sun or Earth, debunking all Oneism Gods taught by religious/academic Word Animals.
This will also proove that a cube has four sides, a top, and a bottom.
Spelling is irrelevant to anyone with superior logic.

rofl

This makes me the best programmer and the wisest human.
I consider myself wiser than Einstein.
No human alive has been able to measure my intelligence. Psycologists can do no more than assume I am schizophrenic!
This is why I have awarded myself the Doctorate of Cubicism.
No other human has ever acheived this.

…

who is this lunatic?

Gene Ray, apparently
horrible web design, by the way

Damn, Gene.

huge fonts make for painful reading of long documents

the first site gives me a headache
that background image is horrendous

Test

You are too inferior to read my web site.

hahaha

I have no problem reading it because I am not a word animal.

a … word animal?

My web site has more wise information than evil Google.

ALTER TABLE geneRAY ENGINE=blackhole;

someone here who could help me with a little problem?

Google is an index of knowledge that is really stupidity.

its not a matter of being able to. It just that it makes it more work than it is worth for some rant against religion
huge font = more scrolling

ask your question, and someone might

NASA’s Moon Landing was far less of an achievement than Time Cube discovery, for I have Cubed the Earth, with 4 simultaneous corner days in 1 rotation of Earth. (singularity belief scientist can’t comprehend T.O.E.)

anyway… did you have a mysql question or what?

k..

Yes.

REVOKE NONSENSE ON mysql.* FROM ‘generay’@'%’;

I want to setup a MySQL database to prove my theory to word animals.

then please ask it instead of spewing nonsense

i actually started to optimize my sql-queries, so i tried this one for my stats:
SELECT * FROM user_statistic LEFT JOIN users ON (users.id = user_statistic.userid) WHERE descr=’”.$descr.”‘ ORDER BY descr,score DESC
but i doesn’t really work

how is it not working, exactly?

i want to order the stats in categories like building or sience, but it always shows me the same categorie
$descr is for the categorie’s name

well WHERE descr=’”.$descr.”‘ would only return results matching that particular category
so the ordering does not make sense.

This is why all computers are evil. Their CPU gives stupid lies when you multiply a negative by a negative!

that wasn’t a question. You want to set up a mysql server? go for it. It can be downloaded from http://www.mysql.com
thats called “math”

Math is built upon evil concepts.

how dare you, GeneRay

Because everyone here is educated stupid, I will leave.

math is holy

you’re wasting your time.

I am above God.

yeah but its kind of funny

Good bye, word animals.

kthxbye!

And visit www.timecube.com sometime.

no
we won’t

I already did

lol

I had the displeasure of loading it in my browser

Hello, I am writing my very first mysql stored procedure How do I iterate over a column? I am looking for something like a foreach in mysql stored proc.

well now that THAT is done with… I’m going to change my laundry and take a shower

where were you? You could have kicked him!

shit. something that was a valid query in mysql 4.x has a syntax error in 5.x

how about you tell us what the error is, exactly?

http://rafb.net/p/YaBmD418.html
not an easy query to read, but i’m guesing that theres some variable i’m not allowed to use anymore
like ‘condition’

is ‘condition’ a column name?
if so, put it between backsticks

its not
) AS condition

try AS `condition`

meh, that was the problem
you’re not allowd to do ‘as condition’ in mysql 5.45
er mysql 5.0.45
all uses of condition have to change to `condition` or another name

ok.

thumbs, have you recovered your data?

almost done.
90%
thankfully, my backup was good.

how did you end up loosing your data in the first place..?

I dropped the wrong table.
I need to reindex it afterwards
so it should be OK.

okay..
argh..
UPDATE settings SET value = CAST(2 AS TEXT) WHERE variable = ‘totalMembers’;
even this errors.
If I change CAST(..) to ‘2′ it’s just fine.

http://www.engadget.com/2007/07/28/man-gets-bsod-message-tattooed-on-his-arm/

CAST is awfully documented. I was able to find a lot of examples when people need to use CAST with SELECT, but no examples at all about using CAST with UPDATE. No any mention, thousands of pages found but they all are just release notes and bug reports of mysql..

don’t use TEXT
use VARCHAR(length of field) instead
i.e., VARCHAR(20)

I don’t know length of field. It’s type is text

so use a large enough number, and humour me.
please.

#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server verion for the right syntax to use near ‘VARCHAR(20)) WHERE `variable` = ‘totalMembers” at line 2.
amused?

heh
pastebin the whole query, again, please.

UPDATE `smf3_settings` SET value = CAST( 2 AS VARCHAR( 20 )) WHERE `variable` = ‘totalMembers’;

ok

okay.

sec

UPDATE `smf3_settings`SET value = value + 1 WHERE `variable` = ‘totalMembers’

I got customers to tend to

It doesn’t matter even that value’s type is text, it seems to mysql can go around it.
so no CAST needed anyway.

ok
brb

I need to drop a whole bunch of tables. I’m looking for something like this “drop table foo*”

Put all foo* tables in teh same database and drop that… or use information_schema or show tables like ‘foo%’ from a shell script to generate the drop statements.

Thanks!

hi guys

Hi, I have a select that uses a field as longtext but is very very long, and only one select maybe uses 15m, any idea how can I speed that?

hi
can mysql be used with sql on rails?

sql on rails?

yea
its a new web development paradig
u make apps in PURE sql

never heard of it

sounds interesting.

pure sql sounds scary

not really. Makes for secure code.
since everything is server-side

ya u cant make sql injection
also it supports ajax and scriptacus
so does my sql support sequel on rails?

secure??
hmm
and you cant inject sql because of that?
uh huh…
jeepers do you have an URL for that?

as long as you take no input from users. ;^)

http://www2.sqlonrails.org/

threnody not much point then

theres a screen cast
which is very web 2.0

the point is that you don’t rely on client-side validation.

thats a joke
you do know that, right?

Your browser is insufficiently AJAX-y to
render the content pointed to by this link.
aha!

its just a joke

yes, it’s a dummy site.

making fun of the RoR site
and idea

what?
its a joke?!

yes
are you making fun of us too?

http://www2.sqlonrails.org/down
hahaha

“sudo -u mysql mysql_install_db5″ i get: “mkdir: /opt/local/var/db: No such file or directory” (i am using macports hence that specyfic path). does that mean i have some user rights (mysql user/group) problems or what?

look at the links

ya i know my browser isnt compatible im using an old version

probably no /opt/local

maybe i should install ajax extension

jeepers you ARE making fun of us

ok, we’re not falling for it anymore

Darn.

hii

oh no

I have a table with the columns “id int, order int, name text, desc text”.

Uh-oh, desc

and when I run this query

I liked the DOS 3 terminal though. made me nostalgic.

insert into `forums` (name, desc)
values (‘$fname’, ‘$fdesc’)

Quote desc
It’s a reserved word.
`desc`

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 ‘desc) values (‘fhj’, ‘fj’)’ at line 1

Or just call your column “description”

ah
thanks

No problem.

quote

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)

is there some type of mysql bug I’m not aware of for example I’m trying to run a query wher salesid 84 and export it into csv through my adminstration section on my main website which I have a simple little php file written up, well it exports them just fine into a csv file, but whenever I
have 20 rows for this certain salesid number it tries do download the php script? any other orders that have 20 rows, it will export them just fine, hop

d-media: You’re trying to blame a bug in your web site on MySQL?

Basically yes, because if you knew exactly what I was talking about then you’d understand.

d-media: Can you ask the question in a way that shows the MySQL function or query that isn’t doing what you expect?

yes
k
hold on let me show you

I don’t see how you’d ever understand.

d-media: Don’t include any php detail. Just the SQl will do fine.

k

You just might have a point.

i can tell already you don’t have any idea of why it would be doing this to trust me, watch ok go here http://pastebin.com/m2b4a6d4a
thats my sql
now when I run that sql lets say I do salesid 84 if salesid returns 20 rows it don’t export my csv file right, but if I do any other sales id it works just fine, now salesid 84 export fine until it exactly hits 20 rows

d-media: Are you sure about that? Do you know me?

but if salesid 64 has 20 rows it will export it just fine
I do know your good at this
thats why I’m glad your in the channel
every one else thinks its something else
then there like Idk

d-media: since this sql wouldn’t create a file, I’m assuming your php takes the result set and writes a file?

run the query in a mysql console. If it works there, the problem is somewhere else

see when i try to export salesid 84 it tries to download the file csv.php, but if it don’t have 20 rows the headers in php will bring the window up for export.csv
yes
want me to pastebin it?

it sounds like a bug in the php data handling portion

it could be

d-media: That has nothing to do with the database engine. This is a php issue.

Can anyone help?

field=’user’
(sorry)

nathan^: might be helpful to know which data is in it

“nils_ will switch to closed source software soon, this compiling stuff sucks” — Was that serious?

p–: hmm there is a string function to extract the part before the .dot
nope

nils_, syntax? or url? I coulnd’t seem to find anything

!man substring_index

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

!man string functions

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

ah whatever.

thx

thanks
finally putting together that DB box I was asking you about last week

p–: What’s the objective? FIND_IN_SET might be more what you’re really after.

nils_, a bunch of user inputted data
That contains newlines, etc
It’s a bunch of textg
In this case, it’s a poem

nathan^: and where does it truncate? maybe an encoding issue?

‘The Hunting of the Snark’
Yeah
I think so
But I dont know enough about it

Just the place for a snark! the bellman cried

check your table and transfer encoding

as he landed his crew with care

I dont know what that means, nickm_
nils_

nils_ accepts donations via paypal or wire transfer

I’m new to mysql

snoyes, basicaly we have email coming in as user.blah but the mysql table only has “user”
so I want to be able to get that info

what are the final specs?

ah. substring_index is probably what you want then.

nils_, it’s truncating at the newline character I think
But

newlines should be fine

I dont know where to start
hm

thanks guys, looks like it

well
I cant even select the data in the php mysql web hosting cli client

exactly what I needed.. thanks!

So
Am I supposed to be able to select the data from the mysql cli client?

any errors reported?

no

warnings? just no data?

select * from email_parameters where email_guid = ‘c818300a9ec8ea52816ff5be6cecfe53′ and parameter = ‘customNote’;
I dont know
I dont get any warnings from show warnings or show errors

hi, how can i do a load data replace but not replace one field?

I just get a weird output

is it possible?

+——————————————————————————————————+
| value |
+——————————————————————————————————+

| “Just the place for a Snark!” the Bellman cried,

+——————————————————————————————————+

oi

Is that what it’s supposed to look like
?

i would like that at each new insert, mysql add the today date

I think you had some issues when you imported it.

yep

on a certain table, how can i do so ?

snoyes? You think it didnt get in correctly?

cut at the comma

Why wouldn’t it get inserted correctly, can you think of a reason to look for?

How did you insert it?

Uh

use real_escape_string

an insert statement, or a load data infile?
!m toste timestamp

toste see http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

no no

I’m writing python

cheers

a timestamp field

We dont have a real_escape_string

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

Maybe I see it
Hang on
Thanks for helping I’m going to look at this
dbUtility.escape

twas brillig, and the slithy toves did gyre and gimble in the wabe.

snoyes

sql and poems from snoyes, whatever next

an sql host poem, obviously.

moin

moin moin

“Select, and leave my schema undone/the privileged routine, the barren FROM”

hehe

Data truncated for column ‘value’ at row 1
Same thing
hm

column too short

How do I even know what got in there?

That’s during a select? or an insert?

During a select
+——————————————————————————————————+
| value |
+——————————————————————————————————+
^nl^ As | the place for a Snark!” the Bellman cried,
+——————————————————————————————————+
I dont get why it looks like that
When I select it

nathan^: find a pastebin already.

Do I need to encode it?

because there’s a new line in it, and the client goes funky with such things.

Okay
Is that what’s making it truncate when I select it also?

SHOW CREATE TABLE tableName;
what does it show for the field type of value?

(Via python)
Hahahahahaahahaha
OH wow I’m a retard
It’s not what I thought it should be

no, just still reeling from the attack by the frumious bandersnatch.

that’s what’s wrong
I changed the wrong field to text
It’s still a varchar
I love you snoyes
Both for your appreciation of Carroll and your mysql help

get a room you two
;^)

snoyes what would happen if google was crawling a page of searched result of another search engine?

um…the cat would die?

wouldn’t that require google to put in a search term?

the cat would be simultaneously dead and not dead, until the page refresh.
bthe cat would be simultaneously dead and not dead, until the page refresh./b

So there’d be mice all over keyboards jumping up and down on F5

quantum kitties

time for some gaming

what???
there could some some links with queries
so, if google fetched it, it could crawl a result page

my ass is sweaty

how can i convert a datetime to unix timestamp in a query?

unix_timestamp(datetime_col)

having trouble here. I have 2 tables. I need to populate certain columns from table A to table B but need to set an id column in table B back in table A for each record. I assume I can use mysql_insert_id() or whatever its called but im trying to figure out how to do this with plain sql if
possible.

thank you leith

select last_insert_id();

thanks but how do i do that for the source table.column after each insert into destination table?
nested update? or similar

insert into b (select … from a); update a set .. = last_insert_id() where .. ;
in a loop
or something

aha
ok cool thanks

you need to do them one at a time to be able to use last_insert_id()
so the select in the insert would have to have a where clause as well

ok thanks
quit

The_wench exits (connection reset by luser)

lol, that’s the best ‘bot response yet.

it has a few more like that

pushes the_wench down the stairs

Hey! *thud* son of a … *crash* what the *thump* ouch *crunch* ow

rm -rf

Damn you made me forget all my factoids

I have a table with names in it with the first name in one field and last name in another. What command do I use to see if a name is in the table?

select * from table where fname = ‘yadda’ and lname = ‘yadda’;

lol too simple
ty

Is INTERVAL very slow?

No

So it must simply be that I am checking datetimes that slows this query down.

Possibly

using indexes?

or a lack of an index

seekwill, did you help your 10% today?

Yeah… I was just gonna ask about that.

DSal-Rak: I think so
DSal-Rak: I may have done more
sweetness
But that means no one else can reply if I’m doing the channel’s quota….

Is it possible to copy a table and, in particular, any indexes with just queries? Or is mysqldump required?

Well, adding an index for the datetime column didn’t speed anything up.

I find that difficult to believe.

Catnip96, maybe you need a composite index look explain your query

Hrm… possibly.
I have a seperate index for it.
!man LOOK EXPLAIN

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

!man EXPLAIN

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

(Why did you say “look”? =Z)

you might paste results from SHOW CREATE TABLE table_name, and then your query.
pastebin*

Catnip96, typo

Ah.
When people don’t correct their typos, I get worried.
And think I’ve missed something.
BTW… I understand all “Index Kinds” except for SPATIAL and FULLTEXT. What are those? And I don’t understand any of the “Index Types” (DEFAULT, BTREE, HASH, RTREE). What’s the difference there?

Outstanding. Nike just put Michael Vick’s promo contract on hold.

Vick is overrated

(INDEX, PRIMARY and UNIQUE I understand of the Index Kinds.)

and holding dog fights doesn’t help

hi
I’m have problem
I’m got this message error
SQLSTATE[HY000]: General error
so, what is it?

any other error text

i’ve returned

nop
only that
General error

you might mention your o/s, the php mysql web hosting version, any particular application you are using (php, etc), what you were doing when you got the error, etc.

mysql 5
php 5
trying to do insert
sorry, update

can you install mysql 5 with apt-get in ubuntu ? anyone know the package name to use ?

does a search of synaptic reveal it?

mysql-server-5.0
got it eventually
thx.

the 5.0 client is there too

hey how do i get a database that was create in old version of mysql to come up in latest everion example 4.x db to load in 5.0

commlink are you getting an error?

Xgc, lemme know when you’re back from work

commlink, see the upgrading docs

would insert into select from work on mysql.user table from server to another?

syntux are you asking if it’s possible to use the mysql.user from one server on another one?

i was getting this db was created with an old version of mysql etc

ebergen, yup because I have large user table, about 100 user with different levels so I just want to copy it

it should work

dont forget to flush privileges afterwards

0 -!- mode/#debian [+b %*!*@cpe-075-178-084-017.ec.res.rr.com] by stew (drthunder, acting like a tard and logs show him here week or so ago FYI

how goes your engine?
or still in “thinking” stage?

resting, quoting for an asp to php mssql to mysql job

ah, fun

ebergen, I copied /var/lib/mysql/mysql/ to the three servers, restarted mysql hosting servers and all went just fine.

Shrews been entertaining watching the questions in -dev and on internals about engine details

syntux good to hear

krow has a sample engine skeleton (in case you weren’t aware)

when does starcraft 2 come out?

Shrew I know there is a sample but havnt bothered looking at it yet, trying to get the ideas straight first

I understand INDEX, PRIMARY and UNIQUE of the “Index Kinds”, but not SPATIAL and FULLTEXT. What are those two? Also, what is the difference between the “Index Types” (DEFAULT, BTREE, HASH, RTREE)?

whats the proper way to store a MD5 hash ?

To put it simply, the SPATIAL indexes are used with the geographic features of MySQL. FULLTEXT allows you to search for words within text. The index types refer to the algorithms used to implement the index.
CHAR(32) ??

Doesn’t it represent an integer?
I guess it would be a very long number.. hmm

MD5 has is a 32-char string.
Don’t aske me how it can work, but it does.
Hmm… you mean you can FASTER search for words within text with a FULLTEXT index?

a big one, yes
yes
!m Catnip96 full-text

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

nope
!man fulltext

see http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html

dammit
!man full text

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

So if I have a search functionality in my forum software, and my forum posts are TEXT, I should make a FULLTEXT index on the text column?

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
it can help. and it only works with MyISAM tables

If it’s anything like the one on the MySQL Web site, I think I’ll pass.

!man fulltext s fins that

Oh. I use InnoDB.

finds

Why do they bully InnoDB?

!man fulltext s

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

MySQL Web site is really worthless for searching, and I assume they use their own thing.

ah, thx archivist

Catnip96, use Lucene or write your own or store the text in myisam
Catnip96, use Lucene or write your own or store the text in myisam
monsoon season over there as well?

Lucene… =Z

quite the opposite!

I have NEVER changed the Index Type so far.

were you affected by flooding at all?

not me no

BTREE seems to be default.
I have no idea what the others do.

99.99999% dont change it probably

hash is only available for MEMORY tables
for now

=Z

hash hasnt excited me yet at all, reminds me of Pick
dump and reload your data once a fortnight!!!
else your buckets shall be overflowing

select concat(year(curdate(),month(curdate()-2),7); should be 200757 but its 200777 y is that?

Why does this take 63ms? select * from cached_alerts where `unique`=’0c86755e54d75e95′; Explain says nothing bad. It’s using it’s index.

the computer was busy doing something else

There are only 77 rows, and everytime I click it takes that long.

77 rows of how big

avg row length is 115

is it being written to as well

Nope

select concat(year(curdate()), month(curdate())-2, 7);

thanks. i got it.

I changed the engine to memory and it’s the same thing

perljunkie, show processlist while the query is running

A couple sleepers.. Nothing really going on
Quad core machine.. fast as hell, and 100% idle.

odd
perljunkie, type of `unique`?

Yeah. Thats what i’m using. It happens with this query too : select * from cached_alerts where `id`=’37′
I actually removed the index from id to see the difference and it still took 63ms

how big is the id field? Is is varchar or int?
if it’s int, why are you using single quotes?

int, and good point. I’ll try without

he cast time

Same ms without quotes

dont quote numbers

yeah, I didn’t see it

now put the indexing back on.

63ms

also, does your field contain dupliacte values?
duplicate, rather

Nope

how many rows does your table contain?

77

do you need to select *? Can you select a single field instead?

Definitely
let me try
still 63ms

Then something else is happening.
is the server low on RAM? Is the CPU pegged?

pegged? it’s idle.
1024528k total, 477500k used, 547028k free, 35960k buffers
0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si

ok I have no idea, then.
ask seekwill or threnody

lol. ok thanks.
seekwill. ok. thanks a lot

or a measurement technique error

his query should take ~3-10 ms

Think I should measure it myself and double check it ???
I’ve never seen a query go that fast on this machine. It’s mysql 4.x. Maybe an upgrade would help.

eg somebody measuring the script engine startup time as well

HOLD ON LET ME PAST IT ALL 4 U

SELECT books.title, count(*) AS Authors
FROM books
JOIN book_authors

drop the caps

ON books.isbn = book_authors.isbn
SELECT books.title, count(*) AS Authors
GROUP BY books.title;

lol omg

FROM books
SELECT books.title, count(*) AS Authors
select fags.* from main_db.fags where location=’#mysql’ and fags = a lot of fags;
JOIN book_authors

use a pastebin, too.

FROM books

pastebin

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

I think he’s trying to be funny.

they had trouble with him in ##php as well

he sounds slightly immature.

I think he is bored
Maybe it’s my RH3 OS. It’s old I guess.

hmm.. already mysql 6.0.0 .. that was quick

I’m trying to create a table with pk of type int, but i get error “key was too long, max key length is 767 bytes” , mysql 5 … any ideas ?
this is the error – is int not a valid data type for a pk ??? RROR 1071 (42000): Specified key was too long; max key length is 767 bytes

nkbreau, composite key?

how do you list the functions in a db?

you’ve obviosuly become the victim of an homosexual sql injection attack. Check the manual for “Depends”.

i mean procedures

there is no composite keys…

how do you show procedures?

some tables were ok but others werent
actually it’s the auto_increment tables that vailed
failed

BlkPoohba, look in the manual for SHOW
it is listed int here

archivist – http://pastebin.ca/636487
thats the create for one of the tables

maybe i’m just missing it… i see it tells you how to show the specific procedures but i forget a procedures name
ok. i figured it out

can anyone help me with that create statement ??

nkbreau, whats the create for the table it references

its just a simple lookup table
which created successfully
with no auto_increment
should i be using something other than innodb ??

it may well be but I ant create the table here as I get a 150
cant

ok, sec
archivist, full sql – http://pastebin.ca/index.php
i mean http://pastebin.ca/636489

well I notice an index on a 1024 field in there CREATE INDEX IX_Page_1 ON Page (filename ASC);

ok… but would that cause this error ?

well the error message told you

SIR, YOU ARE TALKING TO A NIGGER
–
– MySQL dump 10.9
– Host: localhost Database: prot_songportal
– ——————————————————
– Server version 4.1.22-standard
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’ */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
–
– Table structure for table `bs_autologin`
–
DROP TABLE IF EXISTS `bs_autologin`;
CREATE TABLE `bs_autologin` (
`al_ID` int(11) NOT NULL auto_increment,
`al_us_ID` int(11) NOT NULL default ‘0′,
`al_key` varchar(32) NOT NULL default ”,

wtf is that ?

a troll

often the mentally ill don’t realize their condition.

lol wut was that
that was hilarious

archivist, even if i take that index out i still get the error… is the innodb type not what i should be using ?

nkbreau, no innodb is ok

omg

how do I force a data type when selecting?

CAST ?
never used it tho

ah, it’s the unique on the filename it doesnt like

just remembering what i read
people are weird

what is the command to generate a create table script in mysql from an existing table in the database ?

show create table

SHOW CREATE TABLE

or mysqldump
caps lock makes you slow

hehe

http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-select-into-table.html

Hello
I’m getting a SQL error, can anyone help please? SQL Error Query: SELECT * FROM games WHERE cat=\’Action\’ AND id!=\’1\’ ORDER BY RAND() DESC LIMIT 0, 5

what are those \ doing there

i dont know

do you actually have a field named id! ?

yes, in the games table there are those fileds
-e

is the filed id! a numeric datatype?
field*

int(10)

If I wanted to take 5 fields for a record, join them with a newline in between, and put them into another field, how can I do this?

!man PhilFX quotes

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

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)

like UPDATE table SET field6 = CONCAT_WS(‘\n’, field1, field2, field3, field4, field5); ?

try, as a test, ’select * from games where cat = ‘Action’ and id! = 1;’

ok, i will try, thanks

what naming convention do you guys normally use when you make a “mapping” table to map records from two tables?
for naming this third in-the-middle table, I mean

apples_oranges

okey, is there a standard for this matter?

it’s all personal preference. I use plural for tables, single for columns.

yeah, but I meant more like apples_oranges_map, apples_oranges or what
whether you try to give it a descriptive name. Mine would in that case be very long

just remember how many times you may have to type it vs. your memory for what you meant. ;^)

exactly.. been evaluating that already, hehe. Thanks to both!

Why can you make comments in MySQL per-column but not per-index?!
I don’t wanna name my index, but I wanna explain it.
Index_4 # Useful when running the heavy script lame_shit.php.
For example.
What directory?

the special one

the one the dba after you is most likely to stumble upon.

trawling text files can be entertaining

Hey

Have no fear, Aquaman is here

hahaha
hey seekwill

do that thing where you talk to the fish

Now you’re more delicious.
Red Kool-Aid.

Who put me where?!

Aquafinaman – it’s all tap water

ahahaha I prefer eating them than talking to them

Weirdos

Xgc, i got disconnected

i got it all fix.. but one small problem,, where do i put for “MySQL Database Name”
or wht do i type in

eh?
whatever db name you created in mysql
in the example commands i gave you, i think i used coppermine
for the database name

gallery

!man IF

see http://dev.mysql.com/doc/refman/5.0/en/if-statement.html

it’s gallery

hi al
all

How do I change the default password ?
I thought it was root/root

I want to ask how I can optimize my mysql all databases and tables?

!m ekim| securing

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

it’s gallery

cant you just tell me what the password is ?

read that link it tells you and how to secure your system

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

got it
I might have set it at one time
but I dont remember

some know how I can optimize my mysql all databases and tables?

!tell megaspaz performance
!tell |messiah| performance
sorry

|messiah|, what sort of optimizing

when I start myqsl said an warning message
Checking for corrupt, not cleanly closed and upgrade needing tables.
said that
what I have to change?
or run?

it was doing it automatically

alwais when I start mysql
said that message

it’s normal

ohh ok, and for optimize (delete the data that is not well delete) I have to run some command?

you mean to optimize a table?

it’s gallery

When i enter with phpmyadmin

optimize table table_name;

said there is data that is no use
and can delete it
I think
maybe there is a command to clean all database delete the data that I don’t use, etc..

optimize table table_name;

There can be any number of JOINs in a query?

and?

ok and for all database I can optimize?

if that’s the database name, use that

There can be any number of JOINs in a query?
Or just one?

ok

many joins

Catnip96, upto the 60’s iirc
Catnip96, but you will need to think about what you are doing at that sort of qty (straight_join)
bCatnip96, but you will need to think about what you are doing at that sort of qty (straight_join)/b

Hehe… 60 ones…
I guess 4 should be enough for anyone.

and what ?

be careful in too many JOINs, I had a multi delete that froze the system (as in, mouse barely worked) as the “optimiser” decided to find the best way to do it

Catnip96, ive tested upto 49 on a self join

whoa

That was a bitch to debug :p

:O

now if only i could figure out my nested set issue

_Lemon_, yes straight_join for that

I must admit I still don’t know the difference between SELF, INNER, OUTER, LEFT and RIGHT JOINs.

i got really close, got everything except for querying by depth

So I just type “JOIN”.
And it’s worked so far.
Is there any sort of document that explains stuff like that in English?

hmmm, well I just ended breaking it up into about 10 smaller queries

The MySQL manual is… well… *coughes*… let’s just say it could be improved.

!google sql JOIN tutorial

Google Returned 1,470,000 Results for sql JOIN tutorial, first 1:
SQL Join: http://www.w3schools.com/sql/sql_join.asp
SQL Join: a href=”http://www.w3schools.com/sql/sql_join.asp”http://www.w3schools.com/sql/sql_join.asp/a

I frequently have many inner selects.
W3schools is spammed all over the Google SERPS, and it’s a really bad site.

ewww, stay away from www.w3schools.com

i dunno… you felt the need to tell me twice that the db you created was named gallery

bueeee – w3 seo spam!

!nextresult

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

you all good or not?

Has nothing to do with W3.
I wonder why they haven’t sued yet.

yes is gallery but what do i type in that area..

_Lemon_, setting your join order in advance is the trick with self joins

How can you sue for using www or w3? :s

type in what area?
you talking about install.php?
enter gallery

Can somebody quickly explain the difference in very broad terms between all the different sorts of JOINs?

what do i type in for “MySQL Database Name” in the boxline

And what is the default sort if you just type “JOIN”?

yes

the DATABASE NAME you created

ok hold
brb

Is laststars asking for help on third-party software?

which from what you said is gallery

And you’re prioritizing him over me?

kick me please!

hehe
what size boot

any size

like George Bush, you broke it, you own it.

I hate all my subqueries.

.

hes all yours

ok i put gallery but error said” mySQL could not locate a database called ‘gallery’ please check the value entered for this, but i did create db name …

laststars, there prolly is gallery support

what do you mean by create db name

somewhere else

create database gallery; ?

i did

maybe the user doesn’t have access to “gallery”

and did you create a user with access to that database?
hint grant command

oops i think i didnt create user for that database

well create one

ok so how do i create user for that database

the grant command
which i gave you a bajillion times
and you promised me you’d write it down, print it out, yada yada yada
..

i didnt say promised but i will write it down
hang on let me drag the text i save

!wench beer megaspaz

Here megaspaz have a cool beer

I can’t BELIEVE you’re helping this guy with a lame third-party software problem and ignore me…

there is something called google
go there
Catnip96, i agree
not that i can help you ..
but you’re still right

I also cannot believe how quiet this channel is for such a high number of users.
uI also cannot believe how quiet this channel is for such a high number of users./u

ok it this correct one
grant all privileges to your database name.* to some user name@localhost identified by ’somepassword’;

flush privileges;

!m Catnip96 join

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

Yeah, well, that’s the evil manual…

ok that grant command is wrong

oh

it’s close but wrong

Catnip96 so read it

please correct to me please whare the grant command are

it’s Friday night, some of us are out having sex or some such…

identified by ’somepassword’;

INNER, CROSS, STRAIGHT, LEFT, OUTER, NATURAL, RIGHT JOIN… =Z =Z =Z =Z =Z =Z

then flush privileges;

Well, I have no life.

you leave me alone

Oh you too? It’s a great life isn’t it.

me, I’m watching Dr. Who on sci-fi channel

dbuser you’ll want to use dbuser@localhost

Why is the MySQL manual written like a book rather than tech reference?

where dbuser is whatever user you create
lots of manuals are like that

k

Epic book.

unfortunately…
but i never thought the mysql manual was really that bad…

Yes… and it makes it extremely hard for me to learn what all the JOINs mean.
uYes… and it makes it extremely hard for me to learn what all the JOINs mean./u
INNER, CROSS, STRAIGHT, LEFT, OUTER, NATURAL and RIGHT. Sigh.

they assume you should be able to read if you want to use a database server

Catnip96, if the bot can read the manual, it should be easy for you

Are they vastly different? Or just good for performance?

Different.

http://en.wikipedia.org/wiki/Join_(SQL)

go to Border’s 3x week, buy a coffee, read the book, take notes.

Border’s 3x week… =Z

do i have to be in mysql root.. or what

what is that =Z meant to indicate?

OK, so INNER is default at least.
=Z — Very cfonfused.

cuz i am getting error in shell

s/cf/c/

Border’s is one of those massive bookstores with coffee shops that don’t complain when you read and not buy.
uBorder’s is one of those massive bookstores with coffee shops that don’t complain when you read and not buy./u

but cody’s didn’t have a coffee shop

do i have to be in mysql root.. or what
cuz i am getting error in shell

yes

That’s strange.

k

I have never been to a coffee shop. Sounds like a UK thing.
Possibly USA as well.

yep

USA

it’s worse in the uk

baltimore.. USA
red emma’s
thats’ where i’m at.. coffeeshop

where as every few blocks you’d see a starbucks, you’d see starbucks at every block in london, uk

and it is a small coffeeshop,bookstore, and you can read

as well as every other shop in london was a macdonald’s, burger king, or some other fast food place…

most coffee houses in US provide free wifi (not Borders or Barnes&Noble, but the smaller indepenedents)

totally erie…

Strange business idea.

not really
you’re trying to bring in traffic

works great

Cheapskates buying a coffee every now and then but usually just sit around and surf for free and read books without buying them?

and free anything brings in traffic
well you’re hoping for volume traffic and potential buying

it said here ERROR 1064 (42000): 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 ‘for @localhost identified by

laststars for is a reserved word you could try `for`

I always see people at the cashiers at Border’s buying $100 worth of books

manual grant
crap
!manual grant
dangit

!man grant

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

:O

you seem like Mother Teresa to me tonight.
grant

oh
change for to to

identified by ’somepassword’;

bleh

There’s something about the JOIN and ONION (UNION?) keywords I cannot stand.

is it beerz tiem? kthnxbai.

finally now let see if that work from install

grant all on db_name.* to ’spaz’@'localhost’ identified by ’scrbbelsers’;

heh

megaspaz gets mega spasms.

only when i see yer mom

FINALLY thanks
it working

yay

won’t last

heh

thanks for the help and i am sorry i am making you suffering with me

we’re enjoying it
;^)

don’t worry about it… next time, i’ll be logged in as archivist

OUCH!
wha?

thanks guys bye

see yer
it does suck

impossible

ok… taking bets on the next time laststars reinstalls his mandy and is back here…
uok… taking bets on the next time laststars reinstalls his mandy and is back here…/u

Mandy?

why doesn’t my socket work?

mandy == mandrake/mandriva

I give it five minutes.

I’m not sure your depth calculation is correct. In fact, there are a number of issues I see. Are you ready to discuss this?

doesn’t matter… /me goes to see if it’s possible to ghost archivist…

you have it on the wrong foot!

lol

Ghost him?

takez hiz nickz
yeah… like that’s better…

Anyone seen The Simpson’s movie yet?

nope
was planning on seeing it tomorrow… but san jose’s having some city grand prix and parking will be nonexistent and traffic will suck

I’m also looking forward to the Futurama movie

ooh… there doing it?
yeah
that should rock

inevitable

hi all
i was wondering how you’d recommend installing the mysql-devel packages on OS X?

http://developer.apple.com/internet/opensource/osdb.html
straight from some mac dude…

well, thats for mysql itself.
from source.

yeah, the source should install the header files and what not
whatever you need to do mysql dev stuff

I am running mysql 5.1.20 on fc6. When I run the mysqld, it runs, but doen’t show up on ndb_mgm. Any idea

ah yes, but since i already have it installed through other means – a source install would be overkill. hmm, perhaps i should check if fink or something have them

ah
yeah… i suppose that’s the better way then

tintin, curiously, whats ndb_mgm?
hope it works

Correction. Depth is ok.

cluster management
cluster management console

ah

hi Xgc

5.1.20 mysql cluster is pain in the ass to install…

true.. depth is ok.. just not in ontext
context*

But your data looks strange.

strange in what way?

Let’s start at the beginning. I’ll ask a couple of simple questions. First, why would node cid=1 have a left = 1 and right = 14?
I started with the parent hierarchy, which seemed ok. But when I compared that with the left/right constructs, they didn’t seem to match.

want to ask a question what website i can able to have redirect dns to my private server beside no-ip?

afraid.org
they do port redirection for free
i don’t use any of them, so you need to deal with them directly

afraid.org

yar

Xgc, the root of the tree matches on modid, itemtype, objectid

weird for that name co. eh eh

so each set of those has it’s own tree

http://freedns.afraid.org/?no_webforward_found_for_afraid.org

so the left and rights match as far as i could tell

cuz i dont want to use the ip number to connect my own server i perfer to use name for it..

get a domain name?
-_-;

there’s also dyndns

I don’t think the current assignment of ids allows you to use them in the left/right form.

how so?

I understand you may have several trees.

cant use domain name for private server can it ?
if so how

But the parent assignment is not constrained by the tree type identifiers. The parent structure should stand on its own.

well that could lead to alot of tables
1 per article
and there are tons of sites with 1000 articles

Right now you have a root of a tree at cid=1. Correct?

that is 1 root
the other is at cid 8
or rather 13

Let’s stick with one tree.
cid 2 and 3 are direct children of cid=1, corrct?

yes
and 7

Well, this can’t hold up if cid 2 or 3 has children, since there is not valid left/right integer values that would keep those ids entirely to the left or right of the other side of the tree.
s/not/no

and 7?
err
?

Right. I see 7. The tree is already broken, I think.

the count looks ok to me

You have to reassign ids as you insert into the tree.

hmm..
aha.. so the code itself is broken

To make sure the left/right assignments are valid, the values of ids under a node need to be within the range.
The SQL looks reasonable, but the data/tree is broken.

they look within the range of 2 and 7
the children that is
of head 1

3 is between 2 and 7.
That breaks the tree.

oh.. it shouldn’t be..

Correct.
The parents ahve to be reassigned as more nodes are added. That’s part of the problem with maintaining this type of structure.

the parents are reasigned in the code
Xgc, http://rafb.net/p/giulIq62.html
it’s kind of a mess, which is why i’m into this issue at all
but i thought that part was sound at least

One moment. Let me recheck your structure. I may have made a mistake trying to compare the parent hierarchy with the nested set values.
I need a nice automated drawing program for this kind of structure.

Xgc, for real
but in the meantime you can concat with spaces for indent
kinda lame
but it’ll work

Ok. I’m fine with the nested set.
So now the depth, which seemed ok, should be fine.

well try the query

The method of counting depth looks ok.

i got odd numbers
like the head 1 count was really off.. while head 2 and head 3 were fine

Ok. Explain at a high level what you expected the outer most join to provide. Where you just trying to find the depth of each node?

g’day. i did an rsync of a whole box (for xen), but when i try to run queries on some tables on the new box, i get this error: “ERROR 1033 (HY000): Incorrect information in file: ‘./rt3/Users.frm’”. this Users.frm file has exactly the same md5 sum as the original – why would this happen?

s/Where/Were

i want to get only the nodes at a certain level
so i could say &depth=x on the query string and only get depth x of the tree

hi

That’s all? I’ll post something. One moment.

how can I know the total number of distinct rows that there are in table?
select count(a.id) from sponsorships a, pledges b where a.pledge_id=b.id and a.name=’something’; gives me the total number of just a kind of row

select count(distinct *) from table

oh thanks

count(distinct a.id)

what did you think I wanted?

select count(distinct a.id) from sponsorships a, pledges b where a.pledge_id=b.id;

http://rafb.net/p/5kzf7I46.html

I am doing that and still get the total number
I just have six kind of pledges
do you know what I mean?
sponsorships has a foreign_key from pledge and in pledge I have just six kind of them
I would like to have a table with six rows where each shows how many of the there is
or this is not possible in only one query
?
anyone?

Simple as that. Now, you mentioned earlier today that you wanted nodes some depth under a given node, not necessarily from the top of the system. Maybe that’s what you were attempting.
Did you want the depth specified relative to the given node?

Xgc, do you know how could I do that?

We’re climbing trees at the moment.

ok

and finally, depth=1 from cid=1: http://rafb.net/p/398ApF49.html
Done.
Notice how cid=14 isn’t in the result, since it’s in a separate tree, although it also has depth=1.
It’s just not in the cid=1 subtree.
Earth to johnny.

sorry
i’m at work
and i was running the closing

That’s the final answer.

i’ll be able to talk in about 10 minutes

I’ve got a column that contains comma separated values. How can I structure a WHERE clause so I can select a row if one of those CSVs is, say, “Google”?

like ‘%Google%’ mebbe?

Bad idea. If you add a separator at the beginning and end of the list, WHERE field LIKE ‘%,Google,%’

Blah

blah

indeed

It’ll be fairly slow. You need a better structure.

be home in a bit

They’re supposed to be subject tags for a blog post
How might I better structure those then?

Very poor design. You should change that.
Let the subjects per post be in a separate table.

?

It’s called a relational database. The reason for the separation is normalization and to meet the fundamental rules set down by Mr. Codd.
http://en.wikipedia.org/wiki/Codd’s_12_rules
This is all fairly basic, but critical to the proper design of these types of systems.

No, I understand what a separate table would mean
I just don’t get how it would be structured

“Codd’s 12 rules are a set of thirteen rules proposed by Edgar F. Codd,” … why is it called 12 rules if there are thirteen rules. this doesn’t make a whole lot of sense to me?!

subject table would have a row for each available subject. subject_post would have a row for each [subject, post] pair.

…
So you mean, two extra tables

[Apollo]-AFK: He meant Rule [NULL] and we all know COUNT(rule) wouldn’t include the null.
b[Apollo]-AFK: He meant Rule [NULL] and we all know COUNT(rule) wouldn’t include the null./b
Absolutely.
Structure is good.

Right. Scrapping that feature then ^_^

Tables are good.
1 table to hold all tends to be bad, very bad.

True
I just don’t know if I’m not-lazy enough to make extra tables just to have subject lables

You’ll eventually figure it out, if you stick with it.

*labels
No, I can do it. I’m just not motivated
I don’t even use the category feature…

You might be motivated when these LIKE expressions start to bog down your queries, since they can’t use indexes.

No, I don’t want to use LIKE. I know that’ll be slow
I’ve been having a hard enough time figuring out this darn search …
I have a table for the posts and a table for the comments, so when performing a fulltext search for both comments and posts matching that term, I’ve had to do two search queries and then use a temporary table to order them by relevance regardless of type
Then to use pagination I’ve had to store them as arrays in PHP session data.
This solution seems kind of not-ideal for me, but I haven’t figured out an alternative.

You really should listen to Xgc

I am listening

Ok

I was just debating whether or not I was motivated enough to actually create the tables, or whether I should scrap the feature itself

Scrap the feature. Users don’t need it

Eh, users don’t need these “feature” things.
And then I was throwing out my other SQL problem just in case anyone with more experience might have a better way of doing it.

You don’t really want users either. They hog up bandwidth

I asked a bunch of people if there was any way of full text searching two tables and they were like, “Er …?” as if no one had ever asked that.

but then who else would you blame for when you fubar stuff?

Well, usually that’s a bad idea

Nah. I take full responsibility for that
So I’ve been learning.

rule #1. never accept the blame even when it’s obvious you’re at fault.

rule #2. see rule #1.

But is my current implementation any better? Or am I doing it totally wrong?

I have no idea what you are doing

I explaine dit above

That doesn’t mean I read it

if I have a 1:* relationship, how do I select the 1st of the many for each of the 1: side of the relationship?

I saw Xgc say something and you saying “No”

Would you read it now and then offer me an opinion?

Define “first”. SELECT a, MIN(b) FROM table GROUP BY a

will that the MIN(b) for EACH of a ?

Well, Xgc knows 1020x more than me

Ah

Thta’s what a gROUP BY is all about

will it work well with JOINs?

Sure

OK, lets try this
this sucks, I need some test data, heh

I have what should be an obvious problem with syntax for an insert statement, and I must be reading the manual wrong because it seems I should be right
uI have what should be an obvious problem with syntax for an insert statement, and I must be reading the manual wrong because it seems I should be right/u

anybody know how to export/import data from an old mysql database without using mysqldump? My old computer died, but I was able to grab the contents of the hard drive

How old is ‘old’?

… I don’t know how mysql data is stored in the filesystem

As files

not really that old
like a month

Well that’s just realtive
Dude, that’s ancient

lol

From your description, comments == posts. Place them in one table and link them such that the original post has a reference_id to itself and each comment has a reference_id to the original post. Then you can fulltest search the one table for matches.
fulltext, that is.

there shouldn’t be any version problems

can I get a little more help, I have a self join, and I’m unsure how to do this. *tries to explain*

Just copy the datafiles over then

I tried that but I didn’t get any structure or data in my tables, just the table names

Joins do not affect your original question

looks like I’m having a problem with quotes; how do I use double quotes?

yes, but they confuse the HECK outta me

Paste query
First, make the join

Sometimes more tables == bad. It’s a strange world.

Make sure the join works.

Unfortunately, the fields differ

I guess I need to make some test data

insert into test (“a”,”b”,”c”) values (“1″,”2″,”3″),(“4″,”5″,”6″);

That’s not an issue.

Why not?

Don’t quote your columns
Use ‘ instead for the VALUES stuff

automation requires it

The basic purpose of a comment and a post are the same. Merge the common features and place the differences in other tables if you wish.

Automation?

yes, taking it from a .csv file

So?

some programs use the double quotes

Stop
If the data is coming from CSV, use LOAD DATA INFILE

I have no control of it

What “program”

it’s actually from my stock broker

Well I’m only searching the heading and content of a post and the content of a comment, which I’ve got indexed. So you’re saying to put these three fields together in a single table and then put the other things separately?

Proper design often involves identifying these common structures that need to be handled similarly, like the text of a comment and post.

If you want some help, you’re going to have to be waay more specific

There appears to be an opportunity here. I’m just offering a suggestion.

Now what if I want to expand this in the future and I start handling different types of data?

if it helps, the old version I’m looking at is 5.0.14 and the new one is 5.0.45
would the datafile format have changed between versions?

The text of the post and comment will not change. If you ever decide the text of a comment or post needs to be a picture/blob, you have some work to do. Your fulltext search may need to be upgraded.

ie, should a 5.0.45 installation recognize 5.0.14 datafiles?

Yes

Sorry, maybe I worded that wrong. What I mean is, what if I expand what I’m searching, into pages on a site, etc–things that aren’t similar to posts and comments.

Time for a movie on my new DVD player

and all I need to copy over is the folder that corresponds to the database I want?

Then you’ll probably want an even more general design, not less.

-_-
Sigh. Thank you for your help. Really, I appreciate it.
I just dislike “general”

hmm I think permissions are an issue here

Obviously the way my mind organizes things is totally different from the way relational databases organize things.
Sometimes, it would be easier if I were more normal…

You keep trying to avoid the issue. Why ask if you think you have the best answer? The comment text and the post text should probably be treated similarly. That’s pretty obvious. The fact that there are other things that aren’t as easy to process, doesn’t change that.
from an application point of view (forget the normal structure for a moment) this type of common handling of objects is very important.

I only recently decided I might possibly be able to wrap my head around OOP

That could cut a fair amount of work directly in half if done correctly.

For the longest time I’ve been unable to understand it
That’s the problem. I don’t know if I’m doing it correctly. Hence why I’m asking.

It’s certainly possible you aren’t quite ready to sink your mits into this yet. No shame in that.

The only way I’ll ever be ready is if I try it.
But I guess I’m learning OOP procedurally, if that makes any sense.

lol
you mean step by step?

Systematic destruction via LIKE ‘%,ouch,%’;
Do we have a winner?

i have one little problem but overall the coppermine is working but i cant even able to upload any pix to my coppermine what can i do now
ui have one little problem but overall the coppermine is working but i cant even able to upload any pix to my coppermine what can i do now/u

so… I’ve taken a closer look at my datafiles, and it appears there are three types of file in a database folder: .frm, .MYI and .MYD. In the database I want, there are only .frm files, and I could only find the data in ibdata1. Is this unusual?

create a directory in /path/docroot/coppermine-install/albums called uploads

/path/docroot/coppermine-install/albums

aha.. awesome ..
Xgc, sorry, i had an unexpected guest to stay over, so i had to set him up

You’re welcome.

Xgc, truly i am.. and thank you

in a many to many relationship, a join table is used, right?

Yes.

ok, thanks
now in this table, do I just want to use pure id numbers or 2 foreign keys?

Two foreign keys, which together are the primary key.

ok, that makes sense
i’m trying to run this SQL
ALTER TABLE `designs` ADD FOREIGN KEY (`category_id`) REFERENCES `category`(`id`);
but I get this error:
Key column ‘category_id’ doesn’t exist in table
even though this syntax has always worked before

designs doesn’t have that column.

yeah, i’m trying to add it as a new foreign key

You need the column first.

what type does it need to be?
just an int?

Adding a key doesn’t add a column.

well, i usually just create my foreign keys when making a table

whatever fits your data.

The key a a structure that refers to an existing column or set of columns.
s/a a/is a/

so, since this refers to an int, it should be an int

It needs to match the type of the corresponding column(s) in the parent table.

ok, i follow

and also match the column order of the parent key.

column order?
keys point to columns…

(x,y) would not be a proper foreign key to refer to a primary key (y,x)

oh, well, i’m not using multiple columns in my keys…yet

Keys can be composites. I was just completing the description.

right, thanks for the help, Xgc

You’re welcome.

if I have a mysqldump/phpmyadmin dump.. is there some how to easily excute it again through PHP?

Any way I can see verbose info from mysql such as things like failed logins? A script is unable to authenticate, and I can’t figure out why.

ok im officially desperate – has anyone here encoded any x264 movies :/

hi i’m wanting to use ` symbols in a query but am getting an error
how can i work around this?

escape it?

ah like \’ ?
welcome to rookie hour

x264? or x263 ?
in any case, try ffmpeg in combination with libavcodecs
4044800 kB; … is it possible to free this space to fs ?

why?

why what? because it’s spare 4G that i could use

use for what?
have y better choice??

i dont need this table to have that much space anymore
it was collecting log information, but now it only has past day and the rest is summarized and cleaned up..

aha
in config file is many innodb settings, go there, change this value and restart the server

so, is there anything i could do to free space from innodb tablespace back to disk?

but i don’t know whitch one exctly for your problem

i have a per-table tablespaces btw

i know exactly only about innodb_pool_buffer_size – space for innodb indexes

What does this mean
?
0
0 mysqld

I perl with DBI. now I execute an sql statement that works in the mysql console, but here in perl it gives an error becasue it is seemingly not able to accept an alias of a subquery (let’s say ‘derivee’) – in the from clause of the outer query – at other places in the outer query. Why?
of course I use mysql

thats mean, nothing is running
mayby another result set. i’m using any aplication with mysql and this aplication don’t can understand some specific comands, whitch is no problem in console

thanks

hey
can i have multiple indexes?

yers
how many would you like ?

two
for one table
atm it has two fields
and i want to optimise it

you can set up to 250 per table if I remember correctly

ah, thats good

optimise how ?

mysql is awesome
make it faster for a particular query

wordid

and back

then index it one whatever that qwuery queries
*on

(basically an fast associative away)

*query
beh

ok

will you be doing exact searches, or partial matches ?
on the word

this is exact searches for now
how does this change things?
if i want partial matches?

then set your PK to word, wordid, and set a normal index to wordid

pk?

you can’t use indexes if you don’t start at the left of the word
Primary Key == the primary index of a tahle

can’t you only have one primary key?

if you want to be able to search inside of the word list, you have to use full text indexing instead
yes, but it can contain more than one field

ok

if you primary key is (word,wordid) then the index is the table, which means the index search will return the actual results as well
that’s called a clustered index, meaning the index causes the table to be in index order

ah, ok
fun

(it’s a lot more expensive on bigger tables)
the second index (idx_id, or what have you) will point back from wordid to word, but the index only stores the wordid this time
depending on the ratio of searches (for words or ids) you make one or the other the clustered index (and the primary key)

http://www.google.com/technology/pigeonrank.html

hi
is it safe to repair a database while it’s being used?

how to connect html web page and mysql

with great care

someone help me, mysql is new for me… how to connect html web page and mysql ?

you must learn

Have anyone have installed SphinxSE mysql storage engine under freebsd ?

and there is much to be learnt

I have probs at run sh BUILD/autorun.sh

So riddle me this.
What are the advantages of say ..
IF you have a username column which you specify as a VARCHAR(255), what are the benefits of just specifying it as VARCHAR(16) if the maximum value ever entered will be 16 anyway?

you’re just enforcing business rules
lets say you’re setting up a column to store some ID number
the format is 7 digits plus 2 checkdigits
that makes 9
if you force it to 9, you ensure that at least SOME mistakes wll be icaught by the DB

Ahh okay, so any perfomance advantages?

no not performance
just Making Sense

Yep.
I get it.
Thanks.

whenever things arent in my control, i set them up as 255
like reference codes that come from other systems
cheers

Yeah, I usually just follow what I said for example, 16 char username limit, I set it in the DB as well if I don’t think I’m gonna change it on the front end later

out for lunch, see ya!

can somebody explain me the difference between MyISAM and InnoDB?

seba, yes
different engines
InnoDB is transaction safe
and MyISAM isn’t

any other differtences?

many actually
but that is the main one
myIsam is built for reading speed – InnoDB not so. MyIsam has table locking and InnoDB row locking
read up on the engines in the manual

is there a way to know what the next AUTO_INCREMENT number of a table would be after the next INSERT ?

max(autoincrementing field) + 1 ?

i dont think so

ok

http://ebergen.net/wordpress/2007/03/20/dont-reference-auto-increment-ids-outside-of-mysql/

is there any open source tool that creates java script or html when i drag and drop buttons ?
web design tool

Hi, can anyone shed any light on this ON DUPLICATE KEY UPDATE failing? http://www.sitepoint.com/forums/showthread.php?t=493596

Is it possible to convert a live database to master-master replication without downtime?

how can i group records in reultset *quarter-hourly* (for instance, basically, i would like to control the ranges in minutes)
group by hour(ts)/4 didnt seem to do anything different from hour(ts)

why don’t you just delete the datadir and run mysql_install_db ?

1 PM” is there a way to cast this into a TIMESTAMP of any

This was going to be my “learn Django” environment, so I can hose it and start fresh if I need to.

yup

Where would the data dir be located?

cereal_-_: you should store it as a DATETIME or similar

Agreed – but need to get it to something like that.

you could locate it by locate mysql/mysql probably

what disk innodb_flush_method do you use ?

Is there any way I could do this or am I SOL/
?

cereal_-_: str_to_date

I’ve been trying to do that, and it hates me…turns it all to null.
let me get the sql command I had be attempting…

cereal_-_: well it works, trust me
a few. That’s something you should choose based on the environment

not sure what I’m looking for…found a ton of mysql/mysql, but not sure which one’s the data dir.

UPDATE `news` SET `tstamp` = str_to_date(`dte`, ‘%Y-%c-%d %T’);

I use Redhat enterprise chadmaynard

Is that way off?

but, I found /opt/local/lib/mysql5/bin which has the commands I was looking for earlier…mysqld_safe, etc.
Should I have run those from here instead?

just cant seem to find anywhere if I should use innodb_flush_method=O_DIRECT or not on redhat enterprise

try locate mysql/test

nothing found.

sorta
try locate user.MYD

also nothing found.

Any ideas? Because that doesnt seem to do anything for me other than give me a null output.

1 PM’, ‘%M %d, %Y %l:%i
UPDATE table SET newCol = STR_TO_DATE(oldCol, ‘%M %d, %Y %l:%i %p’);
how about user.frm?

thanks — I must have confused myself with the output…

cereal_-_: bummer
well?

“sudo locate user.frm” also gives me no output…tried user.MYD with various forms of capitalization, but didn’t make a difference.

herm
ps ax | grep mysql | grep -v grep

0.01 /opt/local/bin/daemondo –label=mysql5 –start-cmd /opt/local/etc/LaunchDaemons/org.macports.mysql5/mysql5.wrapper start ;
0.01 sudo /opt/local/bin/mysqld_safe5
0.03 /bin/sh
0.61 /opt/local/libexec/mysqld –basedir=/opt/local –datadir=/opt/local/var/db/mysql5 –user=mysql

there she is!
kill 28; kill 774; kill 805; kill 818; rm -fr /opt/local/var/db/mysql5/*

Morning all

ok, done, except it didn’t find process 818. Now how do I re-start the mysql setup process?

mysql_install_db

This time, hopefully, by setting an admin password?

ok i asked this last night but the answer didnt work so here goes
if i have two tables, old and new, old contains 12,000 names, ages, emails and shoesizes and new contains 9,000 names, how would i copy over the details for the names in new from old

12,000 shoe sizes?
cool

rofl

you must really like feet

Someone correct me if I am really offbase with this but can’t you just create a new table in the catabase and for type do type=MERGE UNION(feet1,feet2) INSERT_METHOD=LAST; or something such as that to merge them?

i really do like feet ,
you wouldnt believe what shoe size row 2560 has

When I do /opt/local/lib/mysql5/bin/mysqladmin -u root -h dave-chakrabartis-computer.local password ‘new-password’ do I need to include the single quotes around the new password?

yah….
did you go ahead of me?????

TheNo1Yeti, i dont want 12,000 names in my new table

Not yet…tried mysql_install_db, worked when I sudo, now I’m reading through the resulting doc

i only want the details for the 9,000

Says I need to set up a new password for root, hence my question.
Trying it now…

you’ll have to restart mysqld_safe of course
you could select details with a JOIN right?????

/opt/local/lib/mysql5/bin/mysqladmin: connect to server at ‘localhost’ failed
‘Access denied for user ‘root’@'localhost’ (using password: NO)’

if you know how to do that then maybe you just need to know that UPDATE syntax supports joins as well

chadmaynard, i’ve never done joins

what’d you type to get that?

sudo /opt/local/lib/mysql5/bin/mysqladmin -u root password ‘passwordtext’

update shoes2 t1 join shoes1 t2 on t1.name=t2.name SET t1.size = t2.size, …
dom2 ^
sudo /opt/local/lib/mysql5/bin/mysql -uroot fails?

sudo /path/mysqld_safe, I get:
Starting mysqld daemon with databases from /opt/local/var/db/mysql5
STOPPING server from pid file /opt/local/var/db/mysql5/dave-chakrabartis-computer.local.pid
6 mysqld

Can’t connect to local MySQL server through socket ‘/opt/local/var/run/mysql5/mysqld.sock’ (2)

read the .err file to see why. My bet is you messed up in mysql_install_db somehow

Where’s the .err live?

ok ill try that chad

ls -la /opt/local/var/db/mysql5/
you can show me the query if you can’t get it to flow

This seems to be the problem:
4 [ERROR] Can’t start server: Bind on TCP/IP port: Address already in
4 [ERROR] Do you already have another mysqld server running on port: 3306
4 [ERROR]
….looks like there was another instance of mysqld already running?

so you already started mysqld????
yep
ok 3AM is my bed time. I have to go now.
goodbye all

Thanks, chad!
I’ll tinker some more, but I really appreciate the patient help

you are getting close! don’t give up

‘night
I know…will fix this over the weekend, I’m certain.

back to square zero
DELETE FROM cubecartstore_inventoryXX WHERE supplier = ‘techpac’ and cat_id NOT IN
Error – 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 ‘DELETE FROM cubecartstore_inventoryXX WHERE supplier = ‘techpac
i honestly can’t see what i’ve done wrong
should i put ` ` around the table name?
mysql 4.0.27

when in doubt, use `
or look up the reserved words
!man reserved words

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

azuranz, change the delete into a select…
select * from cubecartstore where supplier = ‘techpac’ and cat_id not in (123,123,123,123);
once you got it working, swap to delete
or , practice on a temporary table
create temporary table testing like cubestore;

here’s the thing

insert into testing select * from cubestore limit 100;
practice on testing
what’s the thing/

when i run the script via phpmyadmin it works
After-Import script
that line keeps throwing the error

azuranz, I’m not sure what’s going on.. maybe it preparses it .. maybe something else, but the way to find out capture tcp packets and see what it sends, binlog on mysql to see what it sends, or enable client debugging in the app.. I dunno what that EMS Mysql Data Import is..

oh really
how can i capture tcp packet
will tcp contain the data query it’s sending in plain text?
hold on
when you use a delete from query you can’t put ` ` around fieldnames right

you can use `’s around any column name/table name

i was told you can’t in DELETE statements

news to me

yeah man. by 2 people just 1 hour ago
in here
anyway i removed the “ ill try it again

insert into testing values (1);

Query OK, 1 row affected (0.00 sec)

delete from `testing` where `id` = 1;

Query OK, 1 row affected (0.00 sec)
azuranz, maybe in mysql 4

yeah
testing it now
ok think it worked
doing commandline update to test
praying this works…
cause it takes like 10 minutes to upload all

Is there a way to temporarily allow just root mysql logins so I can do an export/backup without having to turn every site offline?

http://pastebin.zentrack.net/12520
been stuck on it for 2 hours now

check mysqldump options. I believe you can tell it to lock all tables while doing a dump

i have problem i did create database gallery but it kept telling me mySQL could not locate a database called ” please check the value entered for this” how do i get this to work..

um… the value should just be gallery
you need to also tell it the server and the mysql connection port
all that should be taken care of by that initial install php script

are you issuing these as seperate queries or all at once?

how do i do that it been long time not doing this since last time you helped me was work great till my system was messed how ….

one after another
separate

ok… the cheap way is to go into your /path/to/docroot/coppermine_install/include and edit the config.inc.php file
in there you should see the database values to use
everything’s in single quotes

ok what do i do with the value ?

dbserver = ‘localhost’… etc…

k brb

just add the values
don’t just add lines

k

you should see things like $CONFIG['dbserver'] = … etc…
after the = , just add ‘the_right_value’

k
brb

TooBee can i pm you
just quickly

well that doesn’t make sense… I was thinking you were pushing the whole thing to the DB as one giant string with ; delimiting queries which some (all?) APIs don’t allow to minimize the risk of SQL injection attacks

to be honest i dont know
was just guessing
it’s sent to the database via a Mysql Gui program ~ ems mysql data import
i’ve narrowed down the problem though
in my pastebin @ http://pastebin.zentrack.net/12520
if i remove line 7 the error is resolved
i found this out cause i removed line 8, and then the error was simply thrown and quoting line 9
instead of line 8. so i removed line 7 tested and it ran fine
any ideas now? issues with line 7? mysql 4.0
it’s weird cause i ran line 7 query via phpmyAdmin and it worked fine no error

ah so it is some error in that huge query that is causing it to not interpret ; as the end of the query

i see

hmm
don’t see any obvioius unbalance parens or quotes…

neither
i’ll try by removing the extra not Likes after the NOT in
godam.
the GUI version of the program runs the script ok.
commandline executed ok
hm

hmm maybe an unescaped special character somewhere in the middle that the gui util is automatically escaping?
oh…

very weird
oh hold on
ok you know what it is
the queries too long the programs cutting it off

hehe

how can i restructure this query to be shorter

nasty… but maybe put all the values into a table and do and cat_id NOT in (select * from other_table)
hmm but that might not work in 4.0

screw it ill remove the query
what a waste of 2 hours
mysql is such a s*** language for debugging
uninformative error messages 24/7

yea, it could be a lot better

ok i found it in kwrite i see it but what do i do to input ?

well, what’s in there?

$CONFIG['dbserver'] = ‘localhost’; // Your database server

well, you put in the values you used when creating the database
dbserver can stay localhost
the only other thing that would stay the same is the table prefix

Take 123123

i think everything else your values

i am sorry i am still confuse

guys, is there anyway to do stored routine READ from server1 and WRITE to server2 ?

how do i update database

yeah. its called perl :p

ToeBee, heh, no really :p I need to update summary table hourly from table of 20million record and growing

what happens when you do http://localhost/install.php?
and you put in the right values?

where do i put the value you mean the MySQL table prefix?

usually, it’s just he default
whatever’s already there

it dose open then i put my information there like name, user pasword,etc then it asking for where the database is and mysql user and password etc..

and they are on different servers?

right

ToeBee, yes

when i put where the database but still error

so what’s the error exactly?
i can’t read your browser from here

mySQL could not locate a database called ” please check the value entered for this

well you didn’t add the database name

you asked me to create as i did last night

” == nothing entered/empty string

dang so what do i do now to make sure it there

the install.php didn’t take in whatever you put in for the db name
show databases;

ok hold

hmm well that sounds like a job for a script and a cronjob to me…

ToeBee, yeah but since it’s all SQL then why not moving it into SQL script that read and fill

show databases show database;

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 ’show database’ at line 1

laststars show is a reserved word you could try `show`

show databases;
plural

same thing

show databases;

+———-+
| Database |

well maybe you could do something with mysqldump piped to ssh piped to mysql on the other end… but you’ll still have to initiate it externally somehow. The only server-to-server communication I’m aware of is replication

shoud show you something like that, but with your dbs

now if they were on the same server just different databases it would be a different story

or mebbe it’s different depending on the version of mysql you’re using
*shrugs*

oh got it hang on
ok i see 6 row each title
i see database and gallery and others

so then for database name, you put in gallery
for dbserver, if you’re on the machine where mysql is running, you can put in localhost
for the user, put in the mysql user you created that you gave full privileges to for the gallery database

i put gallery in the install.php page right

you made sure that the prereq’s for running install.php were satisfied?
other than that, i have no idea why install.php isn’t keeping the value you enter…

k i do the best i can.. i kinda frustration….

seems to be a php issue or a coppermine issue.
either way, you’ll get quicker help on the coppermine forums
they’re pretty decent at answering questions
and i haven’t set up coppermine for years
since, i’m big on backing up

i did go there but not lot of that kind of information i am looking for.. but when you were using the IMs and helpmed me it was lot easier then this chat room
i remember you had your coppermine in your system,, with the animation stuffs.

well, if you’re putting in the right values in the right places, i don’t know what else the issue could be with install.php
afaik, when i put in the info, it just worked

i know it makeing me mad it not working and it making me crazy just trying to get it back to work ya know

but that was a long time ago on an old version of coppermine that’s been continually updated

i have the updated one then the old one

hi! I have a noob question it must be easy but i dont know how… i want to insert a row into a table with an increment item, but i dont want to duplicate it, means i dont want rows with same values but the increment item is different

sorry it’s frustrating you. not much else i can tell you

i still have the back up when i put back in there not work

well, the problem isn’t the old config, the problem will be that you didn’t back up the database

true but i pissed me off ya know but damn

if you had the old database and the old coppermine version, you could put everything in the right place and it should work
provided you back up and preserve permissions

yeah but oh wekll

and file ownerships

do you know anything bout webmin

city_state_id int unsigned foreign key(dealer_states, state_id) on delete cascade

nope

What is wrong with that line?

oh just wondering

webmin’s just a web interface mysql control panel?

hei me is talking!

`so uh
city_state_id int unsigned foreign key(dealer_states, state_id) on delete cascade
what am i doing wrong there? anyone?

what you want to do, and what does it do?

i get an error

well city_state isnt a mysql command, and which error do you get?

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 ‘foreign key(dealer_states, state_id) on delete cascade
)’ at line 4
key(dealer_states, state_id) on delete cascade
)’ at line 4

brian foreign is a reserved word you could try `foreign`

ey(dealer_states, state_id) on delete cascade
)’ at line 4
ear ‘foreign key(dealer_states, state_id) on delete cascade
)’ at line 4
oops
sorry
oh wait

i give up.. thanks for helping

I need a MyISAM table for foreign keys huh?

insert into sys_docxs (arc_id,doc_id,usr_id,rights) select arc_id,doc_id,usr_id,” from
sys_docxs as b where b.arc_id= 7 and b.doc_id = 14 and b.usr_id= 15 on duplicate key
update b.arc_id =arc_id and doc_id = b.doc_id and usr_id = b.usr_id;
tells me table ‘

hey there

tells me unknown table ‘b’ in field list
hi
why?

Hi MFOX
I’d like select 56th rows of one table with high performances , I have to say there is no any specific ID for that row – how can I do this in mySQL
?

again the question? you want to select the 56th row or rows?

I have one table with 12000 Rows
and I would select row 56

which language?

T-SQL

uhm… dont know, in delphi, c and php, i could say next record 56 times…
55 times i mean

!m arpa_ limit

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

yeah… the_wench fails
http://php.about.com/od/mysqlcommands/g/Limit_sql.htm

is htere anyother option for on duplicate key?

OK
But this is not good solution for my Project

i want to update a existing record (with an unique id, which i dont know, well i could know, but it will cost me another query)

in Limit you are going search in ID
but my table doesn’t have any unique ID

or if the record doesnt exist create a new one

huh?

there is no ID colum to specific rows number

limit operates on the resultset

I have one table with 2 colums one is FirstName and the other one is Family
only these two colums and I would like select row 56

arpa_, why don’t you have any IDs?

there is no need ID

arpa_, really?

yeah

arpa_, seems like you wouldn’t have silly problems like this one if you had an id.

so select * from table limit 56, 1

ToeBee, stop encouraging him

but It will be select all of first 56 rows , isn’t it ?

read the URL I pasted

ToeBee, you’re a sinner.

of course the problem is that “56th row” really doesn’t mean anything unless you can sort by something

ToeBee I’m saying something diffrent ok?
ok how can I sort that when my table doesn’t have any ID ?

tables have no order

what is your solution ?

well you can either sort by FirstName or by Family

)) ok BRB

…

er… um…
-_-;

is there any easy solution to my problem?
replace seems odd since it creates always a new id, and doesnt work as i spected…

megaspaz, does arpa_ make you cry too?

REPLACE is DELETE/INSERT…
So… yeah, creates new ID. What did you need?
There is no crying in #mysql

the problem is, the db structure will be updated in 6 months, since the data is growing very fast

I don’t see how that is a problem

but till there i need to put in one table a lot of information

ok

iskywalker, what exactly are you doing in this table?

but maybe a replace could do the job. the problem is very simple

What is the “problem”?

atm, for deleteing a document from a table i insert into another table (rights of documents) an entry where the usr has no right at all for this document

iskywalker, that doesn’t tell me anything.
I have no clue what you’re referencng.

so i must replace the existing rights or insert a new item with 0 rights
so i must replace the existing rights or insert a new item with 0 rights
bso i must replace the existing rights or insert a new item with 0 rights/b
i dont want to query an update for see f the entry already exists and do an update

Maybe you want INSERT … ON DUPLICATE KEY UPDATE

an insert i mean

Maybe you want INSERT … ON DUPLICATE KEY UPDATE

seekwill, hi!

yes i thought that too, but i must know the value of the key right?

no
The key is a unique or PK
index in the table
In your case, you would have the PK the user_id

yes i have a unique key on this table.

Great
I thought you were hiding from me?

No.
I was hiding my emotears from you.

We don’t do emo in #mysql either

Crap.
Heh.
So no fun in #mysql?

Nope

so seekwill

So brian

how long have you been using mysql

Tomorrow will be month two

insert into sys_docxs (arc_id,doc_id,usr_id,rights) values (7,14,15,”) on duplicate key update docxs_id
docxs_id is my unique key
the above doesnt work…

!m iskywalker insert

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

http://rafb.net/p/x0cq9933.html — Is my database design retarded?

yes

You didn’t even look.

But you said it. I can assume

Stop that.

Stop what?

Look at the URL before jumping to conclusions.
after you look at the URL you can jump to conclusions.

But that would be wasting valuable resources
ok

so much for sene of humior mandatory…

yes

you did not click.
i do not believe you.

maybe brian has a trojan

oh no i’m all out of those.
oh..

Too bad your pastebin doesn’t show counters

you’re talking about the computer….uhhh…nevermind.

What is ‘dealer_states’?

cities and states.

Why break it into a table?
It’s not exactly going to change much
Put that into _cities

Why?

Performance
No need to normalize it

I thought about that

still dont get how the reference can help me (i already read it 5 times)

And…?
Your query doesn’t match the syntax

Yeah…I don’t need the state table.
But I can’t call that table cities now.
I have to call it something that says “I store cities and states”

yes that also, but i dont know the value opf the unique key before inserting it, i must do a select first?

dealer_locations

Yeah…That’s what I was thinking.
Stop thinking what I’m thinking.

You don’t need to know the name of the key… you just need to tell it what to do

It’s creeping me out.

You are weird.

we are talking about mysql and not english, can you translate it please

You might also want to add some indexes

but won’t the database get big

Let’s talk in English first. Explain EXACTLY step by step what you want to happen.

when I have “Florida” repeated 100 times for every city

Hopefully
!calc 4×4
!calc 4*4

and “New York” repeated 100 times for every city

Stupid bot

Horrible human

SO?

And all other 50 states
I guess it dosen’t matter

There are state abbr.

Those are going to be static anyways

Are you running this database on a 386?

b,c,d) information, which together they are unique, plus a unique key(column a), i want to use the 3 information
for updating or insert a value

Use the enter key when you’re done typing…

I don’t really understand indexes.

!m brian how mysql uses indexes

brian see http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

Like…how do I know if I need an index and why?

!tell brian about book

http://www.kitebird.com/mysql-book