will that cause any problems?

Doesn’t matter

i might try copying the entire /var/lib/mysql over then

I have two tables, and I want to create an average on the result of an average on each table. This is possible with subqueries ?

‘default varchar(255) NOT NULL default ”, … something is missing there

I would hope not but the alternative is the admin changed the database

the tables do not have a relation to one another

Take a look at the definition of this table. Sounds like some column name is missing

thats what im thinking

Why not UNION?

i tried importing it to 5.0.x with the same problem

thx, i’ll go read up on it

Could be some non-printing character in the column name.

right now and then i’ll

hmm, no salle I think something more complex

ip_address — (average_mbps_for_ip) — percent (percent is based on AVG(ip_address.mbps) / AVG(link_rate.mbps) )

salle, im scp’ing over the entire file set now, i shut down mysql on the old machine first before i did
salle, once i copy it and start mysql on the new machine, everything should be identical?
the configs are a bit diffrent, but nothing huge.

Yes and then you can expect privileges to not work

okay
privileges arent too much of a concern
i skipped grant tables

If some of them rely on localhost name

and i’ll re-create the 2 users i need
the rest of the databases i migrated to 5.0.41
this one wont migrate cleanly
so i installed 4.0.x on the box in a seperate directory then chroot jailed it.

Definitely not with file copy

salle, with mysqldump it wouldnt work even
kept getting error after error
so one db runs on the old until my programmer can update his db code.

Do you have –quote-names among other options of mysqldump ?

here are the options i uses
used***
mysqldump -K -F –no-opts –single-transaction romdat_
think it’ll work if i –quote-names?
id love to use 5.0.41 for this machine as i have it configured perfectly, i dont like this parallel configuration if i can avoid it

morning

Try with –quote-names. It will help if you have some strange characters in table or column names

which is a possibility

Also take the dump with 5.0 mysqldump
I wonder why –no-opt

so dump the 4.0 with the 5.0 dump
didnt use –no-opt as it wouldnt let me

?

invalid option under the old 4.0
YABBA DABBA DO
salle, your line worked!
quoted took it perfectly!
on 5.0
I LOVE YOU
its 0400 here and ive been messing with this for 9 hours

hello, how hard is it to use mySQL from C ?

!man c api

see http://dev.mysql.com/doc/refman/5.0/en/c-api-function-overview.html

do you know where i can find simple examples ?

see the url

the URL is the API, i want shortest minimum example of doing a single query.

there should be some examples if you go lower down

Consider getting rid of strange characters in names
You can get various headaches with them in different places

i didnt code the sql or the site
im the net admin, the client hired an indian firm to do the coding
i must mention, however that they did a very nice job

how do you know ?

dc2447, Hey I solved my debian-sys-maint error I reset the password for user debian-sys-maint and then add the password to /etc/mysql/debian.cnf ( plain text file) and all errors are gone

what is the SQL syntax for LIMIT ?

!man select

!man LIMIT

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

Habhari za asubhwihi everyone

Cannot load /Library/Apache2/modules/libphp5.so into server: Library not loaded: /usr/local/mysql/lib/mysql/libmysqlclient.15.dylib\n Referenced from: /Library/Apache2/modules/libphp5.so\n Reason: image not found
can anyone help?
I dont know how to fix that

Could someone suggest amethod for checking if a field contains any characters within it. I wish to return all rows with text in a specific field.

!= ”

select sheep from farm where sheep != ” and sheep IS NOT NULL

is not null is not required

okay well its going to be NULL which is the default value.

use is not null

IS NOT NULL is more applicable in my case

and !=

null != ” will not return the row anyway

SNARFMONKIES
gah torrets

if the value is NULL .. “and != ”” would work? or do i need to do IS NOT NULL

!= ” should be sufficient, null will not be returned

ok

Is it possible to somehow “lock” tables so you cannot modify them before removing the locks
like read-only?

Do you have to pay for bit keeper to check out mysql development sources ?

!m LOCK TABLES toma^

Sorry – I have no idea what function you’re talking about! but try http://dev.mysql.com/tables toma^

!m toma^ LOCK TABLES

toma^ see http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

thanks

There is free client

Oh ! I missed that from the bitkeeper site
perhaps intentional
Is there a link I can follow please ?

http://dev.mysql.com/doc/refman/5.0/en/installing-source-tree.html
It says use http://www.bitmover.com/bk-client2.0.shar.

ah great, thanks

ahem. pardon, where do I point the browser? http://localhost/phpMyAdmin ?

thats assuming your webserver is running on port 80 and that phpMyAdmin is in the root htdocs directory and that PHP & MySQL are running

I did get localhost/phpmyadmin to load, but it shows a list of files. (got a response in #phpmyadmin, so…) thanks
I think it’s that root htdocs thing you mention.

/msg NickServ IDENTIFY nifyzab

time for anew password there

hehe

[[thufir]] if you just get a directory listing your webserver does not know how to handle .php files

SELECT “Werknemers”.”Initialen” AS “Werknemer”, “Werknemers”.”Achternaam”, DATE_FORMAT(“Werkdagen”.”Datum”, %d %b %Y), “Werktijd”.”Begin”, “Werktijd”.”Eind”, “Begin” – “Eind” FROM “Werkdagen”, “Werknemers”, “Werktijd” WHERE ( “Werkdagen”.”Werknemer” = “Werknemers”.”PersoonNummer” AND
“Werktijd”.”Werkdag” = “Werkdagen”.”Werkdag” ) ORDER BY “Datum” ASC

can the bkf client do tags ?

I added DATE_FORMAT(“Werkdagen”.”Datum”, %d %b %Y) and I got a syntax error, would someone know why?

!tell foo-nix about quotes

foo-nix Use ` around identifiers (database/table/column/alias names) and ‘ around strings and dates. php mysql web hosting does allow ” for strings, but ANSI standard uses ” for identifiers (which you can enable with ANSI QUOTES option) and dont quote
numbers

what to use as field type for a html text which I want to paste as description for a item ?

foo-nix: You think you’re funny?
foo-nix: I own you.

hi, im getting a “Got a packet bigger than ‘max_allowed_packet’ bytes” error message. I’m only doing little inserts how could this be possible

define little

1kb

I did not add the quotes, openoffice Base generated them.
I am not intending to offend or insult you, My appologies if I did.
*dis=was
omgwtfbbq?
I see, I am using SQL, there probably is a difference with MySQL, is that true?

foo-nix: No worries, hmm. I don’t see you too active in other areas of freenode (where I am). Otherwise, that may look like it’s me

ok foo, thank you. I will ask in #SQL

well as the factoid says set the ansi quotes option, then maybe it will be ok

foo-nix: I am though :p

foo-nix: hmmm.

hi
is it possible to order a table by a column in function of another similar column in another table ?

huh?
guten tag archivist

you didn’t understand my questin ?
question

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

maxagaz, no we did not

ok…

do you just need a join with an order by

I have two tables, the id of the first one are almost all in a foo column of the second. In this second table, the foo culumn has a specific order, so my quesion is, how to order the first table (order by its id column) in function of the foo column ?

What’d you call me?

foo:
is it more clear ?

maxagax you just need a join with an order by secondtable.foo
bar

could you please tell me a full syntax to make it ?

select fields from tablea where tablea.id=tableb.id order by tableb.foo

thanks a lot… I’ll try it right now…

hey, how do you connect not using a socket in mysql-query-browser?

to the ip addr or url

well, if i connect to localhost and try to change the port to say, 3307, it still connects through the socket

127.0.0.1 will be to the port not the socket

oh!.. i changed from “localhost” to 127.0.0.1 and it worked.. weird
thanks

is it possible to encrypt a string with NT hash (ntlm_auth) like it’s possible to do with Unix Crypt using ENCRYPT(’somestuff’)

!man enc

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

anyone know of the easiest way besides changing the startup script to enable general query log in gentoo..

thanks
seems like it’s not supported…

codestr0m, edit the my.cnf …
!man general query l

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

I was looking at that and don’t see an option in my.cnf for this.. from the manual “To enable the general query log, start mysqld with the –log[=file_name] or -l [file_name] option.”

just put in in the mysqld section
but general query log is deprecated afaik

I’d like to log all queries while I debug something. that’s my end goal.. sorry I’m not a mysql guy

well normally you dont want it permanently on as it grows quickly

yeah.. this isn’t production it’s entirely testing

What query would I run to search the table for duplicates of a field? Any rows which have `field` the same as another

dupes

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

O_O
Okay, that just confused the hell out of me

have a play, try it

ha, thanks archivist

hi
why i get this error
mysqladmin -u root create test
connect to server at ‘localhost’ failed
‘Access denied for user ‘root’@'localhost’ (using password: NO)’

dimgr use –password=

ok thanks it worked

np

can users and not root create databases..? all the configs i see are with root

yes
mysql.com/grant
-p is enough

why would I see “Copying to tmp table ” when doing a select on a mysql table?
innodb

because it’s doing a sort in a temporary table

aha!

do an explain on your query

which means it very slow i guess

likely it could be faster

sum(if(vl2,vl2,vl1)) as total
whats wrong?

what does the server hosting say, and what do you think you wanted

helo, i have a problem with an export of data from DB, collate tables is set to latin1_swedish_ci but i am using utf-8, how to solve it or how to export it correctly? i never know how this encoding works, any one can help what to do? thank you

alexwx, some reading… http://mysqldump.azundris.com/archives/60-Handling-character-sets.html

well
is not that function
it is another problem
thank you anyway
=D
other function with problem

hi all – I’ve got mysql up and running on my VPS. have confirmed it’s definitely running. I’m trying to access it remotely and have set up a user but I keep getting a “can’t connect to mysql server on ‘ip address’ (61)”. I thought it was the firewall that was stopping access but I’ve since shut
that down

archivist i cant see some information how to solve my problemm can you help ?

is there anythign I need to do on a fairly fresh install to make it accessible remotely?

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

and wear a condom

?
cheers archivist, i’ll give that a go
any idea where ubuntu sticks my.cnf?

welly, i guess /etc/mysql/

ah so it does

you can try locate my.cnf

just couldn’t see it in the directory listinng

can anyone tell me what does “–” do?

or find / -iname “my.cnf”
=]
comment

dex, comment

aha, thanks

dex;:;:;’**’*’ comment

/* this is a comment too, right? */

no

in mysql not?

yes
an in-line comment
or a multiple line comment

confused

sweet

good day

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

/* */ ()yes ()no
youre right… =]

wrong

good

up

can i ask for optimal setting my.cnf on 2x dual core xeon with 4gb ram ( i mean key_buffer, max_allow_packed …. etc)

well need go
thank you all

archivist but i am using phpmyadmin, i have not an access to console, can i do that somehow?
i have a problem with an export of data from DB, collate tables is set to latin1_swedish_ci but i am using utf-8, how to solve it or how to export it correctly? i never know how this encoding works, any one can help what to do? thank you

depends on your setup
!tell mrak about tuning

mrak http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/

thx

I avoid charset issues if I can