file for table -database-fake_table I dont get it I just repaired it with myisamck which is I thought the recommended
utter fun is is stock check while company running so counts dont match (keep separate data)
and work planning for order to cutting to out the door
Giddion55, I had a temporary job at a steel stockists, 30 years ago
cool
atm we have 3 managers all with different versions of what we have in stock :/
so ive been asked if i can make a stock control system that looks up a DB for all the data
that sounds normal for a small company, keeping the db up to date will be fun
ye, im building the update bit into the program
thats where c# comes in
bought software for that market it probably $$$$$$$
ive already made a program for orders, but its very messy (the db is) the c#s is sound though
yep big bucks
very actualy
I have a question - I need to export just one table - how would I do that?
do I use mysqldump or what?
mysqldump or select into outfile
ok - mysqldump got it
can you give me an example of how this would look
…sql
/path/file
ok so I need the period in there that is the problem then
or no?
!man mysqldump
see http://dev.mysql.com/doc/refman/5.0/en/client-utility-overview.html
cant he just use the mysql administrater gui and backup the table from there?
no gui - I am blind
he can use all what he like
if i want to delete a table that is over a gb - truncate is the fastest way to do that right/
truncate or what?
Hello Room
I m in very bad condition.
hello JoelSolanki
Hello Giddion
hemeroids?
no
thats a bad posittion
let me explain what is the problem.
Do you want delete table or truncate it?
what max length can mysql passwords have?
there are ServerA and ServerB
by mistake i scp /var/lib/mysql/dbname from serverB to serverA
JoelSolanki, do it in 1 big statement mate
ok got it.
lol kk
this room has that effect
now i need to get that original database backup from serverA
is there any possibility.
i m using mysql 5
can we get the original backup of ServerA
JoelSolanki, only you know that
means ?
we cannot guess your backup status
no i didnt took the backup.
so i want to know is there any location where database resides in memory
hey everybody .. is there any way I could make a mysql pipe ? .. like .. I'd want to echo to a file ..and that file to pipe the data to a mysql connection
any help plz
is there a way to get it sir?
I dont think so
oh.
then i have lost the database
i m going to be fired.
not having backups should be a sacking offence
why not pillage while you're at it
hello
I have a problem with a query crashing my computer
it does it because it uses up all my ram, all 2GB of it
anyone think they can help me figure out why?
I will put the query on a paste page
sorry about my disconnection earlier .. anyone can help me with that mysql persistent connection ? ( pipe )
http://sh.nu/p/21783
anyone who can help me with that?
I think they're all sleeping
can you also post an EXPLAIN output there?
can you clarify about crashing your computer?
does mysql server crash, does the OS crash?
if the latter, that's rather odd. what OS would that be?
if mysql server, then it is definitely a bug. db server should never crash. the mysql server's error log will contain some info about the crash which you can use as the basis for a bugreport. there's also info in the manual about tracking down crashes.
or are you just not waiting for completion
quite. people say "crash" on all kinds of different things
if the server eats up all your ram, you probably set some server host params incorrectly.
what happens then is it eats it up, Os starts swapping, then the OS will kill the mysqld process.
that does not crash the OS, however.
when asking a q, it helps to hang around to see if anyone bothers to help… I have to go now.
arjenAU, sorry, had to leave for a few minutes
bah
_tfr_, I will post explain
and as for arjenAUs question, my OS locks up
and there is nothing strange with that
ram gets filled up, and I have no swap
then your mysql buffers are too large
ok, I will lower them
but I would still appreciate if someone could take a look at the query, see if I could improve it somehow, I will post the EXPLAIN shortly
http://sh.nu/p/21784
hm.. maybe ram gets filled up because I have set mysql to put its temporary files on a ram disk
but the temporary files shouldn't get 2GB, should they?
let me check if thats what happening
rework the sql hosting to reduce the sizes
eg fix the join order smallest to largest and straight_join
nope ram disk doesn't get filled up
archivist, ok, I think I'll have to read some documentation to understand that, I've only used left joins
but I'll look into that
hey guys i have a problem while connecting to mysqlserver 5.0.41 from another computer, its makes me wait for a long time and then only connect to the database, what might be wrong?
reverse dns check
fix or disable
I have a database I cant drop "dbrman-1"
it keeps saying syntax error
drop database dbrman-1
it says it has a syntax error near -1
any ideas?
“ around it
you specifically mean the backquote?
` versus '
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)
that worked flawlessly
much appreciated
like `mysqlfront' in windows is there any front end available for php mysql web hosting in debian ?
!mysql front end
Blush, an unexpected wench error, manual section !mysql not found
help
!m [name] [function] !man [function] !m41 !m50 !m51 !man !mint !man41 !man50 !man51 !manint !manmt !manndb !manqb manwb see aide (fr) hilfe (de) ayuda (es)
I have a bunch of answers in a table with an answer_id, exercise_id and a tstamp field… I need to get a list of the exercise_ids (each id only one time) sorted by when the first answer was submitted. How do I go about creating this query? I cant "order by" tstamp once I've group by'ed the
exercise_id
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
replace max with min as appropriate
I dont need the min() tstamp.. I need the exercise_id for the row with the min() tstamp
I know. See those links, and use min when they tell you to use max.
any mysql front end package available for linux?
gui
GUI tools can be found at http://dev.mysql.com/downloads/gui-tools/5.0.html phpMyAdmin at http://www.phpmyadmin.net/ and even navicat or http://www.webyog.com/en/
hi
Is there a way to detect bad characters in a database?
Like ISO chars in a UTF-8 database
I'm afraid not, but maybe I know nothing
thanks…. phpmyadmin is working fine
if i make changes to a replicated database will those changes be undone when its updated or does the update only contain new changes?
Meaning you're changing data on the slave?
That data will stay as is until some other query comes along to change it. Be careful doing that; if you alter data such that a query succeeds on master but fails on slave (or vice versa), replication will stop until you correct it.
hm.. managed to improve the situation somewhat
not it only went up to 600mb of ram instead of 2gb
now*
basically, i want to replicate a production database to a development server, but i don't want the changes made to be erased
Anyone for that charset problem?
you could maybe use the hex() function to get the hex value of the characters and look for anything out of range of your current charset
why not use mysqldump for that?
archivist you around bud?
yes but at work
ahh ok ill throw the question to everyone then, my pc crashed and i lost our convo earlier
can anyone help me with what tables and fields ill need to make a stock control system?
I only gave you a fraction of your needs
and set logging on on your client
done
I have a query that returns a bunch of rows containing an ID and a timestamp, for each id there are multiple timestamps. However, I'm only interested in the latest timestamp for each id
how would I tell it to only select the latest timestamp for each id?
select id, max(timestamp) from table GROUP BY id
ok, I'll try that
great, thanks
ToeBee, thanks, that saved me 300mb of ram usage
heh
SQL is kinda sexy that way
Giddion55, see www.archivist.info/tabs/mnemonics.sql see rh cols look for mnomonics statring with gaper etc second letter is a new table e is sop etc this is not a working mysql system but does have the basic req fields for that sort of app
uGiddion55, see www.archivist.info/tabs/mnemonics.sql see rh cols look for mnomonics statring with gaper etc second letter is a new table e is sop etc this is not a working mysql system but does have the basic req fields for that sort of app/u
Anyone here familiar with the mysql C api?
just ask your question
ugh glibc takes a while to compile
hi
is there a way to convert binlog to ascii ? I use binlog and I need to debut my sql in the past
Hello
how can I right join on field to a commaseperated list like if I use " IN ( 1,2,3 ) " when making a WHERE clause?
jaypipes, thanks very much for the webinar 
anyone has done some benchmark of MySQL on Linux versus FreeBSD 6.2? .. I read that freebsd 6.2 performance as a lot better than previous versions. Now, how will mysql compare on this freebsd 6.2 versus linux 2.6?
welcome! it was a fun one, eh?
yeah, I learned a lot
what max length can mysql passwords have?
which MySQL version?
5.0.41
I have a 'date' field and im trying to enter a string '2007-07-17' but it isn't going through - why?
hmmm. 41? I think…
so 16 is ok
yep.
http://dev.mysql.com/doc/refman/4.1/en/password-hashing.html
because I want to limit the password between 5 and 16 chars
thanks
and I think for username length is 16 chars
Let's see the query, and the error if there is one.
!man mysqlbinlog
see http://dev.mysql.com/doc/refman/5.0/en/client-utility-overview.html
^
ok …
np
thx
Not sure what your question really is. Can you explain what it is you don't know how to do?
And what happens? An error? Row saved, but the expires field is something else?
row saves, field shows up as 0000-00-00
die() in php doesn't give any output so I assume no errors?
I have a list of ids - say 1,2,3,4. In a table with id=1, somevalue=a, id=3, somevalue=b I want to select somevalue and right join this with my list from 1-4 and get the result a, null, b, null
http://rafb.net/p/B957Pm29.html - On startup if it doesnt get a connection it should sleep for 5 secs then try again, but it seems to be consuming hundreds of connections and not closing the failed attempts:
Any ideas?
Hm. I can run that same query here and get the correct value. Are you sure that's the query that actually gets sent to MySQL?
perhaps post the results of SHOW CREATE TABLE job_listing;
Ah. It would be easiest to load that string of numbers into a table (temporary if you wish) and join against that.
Does your log show errors?
I am trying to get back unique records that are distinct based on two fields.
SELECT DISTINCT field1, field2 FROM table
how do i do select from table A where it has no children in table B where A to B is one to many
a not in b
SELECT a.* FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL;
is it left join on null or something like that ?
thanks.
Hi there, I've got a mysql server that I can connect to when im on the same local network as it, but as soon as I go outside of the local network it doesnt allow the connection to the server. I have made the host for the user as '%'… When i first tried connecting it gave me 10060 error but
I changed my firewall to allow outside IPs but now it gives me 10061. Any ideas why i could not be connecting still?
Is your router appointing to your local IP as dmz?
I'd guess only port forwarding
(Looking this up for a client)
Does this happen only with mySQL-server or everything, such apache?
only MySQL
try appointing dmz
Does all the traffic for mysql incomming go through port 3306?
yup
thought as much
There be nothing else other then the firewall stopping me?
external
remove bind-address= and skip-networking from my.cnf and grant permission to the external 'user'@'host' and remove any firewall rules blocking port 3306
wow busy
does a mysql cell have retrievable atime/ctime data?
no
add a timestamp type
is that like an index but with a timestamp?
its a field type
I mean, is it "filled" by mysql itself?
yes
either on update or insert, up to you
ah can you make it monitor only certain fields in the row? or does it check the entire row?
row based
ok, thanks
unless you add a timestamp per field.
But then you'd have to add a timestamp per timestamp.
hehe
And then what if you want to monitor those/
audit trail ftw
And that's only for ctime. If you want atime, you have to do all your selects through stored procedures.
hmm.. i just tried the mysql client as a user beside root..
(yeah.. I run as root a lot 
)
and I got this weird can't connect to local server through socket error
I have no idea what that means..
i am calling mysql -p
can you still connect as root?
yeah
that works excellent now
or rather, has always done
Is it on localhost, or some other host?
it is localhost
Can't connect to local php mysql web hosting server through socket '/var/lib/mysql/mysql.sock' (13)
!perror 13
Permission denied
sounds like mysqld is running as root
hmm aha..
how do I fix that?
which is wrong wrong….
yeah I can figure out that it's rather bad
the 0 here means root, huh?
0
hmm.. not so readable without headings
ehm..
root root root root 4 mysqld_safe -
mysql mysql mysql mysql 4 mysqld
it seems there are two mysql?
mysqld is running as mysql:mysql thets correct
I want to join cgs_order with cgs_payments but cgs_order doesn't always have a corresponding row in cgs_payments, so how do I do the JOIN?
LEFT JOIN
okay.. hmm.. what could e wrong then archivist ?
what permissions are on the sock file
ah.
thanks
I'm going to take a look
it's rwx for all and owner and group are mysql
should I try to add this user I am trying to log in with to the mysql group?
is that "the right way" to do this?
okay.. well, it seemed to solve the logon problem at least
hey, anyone can tell me if my table 23G i need 23G more to repair it?
guys?
with a REPAIR TABLE statement?
with myisamchk
grant all on db.prefix_* to …?
avip__, no, it should repair in place
hrm guess not, oh well
you can use % wildcards
since % is a wildcard in SQL, not *
ok
i've always seen db.* or *.*
don't think % will work there either
doh, manual says {tbl_name | * | *.* | db_name.*}
well * means all objects under there
ok
but for example, you can do `db%`.*
for all databases that start with 'db'
but it create .TMD
_ and % are allowed for db names. not for table names.
how big it can be?
That is, they are wild cards for db names, but not for tables.
what repair options are you using?
-r
myisamchk -r tablename
ah –quick doesn't require it
avip__, read http://www.mysql.org/doc/refman/4.1/en/myisamchk-memory.html
it talks about disk space
good morning all. i've got an index question i can't figure out. http://www.slexy.org/paste/3618 i've tried all possible combinations of an index on m.date, m.id, and m.deleted and i can't get rid of the using temporary/filesort. is such a feat
possible?
fatpelt, (deleted, date) didn't do that for you?
*checking*
you might need to remove the index_deleted first
that's one thing that i didn't do…
did it work?
index_deleted is already (deleted, date)
hi, how do I make relations in mysql, eg. I got a table for users and one for groups and a user is a member of 1 or more groups?
!man select
see http://dev.mysql.com/doc/refman/5.0/en/select.html
Presumably you have a third table, mapping user to groups. SELECT * FROM uses JOIN userGroupMap USING (userId) JOIN groups USING (groupId);
snoyes, then it is not possible to make an "array-like" field in the user-table which contains the groups for the current user without making the function manually?
!man group_concat
see http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
It's possible, but not a good idea.
why?
it has to do string searching, which means no indexes can be used. A third table is the standard way to do it, and since you can put indexes across the relevant columns, is much faster.
You can retrieve the data in whatever format you like, using group_concat as fatpelt mentioned if you wish.
s/would/would not/ oops
If I were to set up a database with two tables, categories, and posts, and I wanted to frequently display all of the posts in a category, would that be the most efficient way to set the DB up? (If it's confusing, let me know and I'll clarify or set up an example db)
ok, if it's faster it's argument enough for me, but wouldn't it be possible to contain an array of id-numbers that refer to the group, which would avoid the problem of string searches (presumed that it's a true array and not a string array)
*duct
MySQL doesn't have an array type.
exactly as you say with some indexes
which means that the only way to get what I describe is to fake an array with a string?
or do it the right way.
true, I see why a third table is preferable
i want to do X in language Y and the easiest way was to explode off a ',' character or something)
If you already have a comma delimited list, or array, or something, that can be turned into the third table, and you can turn it back when you select the values.
fatpelt, you might be right on that one, but what I want to know for sure is why the solution is better too
snoyes, well I got nothing atm
well, what happens if you remove a user from a group? with the array type of thinking you'd need to search the string and remove the one, then rewrite the string back. with a separate table, you just remove one row
KR-data: the database will be MUCH faster at finding the one row due to indexing than trying to search a string and splice out a portion of it
fatpelt, hmm good points, I'm quite convinced
KR-data: let's say you chose to implement it using the SET data type… what happens when you add a new group? you need to modify the schema of the table(s) and setting the SET to not have the bit set instead of just inserting one row
I'm using MySQL Query Browser on Windows to send a LOAD DATA INFILE command to a secure linux web hosting mysql server. Problem is that it formats the path before sending the query. "/" becomes "\" and the file is not found on the remote server.
Suggestions?
Isn't SET an array type?
use LOAD DATA LOCAL
I'd argue it's more a struct than an array.
okay, actually i am using load data local. The command works. But not from query browser
in my mind a set is an ordered list of non-dynamic items whereas an array is a non-ordered list of dynamic items
mksm, scp the file to the server, then load from there.
Or perhaps a glorified bit map
Oh I see what you mean
the file is in the server already
the file is on the linux server already? Then you DON'T want local.
mksm, LOAD DATA INFILE should work then…
okay, removed local and worked. Thanks
I can't wait for the pizza next week…
When a LEFT JOIN doesn't match, I get NULL in the joined fields, can I change this? So instead of NULL I get 0 for example.
snoyes, does this seem sensible for the third table? "CREATE TABLE `grouprefs` (`userid` INT NOT NULL, `groupid` INT NOT NULL, INDEX ( `userid` ));"
i've got an index question i can't figure out. http://www.slexy.org/paste/3618 i've tried all possible combinations of an index on m.date, m.id, and m.deleted and i can't get rid of the using temporary/filesort. is such a feat possible?
how bout in your select using IFNULL() ?
KR-data: Will you ever need to find out which users are member of a particular group? If so, you'll want an index on groupId too.
!man alienbrain coalesce
Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/alienbrain coalesce
!m alienbrain coalesce
alienbrain see http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html
snoyes, not often but the situation will occur, ok I'll add the index, anything other than that?
fatpelt, I'm trying to match the NULL in IN(). Example: SELECT …… FROM …. LEFT JOIN … WHERE status IN (1, 2, NULL)
snoyes, thanks! will check
KR-data: You could use UNIQUE(userId, groupId) instead of index(userid) if you wanted to be sure not to repeat user/group memberships, but that won't make much difference to performance at select time.
Other than that, the structure looks ok (except you'll probably never have a negative number, so you can make those fields unsigned, and maybe drop down to a mediumint or smallint and save a few bytes)
snoyes, well those checks will probably be performed by my php, but on the on the other hand an extra checkup never hurt
If you add the unique field, then you don't have to bother checking if a user is already part of a group - just INSERT IGNORE it, and if it's a duplicate, no worries.
*unique index, not unique field
snoyes, awesome! bunch of thanks!
IFNULL, like fatpelt suggested, would do the same thing. COALESCE just allows a list of arguments instead of only 2.
snoyes, I lost the thread with the unique-thing, how should I make the unique?
Hi everybody
I have a little SQL problem
and i tried solving it during the last hour x) and i failed :p
CREATE TABLE…UNIQUE(`userid`, `groupid`), INDEX(`groupid`));
I think it's pretty simple, it's just about 1 table and 2 fields
I have a `login` table, and two fields `account_id` and `last_ip`
snoyes, ah ok thanks a lot for the help
It's for an anti-cheat system, and i want to detect the IPs that have more than 1 account
and i want mySQL to send me back the IPs that have more than one account AND the assosciated account_id s
1;
I can do it using an IN and 2 request, but that takes 15s
I try
What does [BLOB - 15 o] means ? x)
That phpMyAdmin is a pain sometimes.
XD
I'm trying with php
we have left-overs, i'll save you a few slices
Awesome!
Just keep it at your desk
hu.. how to get rid of these "blobs" ? x)
will do!
a gym usually helps with misc. blobs
a gym ? XD
If it's a joke i don't get it :p
eat less, exercise more
!tell DarkM about dupes
DarkM find them with select count(dupefield) as qty,otherfields from table group by dupefield having qty 1
I think there's a setting in phpMyAdmin's configuration file for 'show blobs'
XD
QC
Or is that 490?
sorry…. /me thinks he's funny esp. due to his nick
:P
S'rry it's just that i'm french and i'm not used to a few abreviations or jokes.. etc.
hello
how i get rows from two tables with one query?
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
!man union
see http://dev.mysql.com/doc/refman/5.0/en/union.html
Yeahp, your Groupconcat works really well =D
one of those two, depending on how they are related
man union is unnatural
use join
x)
lol
he was making another obscure joke.
ah
i understood
loul x)
he's a rocking horse
I was born that way x)
I'm banned from #postgresql
They told me that Postgresql was better. Then they banned me for trolling.
That's why it's better. They keep the riff-raff out.
#mysql - you'll never find a more wretched hive of scum and villany.
What does wreth means ? x)
I AM NOT SCUM!
wretch* xD
"sorry for the mess"
can't argue with that
I HAVE MSCE CERTIFICATION!
snoyes, but willing to help anyway.
did you mean "who's scruffy looking" ?? he's quoting star wars
!
the guy who shot first is scruffy looking
!
so
I HAVE MSCE CERTIFICATION!
who banned you ?
I think I know why he was banned…
DarcyB!
my slave got confused after i rotated logs on my master
you can't tell just based on his last 6 statements here?
wow, darcyb is actually pretty sane, you must have really been a jack-ass
Can someone ask DarcyB to unban me?
"i'm picking up on your sarcasm" "that's good because i'm laying it on pretty thick" tommy boy
Could not find first log file name in binary log index file", is there a way to fix that, or do i need to re-copy the DB dump and restart replication?
bCould not find first log file name in binary log index file", is there a way to fix that, or do i need to re-copy the DB dump and restart replication?/b
I was only floodin with bots.
Ok, you can stop now
Okay.
Yes.
fzzzt, what's the name of the logfile, and does it exist?
I use function pointers!
I still can't join #postgresql using RFC 1459!
It says DarcyB banned my address!
And pretty soon it'll be #mysql as well
hahahahah
Sorry.
then he will have to go hang out in #firebirdsql
Darn!
or #sqlite
schoonm, umm the file referenced in the client's error log, is named after the master, so if i'm right in looking for the actual file on the master, no it doesn't exist anymore…it was rotated and deleted
i like sqlite, i wouldnt send him there
haha alright
so he's some kind of fly on the hear?
snoyes ?
Little question
with the request you gave to me
if i wanna have first the ips that have the most accounts
what should i put for ORDER BY ?
The COUNT() alias
fzzzt, my replication experience is limited, but I'll give it a go anyway. I'm thinking you're going to have to stop replication, and recopy the DB dump
assuming i need to copy the latest dump and restart replication, what's the correct procedure for rotating the master's logs so the slave doesn't get out of sync? :/
schoonm, i'd agree
Thanks seekwill
np
fzzzt, how did it get out of sync in the first place?
hi all!!
anybody help me?
please!!!
you need to ask your question before anyone can help you. don't ask to ask, just ask your question
fatpelt, i'm sorry, i'm newbie from here.
not sure
no probs. that's standard irc practice, now you know
fzzzt, what storage engines are you using?
innodb and myisam
nostly innodb
s/n/m/
fzzzt, version of MySQL?
it looks liek it lost connection, master rotated, and when it came back the file nmbers were different
5.0
fzzzt, my first approach would be to make sure the network is good!
i have problem with a forum database with InnoDB engine. this database does not recognize from mysql and a few moth, it's works.
yeah, unfortunately nothing else had problems
maybe its that machine
and i need to recovery this database info.
fzzzt, could be. Do you have sync_binlog set on the master?
yeah, it's 1
use cmx, thats ok, but when i type select * from T01001; it's said me empty info.
any ideas?
fzzzt, well I've run out of ideas at the moment. My gut is telling me if you're going to experience network issues that will impact replication, I'd fix those first.
hmm, maybe i'm wrong.. it doesn't say it lost connectino, just that its reconnecting to retry…
fzzzt, can the slave ping the master?
yeah, its working fine, just not replicated
i stopped slave until i can figur eout what to do
fzzzt, good idea.
hello!! any idea about my problem?
Error reading packet from server: File './aphrodite-bin.000083' not found (Errcode: 2) ( server_errno=29)
hello, could someone help me using libmysqlclient on linux? i compiled and installed mysql from source, but there are no libraries for libmysqlclient. i can compile my programm, but the linker says, it can't locate -lmysqlclient
ah
oh, i mean on mac, sorry
fzzzt, can you post the errors that come after ??
well i found an error on the master a few minutes before that
so maybe it started there…
oy
fzzzt, what's the last logfile on the master?
well, now its 000002
i dont know what it was when it had the problem
Is there any way to make a query return unique records based on 2 fields instead of one (DISTINCT)?
the backup has run since then and it uses –delete-master-logs
would a group by suffice ?
hi
I am checking that now.
is there a way to log (or watch) every single mysql-query the server receives? i have a problem with my cyrus and need to see what it SELECTs
i think i'll stop doing that and start using expire_logs_days instead
fzzzt, that's what I was thinking, plus maybe making the max_binlog_size larger.
!man general query log
see http://dev.mysql.com/doc/refman/5.0/en/query-log.html
Krstfrs, you want to look at the general query log
thx
is it possible to use the results in a subquery within another subquery
?
btw group by is what I was looking for.
tias?
i believe the answer to your other question is yes, but i'd think it is fairly innefficient to do so
unless you mean two different subqueries, i'm not sure. i'm thinking of select (select (select …) … ) type situation not select (select), (select)
I am tlaking about the second scenario
good day lads!
I want to use the results of the first subquery in the second one… instead of repeating the query inside the subquery
If you got all that
well, i've had quite an annoying issue this afternoon, which is currently preventing me from leaving work completely..
i'd appreciate any help on this!
i don't think you can do that, but i don't know at all
have install errors under freebsd http://pastebin.ca/616462, maybe someone had this already ?
do you have a question in there ?
i have a set of tables with utf8_general_ci collations, storing text in cyrillic
bi have a set of tables with utf8_general_ci collations, storing text in cyrillic /b
%)
the thing is that the text is not being stored as CP1251 characters and NOT UTF8
How about you show what sort of results you're after?
which means that when i dump the data and import it into another database - i get garbled text!
iconv doesn't work
set names doesn't work
changing collations after import doesn't work either
snoyes, but it is complex… so it is difficult to explain
i'm not quite sure how it came to be - but cyrillic used to show up fine being stored in cp1251 in a utf8_general_ci collated table..
could it be that your connection charset isn't set correctly ?
Provide a simplified version then, which you can extend to your real scenario as needed.
how can I compile mysql using - AES and DES
eth01, start with http://www.mysql.org/doc/refman/5.0/en/compile-and-link-options.html
eth01, sorry I sent you the URL from the wrong tab… Try http://dev.mysql.com/doc/refman/5.0/en/configure-options.html
eth01, sorry I sent you the URL from the wrong tab… Try a href="http://dev.mysql.com/doc/refman/5.0/en/configure-options.html"http://dev.mysql.com/doc/refman/5.0/en/configure-options.html/a
i've got an index question i can't figure out. http://www.slexy.org/paste/3618 i've tried all possible combinations of an index on m.date, m.id, and m.deleted and i can't get rid of the using temporary/filesort. does anyone have any ideas on what i
can try ?
http://pastebin.ca/616519
Let me know if you can understand that…
This is the top level explaination
So, SELECT * FROM p JOIN spm JOIN s JOIN asm JOIN a WHERE a.text MATCH ('someKeyWord') GROUP BY p.id, a.id
Ouch
what is the most efficient way to get the single most recent row (by date) from a table? is ORDER BY date DESC LIMIT 1 really it?
probably.
yup
hmm is make world just buildworld+installworld, or is there more to it?
wow wrong channel
hi everyone
I updated mysql from 4 to 5. Before I saw many mysqld processes in "top", now I see just one. I mainteined my.cfn. Why this?
surely now the only one process is more heavy
can i select some rows and delete them in a single query?
delete will not return rows. You could write a stored procedure that called a select and a delete.
ok
ta
hello, i have big mysql dump file with all databases, how can i import just one of the from this dump?
hello again
is anyone able to help me out with encodings please?
ser either cut it apart with shell commands or import the whole thing somehwere else and dump the table you want
it has 8GB
http://hashmysql.org/index.php?title=Single_table_restore
how to edit such a big file?
thanks, i;m looking
thank you
ser shell commands
but tricky
there is absolutely no way to guarantee which record i get back on a group by right?
ser then go fix your backups so you don't have to do this in the future
I updated mysql from 4 to 5. Before I saw many mysqld processes in "top", now I see just one. I mainteined my.cfn. Why this?
If the field is not part of the group by list, and it's not an aggregate, correct.
correct. something like get me field max(x),y,z grouped by y. z might not be the same record as the max right?
correct
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
a href="http://jan.kneschke.de/projects/mysql/groupwise-max/"http://jan.kneschke.de/projects/mysql/groupwise-max//a a href="http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html"http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html/a
just found out Polytechnic University offering $40 a night rooms for MySQL Camp II…. Good times.
hello
and a good day for all..
i have a question for mysql cluster!
anypeople can a help?
jaypipes are you planning in the future do camps and conferences in southamerica..maybe brazil?
Which one?
hmmm. It's an idea! The camps are more community-driven than anything else. I organize the US-based ones mostly, but I know that MySQL Camp in India happened recently…
what do you mean?
I think there are at least two Polytechs…
jaypipes i see..i hope to see some of that near here
ah. The one in Brooklyn: https://idmi.poly.edu/mysqlcamp2
email me for information on starting one up: jay at mysql dot com
jaypipes, do you know of mysql cluster??
I'd just go ahead and ask.
lol
great!!
after "UPDATE … SET a=a-5, b=a-5", always b=a-10, b=a-5 or the value may vary depending on the implementation so this kind of command wouldn't be recommended?
Can you help me? Can MySQL do everything?
certainly not my area of expertise, but I'm sure somebody can help!
is this the jaypipes from yesterday's webinar ?
yep
I have a environment with two DB server running NDB
had one today for Europe too
i'm rewriting a query based off my email question (patrick felt) with the subquery in the from clause and i can't get rid of using temp/filesort. nobody seems to know how i can do it. http://www.slexy.org/paste/3618
Google for Nested Sets model in SQL.
case one DB server fail, and i change this server for other new server, the mysql can to talk back this new server?/
Err, okay, I'm having a problem, and I've had it numerous times… and no one seems to be able to answer this question: I do this: myisamchk -vvv –force –fast update-state –key_buffer_size=3500M –sort_buffer_size=3500M –read_buffer_size=1M –write_buffer_size=1M *MYI in the database dir
…. it repairs a table called "fake_table" … then, I start mysql, start the slave (this system is a slave)…. and it eventually throws this error: "Incorrect key …
…. file for table './database/fake_table' … I don't get it, I just repaired it with myisamck (which is, I thought, the recommended way to do it). In the past, I can do a "repair table faketable" … start the slave, and I won't see that error again. Why is this happening? I've checked all
parameters I'm using with myisamck, and it should do the same thing as "check table" if it repairs a table … any ideas?
janey, case one DB server fail, and i change this server for other new server, the mysql can to talk back this new server?/
jaypipes, , case one DB server fail, and i change this server for other new server, the mysql can to talk back this new server?/
someone know if exists a webex player for linux?
Derived tables will trigger using temporary using filesort because they are materialized without indexes. But, unless the size of the temporary table is large, this shouldn't be too much of a bad thing. Especially compared with a correlated subquery.
you mean for Firefox on Linux? Yes, works fine. Only presenter has to use IE on Windows.
ah! ok. so i can't get rid of it then. how large would you say is too large? (i realize that's a pretty generic question and will only expect a generic answer)
but..i was trying to download a webcast by demand..and the extension…wrf i think…is there a player for that in lnux?
not quite sure what you're asking… you mean if you take one node down and re-add, can the mysql management node see the server automatically? yes, I believe so.
ah, I see. Never heard of a WRF file format…
jaypipes, i dont re-add the server fail, i add a new server!
I run linux and can open .wmf files but not .mov files…
Any ideas on that issue? It's driving me mad
one sec.
np, thanks
jaypipes, with configuration for supported mysql with ndb, but without tables and databases for the master…
jaypipes look http://www.mysql.com/news-and-events/on-demand-webinars/display-od-4.html?done=476758872fde6f
hop over to #mysql-dev and ping MacPlusG3 (Stewart Smith). He should be able to help…
oks!!!
thanks for a help
Hello, i have created a user with grant, select, insert, update, delete global privileges. He is able to grant but not able to revoke. How can i give him revoke privilege?
hmmm. no idea!
when you play it..it will try to open a wrf file
he..np..i'll see it in my house
try vlc
vlc? tks foo
Hi everybody
does someone know how to make a dump of the sql structure without using mysqldump?
hmmm. strange stuff. Is the slave accepting the relay log when you run myisamchk?
i mean with a sql sentence or something so
It would be possible, but needlessly complicated. Why can't you use mysqldump?
hmm, what do you mean? Myisamchk also works with the relay log? (fwiw, there are other slaves in sync… so, odds of bad data from the master are unlikely, I think, if that's where you're going)
snoyes, i have i problem with something about the bus or something so
on a solaris 9
no, I was just wondering if you are running myisamchk while the slave is taking in writes from the master, or while it is stopped?
is the master table also MyISAM?
ohhh, my bad.
something like this? http://bugs.mysql.com/bug.php?id=28099
i do not get those engines
Mysqld service is completely stopped when I run myisamchk. Yes, master table is also MyISAM
snoyes, exactly that one
can someone give a tutorial for what those engines are
im using myisam always
Hi there. I have a merge view like SELECT * FROM t1; and I'm doing SELECT * FROM vw_t1 ORDER BY timestamp_column; — however explain extended says that it's being queried as ORDER BY UNIX_TIMESTAMP(timestamp_column) therefore the index is not used. Does anybody have any tips?
then one solution would be to just copy the .MYD and .MYI files from the master onto the slave… Of course, you'd likely have to stop the master momentarily for that.
im not database expert, i just need mysql for making simple cms systems
somebody can help me??
just have to be patient.
I can't really afford to stop the master unless absolutely needed. Shouldn't myisamchk be able to fix this? If I run "repair table fake_table;" at mysql prompt … the table will be solved …
lololol
So, REPAIR TABLE works, but not myisamchk? That sounds like a bug, no?
I wouldn't be suprised. I've encountered this example same problem on these systems about 3-5 times now
What version are you using?
if you can put together a reproduceable test case, I would report it as a bug… see what the bug verifier says.
5.0.27
snoyes, i suppose i need to download a new one and replace the binary
That would be my first step.
snoyes, i cant beleive the people from sunfreeware uploads binaries that doesnt work, the QA Team just sucks
Alright, I'll give that a shot, it's my only real option Hehe
sunfreeware.com? i doubt they have a QA team, it's just one guy iirc
matrunix, whats your question about ndb exactly?
_mary_kate_, you are right
Why are there 4 default accounts? 1 blank, one root, each for localhost and root?
thanks
localhost and (host)*
why aren't you using the mysql.com binaries?
matrunix, you want to add a new mysql node or a new data node?
np.
HarrisonF, my question is case my server "a" is down, and i substitute this server for a new server. The databases will replication for a new server??
_mary_kate_, i 'll do now, the problem is that my servers are always on production so it is not very easy to me reinstall a server on those conditions
HarrisonF, new data node
matrunix, if you replace the node in a node group (you will most likely need to give the same IP to the new server), then it will resync from the other member of the nodegroup and all will be fine
would it be terribly innefficent to put a subquery in the FROM clause then put that whole query as a subquery in another FROM clause?
hi, where i could find the log of mysql activity? like who has logged in, what he is doin. etc
depends how many rows are in each subquery and whether the size of the generated temptable exceeds max_heap_table_size
right!
you must have had turned on the General Query Log
thanks!!!
i will search more about mysql cluster!!!
very tks!!!
or get HarrisonF's book on it http://www.amazon.com/MySQL-Clustering-Alex-Davies/dp/0672328550
matrunix, that is the same reason you can do a –initial start on a pre-existing node
matrunix, the other one will give it everything is missing (in 5.0 and prior, it actually *always* gave it everything new)
ok!!! i understand!!!!
general query logs? you mean all logs?
great!! tks
and the databases, too replication for a new node???
HarrisonF, and the databases, too replication for a new node???
and congratulations for your book….i have this book…and is a excellent book,….!
matrunix, yes, since it is a data node it is fine
ok..
matrunix, new sql nodes need the database created on it (and procedures and users, etc…)
HarrisonF, because when a create cluster, i was create the databases hand's on in each node…
HarrisonF, because the master node dont replication for a other node..
only the tables replication for a other node
matrunix, that is with SQL nodes, data nodes contain it all
ok
what the functions of mysql node??? and data node?
matrunix, huh?
i come back later….i go read your book….and new question i come back!!!
tks!
so if the speed of joins with subqueries is dependent on the size of the resultant table, but a dependent subquery is just as bad, the only way to get near consistent time is to use temp tables right ?
Hello; any ideas why am I getting 'Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 6' when using mysql_result() in php?
but if I "su mongrel" first
and then "service mongrel_cluster start"
the application seems to work fine
and wow I am in the wrong channel
how can i give a field auto_increment starting from 158255?
autoincrement
http://hashmysql.org/index.php?title=Autoincrement_FAQ
how do I repair tables in a database
all tables in a database*
the table is already created
yes
See #4 on that link
oh
hmm, i cant find it there.. ive got a column userid in an existing table which i want to add auto_increment to
Ah, so it's not already auto_increment? Use ALTER TABLE MODIFY COLUMN to add auto_increment. It will automatically pick up at the highest value in the existing field.
ah, great, thanks
!man alter table
see http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
how do I find the location of my database files on my hard drive?
Snake0, that's set in my.cnf file.
!man select syntax
see http://dev.mysql.com/doc/refman/5.0/en/select.html
Is this the right place to ask MySQL DB design/query questions?
yep
how do I start mysql from the command line?
windows or linux?
linux
http://dev.mysql.com/doc/refman/5.0/en/unix-post-installation.html
step 3 in particular
I need to figure out how to insert a new node. The data is a tree. Each node is guaranteed to have no more than 3 children. So when a new node is created I need to put it in the first available space below a certain node possibly many levels up.
trees
http://www.dbazine.com/oracle/or-articles/tropashko4 and http://www.sitepoint.com/article/hierarchical-data-database, http://jan.kneschke.de/projects/mysql/sp/ http://jan.kneschke.de/projects/mysql/sp/sp_tree.sql http://www.vbmysql.com/articles/database-design/managing-hierarchical-data-in-mysql/ http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
Very _cool_ thanks!
woah lol no ops :Z
eh, ran too fast away
Who?
Graham
is there a way to rename a database?
ah, looks like in 5.1 there is
fzzzt, RENAME DATABASE, but privs won't change, and any stored routines or events won't migrate.
how do i convert it so it collates to 1251, stores the data in cp1251 and doesn't lose any of the data already in it?
any help would be greatly appreciated
schoonm, doesn't seem to work in 5.0
are table names case sensitive?
pants-wd, yes and no
!man identifier case sensitivity
see http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html
pants-wd, in an os that has file name case sensitivity, like Gnu/Linux, BSD, and Unix, the table names are case sensitive, in Windows the tables names are not.
suppose i have a database table with columns date and val… how do i find all items on the earliest existing date on or before MM-DD-YYYY
is there a simple query or do i have to iterate the date?
s/simple/concise
ok got replication going again
"all items on the earliest existing date" means what - show each item and the date it first appeared in the table?
select * from table where mydate = "2007-01-01";
where datecol 2007-01-01 order by datecol
thanks
pants-wd, yw.
pants-wd: there is also a config option to make them case insensitive on systems where they are by default
find the next earlier date with an existing entry and return all the rows with that date
gotta love windows and ntfs
so if today is Thursday, and there's some stuff on Monday and some stuff on Tuesday, i want all of Tuesdays stuff
SELECT * FROM table WHERE date = (SELECT MAX(date) FROM table WHERE date = '2007-01-01"); — something like that?
is there a way to set a max query runtime?
great, i'll explore that, thanks!
does multiple if-else condition in trigger need "BEGIN END" also?
is there a way to set a max query runtime? using myiasm and php is performing the query
on mysql config?
Is there a way to get only a certain amount of results from a table, like the first 50? or perhaps specify a start and end point, like get 20 results from the 10th result to the 30th
use limit
in the config would be great
I've tried search for amount and quantity things like that, and all I've come across is SUM
use limit, jogn
oh andres_ thanks
offset
thanks c_newbie
i know there is the log slow queries but i've not being able to find anything to say "If a query is longer than X, kill it
stupid bot
Horrible human
you migh combine the sql querry max time and the apache timeout it self
offsets
than the id's already passed.
i suggest you set the apachet timeout is bigger than the query max runtime
do you know what the query max runtime configuration directive is?
you can manipulate it 1st by using select count(*) table 1st, if its more than $maxrow, then do not pass the 2nd query, showing result
but if you feel it still take a long time to make result, feel free to optimize your indexing
ok, thanks
Anybody here good with clustering?
words, shaun
I have clusterballs
….
i guess i need to ask, "anybodey here good with triggers" ?
badly, i need a help
no one?
have you looked at the mysql manual, c_newbie?
c_newbie, what's your question?
c_newbie, i am good with triggers
Shaun2222, sorry i am not good with clustering.
can i use multipe if condition in using "IF" statement?
c_newbie, reading your statements
like IF OLD.field1 != NEW.field2 AND OLD.field2 = NEW.field1
aye I think so
i've create trigger and showing me unidentified error
give me a second to modify a trigger and test
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 '' at line 1
c_newbie, mysql is notorious for those kinds of errors
That usually means a missing delimiter or closing quote or bracket.
c_newbie, if you want you can paste the entire trigger to http://www.pastebin.ca/
ok wait
because I dont think it is your if statement i think its as snoyes says, a missing dilimiter
c_newbie, looking and also testing
thanks
ive double-checked the delimiter, closing quote or bracket…
dunno if it matters in this case, but I'd put old and new in backticks in line 39, just because it's in a trigger.
im sorry, i lost you
oh, it's ELSEIF, not ELSE IF
Anybody know how I could turn these this PHP script with these three mysql queries into one straight mysql query, my site is dying and need to run this fast http://pastebin.ca/616795
snoyes, ive tried both. actually, using ELSEIF and ELSE IF is a same
c_newbie, ` this is the backtick
If I take your pasted text and run it, I get the error you describe. If I just s/ELSE IF/ELSEIF/g, I get no error.
HEY
its work
snoyes, yeah thats it
but why..
ive create before using else if, and its work
c_newbie, cause elseif is proper
silly me!
but im using it before, ELSE IF and its worked
c_newbie, because each IF statement requires its own END IF;
Hi all. I've a performance question. I have a query that is using filesort according to EXPLAIN, and I'm trying to get rid of that. But I've simplified the query down to a single ORDER BY and it's still doing a filesort. Why would that be? I thought filesort was only used when the WHERE and
ORDER BY clauses required different conflicting tables.
Sure, if you do IF…THEN..ELSE IF …END IF; END IF;
which you arent supplying
exactly snoyes
ahh
RobRoy, looking at yours now
so the mysql take the 2nd ELSE IF as like ELSE { IF } ?
correct
awesome! I was told to switch to just mysql_ instead of adodb and will speed up a bit, but I think I should be able to combine these into one query…
RobRoy, yeah I wont use ado or any abstraction anymore. Ive been burned by them.
if I do 'SELECT type FROM etc.computer ORDER BY type', can I tell it to toss repeated values, so I can get a list of all types?
and unless yo uever plan to move to something else..its kinda pointless
SELECT DISTINCT
thanks.
ok let me restate..its kinda pointless for me ado might not be pointless for you
uok let me restate..its kinda pointless for me ado might not be pointless for you/u
uuok let me restate..its kinda pointless for me ado might not be pointless for you/u/u
RobRoy, why are you going through the first 10000 rows from the user table?
well, I added that since i was running out mem selecting all into an array, so that just loops through 10,000 at at ime
time
and I guess more specifically you want to detail the information you want,
really I want to select all rows from users
why?
I want to go through each user, update his/her level with the result of the count(*) query
i'd think you should use a cursor, though i'm not familiar with thier implementation in mysql
xzilla, maybe.
me neither
RobRoy, unless we can do something like update where set bla bla etc
otoh, if you are updating with the result of a query, your might be able to turn that all into one beast
as much as I can I try to use a single sql statement, if I cant then I use cursors
exactly
let me try something
brb
hi all.. i'm looking for recomendation for a GUI for MySQL such as MySQL GUI Tools. Any recomendations on what to try?
yeah, I tried something like update users u1 set u1.level = (SELECT count(*) FROM users u2 WHERE u2.path LIKE CONCAT('%', id, '%/')) where id = u2.id but that's all wonky
thx
mysql query browser, from the mysql.com web site. GPLed. Pretty good.
Crell, thank wil have a look
I'd need some help on a nested query I'm trying to build
Navicat is quite nice, too, but it's non-[F|f]ree.
RobRoy, would you be ooposed to pastebining your create table?
would anybody be willing to let me semi-flood them with what I've currently got?!
not at all, one sec
pastebin?
bah, no bot.
flow, http://www.pastebin.ca
brb
Crell, ah that's part of MySQL GUI Tools, any other recomendation?
btw for X
icebrian, what exactly are you wanting the GUI to do?
at bottom of http://pastebin.ca/616817
navicat is for X, the version for GNU has fewer options than the version for windows, but its pretty good
RobRoy, going brb
this is the query that renders results - but results in some optimization that coughs up an error
all other attemtps to nest the query fail miserably
RobRoy, each row is a unique user?
yes, user.id is primary key
aoirthoir, well mostly just browse DBs, SQL queryies, etc, no administration functions needed
icebrian, have you used phpmyadmin?
not fantastic but it works..also navicat is your next best choice http://navicat.com
aoirthoir, I have
non-free
aoirthoir, I am just seeing if there are any other viable options out there
RobRoy, so for each row you are looking to count all similar users that have the same path and type?
gui
GUI tools can be found at http://dev.mysql.com/downloads/gui-tools/5.0.html phpMyAdmin at http://www.phpmyadmin.net/ and even navicat or http://www.webyog.com/en/
version of MySQL?
hm. good Q - I dunno, not my account, only got ftp access
send SELECT VERSION();
the_wench, ahh navicat never heard of that, will take a look. thanks
The error shown is not from the query shown. COUNT(*) appears nowhere in the query shown.
I know
that's why I assume it's from some optimization
I don't think so.
unless it's some optimization done by something before MySQL gets it.
for each row I want to count all other users with a similar path and type AND take that count and update the current row's level
mmh. drupal
RobRoy, ok gotcha, i was thinking thats what you wanted.
Does drupal attempt to count the number of rows a query will return before executing it?
Unknown column 'VERSION' in 'field list'
oh. damn - forgot ()
flow please do not use language like that in here
'forgot' is not allowed
4.1.22-standard
(see title sense of humor mandatory
ok, so your version does allow subqueries. I suspect therefore it's something drupal does to attempt to profile a query before sending it.
RobRoy, havent forgotten you , doing some reading, I found a couple things but not sure yet about them
snoyes, drupal does have it's own db abstraction layer.
yes, I think I found it
something in includes/pager.inc
awesome, thx
due to drupal needing to know the number of results for paging long sets … hm. hm.
what's the best/easiest way to automagically backup a mysql database every day?
cron + mysqldump
!m raar binary log
raar see http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
!m raar backup
