it is not working I am getting a weird column error
any thoughts?
hello. I need to know how to do this selection insert

http://pastebin.ca/649083
why does it give me that error (in the paste)

are you there?

show us the full error message. "weird column error" doesn't tell us much

Operand should contain 1 column(s)

hey, umm.. I'm trying to make a game database.. its a simple project.. here is an example. gamedatabase.no-ip.org

INSERT INTO testingdb.tempo1 (first_name, last_name, primary_address_street, primary_address_city, primary_address_state, primary_address_postalcode, email1, email2, phone_home, phone_mobile, description) SELECT (first_name, last_name, primary_address_street, primary_address_city,
primary_address_state, primary_address_postalcode, email1, email2, phone_home, phone_mobile, lead_source_description) FROM nhmc_sugar.leads WHERE assigned_user_id LIKE '

Operand should contain 1 column(s)
wait I think I figured it out

so if someone can help me. since I'm more of a designer than coder.

please ask a specific question, don't ask us to design your database for you

ok this is my question. I have the idea, for a form submit. It will use php to add a game to a database. I dont know much of anything about coding.

just some guy. have you checked /whois ?

an example of what it will look like is gamedatabase.no-ip.org

your code is outputting PHP tags rather than executing php webhosting code, this looks like a bug

I need help still I am not able to get this to work still

For help with PHP try ##php, although even they won't tell you how to make something from scratch

INSERT INTO testingdb.tempo1 (first_name, last_name, primary_address_street, primary_address_city, primary_address_state, primary_address_postalcode, email1, email2, phone_home, phone_mobile, description) SELECT (first_name, last_name, primary_address_street, primary_address_city,
primary_address_state, primary_address_postalcode, email1, email2, phone_home, phone_mobile, lead_source_description) FROM nhmc_sugar.leads WHERE assigned_user_id LIKE '5d8efe7
Operand should contain 1 column(s)
I am still getting the same error

No () around your select list.

Try removing the brackets
Try the select on its own in the mysql client. If it doesn't work, an insert…select definitely won't

You should have them in the insert field list, just not in the select part.

ok
yes awesome it worked

What's the difference between a unique key and a unique index?

nothing. key == index

That's what I thought…

I'm not your guy. sorry. busy with other work.

I just got confused when reading the mysql hosting documentation for alter table.
ALTER TABLE blah ADD UNIQUE {INDEX|KEY}

{ | } means you have to pick one of them
compared to [ ] meaning it's optional

Yes, I realize { | } means pick one or the other and that [] is optional… that's fairly standard notation.

Not everyone knows that…

But the option of picking one or the other for some reason led me to believe that there was a difference between a key and an index in mysql.
I now realize I was just over analyzing that the syntax meant.

The syntax doesn't tell you whether the choices are synonymous or not.

I need a little bit of help.

No, it doesn't.
So I assumed they were not synonymous.

Have to read the manual for that, and sometimes it's surprising. Occasionally, it even changes between versions.

I'm trying to create a php login form, which checks a mysql database and sees if the info provided and the info from the database match, but it isn't letting me in, even with blatantly correct info.

What does "isn't letting me in" mean?

Well, the code sends you back to the login page if the info is incorrect.
And i keep getting sent back to the login page.
http://pastebin.ca/649107 - code for the login

use mysql_real_escape_string around those values from $_POST, or some unscrupulous cur will come along and hack your db.

mysql_real_escape_string? (sorry, i'm fairly new to mysql)

!php mysql_real_escape_string

string mysql_real_escape_string ( string unescaped_string [, resource link_identifier ]) Escapes special characters in a string for use in a SQL statement http://php.net/mysql_real_escape_string

Don't use the PASSWORD() function

PASSWORD() makes it more secure?

Use a different function, like sha or md5

!php sha

php.ini variable name= default= http://php.net/

o-o
Will sha or md5 work on mysql 4?

Yes
Next step, for debugging purposes, echo $sql, then copy and paste into the command line client / phpMyAdmin/SQLyog/whatever and see what results you get.

I'm still on step 1 lol.

Is there a mysql benchmarking program (like bonnie for mysql) that I can use for analysis of a mysql setup?

There are some advisory tools available when you buy Enterprise.

i suggest install collectd
installing rather

winmutt, thanks, i'll look tha tone up

it will help to some degree

where /what do i set samba user/computer so a winbox can login
man samba mentions .tdb but fails to say how to change

Guest = ok ?

heh I was ranting in wrong window

http://www.linuxheadquarters.com/howto/networking/samba.shtml

thnks

UPDATE tempo1 SET id = CONCAT('arg020708-', id);

Duplicate entry '0' for key 1

Dell memory is SOOOO expensive

I think it's made of gold

hehe

Is there any gold used in the production of memory chips?

or Michael Dell has to poop out every single one…

Might be worth keeping old memory around just to melt it down…

requires lots of industrial lubcricants
hmm don't know, but generally hardware contains some gold and other stuff
heard of a guy who got fired for taking the companies electronic trash home and extracting gold and other stuff from it

pretty toxic stuff in their, too

yes

there*

so if you have a wife that bothers you, you could try the Reiser method

as usual, the stuff is getting crapped all over the 3rd world

all with old hardware

And fun magnets in hard drives.

Any help on this error?
Call to undefined function: mysqli_connect() in /home/bavirtua/public_html/login.php on line 3

If i'm in the mysql hosting linux client and I want to clear the screen like using linux "clear" how can i do that?

Usually that means you don't have mysql enabled I believe.

control-l

Did you try ctrl-l?

Thank you!

function mysqli_connect($user, $host, $password) { return popen("/usr/bin/mysql -h $host -p$password -u $ user"); }

You just asked the same question in #postgresql and got the same answer.

has your php ever been able to connect to mysql?

It is enabled. I've used other script using mysql

In addition, I told you that ctrl-l is more of a shell convention than linux convention.

Yes.

I didn't ask the same question, I asked one for psql client and one for mysql client

You need the "mysqli" extension enabled.
It's a ##php question

My company supports both so I wanted to know, and I just tried it in the shell and yeah, it's the same as clear on the shell

Are you really buying Dell memory?

no. Kingston probably

'We live only to serve the shell.' I like that. New email sig.

I need some help I am trying to update a table and I keep getting a Duplicate entry error and when I look at the data I am seeing only one record

already an oil companies slogan

lol

SiliconG, but its already in the destination table

Is it possible to connect to the db without using mysqli?

some guy in here mentioned that there are problems with other memory manufacturers in dell hardware…

I am unfamiliar with mysqli

but well, you can buy the same ram twice and still save money

mysql_connect?

Try mysql_connect() maybe?

Not sure if I like Kingston any better. I am upgrading a friend's Dell right now actually. Just bought two 512MB sticks for $30 each. Corsair I believe.

i run kingston in it now

what kind of hardware?

Just about to

it is not in there I have looked

i have to buy the fully buffered, yada yada yada, ddr2 stuff

I want to update a column in the temp table

it's a dell pc

I just want to concat a string to the id

ok, we talked about servers back then

I just got a whole mess of errors.

"what kind of car do you drive?" - "A blue one!"

what I am trying to do is update one column in the temp table and it is not working

I don't know enough about php to know what the difference is between mysqli_connect and mysql_connect.

http://pastebin.ca/649152 — The errors i got. http://pastebin.ca/649154 — the code used.

But I suspect the folks in #php would know.

Okie dokie

Well, these are pretty obvious.

I'll try there

Access denied for …

SiliconG, duplicate means the id is already in the table

ok - I dont want to insert anything I want to update the id field I want to concat to it
there is nothing in there like that
they are already in there right - so how do I just add to the id
update right?

update to change

ok how do I do that?

!man update

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

I am a frustrated newbie
can I show you the way I got the data into the temp table

is there a signal one can send to mysqld and have it do a graceful shutdown?

update tablename set field = somevalue where field = someothervalue;
what o/s?

the socket got removed and the root privs are for the socket
debian

But if field has a unique constraint, it could possibly fail right?

try signal 15

I want to update them all;

So drop the WHERE clause.

update tablename set field = somevalue, field2 = somevalue, field3 = somevalue where field = someothervalue;

Are you sure that's what you really want though?

all fields or all rows
all fields or all rows?

ok id int AUTO_INCREMENT PRIMARY KEY - I want to add concat a string to that number
for all records in the temp table

you can't

An int can't have a string on it.

should I kill -15 the mysqld_safe process or the mysqld instance?

I'd recommend a bit of reading, perhaps the tutorial in the manual
have to kill mysqld_safe first, or it mught restart the mysqld
might*

can I just change the type to char(36) then?

trying a few other means before the kill

how do I change the type then

there was probably a good reason to choose an nt auto_increment
int*

there was not - so now I want to change it how do I do that

!man alter

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

what's the reccomended ammount of memory memory_buffer_size should be equal to in terms of a % of memory? 80%?

memory_buffer_size?
I think you just made that one up.

uhm

well, killing mysql_safe (no signal specified) seems to have cleared it up
now I wonder if the tables are corrupted or not

hrmm

!tell O10xz about tuning

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

is there a web site when you put a word, it gives the word in many languages automaticaly ?

google.com

I need better harddrives

http://pastie.caboo.se/85797

Great. Banned from ##php. Thats useful.

That doesn't mean you come back here…

It's a free country. Sheesh.

Maybe where you live.

Dude, i aint even looking for help this time.

Then you're being way off topic…

You lot where aswell…
Sheesh, why does everyone hate me.

Because you're cheese.

But everyone loves cheese? (unless your lactose intolerant)

don't worry, I also got banned some time ago

What for?

nothing, they banned my whole ISP

Ooooer
I was cheeky to two admins :-[
How do i select a database?
Cos my login script is giving me a no database selected error.

*sigh*

…
Nobody knows how to select a database?

how do i generate a report(count) of record.answered and record.unanswered per number based on this setup. account has many numbers. record belongs to numbers.

group by … order by…

in one sql statement? can i generate unanswered answered busy etc?

USE dbname;

would that be in mysql_query()?

`paul, i do not know what you are talking about, but the answer is probably SUM(CASE WHEN TEHN 1 ELSE 0 END)

it's for the mysql console.

MonsterCheese, in client application, usually it means connection

No database selected.

specify a valid database name, then.

does it need the username prefix, thumbs?

I HAVE NO THUMBS

Obviously so, thumbs. ty.

I HAVE NO SEEKWILLS
the username?
start a terminal, run the mysql client, and connect to a database

MonsterCheese, how about reading the fscking manuals instead of asking dummy questions again and again/

No need for that language

no offense, just common sense

And the reason i don't read the manuals is, they're too long and half the time i don't understand em

well either way, I probably just grabbed a hot potato

MonsterCheese, thats bad attitude!

have you read the mysql manual?

GIVE ME DIRECTLINK IM HIGHPAID MANAGER I HAVE NO TIME TO USE GOOGLE
*sigh*
thumbs, he cant, his time is more valuable than ours!

oh ok then.

hello, i'm in process of moving my client's hosting accounts to another, dedicated server. One the current server database is running locally and all client's apps connect to it through localhost. In case of new server, database is on other machine, listening on some certain ip. Is there some
method to "redirect" localhost connections to that new address ?

oh for fuck sake why the fuck is everyone is the goddamn mother fucking world against me!?

MonsterCheese, if you do not understand the manual, that just means that you need to read it a few times more

well, now Im not surprised at all why you were banned from ##php
bwell, now Im not surprised at all why you were banned from ##php/b

we don't spoon feed people

MonsterCheese, you are not worth being banned

we expect people to actually read the manual and / or google a couple times
if you can't even make that effort, then why would we help you?

Oh thanks alot tibyke. I'm gunna go now and jump off a cliff.

lets get ontopic again then

MonsterCheese, do not forget to report back on how well it goes

well, you would want to replicate all the data to the new server
then configure your application to use the new IP/PORT

…

this way, you can have next to no downtime.

right, but these are my client's apps, and there's quite a few of them

well you're can't simply 'redirect' the mysql connection.

i was trying to do something with iptables but with no effect

a replication server would really be the best solution

anyone here know if the rails add_index migration will look at your current rows and add indexes to all of them?

you can do a perl -pi -e "s/localhost/[newipaddress]/" in the files, zoldar

hello, setting up backups for mysql4.1 99% myisam tables. can i consider "mysqldump –lock-tables –complete-insert …." to be consistent? (somehow i just can't trust mysqlhotcopy, especially when the manpage says "STILL BETA")

but afaik it requires special format of tables ?

I don't recall if one table format can't be replicated
perhaps someone here can confirm that

i mean innoDB/myISAM

I know what you mean.
I don't want to steer you wrong.

or maybe i mistake it with cluster

better ask the other helpers here

ok nevermind, thanks for effort

help this guy, please.

With?

zoldar, cluster needs it, but not replication
cluster need ndb, or ndbcluster

replication has no restriction on the table types, doesn't it?

right

Correct. Current replication is statement based.

thanks, that sounds promising - just one question - isn't it passive-active relation then ?

thank you.

passive-action?

can there be two peer databases syncing with each other? both being actively used ?
ok, i'll just take a look in the docs, thanks for pointer

zoldar, no direct update/insert to slave, only master

He's asking master to master

ah, sorry

so, that's possible ?

Yes

I believe the concept you are looking for is "multimaster replication"

But I don't know how to set one up properly

Auto-Increment in Multiple-Master Replication ?

me neither… I've just seen it mentioned in here before

zoldar, but you can just simply dump your old databases and restore it to the new box

looks risky
i have no problem with that, the thing is, that all client's apps have multiple, hardcoded references to database by localhost

sucks for them, they'll have to update

sucks for me then…. ok, just thought there's some relatively easy workaround

Any idea how to fix this syntax error? –http://pastebin.com/d367684ac Error message is posted here –http://pastebin.com/d731fde34

group by c.CompanyName (if it's in that table) afaik
and qualify the fields you select when using names for tables

ok. I will take a look. Thanks Ekimus.

well there is an automated way to do it if you just need to replace the hostname

filename1 && mv filename1 filename

i'll have to do it more or less that way then , thanks for help

CompanyName is in the Customers Table and Customers Table is already declared by northwind.Customer as opposed to c.Customer. I am wondering if something else isn't going on here?

all

I did try it with c.Customer and still got the same error message.

ciao
how can i change varchar fields from latin1_swedish_ci to latin1_general_ci? is there a way to do it on all fields or better all tables?
do i have to do it manually field by field?

hmh

Whats a reliable way of getting a list of databases from mysql? Db in mysql.db is not.

um, show databases;

is there a way to change the collation and charset of all tables/fields in a db?

Lumber1:

hello
i lost my mysql server, how can I extract my sql data bases from linux? what's their location ?
bi lost my mysql server, how can I extract my sql data bases from linux? what's their location ?/b

what do you mean "lost"

depends on the installation
anyway, search on your disk for files with the .myd extension
or MYD

indeed, how did you lose the server?
you!

me?

is that your *real* name?

… If i told you i'd have to kill you

hehe

ha
hmmm
stupid /nick isn't working!

my name is chadmaynard

Lumber1 … it's supposed to be Lumberg!

all imposters tonight

archy

gaim is a bad irc client

why is gaim a bad irc client?

because it wasn't designed to be one?

maybe

hmmm. i've had no problems with it. well, using pidgin, but still.

is it possible to ask mysql to set a random value of a given range for some field?

why?

robboplus, yes

gnari hm
!man random

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

!man rand

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

oh hm…

random

http://jan.kneschke.de/projects/mysql/order-by-rand/

!man rand(

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

does anyone have a procedure or a function that lists all tables and then all fields and changes their charset and collate ?

I'm trying to drop a row in my table with:
ALTER TABLE `customers` DROP `last_credit_card`;
I get this error from MySQL:
Error on rename of './quickiwds/#sql-ac9_36' to './quickiwds/customers' (errno: 150)

!perror 150

Foreign key constraint is incorrectly formed

doh
thanks chadmaynard

150

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

Anyone play Forza2 on Xbox Live?

how do a automate a mysql backup?

make a cronjob that runs mysqlhotcopy or the like

an anyone here make a procedure that lists all tables on on each changes the charset and collation?

Sal`: did not understand

i need a way to change collation to all fields, tables and databases
so i was thinking that maybe it could be done with a procedure

alter table can change for 1 table

yes, but thtere are a lot of tables and fields

SELECT * FROM links WHERE created_at = 2007-08-07 AND created_at 2007-08-08
where created_at is a datetime

i want to find a way to do them all with 1 command

why care about fields if you want to change all fields in the table

basically trying to find out if the datetime's day is equal to today

Sal`: maybe can use a shell script

date(created_at)

because if you change the table collation, fields collation doesn't change

oh, well, thats even easier, where created_at = now()

Sal`: CONVERT

i think that a procedure would be better

er, actually, still need date()

Hi All. I have a DB with thousands of phone numbers but they are not stored in a nice format. How can I update my db so all the numbers are converted from this format: 2125551212 to this: (212) 555-1212?

Sal`: i know convert in alter table can change the charset, should work for collation

where date(datetime) = curdate()

convert!!!??? i was using alter
let me see that

Sal`: alter table has a convert keyword

there you go, curdate(), I blanked out for a minute.

datetime = curdate() and datetime curdate() + interval 1 day

convert wors only for charset, can't use collation
so the only solution is a procedure with "show tables" gets the output and does an alter %table% …..

does anyone know if there's a web resource to tell me exactly what files _mysql.so requires and where they should be located?

Sal`: can also do collate, you can set both the charset and collate at the same time with convert

the manual says no

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

Sal`: I don't think you can use the results of SHOW in an sql statement unless you're using some application code to process it

knnylggns, ldd?

a function and a procedure could do it
but i don0t know how to build them

lol, well, there's that

Sal` prepare statement if alter table is allowed in prepared statement

thats the alter table… i already tried that.. but it means, as i said, dot it one tabel at the time

Sal`: have you looked at querying the information_schema and using that?

flawless! thank you so much

good

what can i do with that?
i'm looking at the info_schema right now…. then?

Sal`: you can use a "select" to get the table names

show

Who can talk in Chinese with me?

Sal`: querying the info_schema as an alternative to using SHOW statement results in other sql statements.

Sal`: problem with show is that how do you use the result in a stored proc

Who can talk in Chinese with me? I need some help.

why? doesn show return data like a query?

Sal`: no

Sal`: you can use cursor with select and process 1 table name at a time, show probably cannot
Sal`: then use the table name and generate a prepared statement and execute it
Sal`: i hope prepared statement works with alter table
Sal`: too bad, alter table not supported in 5.0
Sal`: a simple shell script should do it

kimseong++

ok, i'll do it with a scripting language
yes
thanks anyway

hi guys

I am trying to do a JOIN between 3 tables 2 of the tables. 2 of the tables have the same data element and this is undesirable because it gives me an ambiguity error. Is there a way to limit the data element from this table and still attach it?

I have a small problem with simple mysql query
http://pastebin.com/m599bf045
AsText(bounding_box) as bounding_box it works, but if I use "as Layer.bounding_box it doesn't

hi

how can I make the 2nd one work ?

can i see any MySql configure " for much "Large" data" ??

quote it
my-huge.cnf ?

kimseong i dont have any files my-huge.cnf

thanks

./usr/share/doc/mysql-server-5.0/examples/my-huge.cnf.gz
i open theat .gz file no ?

that is a sample config file

so is theer conf for huge mysql ?

how huge?

my web site send php data to mysql ..in large amount " data info" so CPU get up to 99% ..i must configure Mysql and php

cialis huge, viagra huge

anyone know how to check if a columnName exists in a table in DBI?

information_schema

what command do i use to allow root access remotely

!man grant

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

^^
!man remote

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

!man remote access

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

should i hash the passwd first?

sigh

create user root@'%'
you get a non priviledged root with no password

hey folks
this legal? select * from sometable where uid in (10,13,3424,2342,32)

snoop-: try it first

ok kimseong
i think it will work but am skeptical
curious as to which is quicker, a query with 40 or's or an in statement w/ 40 elements

snoop-: it is handled the same way internally

so the in would be better since my search string is smaller

snoop-: which is faster for you to type

therefore I would use the IN notation because it gives a better overview of the logic

true

what use is a non prived root?

yeah, that's logic for you

typo less likely

it is shorter and takes less communication and parsing
you can connect remotely

well, as the parsing expands the IN it might not be so But the parsing is really, really minimal

what if i need to crete tables, etc remotely?

you should have another user with just enough priv, instead of root with full priv

ok, my point is - how can I hash a password in SQL when creating the user?
what ever privs i do
i still need a passwd

the create user and grant syntax shows you that

what?

identified by 'password' plain text

ok

is it possible to combine different types of indexes into a multi-column index - such as an index first overlapping an ordinary int field and secondary using e.g. FULLTEXT or creating a SPATIAL FIELD?

hi

I would e.g. like one table with a lot of POINTs for a lot of different customers. I don't like the idea of creating a table per customer

guys I have a little trouble
I have just added two fields to tables
alter table hhg_interviews add column photo_big char(255) not null after photo;
alter table hhg_interviews_sv add column photo_big char(255) not null after photo;
now, I'm told to remove them
how can I do it?

alter table hhg_interviews drop photo_big;
alter table hhg_interviews_sv drop photo_big;

thanks a lot pbro

every 8 hrs ..mysql database is losing its connection after the idle time,how to avoid it?

sounds like a default connection limit

timeout when you leave it idle

What have to do to avoid it?

'By default, the server closes the connection after 8 hours or 28800 seconds if nothing has happened. You can change the time limit by setting the wait_timeout variable when you start mysqld via your servers my.cnf'

why bother, mysql cli reconnects, and why keep it when you do not use it for so long

Thanks threnody.

thanks thats done the trick.

you should probably close your connections when you're done with them.

mysql cli reconnects? How come?
ok

if you use mysql cli, it reconnects when connection loss, if your own application, you can code it to reconnects

oh! ok

what's the function to get the last row inserted?

LastRow() ?

last_insert_id()

aha!
ty seekwill

ah, thanks

I know that I can determine the number of child nodes in Modified Preorder Tree Traversal with some simple math. But what I can't figure out, or find, is how to do it in the reverse?
That is, can I use the left and right value of a certain node and figure out how many parents it has?

http://www.mrnaz.com/static/articles/trees_in_sql_tutorial/mptt_display.php

pap great thanks.

hehe
that's probably the most quoted article i've ever written hehe

it's you !!

indeed it's me

nice work, ty

any time

MrNaz excellent article. this is a keeper.

given how popular that is i've been meaning to clean it up and fix all the spelling and grammar errors all over it

MrNaz question… If there are going to be more than one major category, should they all be placed under a master 'root' node?

no
i'd give each major category their own ID and mave 3 separate trees

ok. so they should all be siblings at the very top level?

no not that either

interesting. i see.
so i'd need a tree_id field then.

so there'd be a table with a list of categories
id, category_id, lft, rgt ….
yes, the category_id is essentially a tree_id
http://www.mrnaz.com/static/articles/trees_in_sql_tutorial/mptt_deployment.php under "Multiple Trees per Table"

great thanks. i'll check it out.

is there a startup script to have mysql ndb management started upon boot?
or is there a reason why a startup script doesn't come with the package?

mrnaz i'm a bit confused about using multiple trees. let me use my case as an example. i'm building a shopping cart and i'll have to organize the products. if one category is called Electronics, does that mean the top nodes in the tree will be (1)TVs(2) and (3)Stereos(4)?

firstly… are you sure that you need trees for that?
it's only ever going to be 2 levels deep
why not just have a products table that references to a categories table

yeah that's right.
but i'll have several levels of classifications for products.

ok

could a product ever show up in more than one category?

in that case your categories table should contain a single tree, and your products table just refers to the categories id

pap yes

danggun in that case your product table and categories table should be related using a link list

yes, trees are not best for your purpose.

mrnaz so then my initial table layout is ok?

Pap i disagree…

mrnaz yes. that's the way i've got it planned so far. tables: categories, products, and products_categories

1) a "categories" table with a tree of categories, 2) a products table with your products and 3) a link list relating products to categories
Pap does that make sense or am i missing something?

mrnaz yes exactly. products_categories does just that. it will be defined as "product_id, category_id".

danggun sounds fine to me

yeah

no

mrnaz great. so then back to my original question. do i have one master 'root' node or do i simply start with the top level categories being siblings with no common parent?

it's unlikley that you'll end up with millions of categories, so i'd just use one root node

(2)electronics(3), (4)stereoes(5) OR (1)electronics(2), (3)stereos(4) ?

mrnaz you're right.

you can have a tree like the second description
— that's not a valid
as you

I have a table called "censored_word" with only one column, called "words". How do I check if a string contains any of these entries?
SELECT COUNT(*) FROM censored_words WHERE LOCATE('word', string)?
s/"words"/"word"/
Hmm… no.
I'm stuck. Please help.

WHERE string LIKE '%word%'; -

How can I make this query any more efficient:

Catnip96, SELECT 1 FROm censored_words WHERE string='word'

http://pastebin.com/m6e85396f

start over : )

No.

start over?

No?

You're thinking about it backwards.
I must use LOCATE…

why?

Are you saying that it's as efficient as it'll get?

Because the table is the one containing the words to check.
Not the string.
Is that a joke?

you want to see if the value in the database is in the provided string?

That is honestly the worst nightmare of an SQL query I have ever seen, and hopefully will ever see.
Um… I thought I put it clearly. :/

I'm using MySQL to store binary data in a 'blob' column. At first glance, it appears my application program is inserting all of the data correctly, but MySQL is magically truncating it to 65535 bytes. My max_allowed_packet is not being exceeded, because I have it huge (500M) and I get errors
if I try and upload anything larger than that. This truncation is happening silently. If the number wasn't suspiciously a power of two, I might think it's my applicati

!man AGAINST

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

Catnip96, You have a row for each word in censored_words, correct?

Sorry but I am not sure what's wrong, it's huge but other than that what would you do differently?

Yes. And this works, but I need to check for extra chars around this.

Catnip96, %% should work…

I really cannot imagine why you have to make such a query.
Really bad database layout from the 1970s?
….

No, I am doing a FULLTEXT search using multiple keywords.

Ever heard of formatting your code?
This is impossible to read.

I echoed this from PHP catnip

select * from foobar where LOCATE(barfoo, 'search');
like that?

Did you even see my example?

http://pastebin.com/d387159b3

SELECT COUNT(*) FROM censored_words WHERE LOCATE('word', string)
But I also need to check for special characters around the words.

yes so what's the problem? switch the args and be gone

So how do I add that check?

what is a special character?

There's your formatted version, by the way I am asking for advice.

Not important. How do I add it any string to this check?

I don't have a clue what you are talking about… ?

!man LOCATE

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

So you criticize and have no comments–wonderful.

trollish isn't it?

Yes it is.

Eh? Nobody could help you with that blob of text.
So I asked for a properly formatted one.

I think that you have the wrong approach

http://pastebin.com/d387159b3

Yes.
And now somebody can probably help.

blog length limitation is the same as text. 65k in your case. try a mediumblob or longblob. http://dev.mysql.com/doc/refman/5.0/en/blob.html

threnody to the rescue

well great.

tell me the benefit of using trees (which I am clueless about) vs. relational tables.

i had not even heard the term tree in years

oh humbug! there I go, assuming that blob is like it is in other DBMS'! thanks for finding that

relational tables for the win.
So…anyone for maximizing efficiency in:
http://pastebin.com/d387159b3

In highschool they used that term in my MS access class : )

it is almost always an error to store files in databases, instead of a pointer to the file. Be careful.

In "… LOCATE(`some_column`, 'huge string of text') …", is it possible for me to add characters before and after `some_column`?

CONCAT?

I agree, although in this case we have multiple web/app servers, and figuring out a good distribution scheme that 'works' is out of our realm of expertise

!man CONCAT

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

you have a good reason not to use 'where `col` like '%text%'; ?

Yes… because that is backwards.

sorry

I need to check the other way around.

no indexes either way (%foo% or locate)

Well… it doesn't seem like CONCAT can be used inside LOCATE… or can it?
No indexes?

did you try to use concat inside locate?

Well, I am trying to figure out how to use it.

is there anyone using Ubuntu 7.04 and using some GUI tool (myslqcc) in Ubuntu?

Ah…
Well, it seemed to work, chadmaynard. Thanks for telling me about CONCAT.

when you do (select id, description, prioritize, date_added, date_completed, status from items where list_id = 16 and status = 'complete' order by date_completed asc)
union
oops
wrong button
i apologize

Does not MySQL have any sort of convenient built-in string concat…ation stuff?
I love how you apology on three seperate messages.

concat(cola, colb, colc, …)

CONCAT() is built in

Well, I meant without using a function.

Catnip96, i panicked

Like PHP's 'foo' . 'bar'.
This is just me being curious.

Does anyone know how to FULLTEXT search on multiple keywords using one query in an efficient manner?

operators are functions

This is probably not what you want, but what about OR string LIKE '…' OR string LIKE '…' OR…?

I said FULLTEXT search, that would incorporate MATCH();

Evil FULLTEXT indices. :/

How are they evil?

Because they obviously make things more complicated.

fulltext is a lot less evil than '%foo%' or locate
(if evil = slowness)

Indeed, so does anyone know how to do FULLTEXT search on multiple keywordS?

how can i debug why my mysql cluster isn't starting? i can't get the ndb_mgmd running and i don't get any errors why

nope, is that what you are trying to do?

yes.

Is this a huge table? Is this actually necessary at this point?

Yes it's a big table.

So it takes like seconds to run with LIKE?

I've determined you're not very smart.
Additionally you have a very condescending attitude.

modifying the database is done by running the appropriate query?

Yes.

well… yes? the question is a bit ambiguous

There's plenty of GUI tools that will let you do so though without actually knowing the query.
SQLylog is one of them.

fair enough. I have a dbi that gives me five functions: open, query, get_status, get_row, and close.

are you using boolean fulltext or ….

No boolean.
http://pastebin.com/d387159b3

I would use query to modify the database (mysql backend), then, yes?

Look for yourself chadmaynard.
Yes j85wilson.

I know you didn't specify it but you aren't using Fulltext properly in your queries

look in the manual for ALTER
alter

also, terminology question, what verb is applied to query? run, execute, issue, what?

so i thought you may be confused

I am not running FULLTEXT properly?

i say run

all of the above, depending on who you talk to

That query executes just fine…

thanks, Delixe, threnody, ToeBee.

start here. http://dev.mysql.com/doc/refman/5.0/en/tutorial.html

tizag.com is good to for newbies j85wilson.

I know it executes properly that doesn't even infer that it is right.

again, thanks.

It's been working the way I expect for so long, itneresting.
So tell me how should I do it then/

for easy intro, try the latest Larry Ullman book on MySQL

hold up a second let me make an example out of your pastbin. Would you mind posting your schema as well?

sure

bah, my needs are simple and I think I'm smart. Web tutorials will suffice (unless they don't ). Plus I'm on a grad student budget.

me, I go to Borders with a legal pad and pen 3x/week, grab a coffee and make notes.

nice

if I have to buy, I buy used from amazon

I just download illegally from an e-Learning torrent site called BitMe.org.

just don't create any database models until you learn normalization

That's how I learn.

bitme.org? sounds delicious, thanks

that is a reflection on your character, as much as you'd wish to tart it up.

what is this normalization you speak of? I can normalize a wave function, but not sure about a database.

It's a systematic process of removing redundancy (in short) from a database. Google "database normalization" and you'll get more links than you want, although the first few should be good

all database goodness flows from the sweet springs of 3rd form normalization

kk thanks

abandon all hope, ye who don't normalize

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

I was afraid I'd end up learning a whole lot… Not that that is a bad thing at all.
I was writing an IRC bot (markov chain based), and my hash tables filled up memory (plus I had to worry about saving and restoring them), so I figured a database was probably the better solution.

probably
I'd be lazy and do it flat-file

retrieving information from a flat-file is going to be O(size of file).
retrieving data from a hash table is faster (much faster), and I'm presuming that so is retrieving from a db.

you have to explicitly create indexes to achieve such perf (generally log2n)

j85wilson, You might try SQLite if you don't want to have a SQL server running for your IRC bot…

I've already got one running for my webmail interface (but it handles itself entirely, so I never messed with the db for that).

ah

otherwise sqlite would have been a natural choice.

Also look @ the docs for MySQL indexing; you'll have to create indexes on attributes you wish to query, otherwise you've just got a giant flat file with some overhead

kk thanks bobross1

I believe the docs indicate this, but indexes are B+ trees on your attribute(s) and thus can be expensive to maintain on any data write (insert, update), so don't go crazy and index everything

hey guys … just wondering if anyone had a better idea for me on this query … its been about 15 min and its still running … table a has 1 field … aa …. and is 2000 rows. table b has one field .. bb… and has 10 rows… table c has one field .. cc… and that has 16k rows… what I
am trying to do is combine aa and bb when the combination exist in any row in table c.
this is what I have … SELECT CONCAT(aa,' ',bb), CONCAT (aa,' ',bb,'s'),cc FROM a,b LEFT JOIN c ON(c.cc LIKE 'aa%bb%') WHERE cc IS NOT NULL

yowza. is that LIKE statement supposed to be a join condition?

an example would be aa='my' and bb='dog' and cc ='my bloody dog died' …. i would want to then return back'my dog'
yeah bobross1 … thats why i think something might work better

Hola PHP Wizards… I'm having a hell of a time with wordpress (argh!) … Any help you can offer is greatly appreciated : here is the information: http://pastie.caboo.se/85855

but logically, i believe thats what i want to do
so I am inner joining a and b, to get ALL combinations …. the LEFT JOIN'ing table c to that with the like ….
and its still running :-p

you're getting a crazy cartesian product is that what you really want

for a and b, yes
then i want to select from that combination , IF my example holds tru
true

What is the exact thing for C — what is 'aabb'? right now that's just a string, but is that supposed to be data from the tables a b ?

"I've determined you're not very smart."
I've determined that you're a rude fuck.
Who cannot format your crappy SQL code.

k

But have fun with your incomprehensible crapcode.
Retard.

right …. aa would be 'my' .. bb would be 'dog' and cc could be 'my purple and brown dog died'

… that was a stream of invective the likes of which I have not seen for some time.

if 'my' and 'dog' came together in the inner join of a and b …. then if that sentence existed in c, i would want to output 'my dog'

lovely

you might try something like SELECT … FROM( SELECT a CROSS b ) INNER JOIN c …

as far as I see …. my query should logically give me the right result … i just dont know if it will take until I wake up
but that does not satisfy my condition of aa and bb existing in a string of c together

and then I am not sure if your C condition is what you expect, oughtn't it be ON c.cc LIKE( concat( a.whatever, '%', b.whatever, '%' )

I'm looking for something that gives users the possibility to easily add new columns to tables and add new relations .. all within a GUI which is usable by non-IT staff… any hints? this is for project management with rapidly changing and per-customer different tables…

ribht, but you just said that the LIKE as my join is why it is taking so long…

I believe that's called suicide
but if you like giving your customers guns with which to shoot themselves, you can install PHPMysqlAdmin and be done with it

well, right now they're on excel - and it can't get worse

navicat is pretty good

Well, my guess is the join condition is incorrect, thus you're getting a x b x c rows, which is probably not what you want. Grouping a x b together in a subtable solves that nicely, then you can focus on making sure C is joined correctly
also, since you are outer joining, you're getting EVERY row of a x b which is likely a BigNumber

oh *** i was thinking it did a and be .. and then did c

thanks, i'm looking at it

Why can't I have both a FOREIGN KEY and UNIQUE constraint on a column?
phpMyAdmin whines

SELECT CONCAT(aa,' ',bb), CONCAT (aa,' ',bb,'s'),cc FROM (SELECT a CROSS b) LEFT JOIN c ON(c.cc LIKE 'aa%bb%') WHERE cc IS NOT NULL
that errors ( I have never used a cross join, or nested queries like that )

padde, there's always ODBC + Excel

remember that LIKE 'aa%bb%' is a string — so it is literally looking for aa followed by anything followed by bb followed by anything; it's not using the value from the table

yeah thats dumb … i see why you did the concat… but it is still erroring before that

so, using excel as a frontend to a real database…

SELECT cc, concatted, CONCAT( concatted, 's' ) FROM ( SELECT concat( aa, ' ' bb ) AS concatted FROM a,b ) AS derived_table INNER JOIN c ON cc = CONCAt …

does this work well? can new columns be created? how does excel then show relations?
is it possible to work on the same tables simultaneously?

excel can read data only

i see

just pass in a select statement

Can't Access be jury-rigged to talk to a SQL DBMS and manipulate it via Access forms ?

access can read/write

SELECT CONCAT(aa,' ',bb), CONCAT (aa,' ',bb,'s'),cc FROM (a,b), c WHERE c.cc LIKE concat( a.aa, '%', b.bb, '%' )
look possibly faster?

openoffice also can

i moved it to another inner join with the LIKE in the where….

i'd really like to rule out access that would be another program which makes this company dependent on MS office stuff excel is bad enough

still not looking to run fast ….

openoffice then

my ndb tables are not being replicated, how do i fix this?

It's possible, I'd compare the runtimes of each to see

306.5 M combinations ….

confirm it is ndb

i'll have a look at its database functionality after lunch. thanks for the suggestions so far

i havent gottent it to complete yet to where I could compare

yeah, that's nutso. Silly question, perhaps, but what is it that you're trying to do? Maybe there's a better way

i just created the database and created an ndb table but i don't see it on my other nodes
ui just created the database and created an ndb table but i don't see it on my other nodes/u
how do i figure out the problem?

other sql nodes?
try to create the database there too
autodiscovery of database may not exist, I cannot confirm this

i have key phrases in a and b …. i have sentences in c ….. i want to list all combinations of a and b where there is a sentance in c which contains both phrases

i did create the database on all nodes first then i created the table and i only see the table on the node i created it on

did you add ENGINE=NDB

no, i set the engine to… ENGINE=NDBCLUSTER
is it supposed to be NDB?

ndbcluster works too
confirm it with SHOW CREATE TABLE tablename

padde, never used ODBC… sorry, but I use navicat all the time and I love it!

ok, in my user database, I have mail_admin@% (I think this is for roundcube), root@127.0.0.1, debian-sys-maint@localhost, root@localhost, wordpress@localhost, and root@mydomain.tld.
do I need three roots?!

depends

yes, this is my sql command: create table test_clust ( id int(5) ) engine=NDBCLUSTER;

hi ladies and gents, im having trouble with parse_str() … anybody have any experience with this? here's my code: http://pastie.caboo.se/85855

I'm never going to access this db from outside of the box it is running on, and so neither should anyone else.

do a SHOW on ndb_mgm to make sure those mysqld are connected to the cluster
all your 3 root are for the local machine, right?

yes.

actually i just did that and it says that the server i'm not seeing replication on is not connected to the ndb_mgm… why is this?

try restart that mysqld
if still not connected, then check if there is a slot in SHOW and ndbcluster option is enabled on the mysqld config

ok its showing now

old_text, old_id and old_flags. I'm trying to add a fulltext index on that table, but it won't let me.
# time mysql mediawiki -e "ALTER TABLE ep_de_text ADD FULLTEXT(old_text);"
Column 'old_text' cannot be part of FULLTEXT index

the problem could be that mysqld was started before the mgm server, so it did not join the cluster
what is the datatype of old_text ?

| old_text | mediumblob | NO | | | |

maybe blob not supported

UPDATE home_runs SET hr = 756 WHERE player = 'Barry Bonds'

kimseong, Hrm, ok… but why can this person do the same thing? http://lists.wikimedia.org/pipermail/wikitech-l/2007-August/032616.html
I'm trying to replicate his situation, so I can test the timings

maybe this person has a TEXT type instead of BLOB

ok i have another problem… anything i try to insert into that table keeps coming in as a null value… ie: insert into test_clust values (id='test');… then if i do a select * from test_clust it shows NULL for the column value

kimseong, that would be very odd, since the data itself requires it to be blob

do you know why everything i am inserting is coming out null?

You mean something like bigtext?

You aren't inserting it?

you are inserting the result of the comparison
id should be null, compare to 'test' gives you null
mediumtext since you have mediumblob
!m winblows insert

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

oh sorry i'm retarded, duh
heh, in that case it works

hello
I am thinkin to backup all my mysql's server onto a remote server. My problem is I need to define a backup regime but I have no idea what I should start by

Ok, here goes

I have seen scripts and software as Amanda, Bacula and http://www.debianhelp.co.uk/mysqlscript.htm
and much more
but what should I take into account? For example, all my tables are miysam and none is bigger than 1 gb

if my database usage grows larger than what i have DataMemory set to then would my database crash?
and i will not be able to insert more data into my database until i increase that limit?

the question is I suppose the normal procedures is by script or whatever to do a mysqldump of all databases, zip and send them to the remote's server

you will get table full error

but what about if a server does not have space enough in the local hard disk to do that
?
I should to run the mysqldump against the remote server

run mysqldump from the remote machine connecting to the mysqld server

is there a way to set it so that it could just keep using as much memory as it needs? i basically want it to have an unlimited amount of memory

no, the memory are preallocated

Kimseong, What about if I an handling important information and I need to encrypt them?

you can increase it in small quantum with a rolling restart of the nodes without downtime
ssh connection

well if i reach what is the maximum memory i allocated then when i change it do i need to restart the whole cluster thus causing some down time?

Kimseong, what about if these servers do not have firewalls

monitor the usage

what do you mean by a rolling restart that won't cause downtime?

Kimseong, ssh connection? So you mean I have to write an script than mysqldump run on ssl connectios because I don't want to do it manually

change the config in mgm server, restart it, then restart 1 data node at a time

that has to be done automatic

make sure the data node has been fully started before restartign the next one
mysqldump connect to the mysqld, the connection can be ssh encrypted
not ssh, ssl

how do you monitor how much memory all your ndb tables are using so you can upgrade the memory limit before something bad happens?

that sounds fine. Even so I am afraid of open a 3306 port because of the servers do not have firewalls. Is it possible to decide in my.conf who is able to access to mysql by IP i.e?

Firewall it out

run it on a non-standard port

mysql user can restrict the client host

hey, is there a mysql gui tool that will allow you to create php forms drag and drop that query and and post to mysql db's?
or rather a 'suggested' one?

guys, the problem is I have found with they have open the standar port without any protection and the servers connect each other without restriction… It is unbelieveble!!!
but what kimseong says it sounds good

what is PHP?

and maybe you can force that user to use ssl connection, and you need the necessary ssl certificates

the devil

php manual might have the definition

kimseong, even so they will continue working with the former users that are allowed to connect to the rest of the servers. Arghhhh. I don't know if some servers are open for everyone

~man
!man

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

!man fulltext

see http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html

nabfphp

We are not a backup channel for ##php.

does the MaxNoOfTables setting mean the max number of tables in a database or just max number of tables overall that ndb will support?

should be max number of ndb tables, but sometimes hidden tables are created

and I suppose mysqldump handles copies of table that are currently in use

is there a command or script that will give you your current ndb usage? ie: tables, memory, index memory, etc?

is not there a show database status as tables to know how big the databases are?
how could I know the database's size besides using the system's commands?

what's the speedest way to check if a table exists in a db? select count(*) from tab ? select 1 from tab where 1=2 ?

A258, show tables like '%partial_table_name%';

i checked : if db got many tables, this is not the fast one
show tables like 'tabname'

A258, I have a db here with 918 separate tables
It returns the table name in less than 1/2 second

mmm

doh
# time mysql mediawiki -e "ALTER TABLE ep_de_text ADD FULLTEXT(old_text);"
/tmp: write failed, filesystem is full

select count(*) from tab — 375ms
select * from tab where 1=0 — 215 ms
select 1 from tab where 1=2 — 195ms
show tables from db like 'tab' — 236 ms

why when I go to /var/lib/mysql I just find ibdata for innodb and not any information about miysam's table?

Because ibdata isn't for MyISAM

setuid, yes I know but where is the miysam's information then?

setuid - db has 90 tables, tab has 165 000 rows

A258, That's pretty small

select count(*) from ep_en_text;

+———-+
| count(*) |
+———-+
| 5365743 |
+———-+

dear! you are big
i found bigger table slow down a lot …

Depends on your keys, indices, engine

is there any online tool where i can like layout my db structure with diagrams?

i got an apache log table - not pk, no key, 11 000 000 rows, then i have to cut it
myisam

How can i compare the shema of two database?

ricknick, show create table table_name;

it sounds comparing manually.
any tools that i can use which tell that which tables is different or schema has changed

all dump 1000

DIY PHP loop the db

huh?

this will write the memory usage to the logs
run on the mgm client

where do i write that?
ok

most mysql tools can export db schema - you can use php editor to compare 2 exported schema

can try using information_Schema

which log file would that write to? /var/lib/mysql-cluster/X or?

cannot remember, logs on the data node if I remember correctly, log extension

is there a way to output that onto the ndb_mgm program instead of the logs?

don;t think so

kimseong, why am I not able to find myisam's files frm, myd in my default location for mysql's database (in debian /var/lib/mysql) I can just see innodb

show variable like 'datadir'
then search the subdirectory with the database name

good morning

happy yesterday

hi everyone. I have a timestamp column, but when I query I got a date time format. How do I return a unix timestamp when I do select query ?
thoughtful, happy yesterday too

mysql.com/date and time functions
you'll find a function that converts to unix timestamp (epoch counter)

arjenAU, so we need extra function for that ?

on input you should put a datetime in also, or use NOW(), or use the CURRENT_TIMESTAMP magic in your table create.
yes. it is stored as a 4 byte epoch, it's just the in/output that's ISO datetime.

arjenAU, ah great.. will take a look at the site. Thanks arjenAU !

if you just need the epoch always, and don't need the magic in the table create, you would use an INT UNSIGNED instead
then it's stored the same but you get it back the way you want it. but it's really not bad to use the function on output… harmless.

arjenAU, hm… I think that's the thing I want. You see, I'm using PHP and each time I have to use date('d-M-Y', strtotime($row["dateField"]))
that's an overhead
so everytime I need to convert to Unix's timestamp first

nah, you can stick it in the SQL

arjenAU, that's why. Thanks for the clue arjenAU !

With a .sql table create script what should one prefix comments with?

When I run mysql -u root I get ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
I'd appreciate any help

KanRiNiN, do you have the root password?

I thought I defined it.
I apologize, I'm a noob.
I'm the admin, so yes

if so you will need to add '-p' to your 'mysql -u root' command
that tells the mysql client that you wish to specify a password

so mysql -u root -p password whateverichoose

no, just "mysql -u root -p"
then you will be prompted to type the password

excellent. I'm following the Feisty guide, so it's been smooth so far

this way the password will not appear at any time in plaintext
I know of the show create table statement, is there a way to do this for every table in a database?

So for example, I get the ERROR 1133 when I run SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');
So I assume I need to do the same for my localhost name
and create a user/password for it?
again, localhost and newpwd are obviously changed

KanRiNiN, but that initial '' specifies a user with no username, is this correct?
try running 'select user, host from mysql.user' to determine all the accounts that mysql knows about

ah, so it should be SET PASSWORD FOR 'root'@'mylocalhost'?

if that's the user account you plan on changing the password for then yes

got it.

hi all
I need some help with sql

Quick question (I've googled and found conflicting answers)
INT(4) - is that 4 bytes? 4 digits? 4 bits? 4 parsecs?

4 parsecs. definitely 4 parsecs.