wee offline one works today ….

where was the setting in my.cnf to allow connections not only from local hosts ?

what would you use as a datatype in mysql for a column that will only hold calues between -2 and +2 inclusive
INT(2) signed ?

TINYINT

there is none, use TRIGGERs to enforce it

weigon i dont want to worry about enforcement

127

just looking for the most appropriate data type

in that case, follow Procyon`

yeap
ta

!tell xored about external

xored 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

i have installed the mysql hosting odbc driver ( never worked with it before ). No trying to export a table from access directly through a odbc connection. But he asks me to choose a ODBC connection in a directory. What does that mean ?

play with it
and make sure to check the dropdown

your trying to completely move an access database into mysql?
I would suggest, rather that trying to migrate it in some crazy way, building a new mysql database hosting and migrating gently over to it
else its all going to go pete tong, guaranteed. I mean, trying to shove access into MS sql fucks up
its all a crock of shit

bah Shrews http://bugs.mysql.com/bug.php?id=29395 on win2k as well

bummer

silly bug
I want to do a migration job

I'm getting this error when executing a query. I'm MySQL 4.0.38, any hints? ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_4cdd_0.MYI'; try to repair it

so repair it , and check hardware
or is that a full temp error

archivist, I don't have a file /tmp/#sql_4cdd_0.MYI
It's perhaps a temporary table for a join. I'm not really sure.

low on disk space?

it may have failed to create when making a temp

I have 150mb but I will free more and try

yeah
guys i install odbc from MYSQL for windows..but how do i configrue it ?

How to implement datetime type value minus several days?
for example, select * from foo1 where createtime between (NOW(), NOW()-3days)
how to compose sql that can do something like this?

that was it, my bad.. thanks much!

edit my.cnf and restart mysqld

NOW() - INTERVAL 3 DAY

what is that interval function to use?

the mysql server does not run on the windows machine and i have been told, the ODBC driver is client only. So why should i modify my.cnf

!m JBond2 date time func

Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/date time func

!m JBond2 date time

Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/date time

dammit

!man date and time f

see http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

hehe

archivist:

oh I missed the odbc part

see the link from the_wench above

go to the control panel and edit it, I suppose. I don't use that OS much.

Shrews or cheat !man now(

i'm just losing my mind
i blame therion

aw poor thing

You're welcome!

is the mysql odbc driver free for use in a firm ?

hehe

that depends on the license.

"Comply with the GPL"

you were prompted to read the license at install time, were you?

As always for dev reasons, i pressed ok Iam not the one who will use it
iam the one who will administrate it

perhaps you can reinstall it and read it.

GPL
so its free, thank you

there you go.

hi, is there a better way to ORDER BY than "order by abs(X)" where X is the integer representation of a string? (i want to have "host1" before "host10", "host100" and so on)
if not, how do i get the integer representation of a string

you can CAST it to INTEGER and make an alias
then, ORDER BY aliasname

andiolsi, store the number separately

hmm ill try that casting thing, storing the number isnt very practical here

is there a way to know when the corrent week begins and ends ?

andiolsi, that will be slow

if i could use the unix sort program i'd just add "-n"

the idea is to makes sure you can use an index

well i didnt create this database schema, its lacking numeric primary keys and so on, i just want that list to come out sorted correctly. the guy who is supposed to do the programming cant figure out how to do it. and i totally dont care for the performance, if it was for me, i would sort it
in the application.

sounds baaaad, have fun

:/
according to the reference manual i cannot cast just into "INTEGER"

if i have many cfg1 … cfgn how can i make regexp? 'cfg(\d+)' ?
is that correct ?

noob question, and a tad silly one… how do i empty a database without dropping it? ie. since i can't recreate the database just dropping it and reacreating it isn't an option…

speaking for regular expressions, yes if it fails try [0-9] instead of \d and maybe {1,} instead of "+"

thanxs andiolsi
works cav[0-9][0-9][0-9]
with cav(\d+) syntaxs works but not filter..
anyway thanxs a lot

Is there a way to stop a group by using a temporary table and a sort?

like 'cav%' could use an index and be faster

describe SELECT Pb1.id, Pb2.id, CDR_id, CDR.*

FROM CDR
LEFT JOIN (PhonebookEntries as Pb1, PhonebookEntries as Pb2)
ON ((Pb1.phonebookId = '1' AND CDR_terminating LIKE CONCAT(Pb1.number,'%')) AND
(Pb2.phonebookId = '2' AND CDR_terminating LIKE CONCAT(Pb2.number,'%')))
GROUP BY CDR_id

pastebin

LIMIT 0,10;

try http://pastebin.ca or http://pastebin.mysql-es.org

ops yeh, sorry forgot :/

hay

Horses eat hay

hoy

http://pastebin.mysql-es.org/62

i cant figure that cast thing out. i just need a statement to return the integer of a string whithout using variables aliases or such things, are you shure cast can do that?

andiolsi, probably need to cut the string part out
double slowness

i have a list of hosts, and i need to order them by hostname, how do i "cut" a string out, if its needed to do the sorting part?

a string is a string

?

host01

the hosts are named without leading 0's
like host1, host2, host12 …

so accept the crapness

maybe i should just plain ignore you, it will be faster that way

Any suggestions how I can speed-up the following: http://pastebin.mysql-es.org/62

with less like and concat

Hi

Its fine (just) until I do a group by and since i know its a left join and I am grouping on the primary key why should it need to complete the entire query before it does a group? Surely it could just group them together as it goes along?!

repaste with the create for the cdr table (probably needs a compound index) or be done some other way
Voltaire, its not fine without the group by, note the cdr count

Yeh. It is slow, but since the user will be paging through data set it is at least acceptable.

Whenever you have GROUP BY the entire interim result set must be obtained before aggregation

hi

Seems like you are thinking it is possible to get first ten CDR_ids in ascending order and then apply them implicitly in WHERE clause so the aggregation is done on smaller set. Not yet implemented if at all possible

Guess ill have to open up the engine and add an optimisation case

Not the engine, but the Optimizer code

I want to clarify something - with mysql on a linux system is it pretty much standard to set innodb_flush_method = O_DIRECT and take a small penalty for writes?

Meanwhile you can do that optimization manually with temporary table and one more join

Possible i think in some sitauations; this being one of them I think.
how do you mean? Wouldnt that still require the full CDR set to be passed?

"Incorrect key file for table '/var/tmp/#sql_3dc4_1.MYI'; try to repair it" - any ideas?

As long as CDR_id is indexed no

this shouldn't be happening. Please submit a bug at bugs.mysql.com

Nope if CDR_id is indexed. In worst case the entire index will be scanned, but not the entire table as in your query

you might have to walk me through your thinking.

will do… however, I'm not sure if I can create a minimal test-case for it

Voltaire suggessts interesting optimization in case of SELECT .. GROUP BY column_with_high_cardinality LIMIT N;
I wonder if such WL exists

please try to create least possible. If that fails, we prefer to have huge testcase than no tescase… it will be hard to analyze such bug w/o testcase…

karmazilla, out of temp space??

reading…

The optimisation would be in a case of a left join and grouping on a unique index only i think.

LEFT JOIN is another story.

does anyone know why the first query only uses where, but when the where is taken away it then uses filesort? how can i stop it using filesort? http://nopaste.com/p/akzIgOXPF

ain't it… got 7.5 GB worth of available temp space

ok

however, the query has triple nested selects and 23 joins…. explain takes 10 minutes on it

straight_join the optimiser is confused

sounds like a worthy idea

especially with self joins
you have to order the query execution though

the're no self joins… only left joins to other tables and mostly on PKs or natural keys

salle: we have a bug in the works ATM that, perhaps, could help with this… Igor's making cost-based choice between doing ORDER/GROUP BY + LIMIT and full table scan
http://bugs.mysql.com/bug.php?id=28404

So it is already there between the lines

yes, sort of.

Bad news. You can't claim copyright of that idea

Thanks.
Fudge

by nested selects, do you mean FROM-subqueries? If yes, I'd try replacing "FROM (SELECT ….)" with "FROM tmp_table", where tmp_table is manually made table with the select contets

spetrunia is the guru when it comes to teh optimizer not me

hmm nice to know that

where are the MYD and MYI files usually stored?

in the same place as .frm

In the database directory. Don't ask where it is

i think its /var/lib/mysql/useraccount/

You should know.

I forgot

Hello

but dont exist for innodb

Is there away to see the information in the host cache (the one that gets flushed with "flush hosts") ?

anyone know why the 2nd query uses filesort but not the first? http://nopaste.com/p/akzIgOXPF

I'm not use it's possible to break it up in multiple queries… it's a report query for Jasper reports, so I'll have to investigate that

the first query can use a range with your listname key cause your like in the where clause starts with a character and not a %
the second one as to read all table and then sort

ah right
but i do have an index on listname
so why cant it just grab them all in the order of that index
is there a way to stop it using filesort?

http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

ooh complicated lol. i'll have a read through it later. thanks for ur hel
help*

I'm having problems using mysql dump. I use this command: "mysqldump -p –tab=. drupal" and I get this error: mysqldump: Got error: 1: Can't create/write to file '/home/brian/dbbackups/drupal/access.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'

does the table size affect query speed?

JoeyJoeJo, you dont have permission to write the file

yes it does

well, it's weird because I'm running this command as root

mysqld runs as mysql

I just figured out that if I take the –tab subcommand out and just save all the stuff to a txt file, it works
so it's something with that –tab

Hrm, may I ask what exactly is wrong here?
You have an error in your sql hosting syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''novice' ( 'id' INTEGER NOT NULL AUTO_INCREMENT, 'naslovnovice' VARCHAR(100)' at line 1
Where line 1 is
"CREATE TABLE 'novice' (
'id' INTEGER NOT NULL AUTO_INCREMENT,
'naslovnovice' VARCHAR(100) NOT NULL,
I really can't figure out what exactly is wrong

Hi guys, I am tryng to setup dual master replication between two mysql hosting servers
one is 5.0.22 on RHEL5 and the other is 5.0.32 on Debian Etch

Matic`Makovec, are those single quotes? you need to use backticks
Matic`Makovec, or nothing at all

(or sql_mode = ansi_quotes)

from 5.0.32 to .22 it works fine but the other way round i run into a problem where the replication tries to create the database its replicating, but the database hosting already exists

HarrisonF, thanks

is this a bug?

weigon, where or how exactly do you do that?

isn't multi-master the feature which is documented to not work ?
you build a ring, right ?
server-ids are unique and log-slave-updates is set
and you make sure that the two can't write the same data at the same time

there are only two servers with unique ids
log-slave-updates?

imhotepp, the create database would only be replicated if it is in the binary log, did you start replication at the current offset?

and you write into both at the same time or only into one ?

both will be written to

HarrisonF, well I use double quotes before CREATE and single when it comes to name of table

I think that is my problem…
2 mins…

before we go on, you know about UPDATE, INSERT and DELETE anomalia ?

Matic`Makovec, that's nice, you can't use single quotes around identifiers

… or about conflicts and not existing conflict resolution

i am afraid not, any online resource?

disregarding network overhead/delays and considering proper indexes,
does making several small queries (1 per table) has the same performance as executing a big complex one (for JOINS)?

rsd, it depends on the execution path

what do you mean?

rsd, either one could be faster depending on the exact query

hi

rsd, the answer is maybe

for example, supose I have a SELECT for a table and there is a field city which is an external key to a table city
instead of joining it
in the same select
I do an extra SELECT to get the city name

when i do a select in an in-clause of a select query, my mysqld 5.1.11 (win) needs too long (actually not tested how long) to deliver the results.

for each row

srv1 executes UPDATE tbl SET fld = "foo" WHERE id = 1; and srv2 execs UPDATE tbl SET fld = "bar" WHERE id = 1; at the same time

if i paste the results from the inner select into the in-clause, it works within seconds.
any ideas?

what happens ?

rsd, do the join in that case

ok, but would it be a bug performance hit for mysqld?
big* not bug

to do it manually?

yes

the network round trips will kill you to do it manually, there is no point in doing it manually

I am working on a lib for this
and network is not a problem

weigon, they won't be executed at the same time, the table will be locked for each update, but it will end up as either "foo" or "bar"

we have 2 databases which use async-replication. They WILL be executed at the same time

no ideas?

SELECT … WHERE x IN (SELECT … ) ?

yep
and if i paste the result set of the inner select statically into that query, it's workin.

how are dual masters setup then?

they aren't
in mysql-replication you can't have more than one writer for a data-object at the same time
there is no protective locking
you can use MySQL Cluster if you need to
with sync-replication you don't have this problem

the machines are far apart
over the inter-web-net

if you can make sure that the two machines never write the same data at the same time, no problem
another problem are UNIQUE indexes .. let's say on usernames. You always need some kind of conflict resolution.
the easier way of conflict resolution is making sure there are no conflicts

damnit

i think i'll downgrade to 5.0.x and see if the problem persists.

bug submitted: http://bugs.mysql.com/bug.php?id=30180

do you know of a manual page where we discuss a ring setup ?

no idea

SELECT … WHERE fld IN ( SELECT * FROM (SELECT …) AS foo))
it's a hack but should work
it tricks the MySQL server host to read from a temp-table instead of executing the inner SELECT for each round

weigon, i'm sorry, but now i see 3 selects!?

nice, isn't it ?
SELECT … WHERE x IN (SELECT … ) becomes:
SELECT … WHERE x IN (SELECT * FROM (SELECT … ) AS foo)

ah i got it.
crazy shit. thx.

the extra SELECT is bogus in the logic, but tricks MySQL into another way to execute the query

weigon, so there are some design issues in mysqls query handling?
i remember working for several months with a large data warehouse within an oracle instance - no such problems ever seen.

the IN (SELECT …) isn't optimized at all right now
IIRC that will be fixed in 6.0 which will go into alpha soon

ok, so now i downgraded to 5.0.45 for nothing. but at least it's called stable, feels better :]
ah nice it's workin. thx again.

yes one hell of a query. I'm afraid we won't be able to do anything about it until we can repeat this on our side.

hello

Hmm
from (
select * from enduserhistory.sms_new_minigloballog_combined
union all (
This means that it will read through the entire sms_new_minigloballog_combined tables
s/tables/table/

in postgresql there is a mechanism called vacuuming…. is there something equvallent?
….in mysql
?

I wonder if the problem with the query is that the temp table becomes just too big

not really. mysql table types that use mvcc tend to do the cleanup in-line, rather than pushing it to a background process

xzilla, purge is a background thread in InnoDB (for removing old tuples that aren't needed anymore)

akin to autovacuum? i could have sworn all clean up was triggered by actually running events

if i wanna use the rhee3 binarys on my server, what do i need? do i just need the server rpm, or do i need client, shared libs and anyting else?

xzilla, for ROLLBACK itself it is generally done by the thread issuing the command (though it can be in the background as well, for example for crash recovery)

xzilla, I noticed, that some inserts hang indefinately
xzilla, so I thought, this could be due to a "dirty" table, or old indixes

xzilla, the removal of old tuples no longer needed is always done in a background thread

what spawns the background thread?

keex, that is most likely due to log file bottlenecks with disk writes

so I should apply any aplicable where clause to the "select * from enduserhistory.sms_new_minigloballog_combined" query ?

xzilla, it is always there, created when the server starts
xzilla, it checks periodically if it needs to clean up

hmm… so it is like autovacuum

xzilla, kinda, except much cheaper due to the fact we have a list of old tuples and you have to do a full table scan

this doesnt seem to jive with what i read on it, but its been awhile since i had to know the perticulars

yes, if there is any. MySQL atm is unable to push selection conditions down into FROM (….)… you have to do it manually

note though, HarrisonF is an authority on this, and i am not, so take his info over mine

hi all!!

xzilla, it has always been that way

I can do it with the timestamp range check and the sessionId like … part, but the rest of the outer where clause is actually generated at runtime

xzilla, you may be thinking of the normal rollback command which is done in the thread

ok, xzilla thank you kindly

must be

i have some problem, there isn't patch for use comma how decimal separator?

xzilla, pg treats them the same way (old versions and rolled back tuples), whereas InnoDB handles them differently

HarrisonF, how do I find out about these bottle necks?

sorry for my english…i'm italian

is there a way to force cleanup, ala manually running a vacuum command ?

keex, SHOW INNODB STATUS is useful
xzilla, nope

Hi, I have a db of about 150000 records to update over a WAN connection. the update takes 1-2 secs per row. is there any way to make this quicker?

if i wanna use the rhee3 binarys on my server, what do i need? do i just need the server rpm, or do i need client, shared libs and anyting else?

danielgee7, batch the inserts

how do i do that?

I inherited a MySQL database and I notice it has multiple indexes on the same field, with different index names

insert blah values(1,2,3,4),(1,2,3,4),(1,2,3,4),…..

is MySQL smart enough that it won't actually index the same field twice?

the insert is pretty quick, its the update that takes long

add an index to your update matching criteria ?

danielgee7, write it as a select and explain it, check for index use

looks like my answer is no. http://www.mysqlperformanceblog.com/2006/08/17/duplicate-indexes-and-redundant-indexes/

add an index to your update matching criteria ?

danielgee7, write it as a select and explain it, check for index use

oops, sorry, was just copying that
you can do an update through a select? i'm lost

no its a way of checking for existence of an index

HarrisonF, I can imagine you are very busy. would you still have a look at the SHOW INNODB STATUS output?

keex, sure, paste it somewhere
keex,it is taken while the slow inserts are occuring?

does anyone know where mysql logs replication errors
– /var/log/mysql.log is empty

ok, i understand

http://phpfi.com/253772
HarrisonF, the insert simply times out

keex, the system is completely idle, heh
keex, paste it while the insert is hanging

HarrisonF, yes… that happens sporadically mostly in the middle of the night..

hey guys is it possible for a database to have a relationship with another database? for instance, if i have an datababase1.entity.entityId = 1; is there any possible way, database2 will ahve knowledge of that key?

HarrisonF, the dhcp-server that executes the perlscript, that in turn hangs during insert , then crashes..

in an integer column can I store a value like "34.45"
or would I need to specify it as decimal?

does that look like an integer

no

if money then decimal

is there a decimal data type for mysql?

keex, well getting an SHOW INNODB STATUS when it is hanging would be very useful

(which is what i was originally asking)

yes

ok thanks

money decimal (4,2) ?

Yes, on the same server. For accessing remote databases, see the federated storage engine.

problem still exists. I have data and when i load it, it cuts off the first char of the first column. how do i correct this? if I put space(s) in the column it picks up the chars but I don't want to do that

BlkPoohba, wont get rich on (4,2)

i agree but doesn't that cover 34.45?
4 digits 2 decimal. isn't that what that means?

we dont know his real range

money decimal(8,2) should cover the average joe. super joe may need money decimal(10,2) avg Gates would use something like money decimal(15,2) i wish i was a Gates.

what about a Gates in Zimbabian $
hehe

anyway… back to my issues. what would cut the first char off the load data

bad line end

'\n' i assume, its a flat file
but it does it for the first line also

I have a feeling that the importer is fussy about them I had a problem last week

how did u resolve it? i tried copying to a new file, saving in different formats, csv, tsv using notepad, gvim, nothing seems to fix it

I cured my problems with a php csv import

i was thinking about writing a perl script to do it but damn… i shouldn't have to

I know

dudes, i want a field in a table wich can be text, or a number or an IP, how can i define it?

I was getting data correctly for x lines then after that every sedond line was in the last field

something like "(…,$FILEDNAME,….)"

StereoSkit, think about how you will search it

i dont get it

hello people
what the best maxine for run mysql in big supermarket?

varchar?

hi all

ill just say, "SELECT $FIELD FORM …"
form
from
merde

been server!!

varchar?

i am quite new to mysql, and have an general question about schemes.
when building (example) an inventory list, how to make the "tree-like" structure?
categories have subcategories etc.

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

ok

can mysql run things from the command line?
i know im crazy for askin

so, most webshops, build their "catalogue" with a tree?

some

ok, so a nested set should be good, for building a inventory-db, right?

hi, how can you change the engine for a temporary table?

danielgee7, why?

created a temp table to do that update I asked you about. I think I set it to memory and must have run out of memory space.
btw, the update runs within 3 secs for 156000 records by doing it that way.

I think there is a disk option iirc

thanks! busy reading up on it now. thanks for your help.

i have some problem, there isn't patch for use comma how decimal separator?

cristianmm, you could use replace() to change the . to ,

Anyone have any good suggestiosn for an alternative to 'SELECT DISTINCT' ?

!man replace(

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

group by ?

doesn't that still use a temporary table?
Trying to speed up a query..

that wasn't the question

lol
details details..

^__^

Any other ides?

group by

perhaps you could show us the query and the explain for that query
"Anyone have any good suggestiosn for an alternative to 'SELECT DISTINCT' ?"
for example, does the distinct operate on a key column?

hello folks

Dont forget the bots!

hello bots

i have a column named last_login which lists date/time - is it possible to count the number of records matching the current date?
other words i would like to know how many accounts were active today
not sure how to make such date comparison

select sum from table where datecol = now()

damn.. this easy?

sum(*), even
yes

thank you so much - let me test now

you have to convert now() to the whole day too, probably

but does it matter that same column also includes time?

not if you select on the whole day

hm

0 and

when i importo a utf-8 file in a utf-8 database, wich copatibility mode should i choose ?

i had no idea how flexible mysql was…
many thanks adaptr - testing now

kaydara compatibility mode ? ITYM collation

itym ?
adaptr, itym stands for ?

0 and
any sense in that?

well, no

select id from db1.account where last_login = now();
but it's better - just returns empty set

0' AND '2007-08-02
!man sum

see http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

!man between

see http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html

oh damn this is really kind of you as i was about to dig into docs
!man now

see http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

you are going to dive into the documentation

he he
about to test your line plus i will do some heavy reading…
thank you again
err it doesn't like the sum(*) thing
!man sum

see http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

9 [ERROR] Slave: Error 'Duplicate entry '660551' for key 1' on query. Default database: 'drupal52'. Query: 'INSERT INTO accesslog (title, path, url, hostname, uid, sid, timer, timestamp) values('', 'rss.xml', '', '209.85.238.5', 0, '0434af6d7703776f55a7032afdc7f636', 0, 1185838032)',
Error_code:
I keep getting that error (although the replication still seems to be working).. is there an easy way I can delete that entry on the slave?

adaptr just found out that COUNT(*) is better than SUM(*)

how would I set up a foreign key to keep anything from being deleted in the referenced table if there was still an instance in the child table? RESTRICT?

CONSTRAINT ?

a foreign key by definition is a constraint.

then you cannot delete from the referenced table if there is a NOT NULL on the column, obviously

please, what command do I use to change variables like collation_connection, collation_database, etc?

how can i get this to go in alphabetical order?
"SELECT actor FROM all_actors WHERE actor LIKE '" . $letter . "%'"

obviously you're not familiar with the different options regarding the actions possible

LoRez obviously, yes - so why are you asking ?
Chatmasta add ORDER BY actor at the end

I would like to change these from latin1_swedish to latin1_general_ci

really new to mysql here, but how does the database privileges work? is it possible to allow a user to create/manage his own databases without giving him access to all other databases on the server?

adaptr, i did that, but it just returns blank.

Chatmasta then the query returns blank without it as well

it doesnt though
im not getting any PHP error, either, even with error reporting on E_ALL

then you're not doing exactly what you're showing
ieugh
also, you're comparing to a string, use LIKE $letter.'%' instead - you probably didn't close your quotes properly or something

nvm, i got it

never mind ? I don'
t think so.. show what you screwed up!
how will anybody ever learn unless you tell us what it was ?

it wasnt a eok that didnt work like i thought it would
adaptr, what i thought was the problem had absolutely nothing to do with mysql

that made zero sense

i just was uploading the file

ah, okay

er
uploading the wrong file*

what can typically cause a table to be "crashed"? Is it only if the server is 'mistreated'? (e.g. by shutting it down abruptly) .. or can it happen through normal usage of the database?

will an alter table to add a foreign key take a long time on a large table?

i want to find all records where col3 character length is less than 5

Can I make a table that uses _two_ columns as the primary key?

yes

depends, but the time is proportional to the size of the table. Also, the table is read (and sometimes write) locked during the update

aliver sure

What do I google on to see an example of how to do that?

search the mysql docs for "PRIMARY KEY"

Oh, that's all? Okay.

yes
standard feature

primary(field1,field2)

it has 10.3M rows in it.

then it'll take 10.3M times X, where X is an unknown quantity that is much smaller than a second
(I created a FK today on a table with 10k rows in it and that was almost instantaneous)

so it's never instantaneous, it rebuilds the table…

can someone please help me understand this code? it doesn't make sense to me because it seems like its saying if you haven't connected to the db - http://pastebin.ca/641947

i said almost

Hmm, nothing about how to use two columns as a primary key is in the section that talks about primary keys.

!man primary

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

PRIMARY KEY [index_type] (index_col_name,…)
the ,… means "add whatever needed"
so yes, it's very much there

Ah, okay, my bad.

if you really enjoy the experience you can add up to 254 columns to a compound primary

Uhh, that doesn't sound so good.

but one might question the need for a database in that case

so I'm not sure where that code says to connect to the DB in the first place

would someone be able to help me create a wordpress plugin?

isn't that just *wrong* in some strange geeky way?
(254 columns)
like, you must be insulting some MySQL deity in doing that

not really, if you choose the order right you end up with a clustered index
it's just not particularly *useful* for any kind of normalized table

hrm

(it'll be a clusetred index on the entire row)

Hi, i'm getting an error with this statement. please help - LOAD DATA LOCAL INFILE 'c:/tempstk.txt' INTO TABLE tmpdb FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (gcode,SC2)

how do i stirp '-' from a col?

Please someone could help me to find the good index that I should put on a table for this query ? http://pastebin.com/dd84e07d
I tried multiples index (multiples or not) and it's not good

b4ckUp, you are only showing one of the tables and no explain

what explain do you want ?
oh sorry
explain of this query
okay

Therion!
hiiii!

does mysql support foreign keys across databases?

yes

domas|TW!!@!@!!!2334$##@@#!

whoah

wth.. are we in a young girls high school now?

can anybosy help please?

yes

*anybody

young girls!! where?!!!?
gief!

can you help me with with the error is here - "LOAD DATA LOCAL INFILE 'c:/tempstk.txt' INTO TABLE tmpdb FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (gcode,SC2)"

http://pastebin.com/m639f9460
)à

anyone able to help me with this code? it doesn't make sense to me - http://pastebin.ca/641947

swimrr is it SQL ?

what's going on with your code ? and it's PHP

mysql
its from a book, but it confuses me - doesn't it say that if you're not connected, to die - yet it never has a connect command?

:-)

mysql_connect returns TRUE if connected
line 1 is the connect command

but that only connects to the DB right, it doesn't select the table?

while u're doing a $var = php_function(); You execute it… But it's a PHP problem, not MySQL

doesn't line 2 say that if 'myblog' is not selected, to die the command?

if you are connected, it tests also the select_db
nope, there's !
on PHP !function(); it's like NOT function();

b4ckUp, a compound index on comments id,userid or userid,id whichever works best

so if it can't select your db, it dies, else, you're connected
and PK on id only ?

huh - I thought line to was trying to select a table in the dB
thanks

how can i show all procedures?

NULL in possible_keys and ref in both cases

I am having a little trouble with a GROUP BY statement. I have duplicate entries obviously, and one of my columns is reporting the last variable while reporting the first group'd by item… I need to change it to match the first grouped by item

b4ckUp, just noticed c.date as well

I don't understand why

has to scan the entrie table looking at the dates

yep
so idx with id,userid,date ?

dunno if date first will be best but try

how can I retrive the latest recor in a table ?
how can I retrieve the latest record in a table ?

b4ckUp, and run analyse table to it can make a better guess

it lists it as possible_keys but MySQL doesn't use it… Again 130k rows with "ALL" type

HaNNiBaL80, first decide what latest means to you

by date

b4ckUp, then it may think50% and a scan is quicker

or id, its always the last one to enter

order by date/id desc limit 1

so there's nothing I can do ? I have to scan the 130k rows each time ?

date/id? What kind of date is that?

it takes more than 4 ou 5 sec each time

rejig to a subquery to trick the optimiser or whatever

a subquery like what ?
I didn't use this before, if you can show me it would be great

I have a innodb problem

Uh, very strange. I tried the idx (date,userid,id) there's the idx on "key" but not in "ref"…

order in the index matters
it needs to match the query's needs

I see, but MySQL doesn't use it… (I follow the query's needs)

hello all
i have having an issue i have never encountered before

autocommit was set to 0 for a database, and the database is 13 GB. we need to shut the database down, but when we start it back up, its going to need to read in those transactions that were not committed, which is going to take a very long time (this happened once before, and it took 2 days
to start back up). This time there is even more data. is there a quicker way for us to start this back up?

let me make a pastie… but the short version is, it seems like records are missing for certain queries, but exist for others

s/missing/not retrieved/

The initial problem requiring us to restart the DB ios that it has reached it's thread max, and we're unable to connect to it.

what could be the cause of this nonsense
http://pastie.caboo.se/84083

and about the subquery ? How can I do that ?

can I make this assumption…if I'm looking at all the code to create 2 databases including their tables inside, and I don't see any triggers/views or references to another database which is on the same server, that there is no connection from 1 database to another, and they must be using
external code to match up data, like select db1.tab1, db2.tab2 to combine the data?

is this valid GRANT ALL ON ejabberd.* TO 'ejabberd'@'10.0.0.0/8' IDENTIFIED BY 'password; ?

valid for what ?
does it parse ? yes

10.0.0.0/8 , insted of localhost as it was before
the syntax

although I'm not 100% sure on the netblock

what is the limit for PK on innodb's?

have you *tried* it ?

there's a ' missing near password;

how do i remove the previous a grant command
i want to remove the previous block i set

revoke

b4ckUp, c.userid!=6 basicly says all the table except which will be 50% therefore a scan

b4ckUp, revoke ALL ON ejabberd.* TO 'ejabberd'@'10.0.0.0/8' IDENTIFIED BY 'password'; ?

so without c.userid !=6 my query should be faster, archivist ?

may be
the c.date may also equate to a large part of the table

yep
I tried without c.userid !=6 and the result of explain is the same

how do i strip '-' from 99b-2345?

!man replace(

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

Hi! Is it possible to do this? Every MySQL-user should be able to create databases and remove the once he has created. The user shall have full access to the databases he creates. But a user shall have no access to any other database.

!man prefix

Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/prefix

so, nothing could be done ?

dunno rethink your sql

rejeep set up database prefixes for your users, this allows them to create prefix_database databases at will, and only have permissions on those

how do i allow all hosts in a domain GRANT ALL ON ejabberd.* TO 'ejabberd'@'domain.com' IDENTIFIED BY 'password';

rethink my bdd design or query ?

i want *.domain.com

@'%'
@'

b4ckUp, whichever gets the result you need

raah !

adaptr, Ok! Any link where I can read about it?

@' or @'%' ?

all hosts, and '%.domain.com' but it's ALL IN THE MANUAL !

rejeep, just read the grant docs

rejeep the manual ?

okay, but I have to go where ? Looking for a subquery ?

rejeep GRANT ALL ON userprefix_*.*, or summin'

b4ckUp, sorry

you're welcome

Ok, thanks guys, I'll try!

I really need to fix this query ASAP

Any ideas for a decent back up solution, php based?

Anyone around with a Windows MySQL 5.1 setup?
I want to know if –use-threads is appearing in the mysqlimport binaries.

is there anyway to get around the connection error "can't create new thread" without restarting mysql so that we can make a connection (even if it's just one)

Login as root

if i do a truncate table 'table1' it will clear the data but leave the table's fields and datatypes alone, correct?

what datatype do you usually have for dates?

Codler - http://dev.mysql.com/doc/refman/5.0/en/datetime.html
http://google.com

!man truncate

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

yes btw

ok

lol
So you use Google to answer someone else's question, but not yours?

http://pastebin.ca/642025 why is ip continued to the next line?

seekwill - i get generic crap answers =/

Try searching: site:dev.mysql.com truncate

seekwill - from the first line of the 5.0 mysql doc: TRUNCATE TABLE empties a table completely.
completely? as in removes the fields? or just the data within the table?

Well, what would you have without the fields?

Is there a newline in the "ip" column for that row?

Why didn't you read the second line?

i was just thinkin that. shouldn't be but probably is

seekwill - a table with no fields - hense a a completely empty table!

A table must have at least one field

You might want to try visually highlight any strange whitespace with something like: SELECT CONCAT('–', ip, '–') FROM combined;

There's a newline…

seekwill - oh, didn't know that - but that ensures me it won't kill my fields

The second line would have told you that.

again, vague statements, though

Basically, it's the same as "DELETE FROM table". On other databases, like Oracle, TRUNCATE behaves somewhat differently

practical differences?

How does it behave in Oracle?

It ignores transactions, rollback segments and nukes all the rows in a table very very quickly.
In MySQL, there's no practical difference between TRUNCATE and DELETE

Not for MyISAM

ghakko, there is

As stated in the truncate pge…

There is? I've got 5.0.1 installed here, and the docs say there's no difference

Where in the docs does it say that?

Section 13.2.9, in the documentation for 5.0.2: "TRUNCATE TABLE is mapped to DELETE"

You're missing the prefix…
You're taking it out of context
For InnoDB before version 5.0.3, TRUNCATE TABLE is mapped to DELETE, so there is no difference.

ghakko, online delete docs … As stated, a DELETE statement with no WHERE clause deletes all rows. A faster way to do this, when you do not want to know the number of deleted rows, is to use TRUNCATE TABLE. See Section 13.2.9, TRUNCATE Syntax.

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

Aha, I see. It's changed since 5.0.3. I've got an older version
Sorry about that.

This applied even in 4.0

wooot woot! I finally turned up circular replication with 4 nodes

Good luck!

hi all, how comes I can use 'mysql' (console one) but when I try to telnet localhost 3306 / or nmap

next - automatic fail over

my 3306 port is closed.
I am sure that it runs on 3306 fresh install and didn't changed anything.

because it's connecting through the sock?

so I can't use external program to connect to it?

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

The MySQL client can connect over a Unix socket or over TCP. You may have brought up the database with networking disabled

I don't even have my.cnf )

make one

I have 3 .cnf huge medium small and another with 4gb
ok will take a look to those then

Are you really going to be connecting to your database from a remote host? Or are all your applications running locally on the same host?

on the same host, but just for ATM i need to be able to connect from external IP
I am designing the database with an application and I don't want to get sql, upload, run. over and over
this software allows me to connect directly and do that stuff.
I think my "my.cnf" is weird one the_wench I don't have any bind-adress part or something like that

Firewall

dont have any

"bind-address" probably won't be in the stock my.cnf file. It's not needed unless you need to make MySQL listen on a specific network interface. If you leave it out, it'll accept TCP connections on any network interface
What you do need, though, is to comment out "skip-networking" to allow TCP connections

still same result, just restarted the server and put the my.cnf in /etc/
I can connect with phpmyadmin/mysql
but the port is closed

ocZio, what op system

linux, 2.6 kernel, slackware distro

haha

You've got to figure out where your my.cnf file ought to be, if not in /etc

any firewall

I don't have any firewall!
ok ghakko will check that too

Why not?

You might want to look at the output of "mysqladmin"; it'll tell you where MySQL expects its my.cnf file to be

./usr/libexec/mysqld –verbose –help | grep cnf
=/etc/my.cnf ~/.my.cnf /usr/etc/my.cnf

Ok. Did you comment out "skip-networking" or UN-comment it from my.cnf?

I think you need to give me root access to the box.

hey there, is there a way to instead of logging every request only log 1 or 2 dbs requests ?

I can solve anything.
What kind of request?

right now it keeps the logs of all querys
i just want a few dbs to be loged

General query log?

yes

!man general query

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

thnx

That was for me…
Nope, doesn't look like you can

ok
so it is either log all or none

sure

I don't think you can log only certain databases in the general query log. You can, though, do that with binary logs

ghakko I found the solution

k

in my rc.mysqld there was a –skip-nerworking in the command line

Was that Slackware's fault?

I think so, since the script comes with the package (:
now I am able to connect
but my host isnt allowed to access lol

!man grant

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

Be careful not to grant public access to your database.

well god damn I am sick of this :p all the time I took now… I could use that time to just upload and run the script lol :p

Blame Slackware

I blame ghakko for not spotting the issue earlier.

seekwill updated…

anyone know what im doing wrong when creating a hardlinke between apache and mysql
Invalid cross-device link

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 and make sure no overrides on the mysqld commandline

hehe

anyway thank you guys
I will just upload and run the script with phpmyadmin

What's a "hardlink" between Apache and MySQL? Why would you want that?

well there both in chroot
so i need them to talk

Good luck

arnt i suppot to copy the mysql.sock to the tmp folder

slackware sox
*rox

Slackware sucks

seekwill, slack own your little ass

k

whats your problem about slackware

i like slackware
i used it for the longest time

hard to use ?

You can't make hard links across file systems, and you can't symlink your way out of a chroot. You need to either put the MySQL listener socket in the Apache chroot, or accept TCP connections instead

ahahaha

randoman, +1

Their weird users.

ok though so
i read that
ok
so just copy it

You can't just copy the socket in!

for sure
any ideas?

Are both MySQL and Apache chrooted? If that's the case, then you need a bind mount to get past this

yes
how would i go by doing the tcp connections
i guess ill google it

afternoon all. i'm using the nested sets method of tree storage and have done an explain. (http://www.slexy.org/paste/4124). there are 62 rows in the LeadSource table. in the explain there are 3 tables in which it is estimated that it will have to
look at all rows. is this query going to be horrid when there are more rows? is there a way to optimize it any better?

I think the_wench was explaining to ocZio how to do just that 5 minutes ago.

ahh
missed that
ill try to scrolll up

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 and make sure no overrides on the mysqld commandline

Do you collect stats to see which is the most frequently asked factoid

no
I could trawl the log

But it would be cooler if you used SQL.
Doncha wanna be cool?

DONTCHA!

I have a desk fan to be cool

lol

With the added overhead of the UPDATEs, it'll keep your case fans on
With the added overhead of the UPDATEs, it'll keep your case fans on
gentoo–
multi
!man multi server

Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/multi server

seekwill wot ugrade to use triggaaaars

heh

a friend suggested to use the binary packages. he thought it'd be easier maintenance

fatpelt, there is a page on the subject

But that's not gentooy

!man running m

see http://dev.mysql.com/doc/refman/5.0/en/multiple-servers.html

hehe. yeah i know. we've got a new server on order, but i'm thinking that i'd like to get a head start. i need some trigger action

You can emerge the second MySQL server into a chroot, if you can spare the CPU cycles and disk space

fill yer box like snoyes does

hmm. that is an interesting one. that would allow for pretty easy maintenance especially if i build binary packages for the chroot
/me currently have 317G available. we're ok for a while

is there a chance of reversing a drop database command?
i just dropped a database by mistake…

pick up database;

from your backup

i don't have the latest backup
like 12h old..
jbalint did you mean that's a command that will reverse the drop?
just don't tell me that the data are gone…
must be possible to reverse that

i was joking

thats only 12hrs typing or learn to use binlog, if it was on

archivist it was off
so no way to get data back?

you don't replicate?
hmm, you could restore the 12h old backup, setup a slave and import it, then setup a master and replicate via binlog. I'm not sure how you'll get the binlog position though

wrt nested sets, since i have to update the tree on inserts and deletes, should i also just store each node's depth in the tree too?

what's the difference between org.gjt.mm.mysql.Driver class and com.mysql.jdbc.Driver ?

hello
"10.15.9", "1.5.19", etc… However, probably "9" will be bigger than "10". How to sort it correctly?

SettlerX, better normalising

??

3 int columns

or table or…

i don't know how many levels will be…

exactly then..
normalization

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html and http://datamodel.org/NormalizationRules.html and http://mysqldump.azundris.com/archives/20-Nermalisation.html and some here http://www.keithjbrown.co.uk/vworks/mysql/

hehe, i just went through those hoping to answer a different question

is it that you've got child & parent items in the same table?

yes, all categories are in 1 table

so "1.5.19" means that 1 is a parent of 5 is a parent of 19 is a parent of current row?

yes; or 19 = current category's ID for avoiding problems

dumped my .mysql database, its like 10 megs…. how would i go about splitting up the file into many many many many chunks…
I am running nix

SettlerX, you're gonna have trouble working with that model

just use a parentId which is a foreign id pointing back to the id

i have parentID column but i have to display all categories too without recurention or display higher categories of a category.

!tell SettlerX about 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

good bot
i can go either way on this, but i'm thinking it'd be better to optimize for viewing… anybody have a suggestion as to whether i should make a column for min height, column for max height, & then a true/false as to whether it's in metric or not… or whether i should make a column each for
metric min/max & inches min/max?
or might the bot have an answer?

bot smack
bot snack
botsnack

ooooh let me bend over and lift my skirt for that

uhh

hello
is there's a command/query i could run to see all the external connections?
i know i enable the query log i could see them
but is there any way to check them on the fly (ie like mtop/mytop would show)?

show processlist; ?
that'll show local connections too

hehe that would work, thanks, i'll filter the output
silly me

how can i see what character set a table is using

describe tablename?

nope

heh ur right

hello there. im getting some "Didn't find any fields in table" and "Incorrect information in file" on two innodb dbs
what could be wrong?

Exact error message

thanks guys, bye!

!m qwerty_ repair

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

phpmyadmin will show that info, but i'm not sure what db command it uses

Repair InnoDB?

i dont think thatll do

What is the exact error message you are getting? Plus the query.

no queryes

You're getting the error in the paniclog then?

show table status shows me all tables corrupted

ew

"corrupted" == Incorrect information in file
where the innodb data tables stored?
data files*

all in one tablespace in the datadir

I keep getting the error "/usr/bin/ld: cannot find -lmysqlclient_r". I also apparently don't have the "libmysqlclient_r.so" module. I compile mysql from source and it's running fine. This error comes when trying to compile mysql-administrator

How is it possible that the same exact query will result in different output if the DB has not changed and there are no calls to random?

derenrich, an innodb estimated number?

What is the query?

no, nothing like that

Does it involve floating numbers?

no
it's just selects, joins, a where and an order

ooh, this is like 20 questions.
Does it involve self joins?

upper left middle sidways inner join

.frm files?

.frm is not the data

only inner joins and no frms

just the format

SELECT b.fund_id, b.fund_name, r.fund_id, r.ytd, a.fund_id, a.user_id, a.user_type

Does it involve VARCHAR?

FROM watchlist a
JOIN funds b ON a.fund_id = b.fund_id
JOIN return_stats r ON r.fund_id = a.fund_id
WHERE a.user_type = '??'
AND a.user_id = '??'
that's the query where the ? marks are some values

And actual and expected results.

wheres the datadir then?

qwerty_, sounds like the innodb table space is missing/unreadable

actual results vary randomly
(seemingly)

Again, how about pasting some examples so we don't have to guess?

what varies i should say is the number of rows returned

qwerty_, see my.cnf for your location

sadly i can't paste examples

Well, if it's number of rows return, it's different.
Someone else is making changes.

/etc/my.cnf has no location in it

Or you are and not knowing

I'm doing the queries from phpmyadmin…So there aren't side effects of the queries. I doubt changes are being made, but I'll check that possiblity out
thanks

help now is apreciated :/

mysqldump -T db db -u user -p Can't create/write to file (Errcode: 2)

all.sql works fine

I have a dilemat between materialized path and nested trees

qwerty_, check the difference to your backup my.cnf

I'm going crazy.

is implimenting nested trees atm on one of our systems
s/is imple/i am imple/

so you mean?

sorry, i missed some chars there. i'm currently in the process of implementing nested sets
usorry, i missed some chars there. i'm currently in the process of implementing nested sets /u

havent been touched since 2005

anyone know why i would have "libmysqlclient.so" but not "libmysqlclient_r.so"?

I discarded nested trees before (too hard) and the method of making all connections between categories too.
I've thought materialized path is the best solution (but there are some variants of it). However, I have read more about nested trees and i think about use this method.
However, i want to display all categories in good sequence and i don't know if nested trees are so good like materialized path.

SettlerX, seemed good to me .
as per the queries in the dev.mysql article

in materialized path i could count amount of . to get the level of category. How is it in nested trees?

depth
in one of those queries
look at them all
i'm in the middle of implementing nested sets too
or perhaps better said, reimplementing

seems this is the place to be…

of course, the php code built around it is very complex

all connections between categories is also good method but i had a problem with sorting…

so.. i'm having some trouble cleaning it up
well.. i did have 1 sorting problem
so far
that i haven't gotten an answer for
sorting thread head, while keeping p/c relations

anyone know why how I can compile mysql and still not have "libmysqlclient_r.so" but do have "libmysqlclient.so"

but it's prolly due to my sql fu being weak

Post your problem description (URL). I'll take a look when I have a moment.

SharkWave, i might be able to tell you if i knew what the _r part meant

my assumption is restart
typically what that means
for instance, if inturrupted

that doesn't seem right?

the call should restart

maybe..

does anyone have any opinions on the GA quality of the 5.1 beta? how unstable is it?

Xgc, i'll bother you some other time when i clean this crap
Xgc, i'll bother you some other time when i clean this crap
the code has all this stuff for depth buoys and such
bit above my head

hrm

trying to take theparts i don't need out still
keep breaking it in the process tho

if i have a query that's using count/having/group_by, how do I actually count() the total number of rows

5

thats the query

i'm thinking about keeping the depth each node and updating it along with the positioning on insert/update/delete

Some questions about "nested tree":
or not… no questions

fatpelt, is that necessary??
it doesn't seem so

How do I find the type of a column? as in 'tell me the type of column x from table y'

i am getting depth on my get query, but not touching it on insert/update/delete
the l/r stuff should give you enough info to delete properly

show create table table_name; is one way

http://www.slexy.org/paste/4124 i can't get the indexes right

funny fatpelt ..

(it isn't an issue with the deletion, but the depth calculation)

hottest pastebin

Total over all groups? Three ways. 1) WITH ROLLUP … 2) Join with a derived table to provide the total. 3) Wrap that in a SELCET COUNT(vers) FROM (…) v1;