uneed help, how do i reset my user and password for mysql.. since i can not get the root for mysql. what should i do.. /u

Hey ya'll
I have 2 fields. One with "expiry month" and one with "expiry year" — I need to compre to see if they

need help, how do i reset my user and password for mysql.. since i can not get the root for mysql. what should i do..

to see if they're greater than this month and this date
any ideas?

hi, all
how to access variable values of any table, syntax ??

Hey all, if I have variable with "2007-04-01" how can I check to see if that prior to today?

like version,engin,checksum ect
etc*

do they make easy-to-use toolsets for DMSs that let you keep the data when you change the structure of a database? I always hear that it's better to build it the way you want before you put the data in, but that doesn't seem very friendly.

how about , if I just wanted to replicate all the databases I have?
can I use a wildcard?

remove all 'replicate-do-db' then
and all databases will be replicated

cool , thanks :
*:
ugh that key doesnt work
(:

hi, if i wanted to start learning how to make a complicated database where would i start, and would mysql be good for making a stock control database?

complicated?
learn the basic and use it to build up wahtever you required

ok, but again, is php mysql web hosting a good db to use for stock control?

why not? but mysql 5

ok
thx

anything can be used, even files to store your stock data

hello

heya

pen and paper also can

i want to select all fields from a table where first character of password field is * ?

WHERE

kimseong i get your point

LIKE '*%'

SELECT * FROM table WHERE password = * or somthin to that effect
lol

if pen and paper can by used, mysql 4 can by also used

it is how well you can use the available tools than how well the tools can do it

kimseong true
looks like im buggered then

yes i know where kimseong, but i remember a function i can specify the position of character

!m abuyazan string functions

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

anyway, LIKE is better for starting of string if you know that

I have a table with men's and women's first names. I'd like to identify the duplicates (names that can be used for both men and women). Can I do that (efficiently) using SQL?

I think so.

Any idea how?

I really suck. but one way or another you're going to have to get a list of those names to give it.

select names where sex = m UNION ALL select names where sex = F … now I have a list of male and female names, with duplicates
select distinct names where sex=m or sex=f … that gives me a list of unique names
Now how to find the difference between the two sets - without using the painlessly slow "and name not in (select … union all …)

but unless you have a name that can be used for either gender that actually IS being used for both genders, you won't spot it that way.

Oh, it's no a list of actual people's names. It's a list of all possible first names in my country. So there should be duplicates.

how to access variable values of any table, syntax ??

is it normal for a innodb file to be 2-3x the table size?

G'day
Can I execute a GRANT command via mysqladmin or something that can be incorporated in a script?

hey

wide-eye: yes, if it has endured enough deletions and updates

is that true for other engines?

What are the possibilities of having 2 servers with 2 the exact same db's that keep synchronizing eachother? (To distribute load)

i want to select all values in a tabl which fall under a particular range
something like
select * from table1 where age =15 and age = 20
is this syntax rigth ?

can someone tell me if this query is correct

the defrag methods i've tried so far haven't made a dent in the size

wide-eye: it is at least true of myisam and innodb
with myisam it can be worse because even a single row may be fragmented
wide-eye: are you speaking of the size of the entire tablespace on disk, or are you using one file per table?

there is just one innodb file

ah

has one big database, and a few tiny ones

short of recreating the tablespace, i am not aware of a way to shrink a tablespace once it has been extended to a given size
so even if you have optimized the tables within, it would keep its maximum size

i have a dilemna
i want to insert into table A, select * from table B
BuT i want to insert the cheapest from table B (because some products are duplicated

it is clearer to use BETWEEN syntax

insert into table A (select * from table B order by price asc) ???

how does one use a multiple innodb files?
can i define one per database?

wide-eye: http://dev.mysql.com/doc/refman/5.1/en/multiple-tablespaces.html

cant you execute a GRANT or SELECT statement from the commandline via mysql client or mysqladming?

hey

ok thanks

err mysqladmin

1 EST' converted to the local timezone

ok tahnk you

from within mysql hosting only

hm, ok
strange though

sure, check out the -e (–execute) option to mysql

oh
ty

how does this sound for a parts table
oops, didnt mean to send that yet still drawing it
how does this look for a parts table of a stock control system? http://img186.imageshack.us/my.php?image=tblpartsv11by8.jpg

how far can you go with logic in mysql?

3rd base

home run!
all der way, trust me on this one

i have prodcuts in a table
Code, Supplier, Quantity
And price
i want to update another table containing these products also but with a Primary key on CODE field. (ie. no duplicates from different suppliers)

0

so the code in table 1 is actually the non-unique supplier's code ?

yeah

rename it
supplier_code

so Table A contains EVERY item, and table B contains every item but only from 1 supplier so. lots of rows not there

you'll make yourself insane

bad idea
TABLE B ends up duplicating data in TABLE A

0

is what im thinking

why you just don't query table a using some grouping and ordering ?

How do you guarantee the data remains in sync if you make a change in TABLE A?

azuranz if you want all data from table1 from one supplier, select on that condition
if you need to re-use it, make a view

well i haven't explained it all hence why you guys don't undersatnd

i think what he's saying is table B is intended to represent the "best current supplier" for a given code

tableA is emptied & updated daily with imported CSV files containing products from different suppliers
TableB is the Live inventory table for the product database

ow, my brain…

okay, so table B should conatin all products

table A contains all products

not just from one supplier

tableB contains all products but only from one supplier. TableB is the live product database
the poitn is so that someone browsing the website doesnt see one product 15 times cause we have 15 suppliers

ok, so don't empty TABLE A, update it against the new CSV.

doesn't matter. Table B is never emptied

And use a view in place of TABLE B

guys? , this look kinda ok? http://img186.imageshack.us/my.php?image=tblpartsv11by8.jpg

so can you set multiple values in one update?

i think what he's saying is table B is intended to represent the "best current supplier" for a given code

the reason that might not work, is because in-stock quantities change from day to day, and you may not want to change suppliers if all of the sudden they are all out of stock (depending on the problem)

yeah

Giddion55 a jpg could not possibly hold meaningful information about mysql that a text file couldn't

what does the problem say you should do if they are all out of stock, is tie still broken by lowest price or ?

hello, why does select count (distinct *) not work?

so everyday i want to update TableB and (where there is a change) set the B.Price = A.price, B.supplier = a.supplier where b.code = a.code and a.price b.price
yeah lowest price
which is why i originally asked how far can you go in mysql… can you tell it, if all quantities are 0, just go by lowest price

adante its a screen shot of somthing ive drawn out, part of an ERD

guten morgan mein mysql buddies
is it possible to do the following?

oh wait ez76 how about

select blah from table where field = ",*"; where the field data begins with "," (comma) and is followed by anything?

ancientt is this better http://rafb.net/p/800Skv64.html

0 OR b.quantity = 0))

you can't do what you are asking, directly

isn't that exactly it?

yeah, conceptually

Giddion55 you want to make any FK a unique value, preferably an autoint as well - put your parttypes in a separate table

well minus a few words looks finished? whats wrong with it

heh

0 OR b.quantity = 0))

hmm ok, ive got my types in a diff table already i think the notation is off on my part though

wow i just realized my question was totally retarded

Giddion55 do you have real tables yet ?

i had my head wrapped around regexp

just to be sure - if a supplier doesn't have a product at all, is there still a row in table A?

no just paper

when all i can do is select blach from table where field like ",%";

im drawing them out first

although i would be curious if there is a better way so i dont require using like

Giddion55 the best way to inventory a database design is to start with actual paper data the database needs to hold… then normalise form that

yo ez lemme pm u

but that only works if there is sufficient data on paper, of course

i dont have any paper data just 600 spreadsheets lol
in polish…. lol

the server is not configured as slave. How can i fix this? This is a development server so that's why i'm asking… to make sure

Giddion55 so first write out any possible attribute of a product/part, and see which are unique to each different entity - those you group in a table
Giddion55 the table you showed will cause you many problems later on

i would appreciate if anyone would have documentation on how to set it up

what type of field should i use for an year field ?

what is the maximum data support by mysql 5 ?

maximum what ?

Maximum data

what does that mean

max data size per table? max data size per database? max data tables? max what in particular ?

xushi what is the exact error?

Database Size.

google the exact error
xushi_ rather

i did, but that's not the problem.. sec, let me type

adaptr thanks
btw, does anyone know polish? does "suma" mean total?

i was told if i were to make a dump, i should switch the server to slave mode first with –start-slave and –stop-slave afterwards. But when i do that, i get the error shown above: "mysqladmin: error starting slave: the server hosting is not configured
as slave; fix in config file or with CHANGE MASTER TO"
now i don't want to mess up everything, that's why i'm asking for advice first

max data size per table, max data size per database and max data tables — all three information will help me a lot

i think you'll also need to inform the channel which mysql version you're asking about

i told already, it is for mysql 5

prosenji dev.mysql.com will show you all of this
but it's big, believe me

heh yea, i'm still searching in it

Any link that you can provide me where i can find out the details.

prosenji dev.mysql.com holds the *manual*
it has an index, and everything

am i understanding this right? if i want to switch from –start-slave and –stop-slave, do i have to have two different mysql servers ? If yes, do they have to be on two diff physical machines ?

if you want to use a slave, that has to be a different server, yes
otherwise, no idea what you're talking about

does it have to be on a different physical server ?

well, no, of course not
a different *mysql* server

thanks for the advice
hehe
to rephrase, thank you for your kind help instead of "google" or "google"

i only suggested to google the error
i wasnt suggesting to "f*** off"

yea i know.. i'm talking about others on icq that only have 1 answer to ALL questions.. "fsck off and google"

xushi_ I still have no idea what your question was, so I consider it a freebie anyway
xushi_ this is not ICQ

true

it will never be ICQ

well to be more specific adaptr, i have a script that dumps mysql databases for backup purposes, but i was advised that it could be dangerous if i'm dumping while others are reading/writing to it.
they suggested that i use "–star-slave" and "–stop-slave". When i tried i got that error.. So i was just asking on what i was missing

dangerous, no, but it will (should) be locked

correct without the correct locking

and did "they" explain what that does ?

ah, i do have locking, sec. i'll paste it. And no "they" didn't.. that's why i asked here..

speaking of icq
i dont understand how it became so popular
him icq me on 23418239412304812394812348123941238412394

it was Jewish!

s/him/hi

foo.sql

lol

what's the current 'secure/stable' 4.1 release? I've just taken over a box with: 4.1.18 rpm from mysql; is this ok?

is it possible to rearrange the order of columns in a table without losing data?

yes but why bother dont use *

is there some 'common' way of supporting automatic versioning and upgrading of mysql dbs? Some sort of diff for mysql?

bits of..
toolkit

xaprb's MySQL Toolkit (http://sourceforge.net/projects/mysqltoolkit/) includes tools to compare databases across servers (such as master to slave) and bring them back into sync, profile queries, and other handy features.

hows this look for the entitys of a parts table? http://rafb.net/p/kFdQ6Q83.html

Giddion55, still missing a few

heya archivist

SELECT * FROM files WHERE tags LIKE ftp doesnt work eh?

ive just taken what they are using at the moment in their spreadsheet
any sugestions for other entitys?

i want to search a table, then have the column where there is a entry like it

Sashi, like "ftp%"
Sashi, like "%ftp%" will be low and cannot use an index
Giddion55, stock location

very good one

price per unit lenght

now, this is where i was a bit unsure, thats going a little "out of my box" of operation at the moment
i wanna start small and just put a parts table up first then link in the rest

well start filling and amend when you find stuff does not fit/do whats needed

so i was thinking of getting a parts table up with a little data in it then seeing what i need next (i know its not thinking of the bigger picture so ill miss some things, but its my first try so i dont wanna bite off more than i can chew )

good day archivist

morning , off out in a bit

there is 5.2 alpha as well since yesterday, apparently :-

hi everyone!
http://paste-it.net/2915 is it possible for item 2 to get the product title of item 1?

dodgy

SET productid = 1

;-)

roxlu, its what joins are for

i want to thank you for something you taught me a number of months ago
i have finally found a job here in germany .. in köln as a matter of fact and i have been able to apply your explanation of many-to-many relationships using a third table with total understanding and courage now… woohoo!! thank you again…

how do i set a password for a given user on a database?
haha, nevermind, finally got it
er…so i didnt

useless man
google it. or use phpmyadmin

err, again, i think i got it

godam

i did google it, and have been reading
with no
luck *
which is why i come here

select * from ceis_ty.type where id IN (case type.tower_id when 20 then 19 else 21 end);

http://dev.mysql.com/doc/refman/5.0/en/set-password.html

haptiK, mean me?
http://pastie.caboo.se/80192
why this doesn't work? what's wrong?
ahhhh found it..
but shouldn't this return three items? http://pastie.caboo.se/80192
http://pastie.caboo.se/80192
nobody here who can help me? nobody knows more than (a newbie)?

can anyone tell where's the error in this query ?

u want this to return 3 rows ?

"php
if ($value)
return
'img src="{mossite}/administrator/images/tick.png"/';
?
"
ipus
ups
select c.id as id, c.userid as userid, u.name as user, c.nume as nume, c.prenume as prenume, c.adresa as adresa, c.localitate as localitate, c.judet as judet, c.ziua as ziua, c.luna as luna, c.an as an, c.telefon as telefon, c.email as email, c.acord as acord, from jos_rnr_contest2 as c left
join jos_users as u on c.userid = u.id order by user, nume

mayavi, yes
thought so

it would not ….

alecssys, stop flooding the channel, willya

check the docs for CASE statements
if you want three items why not simply run — select * from ceis_ty.type where tower_id IN (19,20,21)

I am experiencing extreme slowness when querying with the IN predicate. My dependent subquery returns 7 (seven) rows - shouldn't the primary query take (at max) 7 times its normal time?

when the tower_id is 20 the the tower_id is 19 else the tower_id is 21
mayavi, i want to learn about case… (remember: newbie)

ok …. what's the error in this query ? http://pastebin.com/da6baba3
please

mayavi, ah it should return 2 resultsets, right?

it will return the last one …

mayavi, but it should return more than one resultset…
mayavi, aha

The comma (,) in front of "FROM", perhaps?

yeah … i was about to tell him that …

mayavi, how would you use the case in this case?
mayavi, don't care about it's senseless…
that it is ….
mayavi, but i want to learn….

YESS
thank you very much

so i have to use simple examples.

When everything else fails, check for syntax errors

mayavi, why would it return the last item?

i knew is a syntax error … but i couldn't see it
*it was

Has any of you got any good experiences with the IN predicate? I find it's making queries extremely slow

yeah
i use IN

bquorning, rething your db structure then, if you find it slow
rethink*

My dependent subquery returns 7 rows, so I thought the primary query should at max take 7 times normal time.

index
the field

mayavi, i found out that case-statements are quite senseless in the where-clause (as i used)

where id = '1' or where id = '2' or where id = '3' or where id = '4' / where id in ('1','2','3','4')
your query do what you want

Everything in sensible in particular context

mayavi, do you have a good example for using case in where-clause which is not that senseless as mine?
salle, ^^

hi!

.. WHERE some_int_column = CASE category WHEN "apples" THEN 1 WHEN "bananas" THEN 2 ….

i am trying to search for a number in a text with match against but it doesnt work… do i miss something?
Select Sql_No_cache * from table where Match (doc_txt) Against ('23' in boolean mode)

I don't actually know what are you trying to do

if i search for a word it does find a match! but not for a number

Full text search looks for 'words'

salle, i try to understand how it works…

CASE is most often used in SELECT part tough

'23' is also a word or? (string)

salle, right. now i know.

if it is delimited by spaces or some of other "delimiters" fulltext defines then yes

My query is something like "select * from foo where a='b' and bar in (select bar from x where … group by c bar having count(c)1 )"

But if it is part of other string like "abc123xyz" forget about full text

how do i find the 23 in "Friedrichstr. 23 …-2.e-. "

the "inner" query returns 7 rows, and performing the outer query with "bar =" just one of these takes no time. But performing the query as written above takes ages

is find_in_set faster?

Using the 'explain' function, it says something about 12000 rows in both the primary and the dependent query
I don't know that function. I'll take a look at it now

instead of bar in … you could do find_in_set ( bar, list) (check the syntax, i always get confused with the syntax of find_in_set)

How can it be used in this context? …where find_in_set(bar, SELECT … GROUP BY … etc ) ?

CASE is good when you want to convert between different types. Example: .. CASE WHEN age BETWEEN 0 AND 3 THEN 'baby' WHEN age BETWEEN 3 AND 12 THEN 'child' WHEN age BETWEEN 12 AND 18 THEN 'teenager' ELSE 'adult';

i would say so

salle, quite useful.

http://bugs.mysql.com/bug.php?id=1030 , that is a bad explication, why this is not a bug!!!!

Ok, lemme try

whats the default mail reading program in debian

For simple stuff it's easier to use IF()

"mail" just sits there and pine certainly isnt installed
never mind ill just read /var/spool/mail
sigh

where does it say that match against doesnt like numbers?!??!

MySQL complains when the subquery returns more than 1 row.
Is there a way to "flatten" more rows to an array of sorts

hum… we can make any or all
you can use i mean

I might as well use IN, then. Isn't IN just a shortcut for "= ANY" or something?

could be

Yes it is

In any case, ANY seems to be just as slow as IN

What else do you expect?

By now, a miracle

where the hell can i read that match against doesnt accept numbers?!

It seems you can rewrite this to a join. Try and see if it is faster
It does accept them, but it is not written with numbers in mind
What about ft_min_word_len?

why do i dont have aresult then? i mean doc_txt like '% 23 %' does return a result
ahh, i see your point…

If like '% 23 %' doesn't retutn result then you don't have ' 23 ' sequence anywhere in doc_txt

but it does!
the match aginst doesnt

0 OR i.quantity = 0))

SHOW VARIABLES LIKE ft_min_word_len; then and read about it

fuck, it is 4!
salle thx a lot!

Also, try using the fulltext search in boolean mode (http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html and the last line in http://bugs.mysql.com/bug.php?id=1030)

thx, but it seems salle is right, i mean it wont index the number 23 if it indexes only words bigger than 4 letters

Indeed. But the last line in the bug report indicates that it didn't work even with ft_min_word_len=1, when not using boolean mode
You said something about rewriting my query as a join. How's that? An inner join?

Yes
I didn't read yours carefully to be honest, but in general SELECT * FROM a WHERE x IN (SELECT x FROM b) can be rewrtiten as SELECT a.* FROM a, b WHERE a.x = b.x;

question

If you have a question, please go ahead and ask it. We will not beat it out of you.

Good bot! Have a cookie!

how do you set A.Price = B.Price BUT….. set the cheapest?

1) . I am not sure if that can be done with a join?

Example?
Why not?

Because I'm not so good with joins I'll give it a try.

well table B has multiple products with a single "Code" value

SET A.Price = SELECT MIN(B.Price) FROM B WHERE … ?

ah
but
it's currently
set a.price = b.price where a.code = b.code
so will that still work?
set a.price = select min(b.price) where a.code = b.code

heh .. there are now ads in the mysql docs ..
http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html
not just in the sidebar

update a set a.price = (select min(b.price) from b where a.code = b.code);
Where?

hi everybody

look in the link .. it talks about how mysql enterprise advisor lala monitors stuff

Of coruse it does. The manual is for both the community and Enterprise customers

well the thing does not come across like a manual entry
more like an add
if its a manual entry .. it should contain much more information
and i think the "For information about subscribing to this service see" gives it away as an ad
anyways .. i do not mind it all that much ..

13 on a phpwebgallery server

aisde from the fact the i think ads should be clearily labeled as such

http://deadbeefbabe.org/paste/5481
If anybody can glancing to the paste It will be thanksful

type at the shell # perror 13
OS shell and you will see what does it mean
$ perror 13
Permission denied

in root user

mysqld user does not have enough permissions

Hi, what is the way to get a dump a specific a table/tables not a full database?

use mysqldump and specify the database
oh, nm

I have already 145 pictures in my table but when I try to put 220 extra pic ; mysql return error

how?

please see http://deadbeefbabe.org/paste/5482

ruben_, man mysqldump, rtfm

[mysql error 1005] Can't create table 'phpwebgallery_categories_1184841006746304' (errno: 13)

just type mysqldump –help

ok, sometimes when i do an update, all the text goes in the database, mostly only some part of it does
sometimes none of it does

hi, is there a way to set to default value current time, I mean if the insert doesn't have value for a datetime row add the current one?

use timestamp

jessy_, now()

tibyke, thank you

hi all - i have just started to play with federated tables in 5.0
all seems to work well, but i see no way to allow people to use the federated table without them being able to see the password used for the connection

is it safe to use CONCAT on blobs?

so, is there a way to prevent the password to show up in SHOW CREATE TABLE, for keep people from using that command, but still allowing them to SELECT from the table?
in 5.1, it seems to be possible with pre-defined connections (CREATE SERVER…), but i don't see it in 5.0…
any ideas?

aww fuck

that cant be good
lol

using innodb, can i lock a row in such a way that when other threads access the table, locked rows are not selected while everything works the normal way?

be happy that it actually runs
i wasnt able to use it in my case

Is it possible log quieries only for specefied DB ?