seekwill ive debuged it and ive found what causes the error but thats what i dont understand its a simple SELECT
Grouping categories must be done in PHP, true? However, are all rows downloaded in correct sequence do to it? How about changing the sequence of categories?
hehe. i found that in a different irc channel. it's always been zippier than other pastebins for me
I'd like them one at a time….
Sorry. Change that COUNT to SUM.
Perhaps I'm going about this the wrong way. How about how do I change all the column names to lowercase?
does it matter ? is mysql case-sensitive in entity names ?
one at a time, using LATER TABLE syntax
fatpelt, i've liked pastie.textmate.org
ALTER TABLE*
I'll start up the FreeNode Campaign to Swap the A and the L
and change my name to ldlptr
did you try to implement possibility of changing sequence of categories?
well that being the case I'd like to put it in a nice programmatic loop hence the earlier question
no
but it is implemented in another module
the categories module
i'm not quite sure how
it uses nested sets as well
i think the change of "left" and "rights" of all lower categories is required when we change the sequence. We can sort by name as well.
I know when i looked at example of tree with left and rights marks, i thought getting higher categories is impossible. However, articles say it's possible.
well, you could cobble something together in an app like php or perl, but by the time you got it tested and finished, it'd be easier to just do it manually.
mysql internally returns the number of rows available when you do a query, is that correct?
ya
know how you can grab that in Python or php by chance?
looking for a few references to get me a head start 
mysql_get_num_rows() i think it is in php
that is more of a #php #python question
ya I realize that
hrm i think that just gives the length
i can literally get the entire result set size though, even if i only select 30 of them, right?
w/o doing an extra count() query
ya.. you never said you did a limit
SELECT SQL_CALC_FOUND_ROWS bla1,bla2 FROM table LIMIT 0,30;
SELECT FOUND_ROWS()
that will give it then?
it will run the query to return the 30 rows.. then the second query will return what could of been found if no limit was there
hrm, found_rows() returned 1
5
ya?
well of course it will return 1 if you do a count()
since count returns 1 row
the count is aggregating
its returning 1000+ rows
no
yes it is
1091
yes it is.. but in sql.. its getting 1 row
the 1091 from count()
Showing rows 0 - 29 (1,091 total, Query took 0.0944 sec)
no its not
or i wouldnt have this issue
sigh
jY, im aggregating the results, its returning count()'s but for each row
so its giving me 1091 results, but i want to the total number of results, without doing a subquery
and if i can find a way to not have to do select count(*) on every query for pagination (by using this) that'd be a huge help as well
yes.. you do pagenation like i did
there should be no count(*) in your sql query
ELECT SQL_CALC_FOUND_ROWS bla1,bla2 FROM table LIMIT 0,30;
returns the first 30 rows
ya, that seems ok, and sounds right according to the documentation
SELECT FOUND_ROWS() returns the total rows that would be returned if no limit was in there
its just not working for this query
cause you have a COUNT() in there
so sql returns 1 row
its not returning 1 row
yes it is
well you are grouping
so its not gonna return them all
exactly, im grouping
so theres 1k+ rows, w/ count values in each
i need to know the 1k+ value
phpMyAdmin seems to display it fine
my assumption is they were using this method
what field type do you use for a number?
what sort of number?
a number between 1 - 10
inc decimals
inc decimals?
including decimals
1.24 etc
crazyryan1 a float ?
How about the DECIMAL field?
what sort of precision do you need?
Really don't want to use FLOATs
2 dp
decimal it is
ok
I'd really like to know a real-world use case for FLOAT/DOUBLE
what would i use for id?
int?
Sure
okay, finally
seekwill not in a db but in electronics design
Well, I mean in DB
for the decimal field type, i put the limit as 2, does that mean numbers that are 3 digits won't go in?
decimal(2,0)
probably but i dont have time to read them
Yes you do
hehe idle sh…
jY, it was phpMyAdmin messing it up
oh you were doing them in myadmin
no wonder
ya that next query needs to be the next query run to work
hey guys. say i have a table with id and file, all i know is id…how do i return a count of all records that have the same file as the id i know?
do a join on the same table?
better do a subquery if you only need one ID to start with
i only *know* 1 id, but only need a count to know if the file is in use by more than 1 record
but i dont know the file
well a self join is ok use an alias
that's what I mean, so you need something to determine the filename from the ID
and then a simple select on filename
but since you'll be inputting just a single ID to that first step, a subquery might be more efficient
http://pastebin.ca/642282 so that should work, eh?
not sure what you mean with the dots, but syntactically, yes
yea, ignore the php in there.
no, not php - the *dots* around your id in the second select
that would be escaping the string in php.
but yea, thanks for the help, i think it works.
I came up with SELECT COUNT(*) FROM table1 WHERE filename = (SELECT t2.filename FROM table1 AS t2 WHERE t2.id = yourvalue), since I think you dohn't need to alias the first table occurence in a subquery (no chance of ambiguity)
hrm, it was working….
you're probably right. i always alias all if i alias one. just habit
but you'd better make sure the text field you're selecting on matches (no whitespace, no case sensitivity etc)
they'll be identical or very different.
but it stopped working….strange…it worked the first go round.
have you tried the self join ?
you should probably test and EXPLAIN both to see how it optimises each
is there a way to see how many clients are connected to mysqld?
well, yes
why is count returning ''
shouldn't it return = 0?
could anyone tell me how to stop the 2nd query using filesort? http://nopaste.com/p/akzIgOXPF the first doesn't use it yet the 2nd is the same but without a WHERE.. i don't understand why it can't use the listname index even after reading the
order-by-optimization page on the MySQL site
megasquid SHOW GLOBAL STATUS
madriss, add table definitions to the pastebin
how do i get the table definition?
adaptr, thanks, which stat is it? Threads_connected?
best guess, yes.. I thought connections, but that turns out to be connections since startup
it's 24 on my box, which seems high to me
madriss, show create table tablename;
ok thanks, 1 mo
adaptr, alright thanks
or else perhaps
!man status
see http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html
http://nopaste.com/p/abd5BW4oz
ermm.. not that one
!man stats
Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/stats
!man global status
Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/global status
pfft
don't understand why the query with the WHERE in it uses the listname index, but the other one can't use it
probably needs a compound index
could you advise what exactly i need to index?
i think i tried "listname, rateid" and "rateid, listname" but that didnt help
i guess its not because of the JOIN, as the first query on there doesnt need filesort for some reason
madriss, how many rows in 2007caws
2394
heh 50%
hmm
actually thers only 2305
but autoincrement is higher i guess some have been deleted thats why
what u mean 50%?
Class 'mysqli' not found in
run analyse table then retry
derekl, fix your php setup
ok
analyze status Table is already up to date
still uses filesort
madriss, how many rows in the result 1?
you mean the first query? there was about 29 rows i think.. ill check
the one with the where in it
no the second
if all 2305 then use a better data set
it should be all 2305
im just wondering why it doesnt use the index
there is no WHERE in the 2nd one.. its just selecting all rows and ordering by listname, but ignores the index and uses filesort
It has to read the entire table, reading the index as well is overhead
oh right
so it's not possible? :s
no a waste of time, hence the comment about a more sensible data set eg 50% of a table
ah i see
ok that makes sense
im trying to optimise all the queries on the website.. and one of the pages allows you to view a list of everything in the table
so i guess i should not have that feature
never allow a full table slow download etc
maybe ill learn how to have it split into multiple pages
offsets
than the id's already passed.
is there a tut for how to show a whole results set on multiple pages?
google "paging"
ok thanks
thanks wench
It's been a pleasure serving you, madriss.
Is it possible to combine two columns of a result set into one column and then DISTINCT it?
makesa bit more sense now
afternoon shift has arrived, hello snoyes
ill try paging it then, thanks everyone who helped
CONCAT() to combine them, DISTINCT operates across all values in the result set anyway.
howdy archivist
orf home time
soon
#quit
hi, I've got a test server, and mysql hosting is running 10 processes, how do I make it launch fewer processes ?
can someone please tell me what is wrong with this query? http://pastebin.ca/642331
aarcane, best not to worry about the first few some are background worker threads
Probably need quotes around the email
and you should be doing some escaping of that posted value, to avoid sql injection. At least use mysql_real_escape_string()
archivist, I'd prefer to only run 5 or so instead of a full 10 though, esp since between them they're using 114 MB of RAM
the quotes didn't work
swimrr, and learn to echo your sql to see what you are doing
$query = 'SELECT email, password FROM users where email = "' . mysql_real_escape_string($_POST['password_signin']) . '"';
else echo mysql_error() . " in query " . $query;
let me try
thanks, why do I need to escape the posted value?
What if I go to your site and submit my user name as something like ' OR 1 –
I don't know - i'm new to this
thenh he could break in
is his point
figured as much - how so? what does entering ' or 1 do ?
well how do I get memory usage down ? 114M is an aweful lot of RAM to dedicate to a database for testing purposes only
Without escaping, your query becomes SELECT email, password FROM users WHERE email = '' OR 1. Which means I can pull out any email host I want.
That's not so scary. But with a little more work, I could make your query return passwords in the email field, which you print out a few lines later, and then I can log in as anybody.
huh - interesting….where can I read up more on mysql security?
I know there are lots of risks I need to be aware of
http://dev.mysql.com/doc/refman/5.0/en/security.html
although that's more server installation related
this is hardly mysql security - it's basic web site security, and law one of same: NEVER pass unescaped values to scripts
can I upload a schema and import it to an actively-working DB so that new columns / tables are added but the information that is already there stays in place?
http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php
thanks again
If you need to alter table structure, you'll need to use ALTER TABLE, rather than creating a new table. You can insert new data all day long without any problems.
xaprb's toolkit has something to help create the required alter table statements if you have two dissilimar tables, I think.
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.
What I mean though is can I do an sql export from my new schema, sans the actual rows of data, and import it into another source where the schema is all that is used and the data stays the same?
No, the export will probably create CREATE TABLE statements, and you need to know which fields are different and create ALTER TABLE statements.
Can anyone tell me why no indexes are being used in this? http://pastie.caboo.se/84183
How do I merge the values in two mysql columns into one column?
As in
SELECT (`A`,`B`) AS `C`
concat
concat is adding the values together
well combining as strings
which i don't want
so what do you mean by merge the values?
concat
Basically
If A has 1,2,3 and B has 1,2,6
I want C to have 1,2,3,1,2,6
then using DISTINCT i get unique values
concat(a,",",b)
……..
I already said I can't use concat it's not right
whats wrong with concat?
Because it literly adds `A` to `B`
and you want?
I want two seperate values in the same column from the sources A B
not one value added together
hola!
so I've got a mediumblob field w/ a bunch of text in there (UTF8) and I want to do a case insensitive search on it.
any ideas?
HighAkujin… according to what you wanted ….. concat gives you what you want
select @a:="1,2,3",@b:="2,3,4",concat(@a,",",@b);
instr(blobfield,'what you looking for')
^^TimLaqua
that's case insensitive?
its all case insensitive
well, I did a old_text LIKE '%string%'
Can anyone tell me why no indexes are being used in this? http://pastie.caboo.se/84183
old_text is a mediumblob - and it was case sensitive
i don't know ….. try fulltext indexes
iratik, it's f/ MediaWiki / boxed. 
tryin not to go all reindexing fancy on them
so I have 10 processes each using 108MB of ram, how do I get that down to fewer processes ?
the instr(mediumblobfield,'string') method is case sensitive. 
I even tried LCASE(medblobfield) - no dice.
or another Idea I had was the REGEX matching - is there a way to specify a REGEX pattern as case-insensitivei n MySQL?
!man pcre
Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/pcre
shoot
SELECT DISTINCT `B`.`friend_id` as `id`
How can I add the values of `A`.`friend_id` to the column `id` ?
High_Akujin, both number fields?
yes both are ints
they are origionally the same column technically
you want to add them mathematically in the output?
like… with a plus?
No, I would like to merge them
Imagine them as two arrays
how can I now how many inserts I have per day? in the whole DB?
I mean use something like show….
with a current value in id that you want to maintain?
show status
12«11threnody12» I don't really understand that question.
any resolution to Too many keys specified. Max 32 keys allowed
update table set id = concat(id, friend_id) where …
it occurs when i perform INNER JOIN
anyway around it?
different type of join maybe?
Anyone know how to do a case-insensitive substring search on a mediumblob field?
No I'm simply grabbing all the Friends of Friends of user X in a very simple table
to achieve that I do a query for all the friends of the user X and then I do INNER JOIN on the result doing the same exact query to grab all their friends
the problem is the result set needs to contain all your Friends AND Friends of Friends
if there happens to be a friend who isn't another one of your friend's Friend then they don't get put in the result
so……..
I need A.friend_id combined with B.friend_id into one column
then i use DISTINCT
I would like to achieve this with one query and not have to have an extra query for php webhosting to work with
you need to do a full (cartesian) join then
it sounds like you've taken more time finding the answer in mysql hosting than just implementing it in php
It has to be efficient, I don't want to put more load on mySQL then I have to
and you think running a full cartesian join instead of two small ph pqueries is *more efficient* ?
buh
I don't really know what a full cartesian join is…..
But I would like to learn if you have some reading material?
a huge advantage of using php is that you control the queries
!man full join
Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/full join
!man join
see http://dev.mysql.com/doc/refman/5.0/en/join.html
it can take some rerading to understand the differences, but you should be able to see that a full backreference (generating 1 x N x N results) needs a full join
full join all friend_id's on all id's and then select distinct on one id - it's a relatively simple query, but rather expensive to run
It seems rather weird that it's not possible to simply combine the already created result of two columns…….
the result is not two columns - it cannot be
if you're doing this with one table, by the way - good luck !
lol thanks
friends has to be a cross (or reference) table linked to people ids for this to work *at all* in SQL
ftp://akujin.mine.nu/friends.sql
here's the query
or what i have now atleast
that's not too interesting - what's the *data* look like ?
inner join ain't gonna cut it here, since you will only get full matches on the first ID from the second table - i.e. his friends
ftp://akujin.mine.nu/fof.png - some data
huh ? how about show create table for any tables involved ?
Is replication for the entire MySQL instance, per database, per table?
on a mysql master you can execute the command SHOW BINARY LOGS to have it assert the file sizes of binary logs on disk. I'm wondering how I can do this operation for relay logs on a slave. Anyone know?
just look in the file system?
that screenshot enough or you still want a create query?
High_Akujin what I thought - full join it is
wow, ftp links are awesome in kirc. i just opens the file in the associated application. i imagine that's a security risk, however.
that relates all freinds of X back to their friends, so you get a user * friends * friends join
yes, I can do that… but in order for me to be flexible I have to open and parse the mysql config file to find the location of those files
/var/lib/mysql in all intances ive seen.
unless there's a way to query for the location of those files from within mysql
Is replication for the entire php mysql web hosting instance, per database, per table?
well, in my setups I put my relaylogs in /mounts/mysql/[mysql instance name]/relaylogs
i'm pretty sure mysql has something for this (specifically mysl 4.2.1), but to be sure, is it possible to specify a start and ending row in sql, like return resulting rows 30-45
you can include/exclude specific dbs and tables
there's even a wildcard thing for the tables
at least, that's how it is in 5, I don't know what 4 lacks, or what new there is beyond v5
High_Akujin the table itself is already the user * friends "join" (as a crosstable), so all you need is the selected friends as users in the join, yielding all "friends of friends" for all users, and then select DISTINCT WHERE userid = "billyjoebob"
is replication setup from the mysql CLI or in the my.conf ?
but as already stated, it's incredibly expensive when compared to two separate queries
maybe you dont have to open and parse the conf file, .. cant you just run a query to get the mysql conf options?
lol wow
that would be fairly rediculous
datadir
how do i reduce the number of processes that mysql has running ? I currently have 10 processes running, I'd like to get it down to 5 if I could since it's only a test server hosting and I'm the only one connecting to it
again though i gotta complain, the damn column i want is being used in the join anyway, how the hell is it that i can't simply get that result set too without all this fuss
show variables;
show variables WHERE Variable_name='datadir';
your comment 4 ago was precisely what I suggested, I was wondering how I could do that
i see
I have relay_log = /mounts/mysql/shego-slave/relaylogs/mysql-relay.log, yet SHOW variables doesn't seem to have it in the list
mysql 5 ?
yeah
5.0.41 if my memory serves me
yeah
i dont see it either
well this is hilarious
I asked a question in the PHP room, the suggested I come in here and enquire about "views"
I wish to take the results of 3 queries, and place them into a table, so that I can select distinct from the final table
Billium, look at create view
the column B from table C with id = 1?
what kind of join do i use? do i put the conditions in the on clause or in where?
c.b and id = '1' ?
so i don't use a join?
sweet
that's an implicit join.
but yes
'…a,c..' == '…a inner join c…'
I am looking at the create view, that seems to be ok, for a 1 query result set. I wish to use 3 queries, and place the results of all three into the same table.. using a temp table perhaps? and an insert? then I can select distinct record from that temp table
or maybe an array?
hello everyone, just a quick question, it i were to perform a search for an occurance of a word within a varchar field that contains around 150 words, would i be best off using a FULLTEXT search ?
good evening
Hey guys I've got a collation error here,
Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'UNION'
Please don't tell me to upgrade I'm running 5.0.19 on this server.
I try to recover a inno database (5.0) without the main tablespace, mysql was configured with the option innodb_file_per_table, so I have all the files of the database, but now I am unable to access the tables (table not found).
I just dumped a customers database converted it to utf8 recreated and reimported the sucker, now the customer is getting an exception on their website due to this.
shocking
I'm at a loss here.
can someone point my to some documentation, how to recover from this situation
anyone a idea?
hi all!
Hi one!
what's up the_wench !
anybody know about mysql HA?
anybody know about mysql High Availability or mysql master-slave?
http://rafb.net/p/tt6WyP61.html
I would really appreciate some help
labuser, I'm sorry about your problem i don't know
:/
labuser, but i answer you very fast or not?
I have a procedure that insert a row to a table with auto_increment. I tried to set autocommit=0; start transaction; call my_procedure(…); rollback; a few times, and it seems like nothing is written to the database, but the id increases
is this how it's supposed to be, or am I doing something wrong?
ok, according to http://lists.mysql.com/mysql/178603 that's how it's supposed to be
what specifically do you need to know?
hi all, im returning some values from a query - the value of one field is CONCAT(table.field,'/', table.field) - this is in the actual table as a row. When it returns this, is it possible to get it to parse it as part of the query?
"parse it as part of the query"?
well it returns the above as data in a row, but obviously i want it to return value/value instead of CONCAT(field,'/',field)
I'm still confused… you don't want it CONCATed?
FOREIGN KEY (nid) REFERENCES news(id) ON DELETE CASCADE, in a create database, what does it mean?
what is wrong with this query? echo '$_POST['email']' $_POST['password']';
No i want it to
that's not a query.
but im not entering that in the query, its returning that as data from the table
Examples?
sorry, I meant code
##php?
perhaps the question is better suited for ##php
gotcha…thanks
In the row i have the following, id: 1, attribute: 'speed', value: CONCAT(user.up,'/',user.down)
oh
Dynamic SQL. I don't think you can do that, and it's VERY dangerous.
hm
how can it be dangerous if you can't do it?
does mysql not support a FULLTEXT index on text type data fields? it is searching every field i have specified using MATCH() except is does not seem to be searching the TEXT type field i have in my table
is there some quirck im missing? like it only indexes text types if…
I believe SQL Server can do it…
It cant
haha
It can…
No idea where that 't' came from
uhmm yeah.. it does support a fulltext index..
read..
I was finger happy
no, thumbs happy
that would be the second time you do that to me…
i have, read 4 tuts on it, i just keep thinking im missing some quirck as i have done everything exactly, and it works for every field but the text type field, strange
You must like it. You never said stop
you're right!
Hitting the 50% mark?
anyway
50 mark ?
*50%
50% threshold
nope, not even only 2 rows i am querying, and both are 100% unique, even made both entries longer than 3 words so they will be indexed
There are only 2 rows?
heh well for right now, there will be thousands later on, im just trying to get this set up and havent entered a bunch of test data yet
50% threshhold…
DELETE * FROM mac WHERE address = (’alias@domain.tld’);
is it 50% or =50%
is that wrong?
50%
i see the table in there..
!m `mac` delete syn
`mac` see http://dev.mysql.com/doc/refman/5.0/en/delete.html
thank you
Why did you put ( ) ?
right, so then if my rows are 100% unique, then it should be returning a row
Insert one more row
the example said it like that seekwill
I don't see an example…
not on that page
What page?
i m using freebsd; and i m following this page..
kewl it worked
how can i tell DISTINCT to only look at one column instead of all of them
DISTINCT applies to every selected column.
ok but is it possible to do what i just said?
perhaps you want to use GROUP BY instead
don't help me
!man grouping
see http://dev.mysql.com/doc/refman/5.0/en/operator-precedence.html
!man group
see http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html
see a href="http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html"http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html/a
see a href="a href="http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html"http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html/a"http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html"http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html/a/a
If you have two rows, A and B. You want DISTINCT A, but B has different values, which value do you want?
hey! give it back
A DISTINCT with the first possible value of B
but w/e
i know it's impossible
or annoying to do
Define "first"
"first"
Well A is just an INT and B is a timestamp
I'm having a really dumb problem with getting mysql up and running…
Which timestamp do you want?
Don't say any…
there's only one so….
There is not only one.. if there is only one, DISTINCT would work
uThere is not only one.. if there is only one, DISTINCT would work/u
And you wouldn't be here
it's my first look at sql.. anyhow, I have installed (from repositories) mysql-client and mysql-server, but it won't let me access the administrator thing.
He specifically said "DO NOT HELP ME"
$ mysqladmin -u root password mysqldata
connect to server at 'localhost' failed
'Access denied for user 'root'@'localhost' (using password: NO)'
owm-seabrok: maybe you already set a root passwd?
chadmaynard no, this was happening right off the bat
owm-seabrok: do 'mysql -uroot -p'
does anyone know if binlog replay on 5.1 shows the proper binlog positions after a crash and restart?
innodb related, I mean
in 5.0 it just shows 0 173 as the position all the time
$ mysql -uroot -p
Enter password:
Access denied for user 'root'@'localhost' (using password: YES)
reset root
See http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html
If I import a mysql backup
owm-seabrok: ^^^ that's for you
will do, thanks!
do I need to have 2 times the size of the backup in free HD space?
I am trying to do a database of credit card transactions. They have an unsigned integer id, that is 23 digits long. For example: 24445007211915555639550, this is too long for a big int. whose maximum value is 18446744073709551615. Who would you guys store this integer
?
if my query is $query = 'SELECT email, password FROM users'; how do I echo the email addy?
you use php. And ##php
perhaps as a decimal with 24 digits?
or maybe a string?
if I was asked to decide, I'd try to pack it into a binary structure of just the right width
I know there is redundant/checksum information buried inside cc numbers, and I'd try to remove it and store only what I need
hmmmmmmm
get on with it
|
just use strings
IMO thats an excessive length
encrypted strings
and i used to work at a credit bureau
Is it really that bad to store one or two extra characters?
base64 encode it. That'll fool them!
or generate a new id to map to the long one
and then send them back the new id with the mapping file
ye, I willl just use strings, that is the most portable way
I have to read this stuff into other languages
and print it and sort it and stuff
lol @ Julian yes that would
seekwill - achieved what i wanted to do with multiple subqueries and UNION
How?
with multiple subqueries and UNION
yes, you said that already
(select id, username, 'some_attr' as attribute, CONCAT(val,'/',val) as value FROM table) UNION (select etc)
and so on
huh?
hi all!
Hi one!
I must have misunderstood your question
hi all! I got some newbie questions
can anyone help me out n_n?
not if you don't ask your question
xD okey
i just installed ubuntu, mysql and mysql admin, i wanna know, how to add services, users and tables? for some reason it wont do the work nor display the function
hello
its ok - it took me a while to get my head around it - i was trying to retrieve 4 unique rows of data, by using a single query - so I can send the proper accept packet via freeradius
once again, you need to formulate a clear question.
im sorry for my english, im good but not that much… so basically I just want to add services using MySQL Admin, since im going to start setting up the gaming server.
add … services?
what kind of services, exactly?
mmm not a service im sorry, a new database
!man create database
see http://dev.mysql.com/doc/refman/5.0/en/create-database.html
you should use the mysql console instead, and read that man page
what is your native language?
mine? Spanish o
me hablas espanol
haha
obviously, thumbs
well, enough to get around
chadmaynard–
lol "me hablas"
hey I make my own sentences
does someone feel like helping me with (what i thought should be) a simple query?
me isn't even a spanish word
I know that, obviously.
you should try asking your question.
asko el questiono
wrong person : )
lol
ok then
hablo un poco espanol
ok… well, im trying to inner join 4 tables and COUNT() 3 fields
muy un pococito
count the fields? # of rows * 3
it'll work with just one COUNT(), but when i try to count another field from another table, the resulting data is incorrect
well, i want like… clients.id, count(referrer.id), count(autos.id), etc
you are also grouping, correct?
yeah, i have a GROUP BY clients.id
offer him 5 pesos to show you the query.
haha
demostrarme la pregunta por favor
ok, want me to send u the query?
what about the pesos??
uso el pastebin, por favor
in here or in a pm?
RyukZilla is the Spanish speaker.
deseas tener cinco pesos
oops
Isn't there an #mysql.es
hey, I was practicing my spanish
!manes select
#1064 - 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 3
vea http://dev.mysql.com/doc/refman/5.0/es/select.html
pastebin isn't in /topic
I'm just trying to create a table and put 1 field in
good evening everyone
try pastebin.ca
I'm in trouble
http://pastebin.com/d109f139
ok?
ok
nvm got it
and yes, my spanish *is* terrible
the only thing I change it's the ID and there's TWO different explain..
haha
i f u
and f u i on the first one
apparently, it was good enough for p_orange. He understood it
it's really strange
\o/
I don't understand why it's not getting the same order/ number of rows
ok. here ya go: http://pastebin.com/m79665a4d
are you trying to understand the optimizer?
awesome site btw
i'm trying to understand why my query is slow 
that query looks sane. How are the results not satisfactory, exactly?
i see
do you have a clue chadmaynard ?
the data is incorrect
looks like it'd be quick from the explain. How slow?
show me how. Show me data how it should be and how it's really returned.
explain is always quick ! Slow like about 3 sec
sometimes 5sec
im sure left joins have lots to do with it
id=6 : #3 sec and id=530 : #5sec
i have to deal with INNER JOIN ?
well, explain should run under a second, regardless
If I have too much idx on a table can it slows the table ?
thumbs… ok, gimmie a sec
Are there any fixes in the mysql-proxy trunk regarding 'lost connection (..) reading initial communcation packet' errors?
indexes makes the insert/update slower and disk usage higher, but it shouldn't slow down the rest
and if I shouldn't use LEFT JOIN, what i must use ?
ok thanks
well im not saying you shouldn't use left join but if you are and you aren't using it correctly the result set can grow exponentially
okay. But what's the good use ? Because my query is really simple
why did you pick the type of join that you did?
because I have to mix up all the data and the only way for me is LEFT JOIN.
I have to select all the i.id by comparing 2 dates on 2 tables
you want this data in a pm thumbs?
I think he wants you to paste it in the channel
join.intcol1 AND intcol join.intcol2"
ok, i'll give it a shot
Client Name | c_id | cars_sold | total_members
lujack | 1 | 55 | 55 |
test dealer | 2 | 2 | 2 |
Client Name | c_id | cars_sold | total_members
lujack | 1 | 11 | 8 |
test dealer | 2 | 2 | 1 |
Why do you think MySQL cannot optimize it?
i guess irc recognizes "//"
Try a pastebin then
explain in the BETWEEN case shows join type of range est. rows of 4 where it is join type ALL est. rows of 62 (number of rows in table)
i should be add that i'm self joining here
What happens when you run both queries?
Any real-world perf diff?
probably not right now, but if i'm doing ALL there would be once i get a bit of data in there
There have been times where EXPLAIN was wrong.
It only estimates what it thinks it will do.
Purple Otterpops melt slower!
I was wrong in my physics somehow
how much slower?
I think you are just being ridiculous
25%
no way
It has been consistent over two trials.
stop breathing on the red ones
Silly, I don't do that. That would obviously change the defrost rate. I just hold the red ones.
lol
is there a good way to clear the query cache on just one query/table?
(on myisam)
anybody knows how to configure a mysql-replicate-client to start auto. the slavemode after a server reboot?
evertimes i reboot the slave he tells me that he doesnt find the binlog
how can I get to the Mysql console?
I am getting a syntax error when trying to run a subquery. Any idea what the problem is?
hmm. in the nested sets system, does there always need to be a "root" of the tree? if not, how do i know if a given node is a top-level node or not?
SELECT ProductName, SupplierID,
FROM northwind.Products
WHERE northwind.Products
IN (SELECT SupplierID FROM northwind.Suppliers
WHERE CompanyName IN
('Exotic Liquids', 'Grandma Kelly''s Homestead',
'Tokyo Traders'));
use a pastebin please
I am a little dense. How do I do that?
your problem is that IN compares to a column not a table
Are you referring to the first IN?
y
can someone take a look at this query for me por favor? http://pastebin.com/d14503c88
How would you change it?
how can I get to the Mysql console?
run mysql from an ssh/terminal session
okey
was that a serious question?
it says access denied and ye.. am a newbie so u can laugh about it
If c_id is the primary key for clients, the SQL seems reasonable. To explain the behavior, I'd need the data as well.
One possible answer is you aren't planning for the effect of N-M or several 1-N relationships.
One possible answer is you aren't planning for the effect of N-M or several 1-N relationships.
Try this change… COUNT(distinct autos.id) AS cars_sold, COUNT(distinct referrer.r_id) AS total_members
Xgc _ can u help me?
type mysql and press the enter key
Just a guess. Without knowing your data, I can't tell if that is correct.
it says "access denied for user xxxxxxxxx@xxxxxxxxx (using password: 'NO')"
mysql -uroot
i'll give that a shot Xgc
same error
The other approach is to use derived tables to provide the counts for autos and referrers per client.
are derived tables like stored procedures?
like, predetermined queries?
or someothing
Same error !
No. They are subqueries used on the FROM clause.
oh… ok, i heard that subqueries are not as good of an approach to most things as JOINS
oh S&*! that distinct worked!
*nod* You're welcome.
you think i might not have formatted my tables well?
i THOUGHT my stuff was properly normalized
This isn't an indication of a normalization issue. But I haven't lokoed at your schema. This is a simple JOIN misunderstanding. Here's the derived table form of the solution: http://rafb.net/p/ao2R5646.html
I did the best I could without having the schema to test against. I may have typos in that.
It also wasn't clear to me that a referrer match is guaranteed for each client.
So I used a LEFT JOIN.
Any idea what I am doing wrong here http://pastebin.com/d2bd03c9b ?
is ) a special character that has to be escaped?
you referring to me?
No.
The first thing you did wrong was you forgot to ask a clear question. What behavior dno't you like? What error did you see?
I don't think so. But I am a newbie. Only thing that I know has to be escaped it the apostrophe
Single and double quotes are special.
I am getting a syntax error. It says you have an error in yoru SQL syntax; check the manual that corresponds to yoru MySQL server version for the right syntax to use near 'ProductID' FROM northwind.Products WHERE SupplierID IN (SELECT SupplierID 'at line 1
Not possible.
Post the exact SQL and the exact error. Copy/paste. Don't retype it.
The SQL you posted can't produce that error.
The error message is posted here http://pastebin.com/m5c2a0d65
Now post the real/exact SQL.
The SQL you posted did not produce that error.
hi
guys, tell me how I can change a table field definition
I figured out the problem. It was an unneeded comma at the very end of the first line. It now works.
ALTER TABLE …;
Hello. May I know is there a function I can do on MySQL to monitor if there are any queries that are being executed for longer than it should be?
thanks Xgc
I would love to have that monitoring so that I can optimize my app.
See: Slow query log.
hello someone in ##php told me to ask here for help on how to grant my user access using GRANT
how would I do that
Xgc, cool. But may I know where the log is?
I think I will learn MySQL again
http://dev.mysql.com/doc/refman/5.0/en/grant.html
thanks
Xgc, found it. thanks
You have to enable it. The location is configurable. Could be in /var/log/mysql or the mysql datadir.
Thanks Xgc.
is there any way I can list all the users on my SQL server
with a query or with phpmyadmin
Hello all
I'm using PHP to upload values such as '0255' to a mysql database into an value in the table. But, the info only comes through as 255. How do I make it keep the 0?
You shouldn't let people stand on your server.
?
i dont understand
Do you want those connected or users that have been granted access?
granted access
i want to be able to view all the names of the users
SELECT * FROM mysql.user;
oh
SELECT user FROM mysql.user;
#1142 - SELECT command denied to user 'rave_'@'mysqladmin2.secureserver.net' for table 'user'
Find a better user that has access to that information.
the only user
darn
oh its because my webhost uses one server to manage all the users
anyone here an expert at creating queries that utilize index optimally
(not a dedicated server)
hope that doesn't affect me in the future
You're in the right place. 428 people. Many mysql developers/employees and other experts. Just ask and hope someone can offer advice.
xgc I have a site that is basically on its knees at least twice a day now.
oreonix are you sure the db is the bottlekneck?
ChrisPartridge, it feels like it
23 queries per second
when i type status in the console
i have a data table with a little over 2 million rows in it
and I dont think my indexes are doing anything for me
data_id | dtstamp | listeners | song | stream_id | is my structure
actually
let me put this in paste bin
brb
!man create table
see http://dev.mysql.com/doc/refman/5.0/en/create-table.html
hehe. http://dev.mysql.com/doc/ has broken links to the documentation….
I am not if my index helps at all
Which links are broken?
the ones that are the html online
sure8
sure*
man i am having problems typing tonight
:-D
they show up as https://docsrva.mysql.com/docs-translations/en/refman-5.0/html/manual.html here
^^
hmm
So I have 16GB of RAM on this Sun Fire 4600. And I've given MySQL really high settings for key_buffer, sort buffers, etc.
But when I run top, MySQL shows only a few hundred megabytes being used, and most of the memory is coming in the VIRT column
the real mem is like 650M, and VIRT is 2900M
dont set key_buffer higher than 4G (4096MB)
does that mean MySQL is swapping out of disk?
key_buffer is like 2GB
im not positive but i think if the ram isnt actually used yet linux wont actually allocate it out of real memory, i've noticed that before, feel free to look into that properly.
so it show sup as virt
but thats a mostly guess from me
wonder how you can find out if your programming is being swapped to disk
does "free" show swap being used? ?
yeah a bit
28228
Around 27M
So I configured top to show SWAP, and it shows a 2.2GB SWAP
interesing. mine shows 1G
(yet i've only got 2M swap used)
boohoo, see how many blocks are used versus not used.. show global status like 'key%';
83240 vs. not used : 1631496
so it's saying it aint really using all your blocks anyways, lower the key_buffer_size variable and increase other ones
I've noticed that too about my key_buffer_size, rarely ever uses the full amount
or even 1/2
maybe cause it's accessing so many different tables and only caches what it decides will be used in the future.. who knows
I wonder if it'll help to increase the query_cache_size to like say 4GB
boohoo, don't!
each update will invalidate all those queries… heh
k, any reason why?
hmmm…. well it's mostly static
someone wrote about a bug with query cache recently anyways .. i can't remember what it was but it was just like 1-2 nights ago i read it
if it's mostly static, do like 128MB
and then monitor how much is used vs how much is free.. and then increase it if you need oto
too
hmmm yeah i guess no point wasting it
boohoo, for me .. I'll use a lot of memory tables .. mysql will show like 1.8GB of ram used but really it's not…
At this point I'm repairing tables worth 23.1GB
also I run with swapiness at like 10
ohh.. myisam_key_buffer_size=1800*1024*1024;
is it 32 or 64?
err.. is it myisam_sort_file_size…
one of those … will help with fixing the tables
myisam_key_buffer_size=1800*1024*1024 ? is that KB?
Hello, while EXPLAIN shows how a statement is process, may I know how can I know the time it takes to process a statement?
a query, i mean
buffer_size is like 128M for me
boohoo, sec lemme see which it is
set global myisam_sort_buffer_size = 1800MB ;
it won't recognize the MB .. so you gotta do :
set global myisam_sort_buffer_size = 1800 * 1024 *1024;
reconnect, repair table
should go faster
there may be a few more variables that help but myisam_sort_buffer_size helps bigtime
shit, 1800MB?
May I know what is the best way to index a DATETIME column?
I tried indexing it but the query still doesn't use it
uzyn, read the part of the manual that says "how mysql uses indexes"
that may reveal the solution … I don't know offhand tho
ok Tapout. thanks
uzyn, you get the time of the query at the end of the execution.. that's the only way
got it. thanks Tapout.
i thought there's an easier way by using some built-in mysql timer or something
myisam_sort_buffer_size should be like in two digits of Memory
why?
well I wonder what 1800MB will achieve
1800MB per thread?
Hello friends
Y need some help
whoever broke the mysql.com/doc … forcing it to go to the intranet site.. fix it pls
boohoo, hopefully this pastes properly
myisam_sort_buffer_size
The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.
http://forums.devshed.com/mysql-help-4/slow-to-add-an-index-186951.html
Friends i need your help urgent please :'(
uggh.. your urgent isn't anybody elses
just ask your question
Thanks
Modifing my file my.cnf
Y modify the size of the Innodb log file
When i did that my tables where corrupted
A messages of erroneous data in .frm files appear
I go to the help in http://dev.mysql.com/doc/mysql/en/repair.html
Creating a new BD with the exact tables schema of my corrupted tables
you're right
But my .MYD files are deleted
Is there any possibility to restore my information
?
no
if you hav myisam tables and deleted the .myd your data was deleted
if innodb then you don't have .myd
The engine is Innodb
But when i follow the procedure moving the new .frm and .MYI files to the old ones and execute an REPAIR TABLE and follow an SELECT
The table is empty
innodb stores data in ibdata files
CREATE TABLE wil_aliases (
alias_idnr bigint(21) NOT NULL auto_increment,
alias varchar(100) NOT NULL default '',
deliver_to varchar(250) NOT NULL default '',
client_idnr bigint(21) NOT NULL default '0',
PRIMARY KEY (alias_idnr),
INDEX alias_index (alias),
INDEX client_idnr_index (client_idnr)
) ENGINE InnoDB DEFAULT CHARSET=utf8;
This is the SQL CREATE of my tables
plz, use pastebin
:P
Any suggestion?
i know this is kind of a stupid question, but is there a way to control what number an auto-incremented field starts with?
yep
ALTER TABLE t2 AUTO_INCREMENT = value;
oh
does that effect performance at all starting with a larger index?
no
sounds cool to me
you can do the same with CREATE TABLE. It goes at the end with ENGINE IIRC
oh
thanks for the help chadmaynard
Hi Greetings!
If i take the backup of .frm, .MYD and .MYI … will I be able to restore it to the DB or tables later ?
Friends thanks a lot
"Googling" i found the solution
And is more easy that i think
yes assuming you lock everything or stop the mysqld process while you copy the backup files
Thanks
yeah I lock all the tables and once copying is done I unlock
sure then
thank you )
*wrong smiley forgive me for that!
lol
Possibly stupid question, how can I insert an .sql file into a running database?
what OS?
Debian Etch.
cartesian mysql -uUSERNAME [-pPASSWORD] DATABASE_NAME /path/to/sql/file.sql
Thank you.
hmm, I'm not sure if openvcpd wants me to make the database or no. How would I make the database first and set a password?
CREATE DATABASE name;
Heh, thanks.
GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost' IDENTIFIED BY 'password';
funny thing…
Lost connection and the transaction is committed anyways
nils_ you must have 5.0.2_magic
indeed
only that it is 4.1.11
hey all… hopefully a real quick one, im looking for a client application i can run here to edit my servers mysql database direct.. any bright ideas?
windows, preferably open source
sqlyog
not opensource maybe, but free
good enough i suppose
im unlikely to actually change anything in the source, i just like to use OS where possible
wow.. this thing looks like a way bigger hammer than i was expecting
that's what she said!
Anyone who consults or whatever. What do you use to generate invoices? Any decent tools/web apps out there?
I use to con people, and I use to insult people, but not in combination
invoices? quickbooks
nils_++
good humor there
for the 1 to 10 invoices a month I use openoffice
especially for the small business there doesn't seem to be any software which makes life easier
hmmm.
hmm I think it's time to switch to mysql 5 soon
I've never used it, but I know MS is offering Microsoft Accounting Express 2007 - http://office.microsoft.com/en-us/accountingexpress/FX101729681033.aspx
I thought Microsoft only builds Mouses and Keyboards?
alright, that was pretty dumb. Just ghosted myself.
Is there a way to get the category ID to show up in the search? Right now only the company name and supplier ID are showing up. http://pastebin.com/d7f1b5f7a
is there a way to make an INSERT statement return a value? specifically, the index of the row inserted?
you need to read up on mysql_insert_id() - http://dev.mysql.com/doc/refman/5.1/en/mysql-insert-id.html
k, will do
also, is there a seperate way to do it using "last_insert_id" or something?
select last_insert_id();
well, that's easy enough
can anyone here help me figure out how to set up mysqld to accept connections from another computer?
is there any reason why my program would work on xp but when i run it on vista i get sql connection problems?
!tell mvfeinstein about external
mvfeinstein remove bind-address= and skip-networking from my.cnf and grant permission to the external 'user'@'host' and remove any firewall rules blocking port 3306 and make sure no overrides on the mysqld commandline
Usually you get an error message…
ye its just an error message that ilooked up and means LoAdS of things, Unexpected Connection Termination
is it possible to perform an ALTER table statement to add column only if that column doesnte xist
And what program are you running?
myslq
one i made in c#, it looked up a table and selects a few things, nothing complicated
mysql
seekwill did that already
That is all you need.
If you wrote the program, you need to add better debugging messages to your app
seekwill ok but its still not working
You didn't do everything then
seekwill ive debuged it and ive found what causes the error but thats what i dont understand, its a simple SELECT * FROM tbl_a WHERE x = 1 sort of thing nothing complicated at all, why would that cause an unexpected connection termination only on vista?
It's Vista…
??
I checked my.cnf and neither one of those things are one there i added a rule to iptables on the server to allow port 3306 and restarted it then I grated premissions in mysql but when I try to conect I still get an access denied error
What is the exact error?
Access denied for user
I'm sure it says more than that.
'root'@'10.10.10.12' (using password: YES)
Why is it so hard to get info from people wanting help?
How did you add that user?
here is the command I used to grant premissions grant all on *.* to 'root'@'10.10.10.12';
You didn't specify a password?
In that case, you shouldn't be specifying a password when trying to connect
seekwill good point lol
It could be something where the connector is not compatible with Vista…
damn it, i hate making stupid mistakes…. thank you for your help
See, if you didn't paste that last part you omitted before… never would have known!
That's why it's important when someone asks EXACT, you give EXACT and no modifications.
sorry I was just proof reading it to make sure there wasn't anything I shouldn't be pasting to irc in there
Not like you couldn't change a password or something
There's not much sensitive information you can paste.
A root login without a password perhaps…
I have been working for 12+ hours and typing so many connection strings with public IPs usernames and passwords I just didn't want to make the mistake
thanks again for your help
Hi every1!
How to get all categories to max. 2 depth in "nested sets"?
can you check if column exists in mysql 4
in mysql idoes subtraction have to be -= 1? when performing an update field = field + 1 works for addition, but field = field - 1 does not work
any ideas on what im doing wrong? im sure its something dumb im missing
hi, can somone tell me why this doesnt work please? http://rafb.net/p/cJBReA34.html
seems like there is something wrong with your code or your server.
or compiler
or operating system, or vm
thats very specific answer xD
lol
l
the problem is with the param, its not getting filled
how do i order by A, then order by B in a select?
instead of A, B
because A is DESC, B must be ASC
ORDER by a DESC,B ASC ?
aha
anyone awake?
nope
WHERE X=…?
better use an index
okay; i want to use nested sets and should "left" and "right" parameters be in one index?
WHERE left5 AND right6 ORDER BY left;
should be in one index.
have to read on that a bit range matches are difficult.
grr. /me can't figure out the math right on moving nodes in a nested set tree. does anyone know how to do this?
(i should add that i'd like to do it in one update)
hi
why is it tht i cant connect to a ms sql instance, but i can if there is no instance?
what do you mean ms sql?
how are you connect?
sorry
its not mysql… its ms sql
ok
you can call microsoft for support
heh
aight
lol
at least I hope so. You pay for it… Well for windows it's always "ask your hardware vendor" as most windows installs are OEM stuff but with SQL Server…
dunno… ive got 2 machines on a lan both with ms sql, on the 1 its set up with an instance, on the other not
i cant connect to the instance install
n i cant figure out y…
perhaps the MySQL channel isn't the place to ask for help wrt MSSQL
you are right
i know…
there might be some channels which are more windows specific, usually most MySQL users haven't touched SQL Server, only other unix based databases.
postgres is console only, no X involved
hehe
hmm and the postgres channel is not so bad after all, but it's some time ago since I used postgres
now hes gone /me admits to having mssql on a box to do a migration from
shame on you!
hehe. doesn't count though if you are migrating off it
I wish the migration toolkit didnt have silly install bugs though
i can't put an IF() inside a BETWEEN can i?
postgresql?
not that i can give an honest opinion, 99% of my experiences have been with mysql so far
I'd say it depends.
factors?
usually choice of RDBMS is given, you can't use postgresql like mysql and vice versa and expect one to perform better in all cases
yea thats a fair call
each has its plus sides
one example is subselects, postgresql optimizes them way better than mysql
mysql seems to focus quite a bit on performance in certain areas, whereas postgres focused more on raw functionality by comparing what each can do already, mysql is playing catchup there on functionality but i couldnt say whether postgres lacked on the performance side or not
but if you use mysql a lot you are used to that and use joins for most cases because a subselect isn't really needed (the infamous left outer join is an example)
I saw a nice barndoor security hole on pg
i guess considering mysql has had most of the functionality for a while now, theyve had time now to try perfect things performance wise
hehe
well postgresql aims for feature completeness and accordance to standards
with performance being a secondary goal
yea, completeness definitely
yep
vice versa on mysql you think?
yes
i tend to think mysql wants to add functionality but not at the cost of performance
thats what ive found
hence why its taken a little longer
well they do, but you can break standards accordance some times if you need performance, for example you won't need ACID for many applications
mmm yea
plug in engines is nice
is it good to run an internet ads agent?
gah damn rats
stupid food processing business next door to us, their infestation spread to us
I think most databases are equally usable, each has it's downsides which you should know and work around
just heard them rustling around in the ceiling a min ago
yea
agreed nils_
I personally don't beleive there is database abstraction at least from a performance or extendability viewpoint
If I decided years ago to use postgresql and perl instead of php and mysql I might as well be hanging out in their channels now
If I have lost /var/lib/mysql/ibdata1 but still have all the innodb files for my database in /var/lib/mysql/foo is there any way to recover database foo?
you have innodb_file_per_table?
I doubt there is a way to recover without the main datafile…
'fraid not
well then it's all gone
