colID orderID dataCol I have selected all from that table with a certain colID and with orderID=1 orderID =7 This
maybe, SHOW CREATE VIEW `view`;
that works for tables, anyway
!m SettlerX joins
SettlerX see http://dev.mysql.com/doc/refman/5.0/en/joins-limits.html
funky
ergh, stupid bot
joins
http://hashmysql.org/index.php?title=Introduction_to_Joins - For more indepth info: http://dev.mysql.com/tech-resources/articles/mysql-db-design-ch5.pdf
or union, of course
!man join
see http://dev.mysql.com/doc/refman/5.0/en/join.html
one letter of number (1 or 2), ID field and NAME field.
Union… hmm… Perhaps that's it.
SELECT CONCAT(1,ID),name FROM '.PRE.'cats WHERE sc="P" && access!=3 UNION SELECT CONCAT(2,ID),name FROM '.PRE.'pages WHERE access!=2
is it right?
can i ask one more dumb question? (er, make it two — #2) how do i check what the values of innodb_table_locks and AUTOCOMMIT are?
select @@innodb_table_locks, @@autocommit; 
)
hi all
i have upgraded my mysql server from ubuntu warty to feisty
feisty comes with mysql hosting 5.0.38
i have had old isam tables, that can no longer be read
is there a way to read isam tables with mysql 5.0.38?
you did of course dump them first
If i did that, i would not be here asking the experts
i tried to migrate to anonther server (still 4.1) but it keeps running away, when I am accessing the tables
see upgrading docs for issues
ok thx
I'm probably not explaining this in the most understandable way, but
colID orderID dataCol. I have selected all from that table with a certain colID and with orderID=1 orderID =7. This leaves me with all dataCols of colID within a specific order range. I need to find all other colID with dataCol of the same value in the same /respective/ order: that is, not the
exact orderID. Any idea how such a query, if even possible, would look?
itrie to stop Mysql it says failed how can I then stopp it?
an exact pattern matcher: to find all colID with certain data in a certain order
i did mysql_upgrade, but i am still getting ERROR 1017 (HY000): Can't find file: '' (errno: 2)
I try to stop mysql but it failed what can I do to stop it?
kill -9 [pid]
i did mysql_upgrade, but i am still getting ERROR 1017 (HY000): Can't find file: '' (errno: 2)
!perror 2
No such file or directory
nog mensen die hebben in een leuke chit chat ?
the_wench: http://nopaste.org/p/afLqBV0ykb
probably an innodb table and you did not copy the innodb tablespace
when i do a "show innodb status", i see this 20-25 times: —TRANSACTION 0 984019436, not started, process no 4901, OS thread id 1160055136
MySQL thread id 19236, query id 1353401520 10.10.170.16 root
is that bad?
i really just upgraded the system
with different ids, of course
here is the fs structure
http://nopaste.org/p/aZQRLKA0I
i see the ib_data file
that should be it, right?
from what version as isam support has not been in the engines for a while
hence the need to dump from an old version first
you will need to downgrade/compile isam in
Note that mysql hosting 5.0 does not support ISAM
ok
my problem is that i did the following
stop mysql
copy the isam tables away
move them on another server
running mysql 4.1
but on that server i keep getting
MySQL server has gone away
henceforth never upgrade without reading some documentation and a backup
!man gone away
see http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
hi people, i will like to know how can i call functions like LAST_INSERT_ID() and NOW() by using query strings/?? example SELECT LAST_INSERT_ID() FROM Table1;?
i assume it's similar to COUNT(*)?
FreeNet, depends on language for last insert, now() its just that
!php mysql_insert_id
int mysql_insert_id ( resource link_identifier ) Get the ID generated from the previous INSERT operation http://php.net/mysql_insert_id
mysql_insert_id is part of the php library.
but i am using C#
so is there a way to retrieve the field just by using querying
actually it calls the C so whatver its called in your lib
so that means even if i do this, SELECT NOW() FROM TAble1; won't work right?
no need for from table
tias
Try it and see, its quicker to type it on your system and try it than wait for one of us to tell you its ok
got it
SELECT LAST_INSERT_ID(); and SELECT NOW();
mysqlcheck –auto-repair EVO … mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE … '. I think I have learned my lesson
thanks.
what can i do now?
is there a documented way to downgrade?
I would just copy the isam tables to a downgraded box and dump them
Unsupported. Period.
this is what i did, and where i am getting the above error
hi, i've got a warning with a command, how can i know what was that warning…?
show warnings;
…
sorry …
thx … :
I typed this:
CREATE DATABASE my_dbname;
GRANT ALL PRIVILEGES ON my_dbname.* TO 'my_username'@'%' IDENTIFIED BY 'my_password';
as root on a mySQL version 5, and it definately won't let that user/pass connect, do I have to do anything else?
you want to connect from remote?
first try local connection
I tried both local and remote
you think it would work right? it's always worked in the past
try FLUSH PRIVILEGES
tried that 
oh well
bye bye
flush privileges is not needed this time
i found a decent backup
copied the file and everything is fine now
thx for your help
np
To copied the file,can it using in innodb type table??
what's the command to find out which program is using a given port?
lsof
telnet
ls of -i
:-)
errr
lsof -i
man fuser
or lsof, yes
fuser -n tcp xx
Welll hello
I have a table (1 million rows) and I want to count how many entries there are for an array of Zip Codes. So what I am doing is SELECT count(*) FROM realtors WHERE (shortzip = '123245' OR shortzip = '123246' ORshortzip = '123245' …)
and I usually have about 20-300 zipcodes in my where clause
how can I get the results to come faster?
little bit faster to use WHERE shortzip IN (12345, 56789, 98765)
Do you have an index on shortzip? That will help too
yeah
I jsut index shortzip
that is the correct way.. right?
yes
ok
If you need thousands of numbers in that array you should think of temporary table with them
ohh ok… thanks salle I will read up on that
CREATE TEMPORARY TABLE temp (shortzip INT UNSIGNED PRIMARY KEY) ENGINE=MEMORY; populate it and then simply do join: SELECT count(*) FROM realtors, temp WHERE realtors.shortzip = temp.shortzip;
CREATE TEMPORARY TABLE temp (shortzip INT UNSIGNED PRIMARY KEY) ENGINE=MEMORY; populate it and then simply do join: SELECT count(*) FROM realtors, temp WHERE realtors.shortzip = temp.shortzip;
There is no such thing as explicit join defined in standard
damn I don't even have to work anymore! Just sit back and ahve you guys do everything… I really appreciate it. thanks 100x
standards schmandards
Next time someone needs help you can do it
I will do what I can… if they need help asking question… I am their man
I wanted to give access to a remote ip, but the db table in mysql database host is empty!
so my update db set host… where db='blabla' statement is useless
any ideas?
use the GRANT statements
!man grant syntaxx
Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/grant syntaxx
!man grant
see http://dev.mysql.com/doc/refman/5.0/en/grant.html
hello
i need some help about .NET platform, do you know any sepcific channel for that?
I've a SELECT SQL statement that returns results on my dev server host (5.0.38 Ubuntu) but doesn't return results (0 rows) on the production one (5.0.24 CentOS). Does anyone know where I should start looking?
If I remove the "GROUP BY …" bit from the statement, it works fine on both. But I hope I could figure out what's going on.
I am trying to get remote connections working on a windoze php mysql web hosting server and having some trouble. When I uncomment 'bind-address' and 'port' the server begins without error, but then is inaccessable by phpmyadmin. I have tried setting the
config for phpmyadmin to the IP instead of 'localhost' but that didn't really do anything.. ideas?
what are those all people do without talking?
You've compared the tables and they are identical?
alienbrain_, i'd look at upgrading
snoyes, I've dumped the exact database from the production to my dev, tried the sql host and it returned results. On production it doesn't.
does anybody know how to add ASP mobile web form templates to visual studio 2005?
HarrisonF, Of course, I'd run the latest version myself, but unfortunately it's not an option for this client (e.g. the server is not under my control and is running dozen of other applications)
HarrisonF, I'm trying to find the bug report so at least I could convince them
(if it's a bug that is)
While it could be a bug, I'd first make positively certain that the two data sets are identical (using xaprb's toolkit), and that the query is identical on both sides.
snoyes, thanks, I will look into xaprb
Is there a channel that specializes in windows mysql installations?
tookit
toolkit
xaprb's MySQL Toolkit (http://sourceforge.net/projects/mysqltoolkit/) includes tools to compare databases across servers (such as master to slave) and bring them back into sync, profile queries, and other handy features.
i'm trying use mysql on one DB and tha shell return form me
Access denied for user 'www'@'localhost' to database 'PORTAL' when doing LOCK TABLES
what is this?
Just what it says. The user 'www'@'localhost' doesn't have permissions to lock tables on the 'PORTAL' database.
Log in as a user with sufficient privileges. Change your privileges with a GRANT statement
!man grant
see http://dev.mysql.com/doc/refman/5.0/en/grant.html
snoyes, thanks man.. i will do it!
Hello, is there a way to copy a database without using mysqldump?
stop the server, then copy the /var/lib/mysql directories and files.
thanks
howdy!
Hello all
it is a known "feature" that a table biggern than 4GB is the max limit?
table size is a function of o/s
Linux 2.6.x
I guess my MySQL is compiled without large file support
it's a pretty old Debian box actually
http://dev.mysql.com/doc/refman/5.0/en/full-table.html
thanks
Linux 2.4+ (using ext3 filesystem) 4TB
I'm using ext3 under Linux 2.4
still I get the error
it seems like a pointer size stuff
Some idea about how to get a decent value from AVG_ROW_LENGTH=nnn from my dataset?
snoyes, unfortunately I don't have GRANT privilege to allow connections from my host. and can't install it there as I'm firewalled so it won't be able to connect to my mysqld.
I will continue searching the bug queues for now. If anyone could help please do so
Can you mysqldump on both and run the results through diff, or is the db far too big for that?
snoyes, I can.. will do that now
oh first thing.. my bad. The production is 5.0.42 not 24
that makes it higher than dev
snoyes, I'm positive, absolutely no changes.
Ok, can you make a small test case that shows the difference in behavior?
snoyes, I didn't understand the cause yet, so I'm not sure I will be able to identify the issue and make it reproducable, but I will try
does anyone know if mysql stored procs can be used to create triggers and other stored procs?
You cannot create a trigger from within another stored routine.
nor a procedure
Does anyone know why "UPDATE `tv` SET release=2007 WHERE id = 0000002045;" stopped to work after upgrade to MySQL 5? I've released that changing query to "UPDATE `tv` SET `release`=2008 WHERE id = 0000002045;" make it through.
hi. i noticed a new option on one of my servers. i can optimize the machine for sequential memory access, or random meory access. what would mysql be doing most of? or is it application specific?
i would assume sequential
What's a magic quote "`"?
release is now a reserved word.
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)
snoyes, thanks alot. Next question, is there something to do about that except changing queries?
snoyes, ok thanks kindly, thats what I thought.
the_wench, thanks too.
Nope.
snoyes, thought so, thanks!
Is this the correct way to do transactions in mysql 5.0.37?
http://bin.cakephp.org/view/745718243
up to now i have my stored procs or functions basically output the code for the triggers, which Ive been copying and pasting, but from the sound of it I am going to have to create a php hosting front end or something.
use "start transaction" instead of "begin"
snoyes, okay lets see if it works
Yeah, or mysql -e "call createTigger()" | mysql
Hi. I have a question about MyISAM indexes. I have a 44MM record table with a CHAR(3) column that I want to join on. There's an index on that column, but EXPLAIN says it's not limiting the number of rows in the results set
The values are not unique
asciimo, post the query and the EXPLAIN somewhere
thanks, HarrisonF
snoyes perfect!
gonna test
Hi, How do I use output of one SQL query in another, like "INSERT INTO a (b) VALUES ( (SELECT z FROM x WHERE y='q') )
!man insert-select
see http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
hey ho!
I got disconnected, so just in case… Hi, How do I use output of one sql host query in another, like "INSERT INTO a (b) VALUES ( (SELECT z FROM x WHERE y='q') )
!man insert-select
see http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
Thank you
snoyes have you used the method above?
"SELECT a.id,f.position FROM ALTRO as a, ALTRO_FEC as af, FEC as f WHERE f.position != 1 and af.FEC_ID = f.id and af.ALTRO_ID = a.id;" - I wrote this to make sure I got the where clause. I actually want to remove all of the rows from ALTRO,ALTRO_FEC and FEC where the clause is satisfied.
How2do ?
not for creating triggers per se, but for generating dynamic sql, yes.
!m hyakuhei delete syntax
hyakuhei see http://dev.mysql.com/doc/refman/5.0/en/delete.html
See the "Multiple-table syntax", and note that you have two options.
They do the same thing, just different ways to write it.
Did you catch that pastbin URL, HarrisonF?
snoyes, gotcha. Is there a way to eliminate the ——– that surrounds the output?
-B (and you'll want –skip-column-names too)
ah thanks
I still want to know what -N and -L are deprecated.
*why
hello
wich command should i use to import a file with the from file.sql?
mysql file.sql, or if already in the cli, source file.sql
does alter table mytable order by serial_number desc; stick?
ok that worked for creating the code properly…when I | mysql -pmypass dbname I get PAGER set to stdout
snoyes:don't i have to specify the table that i want to import the data to?
the thing is my show index mytable still says its sorting ascending
does a unique constraint over two fields create an index?
yes
howdy howdy howdy
ping
pong!
out!
I am selecting from a table based on a date field using the syntax field BETWEEN start_date AND end_date but am always getting an empty resultset although there are definitely entries present
any ideas?
What should I use to turn this txt(http://www.itu.int/ITU-R/space/plans/ap30b/RS42C_030507.zip) to a importable CSV file?
Put the SQL and a sample of the data that you would expect to see in the pastebin
magic?
nils_:talking to me?
You'll need to write your own parser and convert it
any recommendations one what I should use to parse it?
Any language you are comfortable with that has a good string library. I would use Python if it were me
ah forgot the quotes around the dates
now it's working
that'll do it
also, that doesn't really look like flat data. ie, I don't think it really belongs in a CSV format. Looks like the data will probably end up in several tables. Probably best to parse it and then insert it into the database on the fly
I have a 8 page explanation on the format of the txt files data. Isn't there a program where I can give it the format and it'll convert it to csv, xml or whatever..
hi.
I need to do an update using data from another table. is this right:
update t1 set field = (select data from t2 where t1.id = t2.id)
snoyes, cheers, upgrading to 5.0.44 solved the problem
alienbrain_, what was your problem?
help? 
TapouT, I had a SELECT query that was returning results with older versions (5.0.38) but returning 0 results with 5.0.42
TapouT, removing GROUP BY part of the query however, makes it work
TapouT, anyway, once we upgraded to 5.0.44 all worked fine.
hello
how do i change root's password?
davidfetter, you can start mysqld with –skip-grant-tables
reset root
See http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html
alienbrain_, archivist, thanks
um, it's currently empty string
so i can get in there
hi
how do i remove a database? deleted database and remove database does not work
someone runs mysql replication??
not works for me
Ahmuck, it's called 'drop'
does it make sense to create the columns 'first, last, name' name = 'first last' ?
BlkPoohba, rarely
BlkPoohba, I use firstname middlename lastname companyname things like that
i used to use namefirst namelast namecompany etc to organize all names together but this is tedious. now i use a serial number based on an ID in the comment, so I can do a select from information_schema and my sead database (sead is my personal tracker for all information related to columns,
tables and dbs)
i shouldn't need a column that combines the two though right.
hello, at http://rafb.net/p/dzMbCb48.html i have pasted the table description, the query i need to do, my try and what i think is wrong. please can somebody take a look and try help me with this query ? thanks
BlkPoohba, no
BlkPoohba, you can do this:
select concat_ws(" ",firstname, lastname), address, city, state, zip from mycusts;
GROUP BY stream_id, city, country
oryou can even use a view, but usually the select with the concat_ws would be enough
that is what i was thinking. thanks
Hi. Is it possible to autoincrement with a unique ID (user id, in my case)? Like, row 1: 1-1. row 2: 1-2. row 3: 1-3. row 4: 1-4. row 5: 1-5… you catch my drift.
does mysqld_safe always try and run as the mysql user?
autoincrement
http://hashmysql.org/index.php?title=Autoincrement_FAQ
see #6 on that link
d3c, yo ucan only have one auto increment column
snoyes, I'll have a look
well unless you use triggers
You can have lots of auto_increment columns if you play with LAST_INSERT_ID(expr)
not so much. You would have to "explain" the format to whatever program might exist anyway and like I said earlier, it doesn't look like a single table of data anyway
snoyes, via triggers you mean though?
Triggers are one way to do it. You can also roll it yourself with one or more update statements.
guys, please. I need to do an update using data from another table. is this right:
update t1 set field = (select data from t2 where t1.id = t2.id)
?
what do you mean by triggers?
It might work, but you can also use UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.field = t2.data;
ah
thanks
snoyes, ah gotcha.
i am a big fan of triggers
Row 17 doesn't contain data for all columns…. is there a way to bypass this?
If I select, say, mediumtext as the data type instead of varchar, it can still have numbers and symbols, right? It's not literally the 26-letter alphabet (in English), is it?
have it enter null values for it or something?
!perror 150
!perror 150
Foreign key constraint is incorrectly formed
you're good to go.
thank you, threnody
KEY crawling_name_idx (name),
CONSTRAINT `crawling_ibfk_1` FOREIGN KEY (`name`) REFERENCES `script` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
any character limitations are in your choice of charset
how is that malformed?
I see. That helps.
is it possible to create a constraint which limits the amount of rows which has a column with a specific id?
write a BEFORE TRIGGER
ah, before insert?
STOPPING server from pid file /var/db/mysql/ngdev.org.pid
and BEFORE UPDATE
why before update?
it might be close to your pid file
UPDATE tbl SET fld = 1;
ah, right
ah - gotit, thanks! In there I have: http://rafb.net/p/s6Vvmb54.html - any clue what's wrong?
!perror 13
Permission denied
"The error means mysqld does not have the access rights to the directory"
that's a clue
;^)
Operating system error number 13 in a file operation." why can't it print strerror(errno) as well?
ah I see, okay I'll look into that, thanks threnody!
now I'm getting http://rafb.net/p/w3RWsI57.html - is this permission related too?
thanks
raar most likely you did not run the mysql_install_db script
helo
please
http://www.lcc.uma.es/~bds/adminbd/practicas/ABDPract5.pdf
as serian the possible tables porfavor? of the question I number one
#mysql-es maybe?
what is the command that makes mysql suggest a table layout?
SELECT * FROM table PROCEDURE ANALYSE()
thanks
Suggest a table layout? wtf?
is there a way to group data in a SELECT query returned by HAVING? if i don't group, i get the relevant rows but i need counts. if i group, the HAVING seems to apply to the grouped data and i get an empty set which is useless. suggestions?
show your query and the result set you're going for
pb_werkin, probably want that in the where not having
the problem with where is that min() isn't allowed.
me thinks its a groupwise max miss understanding
do you want min date or a range of dates
i need the minimum date. basically this is a list of transactions, and i want to get the first order date of each customer as long as they fall within the date range. if i do having without the group by, i get good info, but it's not summarized. i need the summary of what the having returns. i
suppose if i can't do this in the query, i can build the summary in perl easily enough.
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
i'm reading through that but so far don't see how to make it apply here.
you want min instead of max, same problem though
Is there a recommended maximum size of a table? I have a table that might end up being 7-8GB. Is this too much for MySQL to handle? I can break this table into smaller sets of data if necessary.
people are working with much larger tables
archivist, great.. I figured as much, but just wanted to make sure.
It could be beneficial to separate out the data. It depends on what kind of queries you run.
hello
if i execute a 'set character set' query in one connection, would it affect another ?
i mean php scripts
maybe if you are using persistent db connections
can anyone see what the syntax error is in this? INSERT INTO images ("imageindex","manufacturer","keywords","image","thumbimage") VALUES ("","130","130","/images/130-Nexus_NEX-G_Chair.jpg","/images/thumbnails/T130-Nexus_NEX-G_Chair.jpg");
your columns are quoted.
aaargh
lol
trying to recover mysql from a power failure here - i've determined which tables are corrupt with mysqlcheck
thanks pb_werkin
do i just drop the old tables and "mysql -u -p database database_backup.sql" ?
haha!
hehe
only 95% of the mirrors
what does it offer?
Lesons - Lessons?
Or is that some sort of UK humor????
k, that max groupwise doesn't fit my situation perfectly, but it gave me an idea how to make it work. i can query the having results into a temporary table then select the groups from that. i can live with that, but i'll probably still see if i can figure out a single select
method.
er where?
Topic
seekwill dunno who added that
I thought it said archivist…
that was an update to beta version no
Over the river and through the woods.
and under the bridge by the stream
hmm grep the log to see who added it
here
Awww!
No sense of humor allowed anymore?
ergh, did I remove that too?
dang
gotta have that
apparently my UC slides have been on del.icio.us frontpage.
34k downloads in single day
hah
and domas
thats Therion
We all know that
Oh wait, reflex
more general question. i've been running 4.1.10 on centOS, overdue for an upgrade. any concerns or gotchas upgrading with the latest GA rpms? (doing a backup first, of course.)
Using a CentOS package?
the 4.1 was installed from the packages at mysql.com, not from the centOS dist.
see upgrading docs, for issues
hey. is there a way to tell if an UPDATE *actually* altered a record?
row_id: 123, col_name: foo, col_descr: hello world
if i run UPDATE table_foo SET col_name = 'foo', col_descr = 'hello world' WHERE row_id = 123
is there a way to tell that nothing actually got changed?
yes
or vice versa, that a change actually occurred?
archivist, cool, how?
!man information functions
see http://dev.mysql.com/doc/refman/5.0/en/information-functions.html
If you used the command line client…
SELECT ROW_COUNT()
!php mysql_affected_rows
int mysql_affected_rows ( resource link_identifier ) Get number of affected rows in previous MySQL operation http://php.net/mysql_affected_rows
huh… ok so if nothing *actually* changed… that value will be 0?
i knew about that function. i guess i just didn't realize it was "smart" enough to report that
who knows something about xampp and mysql under linux?
PetarM, the people in #xampp do
thanks
archivist, ok cool, yeah that page explains it clear as day thanks
Hi everyone, I have a fairly large table with a unique column. When I update a record, it takes about a second, but I'm looking to get the time down even further. Is there a better query than update table set xxx=xxx where uniquecol=xxx?
nope, updates should be fast with that one
just seems like it has to scan through the entire table to find what it's looking for
would limit=1 be of any benefit?
well, is that uniquecol indexed ?
I'm trying to perform a full text search on two tables (news and comments), but I don't know how to structure my query to retrieve the results from both tables in order of relevance
as in, did you specify, that it is unique?
domas, yes
actually, i haven't created the unique column yet but am planning on doing so
it's slow the way it is now
would a unique column speed it up appreciably?
well, an index on it - of course.
i have something like update table set xxx=xxx where a=b and c=d
have an index on (a,c)
or (c,a)
like two indexes?
like index on two columns
do both have to be unique
whatever. sleep.
With index(), there's no unique constraint. If you do UNIQUE(a, c), then the combination of a and c must be unique (a could repeat, and c could repeat, but not the same a-c pair)
snoyes, i think that's what i want, the unique(a,c)
because there are multiple a's and c's, but only one a and c
and that would be quicker than using "where" with both columns?
You would still need to use the where clause. It just makes the query go faster.
Just like looking in the index of a book is faster than going through every page and searching for a particular word.
alter table xxx add unique (a,c) ?
yep. If it's a big table, that might take a little while.
i'm going to try it right now
There's chocolate in my peanut butter!
so would my queries then still be the same?
yes
i have some duplicated
damn it
dupes
find them with select count(dupefield) as qty,otherfields from table group by dupefield having qty 1
delete dupes
If you have a unique ID and the name may contain duplicates then DELETE t1 FROM table1 t1 JOIN table1 t2 ON t1.idt2.id AND t1.name=t2.name if you have other fields that need to be taken into consideration extend the join as needed
quietly erasing those
alter ignore table will do that for you.
what happens to the dupes then
283506 rows, whew
is there a way to replace each space in a row with a %20
they get quiely erased.
i have some urls in a row
is describe table supposed to show that it's unique?
SET field = REPLACE(field, ' ', '%20')
thx
(in an UPDATE statement)
thx thx
i see key is "MUL" for userid, but not the other column
use SHOW CREATE TABLE
UNIQUE KEY `userid` (`userid`,`letterid`)
suppose that's right. not sure if it's speeding up at all
Use EXPLAIN on the query to see how it goes
oooh i take that back
the query takes 0.00 secs now
wow!
where do i put the explain though just for kicks?
!man explain
see http://dev.mysql.com/doc/refman/5.0/en/explain.html
oh, the select doesnt go slow, its the update
How can I perform a full text search on two tables for the same term in one query, so that the result set is returned together?
well thanks for your help guys, everything is a lot smoother now!
bye!
will this work?
UPDATE legacy_url REPLACE(legacy_url, ' ', '%20') FROM `layouts_legacygraphic`
once you get the syntax right, yes.
!man update syntax
in mysql can I create a index of a varchar 55 field ?
see http://dev.mysql.com/doc/refman/5.0/en/update.html
sure
hi
why do i need the set snoyes
because that's the way the syntax is written.
I have a very quick question. I need to perform 2 SET operations in one go. is there any way to do this? I've tried for example: UPDATE pages SET homepage='0' WHERE homepage='1' AND SET homepage='1' WHERE id='8' . But this doesn't work?
UPDATE pages SET homepage = CASE id WHEN 1 THEN 0 WHEN 8 THEN 1 ELSE homepage;
or possiblly UPDATE pages SET homepage = CASE WHEN homepage = 1 THEN 0 WHEN id = 8 THEN 1 ELSE homepage; If that's what you were after.
ok I think I understand most of that except for the last bit, what is the "ELSE homepage" bit doing?
setting the value back to itself if neither of the conditions match
ok cool thanks
and do both ways do the same thing? (just wondering in particular about the 'id' bit?)
The first tests the current value of id for 1 or 8. The second tests the value of homepage for 1, or id for 8, which is what you said, but I wasn't sure if it's what you really meant.
sorry yeh that's what I meant, getting a little confused, ok I got it
i cant figure out why this is wrong
UPDATE layouts_legacygraphic SET REPLACE(legacy_url, ' ', '%20')
lookeda thte syntax for update
UPDATE table SET field = REPLACE(field, …
I forgot to put the END on the examples shown. Check the manual for the full syntax.
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_case
stupid tab completetion. Sorry maxkelley. maxo, for you ^
ok thanks
thx snyoes
hi
hey… so i've got this situation where i've got an M:M relation ship, a user relates to all the states/provinces he covers
pretend it's for a sherpa web app, a sherpa can lead you on a trek through e.g. Colorado, The Yukon Territory or Virginia
i'm looking at the current implementation of this system and i'm thinking it's really pretty dumb. right now it's doing a shit load of logic processing to "figure out" which states to delete… seems to me it would be just as if not more effective to simply delete all the users states and then
re-insert the new dataset…
SELECT sherpaName FROM sherpas JOIN sherpaLocations USING (sherpaId) JOIN locations USING (locationId) WHERE locationName = 'Mt Everest'
does that make sense?
hi
i have problem
snoyes, you didn't see my actual question/scenario yet ^^
kibibyte, i have a nuclear device
kibibyte, what's your point?
I still don't see your actual question.
i set Host column in mysql table to * and i cannot login as root how to fix it
snoyes, i know how to setup the M:M tables and do the joins and whatnot
!tell kibibyte about reset root
kibibyte See http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html
snoyes, for updating what states a user covers, is it any less effecient to simply delete all the states and then insert the new dataset than it is to "figure out" programatically which old states need to be deleted and which new states need to be added
Do that, put instead of changing the password, issue FLUSH PRIVILEGES; GRANT ALL ON *.* TO 'root'@'localhost'
snoyes, right now the program is doing the latter
i have 2 server with differents databases, can i sync the 2 servers ? ie : the 2 servers have all the database : their bases and the bases synced from the other
is it possible ?
snoyes, seems highly inefficient to me, at least from a code maintenance and obfuscation perspective
Depends on how many states and people there are.
!tell Sp4rKy about toolkit
Sp4rKy xaprb's MySQL Toolkit (http://sourceforge.net/projects/mysqltoolkit/) includes tools to compare databases across servers (such as master to slave) and bring them back into sync, profile queries, and other handy features.
snoyes, erm… well, there are about 100 "states" or regions that a sherpa can cover, and about 25,000 users
snoyes, however we have a restriction in place where if a user selects "all states" we actually store "no states"
snoyes, because for our programatic purposes the two are equivalent… e.g. to our users, them not specifying a state is equivalent to saying they cover all states
snoyes, i'm pretty sure we have indexes on the lookup table, at least i sure as hell hope so
but can they do 'crossed' sync ?
snoyes, so, worst case scenario, someone is going to be deleting 99 records, and my guess is it will be rare that they check "all but one" state
That's known as master-master replication. Yes, doable.
!man replication
see http://dev.mysql.com/doc/refman/5.0/en/replication.html
k
i llok at this
thx
snoyes, so… any thoughts on this?
how do I limit the the amount of results returned by mysql ?
I tried top 100 and that doesnt work
LIMIT
m308, with LIMIT
!man limit
see http://dev.mysql.com/doc/refman/5.0/en/information-functions.html
Or just select the rows you actually want to show.
(if you're not intentionally trying to do "pagination" with them)
I can't guess which will be easier for your application. If you can drop and reload, fine.
mm-mysql, erm… yeah, what if the rows you actually want to show happen to be 10,000 of them
snoyes, i did but still i have * in Host field
how to change it
snoyes, why not? i mean. i'm just talking really basically from a code maintenance perspective. i mean think about it. A) programatically identify "rows that need deletion" *and* "rows that need insertion" and then delete and insert as appropriate or B) I. run a delete query II. run a foreach
on the $_POST['states'] array
snoyes, i guarantee option B is way easier from a coding perspective, i just wanna know if you think option B has any possible technical flaws
snoyes, is there any reason you can think of immediately that would either cause a major slow down in performance or be prone to error… i can't
archivist, have you any input on the matter?
I don't know of any problems. We use that approach in some areas on our site.
The only issues we had were when some genius used GET insead of POST and overran the allowed query string length.
When I have a FK that can be 'empty' … or the relation is optional, should I set " NULL " or " NOT NULL Default '' " ?
snoyes, ok cool, thanks. that's all i wanted to know. just basically looking for confirmation that it's not a retarded idea, and i don't think it is
If you can't log in, then you need to use the link shown to skip the grant tables. Once there, you can recreate the root user for localhost, or you could edit the table directly and change the * back to localhost.
ok i used –skip-grant-tables and it works
:P
lo all
anyone around?
NO
when you insert some information into a table, is there a quick way of returning the autonumber it was assigned?
!man last_insert_id
see http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
!man information functions
see http://dev.mysql.com/doc/refman/5.0/en/information-functions.html
"What if someone else inserts before I get to read it?" gausie asks next.
autoincrement
http://hashmysql.org/index.php?title=Autoincrement_FAQ
ohmygod snoyes is a psychic
!php mysql_insert_id
int mysql_insert_id ( resource link_identifier ) Get the ID generated from the previous INSERT operation http://php.net/mysql_insert_id
how can i use it in another INSERT query
so i want to insert something
just like you would any other function.
im not sure how
i insert something in one table, then something else in a second table. the second thing needs to contain the first's "SELECT LAST_INSERT_ID()". how do i do it?
oh ok ill just use mysql_insert_id
thanks
INSERT INTO secondTable VALUES (LAST_INSERT_ID());
So, I'm trying to migrate a 300M database, I made a dump and when I try to reinstantiate it it complains of a syntax error.
is there a way to do a bulk insert?
It says there's a syntax error on the first line .. Which is a comment ..
or do i just have to run a bunch of inserts?
The engine now detects incorrect comments. That's a feature.
LOAD DATA INFILE
snoyes, can i do that from PHP?
snoyes, is there a man page for that?
Xgc, will it stop the migration though? Because that happened after it had done a load of work.
Inappropriate comments in poor taste, or just bad syntax?
!man load data infile
see http://dev.mysql.com/doc/refman/5.0/en/load-data.html
Bad taste.
SHOW VARIABLES LIKE 'thicker_skin';
hmm shall have to grep the source
hey guys… I'm looking through the docs, but am not seeing the answer… let's say I were selecting a series of posts on a multi-author blog: how would I limit it to one (or two) post(s) per author in the same select statement?
what about doing a select statement that will return multiple rows and then using INSERT SELECT
but just doing the SELECT statement with literals
What about it?
snoyes, would that work for a "bulk insert" and how would i get a select statement to return multiple rows given a list of numbers
I'm unsure why you'd want to do that.
snoyes, as in i have a PHP array, i can build a comma separated list, or whatever… how could i get a multiple-record result set out of that and use it to INSERT
snoyes, dunno, just figure it might be faster than making anywhere between 1 and 99 separate INSERT queries with PHP
snoyes, then again maybe 99 INSERT queries is rather negligible (that's about the most that will ever occur at any given time
Well, you can do them all at once with an extended insert. INSERT INTO table VALUES (a, b), (c, d), (e, f)
ahhhh, great, thanks that's more along the lines of what i was looking for
INSERT table (foo, bar) VALUES (a, b), (c, d)
snoyes, yes no?
always
k
From a question asked yesterday: http://rafb.net/p/E2bG9j59.html
It slightly more complex, but you might find it helpful.
thanks, I'll give it a look
Slightly simpler from a question asked last year: http://rafb.net/p/g5WFhm39.html
That
That'll be easier for you to ahndle.
it is indeed. gracias
You're welcome.
There's a little (unnecessary) mysql cheating going on in that last one.
ok, now what about figuring out if a data set (a, b, c, d) is NOT the same as another data set, e.g. smaller or larger (a, b) or (a, b, c, d, e, f, g)
i just want the boolean result… whether or not data set A is the same as data set B
SELECT (a, b, c, d) = (a, b, c, d, e);
i've got data set A existing in the form of multiple rows in a M:M lookup table, and i've got data set B existing in the form of an array in PHP, which i can convert into any format
snoyes, aha! perfect. i was wracking my head to think of some massive LEFT JOIN or something to do that. classic case of overthinking the problem
lol
haha, I was typing out a LEFT JOIN answer
snoyes, hmm… well ok thing is though, remember the form of the data is a factor though
that is a row by row comparison. If you need full sets, you'll have to go with a join and count the results where the join failed
snoyes, i would need to be able to "select" the data set from table A into a comma delimited list
snoyes, data set B can easily be (and already has been) made into a comma delimited list via PHP
Well, you could do that. How big it the array?
select fields from (set A) LEFT JOIN (set B) on conditions where set_B_key IS NULL
snoyes, as i said, max size for either one is going to be approx 100 vals
You could select the values from MySQL, put it in an array, and use PHP to compare the arrays.
snoyes, yeah i'm trying to avoid that
snoyes, well i would prefer to avoid that…
You could calculate the MD5() of both sets of data and compare those two.
You could just compare a delimited list, as you said.
snoyes, hmmm… i suppose that's an option, as in SELECT MD5(SUM(column))
snoyes, the issue is getting the rows in table A into a "delimited" list format just using SQL
I'd do MD5(GROUP_CONCAT(column))
snoyes, ahhh right, yeah that'd be better
snoyes, only thing is i don't have any way of knowing if the concat order for the DB would be the same as for the arryay given to me by PHP
or skip the MD5, for such a small value.
make it the same order then.
group_concat supports a sort I think
though, actually i suppose i could array sort and ORDER BY
GROUP_CONCAT supports a sort, and it's easy to sort an array in PHP
indeed
how would I select all the results with a distinct or unique title ?
ie no dupes
hmmm… yeah i think that's the best idea so far is the concatenation idea
SELECT DISTINCT title FROM table
inviso, problem is i need to know either way, i need to know if it doesn't exist in B *or* if it doesn't exist in A
inviso, i just need to know if they're different
!man group concat
see http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Do you want to suppress those that have duplicates or only show the distinct list of all titles?
erm, that doesn't seem quite right
!man group_concat
see http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
yeah, you'd have to union the two directions to do it that way and I didn't realize you were working with row sets. What snoyes already came up with is simpler
distinct list of titles
thanks
See snoyes comment.
the mysql documentation sucks
I think the documentation is great, but the search sucks.
its easier to just simply say, "group by title"
the site looks like shit and is organized poorly
is there a multiple group by ?
rather group only if all 3 values match ?
GROUP BY title, author, isbn
ty
Is there an application out there to help assist in setting up dozens of databases for replication ?
dozens?
use postgres?
with pgcluster
As in creating new databases, or as in copying them to another server to begin replicating?
sorry mysql guys.. mysql cluster isnt ready.. and replication is crap
yes dozens
and no i am not going to use postgres
as in the entire process to get replication going
is there some way to clone a row minus its key?
But are you asking about existing databases, so you just need to copy them to a slave, or you want something to create a bunch of dbs first?
blobaugh|ct, select… insert?
i am hoping there is an easier answer
just copying already existsing ones to the slave.
mysqldump | mysql -h address.of.slaves
!man replication
see http://dev.mysql.com/doc/refman/5.0/en/replication.html
blobaugh|ct, what were you expecting? i dont know of an easier way
im not really expecting there to be an easier way. just thought i would ask to see if anyone knew
would for sure if there was
!man insert-select
see http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
That's about the best you'll get.
i thought so
blobaugh|ct, not sure if you know about this.. but when i say select… insert… its all one command
blobaugh|ct, look at the docs about it
There are other ways to do it, I don't know that I'd call them easier.
yes i know how to do that HAIDEN
ok cool
this is gonna be ugly. there are a ton of columns. good thing my programming can handle it after the initial query it built
You can use the information_schema to generate it for you.
SELECT CONCAT('INSERT INTO table SELECT ', GROUP_CONCAT(column_name), ' FROM table') FROM information_schema.columns WHERE table_name = 'table' AND column_name != 'id';
hey all… I found a bug in MySQL, I need someone to confirm it. I'm selecting from a view using the full namespace and it tells me "Incorrect table name" — query follows
Impossible. There are no bugs in MySQL. The code is perfect.
select database.table.field from database.table where id = '123456';
if i want to move a column from after a column to before a column, how would i do that?
!man alter table
see http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
however if I use select table.field from database.table where id = '123456'; it works
But remember, it should much matter what order the field is in.
do i have to alter table tblname change col col varchar(30) after col2;
yeah, or use modify instead of change so you don't have to list the name again.
i'm trying to load data into a table that i created
works fine here (5.0.42)
I'm on 5.0.19
are you selecting form a view?
yes
odd
any recommendations then?
http://rafb.net/p/uPvyUO75.html (example)
Is that query you show the view definition, or how you're selecting from the view?
that query is selecting from the view
the view definition is quite large
I win jcornelius, http://bugs.mysql.com/bug.php?id=18444
In other words, keep mysql up to date
hello!
And if that isn't the exact bug, I still suggest you upgrade to 5.0.23 as it may have been fixed in the same patch
err, .25 that is
does a view qualify as a stored function?
No, but it's possible that the same bug caused both.
yup
quoting the bug comments "It seems that you can work around this bug by fully
qualifying not only the name for the stored function but also the name for all tables
involved in the query"
and you're way behind anyway
that's actually the opposite of my issue
views in old 5.0 were quite buggy
some issues with indexes &c
it fails when I *have* fully qualified the tables
you probably want to upgrade anyway
Well, given that someone on a newer version of 5.0 is not having the same problem and you're on a very old version of 5.0..
yea I'll try the upgrade first…. and report my findings
I'd like to give question. I don't understand why mysql return me difference values? website: http://pastebin.com/m5d9cb92d
thx all
snoyes, will that actually work?
Why wouldn't it?
*shrugs*
guess it would
in both examples there is article 0002 have different values - question: why?
MySQL is generous in allowing you to put fields in the SELECT list that aren't in the GROUP BY and aren't aggregate functions (SUM, for example). However, it won't guarantee which row you get in that case.
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
There's a variety of implementations and discussion there.
wrr in article 0001 of course has dealer = B, on second dealer = A
ok, I'll read it
but its strange
you suggest that I have don't use it ?
Not really. Try doing it by hand - take the table and put it into groups by article id. Now, if I say which is the dealer for article 1, which one would you give me?
ok, I'll test it
To put it another way, consider that the various fields in the SELECT list are independent of each other. Now, imagine you'd done just SELECT article, dealer FROM show GROUP BY article. Why should one particular dealer appear over another?
is there a way to execute a query that selects everything EXCEPT a list of columns that arent needed?
sorry, but I don't understand what do you say
did you say
Only by selecting all those that are needed.
why there are independent ?
Although you can build such a query using the information_schema.
darn
Because they are. SELECT a, b, c, d - none of those fields affect any of the others.
SELECT CONCAT('SELECT ', GROUP_CONCAT(column_name), ' FROM table') FROM information_schema.columns WHERE table_name = 'table' AND column_name != 'id';
can 'id' be multiple tables?
if you change it to AND column_name NOT IN ('id', 'other', 'tables', 'here')
You can take the result of that query and pipe it back to mysql if using the command line client. Or you can store it in a variable and make it into a prepared statement from within a stored procedure. Or you can just use it as the sql string for your next call to mysql_query if from within some
application.
However…
evil
ok, thx
evil is clearly defined at http://www.parseerror.com/sql/select*isevil.html
The same arguments apply ^
gnome-term
heh
http://ebergen.net/doc/select_star.php
wow, the linux version of mysql-query-browser has problems
i think this may be easier to just build the query in php
ui think this may be easier to just build the query in php/u
can anyone help me give access to I can use the MySQL Admin client to connect to the server?
Can anyoen give me some reason why to upgrade from mysql 4.1.2 to 5.0? trying to convince boss
snoyes, can i not do a nested query like SELECT `Field` FROM (DESCRIBE tablename);
mugger, I believe my biggest frustration with 4.1 is that there is no innodb support, but that could just be my server
not from describe. you could from a subquery against the information schema.
oh ok
4.1 supports innodb
mugger send him the change list for 4.1 after .2
ok so whats wrong with our host then 
can anyone help me give access to I can use the MySQL GUI Admin client to connect to the server?
you're kidding, right?
or did i just badly misread that?
what
|__rb___|, give you access to one of our servers?
NO!
hey take it easy
ya, give me access to your server
lulz
is that a question
im trying to figure out your question
I need help using the Mysql GUI
ok i use it to, what is the problem
I can use it to connect to one of my server
it says permission denied, I know I have to give permission from whatever IP im' coming from
just don't know how sorry guys I'm sure you can tell I'm a newby in mysql
!man grant
see http://dev.mysql.com/doc/refman/5.0/en/grant.html
kewl, thanks, and I you wanna give me permission to your server, that's fine too
ping
I used to play tournaments. Haven't done that in a few years.
hah
you referred me to http://rafb.net/p/g5WFhm39.html before, and that was definitely helpful, but I have a follow-up … is there a way to make the count go in the opposite direction?
how do i compare two tables to see which column value is not common between the two?
Ping Pong is a very fast sport.
ORDER BY usually works.
He ran away.
good god
there's no information_schema on our host database
because it's 4.1?
i have no idea
its 4.1.22
there ya go
ugh so i'll have to use describe and then some sort of foreach statement in php to exclude certain fields from a query
i hate my host
dumbshoes!
can mysql change data with a regex similar to preg_replace in php? im looking at the regexp page, and im not quite sure. looks like now?
better not, it would generate ginormous queries
blobaugh|ct no
hot damn, 45 is GA
woot, bug fix reading time
ok, read the guide, too complicated, anyone with easier exmaple,
hi
can i restrict SHOW COLUMNS with a regular expression?
I have a set of data I am trying to put in a mysql database. The dataset is very large (~8 gigs). What would be the best way to format the inserts? INSERT INTO table VALUES (data row), (data row), etc.. or a ton of full insert commands?
multiple at a time , put indexes on afterwards for more speed
archivist, is there any limitation on how many I can put in one insert command?
I go upto max packet
in 5.0+ you could do some information schema query
where can I see what my max packet setting is?
it's arjen!
show variables
litheum, our crappy host only has 4.1.22
http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html
thanks archivist. got it.
cool, wasted a couple hours on this now
i just need to select all fields except for a provided list of exceptions
is it possible to move a database from one server to another?
depends normally yes.
mysqldump might be your friend, with a lot of data you might prefer to do it on filesystem level
how do you recommend me to do it? I just have to move my mediawiki db from this pc to another
oh, ok
thanks
mysqldump abopedia; return an error
it's a standalone tool, you invoke it from your normal shell
nils_ no sorry, yes I know realize
archivist, if I incrase the max_allowed_packet, should I also increase the bulk_insert_buffer_size?
i need help optimizing a query in mysql 4.1
i let it run for like 45 minutes while i went to get lunch and it still had not completed
and its a monster of a machine
i think it has something to do with subqueries
explain select distinct(account) from accountlog where ipaddress = any ( select distinct(ipaddress) from accountlog where account like 'ajaxstaff' );
well, - the explain
essentially, this table is a log of all account logins
i want to pull all the ipaddresses that were used to login to ajaxstaff and see what other accounts they logged into
theres only 438k rows
if i do a user with 1 ip address and use = instead of = any it takes .83 seconds
so id figure even with no optimization at all
the worst it could take would be .83 * number of ip addresses
which in the case of ajaxstaff is 22
Mark`: try this - explain select distinct(account) from accountlog as a1, accountlog as a2 where a1.ipaddress = a2.ipaddress and a1.account = 'ajaxstaff'
okay
i mean theoretically the subquery should be faster right? but i guess i cant complain if im not using 5.x
I've found them to be slower in most cases
well
i think your example worked perfectly
thank you very very much 3
excellent
i didnt know you could join the same table
aytime
*anytime
I only recently discovered how to do it properly
im not really a mysql guy, i just put all our logging in mysql to avoid the disk io for local file writes
ahh
only takes 15 seconds too
much better than i though
t
nice
Hello.
To disable binary loggin I edited /etc/mysql/my.cnf and commented out the lines about log_bin, expire_logs_days and max_binlog_size. Was that the right thing to do or did I do something stupid?
why disable it? it is important for recovery
hmmm
I only thought they were used to replicate the db from a master to a slave
I only thought they were used to replicate the db from a master to a slave
also, put it on a separate disk, so that you can recover data if disk crash
heh
I can't do that. It is a remote VPS
they are only required for replication
if you want to do some kind of point in time recovery you can use them for that but mysql can function with them turned off
They may be needed if MySQL crashes for example?
no
they won't be needed for that
From what I saw it records every query executed
the iblog files are used if mysql+innodb crashes
Where are those files located?
the iblog files?
If you know.
Yes.
typically in the datadir
they have nothing to do with the binary log
or log-bin
they are innodb specific
So I'm cool then?
um sure
um?
ask seekwill
summon seekwill
If you are there and see this, is it safe to disable binary logging?
it's safe
ok thanks ebergen ^-^
I'm curious if disabling binlogs boosts performance Lets do some stress tests
Hmmm.. same performance
hi
what should i put on the default of a datime field for it to be the date at the moment of the insertion?
you cannot. change the type to timestamp.
oh ok, then i can put now() ?
you cannot use funtions for default values. the timestamp data type will automatically insert the current date and time on insert and update. read the manual on its use.
byou cannot use funtions for default values. the timestamp data type will automatically insert the current date and time on insert and update. read the manual on its use./b
Bug reporting - hmmm… but what do you think about it?
