i need to “rotate” a table (it is some sort of log information), what is the best way of doing it? also, when i “create table like… ” what happens with FKs ?

depends on if the user was set up with host-specific privileges or not

it connects just fine through the old server, and the password was unchanged in the code when i moved it to the new server
ToeBee how would i find out

the “user” table in the “
mysql database

SHOW TABLE USER?

select * from mysql.user where User=’me_admin_user’;

Empty set (0.08 sec)
oh wait
typo

select user, host from mysql.user…
simpler view

indeed it is limited by ip’s
i see a list of ips that this account can be
and the new server isnt one of them
+—————+—————-+
| user | host |
+—————+—————-+
| me_admin_user | 192.168.254.64 |
| me_admin_user | 192.168.254.65 |
etc
theres like 8 of them all of which correspond with the old server

so, is there a better way for table rotation than “lock tables read; rename table foo to foo1; create table foo like foo1;” ?

so i take it another ip needs to be added. how do i do that?

http://mysql.com/grant%20syntax

k

Lo, in order to change the path where databases are saved do I just change the ‘datadir’ in /etc/mysql/my.conf and move the folder to wherever ?

poncha ok some clarity here GRANT ALL PRIVILEGES ON database TO ‘me_admin_user’@'theipaddressineedadded’ IDENTIFIED BY ‘thepassword’ WITH GRANT OPTION;
where database hosting is the database?
thats the part im confused about is that where the database name goes?
according to the example it’s set to *.* and i want to make sure im not breaking stuff there are numerous databases on here

its database.table

oh so i could go db_name.*

If I’m wrong about the database datadir thing, just buzz me – it seems to have worked

*.* means all the server

yeah i dont want that
so ok i will limit to database.*

yep
you might wanna “show grants for user@otherip” and copy them

Greetings. — Is it possible to request how many datasets would be affected if I do an update with a constraint from a joined second table? I think to remember that “limit 0″ is not reliable in such a case… And it is not easy to check, only with a database backup in case it fails. Do you
remember quirks here?

usually you can create a SELECT statement with the same conditions as you would use for an UPDATE and see what rows will be affected

Ah, the simple solution…
Of course.

is there a way to “turn” an already existing column of unique integers into the index?

!m rook2pawn alter table

rook2pawn see http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

poncha ok i think im done, do i need to run any sort of save or reload command now?

Bye.

probably something like alter table add index(column)
or unique index
or primary key for that matter if the table doesn’t already have one

is there a difference between add primary key and index

there can only be 1 primary key on a table
primary key means it is indexed and it is unique. A normal index doesn’t require uniqueness

do both aid in speeding up update queries?
and if you make a column (that meets the criteria for primary key) into primary key, does it mean you dont have to make it an index ?

depends. indexes can actually slow down updates/inserts if you are changing the indexed field
primary key fields are indexed automatically

poncha?

oh okay
so ALTER TABLE tbl_name add primary key (inventory_id);

or actually ToeBee now that ive made these GRANT changes do i need to save flush or load anything
or can i just exit

that looks about right
I believe with grant you don’t have to flush privileges… although doing so shouldn’t hurt anything

ok

i just executed that command, and it took 0.5 seconds to finish on 40,000 rows. does that mean all my updates where i look on inventory_id are going to be faster now?

“Connection OK” awesome thanks guys

quite possibly. depends on several factors

and did it auto-check for uniqueness on that column?

yes
a primary key is guaranteed to be unique

wonderful. what is the error message if i try to add primary key (column_that_ain_unique)

something about violating uniqueness
oh thats right. ERROR 1062 (23000): Duplicate entry ‘39565′ for key 1

hey, I have a web app that uses mysql and I want to give a minimum mysql version… is there any way to tell what version to list?

Hi
I am devloping a newsletter system currently that has to support send schedules. Whats the best host way to store a cron like schedule in a sql database ?
database ?

MySQL keeps restarting roughly every minute.

Is there no way to test what version my app requires?

can anyone help me with this please, my programs giving me hell http://rafb.net/p/1LnCV365.html

and keeps crashing…
How can I debug whats going on, a INNODB table is constantly marked as crashed…

it works from my pc whats on a LAN with the DB but not on a remote connection, and the connection string is poiting to the WAN ip

ajohnstone, do a memtest

How can I do that, just installed memtest86+-1.26 from yum, however I can’t seem to find the executable for it.

Hey guys. I have a question. What is the mysql hosting binary log used for? Is it used (only) to rollback transactions or are there other uses for it?

Hmmm does memtest have to be done during booting?

if I have a table reference “t1″ (`tablename` AS t1), and a field `id`, do I refer to it as a) `t1.id` or b) t1.`id` ?

hi! when I want to insert a new invoice & invoice_row in my invoicing system. What kind of locks/transactions are adviced?

ajohnstone, look at error log – InnoDB will kill the server hosting if it finds a checksum mismatch. Quick fix is to do a power off and power on and try again in case it’s a transient RAM problem. If that doens’t work you’ll need to say what the error in
the error log is, just what InnoDB says the problem probably is and he two checksum lines, not the whole page dump.
ajohnstone, memtest86 needs to be done from a boot disk, nothing else ot run at the same time. It veyr often finds RAM problems that cause InnoDB checksum failures – if power off and on fixes your problem it’s almost certain that he cause is bad RAM.

Cheers, will try a quick reboot.

proycon, try it both ways and see which way gets you an error message.

Anyhting I can tell from free -m ?
or vmstat

error log
if it’s being killed wth innodb complaining abotu bad pages vmstat and free are useless.

Ok will try rebooting first.

do I need to lock writing to the invoice tables? or the tables from which I get the info that is put into the invoice?

hmm.. I suppose I can do that yes

is there any way to increse the time out so i dont keep getting MySql.Data.MySqlClient.MySqlException: Connection unexpectedly terminated?

hi, i wrote a very small php script to test the connection to the database. when i run it with the shell, it’s ok (`php foo.php`), but when i run it via the website, it says “Client does not support authentication protocol requested by server; consider upgrading MySQL client”. any idea
?

hi, just created a php script, to edit a row within a table, it pulls the information from the database, but fails when trying to write the alterations back, http://www.nottzclub.co.uk/edit.txt

Giddion55, see http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html and search the page fo timeout. You wil find the answer.
vinadelmar, see http://dev.mysql.com/downloads/connector/php/

anyone?

Jamesday, it’s for windows

vinadelmar, what do you want it for?

redhat 7.1

I have a field which has the data “. 0,00″ the ‘.’ can be different currency symbols, What is the best way to strip that field from unwanted data (which can be variable) so it allowes me to do a sorting on it (e.g.: WHERE rent=1000 ORDER BY rent DESC )

but i’m reading a post that could help… brb

vinadelmar, see last answer at http://dev.mysql.com/doc/refman/4.1/en/php-problems.html

Anyone knows where I can find ODBC drivers for accessing Microsoft Access?

how do i force server to answer to all clients with cp1251 encoding? without ’set names’ from client side?

when I create a foreign key, and remove it using drop fk… the index that was created still exists. Can I remove that?
…. or why isn’t that removed together with the fk?

why would you want to remove the index

Jamesday restarted the server, heres the current mysql log. http://bullbearings-data.co.uk/mysql.log I cannot see anyhing in it atm really besides it restarting alot.

Well the add FK statment created it.. so when I ‘undo’ the “add fk” statement it seems logical that it removes everything it created

Any other ideas, what I can check?

ajohnstone, is that the file ending in .err in the mysql data directory?

Hmmm, one moment will get that up too.

how can I view what foreign keys are created on a table?

i need help fining out why mysql eats my server’s resources …

TV-SET, usually poor sql hosting or poor use of indexes os explain

we have a few hundred web sites hosted on the server and most of them are mysql-dependent. everything was fien for a few month now, unti ltoday mornign

os=use

s/fien/fine/

look at the slow query log

how do I do that?

!man slow query l

see http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

thanks

Jamesday, thanks. reading..

TV-SET what did you install or upgrade or get mentioned on CNN today?

cnn option is out of the question
it would have killed us dead

can anyone point out a way to strip all but numerical characters from a field? during a select statment, I’ve looked into regex but I don’t think thats what I could use

TV-SET, ok, slashdot

no, no, nothing like that

slashdot is a small place that only adds 500 -600 page requests per second for a slashdotting
bslashdot is a small place that only adds 500 -600 page requests per second for a slashdotting/b
ubslashdot is a small place that only adds 500 -600 page requests per second for a slashdotting/b/u
TV-SET, somethign changed, need to find out what- sudden change probably isn’t a server tuning question. More likely someone installed a new application or something that’s using a lot of resources.

this is a bit difficult to find out…

TV-SEt – slashdotting is insignificant. And I’m one of the people who can honestly say that – it isnt’ even a blip

it’s a cPanel web hosting machine with about 850 accounts…

TV-SET, it is your best option- something hanged and you need to find what to blame
and one account…

a search for recently modified files will take a couple of weeks, me thinks

TV-SET: was query caching on, and with the upgrade off.. ?

TV-SET, get a copy of mytop and run it with mytop -s 10 and hit the i key. It’ll show you the longest running queries, then you can see which user they are coming from.

waaaaaazzzuuupp…

please help! anyone knows phpmyadmin?

thanks

TV-SET: if you can’t get mytop working, try out mtop, something similair, but the only one that worked for me w/o installing too much other things

there were no system upgrades that I know off.. chances are it’s an update to one of the web sites or something like that

TV-SET: well you updated cpanel

DavidHKMrPowers, this is not a phpmyadmin support chan

haptiK, I’m being mean and saying that Slashdotting is an insignificant blip.

nope.. I would have known that it’s a scary thing to do and I try to avoid it as much as possible

huh?

just a bit

While idly amusing myself until I’m tired enough to fall asleep by answering questions here

in that case, mind giving me a push in the right direction ?

oops

http://paste-it.net/2992 I get error 150

Dynom, use replace()

isn’t there a way to get the reversed result of replace() ?
the problem is that the contents of the field changes sometimes, ( hence the reason I’m fixing it atm )
would be great to not strip out unwanted chars, but to extract only the wanted
like I would have expected from regex tbh

or use the app to clean it

archivist do you know how to increment a field if it exists?

can anyone see any problems with this file, http://www.nottzclub.co.uk/edit.txt, it views the row of my db, but when i try to submit edited info it fails

yeh thats the problem, that is out of my controle, I’m writing a program to interact between two

DavidHKMrPowers, update …where

k1ckn1ck, what’s missing is he error message or error code form the server

I’ll stick with replace then, hoping for a proper regex implementation (for as far that should be part of a database anyway)

heh yes

how would i get it to feedback the error?

archivist how to update by ++ ?
i can’t update with a value
i can update with a value

field=(1+field)

archivistttt

Jaxxxxxxxxxxxxxxxxxxxxx

Dynom so easy

archivist i’m just putting together a new db server… do you think RAID would make sense? also hardware or software?

DavidHKMrPowers, so easy that you should have tried that first

archivist sql is a difficult lang

Jamesday what about slashdot? wtf?

Jax I have see recomendations for particular Raid levels and som NOT to be used
seen

haptiK, was joking about sudden increase in load. Not important.

Jax but Im not the person to ask about raid

Jax, how many servers have the same data? If you have say four servers with same data, RAID 0 is nice.
Otherwise RAID 10 is nice.

only 1 server Jamesday
i haven’t seen you in a long time Jamesday

Jax, you have my sympathy – you’re going to suffer when it has a hardware problem

hehe

Jax, work and sleep and other things have been keeping me busy.

ok, 2 boxes then.
i can use my box at home for replication

Jax, hardware trouble is when you thank your preferred deity that you got yourself a replication slave

i think i’ll have less hardware problems with a non-RAID system actually…

hi.. I have a prefix of a number, and i want to check the best fit on a database of prefixes.. (different length), can i set how many chars i want to compare in the query?

Jax, with one server I’d try to get a second server bewfore worrying about RAID, unless you have a specific performance problem you need to fix

true

ok, mytop is working ..

is it possible to create an FK or index only when they are not defined? (so like create table if not exists..)

what does “Delayed waiting for Insert” mean in terms of CPU usage?

TVSET, means that you wonder what it is waiting for
TVSET hit the i key if you haven’t tried that yet

i did
it’s waiting for half a minute or so..
so there is no need to hit i to long

how long have the top 5 queries each been running?

2946 DELAYED maronite_m 67 Delaye Waiting for INSERT
67 seconds I presume
the other ones are ‘Sleep’
which I guess is just that

what is CPU load like?

is 2GB RAM enough when using query cache a lot?
guess i’ll just have to try

delayed user is the background insert delayed handler thread, is significant sometimes but it’s not the cause of the work – is mostly ignorable.

did that SHOW QUERY CACHE; patch ever get committed by the way

Jax, try a test, fill your2G query cache from one table then change one row in the table. let me know how long your server locks up for
jax, I bet it’s at least 60 seconds – could be five minutes
Jax, max useful sie of query cache is usually no more than 100M or so – has scalability problems whent here are a lot of free slots.
but this is query cache not buffer pool or key buffer

i’ll test

Jax, see http://lists.mysql.com/internals/19534
Jax, see a href=”http://lists.mysql.com/internals/19534″http://lists.mysql.com/internals/19534/a

=D

Jax, see http://bugs.mysql.com/bug.php?id=21051 to start. It’s not really themain issue but that is at least a bit better

hi
I am having a problem with a query and I don’t know why
wh you do select * from table where name like =”%nicolas%” you get all rows that has, at least, a par of nicolas in the name’s field, isn’t?

what kind of program do you use to browse and edit data in your mysql database (except mysql console client) ? I am currently using quest software toad for mysql.

ouch… “create table foo like bar” ignores FKs /
?

http://pastebin.co.uk/20329

case sensitive?
Nicolas?

this is my query, and I have no idea why in the firs statement I get 126 rows and in the other 1512
David, whatever
in both I get whatever

ur missing some joins

me?

yes.

so the second select is wrong?

hm, yes.
http://pastebin.co.uk/20329#comments

when doing a mysql repair (because it claims that a table is corrupt) but it takes far too long, how safe is it to interrupt (CTRL-C) the repair process – or will it just make the data potentially *more* currupt as it is said to be?

use “show processlist” to see whats going on

yeah, we did that. but how safe is it to interrupt it, because it most definitely will still take about 8+ hours (which we just can’t wait)

http://paste-it.net/2993 when I call it I get: PROCEDURE db.get_invoice_price_info can’t return a result set in the given context ?

Which is quicker? VARCHAR or TEXT?

hello there i have a not running mysql installation where i want to get my data from. can iIjust copy the db data files to the new installation and start it to get to my data or is there another way ?

dont know exactly, sorry. in ur case, i would do a backup of the mysql datafiles (*.frm, *.MYD, *.MYI …) and try to interupt (if you realy got no time).
but dont blame me please

thank you…
of course, i’ll refer to you when something shit happens :p

.p

dub di du

o.o

anybody ? if yes do i have to pay attention to anything ?

yes that worked for me one time. but dont coyp the “mysql” table/directory.
*copy

which one do you mean
I’ll copy /var/lib/mysql right ?

okay, anyone want to point out the disconnect between my interpretation of docs and what I really need to do? GRANT ALL ON email.* TO email@’%’ IDENTIFIED BY ‘email’;

you mean /var/lib/mysql/mysq
you mean /var/lib/mysql/mysql ?

and then I try to log in with those creds… and get denied
I’ve tried FLUSH PRIVILEGES (which was the wrong answer and I knew it, but I had to try it)

yes

I’ve tried restarting mysqld
again, shouldn’t be the answer, but I tried it anyways

GRANT ALL ON email.* TO ‘email’@'%’ IDENTIFIED BY ‘email’;
but, you realy should not create a user with such a week password that has access from any host

mysql only listens on 127.0.0.1, hachi doesn’t care
mine had no syntax error, and the row is in the table, so I’m going to be angry if your answer does it
the row is in the tables and is correct

trapni, killing is safe. File a bug report if it causes you a problem.

whoops, I had the single quotes around the ‘email’ anyways
GRANT ALL ON email.* TO ‘email’@'%’ IDENTIFIED BY ‘email’;

trapni, then search for setings to use “repair by sort” and make it fast.

thanks

trapni, there’s a myisamchk option for it, rmember tha tyou can’t use myisamchk ona server where others can access the table you’re fixing.

negative, still no auth… and mysql doesn’t log why my auth is failing

GRANT ALL ON email.* TO ‘email’@'localhost’ IDENTIFIED BY ‘email’;

okay, does someone want to explain to me why the % GLOB ISN’T BLOODY WORKING
thank you for making it work, but now I’m angry at the mysql world for not being consistent
% in that spot has meant ‘any machine’ for years… why suddenly does it not

i am sure that there is a chapter about that issue in the docs.

well, I can’t bloody well search for ‘localhost’ in the docs now can I

Hi all, Any ideas why this query wouldn’t work?
SELECT SUBTIME(NOW(),(SELECT MAX(time_stamp) FROM ussdMon));
I just get null…

with @’localhost’ you can connect from localhost
with @’%’ you can connect any other host then localhost (but not localhost)
http://dev.mysql.com/doc/refman/5.0/en/adding-users.html

“Two of the accounts have” .. there is it.

ahh, so the disconnect is the fact that an empty User field globs to ‘any’, but the ‘%’ means any host
nice consistency in globbing

stop complaining pal

Which is quicker? VARCHAR or TEXT?

bruto_price DECIMAL, to OUT bruto_price DECIMAL, I get a general error … someone who knows what this can be
http://paste-it.net/2994

mysql_query(“DELETE FROM vacation WHERE id=$id”) or die(“Failed”);
why would that nto be returning an error
not*
or not doing anythign else either

how to get marks/results after a match/against query?

how do i take the average of x number of columns from a particular row

Hello using mysql with php, if I have a remote database, mysql_pconnect is better than mysql_connect?

hi to all
can anyone help me ?

ask first?

im in need to make a script to make hotcopy of mysql between 2 pc
but i dont know which mysql way is the best to make hot copies
can u please advise me ?

mysqlhotcopy?

orion setup mysql replication?

is that the best way ? mysqlhotcopy ?
should i cron myqslhostcopy for a 2 pc case ?
i mean, i want 2 mysql db on 2 pc . the first is the master. if it fails the second pc takes over.
so , loadbalancing + hotcopy .

replication?

cluster

Add lustre to your cluster with knacker lacquer

hmm

uhh, what?

hehe

wow
thats the last time i ever usep hpmyadmin

replication is easy to set up

very easy
and clustering wasnt much worse

explain please

cept i was trying it on shared storage :s

anyone use phpmyadmin?

replication is where you have a master db which sends all inserts,updates, and deletes to any number of slaves.
or you can have multiple masters, and any db can be a master and a slave

and the master/slave roles are setup by modifying the mysql.ini file ?
or do i have to do sth else ?

yeah
my.cnf

thats all for master/slave roles ?

if you want failover you probably want clustering

how can i setup cluester ? which commands do i need to use ?

if you have a slave that takes over when a master fails and you do any inserts the two will be out of sync

http://dev.mysql.com/tech-resources/articles/mysql-cluster-for-two-servers.html
its not perfect, but very good

thats the best one ?

no, you can write up the best one

do i have to insert the ‘out-of-sync actions’ manually ?

hi