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

is there any way to specify that a given column of ints, will show leading zeros? (i.e.- a zipcode column, should show 00123 instead of 123)

ALTER TABLE `posts` CHANGE `date` `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP; ?

use zerofill and a value after the int. like ‘int(5) zerofill’

thanks threnody

the value in the parens doesn’t change the size or storage of int.
alter table tablename change column columnname my_date timestamp;
you cannot set default values with a function. timestamp will do it by itself. read the manual.

ok, its odd that mysqladmin gives me this option

well, you can write the sql host anyway you want. doesn’t mean it will work. ;^)

the serveris 4.1
does it make a difference?

only the first timestamp in the table will automagically insert the current timestamp each time you insert or update. Just don’t include the timestamp column in your sql statement.

ok :p

that version won’t matter.
that version will work fine.

Is it correct to state that Mysql 5/innodb foreign keys can reference either a primary key or regular key(s) but not both a primary key and a regular key?

hello sirs
any of you know a reason why i couldnt copy a folder from my old hard drive from /var/lib/mysql/dbname to my new /var/lib/mysql and have it work?

you can ask threnody as well, is he’s available.

or could any of you suggest a good way to import my databases from the files that live in that folder

s/is/if/

if the mysql server was halted before you copied, and if the versions are the same, you should be OK

hrm, I don’t know if the versions were the same, but it certainly was halted

just make sure the new files/directories are owned by the system mysql user.

i did apt-get update or whatever to update my packages, and my grub lost the ability to find a kernal

does the old server still exist?

^conner_: the target of the foreign key constraing needs to be indexed. having a composite foreign key is not a good idea.
^conner_: that would kinda indicate bad schema design.

so i slapped a new hard drive in, moved the old one to slave, and reinstalled

that would be a no.

arjenAU, yes, the errors of my ways are being explained in #SQL

ah. well, you have nothing to lose then, do you? ;^)

should work, and you can run mysql_upgrade to check it out after.
you may also need your my.cnf

what now

^conner_: righty.

arjenAU, thank you thou

with 64bit mysql can I make my innodb pool buffer 5G, if I have say 8G in the system
dedicated mysql box of course
Im just asking as when I was 32bit, the whole of php mysql web hosting could only reference 4G

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

threnody, Im taking about innodbs buffer pool size

sorry, my error

just wondering if I can set it ok at 5G

http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html

yeah
thats why Im going for 5G with an 8G machine
I just remmebr on a 32 bit box max was 4Gb for whole mysql process
buffers, threads etc

try it and benchmark, eh?

yeah
just tried
I’ll let you know

I’d be interested in hearing.

how to duplicate the records of a table, just to easy the ‘fill in’ proccess to testing?
ive heard about join
but i dunno how to use it

your q has nada to do with a join

hm
how i do so?

so you just need a lot of rows? I preusme there’s some requirement on the data you stick in.

not necessarily
just the new ids

as otherwise it’ll have no relevance in terms of indexing.
if your index statistics are different, playing with # rows has no relevance.
mysql can handle bllions of rows, in case you were doubting that. no need to test that

i just want to fill a table with a lot of records
no… i mean testing, because im using another process

easiest way in your case is write a little app (say in PHP) that just loops and inserts.

processes
hm
theres no other way?

I’d write a stored procedure to do the same.

hm

just having lots of rows is not relevant to any test.

theres no sql hosting instruction that can duplicate itself?
the records

but you are of course free to waste time on useless benchmarking

no
no

no.

isnot about benchmark
its just for testing other things
okay
thanks

what is the standard collation?
mine all say latin1_swedish_ci which sounds a little weird to me

That’s the default.
Why does that sound “strange” to you?
(it is a database from a swedish country…)

right, im not from sweden
so why are my databases?
good night pals

you’re too cheap yo buy Oracle?
s/yo/to/
;^)

Well, unless you’re from Germany or France that collation happens to work for most of the western world too :p

removed

hi guys

erm, wrong window.

can someone point me to the easiest way to setup master/master db with mysql?
is replication the way to go, or is there some other way?

Hi. I have a table with a data field. I want to display all records where the day is 7 days ago till today. Could anyone please tell me the date function that checks “periods”?
soz, I meant a table with a _date_ fiels

another question, if i setup mysql in master/slave, can i still query the slave in readonly?

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

(today-7)”

nevermind. I worked it out
I’ll just combine the functions
thanks again

Is there a specific pastebin I should use for this channel or will any pastebin do?

i dont think they are picky

I have a tricky query I can’t get working

i rambled away for a while and i came back like 10 minutes later and some guy had an answer for me
good stuff

This is IRC. Of course they’re picky

unfortunately im a helpee not a helper

http://pastebin.com/d1338b0b6 I am trying to build a query that lists dated records from some linked tables. The records are numbered and dated. I want my list to show only 1 of each question and only the most recent by date. The best I can manage is a
mess.
I know my approach is wrong (its not working).. Its two queries joined by a union. The first query lists only those questions which appear only once, and the 2nd query only lists 1 record, the question with the max date. I suck at complex queries.
What I want to know is, is there a better way?

can anyone tell me if this is safe (relatively)
http://www.howtoforge.com/mysql_master_master_replication
i don’t see any conflict resolution at all (!)

I’ll continue hacking away at my sql monsterpiece. I’ll check in here regularly. I’m sure the problem is my method is wrong, but I don’t know a better one.
:O I think I solved it already :O
I just had to throw out the having clause.

If I “stop slave” and start using the former slave as a master, what is the right way to tell it “and never start slaving again after a restart or reboot” ?
I’m having trouble finding the answer to that in the docs. Or, perhaps I have found the answer, but I’m having trouble finding something that confirms that it is actually the answer to that question :/

I’m always slaving away. I’ll never truely be master.

stop slave;

show slave status\G;

tired.
… and hungry
down for maintenance – gone to kitchen.

there’s a skip-slave-start line you can put in /etc/my.cnf
I forget the exact syntax

If I wanted to allow a user to access the server hosting from another IP, is there a simple way of doing it?
this user already has access allowed for one IP, I just want to expand it to another ip

remote

remove bind-address= and skip-networking from my.cnf and grant permission to the external ‘user’@'host’ and remove any firewall rules

is there a way to randomize the order of results?
(for doing things like statistical sampling)

well you can do “order by rand()” but that can be slow if you are working with a lot of rows

ToeBee- yeah, I’d be working with lots of rows.. (probably up to 150k rows)

so there’s no real way I can easily copy a user?

eh… that might still be managable depending on what you are doing. Try it and see how slow it is

I have definitely fixed my dodgy query. It was right all along apart from 1 clause.
yay me!

yay!

will do ToeBee … Just gotta wait for my import to finish up..

well usually I create new users with a GRANT command that specifies ‘user’@'host’ so you could just rerun the same GRANT command with a different host

I’m thinking I’m going to go “Show grants for user”
then just edit and rerun those

I’m pretty sure there’s a way to do it without changing my.cnf, I think I’ve done it before. but don’t remember what it was.
something you can tell mysql that causes it to forget it was ever a slave.

Anyone see anything wrong with this?
DELETE FROM ispwatch_log WHERE time_created = now( ) – INTERVAL 25 hours
Doh, guess ist supposed to be just hour, not hours

nice catch

ugh, I can’t seem to copy a mysql 5.0 myisam database to 4.1
I wonder if the only way to resolve this is to dump

ahh, the answer to my earlier question is “reset slave”

i have a question about strategy, i have a webserver with mysql and i have office installation of mssql. Users need to be able to update their own info, but so does the office.
using odbc, how can i keep those databases syncronized

how much volume?

i have freetds and unixodbc working now

you can create some kind of federated table in sql server to pull from mysql

well, i think there will be too much volume to interactively update the mssql server
ok, i’m a little fuzzy on federated tables, will changes at the office also propigate to the web server?
i guess federated tables don’t support update

um, are you sure 5.0.45 is GA?
downloads page is showing 41 for me

I’ve got some data in my table that supposed to be stored as ” women’s “
how am I supposed to search for that? WHERE foo like ‘%women\’s%’?

mhmm, women

don’t do that. LIKE with % at the start is slow, because it can’t use an index.
use a fulltext index, or find a better way to store this data

it’s short data, it’s only a varchar(12)

Try it

try what?
I tried the where statement like I specified, and ti’s not pulling the results correctly

they do.
but the user on the remote system needs to have update privs.

doesn’t matter how short it is, the server will have to consider every row in the table
(table scan)

oh

that’s something you never, ever want to do

so what’s the recommended way to search then?

don’t . if there’s really no other way to do it, look at a fulltext index

how is the query different with full text index?

WHERE MATCH(col AGAINST ‘women’’s’) or something – i don’t remember the exact syntax offhand

ah
is it possible to update data where I just want to find instances of \’ and replace it with ‘?
looks like that’s part of my problem

guys
i imported a csv file and phpmyadmin is giving me this error
http://pastebin.ca/613823

as t” in an UPDATE statement ? I’m trying to modify a SELECT that I have already

what’s the bind-address syntax to set many IP ?

none

?

you can’t do that

i would bind on both localhost and eth0
can’t i do ?

people write patches for that )

hi , im not good with mysql , how can I have two tables , with related data?
or can anyone link me to some text on it
for e.g I want to have Table 1 with all the types of animals , and Table2 with the actual data
so I can select the type of animal from table 1 and the its data from table 2

Learn about Joins

ok

SELECT table2.* FROM table1, table2 WHERE table1.id = table2.animal_id;
SELECT table2.* FROM table1, table2 WHERE table1.id = table2.animal_id AND table1.id = 123;
and so on

salle, ok thanks ill read on it

A good starting point for SQL is http://sqlcourse.com/

ok

Go through all lessons. Joins are covered briefly in second part of the course

ok

hey guys. im trying to allow another computer on my home network to access my sql server on a different computer on said network.
can ‘root’@'localhost’ have a different password as ‘root’@'192.168.0.6′

yes
they are completely different users

ah. MySQL Admin program, for windows, shows them in a cascading tree thing, as if they’re the same user.
how annoying. now it’s not even able to connect. this is proving to be more difficult than i had thought.

perhaps you have skip-networking or bind-address=127.0.0.1 in my.cnf
(some OS packages do that by default)

whats the ‘wa’ state when looking at the top command in linux ?

!man top

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

thanks

hey guys
does anyone have any experience with views of pretty big innodb tables (400K rows) ? i’m having a lot of issues with this .. i have this view that’s pretty complex (joins of several tables and other views even), and when i select from it, i get the strangest behavior from mysql 5.0.41 .. it
seems to only send 16K packets every XX seconds (XX being usually 15 seconds), and in between i have no idea what it does ..
anyone ever seen that ?

try SQL_BUFFER_RESULT

ssso.. how would I update a column in multiple or all tables in a database at once?

foreach (@tables) { print “alter table blah …\n”; }
cut/paste

400k rows is ’small’

domas, hey any idea why it says 5.0.45 is GA?

because I set the topic

getting too many conflict when i was upgrading mysql version from RHEL5 . then i remove some binaries and libraries where mysql exist, like i hv been remove “init.d” “mysql pid” and now my remote machine is dead … ? what is the simplest way if u r getting mysql version conflict?
ugetting too many conflict when i was upgrading mysql version from RHEL5 . then i remove some binaries and libraries where mysql exist, like i hv been remove “init.d” “mysql pid” and now my remote machine is dead … ? what is the simplest way if u r getting mysql version conflict?
/u

domas, yeah but 5.0.45 isn’t showing up as GA .. it’s showing 5.0.41 = GA

release announcement pending

did you see my problem about 5.0.43 being broken on many mirrors (corrupted archive)

domas, unbelievable … any idea when that release is coming?
the announcement release i mean
i’m wondering why the sudden 5.0.45 release (security? .. big bug?)

mm, probably with 4.0.45 out there it is no longer a problem
no, usual engineering
release process is quite complex
and sometimes releases are skipped, if it is seen, that is better to do a new release clone-off

domas, those 2 trigger bugs should be released in 5.0.45

hehe
trigger bugs \o/

5.0.45 contains very important fix for solaris

sniffle http://kostja-osipov.livejournal.com/26815.html
domas, ya even your own employee wrote about it.. I didn’t even know … planetmysql.org .. it’s pushed down like 5 articles now but it’s a good quick read

Hi guys. I have a problem the following sql statement is not producing a set of records inlcluding customer 5 and those starting with 345: select customer, fld1, fld2 from customer_table where customer = ‘5′ or customer LIKE ‘345%’;

G’day

hi, how how can i get a line number of a register??

I am compiling mysql and am wondering why a mysql user is needed if I install locally in my own homedir … ?
oh, and group mysql also …
that is not good without root access …

yeah well it seems to be big enough for MySQL to behave very badly
plus there’s several 400k tables joined

then… check if proper indexing exists
I’m used to joining 100m tables with 100m tables with multiple 10m tables
and requests still are instant

yeah but do you do it thru views ?
my main problem is the views
i’ve also dealt with bigger table joins without any issue as long as views were not in the picture

then don’t use views! :-p

hi. I want to make multiple updates on mysql db. but as I update row by row…I get confilce bevcause of unique keys… after all updates are done. I will not have problems. Can I disable check before update and enable it after this again?

unfortunately i don’t have really have this luxury, i’m pushing for my client not to use it, but his whole application stands on it, and i don’t really have time in the schedule to migrate to another solution .. plus i’m personnaly intrigued by this problem :

check plans anyway

Hi guys. I have a problem the following sql statement is not producing a set of records inlcluding customer 5 and those starting with 345: select customer, fld1, fld2 from customer_table where customer = ‘5′ or customer LIKE ‘345%’;

any clue what’s wrong with http://rafb.net/p/NE3Duh29.html ?
and how to solve it?

swure, your mysql installation is broken
or is loking for the data files where they aren’t
‘./mysql/host.frm’

yeah, how is that created?

I can’t seem to allow remote connection to mysql server using grant syntax

or how do I create that?

take a look at your my.cnf
and let me know there the data folder is set to

I have modifed my.cnf

sarikan, my.cnf, google about mysql skip networking

grant all on X.* to Y@’%’ IDENTIFIED BY ‘Z’;
returns Query OK, 0 rows affected
and the funny thing is it used to work yesterday after using update syntax and flush privileges

Any anyone help?
Can*

you can’t connect from remote… or you receive a bad login from remote ?
two completly different problems

select customer, fld1, fld2 from customer_table where customer = ‘5′ or customer LIKE ‘345%’;

where is the my.cnf usually located?

raar /etc/my.cnf
Voltaire, the query seems right

/etc/my.cnf: No such file or directory
I have /var/db/mysql/mysql/host.frm though

raar, wich linux distro have you ?
or do you have.. my english is poor xe

FreeBSD

I managed to connect from a remote wind server yesterday

Installed MySQL from ports

now It does not work
even the telnet 3306
says the connection is refused
local conns work fine though

Yeh, but for some reason it doesnt return the correct results. I wondered if it might be the type of index its using or something else.

sarikan, then it’s not a problem with user permission, but with a firewall bloking the port or with a wrong my.cnf configuration
take a look at your my.cnf and paste it somewhere so i could take a look
Voltaire, you sure it returns something if you change the query ? maybe the problem is just a wrong mysql connection

If i remove either customer = ‘5′ or the other condition then it returns the correct set of respective records.

raar, /usr/local/etc
¿?
SELECT customer, fld1, fld2 FROM customer_table WHERE ((customer = ‘5′) OR (customer LIKE ‘345%’));
raar, it doesn’t exists in freebsd, you need to create it manually
and set in it the data folder where you mysql data lives
mysqld looks by default in /etc , so i should create a /etc/my.cnf

problem solved, I changed to binding address from the name on network to ip and restarted mysql
it’s ok now
thanks

in my installation it looks like :
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
sarikan, extrange anyway, it should understand your code … but life is life
uops, sorry, confused question

ah I see – cool, I’ve made it in /etc/my.cnf – any clue where to set the path to ./mysql/host.frm ?

congrats, sarikan, it had to be something related to networking

select count(*) from user where username = ‘adf’ and password = ‘asdf’; is there a way format a query like that such that it returns a boolean value? I dont ever remember this ability in any of my sql learning, but i thought i’d ask.

raar, that “./mysql” is not a valid path, you must know where the mysql/host.frm is
full path to it, you know
maybe is as simple as /var/lib/mysql ?

yeah, /var/db/mysql/mysql/host.frm

mmmm

I just don’t know how to tell mysqld_safe where it is

so it should be datadir=/var/db/mysql/
or datadir=/var/db/mysql/mysql/
write it in my.cnf, restart mysql and let see what happens
CppIsWeird, can’t you just check it after the result ?

… thats not what i asked.

a 0 results will be false and 0 true… for example in php : if mysql_num_rows($result)0 { …
ok, CppIsWeird… so take this answer: insert random no education and shit here

I put it in there (under group [mysql], still it gives the same error message (/usr/local/libexec/mysqld: Can’t find file: ‘./mysql/host.frm’ (errno: 13), Fatal error: Can’t open privilege tables: Can’t find file: ‘./mysql/host.frm’ (errno: 13))

its my “lack of education” being the cause if your inability to understand a question?

no, CppIsWeird, i understand the question, and i’m just asking to see alternatives… but being answered “insert random shit” is not my idea of education, and i’m not going to spend my time trying to help you
just that
raar, it’s processing /etc/my.cnf at least ? or must you call it as a parameter from command line ?

apparently not, i asked if something was possible, you give me a completely different suggestion. not even related to sql.

yep it’s processing it

can you suggest me how to select without repetition when i use “like” operator

it gave me an error when I put the datadir line in the wrong group

CppIsWeird, ok, as you wish, don’t want to spend more time in this nonsense disccussion.. maybe somebody else ants to help you
nice, raar, so at least you already have a file to config mysql

for example ’select distinct * from topics where topic like “A%”‘ returns me a lot of rows…

let me google a bit about freebsd my.cnf
Gorb, because there are lots of * combinations
try distinct(id)
or just try select * from topics where topic like “A%”
it depends what you want as results

i just want to select topics begins with some letter, without repetition
simply to know is there topics like this…

SELECT * FROM topics WHERE topic LIKE “A%” GROUP BY id
if you want start with, you need “A%” , and change that “id” with your unique index

thanks a lot!

np
raar, another try
Check that the user which runs the MySQL server (mysqld) has enough privileges on the
‘mysql’ directory and on its content.

ah okay, I’ll check thanks
yay! it works *dances* Thanks MarcosBL!
appreciated

hi, how do I get the results of a query in the mysql command line formatted differently (columns under each other, instead of next to each other)?

)
no problem man

it was something like \g or something but I can’t seem to find it

Juice10, not sure if this helps, but try http://dev.mysql.com/doc/refman/5.0/en/vertical-query-results.html

MarcosBL; Thanks that was it!
damn it was with a capital G
thanks MarcosBL

np

how can I select conditions from other tables? for example ’select * from contents where contents.id = topics.for_id’ doesn’t works

Gorb, it’s called a join, read a bit about it

ok, I’ll try
thank you for direction

select contents.* from contents, topics where contents.id = topics.for_id
If you only want columns from contents indeed. But you should definitely learn about joins
!man joins

see http://dev.mysql.com/doc/refman/5.0/en/joins-limits.html

!man join syntax

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

better

great!
thank you too

Hi all, regarding the new MasterCard Secure Code and Verified by Visa (3D Secure) rules – which basically say _have_ to use the 3D Secure service for Maestro cards, does any one know of any reason why an iframe can’t be used to displaying the banks site and if not then are there any standards
any one knows of that dictate what size of page to expect and so forth?

hi all, i am newbie with procedures. i have one error and since i know the procedure is right. can somebody take a look? http://rafb.net/p/X6WDX353.html thanks lot in advance

i have a query that returns a name and (id if found in another column, NULL if not).. is there a way i can get the count of NULLs and not NULLs in the same query ?
Mace[work], wich error you get when you try to include the bank page in the iframe ?

Anyone know if a machine runs out of ram running mysqld would it either drop/truncate or corrupt a table when trying to write to it?

shouldn’t, Foxdie, mysql should handle the situation with no database corruption
can someone help with this ? http://rafb.net/p/6E4Lhd12.html

in my first aproximation i am looking for the way to do a simple select in a procedure but in a table passed as parameter. I tried like this http://rafb.net/p/z4tY1S63.html, but i got errors i don’t understand why. i specify errors in the paste.
thanks in advance
can somebody give me a hand ?
i will give it back lol

hi. is there any chance to get some support in Swedish?

Hello

setting the length of int to 11 means 11 bits right?
or bytes

bytes

SELECT DISTINCT type FROM… or SELECT type FROM … GROUP BY type?

so itd be 2^11 is my max?

jbalint, its a display length

was that at me?

yes

so what would be my max number if int was 11?
99999999999?

you need to read the number formats docs

ok

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

ty
oh so it has nothing to do with min or max val
just formatting

yes

can i do insert-select and convert the column names using as?

I figured out the problem with the specific code we were using; there was a limit and the developer didn’t check on the very last page.

insert into table2 select from table1 firstname as fname

congrats, Voltaire

shit
select firstname as fname from table1

The interesting thing is that the use of a LIMIT causes the output to be different.
Just in terms of the order of the rows, acceptable since no sort was being applied in this instance.

extrange enought

Hi Iam using mysql5… i have a user named test and two database, test1 and test2, i have provided the test user r access alone for test1 and all on test2
is there any was i can list this user has which permission on the two database individually
can some one suggest me on this?

is there a way to have an exlusive not in? like SELECT word_id, word_text FROM phpbb_search_wordlist WHERE word_text IN ( ‘this’, ‘monkey’, ‘hello’, ‘mysql’, ‘bling’)
but that would return only records that are not in the table?
i.e., if the table would contain this, monkey and bling, it would return hello and mysql?

NOT IN?

that returns loads of rows

Hi Iam using mysql5… i have a user named test and two database, test1 and test2, i have provided the test user r access alone for test1 and all on test2
is there any was i can list this user has which permission on the two database individually

that returns all rows that don’t have this or monkey or hello or mysql or bling
or something similar

Hi, is there a way I can alter a value to increase by 1, as in `++’?

update table set col1 = col1 + 1 where stuff=234 ?

great, simple answer. thanks.

raar not in as Habbie told you but your excess returned rows it to be expected, you need to really decide what you want

Hi guys, would someone tell me how to escape a backslash in a where clause, please? ie DELETE FROM mydb.mytable WHERE `mycolumn`= “\”;

hal, “\”"

\\

Habbie, I am afraid that didn’t work

php5 hal ?

archivist, that didn’t work I;m afraid either
MarcosBL, yes, it is php5

mysql5 i mean

MarcosBL, no, it is mysql 4.1.20

could you paste the whole sentence somewhere ?

hmmm basically I want to know which values that I passed are not in the database

the data in the cell, it is just simply \

can some one suggest me how to get the details of the permission of a mysql user has over multiple database?

hal what did you actually use “\\”

archivist, haha – yes, that;s it

raar thats a different question

“\\” worked

hal, oh hal, i’m sorry
i misread

MarcosBL, and archivist thank you!

ah, is that possible at all?

I should have been able to work that out really (feeling foolish!)

raar if your set was in a tempory table a then ..
a not in b

SELECT a.* FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL;

ah