I get "Can't connect to MySQL server on 'www.zendurl.com' (4)" from $con = mysql_connect("www.zendurl.com:80",$username,$password);
that's not very informative 
I'm positive my username and pass are correct
hmmm
but is that for local host or tcp connections
helo
use a semi colon
i upgrated mysql from slack 11 to 12, and my database is gone
ThinkMedical ya… how to use returned results directly for UDPATE?
UPDATE blah WHERE blah; SELECT * FROM blah WHERE blah
unless im wrong (?)
ya…
say, if SELECT returns 100 rows..
use update ffs
ffs?
archivist a little bit more hint please?
your questions are not clear enough to get more hint
ok…
it shows a basic missunderstanding of basic sql such as update
you want to return the updated rows from the query? essentially something like SELECT "UPDATE…"
I need to SELECT * FROM `crawl` WHERE `last_crawl_time` 100; UPDATE (these_resulted_rows) SET `last_crawl_time` = 101;
hehe
sigh
he has no idea
even spoonfeeding him fails
put your select in a WHERE clause on the update, DavidHKMrPowers
UPDATE `crawl` SET `last_crawl_time` = 101 WHERE IN SELECT * FROM `crawl` WHERE `last_crawl_time` 100
no selecat at all
oh
same table… sigh
just a where clause needed
get rid of "WHERE IN SELECT * FROM `crawl` " DavidHKMrPowers
um…so what would be returned resutls?
do i still get the certain rows updated?
phpmyadmin returns nothing for this…
the rows update and you get a boolean result
oh…i can't get only a boolean,..
i needa to get the rows also… since i need to crawl them
the use a semi-colon
why i update them is to udpate them not be crawled in short time
UPDATE `crawl` SET `last_crawl_time` = 101 WHERE `last_crawl_time` 100; SELECT * FROM `crawl` WHERE `last_crawl_time` = 101;
after I change my.cnf (which I found on my own - woot) I assume I have to restart mysql? (since changing my.cnf did not seem to make a difference)? I've looked around for docs on restarting mysql - Im looking for something like graceful — this seems overly complex for just restarting the
database nicely: http://dev.mysql.com/doc/refman/5.0/en/mysql-server.html
?
ThinkMedical ya…it could but still no…because there could be other rows whihch habve `last_Crawl_time` = 101 too
needa update exactly the last selected rows
youll probably need cursors and such
o.O
there's a RETURNING keyword in pl/sql not sure about std. mysql sql
oh…btw…innodb and MyIsam which should i use?
maybe becasue i use the incorrect db type?
i am using myisam
Doesn't matter. Just make sure your search criteria only picks the last 2, using an explicit ordering since rows in a table have no guaranteed order.
Don't select. Just perform the update properly.
By doing this you won't have to worry about any sort of locking.
Xgc ya…but i need to get what i 've updated
i mean i need to get the rows i updated
If you have a specific that isn't behaving well, post it exactly so someone can help you with it.
they have tried
Ultimately, you may need a safe process, if you have requirements beyond just updating the rows. You could even use a trigger and a column that indicates who performed the update. By doing that, you can determine which rows you updated without being bothered by any updates by other users.
The trigger could insert logs into a separate table to indicate which rows were actually updated.
Xgc ya..i also thought of this method…but things would be complicated then…
Actually, that simplifies the process.
This creates a queue instead of requiring locks. You can take your sweet time without worrying about impact on any other users of the table.
you can use cursors in mysql?
You can.
you can use FETCH then i guess, with a cursor
possible to get last_update_id ?
if possible i would update it first and thene select
Via trigger. Otherwise, not directly, without locking.
i let it be..
greetings
Host '192.168.0.31' is not allowed to connect to this MySQL server
any idea?
it means what it says, you need to grant your user rights to log in
i did
GRANT ALL PRIVILEGES ON *.* TO 'oo'@'*' IDENTIFIED BY 'XXX' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
can anybody help with this? If I aquire locks using GET_LOCK from within a table trigger, will it stay locked until the trigger runs again and unlocks it? or automatically unlocked when the trigger finishes?
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
all the case expect what is "external" ?
woa
% instead of *
aep and check if you have an anon user grabbing the login
found. thank's anyway
havea nice day
hi, i'm trying to sum the last 6 values for a praticular IP in my db… select sum(amount) from accounting where ip=$ip will give me all.. how do i make it limit to 6?
i hv been installed mysql .. at rhel 4 .. how to start its service .??
service mysql start
or service mysqld start
… order by timefield desc limit 6
thx pb
(where "timefield" is some field that identifies the rows chronologically, obviously.)
it returns -bash: serice: command not found
did u miss the v out?
try "service mysqld start"
oh.
can i print somewhere which i get … basically i hve been remove php mysql web hosting .. now i install it again
?
what are you trying to accomplish, exactly?
if service doesn;t work, u can use /etc/init.d/mysqld start
or /etc/rc.d/rc.mysqld start
i hv tried to install mysql .. but getting msg .. there is no mysql.sock .. i could not locate it
so i m installing again ..
that would be an error you get as you start mysqld, not install it.
u logged in as root?
the sock is created by running mysqld
check if u have /etc/my.cnf
read the INSTALL docs for your distro. You need to install the initial database with mysql_install_db
from where .. will u pls guide ?
that depends on your distro.
using rhel4 … will u pls check, this link http://sial.org/pbot/26565
read paragraph 2, please
but before you can do that, you need to start mysqld
I would recommend asking in #redhat or #fedora to inquire on the exact startup script location.
ok
cluster works with MySQL 4?
I have databes with 120 000 records in the biggest table. But my application starts to working slowly after inserting of these records. I optimize the indexes of the tables. What other i can do to optimize the queries? To use SQL_CACHE?
!man explain
see http://dev.mysql.com/doc/refman/5.0/en/explain.html
i used explain to see where to add indexes
!man cluster
see http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-backup-concepts.html
bobby switch the slow query log on and look for the root causes
SELECT DISTINCT trade_shows.show_id, trade_shows.show_image , trade_shows.show_name , trade_shows.start_day , trade_shows.start_month , trade_shows.start_year , trade_shows.end_day , trade_shows.end_month , trade_shows.end_year , trade_shows.show_id , trade_shows.location ,
trade_shows.organizer , trade_shows_industries.industry_id , ref_industries.industry , country.CountryName from trade_shows left join trade_shows_industries on trade_shows.ind
Can anyone see why that would output multiple show_id's?
pastebin you got cut short
multiple rows in trade_shows_industries that match the id.
yep
thanks i didnt know that
snoyes you looked at jaypipes blog?
basically the query outputs exactly the same information, for however many results are in trade_show_industries
I just want it to output one result
you should just group by show_id
hello
haven't seen it today
ah ok, thanks pbro
DISTINCT goes on every field
k
.. and not just the first one.
Are you referring to the picture of Marten?
i am having a series of what appears to be mysql server crashes on a debian server, what should i look at to find out the cause?
snoyes its the benchmark tool name
oh yes, I saw that.
Was going to comment about it, but there's no underscore
yea that worked, thanks pbro
anyone could give me any advice on how to sort multiple rows to keep certain order? i mean how, for example sort something in the way that row with prev_id = x follows a row with id = x
field
ORDER BY FIELD( country, 'US', 'Canada', country ), country; // This will order the list by placing the US first, Canada as second, and the other countries in alphabetical order
Hm, maybe that's not quite what you were after.
sounds like you want to order an adjacency list; that's tricky for mysql.
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
easier with a nested set.
or materialized path.
thanks, i will google that i had no idea how to call this way of sorting
can i pipe the output from one select into another? i want to do .. select distinct(ip) from accounting; select sum(amount) from accounting where ip=$ip order by date desc limit 6; but use the ips from the first select in the 2nd
!man subqueries
see http://dev.mysql.com/doc/refman/5.0/en/subqueries.html
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
thank tyou
tcp or socket ?
Hello!
I'm thinking about category tree and I will probably IP.trees.
Hi, I want to set up some backupscript for my sql. mysqldump only requires SELECT, right? (So a GRANT SELECT ON * TO 'backupuser'@'localhost' IDENTIFIED BY 'supersecretpassword' should do it?)
if I have a result set that I want to use in my application, but also use as input for another query, is temporary table what I want?
However, i have some performance questions.
anybode in here uses cluster with mysql 5?
body
amigrave, i would think socket would be quicker than tcp, as there isn;t the static ip hosting header involved
hehe.. would think..
What is faster? 1. Sorting records by 3 fields. 2. Searching by: LIKE "10.5.%" (% only at the end)?
can anyone give some pointers on how to diagnose mysql crashes, i am stuck, and can't find anything helpful on google
thanks
Are you using MySQL's official binaries?
resolve a stack trace, as the crash message suggests. create a repeatable test case and submit a bugreport
SettlerX, what sort of schema are you using?
Can you reproduce the crash?
it's a debian sarge server, so they are debian's version
SettlerX, can you paste bin the "SHOW CREATE TABLE" and indices for it?
Hi there
the server host seemed to stop responding on friday, i restarted it, there was a corrupt table, which I repaired, all was well
trudim test hardware and try mysql.coms versions
I juste got a "Out of memory (Needed 8164 bytes)" error on my MySQL server
with "mysqld got signal 11;" after that
np
this morning all the websites that use mysql were down, everything else on the server is working perfectly
look at .err files and syslog
i am trying to find some log that will point me to the cause of the failure,
the /var/log/mysql.err is empty
is there a way to know what is using so much memory ?
Legor, what sort of system are you running & how much memory do you have?
i'm running Debian 3.4.3-13sarge1 with 8Go RAM
and you need MySQL to use all 8gb of ram? you 64bit from system to userland?
i don't know if i need so much memory, thoug top gives very few free memory
yes this is a 64bits system
check the output of free -m
and make sure the mysql you're using is native 64bit
what should i check in "free -m" command ?
ugh
Legor, how much is being used for buffers & cached while mysql's running
basically you're trying to work out if MySQL is hitting an imposed limit, or a hard system limit
mysql is running now and i have 5054 used for buffers/cache
k, so you're only using ~3gbish
you know what explicitly triggers mysql to run out of memory
e.g. can you replicate the problem all the time?
no i don't
and unfortunately no i can't reproduce the problem on demand
i can only wait and see that it happens
can you upload your my.cnf somewhere
yep
to you know some upload site ?
it might be something silly like having too large per-client buffers, getting a sudden spike and needing 8 bazillion gigabytes
uh. paste2.org
i think i have standard config
ah paste it anyway
i only had to change max_heap_table_size and tmp_table_size to 1024M
here you go :
http://paste2.org/p/5303
what do you think of it HarryR ?
uh, is this a dedicated MySQL machine?
Is there a my.cnf equivalent to skip-name-resolve?
Or is that option only possible through the command line startup?
Legor, you're using the very small defaults, on a system with 8gb of memory you can push some of these up quite a bit
yes it runs only mysql
which one for example ?
Typically the command line options work in my.cnf, just lead off the leading –
you can probably increase the key buffer to a GB or two
query cache upto 32 or 64mb
actually i had a previous conf with higher values, but the servers crashes frequently with "mysql got signal 11" error
ok, it looks like you're running the 32bit executable then
go and get the 64bit build of MySQL so it can actually access 8gb of your memory
instead of getting killed off when it tries to access more than ~3.5gb
how do you see that ?
k..I'll give it a try. Thanks
It's a guess based on what I've seen so far
Legor, see the my-huge.ini example file
e.g. http://atonal.ucdavis.edu/matlab/database/mysql/my-huge.ini
and how can i check i'm running the 64 bits exe ?
if you run `file /path/to/mysqld` it'll tell you the ELF header
wait for a while..
e.g. ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.4.1, dynamically linked (uses shared libs), stripped
what is the "file" command ? i don't have that on my system
it works out the filetype based on a set of configurable rules, so if you're unsure of what a file is you run "file" against it and it matches it against known formats
Which distribution are you using?
thank you, snoyes. worked perfectly. I had tried that before, but I had put –skip-hostname-resolve in the my.cnf file by mistake. It worked now that I typed it correctl.
Legor, show variables
Hi Guys, how to enable mysql to allow remote connections, ie. from another machine in the office?
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
show variables gives the following :
version 5.0.41-Dotdeb_1.dotdeb.2
version_comment Dotdeb Etch backport
version_compile_machine i486
version_compile_os pc-linux-gnu
486 there you go
sounds good ?
no thats 32 bit
so it's definately 32-bit, so you won't be able to use more than half of the memory on your system
which is probably why you're getting memory allocation problems under high load
Legor, are you using Intel, AMD or other processors?
AMD64
k, when you run uname -m you get x86_64 right?
exactly !
which distro are you using?
Debian 3.4.3-13sarge1
oh sorry, I forgot
np
hello
hello Nomalz
Legor, check http://dev.mysql.com/downloads/mysql/5.0.html#ubuntu and make sure you get the AMD64 server
HarryR i introduce to my server admin Nomalz
and associated packages, I can't find just a plain non-rpm or deb version of the AMD64 build
Hi Nomalz
hi
We used the dotdeb debian package version
the AMD64 build?
on debian 3.1 that we built with amd64 "experimental" kernel
(debian 4.0 wasn't released when we installed it)
i don't know which version it is 
9' DAY_MINUTE
wait pls
Nomalz, k, the only thing that seems to have gone wrong was that you installed the 32bit version build and not the 64-bit build
mysql-server-5.0 5.0.41-1.dotdeb.2 MySQL database server binaries
yep it's possible
but how can the server host use all of its ram (8go) with a 32bits version ?
it cant…
total used free shared buffers cached
8110064 7416620 693444 0 37104 1256460
the rest of the memory usage is just disk buffers & caches
3.4gb for disk buffers & caches ? is it possible ?
yeah
oh
not cool
Normalz, MySQL itself can't but if you're using MyISAM tables the 4.5GB that MySQL can't use directly will be used by the OS to cache the data rows. MySQL doesn't cache MyISAM data rows itself so this can be useful.
ok
Since the 32 bit server is also faster than the 64 bit server unless you need to have MySQL itself allocate more than 3.5GB, if you are using MyISAM and need that amount of OS cache for your data, there would be no reason to switch to the 64 bit version.
Jamesday, or like… if you have lots of clients and want to have reasonable buffers for them and were running into problems like.. running out of memory
the major part of our data is myISAM
we had the out of memory problem today only
but we had a few crashes with no explaination
how much load is the server under?
on this server and another one, which has the same ocnfiguration
very loaded
and the database is huge
and you're running with a 16mb key buffer on a box with 8gb ram?
that's 16mb for the whole of that mysql instance
we changed the value
to test
ah k
but we may need a little help on the conf part
we hade key_buffer_size = 768M before
but since it crashed, we tried the default conf…
on our first server (lelit), there is a big database which has 1.4go of MEMORY table and 1go of MyISAM table
2.4go
+others database which are about 400mo in total
is 8 gb sufficient for this kind of database ?
yes
ok
it's a first step
the serv has 288 query / s
how many average connections do you have?
between 150 and 250
if there is a configuration template for high loaded server like ours, it could help
are there any good resources out there to go to for more advanced help with join syntax? the mysql documentation on joins leaves a lot to be desired
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
problem is, if you've got 250 clients at maybe 6mb each per active thread, 768mb key buffers & 1gb maxium temporary table size, you're going to hit the 32bit limit fairly quickly
what about this conf file ? http://atonal.ucdavis.edu/matlab/database/mysql/my-huge.ini
yep :
would mysql enterprise give us a little help with all of this (better management & maybe more support for crashes) ?
it's very likely, but so would understanding why you're getting these issues and what you can do to resolve them
it's very likely, but so would understanding why you're getting these issues and what you can do to resolve them
k, thx HarryR
thanks guys
don't forget to thank the bots!
http://phpfi.com/253330 - but is it helpful for you?
the second: http://phpfi.com/253331
LIKE: "1.17.15.%"
Normal, Enterprise would give you MySQL support engineers to check why your'e crashing (but you'd need Platinum level for performance tuning beyond that)
the second way - we find categories by sorting by: master, step, name (from another table) - so with JOIN
Normalz, it's probably per-session variables liek sort_buffer_size that are too large.
and in the first case - without JOIN
but how is it with performance of LIKE with % in the end?
we're talking about the error "mysqld got signal 11", right ?
is it faster or slower than sorting records by these 3 fields?
Normalz, yes
we have another server which is less loaded and with the standard conf. it has 8go of ram (only use about 4 ou 5 go) and still has about one crash per month !
SettlerX, Like with on the end will use an index if one is available and also can be used for the rest of your join. It's very fast.
so i don't really think that it's a conf value problem, since even the basic conf on a huge computer makes crashes
Nomal, then you definitely need to look at your my.cnf settings to see where the RAM is being allocated and see if youre per-session allocations are too large.
the standard conf provided with mysqld can be too large ??
Nomalz, I don't recall how debian configures it. Standard configuration can allocate too little RAM for key_buffer_size so your queries are slow and your connections build up to the point that you run out of RAM.
that's an neverending problem
Nomalz, also need to look at your indexes and queries to ensure that those are efficient
But can start with my.cnf checkout first to see if that's reasonable.
yep, that's Legor job :p
i'll probably go with the my-huge.cnf
Nomalz, or mine if I wasn't on vacation
Nomal, tha'ts a good start anyway - there's nothing grossly wrong with that one and it does allocate sensible buffer sizes
yep, i think queries are mostly optimized, unless there is some that were forgotten…
Jamesday, any chance we have further information about crash looking at the backtrace ?
with "1.5.17" method (it's a path) i don't need joins. It would be an index. If it's fast, so i will implement it.
Legor, not when I'm on vacation
Legor, see how it does with my-huge.ini and can adjust from there.
ok Nomalz will have a look at that
do you think bad my.cnf can trigger "signal 11" crash ?
If you do get stuck, toss some money MySQL's way But that pays my wages so I'm biased
No wireless at the beach? Totally unacceptable.
Legor, easily
Legor, I've seen people have setups that at the maximum connections they allowed could use several terrabytes of RAM
Legor, I've seen people have setups that at the maximum connections they allowed could use several terrabytes of RAM
so our only way is to adjust the conf each time that the server crashes
Nested trees (left+right), all connections (e.g. 1-2, 1-3, 2-3) with depth, recurenction - they are more problematic
I have heard that Microsoft uses IP.trees - true?
they also use the blood of puppies
Nomalz, or you can watch memory allocation to see it rising. Or cut back on sort_buffer_size and the other per-session variables
evil doers
DSal-Rak: can you extent your words?
*Extend
Your load doesn't sound high for the box you have so it's probably just tuning
Though when you crash, don't trust he automatic MyISAM table check - do a manual check table for each table to be sure.
that what we do
we always have one to three crashed tables
that block our scripts
what will happen if we don't check tables manually ?
(that's how we know that mysqld crashed…)
Corrupt tables can cause the server to crash. Can end up with a nasty cycle
we have MEMORY table that we use for rankings (it's being recalculated each 30mn, and each table is 700mb), maybe it could be better if we converted theses tables to MyISAM ?
Worth a look at your mysql error log file as well to see what it's complaining about - see if it's complaining about being unable to allocate RAM
Nomalz, why do you need memory tables? What is the operation that you're doing with them that caused you to use them originally?
we neved had any ram error before today, and it's only because we switched our conf to the standard conf to test
our main problem is error 11 crashs without any other error (mysql.err staying empty)
Nomalz, so your own config worked, standard causes crashes?
standard caused crashes quicker
ah, quiker is OK - so need to compare yours to standard and see what he differences are
how many sort_merge_asses do you see in show global status?
sort_merge_passes rather
also standard causes "Out of memory" error
hi!
Therion, I'm not here, you don' tsee me, I'm an illusion Hi
The differences are mainly the per session value, which were bigger than the "my-huge.cnf"
wherease own config gives only "signal 11" error without mor information
hehe
Nomalz, OK, which ones are bigger, which smaller and what does sort_merge_passes say?
sort_merge_asses: 617 (it's still running with the standard conf now, so i don't know if the value is really useful)
OK, how much uptime for that?
from smaller to bigger : standard conf my-huge.cnf our previous conf
1 hour and 28 mn
Nomal, interesting.
I have a stuck z key btw
^^
what is sort_buffer_size?
in each config?
in standard, there is no sort_buffer_size
in my-huge, sort_buffer_size = 2M
in our previous conf, sort_buffer_size = 32M
32mb per client! ouch
OK. right HarryR
Nomalz, OK, calculate sort merge passes per second with current setting
current is standard
two show global status 5 minutes apart, subtract values, divide by difference in uptime
Then try sort_buffer_size = 4M and see what difference that makes to the rate of sort_merge_passes. Adjust until you have found the lowest value of sort_buffer_size that doesn't make sort_merge_passes increase t a fast rate.
ERROR 1148 (42000): The used command is not allowed with this MySQL version
but i cant see what ia m doing wrong
LOAD DATA LOCAL INFILE '/home/cot.co.uk/autofiles/cotmembers.csv1' INTO TABLE members FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' thats what i am doing in mysql 4.1
k, i see
Nomalz, sort_merge_passes aren't very expensive so a few per second is fine.
that may mean lowering your sort_buffer_size value below 2M - if so, do that
http://pastebin.com/m79bdfeb1
(our confs)
my-huge.cnf is the conf that we are gonna use soon
at the next restart
we'll see if it goes better…
Nomal, choke…
max_heap_table_size = 1024M
tmp_table_size = 1024M
those are… somewhat large
yep
but our memory tables are 700mb large
if we don't use theses values, mysql says "table is full"
we may use MyISAM talbes instead, as suggested Nomalz before
and do you use 1GB temporary tables that have to be all in RAM?
that one can probably be smaller
Legor knows better than me the utility of theses tables
do you think it could help to use MyISAM rather than MEMORY ?
BDB!
Hi domas
Hi!
Nomalz, maybe
we designed MEMORY tables because there is a lot of access to this table and we wanted to make sure the server has data in his cache
Nomalz, show global status, what are the temporary table lines saying
that works with innodb too 
)
Created_tmp_disk_tables 67
innodb is perfect for hot stuff
Created_tmp_files 834
Created_tmp_tables 15 k
is that what you wanted ?
how to choose between innodb and memory TW ?
TW is the country!
oops :$
are you asking whole country to help you?
^^
Lower max_tmp_table_size to 8M then adjust until the created_tmp_disk_tables rate is about 4-5 per second
lol nope of course
if you have 700MB, you choose innodb, its easy
just turn off the log flushing
could you say why ?
finegrained locking
if we lower max_tmp_table_size to 8M, mysql won't allow us to put 700mb in our table
the maximum temporary tables sie is probably why you're crashing
or am i wrong ?
of course, BDB rocks too
Nomalz, so don't create it as a temporary memory table, create it as a memory table instead
it is a memory table !
what is create table for it?
max_tmp_table_size doesn't limit the size of memory tables - unless you've found a nice new bug
ok
so we can lower this value
ENGINE=MEMORY DEFAULT CHARSET=latin1 AUTO_INCREMENT=2660036 ;
doms could you advise when using InnoDB, BDB, MyISAM, Memory ,
?
and then all the connectiosn that need temporary tables for other reasons wont' allocate 1GB of RAM
ok, that may be better ^^
all data is cached in memory, let it be myisam, innodb or whatever
that will probably stop your crashes
there's just a question if it is backed by physical disk
what about the other values of the my-huge.cnf ? it seems ok ? ( http://pastebin.com/m79bdfeb1 )
and how well backed
then you can think about other changes liek memory or myisam or innodb choice
how many changes are you getting, etc
467
whoah
that was Therion
it can stop our crashes, but remember that we have another server with the same hardware, more database but less loaded, with the standard mysql conf (so without tmp_table_size !) and it still crashes (but less often, i admit)
Nomalz, successive refinement time after this - to allocate as much RAM as is needed and no more to the buffers - will gradually get there
domas, is there a pre-conference yet?
mmm, we're doing sightseeing now mostly
the 'conference' starts at 1st
sightseeing sounds good
well, except that it is 35C and 100% humidity
oops. seeing your pretty computer sights sounds good
nah, did spend whole day out
did have some tropical rain showers today
with lightning in close proximity every few seconds
guys, thx for your advices
yep, thanks a lot
if it continues crashing, i'll come back when you're out of your holidays ^^
domas, our MEMORY table is a huge table (2.5M records) with a lot of SELECT
which engine would you adivse ?
Hey Seekwill
oh no, I'm being singled out
I got a question for you if you have a minute
Nomalz, Legor, heh, just catch me when I'm eating dinner again. going back to playing games now
how to contact you ? you're always here, or maybe i can ask your mail ?
Why me?
I was told you might know
There are 429 other people who might know better than me…
Nomalz, you can't reliably catch me - I'm not normally here.
heh seekwill
To much presure ? hehe
oh
Nomalz, as domas said, subscribing to enterprise can catch me - I'm oone of MySQL's support engineers.
Like, look at Jamesday. He like, invented wikipedia with domas
hey Kaj!
k
hey Domas!
seekwill, heh, you're telling people all of our secrets
Ah, you work for them?
TW= Taiwan?
yep!
HI kaj
OH I didn't know it was a secret!
wow!
hi Jamesday!
wikimania!
Seriously ? I use wikipedia all the time
cool, I might be in TW in a month or so
I'm leaving in ~9 days
I guess will miss you
9 days, that's a long time!
yup, lots of stuff ahead
You are in the presence of the higher power. Bow down or ask a question for infinite wisdom
I should have arranged to visit teh apac group and had the travel coincide with wikimania. But travel… long distance travel.. yuck
lol
http://commons.wikimedia.org/wiki/Image:Wm07warm_up_party.JPG \o/
its already up there
listen to seekwill
I have a table, indexed with 1 row. A query such as "select id from table where id=3" takes 63ms. Why does it take so long?
It should take about 3ms
Also the computer is 100% idle, and has enough memory. Also it's a 2.4ghz quadcore machine.
context switching
disks arn't that fast yet
\o/
context switching?
How did you come up with 3ms?
I can use the memory engine with this table and it still takes 63ms
well, queries have to take less than 1ms usually
then test timing error!
On my other machine, that is the time the query takes
Use the other machine then
lol
perljunkie2, I was first DBA for wikipedia, domas is current. both of us work for mysql as support engineers. So if anyone ever says MySQL support doens' have people with a clue about real world big systems… .
or DNS
what else is that machine doing
or TCP connection
is it a winbox
actually there's no clue about real world
who says wp is a real world
heh
well, look where it is waiting
can be anything in the stack
ok. It's unreal that the place can raise a million dollars in donations
usually a proper database server returns in ~1ms
Can one reset the counter that MySQL uses for auto_increment?
it doesnot
ALTER TABLE
domas|TW, Thanks, I'll check that …
How do I figure out where it is hanging?
strace -t or what was the switch
-r?
domas|TW, Are you sure? "help alter table" doesn't seem to show anything allowing me to reset the counter.
see you
yes, I'm sure
mysql.com/alter+table
domas, you need to work out how many of those Taiwanese girls you can pack and where you will store them in your apartment.
lol wtf
domas|TW, Thanks - found it: ALTER TABLE t2 AUTO_INCREMENT = value;
naaah, its always nice to come here!
ant there's typhoon incoming
never saw a typhoon
should be fun.
I already enjoyed what they call 'rain' here
its probably 'storm of the century' back in europe
lol
domas, typhoon? you should arrange to visit florida dring a hurricane to complete the set
mmhmmm, would have to do disaster migration stuff
get people to migrate the servers first
no!
thats not fun
lol
if we migrate servers before disaster, no way to ask for donations
ah! an evil plan!
Anybody a fan of UserFriendly? Does Domas remind you of anyone?
Dogbert
dogbert?
hahaha
haha
seekwill, hahahahaha
Could the ms delay be due to an old OS (rh3) or old mysql (4.x) ?
no
how do you drop tables? is it simply "DROP TABLE tablename;" ?
RH3 or RHEL3?
it is
ok ty
RH3 is yes, pretty old
you can expect anything with it
lol rh3…
2 EDT
thats probably not RH3
what is it then?
RHEL3
;-)
what's the difference ?
RH3 is very very VERY old
6 years
lol ok
probably
nope, older
March 1996
thats 11 years
9 years from rhel3
still ancient
rhel3 = Oct 2003
lol http://ars.userfriendly.org/cartoons/?id=20070726
hehe wysiwtf
hehe
I can't get any work down when the company VPN is down!
The company VPN is always down it seems.
in the immortal words of jazz…. "what up bitches?"
I did the strace. It appears to take 20ms unless I am reading the times incorrectly. http://rafb.net/p/7X2kdk80.html
1185812089.268749 to 1185812089.281307
I'm not even sure what that means I guess
is it possible to have more than just two 'masters' in a master-master replication situation? all the howto's only seem to ever document 2 servers
JerJer, you can do circular replication
JerJer, you can't do star or similar however
Hi. I have a query which spends a 99% of its time in the "Sending data" state. What can I deduce from that ? What else is interesting to look at in such case ?
Subdino, and the query is?
ugh … so circular sends it from one server to another to back to first server (eventually) ?
Subdino, eg. asking for too much data , select * or a poor use of a limit
JerJer, yes, it goes in a circle
what level of detail do you want ? basicaly, select sum(my_column) from my_table where [15 boolean operators, around 4 inegality operators, 8 egality operators]
A
is that why they call it circular replication?
Subdino, that sounds like a full table scan
The strace says my query is taking 16ms, The interface says 63ms
"explain" gives a index_merge with extra "Using intersect(payment_uid,resource_uid); Using where"
xzilla, yes sir, tricky naming!
3210 rows estimation, table has 16 columns: 9 bigint, 2 double, 1 datetime, 4 varchar
I really don't get what this execution state means… transfered data should not be so big, so I guess time is spent in "processing rows" part as described on general-thread-states.html, but I have no clue what it really means
is it the cost of the index merge ?
intersect, I mean
dunno it may be, can you simplify it at all
afternoon all. in an explain, will a DERIVED table always show "using temporary"?
Yes
i wasn't sure. i knew it was a temp table, but didn't know if it would tell me that i'll stop trying to find a way to optimzize the temp table out then
Joins, Groups, and Subqueries
??
They all use temp tables
joins don't unless you are joining more than your buffers allow
groups you can get away from if you do it right
not always
I thought it was. Interesting
if index can be used, temporary tables are not used
maybe my buffer is to small
so back to my question. on a DERIVED table should i continue trying to optimize out the using temporary? or is that informational (because it is HAS to be a temporary table)
(by DERIVEd i mean a subquery in the FROM clause)
hey guys, im doing a fulltext search to grab some rows from a Postings table, at the same time I want to know if the user doing the searching has previously viewed the posting, this data is stored in two tables eg. tableA[guide_id,user_id] tableB[guide_id,posting_id] ..iv been trying some
joins but all I can get is ONLY the postings the user has viewed and not all postings from the search and extra data for th
do you have any mysql book recommendations .. a coworker just asked me about mysql books
the only good one i know is mysql performance tuning
but its a bit dated .. and focuses on a specific subset of mysql
pro mysql seemed ok from the sample chapter i skimmed a while back
SELECT * FROM postings, tableA, tableB WHERE tableB.posting_id=postings.posting_id AND tableA.guide_id=tabelB.guide_id AND tableA.user_id=123; - this doesn't really work as it only returns postings the user has viewed before
uSELECT * FROM postings, tableA, tableB WHERE tableB.posting_id=postings.posting_id AND tableA.guide_id=tabelB.guide_id AND tableA.user_id=123; - this doesn't really work as it only returns postings the user has viewed before/u
mmm, nope, didn't read any
I thought 'high performance mysql' was a little too basic.
neither have i
SOURCE CODE!
hehe
i have only read generic sql books
didn't buy it after browsing it in the bookstore. There was only a couple chapters not covered in every other SQL book
book
http://www.kitebird.com/mysql-book
and o really mysql stored procedure programming
Just read the log history of this channel.
archivist
the bot keeper at the moment
hehe
er yes
it's a pretty sweet bot actually
domas
heheee
dammit
We have 4 (soon 5) physical locations that the powers that be want to have all of our data 'replicated' at all location… would circular work for this sitaution?
\monster\: Use an outer join, not inner join, when looking for rows of one table that are not associated with rows of another table. The alternative is using NOT EXISTS (…).
JerJer, possibly, assuming you know the limitations of circular
\monster\: or NOT IN (…)
ok ill play with that
what about mysql performance blog ?
\monster\: Listen to the_wench …
a not in b
SELECT a.* FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL;
what about the mysql cluster stuff? assuming we wrote our apps to be aware of the read-only versus write-only. would that be 'better' ?
SELECT SQL_CALC_FOUND_ROWS posting_sl.posting_id, MATCH (desc_long,desc_short,response,ota_keywords,page_title,meta_keywords,meta_description,summary) AGAINST ('java') AS sort_by, study_guide.*, study_guide_record.*
FROM
oops
ignore that
\monster\: Ignore what?
accidental paste
\monster\: What paste?
JerJer, mysql cluster is not designed for geographical replication
ok - good to know
a not in b ..example would only return rows not present in b wouldn't it?
\monster\: That's the idea.
ok, now what im looking to do is do a normal search on one table and also grab info regarding past views for each posting returned for the user doing the search
maybe im making sense here, not too sure
maybe ill pastebin the actual query im trying
\monster\: The example shows you how to obtain the missing rows. Just remove that WHERE criteria to see them all, noting that nulls in the right table represent those not viewed (not found).
quick question for ya, how hard was it to get the_wench to post the correct url's for !man ? did you have to index the page or does it just post a search?
define incorrect
heh
it uses mysql xml index terms
which are borked
hmm this is sql 101, i think iv had a different issue on friday, checks
You're taking a class on SQL?
thanks
(but that stinks about the xml being messed)
nope, just saying what Xgc is describing is basic stuff so i think iv had another problem
bnope, just saying what Xgc is describing is basic stuff so i think iv had another problem/b
fatpelt its been entertaining making a reader
\monster\: Not knowing anything about tableA and tableB (in your example) makes it hard to propose a direct solution. What does tableA and tableB represent?
need to nuke this coffee
one min i think i can do a better job describing this heh
any good books on data structures in sql?
like storing trees
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
amazing…..
hi
how to dump a single table?
mysqldump
http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
fatpelt thats how we cheat ^^^ with bad index terms
Hey, is there anyone able to lend me a hand? Thanks.
this one is php-mysql and is in 3 mysql chans
it is written in php? what do you use to connect to the channel? (i found Smart_IRC and i'm not sure about it yet)
Hey, is there anyone able to lend me a hand? Thanks. [MYSQL]
its got smart_irc in the core
ask
"I have a question", Don't ask: "Is anyone around?" or "Can anyone help?". Just Ask The Question. Also, please read: http://workaround.org/moin/GettingHelpOnIrc
i guess i'll stick with it then
Yah but it's work related, so I don't want to give out sensative information, the_wench.
hehe
if you want private support and hand holding mysql.com/enterprise
Lol, archivist, that won't help either
I've already wiki'd everything, seen it etc, now I need someone to help, wether I'm going crazy or it is a genuine problem within a host or the script
quite a deal……
can't do sub-selects in mysql? like select * from foo where foo_id in (select * from bar); ?
!man subqueries
see http://dev.mysql.com/doc/refman/5.0/en/subqueries.html
Zaki, did you try it
Anyone know why some system variables, specifically innodb ones cannot be selected like 'select @@innodb.buffer_pool_size;' but others can?
ok http://pastebin.com/d3237c990 is my actual query, i want to get all rows from posting_sl table and match if possible rows from the other two tables, what I have there returns only rows from posting_sl that match the WHERe clause
user_id field is in study_guide table
errr "select @@innodb_buffer_pool_size; "
Basically, my client's website is MYSQL powered [eg, all the information, bios etc]. Moved server, everything setup accordingly, the backup was executed perfectly but none of it shows on the pages [ bios etc] which is from within the database..
EvanLugh, are you getting any sort of error?
nope
thats the weird thing, no error
the information just *doesn't* output
are you sure it isn't a rights issue?
rights issue? Please explain
often times sites suppress error messages. something like a "Access Denied" type thing
oh, nope no error or anything everything is fine apart from the information not outputting!
www.lucy-anne.co.uk/index.php [it's for a model website, No XXX]
you should see the disclaimer
Oh, no XXX? Blah, don't need to click it then
well above that, should be the information taken from the sql database, it should give an error should the database info be wrong
haha
and under the stats box
how to cast the type of output values of a subquery?!
EvanLugh, not if you have error reporting off
it's on
YES messgae or whatever
message*
what happens if you run one of the queries in something like Query Browser ?
what's that?
thats a program message not an error message
!man query browser
Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/query browser
+Sorry, I'm not good when it comes to SQL, edit variables is all :p
u+Sorry, I'm not good when it comes to SQL, edit variables is all :p/u
fatpelt as I said index terms suck
hehe
also
it worked before I upgraded the plan with the host
so i contacted them many times
did your ip addresses change in this ?
but they didnt say it was on their side
huh?
think i figured out a solution to my issue, wee
change in what exactly
monster lucky 
it seems that enterprise really wouldn't help here….
hey, is it possible to write a select statement that will select where a condition is met in any of the table's fields ? ie. for a search feature
are the ip addresses different from before when it worked ?
fatpelt, i don't see how/why the ip should affect it
an ip*
answer the stupid question
LOL
(it could matter)
ip address of what exactly?!!
the sql host ?
the db server and or the webserver. you said you migrated the system
oh
yes they did, but I changed all of the variables
(did /me misunderstand)
did you make any modifications in the mysql.* tables?
it's with 1+1 like, 1009@1+1.com _
Yes, fatpelt
EvanLugh, the php may override the error reporting settings
did you "flush privileges" ?
flush prileges?
a phpinfo() will tell you what your error logs is
um
ok
after you made changes in the mysql.* tables did you either a) restart the server or b) flush privileges
nope neither and i never edited the tables just the backup database settings
er… s/error logs/error reporting/
how to create a table from a query?
because this is the third time we've changed to make sure no downtime is occuring, and i've done everything like I have the second time, only the second time it worked fine
!man create table
see http://dev.mysql.com/doc/refman/5.0/en/create-table.html
see a href="http://dev.mysql.com/doc/refman/5.0/en/create-table.html"http://dev.mysql.com/doc/refman/5.0/en/create-table.html/a
see a href="a href="http://dev.mysql.com/doc/refman/5.0/en/create-table.html"http://dev.mysql.com/doc/refman/5.0/en/create-table.html/a"http://dev.mysql.com/doc/refman/5.0/en/create-table.html"http://dev.mysql.com/doc/refman/5.0/en/create-table.html/a/a
this was my last resort too :X
i already phoned 1+1 approx. 4 times
is it possible to setup a mysql 5.0.42 slave to replicate a 4.1.22 master?
did your ip addresses change the other time you migrated?
Hi EvanLugh. My name is Evan too.
perljunkie2 = High five
fatpelt= hostname did
or my ip? My IP changes all the time /dynamic
"select * from #mysql where real_name='evan'"
432
it all depends on how the rights are set up in the mysql.* tables as to if it is hostname or ip address specific
fatpelt= yah already edited those variables
What is the error message exactly?
there isn't one
:p
hmm. something is wrong with my query, my name isn't evan and there are 431 people here….
that's the confuzziling part
so back to my first question, did you then restart the server or flush privileges ?
I think one left sense your query
we cant restart it and no how do i flush priveleges
bah i gotta go for 30 mins, would i be possible you could tell me in a priv chat, fatpelt? OTHerwise I'll miss your answer
I really appreciate your help
We will just halt all conversations here until you return.
Hurry.
haha
nope. sorry
Everyone play world of warcraft for 30 minutes till Evan gets back
hehe
Good plan, I wish
how to rename a table ?
create table foo from select * from bar;
!man alter table
see http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
then drop table bar ?
ok
!man rename table
see http://dev.mysql.com/doc/refman/5.0/en/rename-table.html
thanks alot weign and all
when "im Weierächer" is 13 bytes long and it also has 13 characters, then it is not saved in UTF-8 format, correct?
how to get the integer value of a string?
string = varchar field
It will implicitly cast as needed, or you can use the CAST() function.
!man cast
see http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html
!man adding new user
see http://dev.mysql.com/doc/refman/5.0/en/adding-users.html
Can someone point me to a How-to on how to restore a dump from a remote server, to the local server I am working on now?
I allready grabbed the *.sql file, but my attempts to locally restore it, has failed numerous times.
o.o
you can't just mysql -uUSER -pPASS database backup.sql ?
nope
hey all can someone direct me to the link that explains how to export results to a file? I did it once before but forgot the synatax and can't find it on mysql website
hihi im back
SELECT … [INTO OUTFILE 'file_name' export_options
Etc.
k thanks!
how can i flush priveleges in phpmyadmin
please*
why not just flush them in the mysql console?
i dont know how, it was suggested earlier because im having problems
select *,foo/bar as foobar from .. where foobar 10; "unknown column foobar in where clause" - This surprises me, i was so sure that using "as" allows me to use the name later in the where clause.. no?
its simply, FLUSH PRIVILEGES;
ok where do i put that?
!man flush
see http://dev.mysql.com/doc/refman/5.0/en/flush.html
./mysql -u root -p type ur password in use your data base then type it
99; is being auto limited by something at 1000 rows
which variable/setting is that that I've recently stumbled against
!man outfile
see http://dev.mysql.com/doc/refman/5.0/en/select.html
lokieee, was that for me? nothing popped up after a bout of rtfm
anyone happen to know the export_options to outfile that will save the nice lines/table that sql select usually produces? instead of just the data? I'm using into outfile '/root/devicesOut.txt' but the .txt is just the data, and its not nicely formatted but I can't seem to find that option
kitchen no that was for me
Nope. But you can use a HAVING clause instead.
mysql dbname export.sql ?
SELECT …, expr as foobar FROM … HAVING foobar 10;
there's a flag for that on the cli tool
does anyone know if mysql dependent packages like zlib, openssl, libgcc are available by yum in 64 bit?
mysql -t …
if I had used INSERT DELAYED I might see it as delayed_queue_size is 1000
fatbelt, you alive yet, still need your help and I have some more info
fife_out.sql
woops
rofl
lol
*scans for site*
JK
luckly its local host
grr is it easy to fix that my php files don't seem to be connecting to the databases?
haha
or is there a certain chmod eg 677 or 777
677?
You want it rw by the mysql user
Make sure mysql owns it
Hey I am trying to create a table that uses the memory engine
and I need to save text data
it already is 677
of unknown lenght
so basically, the php files have the correct variables to send and retrieve/output the information into other php files [index.php].. but aren't
but it says that The used table type doesn't support BLOB/TEXT columns
is their another way?
they dont seem to be connecting to the mysql database(s)
do you have shell access to the webserver?
i want to add a n:n relationship to every field in every table of my database, to be able to add "sources" for every bit of information
hey fatpelt
nope
would i end up with a sources table and a linking table with keys "table" and "id"?
i deleted the database
and nothing changed
no errors
so im guessing, the php files weren't contacting the mysql databases
to retrieve the info
or maybe a separate linking table for every data table?
anyone got some input on this? freebsd 5.3
do a phpinfo() and see what your error reporting is.
mario# mysql_upgrade
mysql
Can't find 'mysql'
ok hold on fatpelt
fatpelt
http://lucy-anne.co.uk/phpinfo.php
a href="http://lucy-anne.co.uk/phpinfo.php"http://lucy-anne.co.uk/phpinfo.php/a
what connection method are you using?
connection method? eg- FTP?
for mysql. pdo/mysql/mysqli
is there an expectation of when 5.1.x will be made GA?
I expect it tomorrow.
back
mysql fatpelt
GA?
does your code look like this $conn = @mysql_connect(…) ?
or maybe I should ask if there is a published timeline?
general availability
johnny generally available
oh..
yes fatpelt
ie marked as stable
the @ supresses the error
that's what i just say
here's a part of one
$cn_models = mysql_pconnect($
please help!
DavidHKMrPowers, please read http://catb.org/~esr/faqs/smart-questions.html
SELECT `A`.`a`, `B`.`b`…… how to mix them together to become one column?
don't say another word in here until you do
say, column a is "file://" , column b is "install.bat"
you could always say H. E. L. P [ it's not a word, it's scrambled letters! ]
please h.e.l.p
i was joking _
\o/
Any ideas, fatpelt? OR do you need more info from me xD
do as johnny said and read that url
fatpelt ok..
but it's not related to my question
you won't get any answers until you read it
and would it be a good idea to recreate the database, so that I can see if it connects as of yet, fatpelt?
that url i believe is related to any question
fatpelt, nice response.. i'll use that
i wonder if i should have said "that url is related to every question"
i suppose.
it still feels like a permission problem. where the user that you are trying to connect as does not have rights to the database
that's just me being picky though
Ok I am getting ready ro move from one server to another. Now both the servers are running 5.0.27 the Database uses InnoDB the binary file is 67GB. Now i need to move the site with the least about of downtime. any ideas?
Ok fatpelt, so what should I do? It's frustrating, fifth day with this problem!
It's all chmod to 677 already =s
EvanLugh, same thing here
Should i setup replication on the servers and have the new server be the slave?
but i wait patiently just like everybody else..
!man permission
see http://dev.mysql.com/doc/refman/5.0/en/query-speed.html
see a href="http://dev.mysql.com/doc/refman/5.0/en/query-speed.html"http://dev.mysql.com/doc/refman/5.0/en/query-speed.html/a
huh? does that answer have anything to do with the question?
What isyour problem?
how to make columns other than the primary key column unique?
that's a good way.
UNI
sorry that was for EvanLugh but it messed up
!man unique
?
see http://dev.mysql.com/doc/refman/5.0/en/constraint-primary-key.html
make the field not null and unique
fatpelt, i know
i was suprised at the answer
But can the slave handle changes while it is a slave ?
call me Evan must be annoying writing EvanLugh all the time _
altho i don't realy know how to query the thing personally, i could tell that answer was wrong
tab completion
so not really
EvanLugh just type ev then hit tab
as long as there are no EvanSomethinelse
well blow me
ok well that just did it for me johnny XD
i'm doing that not for your benefit, but for everyone else's
i don't know what to tell you. it seems like the webserver doesn't have permissions to connect. i could be wrong there, but that's what it seems like
Right, fatpelt, I'm editing all the variables for a new database now, and making the database, then I'll add the backup and let you know if it works, which it presumably won't considering it'll be the 4th try.
seekwill, my problem is such that i'm working on an open source project, which is a moudle for a cms type system , it does comments
does any of you have a function that would replace a certain word in each of all fields in a db?
it is implemented with some hybrid nested set and something else model
so i'm trying to bring it inline with it's true nested set roots
i start to like mysql
Were you working with Xgc?
correct
he was a big help
Oh good luck then I'd stick with him
how is mysql comparing with postgresql?
hi
now i'm trying to reorder the results by the thread head
I will to known how to import a backup made with mysqlhotcopy ?
adding backup now
seekwill, i'm trying to spread the joy around
i would like*
lol
being somebody's personal tech slave is no fun.. so i was trying not to mention his name
err his/her …
DavidHKMrPowers http://monstera.man.poznan.pl/wiki/index.php/Mysql_vs_postgres
DavidHKMrPowers, it's faster last i remember..
PHPNuke Web Hosting | PHPNuke Hosting
?
