archivist what kind of backdoor ? fajita has a few

hehe

thank you so much..
thats all
hosts = localhost

the one where pctony replies

all of does files has that now..

Need a way for us to speak for the bot

archivist oh a few more people have SAY nowadays… it's all going to pot!

hehe

when i try to INSET some data into a table it gives me a error http://pastebin.com/d4968cb71

http://dpaste.com/15687/

Entvex, it's INSERT
not INSET?

ups
Thanks johnny

not english i supose

Can't connect to local MySQL server through socket '
what is wrong

snuki_mac_de, mysql_install_db ?
did you run that?

Did you start the server?

have to run that before starting it right? or do i have it backwards?

load data file 'login.txt' into table login; its chomping off the first letter in the first column. why is that?

I have but its not working
do i need to be sudo "root"

yes

its the same

the same?
need some errors..

how do i start mysql hosting "mysql start

that depends on the distro
i usually just use /etc/init.d/mysql start

BlkPoohba what's in the file ?

lad357,justsumotherstuff,username,ipaddy
the l gets chomped
i have fields terminated by ',' and it still gets chomped

!man load

see http://dev.mysql.com/doc/refman/5.0/en/load-data.html

"The character set indicated by the character_set_database system variable is used to interpret the information in the file"
that may be what's happening
also, it's INFILE, not FILE

oh. how do i see which charset its using?

erm.. by looking at it with a text editor ?
oh you mean mysql
!man show collation

see http://dev.mysql.com/doc/refman/5.0/en/show-collation.html

I think, anyway

show create table
it says latin1 but all of my tables are latin1 how do i know which it should be?

that's not what the manual says - it is determined by the *system* collation
not any specific database or table one

is there a way to calculate the sum or difference of a derived value that's been stored in an aliased column?

load data infile 'login.csv' into table login fields terminated by ','; still cutting off first char.

and now it's a CSV - saved by a MS product ?

credits; ?

it is, saved to msdos csv. i've saved it as a txt and a xls also
i have opened it in gvim and notepad to make sure it was just a text file with no excel programming in it

well, how about running it through dos2unix as well ?

i have it saved as tab delimited and coma separated

hothing is working, how do i install mysql_install_db
how to i run the mysql_install_db

Wow! Euro 2008 Qualifier - Interesting end to the match between Denmark .vs. Sweden
http://youtube.com/watch?v=wD9o0ObKvn0

ran it thru but still taking off the first char

I wonder if that fan will need a personal guard or police protection after that stupid move.

can anyone tell me the implications of using latin_swedish_1 as the default collation for a primarily english speaking database host (but will be universalized later) ?
is it preferable to use UTF8-bin or UTF8-general ?

raphael_burnes the major implication is that it won't matter much, as you can set the collation for any database or table separately
but perhaps compare the two ? the point is that all *english* characters are in there, and most English doesn't use the swedish-only characters

thanks, what exactly does collation determine? just the alphabet sort order?

yers
but *in any operation*, including inserts and comparisons and such

ah

WHERE alpha='yes' AND date BETWEEN 2007 and 2000 AND status='on'

what's wrong is that AND always has precedence

interesting

and "200 AND status" makes no sense to mysql

so put () around the between part?

!man between

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

yes
the example from there

SELECT 1 BETWEEN 2 AND 3;

dates are incomplete

they dont give examples of how to use it in a more complex query
the dats are just numbers
not a date field
so do I need to use () around the BETWEEN part

well, yes, they are

it doesnt say in the docs

and yes, you do
it should say that in the part about operator precedence
if I'm totally wrong, it would not be a bad idea to listen to archivist , since he's usually right

who meeee?
nevaaaar

cool, thanks for your help!

but thinking logically (about the logic of it), I would say the second AND also screws it up

thin and use of a reserved word

huh?

status, probably

date

damn, yes

oh
thanks

thin next time paste the full real error message

does using the utf-8 collation take up more size ?

Hello. Im doing some date-stuff… the db-field has a datetime format, and the script has a date-format. How can I get all datetime that is the same as date-format?

is none yet server hosting down for a few minutes. Figured since I was gonna do it wrong anyway and didnt see a more complex example in the docs, should ask ahead of time

thin just that the bot in here spots that error from the message

kool, in a minute

is there any config file where I can set the root password?

no
thank god, no

… WHERE make_this_field_to_dateformat(datetime_format_field) = '$date_format_var' ?

isn't there ?
like .mysqlrc

there are no passwords outside the encrypted forms in the mysql db
nor should there be

does mysql not compensate for missing zero in date '2007-7-01' ?
no error but nothing found

thin, it shouldn't do that.. even if it does

what happened to the -max version of mysql? i dont see it anymore under teh downloads section for 5.0.
there used to be prepackaged rpms of it.

!maxdb

Blush, an unexpected wench error, manual section !maxdb not found

pfffrt

!man maxdb

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

MAX was integrated into the main line, iirc

no, not maxdb
there was a -server package
and then a -server-max package.

anyone know of any 'less-bloated than mysql admin tools'-tools that support innodb+foreign keys/etc?

umm so I have to reinstall mysql hosting to set the password?

here's a snip from teh download page…
'The Max version includes additional features that have not been exhaustively tested or are not required for general usage. When these features have matured and proven to be stable, they will be incorporated into future releases of the Standard binaries. The Max version also, for most platforms,
contains MySQL Cluster storage node, management server and mysqld/ndb enabling programs.'

(preferrably for osx… but i know that's a longshot)

Shaun2222 maxdb *is* the -max version

adaptr, no, it's not

bleh, don't contradict me!

!m Skiessi resett

Shaun2222, the -max was integrated into the standard package, the website needs to be updated

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

can anyone recommend anything besides official mysql tools, phpmyadmin and heidisql?

what else are you looking for?

hi there, can anyone tell me how I can free up harddrive space in /var/lib/mysql when I dropped a huge database and somehow didnt gain any space? I am using innodb(if that makes a difference)

phahn, innodb does not reduce the file size
mysqldump and recreate the tablespace and import

wow, so dump all databases??
and recreate?

a horrible workaround

that is taking ages on that server, its about 100gb of dbs

it will reuse the internal freespace

what u mean with tablespace btw?

so no need normally

it just doesn't resize the files.?

nope
i dropped a huge database and didnt gain a single bit

it will reuse the space as you add more data

hmm, ok
but there is no way to get it to resize at least some of the files? it is almost filling up my /var

not of the actual table space, you may be able to free up space from the binary logs or similar

sounds like your data sizes mean its time for more disk anyway

you might consider using innodb_file_per_table going forward

how can i said.. where username != regexp '(some expresion'

how can i clean those up?

show master logs;
purge master logs before 'date';

ok, theres a real lot of logs, will drop all those
the database is consistent and just freshly backed up, so i should be fine

is there any way to skip columns in the source file when doing a LOAD DATA LOCAL ?

Yes

thanks for the help, the db is fine again now 40 gb of logs dropped

moin

.quit

moin moin

I'm upgrading mysql from 3.23 to 4.1, after I upgrade, can I just move the old /data folder into the new mysql directory?
its just a couple of simple databases, nothing out of the ordinary

I tried that, and it broke. You will need to reimport

whats the best way to do that?
sqldump?

Actually, It might have broke in the other direction. 5.x to 4.x

qbrix, best hosting to read the upgrading docs

the upgrade docs don't have very much information

4

so

charset issues and passwords

Yeah password hashing changed

i'm reevaluating my depth query, it seems sorta slow
altho then again, i'm not sure how fast it can be based on the structure
can somebody look it over to see what i can do to optimize it, and perhaps enable the sorting i need?
uggh.. those rafb pastes expire too quickly
http://rafb.net/p/RaOnun51.html
there it is
it does work atm tho
just not for the sorting that i was hoping to get

Hi, I need some help getting the db to recognise the difference between upper and lowercase, can someone help. pleeez?

binary

Can you do functions within the join statement like this??? "SELECT * FROM settings_evdo se, evdo_ap e LEFT JOIN ON CONCAT(e.sysid, '_', e.sn, '_', e.fms_frm, '_', e.ohm) = e.network_element"

perljunkie, try it, and you'll see

error right syntax to use near 'ON CONCAT(e.sysid, '_', e.sn, '_', e.fms_frm, '_', e.ohm) = e.network_element' at line 4

but you dont really want to do that

i've tried to change the field to binary in administrator, but it gives me an error message
does binary need a binary(length_here) ?
or should i just be ticking binary in flags?

binary('foobar')

so if during my upgrade, everything blows up, and given that I copied my old mysql directory to a tmp site, and I just copy that old directory back over and re-run mysqld to get things back to status qup?
quo

you should export too

what is the max length for binary?

qbrix, allways do a mysqldump

i have '-' in my load data. would that cause a problem?
what about '.' ?

ya, I'll do a backup first, but I'm also making a copy of the original mysql directory
theoretically I can just move it back and start mysqld with no problems, right?

no

*sigh*

perljunkie, SELECT * FROM settings_evdo se LEFT JOIN evdo_ap e ON CONCAT(e.sysid, '_', e.sn, '_', e.fms_frm, '_', e.ohm) = e.network_element

what would cause a break doing that?
is it that a gauranteed crash?

you need to run the upgrade script to do that

to go back to my old version?
because, I think that is what the documentation says

whats the syntax to delete all the rows in a table?

truncate

hi all

will truncate reset auto_increment to 0?

have pb with on delete cascade

!man truncate

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

the fourth to the bottom paragraph on this link says that it should be possible to bring back the old version by swapping directories:
http://dev.mysql.com/doc/refman/4.1/en/upgrade.html

i've 2 tables n-m relations
and i want when i delete one rows from a first table, the relation will be deleted

the_wench, thank you

i wahooooont that all data wich have relation with row deleted will be deleted

: You are soooooo coool. Thanks so much

i want that all data witch have relation with row deleted will be deleted
where should i put the ON DELETE CASCADE
??

heh there is no documentation whatosever on how to upgrade
nothing detailed at least
only precautions

qbrix, note you are going beween versions with no charset support(3.23) and charset support(4.1) and changes in the mysql db

hi, i've changed my field type to binary. will this accept alphabetic characters?

qbrix, is http://dev.mysql.com/doc/refman/4.1/en/upgrade.html confusing?

it doesn't give actual instructions
just setup and precautions
in other words, I'd like to see explicit instructions

each case is different

I think I found something good
http://marcus-christie.blogspot.com/2006/12/upgrading-mysql-323-to-50.html
I understand each situation can be different, but there should be an example for a default setup

official documentation

I have a VERY VERY simple setup using mysql

but you said you were going to 4.1
not 5

well, apparently, I gotta go from 3.23 to 4.0 then to 4.1 then to 5

There isn't step by step guide in the official docs?

not at all
It doesn't even try to generalize the steps

The steps are to untar the new version…

I know that, but it doesn't even mention that
at all

qbrix, you did not look far
http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-3-23.html

again, that covers mainly precautionary/setup steps

http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-4-0.html

again, that covers mainly precautionary/setup steps

the key is understanding, not being wet nursed

heh, then don't say that its in the docs
its such a cop out answer

how do I make an int column pad the results of a query ??

zerofill?

that sounds like it

Someone is actually using zerofill!

is that bad?
I just turned it on.. let's see how it works

perljunkie, it's just the kind thing i normally do in application code
i guess

This is required to be done in the join

aha.. thus you actually need it in the query
it must exist just for you then

do inserts cause a table lock in innodb?

I think you're right. Good MySQL developers!

true..

woot zerofill rules

heh first time Ive seen zerofill used

(offtopic) can someone here explain what M-notation is? it is mentioned in the CAT man page, for the cat option -v, for unprintable characters.

Jimi, that doesn't make any sense
the man page that is
the only thing i know of is ^M
that is even close to what it might be talking about
new lines from an old mac
i'm prolly completely wrong, but it's the only thing i can think of

johnny, type:
cat –help
and you see it also

same thing
it doesn't make sense
and i just told you what i thought of it
the only way to test it, is to get a text file from an old mac

but is it my question that doesn't make sense, or the MAN page?

the man page
and the –help
it sounds like either an old mac thing, or an old timer *nix thing
so.. either find old mac data, or somebody who uses nix back in the 80s

mac? this is not a mac-file. it is a simple text file with scandinavian characters in it, encoded with latin1. the cat command can't display it, so I wanted to know if there is a explaination of the m-notation anywhere, so I can check that the characters are correct

hello friends, if i have a .sql file generated from a phpmyadmin dump, how do i restore it on another server (phpmyadmin gives memory constraint issues on the other server)?

aha.. you didn't say that..

sorry… *ashamed*

so what happens if you try it with -v ? does it work?

or is there some other command like cat, but that can display latin1 fully? (åäö characters)

AgentQ77, best to not use phpmyadmin for that sort of job mysql dbname blah.sql

many thanks, was just reading this: http://dev.mysql.com/doc/refman/5.0/en/batch-commands.html

å ä ö becomes M-e M-d M-v

Jimi, i'm afraid you're going to hvae to go somewhere else
i have no idea

ok, well it was worth a shot anyway. thanks for the effort

Jimy use a hexdump program much easier

aha… good call

ok, any one installed on linux by default?

uhmm… linux?

ubuntu

Ubuntu is an ancient African word meaning 'I can't configure Debian'

there are far too many linuxes
you're gonna have to ask ubuntu peeps

oh, sorry, wrong computer. actually I have no idea what this server is running

as i don't recall any that come with coreutils .. which is one of the base packages of most distros
it's google timee for you

I just thought there was a build in system command, like CAT, MAN, LS… but for hex editing/viewing
hehe, yeah

those aren't built in

alo
what are you actually trying to do?

ok, well I have never installed a program on any linux ever, so i think i will try a different aproach… I will just assume that the file encoding is correct, and try an import and see what happens

don't say what you think you need to use to do what you want to do, just explain what you're doing, from a laymans terms perspective… what is your data, what do you want from it… are you storing baseball cards? job applications? medical reports? do you want the batting average of a team?
the average salary? the average dosage?

hey
lets say we are storing money into a shared bank.
there are multiple shared banks and many many users
We want to keep track of who put the money in

sounds like a textbook example

exactly

is it?

mostly yes
66% of it is

ok well the db design for this is pretty simple.

I have to use a join right?

yes, you do.

I was storing ids into a text and was going to explode them on a ,

make an id on the bank_table

is archivist still about?

nonononono

lol

and also make an id in the users_table, pointing to the bank_id

it ok
i just wanted to thank u for ur help earlier

don't store id's as text. Use int.

u said i cudnt index my stuff peoperly cos i used CONCAT in my queries

well it's an array of ids ^^

ive changed the system now so it just uses IDs

I bet its faster

it seems to work well so far, and ive still got explains to do so i can add some more indexes

well, I did a small test, and all worked out. I was just worried since the characters looked very strange in that M-notation, I thought the data was currupt

thumbs i have all ids as int

i also removed date_format(date,'%D %M %Y') AS realdate from my queries and am modifying date format with just php

thumbs but I was keeping track which ids belonged to a particular row by keeping a textfield and putting users ids seperated with ,'s
I didn't actually do this, (thank god), but that was the plan

postfix/postfix-script: warning: not owned by root: removed that damm virual for 3 times still getting this.. wat should i do?

until I thought about the situation where 2 people might modify this textfield at the same time

any1 got a good page for that?

`mac`, this is not a good place for postfix

thus they could overwrite eachother if one selects the other selects first one writes second one writes

sorry you right i thought i was in there lol

second user write would overwrite the array leaving out the first one's id
So I have users belonging to multiple banks and different amounts in each bank, but each bank keeps seperate pieces of money (for example id=1 might have 5$ so 5 seperate 1$ bills) with the id of the user. The users keep id of how much they have stored into a particular bank.

Is either memcaches or the mysql query cache any faster then the other ?
memcached

which collation should i be using? For some reason mine is defaulted at latin1_swedish_ci

newbie question, I can't seem to find my.conf, how can I find out which conf file mysql is using?

jtm thats normal

i shouldn't be using unicode?
utf8_unicode_ci ?

up to you

what's the difference?
what type of text is allowed?

best to read up on charactersets than a one liner in irc

using *nix? look under /etc
bah
the issue is not as much what type of text that is allowed, but more an issue of what file/character encodings does the rest of the system use? webserver? file imports?

i speak english and most likely i should be using latin1_swedish_ci All users will speak english also.

if needed
our development machines use utf8, and our test server uses latin1 (we might change this in the future). imports works fine on both, as long as I check the file encoding and convert
(switch place of those rows… "if needed" should be after the "our development…" row)

weird ^^ im using windows, so that might matter

we use windows to, on development machines

hmm
I don't think the coding is really important here, everything will be done in english.
encoding*
Basic storage of information data.
descriptions and such

my recommendation would actually be utf8… but that is mostly based on what a colleage of mine has said. don't remember the arguments right now though…

i joined a channel here for a second, and it said right at the top to use utf8
i think it was the innodb channel
but it was empty

from spellweb.com:
859,000 votes for latin1
17,400,000 votes for utf-8
you should always spellweb make those tought decisions

lol

"let"

what's the difference between LIKE 'apple' and = 'apple' ?

nothing i guess
LIKE may slower than = but dunno

under the hood likely nothing

hey guys is there a way to create a database that one user sees, but others dont?

i see…

so say if user1 creates a database, user2 cant see it

easy

yes

don't give read permissions

plesk do it for e
me

and omit the show databases priviledge

do the user
to
so whats the basic command to add a user, so he wont see other databases

but doesn't that stop that user from seeing *all* databases?

also guys, does anyone know why i get this when trying to run mysql in a chroot, am i still missing/in wrong folder lib's?
2 [ERROR] Fatal error: Can't change to run as user 'mysql'

does yoru mysql user exist inside the chroot?

yes, meaning copying passwd to the correct folder right?

to make a column unique is it UNI('Username') or put in the column entry like this `Username` varchar(48) NOT NULL UNI default ''
or UNIQUE KEY('Username') ?
or is it.. NIQUE KEY username (username) ?
ah yeh it's the last one i see now
nm

anyone know how to create a mysql.host if it doesnt exist anymore

I've got a bad feeling about my MySQL database.

randoman, stop mysqld, move all data from the mysql data dir and my.cnf from/etc to a safe place and run mysql_install_db, replace all but the mysql subdir of the data dir (all user info lost), restart mysqld

Last time I came here people told me I did my DBs all wrong. Now I've hit another possibly design-influenced issue.

anyone know how to re init
ok
thanks

Basically, I have three types of people that can join a class - students, teachers, and substitutes/helpers. I have three relational databases set up for each - class_student_r, class_teacher_r, and class_substitute_r. Each has two fields, one with the class ID and the other with a user ID. I
need to gather all IDs from these DBs and make sure someone didn't register for the same class in two positons (teacher and student, fo
r example).

your the ma
man thanks
worked like a charm

yes, archivist is great.

Sugar - I need to run. If anyone has any comments, leave them here and I'll pick them up when I get back. Thanks

tis home time

does it matter for the class table what type of candidate they are?
i.e. could you merge all three tables.

RobbieAB, *duh*

just an idea…

lol, yeah I must say, everyone is very helpfull here on freenode

especially in this channel, I find.

I take it you avoid ##c

I dont know c
so no need to
:P
i need to learn it though

that channel is fun. But it can be quite vicious.

nice ill check ito ut

pussy
oh, wait…

I like C. It is refreshing to find a channel where I'm allowed to be mean to thouse stupider then me.
Even if such folk are few and far between…

lol

Anyone that can program C is smarter then most

what does 3[x]++ do?

yeah, but most have the sense to avoid ##C

hey mysql_install_db is giving me this
Installation of system tables failed!

perhaps they're already installed

k

wats up thumbs:

`mac`: avoiding you.

wise man

Where is chad?

anyone on freebsd

I see this fellow is still asking off-topic questions in every channel.
good choice

pw group add -g 5801 vmail
group name required
thats why i m asking
off toping mof top i m just asking

what year of wine, specifically?

red

I prefer red too. Chardonnay, 1982-86

I try to avoid rust under the screw cap.

Wouldn't the year depend on the winery?

Is this an ok channel to ask an SQL question? I am using mysql and I don't see a generic sql channel…

The year 1981 is very zen
ask

this is the place

"I have a question", Don't ask: "Is anyone around?" or "Can anyone help?". Just Ask The Question

I don't know, but the 1982 bottles I found had no rust on them.

Is there any benefit in indexing a time column (datetime, timestamp etc). It's cardinality will be the same as it's size, pretty much. Entropy will be high.

I guess it depends on how you store them.

seekwill and ye shall find.

Do you search or sort on them? Not sure what you mean by cardinality will be the same as the size…
More specificially "Size"

Yes I will be searching on that column.
cardinality same as table size (count *)

That's a good thing for an index

Good. Thanks.
I guess I was thinking that if the cardinality was high MySQL was pretty much going to have to do a complete table traversal to find a particular value. I was overlooking the fact that the index will be pre-sorted, so all kinds of search algorithms can be used and will speed things up.
uI guess I was thinking that if the cardinality was high MySQL was pretty much going to have to do a complete table traversal to find a particular value. I was overlooking the fact that the index will be pre-sorted, so all kinds of search algorithms can be used and will speed things
up./u

I thought the same thing when I first started learning MySQL

does anyone know why this command set password for user = password('pass');
Can't find any matching row in the user table
isnt that correct?

!man set pass

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

'user'@'localhost'

DuBois — "Indexes work best for columns that have a high cardinality relative to the number of rows in the table (that is, columns that have many unique values and few duplicates)'

thankyou

ahh yoru the main
user@localhost did it
gosh i hate when tutroials give you the wrong command

set password for paul = password('mysecretpassword');

i guess maybe thats there way of getting you to fix the error, so you learn it ;P

RobbieAB, I'll try that idea out, thanks. TTY "awll" L

What tutorial?

http://www.debian-administration.org/articles/39

hi guys i'm trying to add a user and i'm using INSERT INTO user (User,Password) VALUES ('mythtv',password('mythtv')); then do GRANT ALL PRIViLeGES on mythconverg.* TO mythtv then flush privleges but i still can't login via mysql -umythtv -pmythtv
ideas?

put a space between -p and the password

threnody, nope.

Hi there good evening

leave off the password, include the -p, and it should prompt for a password.

i did
i tried that

Can some one help me whit a MySQL Server Config Problem?

no go too

can you log onto mysqld as root, and look in the mysql admin db and see if your entries are there?

yep i see it
| | mythtv | *CC8F35F587CA5A556B4132C2407E556D92172FFC | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N
| N | | | | | 0 | 0 | 0 | 0 |
| % | mythtv | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y

sorry, I'm an idiot. Your mysql user entry has to include host, like 'mythtv'@'localhost'

| Y | | | | | 0 | 0 | 0 | 0 |
+——+——–+——————————————-+———
whoa
oh

the second one is the good one, except you want a password

daum, use pastebin

Julian|Work, yep didn't mean to do that

No problem

in the future, you might use the create user syntax: http://dev.mysql.com/doc/refman/5.0/en/create-user.html

Can some one help me whit a MySQL Server Config Problem?

ask

"I have a question", Don't ask: "Is anyone around?" or "Can anyone help?". Just Ask The Question

OK, I use WAMP to install a Windows MYSQL, Apache y PHP, but i can connect me to the MySQL localhost server fom a Program that use Native access to mySQL
I know that its something hbaut the server config, but i dont know what file/line change

http://dev.mysql.com/doc/refman/5.0/en/windows-post-installation.html

nop lets me check
sorry but what exacly should i undstarnd from thah page?

In a create table syntax, I want to have a blob column which will have an index on the first 10 characters.. How do I specify this? CREATE TABLE test (id integer, data blob INDEX (10)) ?

Sorry guys but i read the article and dont help me, I have accounts created in the Server and can see the tables an acces the server using for example navicat, But still i can´t conecme to the MySQL Server using native acces froma a program
I can make the conexion using ODBC but not Native
So why i can make an ODBC Connexion to my MySQL Server on localhost but can,t make a Native Conexion to the same server?
Thats using WAMP on Windoes. But when i USE WinLAMP i can make the native conexion? so I think its a conf problem but what file/line has to eddit

In a create table syntax, I want to have a blob column which will have an index on the first 10 characters.. How do I specify this? CREATE TABLE test (id integer, data blob INDEX (10)) ?

So people can some one giveme any hint?

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

Thenody, whta abaut my?

I accidentaly deleted both my root and anonyomous users and now I can't do anything

Compare the difference between winlamp and wamp. You know about mysql access. You just don't know about wamp or winlamp configuration. #mysql might not be the best place ask about those apps.

like 'CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));'

whats a reliable way to get the last insert id in a web cluster environment?

how hard would it be to send a real time output from tail -f file to mysql?

Im not talking abaut the app Im talking abaut the Server config

Trivial.
Ditto.

Xgc i thought so…

whats the my.ini line i must change for can allow the server to recive Native connection

tail -f | mysql …

Well Bye guys

Xgc hm…

It better be in a reasonable form.

Xgc the thing is that i have never tried to add anything to mysql directly from shell

I might use perl to regex something meaningful and then pipe to mysql…

If you want to store the output as data, not execute it as SQL statements, that's different.

Xgc it might be trivial but i really can't imagine that even
threnody hm…
Xgc yes, i'm basically trying to log into mysql instead of text file

You haven't really specified what you want to happen or what the data looks like.

it's just a log
a few lines of text per minute
that appear in this log file
it would be so useful to be able to have it inside mysql instead and dig tru that later

you want to store that, or use part of it in a sql statement?

i only need to store those lines

Can someone please help me?

in a simplest way

why real time? you can grep through the log file and mysql -e what you want as an insert
cron it regularly

All you need is a client that inserts into text or varchar columns or converts the output into some trivial CSV format that you import from time to time.

oh hm… not bad idea either
now i see the light finally
many thanks Xgc and threnody

huh

That was to any Robb that cares to listen.

hi

hi!

Hi, guys, can somebody explain to me the difference between InnoDB and MyISAM architecture?

Just be careful of the form, since if you build the insert via string manipulation you could easily run into characters that cause trouble, like single quotes, double quotes, etc..

Please read "Security" section of the manual to find out how to run mysqld as root!
when trying to run it in chroot
?
doesnt it run as run then switch to user
i know passwd may help

Xgc hm yes.. thank you for pointing this out

What os?

usual start is 'mysqld_safe –user=mysql &'

hi all

max-levin innodb is faster but less safe to use

so chroot /chrootdir /usr/sbin/mysqld_safe –user=mysql &

guys what's could be wrong with these statements? http://deadbeefbabe.org/paste/5635?

its ubuntu also

yea I know that,so I am here for a details

to make a chroot jail requires that you put all the proper binaries and libraries where they can be reached in the jail. not trivial.

I got an error message that says something's wrong on line 1

Show the exact error message.

yes
I did this

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; create table hhg_latest_trends ( id int not null auto_increment primary key' at line 1

i just copied passwd/group/shadow
still doing it

I'm running the query from a CMS engine

The problem has nothing to do with the posted sql. It's prior to it in the same file. I just ran it wothout any trouble. Also you might be using a version of mysql that doesn't support something.

max-levin http://www.google.com/search?hl=en&q=innodb+versus+myisam&btnG=Search
being a google proxy seems fun

ok
thanks

I suspect the client you're using only handles single statemnts.
You can't just submit that to the server as one SQL statement.

I see
let me do this one by one

You can process that with a client like the mysql command line client.

max-levin: I did some research after I posted this thread. Apparently MyISAM is faster than InnoDB. The only advantage InnoDB has over MyISAM is that it supports row locking, while MyISAM only supports table locking. Therefore, if lots of reads and writes are constantly being done to a very
large table, it eliminates the constant database errors that using a MyISAM table would cause from the overload. InnoDB would therefore be a tad more rel

mysql … dbname file.sql
That's what I used to test it.

you were right, it ran line by line
thanks a lot

You're welcome.

today was the original publishing date of your namesake: http://www.todayinliterature.com/today.asp?Search_Date=7/31/2007

threnody, looking

You can't use FULLTEXT indexing on an InnoDB table though, right?

threnody, ah!
Actually Aoirthoir is Gaeilge and is pronounced Ur How ur

has it an Anglicized form?
other than Arthur?

threnody, that would probably be the anglicized form, but the transliteration is satirist

I ask as my last name is McArthur

but not like aoirfheagan which is the cast of the satists
ah
threnody, yours is likely from Arthur which means either the bear, or apple..depending on who you believe…among other claims

So if I have mysql installed via a Suse bundled rpm, and I have several databases configured within the mysql… If I upgrade the rpm when a new version of mysql comes out… will the databases still be in place?

however so far as I know im the only one ever named aoirthoir

yes. and you'll see them, unless something changes the datadir= setting.

Ok thanks threnody

the Irish? making unsupported claims? I won't hear of it!

lol threnody

begorra

threnody, one thing I learned way back when I started studying druicic revivalism, is that the Irish had saying "our history is that as it should have been"
;D

i am having problems doing a left join, i need every row in the left table, but i need only certain information from the right table, so its excluding my NULL rows because it doesn't match the where syntax, SQL Statement following
SELECT ts.name, te.hours FROM timecard_entries te LEFT JOIN timecard_sections ts ON ts.name = te.section_name WHERE (te.user_email = 'cruck@data-tactics.com' and te.day = '2007-07-31');
any ideas?
and please don't spam my email i so stupidly just pasted
i figure i need to add something to the where clause to allow the null right records but i don't have the foggiest of ideas how to do that

s/WHERE/AND/

for a replicating mysql server, do I just need to set the CHANGE MASTER stuff every time? it doesn't seem to pick it up from my.cnf
or does it store the info somewhere like svn?

Xgc, but shouldn't i keep the row selection in the where clause?

If you want that to be part of the JOIN criteria, it needs to be ni the ON clause.
Not if the row selection involevs the RIGHT table of a LEFT JOIN.

Xgc, still only returned one row for the one row on the right side

It's really join criteria if you want nulls to make it into the result set.
Ok. Maybe I didn't hear the entire requirement. Explain it clearly.

SELECT ts.type, ts.name, te.hours FROM timecard_entries te LEFT JOIN timecard_sections ts ON ts.name = te.section_name AND te.user_email = 'dummy@yahoo.com' and te.day = '2007-07-31'
[alpha,bravo,charlie,delta]

I just reread your question. My answer stands. If that's not correct, you've left something out of the description.

Xgc, well i suck at describing things so i figure its my description
i want every row in the left table, and only rows that match certain criteria to show up on the right, and null everything else.

You describe a problem where you want ALL from the left table and some conditional rows from the right or null.

yes

The answer is as I describe.
So, if you still don't like the result, show a real example.

SELECT ts.name, te.hours FROM timecard_entries te LEFT JOIN timecard_sections ts ON ts.name = te.section_name WHERE (te.user_email = 'sammy@yahoo.com' and te.day = '2007-07-31');
ok… hmm… example

Post (pastebin) create table statements and just enough insert statements to produce the behavior.
If you wish to post a full mysqldump, that's fine as well. Whatever is easiest for you.

table one (1 column varchar) [alpha,bravo,charlie,delta], table two (3 columns, name (fks with table one), day date, hours int)

Stop.
Reread what I want you to provide.

you want a table dump?

Do you know what a CREATE statement is?
Do you know what a CREATE TABLE statement is?

Xgc, yes

Post the related tables for this problem.
Not in the channel. Use the pastebin.
Also post the test data in the form of executable INSERT statements.

ok

Once you have that we can compare results using the same data.
This is the only way I'll be able to understand your problem, since your SQL description can't cause the problem you tried to describe.

Growl… i just created two tables in a test db, and setup the same problem and it worked properly…
bGrowl… i just created two tables in a test db, and setup the same problem and it worked properly… /b

If you need to (and can) export (dump) the actual database, do it.

… i got it working…
yay
thanks

You're welcome.

btw, i am not a complete 'tard, just annoyed at what i thought, wrongly, was an issue with something out of my control

Well, the WHERE clause needed to be removed in your example. That certainly was a problem.

and that was mostly it
i reversed the tables from left to right, and then you told me to replace the where, and it still didn't work because i forgot i flipfloped the tables

It had to be. But it wasn't going to help you for me to keep saying (that's the answer) and you to say (no it doesn't work).

hello
poll_id , number_of_votes, acumulated_puntuation
and some users votes in a poll
number_of_votes = number_of_votes +1

UPDATE … SET field=field+1 …;

and acumulated_puntuation =acumulated_puntuation + user_vote_puntuarion
ok
thanks
gonna try that one
but do I need to do a query first to know the value of 'field' ??

No.

ok
thanks

You're welcome.

hi guys
why appear it, when y try create te my table? :
#1005 - Can't create table './creatjb0_framor/occabecera.frm' (errno: 150)

is there any good way to allow reads from the local (slave) mysql server but force all writes to the remote master?

your application needs to be aware of replication

Xgc, going back to my left join thing, can i have a left join on a date range instead of a table, (i.e. simulate a table with one row for every day between two dates?)

You would have to generate/derive the range of dates if you don't have a concrete table that provides the data.

how?

with mysql does the database as a whole slow down over time if i have a large amount of tables? (like maybe 25 - 35? or so?)

You can use this type of technique: http://rafb.net/p/x2ECty35.html
No. The behavior is much more complex and has very little to do with number of tables. In fact, if you're asking about number of tables, you might be heading down the wrong path. Your design approach shouldn't involve dynamically generating tables.

well no i'm not interested in dynamically generating tables (I agree that's a silly idea)

Xgc, oh wow… i guess it may just be more feasible to build a table with a listing of days

however i'm currently looking at making a quite large application with mysql + ruby on rails
and i'm just trying to think of the negative aspects of making it all in one big database
i believe it would make it easier to backup and it would seem a lot more tidy for me to manage but yeah

Why did you ask the question? I'm curious?
How many tables (roughly) are you anticipating?
10, 100, 1000?
Do you understand the concepts of normalization and are you planning on using them?

well actually only about 50 or so at max
i'm building a large web-based application for the intranet system for a client, we've decided to go mysql + ruby on rails and since it's quite a large application i guess i wasn't too sure about if it was a good idea to have it as one big application or have it as lots of mini-databases and
applications

*nod* No problems there. The only problems/limitations you should expect are probably OS/File system related.

okay thanks for your advice!

One application can span several databases, if you really wanted to do that. I doubt you would need that, but it's certainly an option.
You're welcome.

well i think whilst it woudl be easier to maintain all the data it's easier to back it all up in one go
actually Xgc would you know about any kind of programs for automatically backing up mysql?

Not really. You might consider using replication.

okay i'll look into it

what is the neq or != in mysql

is sql standard

if colA = NULL colB = "5" and colC = "8" and colD = NULL colE= NULL, how could i generate a column that contains the number of non-null entries for columns A through E? (in this case, 2)

how can i change the pass? Password query failed: Not connected to database

for NULL it is colA IS NULL or colA IS NOT NULL

do you mean within the row? it sounds like you're talking about a computed column?

thoughtful yes, per row
within the row
not "column"
like "Item=SONY VCR EbayPrice: 5.08 CircuitCity = 8.05 SamsClub = NULL" then i would like a column that says we have 2 participating data points out of the three

That suggests a normalization issue.

yep, you're talking about a computed column, i think (ie a column that computes values from other columns), and i don't think mysql supports computed columns?

Oh, should i just do that in PERL DBI then?
i mean, its so easy in DBI
if (defined colX) {$num_data++}

A set of columns that you want to treat like that probably belongs in a separate table related to the primary table (1-N relationship).

I am running head on into a ton of "derived data" issues, i.e. columns i add on with post-table creation processing

Counting is then trivial. SELECT t1.id, COUNT(t2.value) FROM t1 LEFT JOIN t2 ON t1.id=t2.t1_id;

that are based on data inside the columns

given the question you're asking, i'd be handling it in your programming environment rather than in your data server. i know mssql provides computed columns, but not sure about mysql / others.

You can have derived columns in a VIEW.

ah, really? i didn't know that. very cool to know.

But I would first revisit your approach.

many sins are forgiven in your programming environment.

I'd normalize as Xgc suggested

which is not necessarily a good thing.

it'd be very easy

Yes i have the normalization link, i will read about that and more proper database approaches

normalisation is very zen.

You can dig yourself a pretty deep hole very quickly if you don't address design issues early in the process. It can be much more expensive to do after the fact.

hello all. is there a way to treat a VARCHAR field as an INT for ordering purposes? SELECT * FROM foo ORDER BY fieldname;

Not if you use me their isn't. You think this is a game?

fieldname is a varchar containing mostly numbers, when ordering it puts 1111111 infront of 40.
i would like to tread fieldname as an integer for ordering purposes.. make sense? cheers.
… ORDER BY CAST(myfield AS UNSIGNED)

Hey, is there an Apache guru here….
Or was that #bash?

hi all, mysql-4.0.x serials does not support utf8 ?

No

4.1.x and up

seekwill, weigon , thanks !

If I wanted to select the name of the primary key of a table, how would I do it in mysql?

Check Information_schema
Not sure if it's there though
It is in DESCRIBE table

yeah, but I'm looking to determine it programatically, I'll check in information_schema
thanks

hi guys
is there a way to have some report about mysql usage
it seems that i open too many temporary tables
i don't understandf
my program counter show that i don't have more than 2 table at the same time
and mysqld create more than 4000 files in the /tmp directory
how can i make sure my table are DROPPED well by my programm

you could just clean up /tmp

?
as soon as mysqld stops file are deleted
is it a kind of joke ?

*marketing* http://mysql.com/products/enterprise/advisors.html
the files in /tmp are usually temp-tables
used for group-by, large sorts, or just large temp-tables

nmine are explicit TEMPORARY tables
i have a recursive code, with child, that create a temporary table
fo narrowing the number of line to have smaller JOIN
it's the HELL faster than JOINing 400 000 lines all the time
so on object creation i do a CREATE TEMPORARY TABLE

if mysql uses mkstemp() the files will be removed automaticly on shutdown

on the destructor call i do DROP TEMPORARY TABLE
i count on both side cnt++ on create, and cnt– (with a static class var)
the ouput says 2 table are actualy running
sorry disconnection
any answer i missed ?
maybe temp table aren't deleted until EOT

EOT ?
here's the last thing said

if mysql uses mkstemp() the files will be removed automaticly on shutdown

is there a way for mysql to use data from an external hard drive? i'm dual-booting and want the windows and linux mysql servers to both share the same data

Yes

how would i do this
and does it pose a problem for data integrity?

Point my.cnf to use the drive as the datadir

yeah but i have to free / remove them before the shutdown and before the end of connection
in the same session

Shutdown MySQL before you disconnect the drive.
MySQL won't be happy if you disconnected the drive before itwas shutdown

sorry was me
mysql works like a charm like always

Of course. It's never MySQL's fault

i wasn't realizing the hudge size of my recursivity

hi all, how to add fields name in csv file while creating it through sql statement

ok guys you have to find me a solution
anyone does mine if i show an adult link ?
seekwill, ?
we are all non-under age MySQL users right ?
weigon, ?
can I ?
i would like you guys to get some advice about that implementation
mine was good, but not good enough, cause i have an obvious TEMPORY TABLE LEAK
no one wanna see that link
but trust me guys i think it a really complicated problem even if it's adult !
seekwill, Am I alone here ?

how to add fields name in csv file while creating it through sql statement

ALTER ?
what do you mean while creating it ?
CREATE TABLE `toto.csv` SELECT … ?

making a csv file

a csv table or csv export ?

i am trying to make a csv file of 2 tables
how can i do this ??

there is two way's
i guess you wanna use an export
you can't use the mysqldump tool right ?
you want a statement ?

SELECT CONCAT(virtual_users.user, '@', virtual_domains.name) AS email
FROM virtual_users
LEFT JOIN virtual_domains ON virtual_users.domain_id=virtual_domains.id;
i m not clear on that

what's not to be clear on that..
the concat? or the left join?

well, first where it says users.user

it puts them together by @

check SELECT INTO OUTOFILE\

which is actually used by postfix itself

syntax

DO NOT INVENT SYNTAX (well, unless you have commit rights). Just follow the syntax described in manual. Thanks.

postfix needs the whole thing
but it's much nicer to store them in seperate fields in the db

ok.. i see

the version of virtual hosting on my 1 server is still 1 full email for login
but the newer one i setup, is more like the one you pasted

Abdul, SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;

where you store the user and domain seperately

guys i m not on that lvl yet

well.. just gotta read

i know thats what i m doing

!man concat

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

aha.. it does work.. fun

i just came across this

johnny, are you good enough to gimme your opinion on a complexe issue

heliostech, about your /tmp files
no

this is solved

probably not tho

it's a kind of CROSS JOIN problem

i'm not quite so good there yet

or SELF JOIN

i'm actually working on such things right now

Xgc, weigon archivist ? are yu guys there i need some skills

thank you

just ask

hmm, i'd be willing to pay some people for tutoring sessions sometime, i tend to run into problems i don't understand (on the fundamental level)

why does it make this log passes
ebc1934af6bcae8af6bcae8af6bcae8eb7

i ultimately code to complete a job
looks like md5?
password?
they all used md5

yes
let me check on that md5-login
i gues put plain

i always use md5 passwords
if you're making a user, phpmyadmin is handy to do it , you can just select the md5 function in one of the options
well i mean.. i should upgrade to something else

yes i m runing phpmyadmim as well

but it's still better than nothin

weigon, i need to explain my situation
it's all about crossing categories, to N level

hi, is replication a good tool to keep a sample data set on a mysqld running on my laptop when I go mobile ?

yes, maybe

VALUES (1, 1, 'john', MD5—change that to plain?('summersun'));

domas|TW, the only adds I make to the mobile data set will of course be disposable in case of conflicts

there's no way to roll back like that

can't just wipe the server and start fresh?

you can

ALTER TABLE a_new_table ADD FOREIGN KEY a_new_table(yo_ho_ho) REFERENCES contacts.contacts(id);

Can't create table './media/#sql-1080_326.frm' (errno: 150)

'show innodb status'

has mysql just run out of memory or something ?

it will tell you 'last FK error'
you probably provided bad definition
d/probably/

!tell domas|TW about 150

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

ok

thankyou!

o on a side note is there any way to get a paging in mysql shell ?
Cannot resolve table name close toid)
intersting
so my error is in the last bit where mysql can't find the table i am trying to reffrence
i am refrenceing it in a strange way ?

VALUES (1, 1, 'mac', MD5('summersun')); if i don't wan a MD5 i wan a plan what do i put in MD5 part?

but .. it might expect it
you'll have to experiment

i did put plain login didnt work
then i put plain didnt work

plain login is totally different

i check on dovecot.conf it uses mechanisms = plain login

yeah.. that is not the same thing

oh i see.

that's related to between the client and server, and how the password is sent
not how it is stored

understood..
ok i ll just use that md5

to understand more about that, is not related to mysql at all
but to sasl

but i try to change it that didnt work

totally seperate and waaaaaaaaaaaay off topic

i see..

dovecot does have its own implementation of sasl, so i have no idea
it doesn't use the standard cyrus-sasl

i got my sasl2 configed

that stuff is complicated
once i got it working, i stopped messing with it
email is a total mess, since all the protocols were designed before security was a concern

i send mail it wroked

very black art

but what came to the pass it didnt

yeah.. you're gonna have to go somewhere else for help with that

tis making that md5 passes.

that's a problem with the software, not mysql

ok.. i m just lost.. thank you

it's hard to understand
it took me two weeks to get it right
dovecot at least makes it easier, or so i've heard
dovecot wasn't created when i set my stuff up

mysqmysql_virtual_mailbox_domains.cf — by generating the pass do i need to input that pass in there as well?

no idea
it totally depends on the setup

ok i ll try both ways

seriously tho
go join a dovecot room
or one specific to your distro

i was trying to hex it see what it says lol

you'll get much better help there

i m in there
let me ask them

hi
i'm using Mysql on a RH server
do you know the file wher i can allow different network to connect on my server?

you have to allow this with in mysql it self.

yes, but how can i do this?
cause i'm not on the same network and for the moment i juste have a SHH access to the server!
i'm connect to the server thanks a VPN!

5.0 (on linux)

!man upgrading

see http://dev.mysql.com/doc/refman/5.0/en/mysql-fix-privilege-tables.html

!man upgrade

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

That should be it

it does not have "from 4.0 to 5.0"
only from 4.1 to 5.0

rrva look at the 4.1 version of the page for 4-4.1

is it possible to get a working libmysqlclient for mingw somewhere?

I have a foreign key constraint causing problems when deleting a row. heres a paste of a mysql session showing the problem: http://pastebin.com/m51591777
why can't I delete "Acme" there is no linkage to it in departmentexternalid
But If I truncate the table, all is fine.

anyone knwo what files or libs im missing in the chrootdir, if it gives me this
[ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!

none its telling you to not be stupid

no
its telling me your a dick
lol

is there a working solution to connect to a mysql server from a mingw console program?

hi all
How do i add fields name in "SELECT * INTO OUTFILE "c:/mydata.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM login;

would i be stupid to ask, why "chroot /chrootdir/mysql /usr/bin/mysqld –user=mysql &" gives me this Fatal error: Can't change to run as user 'mysql' ;, and in fact passwd etc are wll in the chrootdir

Hello!
I duplicated a database with mysqldump
but copy database misses on etable, how is that possible?

look at error messages/warnings on the import

how can i get INTO OUTFILE on remote location
while makeing csv file
through select statement

thanks archivist

anyone know my issue plz need help
how to add field name while makeing a csv file through select statement SELECT * INTO OUTFILE "c:/mydata.csv"
Abdul FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
Abdul LINES TERMINATED BY "\n"
Abdul FROM login
SELECT * INTO OUTFILE "c:/mydata.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM login

assuming I have it all setup, how do I get in to phpmyadmin pls? http://localhost/ ?

wrong channel ..
plus it's distro and install specific

and web server setup etc…

how can i add 2 tables data in a single csv file through select statement
how can i add 2 tables data in a single csv file through select statement need help

a sensible select statement

select t1.c1, t1.c2, t2.c1 from table1 AS t1, table2 AS t2 WHERE t1.blah = t2.blubb;

hello
how can i see the 10 last value of my table?

first decide what last means to you

humm
last line i think
select * from user LIMIT 10 i just see the 10 1st

order by somfield desc limit 10

humm
a little mystic for me

the table has no natural order unless you state what you want

oh ok

what is the max lenth of a varchar?

PrMoriarty, remember after a delete, the next insert will be into the freed up space

interesting

thanks one more thing how can i add fields name on top row of csv file
i have approx 30 fields in one table
i am making csv for 3 table mean aprox 70 fields
so it is very diff to all all fiels

information schema

information_schema is a virtual database of databases inside of MySQL 5.0. You can use it to get schema, table and column metadata. Please note: It only shows information the user has access to. For more info: http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

0` what is wrong with this statement

what did the server say

it says unknown column 1970-01-01 ….
while there is a column datestr holding lots of such entries with that date …

quotes

Use ` around identifiers (database/table/column/alias names) and ' around strings and dates. MySQL does allow " for strings, but ANSI standard uses " for identifiers (which you can enable with ANSI QUOTES option)

if i give other commands like select etc with ` it works but in the above case even if i put ` still i get same error