Im trying to use the MySQL migration tool to migrate an Access database to my MySQL database but the tool just

CheapWebHosting.co.nz

So Im trying to make a method in a web framework do something its not supposed to Assuming Im limited to a query

YEAR(scheine.datum) to get year in your where clause
Any idea from someone for my sequence problem ?
http://pastebin.com/929519
I d like a seq id for users in the table
each user got its own sequence
I don't know if it could be easyly done in php mysql hosting ... iInstead I would use a query with the MAX function ... I think it is low
*slow

Hello folks

Dont forget the bots!

Client does not support authentication protocol requested by server; consider upgrading MySQL client

I have mysql-server and mysql-client 4.1.22 installed from ports on fbsd 5.4
I don't understand how I can get this error

afaik there is a page on mysql-project page regarding that auth error

!man old client

see http://dev.mysql.com/doc/refman/5.0/en/old-client.html

mysql-project?

mysql.com :d

I don't think its valid here since I have the same! server host and client versions for mysql and from ports
But I'll check

Haris, but which client (eg php webhosting one?)

aha! good point

Client java api hosting version 4.0.27
Is that too old?

have you read the url above ?

I do understand what's mentioned there. Thankyou!

afaik the relevant gap was at 4.1 or something like that. i guess reading the article should solve the problem, as i had that some month ago too

guess, I need to re-compile php

From the shell however
I do get connected fine
so that URL ( http://dev.mysql.com/doc/refman/4.1/en/old-client.html ) perplexes me to a certain extent

i need a command for cleaning up duplicate entries across two columns of a table. ie duplicate rows

dupes

find them with select count(dupefield) as qty,otherfields from table group by dupefield having qty 1

yeh u told me yesterday how to find them
but then how do i do a command to delete the duplicates and keep one

delete dupes

If you have a unique ID and the name may contain duplicates then DELETE t1 FROM table1 t1 JOIN table1 t2 ON t1.idt2.id AND t1.name=t2.name if you have other fields that need to be taken into consideration extend the join as needed

No more, now I know why.

Any idea from someone for my sequence problem ?
http://pastebin.com/929519
I d like a seq id for users in the table
[ each user got its own sequence
I don't know if it could be easyly done in mysql web hosting ... iInstead I would use a query with the MAX function ... I think it is low
*slow

good morn all
whatever happened to mysql workbench for os x
?

search for db_mysql.inc, read function nextid

even then windows version seems abandoned, but i cant even find one for os x anymore

workbench went back to alpha status
chrislewis, have a look at http://sourceforge.net/projects/dbdesigner-fork

appreciated

I did a mysqlhotcopy of a database and didn't get the TABLENAME.MYI file. Is it necessary for restoration of a backup?
I figure it's only the index
and will be regenerated?

: you can not answer my question ?

progzy, maybe wrote a trigger
write

: you never had to do suha thing ?
*such a

no, or have a table with the sequence positions in (a pain)

so if I have a file with multi-byte characters, what all do I have to make sure so that it imports properly using LOAD DATA INFILE?

ok thanks ... (why a pain ?)

if multi user then needs care with locking and and whether to deal with cancelled updates (or do as an update to an already validated insert)

do you mean there could be a "chance" that I get the same seq number for a entrie if 2 users use the system at the same time ?

thats a danger from poor code yes

: well thanks .. I can not see what would be the solution to prevent sucha thing ...

is there anyway to search for partial words in a full text search?
anything like "LIKE"

does DISTINCT knock out rows that have at least one field in common?

is there any way to select on one row and end up with multiple rows?

fieldB i want two rows

should only knock out rows that have all fields in common as i understand it

hi
how can i know the numbers of rows in table which column2 value is abc

select count(*) from table where column2 = 'abc'; ?

let me check
it didn't work

add a "group by column2"
no wait
what are you asking

how can i know the numbers of rows in a table which a column value is abc

why does the dev.mysql.com/doc not actually have documentation on things like GROUP BY
they have "how you can optimize your GROUP BY queries", but nothing about just using group by

they assume you've bought one of their books on basic sql host syntax and know how group by's work already

hi all
i get this log
0 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1048583

ahh they are pimping the books

what i must have ?

you need one more megabyte

heheh, or a better query

heh

i've seen that message a few times when i botched a join

rook2pawn where i must put ?
wich line must be edit

find the query that causes it, and correct it

hey all
quick newbie design question

use int

sweet solved
part of a site im designing has a request board type feature
people log in, make a request, goes onto the board

and?

which is stored in a table requests, with information like the name of the person requesting it etc
when people click on it, they can go in and leave comments

these are more statements

is the best way to do that make another table, called comments, relate it to the request number and just have it cycle through and pick them up
because it seems if theres 5 comments per request, 500 requests a day
the comments table is going to get pretty big, pretty quick

that's probably the way i'd do it

If there are multiple comments per request it should be a related table to keep the database clean and normalized.

what is a "request" a question?
soo this is a forum?

it basicallt functions like one, its a car part
people make a request from a form
hits the request board
other uses can log in, answer it
and yeah, multiple comments/answers per request
so crrj, the way i was thinking isnt best, ie, with a large comments table?

ahhh, yeah, it would make for faster queries (and cleaner db, as mentioned) if you have subjects in one table and relate it
s/subjects/requests/

Certo, the comments would be in a seperate table
Linked on the request id

yeah, thats what i was thinking

write it the easy way and get a bigger HD and more ram

if i had a int field with in requests table, so i knew the amount of requests
sorry comments, for a particular request
i can have mysql only return the amount, to make the query faster yeah?

There used to be a wiki on FAQ for debugging connection problems, and I want to refer someone to it. Anyone know where it is?

Any suggestions on the easiest way to import a csv file to MySQL?

SELECT count(requestid) FROM requestcomment WHERE requestid = (42)

load data infile

As long as the requestid is indexed properly in the comment table it should be quite fast.

so its not worth having something like SELECT commentAmount FROM requestBoard WHERE requestid = ($requstid)
just looking for the fastest way

maybe, but only for a summary page

Hi Shrews!

Certo, the drawback to having the number of comments for the request as an int in the request record means that whenever a comment is added or removed for a request you have to be sure that that number gets changed as well.
Makes for messier database.

so design wise its not good
speed wise is there any real advantage anyway?

great
thanks for the help

not if it's properly indexed

Used properly indexes are your friend. I saw one site where the database had the primary key, then another index on the same column.

I have a huge DB (100mio+ rows in some tables), and I want to do different queries on the same subset. The subset

how many rows can could the comments take before i saw some real slowdown retrieving them?

I have a huge DB (100mio+ rows in some tables), and I want to do different queries on the same subset. The subset is found primarily by using (expensive) string comparison. Is there an easy way to re-use the subset when I've executed the "expensive query" once?

the first thing that comes to mind is a temp table.... someone i used to know used that hammer a lot

[intra]lanman: I've had the same thought. Maybe a temp db, as the subset involves several tables
[intra]lanman: Or should I just use several temp tables?

i actually like the idea of an entire tmp db.... that way you don't need to worry about losing precious data by granting on db.*

exactly. Using a View isn't gonna save me anything, right (except some typing, maybe)?

of that i am not sure

hi
i just installed mysql-server and when trying to chg the password with mysql -uroot password 'newpassword' it gives ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

mysql -u root -pnewspassword or mysql -u root --password=newpassword or just mysql -u root -p and enter password when prompted

ok gimme a sec

[intra]lanman: Ok. I'll go for the temp db, then :-) Thanks for your help.

no success
i just installed it via repos and i am on ubuntu-server

YES" ?

[intra]lanman, Thanks. Load data infile did the trick (with some manipulation :P)

oh yeah, fields terminated, etc, etc
np

Did you change the password during the installation? It's blank by default, typically, and you have to change it explicitly.

ok wait
no success
is there anyway i can remove it and redo it?

reset root

See http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

sudo -k done

The alternative at the bottom of that page is typically the easiest to follow.

how do you determine if you have no results from an sql query
in php

mysql_num_rows returns -

mysql_num_rows?

hmm...ok but this is funny lol, cuz I just installed something and it has a password in it

thank you!

brb and thx snoyes and the_wench

DJ-_-: Have you read https://help.ubuntu.com/6.06/ubuntu/serverguide/C/databases.html ?

readin nw

sudo mysqladmin -u root password newrootsqlpassword

doesn't ubuntu add a service user like debian?

ya but doesnt work

mysql_num_rows(): supplied argument is not a valid MySQL result resource ... im using $result = $GLOBALS['sql']-query($query)

rook2pawn, should learn about sql injection

presumably there's an error someplace. Does your db abstraction provide for echoing the error generated, via mysql_error() ?

worst code seen today

what site is this on?

rook2pawn, you screwed up, you failed to use a valid resource as the argument. Why it is invalid? Because the query failed and you did not bother checking if it succeded!

how do i type a space in .htaccess?
i mean for rewrite_mod
e.g. ^folder name/

query returned no results, not failed

what would i put instead of a space?
ah wrong channel sorry

mysql_num_rows is not erroring if the query is valid but does not return any rows
you did not pass it a valid resource PERIOD

dj-_-: I'd try googling for "reset root password mysql ubuntu".

i am doing what is listed on mysql site for reset

do a var_dump on the parameter, if it is not a mysql resultset resource, you screwed up

DJ-_-: Ok. Too bad it doesn't work out of the box.

well what the hell, the method wont work

ya its starting to annoy me

DJ-_-: I found something in the forums: http://ubuntuforums.org/showthread.php?t=305018

lewking

hello.

not sure I can do private messages w/o being registered or something like that... I've tried and it never worked. Going to research it more now...

| Michael | v. Rhein | rhein | 950.5 or 950,5 ? |

http://pastebin.co.uk/17106
?

what's wrong with the update query on http://rafb.net/p/MffdwA71.html ?

Oh. No big deal, was just going to mention we were about to put your toolkit to work figuring out some replication issues we've been having.

cool

Kartagis or xaprb, snoyes idea?

missing the SET

SET?

UPDATE table SET field = ...

oh, that set. I thought it was another abbrev.
well, thanks anyway

!m ThoMe mathematical functions

ThoMe see http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html

round or truncate on that page
!m ThoMe string functions

ThoMe see http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

format() on that page

!man unicode

see http://dev.mysql.com/doc/refman/5.0/en/charset.html

how can I get the top 10 most popular values of a field?

define popular

select count(*) as popularity from table group by field order by popularity desc limit 10;
?
that's just a shot in the dark since, as shabbs mentioned, we don't know what you mean "popularity"

bbl

With mysql-table-checksum, how do I specify different ports for the two different hosts? I want to compare localhost:3306 to localhost:3307
nevermind, I got it.

with popular i mean the m ost common
for example i have a field called country in the users table and i want to find the countries that most users come from

so the query i showed you didn't work?

select count(country) as countryCnt from table group by country order by country LIMIT 10
err
order by countryCnt

how did you do it?

hi everybody

archibal

everytime I restart mysqld server ; daemon.log says :
/usr/bin/mysqladmin: connect to server at 'seb.archibal' failed
'Lost connection to MySQL server at 'reading initial communication packet', system error: 111'

localhost:3306 localhost:3307

no idea, please ?

Works nicely on my Windows box. On our Linux server, we get "Can't locate Term/ReadKey.pm in @INC" .. is that something wrong with our Perl installation, or with our mysql-table-checksum installation?

/etc/init.d/mysql[29008]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in

whoops. Term::ReadKey is for password prompting. I wonder if I didn't make it a prerequisite in the makefile. You should be able to fix this by a) CPAN install Term::ReadKey or do it via your distro's package management system (I'm sure it's in there) or b) just comment out the "use Term::ReadKey" if you're not using --askpass

I'm trying to use the MySQL migration tool to migrate an Access database to my MySQL database, but the tool just won't connect to the server. It's on the localhost, and I can log in manually with mysql.exe and with phpmyadmin; this tool just fails to connect (connection could not be established). Any ideas?

a "PURGE MASTER LOGS BEFORE YY-MM-DD HH:MMS" doesn't purge any logs, unfortunately. what could be the reasons?

!man purge master

see http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html

need a 4 digit year

ok, sorry, I *did* use a 4 digit year
"purge master logs before '2007-06-15 16:30:00';"

have you ever deleted any files from the disk by any other means? If so, MySQL thinks they are still there, and will silently fail.

I even used dates in future, but it still says "0 rows affected"

use SHOW MASTER LOGS and see if any of the resulting rows have zero size.
if so, that means MySQL thinks a file exists, and it doesn't.

that might be a point, in fact, "show master logs" shows lots of files with filesize zero as another guy today morning just rm'd them from the fs

that is the issue for sure.

but how do I clean this up?

clean up [hostname]-bin.index to remove files that don't exist on disk. I would stop the server if I were you, because when I did this without stopping the server, my slaves got into an infinite loop in the binlogs.

How can I GROUP BY conditionally? I need something like 'GROUP BY agent IF stamp stamp2 + 500'.

that makes no sense

aah
can I do that w/o stopping mysqld?

kalikiana, HAVING?

I want to group all rows by 'agent' where stampstamp2+500. Better?
TSCDan, I don't know, tell me. :/

kalikiana, HAVING stampstamp2+500
The HAVING clause

Why not use WHERE?

Don't you need to use HAVING when GROUP BY is used?

Hm... I didn'T find that in the manual, I'll try that having.

What do you want to do with the rows that do not fall into that cat?

Well, you CAN, but I'd watch slaves carefully if you have any. See this bug report; if you see similar behavior, add your voice to the bug report: http://bugs.mysql.com/bug.php?id=28421 I might do STOP SLAVE on one of the slaves, if you have any, so in case the others get into trouble you still have one that is OK.

the point is, you either group your entire resultset or you don't. you can't group certain parts of it. you could do two separate queries for where stamp stamp2 + 500 and one for where that's false

HAVING is used to filter aggregates generated by GROUP BY. Otherwise, you use WHERE to filter data going into the result set

we've currently no slaves connected, as we first have to get rid of our log-bin problem on the masters

probably safe to do then.

as we've approximately 140 inserts/sec (with 2k inserts/sec highest). so the binlogs are making our disks full incredable fast.

shabbs, Actually I want to "merge" the rows which meet the condition. Is there no way to do this in one query?

do a two-part query, one part grouped and the other part un-grouped, and then UNION them.

Is this a good place to get some help with the mysql++ library?

xaprb, I'll have to look up how UNION works, sounds good so far.

the mysql-5.0.41-freebsd6.0-i386.tar.gz archive is broken or something, not sure, when i unpack it i get the error "tar: Unrecognized archive format: Inappropriate file type or format" after a short while of unpacking, the md5 does not match the one on the webpage but i've downloaded the package 4 times now, in different ways and i still get this error
i don't have any problems downloading files from other sites like php.net or apache.org

the binlog.index file *should* be recreated, on mysqld start, isn't it? in fact, it's not there anymore, but I definitely did not delete it (maybe the other guy having access to it who tried to get some diskspace back)

how do can I tell mysql to send output to stdout/stderr instead of a log file under linux? (according to docs commenting out log-errror in my.cnf should do the trick, but it does not)

I think so it will recreate it, but I'm not sure how smart it is -- it might not make entries for existing files when it starts. Let me know. I've never tried that.

switched mirrors and now i get the correct checksum, the dataphone mirror is broken

xaprb, Why does this fail? 'SELECT * FROM stats WHERE type = '' ORDER BY stamp DESC LIMIT 0, 30 UNION SELECT * FROM stats WHERE type = '' GROUP BY agent WHERE stamp stmp2 + 300 ORDER BY stamp DESC LIMIT 0, 30'

what's the error?

You didn't say what error, but: see http://dev.mysql.com/doc/refman/5.0/en/union.html and it explains how to put ORDER BY on a UNION.

and generally selecting fields that aren't in the GROUP BY is a bad idea

+1 to shabbs, generally a very bad idea.

I'm sorry for forgetting the error message. Like on that syntax page, I removed the first ORDER/ LIMIT. But now I get a syntax error: SELECT * FROM stats WHERE type = '' UNION SELECT * FROM stats WHERE type = '' GROUP BY agent WHERE stamp stmp2 + 300 ORDER BY stamp DESC LIMIT 0, 30'

you still didn't provide the error :P

Wait, I believe I see it myself... o-O
shabbs, I said "syntax error", is that not enough?

I'm not an SQL parser, that's what the server does. generally the full error message tells you where the problem is

hi, how many characters can be stored in a longtext field?

2^32

that is characters, rather than bytes?

Well, no, I think that's bytes. So the number of characters will depend on your character set.

But I'm not certain about that.

Hey folks. I keep getting error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)' after moving the mysql installation to another box. So, I logged into mysql as a superuser and executed "SET PASSWORD FOR 'debian-sys-maint'@localhost=PASSWORD('mypassword');", followed by a "select Password from mysql.user where User='debian-sys-maint';", and copied that hash...
...into my /etc/mysql/debian.cnf, but I still get that error.
Any suggestions? Does the PASSWORD() function encrypt differently than what the system expects?

Can you make --askpass honor a set -u and not ask for the user again, or make the argument to -p optional like the other mysql client tools?

honoring -u definitely, but I have to research the -p behavior. I think Getopt::Long will be hard to convince to do that

I imagine. That's why mysql doesn't allow a space between -p and the password

Ok, so I fixed that (stupid) syntax error, yet the result is the same as w/o union extras. Any common traps I might have fallen in? I fear this might become yet another logic error holding me back for hours.

My InnoDB ibdata file has 1GB despite fact i removed all databases. How can i "reset" it?

try running the two queries separately to make sure they return what you want, then you can work on combinging then if you really need to

It's odd though - I can log in from the command line as debian-sys-maint with the password I've set.

'incident' (errno: 2)
but I can see the file in "show tables"
mysql has the right privs. any ideas?

!perror 2

No such file or directory

Hm. The log is thoroughly unhelpful.
Any suggestions?

but its there, I can see it... Maybe I must just drop it and recreate it...

is it possible to get only specific table imported from dump ?

CrummyGummy, have you copied .frm file

nope, but the server is a slave so I can do a dump from the master.

MySQL Administrator has an option for that, I think. Or you could write a script to extract just that part. Or you could create a user that has permissions to only that table, and run with the --force option.

snoyes thanks

Hey. I am having trouble with my PHP/MySQL IIS Server... more the trouble is setting it up. I hoenstly dont know the name of my MySQL DB and I cant get the localhost/index.php to show up. Says page cant be displayed. This the right channel?

not for IIS problems

Could someone help me send commands to my MySQL DB? I guess I am just not sure how to create a DB/Table.

hey guys
question for you; how do you know if you're io bound?

What client are you using to access the db?

hi guys, whats the command in mysql for exists like WHERE something EXISTS(list)

by looking at iostat? is there any other way?

When the little light in front of the computer blinks a lot. But generally iostat.

WHERE field IN (1, 2, 3)

thanks

or, WHERE EXISTS(SELECT someSubquery)

im not sure what you mean. Client? I am using it with PHP.

would you look mostly at the time_wait?

Ok, then you use something like mysql_query("query goes here") or die(mysql_error());
tutorial

http://www.mysql.com/doc/en/Tutorial.html SQL Tutorial: http://www.sqlcourse.com and tut.php-q.net/mysql.html

Those will start you on the basic commands for creating dbs and tables and such.

alright. let me give that a shot

I don't remember exactly. Been a while since I had to use it. I have a tutorial on it somewhere, but it's an internal doc (and for a different usage)

cool )

hey can i repeat the constraint name in different tables without having problems later?
how can i have a complete description of a table. with constraints , etc

SHOW FULL COLUMN FROM table_name;
or DESC table_name;

Hmm Im not sure MySQl is even installed.... phpinfo() shows nothing about mysql lol

it doesnt work

COLUMNS*
not COLUMN :P
and DESC table_name; should work

but it wont show the constraints in the table

SHOW CREATE TABLE tblname;

Are you able to access MySQL via the command line?

thanks a lot
could anyone check what is wrong with this please? http://pastebin.ca/568515
it gives me error in servicio

not sure... how do i do that?

go to the command line, type 'mysql'

hey guys constraints are not working. i have a constraint name foreign key (column) references table(column) but it allows me to enter any value i want even though it is not already in the other table

pastebin the SHOW CREATE TABLE for both tables.

okey

http://www.reuters.com/article/technologyNews/idUSL0131903220070601

I am looking for a good software for data modeling for linux any suggestion?

http://pastebin.ca/568528

MyISAM doesn't enforce foreign key constraints yet. Switch your tables to use the InnoDB engine.

okey thanks, how should the alter table be?

ALTER TABLE tableName ENGINE=InnoDB;
Then you'll have to recreate the foreign keys.

can a datetime field e set to null?

sure, if defined to allow such.

snoyes it says mysql is an unknown batch command
or whatever it says
unknown command or batch file

here is the thing, i set the datetime field to default to null yet it's not doing it

Then it's likely that MySQL isn't installed.

Or not in path?

http://www.apachefriends.org/en/xampp.html is an easy way to get everything installed and configured automatically.

hi folks. does anyone know why moving part of a SQL query into a stored function would significantly slow down the query?

oh i have to manually set the datetime field to null, if i don't insert a value for it..it defaults to 0000-00-00 00:00:00

Nothing wrong with 0000

is there any way to get the code i used to create the tables into a file or something, because i will have to rewrite the foreign keys for about 20 tablesç

You can get the code to rebuild the tables as they are, but not the code as you typed it in.

Hello are DB design question appropriate here?

sure

heh i NEED to use IIS
it isnt my choice otherwise i would be on linux

noboby needs IIS

okey how can i do it

mysqldump

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

what about the procedure

You can pipe the output to a file, and then edit the file and replace MyISM with InnoDB, but you'll still have to go recreate the foreign keys manually.
the syntax requires () - WHERE cod_servicio IN (servicio). And I don't think that will do what you want anyway.
I think you'd need to put the first query in a cursor.
Or combine both queries using a join.

Fairly new to SQL and have a table design question. So pardon if this is easy and obvious (it's not yet to me)
I'm trying to model an automobile make/model/year. Need to decide best approach for representing an automobiles production year.
Make = tableodel = table
What do I do about the year?
table Car = Id, Make.Id, Model.Id, year

SELECT desc_servicios, COUNT(*) AS theCount FROM servicios JOIN factura_servicio USING (cod_servicio) GROUP BY cod_servicio ORDER BY theCount DESC LIMIT 3;

nice thanks a lot

Just stick it in a YEAR type field, unless you have cars made pre-1901, in which case use an unsigned smallint.

well i g2g see you guys later
thanks snoyes a lot

You're welcome.
If you wanted to be really truly normalized, you've have to create a Year table, and assign each year car you have an id, and then store a YearId in the cars table. But with numeric data, that's usually kinda silly.

I was just ready to ask that question
A sep. table was what I was struggling w/ it seems like overkill.

unitil the year of manufacture is circa early 1930's

What about for a given model, two columns indicating model year range? then in the car table a specific year?

So you want that the Ford Prefect was manufactured 1956 - 1974, and that carId 42 happens to be a '63 Prefect?

yes, i think so. carId 42 = '63 Ford Prefect

which prefect thers about 3 or 4
good one to pick snoyes

Well, Model would most likely be a trim Jetta, Jetta GL, Jetta GLX

I collect car pics among others so see some of the oddities

How do you handle skipped years? Where Ford made a Mustang from 63-72, and then again from 85 - 2007?

Just thought of that myself. Don't know - that's why I'm here. This seems harder than it should be.

Prefect was around like that as well theres a 30's one as well

Ok, what kind of questions are you going to ask the db about that data? Will you need to say, "What models were manufactured in 1975", or just output the string "63-72; 85-2007" soemwhere?

and really Anlgia/Prefect is just a trim change for some years
Anglia

I want someone to enter the make/model/year of a car they own and then validate that the year is correct for that make/model. If I don't validate then it seems that
car = MakeId, ModelId, Year seems good enough.

how far back in time

For validation purposes, you could create a table with (modelId, startYear, endYear) and have one row per range of manufacture

Hi, I am wondering how I can restrict the maximum length of a "insert" query done by a mysqldump. Myphpadmin is doing it and I can't find how it is done

Then SELECT COUTN(*) FROM rangeTable WHERE modelId = 42 AND enteredYear BETWEEN startYear AND endYear;

watch for old stock sold in later years

I using it to track cars I (or someone) might own, so I guess it could go back pretty far.

is there a tool that can just automatically created indexes, based on a list of slow sql queries ? it seems a bit like dumb work to me, doing them myself :d

--net-buffer-length=whatever

Im researching some old car makers at the moment and data can be sketchy

great thanks! will try that out

So Year field could just be of type YEAR in my Car table (MakeId,ModelId,Year) and then I can just take the Year input from the user and validate it against that validation table?

rhalff, thats a human job

It's like mysql saying, I know what is the fastest, but now you find it out and tell me.

yep.

That seems like a workable approach.

It's more complicated than that. Should the index be single column or multi-column? Should it be unique or not?

rhalff, often the query is so badly written that an index cannot be used

archivist, it are logical decisions so that's a computer's task
hmz ok..

If it's a character type, how long should the index be?
Computers are terrible at making design decisions.

archivist, I use a pager that uses IN with like 20 values, that's unoptimizable right ?

its in byte?

at least I remember ever reading something like that

depends try explain ing it

I think so.

archivist, just a bunch of primary ID's in that IN clause.

thanks, will try

!man explain

see http://dev.mysql.com/doc/refman/5.0/en/explain.html

but explain suggest the explainer knows what is the problem..
ok I'll go out of rant mode now, it's irritating..

methinks you should learn how indexes are used ans selected and in what situation they cannot be used in

archivist, it's just that there are so many optimalizations to be made, and I want to code, but it's getting slow to develop :d

thats why humans have to do it
eg in my self join sql its hundreds of times slower if I let the optimiser work out the query order
for large joins

Sorry, got pulled away, thanks for your help

With the help of xaprb and shabbs (thank you) I have a UNION statement now. But how can I use COUNT(hits) in one of the SELECTs if the other one doesn't have a GROUP By?

you can use whatever you want in either query, its just that both have to have the same number (and type) of fields they return

so you could have a count(whatever) in one and possibly just a constant like 0 in the other

is there a "mysql.conf" somewhere ? I can't find it... I'm running on freebsd and I can't connect to mysql

my.cnf
/etc or /etc/mysql generally

I haven't it :/

do a find

that's what I'm doing...

find / -name my.cnf

yep

did you install the server? :P

doesn't return anything...
shabbs, lol yes

you might have to copy and edit one of the sample ones

/usr/local/etc/rc.d/mysql-server/
/usr/local/etc/rc.d/mysql-server
I have this
but doesn't return something when I pass him the "start"
I think it's okay...
I'm found some .cnf

shabbs, This all seems so obvious after having the answer. O-o Thank you very much.

I found samples with a find on *cnf*
thanks shabbs

How can I check a database's encoding from the shell?

show variables like 'char%'

can you slave off of a slave?

yes

hmm thanks, i have been grabbing a innobackup snap off of the slave to setup a new box off of that, set pos,file,servid , it connects but just sits there

how do i setup mysql to access it remotely with third party mangement apps?

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

You have to enable binary logging on your slave-master, and tell it to log the values it receives from its master.

can anyone explain me what's the difference between utf_bin and other utf's? and what's the best to use for portuguese?

thanks snoyes i'll poke at that

hey guys, what's "Disk Scratching" ? is it the same thing as a seek? like seek vs. scan

TaSo, where did you get that term, they may have invented it and explained what they meant /me thought it was a DJ term

Disk scratching is what you do when you have an STD.

hehe

hrm

hello
is it possible to set permissions at the table level?

yes

ah.. how does one do that?

check the grant syntax

how do i grant permissions to user@host?

!man grant syntax

see http://dev.mysql.com/doc/refman/5.0/en/grant.html

i am confused on how to do this

hello does someone knows a search or query script who will be able to bring files out of a backup file, a .bkf

how do you check to see if a query is doing a scan or a seek? I don't see that attribute/value shown in EXPLAIN.

TaSo, see if an index is used

is there a possibility to change the encoding of a table. I mean completely, not just with set names 'utf8' , but completely rewriting it

archivist, that doesn't mean much to be honest

explain

live by explain, die by explain

!man explain

see http://dev.mysql.com/doc/refman/5.0/en/explain.html

Do you use the tracker on sourceforge, or do you want reports mailed directly to you?

tracker is good. I can't keep track of mail

can someone help me?

smartyt3k, you didnt answer my question in ##c

try using MySQL Query Profiler from http://sourceforge.net/projects/mysqltoolkit and see if it seeks or scans. You can do this manually by examining SHOW STATUS, but it's really tedious.

how can I link clients.id to orders.clients_id ? (i did that with phpMyAdmin but I tottally forgot how to do that)

yeah already answered, anything who will help

yea ;0
what variables am I looking for in show status?

smartyt3k, I think you should google as I have just done to find out what .bkf means

can u help me

I forget, actually. But you can't just look at them. You have to look at them, run your query, look at them again, subtract to take the difference, -- but even that's not enough. You have to also subtract the "cost of observation" caused by running SHOW STATUS. As I said, it's tedious.

it's a backup type file

yea
I'm pretty sure you can check it with Innotop
to see if it's doing a scan/seek

smartyt3k, mysql does NOT use that type

I just didn't know off the top of my head if it said it was doing a scan/seek with a command

I know :-) I wrote both innotop and MySQL Query Profiler :-)

ohhhh
haha

select * from foo, dict where foo.chunk like dict.word+'%' ;

error... for the right syntax to use near '+'%''

yea we went to MySQL camp
- Taso

what's wrong with that?

CarlFK, what did the server say

what name did I know you by at the camp? I don't recognize TaSo, sorry

yes but the issues is that Im working on a program who take users info from the db and querry their back up on the hd

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+'%'' at line 1

Taso Du Val is my name
I went with Farhan Mashraqi

Ah! I remember now!

yes but the issues is that Im working on a program who take users info from the db and querry their back up on the hd

CONCAT(dict.word, '%');

CarlFK, where did you guess that syntax from

Context is everything

select 'a'+'%'; - works

heheh, that it is!

thanks

how's everything going with you?

good, but busy. About to go on holiday, though. How about you?

PDO::ERRMODE_WARNING causes E_WARNING. Is it possible to cause E_ERROR after errors?

how can I link clients.id to orders.clients_id ? (i did that with phpMyAdmin but I tottally forgot how to do that)

are DECLARE statements only used in stored procedures? i'm trying to de-store a stored proc.
maybe a simpler question is whether i can take the code for a stored procedure and just "mysql foo.sql" it.
removing the CREATE PROCEDURE business, natch.

DECLARE can also appear in the other stored routine types (functions and triggers)
But you can't just put DECLARE on the command line and have it work.

can someone help me?

very good going to be moving to the Bay Area soon

new job?

okay, but if i want to convert an SP to straight SQL, I can't just take the stuff between BEGIN and END and be good? maybe some of the SP-specific stuff is what would be code logic in whatever is submitting the SQL?

quick poll... how many mysql servers do the professional DBAs admin here?

Right; the stored procedure could contain all kinds of stuff that only works inside stored routines (cursors, handlers, local variables, flow control constructs)

that is, separate out the SQL stuff from the SP stuff and put the SP stuff into whatever will wind up calling the SQL?
procedural code, i suppose you'd call it.
at least i would.

just one...

I'
I
Grr!

come on, who else!

YOU FAIL POLL

I'm so pro, I only need one.

haha

yeap; I've been working on a startup for quiet some time now, and also on my DBA skills, so I will either be doing the startup or being a DBA or a mix of both
I'm dying to leave NYC though, it's just not the same as over there

seekwill has three different keyboards connected to that one, though, and only a few keys on each work.

it's an APL keyboard

are you on LinkedIn?

well, unless someone else pipes in... I think I have you beat seekwill

yea, I haven't updated it in a while but I should be ...

A, G, M, R, and Z can be typed on the black one. B, C, D, F and Q on the beige one, and the rest he has to select with the mouse from the on-screen virtual keyboard.

footmouse
and one of those forehead-pencil things

I just finished an audit for the company's mysql clients.... and being the only MySQL DBA I have 62 servers. 62 servers to admin.
cricky!

you forgot to mention that 58 of them have just one table.

no, I think he mostly just hunt and pecks

10 GB of data... then there are about 10 that have 25GB and a couple that have close to 100GB

Reason for that many servers?

hey, my home db would be 100G if i could just get around to putting all of my mp3s into it.

did you get my notice?

there are about 24 separate clients, most of which have either active/passive masters and a slave, , and one customer that has db 16 servers

maybe a better question would be "how many transactions per second do you admin?"
bmaybe a better question would be "how many transactions per second do you admin?" /b
ubmaybe a better question would be "how many transactions per second do you admin?" /b/u

which notice? I just send you a LinkedIn invite, did you send me one?

queries per second on the big guys is about 4000/sec per server
one of clusters writes out 60GB of binary logs per day

99% of them are SELECTs

selects don't write to binlog

point being, "number of servers" is not a great metric

hrmm

it's a good metric for how many servers I have to log into, monitor, backup, and keep secure
and do upgrades on
etc etc

securecrt has tabs

securecrt is windows, which is junk - don't get me started

screen has tabs

true, but ssh-keys are better

hrmm

Windows has its uses

gaming, yes
everything else can be done on osx/linux/solaris

oh god, not this again

seriously, let's not have this conversation
moving on....

I think we should.

you started it!

haah

hi all

ok let's go, I can rant on windows for a long time
what can windows do that another os cannot?

today I restored a db from backup, i just restored the db directory in /var/lib/mysql
utoday I restored a db from backup, i just restored the db directory in /var/lib/mysql/u

Web services

however when i tries to ..
select * from mls_area;
Table 'mlsclickit.mls_area' doesn't exist

let me rephrase... what can windows do better
web services are you kidding me? apache rules the web

show tables; do show all tables but can't let me do anything with these tables

perhaps you are on the wrong databae?

why does everything have to be "better?" i could give crap #1 for what desktop OS i use.

Lumberg, nope i do 'use' the correct db

better = more efficient = less work
ok, just checking

90% of my work is in putty

and mls_area shows up in `show tables`

today in #mysql... chocolate or vanilla... the battle rages on!

Yes

hrmm, I can't find you

why you gotta marginalize strawberry?

mine is all in terminal too ... but I like being able to script things for linux on a linux workstation locally and not get microsoft messager popups all the time

heh!

Lumberg, some tables are ok but few are giving problem

ah, lunch time... we can pick the windows debate up later if you want.

Table 'mlsclickit.admin' doesn't exist

i haven't gotten a msmessenger popup in at least 5 years.

I'm listed as Baron Schwartz.

check the file system permissions for the table files

in Virginia?

Lumberg, all set to mysql:mysql

I have a hard drive that I can't boot into. It has some mysql data on it. Is there a way to copy it onto a new hard drive without a mysql dump?

excellent, all set

Are there files where the mysql data is kept that can just be copied to another computer?

"80-60","60-80","150+","125+","100+" (the datatype of the column is varchar).. when I do order by columnname asc I get the order of how I typed the records firstly instead of : 60-80,80-100,100+,125+,150+.. How can I achieve this?

The MBR is destroyed on the other hard drive so can't boot into it to do a mysqldump

yes

store the ranges in two fields, startRange and endRange (which can be null for purposes of +), and then ORDER BY startRange, endRange

rb007__ rebuild the mbr

and make those fields numeric types.

The data is stored in the data dir. Where that is depends on your installation.

it's a long process to learn how to do that, then reboot several times, switch drives, etc. I would like to just copy the data off the drive and deal with it later. I have a meeting to go to soon. Is there a way to do it?

hard to do?

I am wondering why the backup not working, giving me ....
Table 'mlsclickit.documents' doesn't exist

dude live CD + u = fixed MBR

live CD + u?

I have a table with two columns, a DATE and an int. I wish to AVG the int column grouping by first year and secondly month range (which I would like to define myself) is this possible using GROUP BY

The data is commonly in /var/lib/mysql,

go get a Live Linux CD and recover the MBR
it is not hard

I have to leave in a couple of minutes though... juggling 3 hard drives... would like to just copy the mysql data and not have to reboot several times... very strange configuration on my system
involves switching drives

make sure the new server your'e using is the same version as the old then copy your datadir completely

Sure. GROUP BY YEAR(date), INTERVAL(MONTH(date), 2, 6, 9, 13)

thanks
where is the datadir?

shabbs that works but if all his problem is a bad MBR then it is real easy to fix

depends on your config, generally /var/lib/mysql

I want to 'merge' lets say months 1,2,3,4 and 5,6,7,8 how would I do that
or is that what INTERVAL does

yep

(didn't see that bit, stupidly read it as INTVAL)

sure, *if* he has a livecd available and *if* he knows how to use the livecd to do that
if he doesn't have the first, he has to d/l which takes time, if not the 2nd, he has to learn or be walked through

snoyes ok thanks will try that

yes but if he is a admin he should know both.
and be able to do both

you assume too much
and he could just be a db admin, doesn't mean he knows how to do all the sys admin stuff

to assume only makes an ass of u and me

thanks -- I have live CDs, but no time, and the system is not standard. Would involve swapping drives, etc. Have to run in a min
thanks

i do not assume i prefer that they attempt to learn it not just showup in a irc channel and ask 5 mins b4 you have to leave

obviously you've never crashed a live system and didn't know how to fix it :P

Thanks! That is a very smart way of doing it

yes i have
i have done worse

so in that case would you rather sit and learn or get it fixed and then go back and learn what you did?

i dont ask others to give me the easy way out not to mention i have backups of things like db's

we don't know his situation, so again we'd just be making assumptions

found an other solution, used order by columname + 0 asc now and it works

that is not an assumption, that is a requirement for doing business today

http://blog.feedmarker.com/2006/02/01/how-to-do-natural-alpha-numeric-sort-in-mysql/

Are the ranges exclusive? Could you have, say, 60-80 and 60-90?

could be

Using the +0 will not define how those two sort relative to each other.

If I am interested in all of the dates which fall in 'this week' or 'last week' is my best bet to work out when this week started and finished and put that in as my range

Or usethe WEEK() function
Your solution is best if the table is of any size and you have an index on the date field. Otherwise, WHERE WEEK(date) = WEEK(NOW()) - 1 would give you last week.

Hmm... "SELECT Name FROM sim UNION DISTINCT SELECT Name FROM access;" *should* select only Names that are in one or the other, but not both, correct?

no, that's not what union distinct does.

snoyes, welcome back

You're looking for a full outer join, which can be dones by unioning (A LEFT JOIN B ON b IS NULL) UNION (A RIGHT JOIN B ON a IS NULL)
thanks.

Oh crap, wow... that's a bad brain fart.
I just re-read the doc on union distinct, feel like a moron. I need sleep
snoyes, Thanks, I'll give that a try.

hi all

did you try checking up on the IP?

anyone can give me a advice? http://rafb.net/p/cUF9fH33.html i use centos

Yup, that IP is local on the LAN and accesses the database normally. Hm

could it be a bad password?

today I restored a db from backup, i just restored the db directory in /var/lib/mysql

you said that already.

lol okay
but still i can't able to read tables;
reparing fails too
mlsclickit.admin | repair | error | Table 'mlsclickit.admin' doesn't exist
what could be the reason ?

hmmm, I see. I doubt it, really. hmm

your backup didn't include that table. that could be one reason. could be.

duno but show tables showing it

does SHOW TABLES read those tables?

its not just one tables i thinks giving problem with 7-9 tables;
by read you mean displying there names then , Yes

is showing the name of a table the same as reading it?

is there a way I can insert multiple rows using INSERT, and at the same time, for each row, ON DUPLICATE KEY UPDATE specifying an arbitrary value for each row?

reading == showing?
sorry i am not getting it

that's what i'm asking

http://rafb.net/p/PE4BbX96.nln.html

INSERT INTO t (a,b,c) VALUES (('a','b','c') ON DUPLICATE KEY UPDATE a='a', ('d','e','f') ON DUPLICATE KYE UDPATE a='d');

rhizmoe, i can't read some of them.

but some are okay?

yes

ON DUPLICATE KEY UPDATE a = VALUES(a);

hmm.

I have just done EXPLAIN and under extra got using temporary, using filesort - I presume this is not ideal?

Then it will take the value that would have been inserted, and update it to that instead.
so, INSERT INTO t (fieldA, fieldB, fieldC) VALUES (a, b, c), (d, e, f) ON DUPLICATE KEY UPDATE fieldA = VALUES(fieldA);

snoyes, so you can only have one ON DUPLICATE KEY UPDATE, and VALUES() should contain all of them?

add me on linkedin

ohw ait, I see, so fieldA it automatically determines what would be there in that row

right, 'a' in the first case, 'd' in the second.

excellent. How come it always says that it affected twice the amount of rows it actually did?
I mean there are two rows in the table, and it says it affected 4 rows
just wondering if I'm doing something wrong

rhizmoe, i am wondering anything can be done to get back these days, i have no other backup and the stupid programmer delete the db from master

because the documentation says so.

s/days/tables

"The rows-affected value is 1 if the row is inserted as a new record and 2 if an existing record is updated."

ah, ok, makes sense then
I mean that doesn't make much sense at all, but if that's the way it is, then that's the way it is.

I don't know that it makes sense, but at least it's consistent with the docs.

rhizmoe, we also have replication slave but as you know its useless in this case :-S

yeah
I mean, by that number, I can't think of any way to programatically figure out how many rows were updated and how many were inserted
basically makes the number useless

yep
Well, maybe you could.
If you sent 3 rows, and the value comes back 3, they were all inserted. 4, two inserted and one update. 5, one insert and two updates. 6, all updates.

maybe...

So, affected rows - rows you sent = rows that were updated.

Hm, anyway I can turn on more strict logging for mysql or something? Something that would show if it's a bad password

How can I name columns in a SELECT from a predefined list? (Not sure if that made a lot of sense)
Well more the column values

select foo as bar from table

For the first row I want 'Row A' the second row I want 'Row B' for the column colname

select foo as 'Row A', bar as 'Row B' from table

I mean for the same column

EvilGuru, why do you need it?

I am currently grouping by months (in 4's) and want to have it so the first row is "First quarter" second row is "Second quarter"

have you checked permissions?

rhizmoe, yep its set to mysq:mysql

well, i think you've reached the limit of my expertise

EvilGuru, it's not possible to name a column differently across rows. it should be doing it in the application side

I really did phrase it wrong, rather than rename the column I just want to change the value

SELECT QUARTER(date) FROM table...

these MySQL devs think of everything
I really should start expecting it to be implemented

you can use CASE to give it a value

you can use ELT to give it a value better.
e.g., SELECT ELT(QUARTER(date), 'First', 'Second', 'Third', 'Fourth')

indeed. I had never heard of it before. handy.

My biggest question there being where the function name 'ELT' came from.

Yes

I think ELT means Equal or Less Than

I think you just made that up.

rhizmoe, sorted out

especially since the function doesn't make any use of Less Than.

it must be embarrassing if he doens't tell us what the fix was.

i was restoring the db direcotry but not the ibdata1 adn ib_logfile.* log files

gotcha

Now i have restored the logs and ibdata1 from backup and its working
Thanks for the help dude

Don't suppose mysql can tell me when Easter falls

Wikipedia can!

And Wikipedia runs on MySQL!

you could write a storage engine to use the wikipedia filesystem and tell you when easter is

I remembered implementing a formula to work it out in JS a few years back, was very nasty and uses a loads of tables
*remember

I'm planning on storing several short stories in my database. Which data type would be most appropriate?

If I group by the month a set of dates are in and SELECT date as well are there any guarantees about what date will be (first matching value that fits the criteria for a group)

What options are available for you?
!m mike_ data type

mike_ see http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

Anything really

No, not anything
!m mike_ data type overview

mike_ see http://dev.mysql.com/doc/refman/5.0/en/data-type-overview.html

Oh, I thought you meant alternatives
I'll take a look at that, thanks

There's not many choices you can pick from, really. Each data type have a specific purpose/limitation, depending on your needs.

Or maybe storing the stories in a file would be better?

If I create a table that already exists will it be deleted/

CREATE will fail

okay thanks

Depends on your needs. If you never need to search in the stories, storing them as a file might be better.
But it's normally a good idea to be able to search in the stories

I'll only be storing 15 stories max

That's what you say now...
It's not really the number of stories. It is what you want to do with them

You're right
lol

I know...

Let's review that page.

I used TEXT for storing large blocks of stories after compressing and base64ing them.

whats the best data type to store IP addresses?

http://forge.mysql.com/snippets/view.php?id=74

Seems interesting, thanks!

Hello! I can install a mysql server free in my company to run with a php ?

yes, so long as you don't distribute MySQL or code that requires MySQL.

yes, if you are not stupiud

snoyes: i'll just run a ERP for my company.

That's fine, MySQL is free to use even for companies.

thank you

Yay! I have got my query working to work out the average attendance for each term given the daily attendance

snoyes: but if i'll distribute a program with mysql then i'll need pay, right?

http://rafb.net/p/PQPhJP32.html

right.
There's no Autumn?

INSERT into key_1, key_2 VALUES (1,2),(2,2); Is there an easier way to do this if key_2 always is 2 ?

School terms are 3 to a year in this country, not sure why there is no autum
or why they are named as they are

snoyes: and a program how require mysql? i can sell that?

alter table so key_2 defaults to 2, and then only insert key_1.

hm that's pretty smart

If your program requires an installation of mysql to run, or includes the mysql connectors, or includes an embedded version of mysql, I think you need to pay for a commerical license. You'd have to contact MySQL's sales/legal department to be certain.

thank you

with always I mean I have like 10.000 with the same key_2 and then the same amount with a different value for key2, with like 10 different values in total for key2

In that case, surely you have something generating those values for you. Unles you're going to type in 20000 rows manually.

yeah I have, thought there was a faster option maybe.

If that's the case, why does your script care if it's generating (1, 2), (2, 2) vs. (1), (2) ?
Where does key_1 come from?

select id from table..

ah, in that case, INSERT INTO table (field1, field2) SELECT id, 2 FROM table;

ah great
hm don't need to program at all then it will become INSERT INTO categorizations (content_id, topic_id) SELECT id, topic_id FROM contents; should think in sql first before I start to script

I'm trying to write a function here, and in it I have SELECT `a` INTO @something FROM ..., but in some cases the select returns no rows and I get a mysql error. How can I make @something be NULL if no rows were selected, instead of triggering an error?

Write a continue handler to catch that case.
e.g., DECLARE CONTINUE HANDLE FRO NOT FOUND SET @something := NULL;
couple typos there, but you get the idea.

ya I think so
hmm. ok that gets rid of the error, but for some reason I don't think I'm doing this correctly. I have another function I made, and in MySQL query browser it shows the arguments to the function in the schemata pane. But for this one it doesn't, and..
well, it doesn't work the way I want it to heh.
The SELECT statement should return a row in this case, and it's still not.
http://rafb.net/p/YLPt9D69.html
does that look correct?
because when called whith arguments where that SELECT statement should return something, it returns nothing

don't use @

yeah I tried both ways
I changed it, only @ I have now is in front of val

There should be any.

shouldn't

when exactly is @ used?

http://rafb.net/p/XS1K0u34.html ?

When you want a user variable (these are local variables)

hmm, ok
thanks

if I use the month() function multiple times in the where portion of my query, will that be a problem. I'm trying to find out if a date falls under quarterly calendar year from a specified date field.
so how can I do this?

how do i search for duplicate records? if two records have the same value for two specific fields...
i have a music db and if the artist and title appear more than once in the db i want to see it

dupes

find them with select count(dupefield) as qty,otherfields from table group by dupefield having qty 1

Not a problem, per se. Can you just use the QUARTER() function?

snoyes, holy shit...i had no idea
haha
wait a minute htat just returns the quarter of the year
i need to find out if it's a multiple of 3 months

um...what?

so lets say if's january, i only want the datefield to return if it's jan, apr, june, sep
ooops
i mean jan, apr, july, oct

that didn't quite work the way i thought
if artist and song shows up more than once in the db i want to show that.

MOD(MONTH(field), 4)
er, make that 4 a 3.

section31, yup
i figured it out
but thanks anyway

does anyone have any idea on table space used by a record in innodb
like the ratio of tablespace size to size of a space delimited flatfile?