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..