CheapWebHosting.co.nz

Domain Search | www. .com .net .org .nz Check ALL

I need help figuring out how to organize the tables that would contain the following relationship. A picture can be about only one of many possible subjects, and each subject can have many pictures.
What kind of relationship is that?

I need a very complex db structure for storing book information, i would sure like to know how amazon stores them :|

voidwalker, do you know sql hosting well?
is somebody awake?

i don't know sql at all, trying to learn the minimum to do my project

I think I'm only one or two steps ahead of you then.
I'll help if I can, but I'm trying to find someone to help figure out a complex relationship.

from 360ppl, not one is awake ? :p
i just need a perfect structure to store complex book information in a database

Hello i need some help with php mysql web hosting 5.0. I am using Win2k Advaced Server SP4 w/ IIS.
Client does not support authentication protocol requested by server; consider upgrading MySQL client
thats what the error is ^

old_passwords

i cant get ito the command lie either
eve though i am 100% sure i am using the password i specified

it is probable that the new password storage method in MySQL 5.0 isn't compatible with what ever program you are using to access it

update mysql.user set password=old_password('password') where use = 'user';

it worked when i used Windows server host 2003

and flush privileges

in a nutshell, your mysql client libraries are from an older version of mysql than the server
er, sry, that was to texta

so the client is the libraries on the computer that is tryig to access the file
or the software on the server that is trying to access sql

client libraries are on the computer you are connecting FROM
server is the one you are connecting TO
if i remember correctly, the change happened between 4.0 and 4.1

where is mysql.user stored?

in mysql database

mysql.user means table "user" in the database host "mysql"

ah
well i cant access the darn command lie
my n key is a bit broke T_T
does sql 5 work with php host 4?

ask some php people please
(and actually you shouldnt use php4 at all)

you have root access to the server?

yeah

try it from the commandline from the server

bad theme... bad talk...

4.0.41 is the latest stable isnt it?
5*
ok i set it to the old password mode
thanks for your help guy's :0

hi, i have problem, i have 4 servers, 3 in one data center, and one in other, i wanted to move all data, and today i changed database on one server which is alone in ngz to db on server wchich is on other datacenter, now i cant open site from old ip
there's biiiig lag

Is there any reason to use MyISAM rather than InnoDB?
Trying to figure out why MyISAM exists.

it's faster, it's older, and it does fultext search
and you can copy the data files directly safely
plus innodb is owned by oracle now (no, really0

I'm new to db's in general... is Oracle owning innodb a problem?

that's the whole point of the mysql plugin storage engine architecture
MyISAM does some things better, InnoDB does others

_tfr_, I seemed like the other things InnoDB does are huge things.

depends on what you need.
if you run an ERP system or something, the ACID features of InnoDB are huge things
if you run a website that gets 100khits/sec, speed is the huge thing.

ok.

btw, has anyone compared the speed of the MAX-CONCAT "trick" and a subquery that does the same thing?
since I'm running MySQL 5.0, i thought i'd solve it with a subquery, but i'm getting 2-second query times which seems awfully slow
is there a point to try the max-concat thing or should i be looking elsewhere?
have a table of stock indexes (23 rows) and another with index history (~100k rows)
SELECT i.indeks_id, i.d_abbr, i.d_name, (SELECT d_value FROM indeks_history h WHERE i.indeks_id=h.indeks_id ORDER BY d_date desc LIMIT 1) as d_value FROM indeks i WHERE i.d_status=0 HAVING d_value is not null ORDER BY d_abbr;
howdy Harrison!

hey tfr!

just the guy i might need

btw, has anyone compared the speed of the MAX-CONCAT "trick" and a subquery that does the same thing?
since I'm running MySQL 5.0, i thought i'd solve it with a subquery, but i'm getting 2-second query times which seems awfully slow
is there a point to try the max-concat thing or should i be looking elsewhere?
have a table of stock indexes (23 rows) and another with index history (~100k rows)
SELECT i.indeks_id, i.d_abbr, i.d_name, (SELECT d_value FROM indeks_history h WHERE i.indeks_id=h.indeks_id ORDER BY d_date desc LIMIT 1) as d_value FROM indeks i WHERE i.d_status=0 HAVING d_value is not null ORDER BY d_abbr;

you want to be reading weigon_'s page
hmm forgot the trigger for it
groupwise max

http://jan.kneschke.de/projects/mysql/groupwise-max/ http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

there we go
first link

quite often max-concat is faster than a subquery
it depends a lot upon the data

with the same limitation of "there can only be one"
if the max() matches more than one key, max-concat doesn't work

though i don't think your above query is really even a max-concat trick canidate
weigon_, well his above query won't work with duplicates either
you can do the above directly with a join and max()

http://jan.kneschke.de/projects/mysql/mysql-proxy-types.png
you did ask for replication stuff, right ?

yes, i want it to automatically do the right thing for reads/writes
so that you can have a pre-existing application work with replication for scalability without having to change the app

the Transaction Splitting there, I need a better name for it
can you give it a nice name ?

query shaping?
like traffic shaping, but with queries?

How do you LEFT JOIN 1 table to 2 tables?
that's probably a wrong question to begin with no? :-)

for a simple user management system that will not have large traffic and will make use of foreign keys, I should probably use innoDB right?
so do you have a table you want to query like: table id some data foriegn table id ???
and you want to show the data from the foreign table

jordanau, I will rephrase my question, When you LEFT JOIN, does that use the 'table' on the left, or the 'tables' on the left (that was specified in earlier joins)?

you are beyond my knowledge of mySQL

forget it, i will just write a crappy SQL that will give mysqld nightmares. there. that's what you get for not answering me #mysql :P

i have the link to the man page on left joins if you want it

jordanau, alright, even though I find mysql pages very hard to understand

Main Table LEFT JOIN Dependent table
http://dev.mysql.com/doc/refman/5.0/en/left-join-optimization.html

jordanau, thanks that's useful
but not specific to my question :/

jordanau, yes, innodb is your answer if you want FK

SELECT .. FROM aa LEFT JOIN xx ON aa.id = xx.id LEFT JOIN cc ON xx.id = cc.id LEFT JOIN dd ON xx.id = dd.id
The key is xx table. It's on the right of the left join with aa, and I want to left join it with cc and dd.

thank you

what data type is the mysql DATE?
I'm trying to get php to input the date, and I can only get nulls to get entered.

err, date But the ordinary input is a string containing 'YYYY-MM-DD'
E.g. 2007-06-15
.. in that order
but even an invalid date shoud end up as '0000-00-00'

alienbrain, that should work fine, assuming they are all the same join type, then they all have the same precedence and will be resolved left to right

HarrisonF, cool! thanks!
I actually was about finished rigging the tables to try it

thanks

I'm running "/bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/run/mysql/mysql.pid --skip-networking". However, I cannot connect to the server (locally). Running "mysqladmin version" gives "n/unix-p" "n/unix-p".
Sorry, again:
mysqladmin: connect to server at 'localhost' failed\nerror: 'Access denied for user 'root'@'localhost' (using password: NO)'
I wonder what may be the reason for the problem. Anyone?
I'm running the command as user mysql
(on a LINUX system)

try using 127.0.0.1 as the host.
no wait that's wrong

connect to server at '127.0.0.1' failed"

yeah

I don't want to connect via TCP/IP.

the servers is denying the user/password you're using

centosian, But shouldn't it ask for the password?

either its not the user/passwd you think, or the server's not set up to allow that
you might be getting the password from ~/.mycfg
NO)'

centosian, Hm, how do I specify the password? There's no ~/.mycfg.

-u=username -p

centosian, Weird, that works with and without "-p".
Also I can specify any username that I like, "root", "somecrap", etc.
The user names don't even need to exist.

if you set up ~/.mycnf right, you won't need the -u /-p
and it lets you log in?

centosian, it shows the version information

er, connect?
you can always check the server logs to see what it shows

I guess that it needs to connect to show the version information.

test with mysql instead of mysqladmin

When I run "mysql", then I get the error message "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)". However, with the option "-p", connecting works, as user "root" or any other user, even users that don't exist.
I also tried specifying "--protocol=socket" explicitly, but that doesn't change anything.
I wonder why the parameter "-u" makes such a big difference.
Also, I wonder why I can connect with user names that certainly don't exist on my system.

think about it

centosian, Sorry, I need to correct what I said:
Connecting with "mysql -u root" does *not* work.
"ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)".

what about mysql -u mysql -p
(using the mysql user to login is not a great idea, btw)
s/login/connect/

centosian, " mysql -u mysql -p" works (with empty password).
Why is using the mysql user a bad idea?

ok, so the mysql user has no password set.

Yeah, but "root" seems to have a password set.
(at least that's what I guess)

because you have permissions to write the mysql metadata tables directly (the ones in the database called 'mysql')

centosian, Aren't these permissions necessary for certain maintenance tasks (such as creating new users)?
I also wonder what's the difference between the user "root" and the user "mysql"

there no such thing as the "mysql" user

yes but generally you should use higher level mysql functions (like GRANT and DENY) to change such data

you are seeing the anonymous user, which is why you can login with any username
the root account (the admin account), is password protected, which is why you can't login as them

HarrisonF, How do I reset the password?

!man reset root password

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

you're right, I was confusing the mysql 'system account' with the users in the mysql user table
also, when you do connect to the server with mysql, type show grants
that should show you which users are setup on the mysql server

centosian, It says "GRANT USAGE ON *.* TO ''@'localhost'".
HarrisonF, What's the difference between the "root" account and the "mysql" account?

that doesn't look great. How about you do some reading about mysql users and GRANTs? I'm going to let HarrisonF take it from here

feklee, there is no "mysql" account
feklee, that is the anonymous user, (the blank user name tells you that)

centosian, Well, first I'd like to get things working. I followed the installation instructions.

he has no privileges

HarrisonF, Who's "he"?

the user

the anonymous uer
user

But do I need an anonymous user?

no
you can remove if you can login as root
do you know the root password?

HarrisonF, I've tried setting one some time ago. But that password is not accepted.
How can I reset it?

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

HarrisonF, OK, I'll try that. Thanks so far, also to centosian!
HarrisonF, I followed the instructions, but it doesn't work.
Access denied for user 'root'@'localhost' (using password: NO)" when trying to connect using "mysql -u root".
Ah, now it works. I have to run mysql as follows: "mysql -u root -psomething"
I used to try "-p something" but that didn't work.
Seems like MySQL is using an unconventional way to pass parameters.
I'm surely not the first one who has been confused by that.
In fact, recently I followed a demonstration of MySQL where something went wrong, probably for the same reason.
This is *very* confusing!

if you use the ~/.mycnf file, you don't have to pass the username and password, it reads it from there
it's also more secure (with the right permissions on that file) because the password doesn't go on the command line, where it can be seen by top and ps
"reads them from there"

feklee, it is because the password it optional, you should get promtped when you do -p something

er, you get prompted when you do -p. -psomething passes the password as password

HarrisonF, Yeah you get prompted, and afterwards you get the error message "Unknown database 'something'". This is confusing and very untypical behavior for a UNIX program.
Usually you're allowed to leave a space between the parameter and its value.

usually yes
one mo' time. Consider using the ~/.mycnf file

centosian, Certainly.
Or, the prompt (which may be safer).
(depending on setup)

you're getting it down now

Hm, suddenly things seem to be so simple. But when you're new to something, then you often don't know where to look and what may be the reason for the problem.

feklee, the issue is that the password is optional, how would it know the difference between the password and the next parameter?

how can I convert now() to unix timestamp?

just use unix_timestamp()

tnx

HarrisonF, With many UNIX command line programs "-psomething" would be equal to "-p -s -o -m ...". They should simply use two different parameters, e.g. "-P" which prompts and "-p something" which passes the password on the command line.

-P specifies the port
if this is your big complaint with mysql then you have a lot to learn about SQL

centosian, You mean there are many annoying incompatibilities concerning conventions etc.?

no I mean that the whole SQL rigamarole is one big ad-hoc inconsistency

I've heard about that

but I'm a purist, and there's always a distinction between what's beautiful and theory and what works in the real world in practice
"beautiful IN theory"
in other words, I use mysql too

Hm, do you say that MySQL is not beautiful?

it is beautiful

I would consider the command line problem "not beautiful". It's just awkward and confusing. What I would consider "not beautiful" would be if the command line syntax were not the same in the different MySQL command line tools.
Sorry, typo ...:
s/I would consider the command line problem /I would consider the command line problem not/
I.e. I don't care about the weird command line syntax, as long as there's a consistency and all MySQL tools use it.

I'm having a play with MySQL, but by default it appears to only allow connections from localhost, how might I change it to allow connections from 192.168.1.* ?

!man grant

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

thanks the_wench

thanks

hmh
got the query down to 0.3 seconds with a little help from http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html
to discover that actually I need two most recent values for each index
any good ideas for this? I'm sure I'm not the first nor last person ever to encounter this
I have a table of stock indexes, and then another table with index_id, date, value
now I need to grab two most recent values for each index (in order to display the most recent and the +- change from the next-to-last)
howdy domas

I need a very complex db structure for storing book information, i would sure like to know how amazon stores them :|

hey Indrek

any clues ?

apparently one of my best friends have met you once or twice in .ee

who?

how many lithuanians do you know?

i can think of 0 off the top of my head :p

heh, see
I'm not sure you'd remember him
he worked for ML

ok, can you help me after you finish your conversation ?

haha, you're void!

btw, wanna throw me a good idea for a query?

select 1;
my favorite

go work for amazon and steal their schema

TITLE AUTHOR ISBN YEAR GENRE SUMMARY REVIEWS[reviewer] COMMENTS COVER URLS RATING N_VOTES
lol
i also need to implement a Genre Tree like the one amazon has
in db

if you need just storing, then normalized is best

domas:

any good ideas for this? I'm sure I'm not the first nor last person ever to encounter this
I have a table of stock indexes, and then another table with index_id, date, value
now I need to grab two most recent values for each index (in order to display the most recent and the +- change from the next-to-last)

storing as opposed to what ?

store previous/next values in a row
or at least pointers to previous/next rows

http://www.mypastebin.com/?code=1985368159. This query bacially just hangs and doesnt complete, if i remove the OR operator and just have one of the AND conditions it works fine, pls help!.

have a table, that has last two values
this is evil, but you'll love me for this afterwards %)

okay, if you say there's no better way

first of all, getting two most recent values is a pain, which would require temptable magic anyway

domas storing as opposed to what?

viewing.
tfr's problem was exact example of how storing efficiently doesn't mean reading efficiently

i need everything, storing, viewing searching sorting etc

HAVE FUN THEN
though I can always offer...

I received a history file for those indexes, with ~100k rows. i suppose I need to write some script that fixes the data then

mysql.com/consulting ;-)

any help appreciated folks

scifi, try AND (rls_quarter='Last Quarter 2006' OR dvd_genre='Action')

domas what can you tell me for free ? ;o

he can point you to the paid services for free.

hmm the query ran but its seem to have ignored the dvd_genere='Action' condition and only displaying the Last Quarter 2006 results regardless of genre :s

I optimize stuff for free only when I use it myself %)

scifi, didn't you mean AND dvd_genre='Action' ?

so you don't read books?

well, amazon already exists, right?

hmmm. im trying to test a php/mysql script, the OR conditions in the php script use variables, so to test just the sql i have to use actual values

scifi, what you want from that? a report from Action movies in the Last Quarter 2006?

would you recommend mysql for dummies for a dummy like me? :P

wow that would be a great book title. MySQL for dummies like you, yes like you! -- and then uncle Sam with a dolphin in the cover

no. in the php script its AND rls_quarter='$releasedate' OR dvd_genre='$dvdgenre'. where '$releasedate' is any rls_quarter value if selected OR any dvd_genre value if selected from a form drop-down list. But because the script was hanging im trying to test it in just mysql admin to see if its the mysql and not the php

scifi, are you sure that there is no Action movies in the report? the way it is now show give both

i will try using those extra brackets u suggested on the full php script query and see what happens. if i dont come back for a while its because its crashed
yes there are action results as well, but also other genre results

scifi, probably because the other genres are from Last Quarter 2006 -- if you want only Action from only Last Quarter 2006 then you need an AND and not OR

it works on the full script!!
ty very much indd!
amazing what difference a couple of brackets makes

how i can to dump all datase in a file of way this file contain database description?
if i import over database the file, that create database structure ( tables, fields, etc... )
sombody can help me?

mysqldump --all-databases
that should get everything

databases is the name of the database
i understand

Understanding is a three edged sword

wonderful, that work very very nice, amazing

in a database can i have certain tables myisam and certain tables innodb... they all dont have to be teh same i take it ?

you can mix and match

yay
gracias

de nada

how do I see a list with my databases? in mysql console is there any command?

show databases;
:-)
Any luck Abo?

yup, thanks

np
I'm having some weird character map problems. My drupal site (recently salvaged from a RAID breakdown) displays all danish char's wrong. When I try to log into a new phpMyAdmin site (using English ISO-8859-1) my username 'internus_db_user' is rejected:Access denied for user 'çÃRâ¬Ü +â¬aÃa'@'localhost'
If I try another char set for log in - I just get a different kind of garbages
Access denied for user 'www-data'@'localhost' ?!???

soon, did you change versions of mysql and/or apache too?
getting access denied in the garbage sounds weird

quite possibly yes
essentially I dont know much about the files on the old system, as the RAID system was 'totalled' - no filesystem could be identified - so I dont rememeber which version of Apache, mysql, php

how was the data salvaged? From the raw data files, or from an sql dump?

sql dump
-- Serverversion: 4.1.11 -- PHP version: 4.3.10-16
thats from the sql dump

sounds like you upgraded mysql. The trick/best solution I think is to get the file into UTF8 before you slurp it into mysql

now mysql is version 5.0.38

yup

I could try the utf8 conversion

then you have to make sure to put everything else into UTF8 mode

yup ... thanks

proably a simple question... i'm trying to access a remote db on an internal network... i'm sure have the ip and port corect, but it keeps refusing connecton, even when i just try to telnet in to port 3306... what am i missing?

external

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

aha! thanks!
i had to change my bind bind-address from being 127.0.0.1
thanks

np

centosian ... btw - the problem with logging into phpMyAdmin could not be related to the new version of MySQL ... the phpMyAdmin is new as well - it's no making any use of the backup from old MySQL
It was installed from ubuntu repos

soon. It sounded to me like you had danish chars in the usernames which were making mysql mess up

nope
the username is internus_db_user

you said that it displayed danish characters wrong, soon

what table engine is better for drive tables with 30.000 register and one with 100.000 or more?

yes ...
sorry I was away there ....
I type in the user name - it looks okay - I hit 'Go' and it complains username and pasword are wrong and the username it reports is garbage or maybe not (re: www-data@localhost)
\me is afk 20 minutes
lol

0' in to a datatime colum

why wouldnt you be able to?

dunno

cohonen... sounds like a name from Dune :p

nvr mind

did you get it working?

jep
small stupid mistale
mistake

if you run a lock table command, does myslq prevent you from inserting into memory tables ?
if /i lock table x...I can insert into table x ok....but not table y (table y being a memory table)
all the same process
if I dont lock table x, than I can insert into both

there is no reason what so ever for a lock on one table to affect any other table

well yeah, thats why its baffling me
if I lock table x, I get an Error when trying to insert into tabel y.....soo strange

good evning everyone
Unfortunately I'm not really good at SQL (I made a decision to start learning though)
and I have an issue related to an upgrade from 4.x to 5.x
a SELECT statement that used to work just fine now seems to fail

Probably just new/better join behavior.
Change your syntax to use JOIN/ON form and not comma table lists in the FROM clause.

ok, I'll dig into that

or just use ()'s where appropriate in the FROM list.
Post the exact SQL and I
Post the exact SQL and I'll show you.

here or in private?
SELECT a.*, cc.name AS category, u.name AS editor, g.name AS groupname FROM mos_events AS a, mos_categories AS cc LEFT JOIN mos_users AS u ON u.id = a.checked_out LEFT JOIN mos_groups AS g ON g.id = a.access WHERE a.catid=cc.id ORDER BY a.cati
it's a Joomla component
the above works fine on 4.1.20

FROM (mos_events AS a, mos_categories AS cc) LEFT JOIN

ok, let me try it

The new behavior more closely matches the SQL standard.

aha
got that
well, as students say, "tomorrow I'll start learnyng [My]SQL"
thanks for the fix!

The other approach is to change the comma to JOIN -or- CROSS JOIN
You're welcome.

don't the LEFT JOINs do the job? do I have to change the separator of the FROM list?

The problem is not the LEFT JOIN, but the comma.

aha

if I have a file with multi-byte characters, how can I ensure that the data gets imported properly with a LOAD DATA INFILE? I'm guessing I'll have to change the charset of the table to something like UTF8 and then set the connection charset to UFT8 as well?

I need to update one table's column(name), with another table's column(type_name), dependent on the current's row's type_id
do I UPDATE with a subquery to the foreign table using the current row's foreing id?
as in UPDATE table SET name=(SELECT type_name WHERE type_id=parent_query_type_id)?
how do I set the current's row's id so it is available in the sub query?

bbs

UPDATE table1 a, table2 b SET a.Name = b.type_name WHERE a.type_id = b.id;

wow that was much simpler than expecte
I apprecaite it

well backup your data first
but I do something similar and that's the query I use

I believe that worked my friend...thank you very much!!!

np
so anyone on the multi-byte character stuff?

You are talking aout utf8?
s/aou/abou/

yeah, I'm wondering about the proper way to import a file with multi-byte characters in it.

hey, what am I doing wrong? i'm trying to dump a mysql db

file.sql user -u root -p

after inserting the passwd I get an error
it's right?

file.sql to the end

and it's -u user -p

file.sql

lol
yeah
i fucked the user part
alrighty,, and now I have to put it back

you mean import the sql?

yes

mysql -uuser -p db file.sql
might need a -Ddb

you know i will do it trough my own comp

SELECT * FROM contents WHERE (contents.user_id = 205) AND ( (contents.`type` = 'Poem' ) ) ORDER BY created_at DESC LIMIT 0, 10

don't use select *
that's a start

a three key index will be the best ? user_id, type and created_at ?
shabbs, that's how rails works, can't do much about that.

hey .. i keep getting 500 internal errors. any code i can type in to 'reset the connections' and db querries
what is the command?

xx_xx_, 500 internal errors sounds more like webserver error codes, but maybe I'm wrong.

i think my host is somehow blocking me .. i ran a script to create profiles and i think they somehow stopped it

500 internal errors are a webserver error. you'll have to check your server logs to see what the actual error was

i ran a script to create profiles, and i guess 5000 is too many. it breaks the shared server. i think the max is only a few hundred at a time. www.pushtube.com is what i am working on
its very slowwww

xx_xx_, is the tattoo girl still available ? :p

lolol
sure, why not
its a "tramp stamp" ;P

xx_xx_, hm so you are adding 5000 fake profiles ?

yeah just to test

1750335 internet users on a shared host is pretty impressive

fuck
one of my clients has over 300 mb mysql db
it's drastic

that just means "internet users" on the internet, i didnt say on the site
plus its just a test site

What is wrong with a 300Mb MySQL db. Sounds pretty normal.

it's huge for me

I'd like to pull rows from my db based on multiple id #'s, how can I do that? Something like where id = 1,2,3 ?

id IN (1, 2, 3)

aha!
Thank you, ElDiabolo!

RaphaelZ, could also be because lack of maintenance

Which MySQL Version (The utf8 file)?
test

'tést'

I have several tables that all contain the same attributes. Is there a way to link the table schemas to one master schema. For example, if I change the type of one field, then the type gets changed in all of the linked tables.

sdz, you just merge everything within one table, easier programming also

It might not be as simple as that ...

sdz, like rails does it, single table inheritance, just define a 'type' column or whatever, that specifies what the content is about.
well several tables containing the same columns dunno.
it should be as simple as that :p

Can a table be renamed?

No.
Yes.

ElDiabolo, no what
ElDiabolo, what's an example of it not being simple ?

What's the syntax to rename the table 'products' to the table 'entities'?

rename table products to entities

Not shure what you mean by "Just merge everything...". The proper way would be to create a table that holds that column and maybe related stuff and reference it with a FK from all the other tables.

ElDiabolo, well at work I do it like that, and end up with 30 different tables refering one article table, with innodb references etc. I really prefer just one one table, with an type column set to define what kind of content it is, like poem story product etc. and just don't bother about that some columns are not used by certain content types.

Uuuuh. You have a large bag of name-value pairs.
?

ElDiabolo, I don't have to use SELECT *, although I do.
it's a lot easier to maintain at least, and not that evil in my opinion.

Writing queries on that gets pretty hard. Writing queries that perform may get even harder.

ElDiabolo, is a join faster then an index on 'type' ?
could make the type integer, if you want it faster :p

The problem with the name-value pair approach is that the joins are expensive.

ElDiabolo, what do you mean by name-value ?

No, I am not talking about miliseconds.
I am not shure if I got your approach correctly.
But as I understand it you recommend creating a table ..... ah, wait.

I mean I have a table name 'contents' which have like 30 column, 20 of them are used by every content type, where 'type' is defined in an extra column, and like 10 of columns are used by some content types and some don't use them at all etc.

You are talking about creating one table for all combo boxes ?

ElDiabolo, hm 'combo boxes' is a term I don't know in this context.
who was talking about combo boxes ? :p

That is where this is mainly used.

could be used there also in many to many relationships.
not good news for dba's though, maintaining a max of like 15 tables, doesn't sound as impressive as 300+ tables because only some columns are extra or defer

Putting all combo boxes in one table is within the range of sane design, putting all relationships in one table is not IMHO.
There have been attempts to get a long with one table.
Didn't end well...

ElDiabolo, yeah ok, but the 'contents' example wasn't about relationships, but I like the idea of products being content and be in that table, no use for letting things like messages and images within that table. so one table is not enough no.

Thats OK, keeping the nuber of tables small is a reasonable minor design goal.

ElDiabolo, what are the major ones ?
I'm not a dba..

Data Integrity and ease of application development.

ok to me the one I mentioned is not that minor.
ElDiabolo, I hate the design at work, that is using the extended table model, I have like 100 databases with the same cms installed, and they all have like 30 tables, because of the extention, and within those tables most clients have like 100 records total, that kinda s*cks
at the moment trying to bring that down to maybe 10 databases, where small sites share the same database and bigger clients get their own, plus not using that extended table model thing.

What is it that you call "extended table model"?
Why not 1 database? 10 is nearly as evil as 100.

ElDiabolo, the innodb reference on primary key to a 'parent' table. or non innodb types who are designed to behave like that.

Is there a way to select all attributes except some?

Unfortunately no.

ElDiabolo, because big clients have many sites, and other clients just one when a client get's more they get there own database.

You want SELECT * EXCEPT, thats missing.
But if you want easy management, 1 is the reasonable number of databases.
"extended table model" is referencig with foreign key constraints?

ElDiabolo, yeah ok, but 10 beats 100 also, and I care more about the bigger clients, a big danger could be revealing content from one client to an other.

Of course.

is there a way to modify the "long_query_time" variable without putting it in the my.cnf file and restarting mysql?

What's the best method for using MySQL for a general logger? Basically three columns will be used, time, type, and action (variable text since it can really be any length)

ElDiabolo, i'm defining indexes at the moment, and see that although I use a LIMIT, all records are always searched, at least it gives me the total rows when I use explain, that's how LIMIT works ?

Should it break the log up to have a table for each day so it doesn't overwhelm search algorithms?

Megiddo, probably you first have to determine how far back you want to be able to search in real time.

"real time"?
I'm still a bit of a MySQL newbie, I know you can do compiled tables though, is that what you're referring to?

Megiddo, hm I mean the information not being in a dump file when the user needs to get the information. and realtime isn't a mysql term, so sorry

Hi, I'm getting ready to set up an interesting system that dual boots windows and linux, I was going to set up a common storage area for both operating systems and I was wondering if there is a way to have mysql on each os share the same database (only one OS will be running)

Ah. Well ideally you'd be able to look up logs indefinitely far back. To give you an idea of the size, there's going to be a log entry roughly once every other second.

Where can I download mysql old versions? (win-4.0.20d especifically)
I already searched on google, but all downloads links are offline.

The problem is to choose a file system for that. And do set lower_case_table_names = 1.

yeah, I'm still looking into the best filesystem for that, thanks for the help though

Megiddo, is every entry intresting enough to be in mysql ? and 1 per second ain't that much, about a million records in 3 months still well searchable

Having the varchar won't slow it down much rhalff? That was my main concern

You can actually do that for years.
What VARCHAR?

What varchar?? I don't understand the question

Megiddo, you could just test it by generating a million log entries and see if it performs, first define some indexes though. without that it will be slow.
Megiddo, what are you logging with ?

The VARCHAR in "Having the varchar won't slow it down much rhalff?"

hi
UPDATE `ugml_alliance` (id,ally_points) SELECT ugml_users.ally_id,SUM(points_points) FROM `ugml_users` group by ally_id ;
how do something like that?

Because it can't search the table as quickly because each row is a different size. Correct?
Do you mean client?

Megiddo, yeah just what is logging.

how do i back up my sql database? phpmyadmin is not installed. mysqldump is broken. and i dont have root access. how do i backup to an importable file with the command line?

repair mysqldump

Don't care about that. Just avoid long varchars for indexed searches.

look into SELECT INTO OUTFILE

or use mysqldumpslow

i dont have root access.

Megiddo, if you are logging directly to mysql from your application, I would just use syslog and use their mysql backend, pretty flexible then.

It's a game server information. I'm sick of spending an eternity looking through plain text files

threnody. will try.

syslog-ng

or you can connect from remote host to database
or install mysqldump as user

This will be a public release, and the majority of servers are currently on windows

http://www.heute-morgen.de/modules/demos/sqil/log.sqil

Megiddo, the majority of webservers is not on windows.. :p is it ? :s

jacekowski, mysqldump has the prob that it cant lock the db. dont think from another system using mysqldump will work.

mysqlhotcopy?

--lock-tables off.

threnody, never heard of it. jacekowski that could do it

Alright, thanks for the advice E1Diabolo and rhalff. I'll look into syslog and see if it will work for my purposes.

Megiddo, syslog-ng

threnody, how do you specify a host in mysqlhotcopy?

Sorry, has to be localhost. http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html
can you ssh as non-privileged user?

oh its a shell script not a binary
might work
Cannot open dir '/var/lib/mysql/': Permission denied at ./mysqlhotcopy
erm didnt mean to hit enter
mysql/user' and hotcopy line 295

mysqlhotcopy db_name [/path/to/new_directory]

threnody, does nothing to the error.
using /home/user/subdir/ or ./ or /somepath/filename.sql

hi

well, the real issue appears to be your access to your database on some server. If that cannot be resolved by the legitimate server root, I cannot offer any help.

and ive tried the INTO OUTFILE using php, it just doesnt do anything

INSERT INTO `ugml_alliance` (id,ally_points) SELECT ally_id as id ,SUM(points_points) as ally_points FROM `ugml_users` WHERE ally_id != 0 group by ally_id ON DUPLICATE KEY UPDATE ally_points=SUM(ugml_users.points_points);

Invalid use of group function
how make this?

is there any way to substitue INTO OUTFILE 'filename' with displaying the contents onscreen?

so you have php
so what is the problem to use phpmyadmin
but try use into outfile 'filename_in_your_home_directory'

anyone up for helping me with a crazy join?

but this directory should be chmod 777

jacekowski, can that be installed as a user? not as root? and the into outfile thing is $query = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName"; $result = mysql_query($query);
and doesnt create a file, nor an error. despite the target dir being mode 777

crazy join is not standard sql.

i modeling a forum. the tables are discussions, discussion_messages, and authors. it is required that each discussion have at least one discussion_message. Each discussion_message has an author. The author of the FIRST message for each discussion, is considered the "initiator". I would like to do a query where i select discussions along with the "initiator" for each discussion. i think i can do this by joining the table on itself, but
Xgc haha. SELECT * FROM t1 CRAZY JOIN t2.......

returns all records in the known universe.

phpmyadmin can be installed as user

http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

jacekowski, thanks. trying that

subquery, i was afraid of that. thanks!

Yes, but it doesn't need to be correlated.

just put phpmyadmin to any directory
modify config
and use

what does correlated mean?

thanks

oh i see the second example... got it. thanks!

The first example in that URL is correlated. Don't use that form.
Read further.

great, thanks

Ok, can anyone tell me what I can do about when I try to open mysql, I always get the error that it can't find /tmp/mysql.sock

You're looking in the wrong place; the server isn't running or the socket isn't accessible.

I checked with a ping to the server, the server is running
but, what can I do about the socket?

What socket?

mysql.sock

Do you know you have one?

oh....wait.....nevermind, I'm an idiot
thanks, I think I've got it now
ok, now it is up and running but, for some reason, I always get the error that access is denied for my @ localhost

on the server running mysqld, do 'ps aux | grep mysqld'

I'm running on Mac OS X 10.1.4

that will show if the server is actually running

it is running, but now, for some reason, I can't access it

then you have password issues to mysqld

great.....any idea on how to solve those issues?

http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html
I prefer the solution that starts at "...Alternatively, on any platform, you can set the new password using the mysql client(but this approach is less secure):..."

alright, I think I'm going to try the one for unix platforms given I'm running mac os x

it's a pita but it happens to many after an install.

ah, ok

hang out here you'll see it all the time

ah, ok, so, is this just some common bug then?

hello.. is there a way to run 2 separate mysql servers on the same machine? If yes, anyone know if there are any serious downsides to this?

odd, it says to connect to the mysql client to perform those steps but, I can't even do that

Can a mysql_fetch_row() and a mysql_fetch_field() be performed on the same $result variable from mysql_query() ?

I think it's finally working! Thanks so much!

sdz, do not cross post, it is counter productive

There are people who are not in both channels.

is there a way to concatenate a whole column into a string?

you can only concatenate columns (fields)
php or perl might be able to do what you need to do

yeah but it would be nice if it worked server side

Ok, who can explain this? "ERROR 1045 (28000): Access denied for user 'nathan'@'localhost' (using password: YES)"

you cannot concatenate on different rows
make sure you specify the right password.

I did

!man grant

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

obviously not
that page might help you.

oh, wait, do I need to grant user "nathan" permission to connect?
er...the privilage, anyways?

you're on the right track
read the page.

you can concatenate on different rows: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

oh, nice
I stand corrected.

boo yeah it is nice

my apologies

np

That would be helpful but, I need to be able to connect to the mysql client to be able to do that. As it stands, I can't even do that.

try usign the root account.

I did, I even set it like you showed

!man reset root

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

for some reason, that's not working

http://www.google.ca/search?q=reset+mysql+root+password&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US:official&client=firefox-a
try that.

alright, thanks. I've been using Oracle for quite some time but, throught I'd try moving to something open source....seems a tad difficult though, heheh

never used oracle, but it seems like it has a lot of features that mysql implemented only in recent years
when did mysql 5.0 come out?

yep

about a year ago.

unfortunately, it was much easier to just set it up. MySQL has had me going every which way just to try and get it running

most distros have a README.mysql that you acn follow.

yes, I've gone through that, followed every step. Unfortunately, I still have these issues
maybe I'll try uninstalling it and trying over again
ugh...sometimes, these database programs can be quite frustrating

ERROR 1005 (HY000) at line 11: Can't create table './bookdb/book.frm' (errno: 150)
http://pastebin.com/930472

Foreign key constraint is incorrectly formed

Have you tried connecting as the default root user and not nathan?

[intra]lanman: what's wrong with it? this makes no sense

i dunno, but perror told me that

Hello fellow internet addicts! - - Are we all looking forward to another Long & Glorious weekend of irc? - - are you all looking forward to getting your "Monitor Tan" again, getting paler by the second, more stomach hanging by the second, via sitting in the chair all day eating pizza etc
Hello fellow internet addicts! - - Are we all looking forward to another Long & Glorious weekend of irc? - - are you all looking forward to getting your "Monitor Tan" again, getting paler by the second, more stomach hanging by the second, via sitting in the chair all day eating pizza etc

ccvp`: fuck you

Evan in Vermont, "Repping" "tha" VA
lawlz

I'm not addicted, I could stop any time!

could anyone help me import a file with multi-byte characters into a table?
nothing i've tried seems to work

hello, i was wondering if it is at all possible to remove "everything" from a mysql server so it is back to its original state

what os?

where no root passwords, tables, information or anything was set
debian

delete everything from your datadir then run mysql_intstall_db

thanks

that'll do a 'factory' reset

sweet, thanks
i will try it in like 4 minutes
brb
bak
so what would the datadir be?
just /var/lib/mysql?

yeah probably

WarpedCoders:/var/lib/mysql# ls
debian-5.0.flag ibdata1 ib_logfile0 ib_logfile1 mysql mysql_upgrade_info warpedsql

depends on the setup, but that's the default in most cases

i located it threw my.cnf

yep

alright

that'd be it then

sweet
worked like a charm
thank you for your help

np

WarpedCoders:/var/lib/mysql# mysqladmin -u root password 'test'
connect to server at 'localhost' failed
'Access denied for user 'root'@'localhost' (using password: NO)'
hrm

mysql -uroot -ppassword test
^
(sorry shabbs )

I think he's trying to set the password

alright thanks
that did set the password
and gave a bunch of other infromation lol

then login with a password

already did

NO) means you didn't specify to use a password
give it the -p option and it will prompt for a password

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
grr
mysqladmin -u root password 'new-password'
mysqladmin -u root -h WarpedCoders password 'new-password'
thats how it told me to do it
and it gave me that error
and that is how i originally did it when i first installed

was the server off when you deleted everything?

neg :P

might wanna do a restart of it
ARGH

you're inventing syntax
mysql -uroot -p if a localhost connection
mysql -uroot -p -h legitimate_hostname.com if a remote connection

i am inventing syntax?
i ran the commands that mysql_install_db told me to run

mysql -uroot --password=yourpassword

i have bigger problems now
lol

then you will be on the mysql command like

i got mysql working though

*line

could you help a brotha out with a charset problem?

'mysqladmin -u root password 'test'' is not correct syntax

mysqladmin --help
that will show you the flags... your flags are wrong

charset and collation are the devil's business. There are no such solutions.

bah

mysqladmin -uroot --password=password
what is your question?

I'm pretty sure I have a file that's in ucs2, so I set the table and all the columns to ucs2, but if I don't set a charset for the connection and run 'load data infile...' the inserts fail. if I do a 'set names 'ucs2';' it gives me a syntax error on load data infile, probably because the command itself is not in ucs2

http://dev.mysql.com/doc/refman/5.0/en/load-data.html
there is an option for character set

hrm
I spent an hour on that page and totally missed that

haha
well there you go

hrm, still no go
now it says 1 row affected
with 11 warnings that data was truncated

how to check which path mysql using of connecting ?

at least before it got the number of rows right :P

show warnings;
as in the path of the binary? `which mysql`

| Warning | 1265 | Data truncated for column 'namex' at row 1 |
| Warning | 1261 | Row 1 doesn't contain data for all columns |
u| Warning | 1261 | Row 1 doesn't contain data for all columns |/u

not sure what your question is really

except there are 77K rows in the file
not 1
Query OK, 1 row affected, 11 warnings (1.83 sec)
1 Deleted: 0 Skipped: 0 Warnings: 11
that was before it

and you're using the same terminated by and etc etc?

yep

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)

ps aux | grep mysql
mysql is probably not on

this is what I get if I don't set the load data charset:
Query OK, 77882 rows affected, 65535 warnings (3.84 sec)
77882 Deleted: 0 Skipped: 0 Warnings: 856699
:P bit of a difference

or your socket_file = blah is set wrong in the cnf file for client / server
yeah a bit

I'd bet mysqld isn't running.

sonuva
"Note that it is currently not possible to load data files that use the ucs2 character set." - from the manual

me too, but I've seen the socket_file set incorrectly before as well
so what
is the deal with this charset you are using? whynot use something else

ucs2 is sort of old school, innit?

my keyboard is all jacked today.... it's delaying my typing :/

it's a CSV dump of another database I got as part of a project. I don't even know what the source rdbms was

dump it back at them. Tell them you have standards to maintain.

0 grep

that means your mysql sever hasn't been started.
what is your operating system?

I think the problem is is that their database doesn't do any other charsets. they're in the middle of migrating to something else, but i have to work with what they gave me
anyway to convert a file from one charset to another? :/

rhel5

as root 'service mysqld start'

threnody:it does'nt concern if i installed it at /usr/local/mysql or ?

if you assumed root by doing 'su -' you should be ok

unrecognized service?

why do you think it is installed?

sorry to ask is that command for checking installed or not .. like "rpm -qa | grep mysql " or "rpm -q mysql" both reply mysql-5.0.22-2.1

as root 'yum install mysql mysql-server'

normally yum installs latest .. but i need some older version ..

well, you could try '/etc/init.d/mysqld_safe --user=mysql &'

gui

GUI tools can be found at http://dev.mysql.com/downloads/gui-tools/5.0.html phpMyAdmin at http://www.phpmyadmin.net/ and even navicat or http://www.webyog.com/en/

hooray
found a charset converter utility

what charset converter?

it's called Textpipe Pro
evaluation version, but i only need it for this one file :P

iconv is the common one
available on most unix

I'm on win

maybe an editor that supports multiple charset can do it

like I said, I found this one, it did the job.
hopefully won't have a need for it ever again
success! it are the imported.

I use MySQL 5.0.41 for windows. What command I must use at MySQL Command Line Client to set a password to the root user?

the instance configurator should've asked you to set one during setup
!man securing initial

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

bah
!man post installation

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

yes, it asked. and I've putted the password. but when I try to use phpmyadmin, it gives me an error, I don't remember what but it was like "10003 root @ localhost Using password: NO)
Then, I leave the root user without password. phpmyadmin worked.
now when I try to connect my php script to the database, I receive this error:

`hex: Using password: NO means you're not supplying it with a password when you're trying to lo in

let me check
Yes, I am supplying the password to login
#1045 - Access denied for user 'root'@'localhost' (using password: NO)

how are you supplying the password?
because its still not picking it up

is there a way to encrypt information in mysql IE 2 way encyption instead of crappy md5?

AES or DES. DES requires openssl to be compiled in though

heh. crappy md5?

!man crypt functions

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

stupid bot

Horrible human

!man crypt

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

argh
!man cryptographic

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

...
!man encryption

see http://dev.mysql.com/doc/refman/5.0/en/secure-basics.html

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

gah

the_wench

ToeBee for the win

I am going into the phpmyadmin user accounts manager nad setting it up. Now, I tried to reconfigure instance and it's opening a new window called "connection error" and giving me the same 1045 error.
*and

hmm wonder why it came up with 4.1 from google. replace the 4.1 in the URL with 5.0 for the 5.0 version

ahh so i can fetch data from mysql through an encrypted tunnel then

that's why I asked how to change the password using mysql command line client, because mysql instance configuration wizard isn't changing the password
it's just giving me the 1045 error

update mysql.user set password = password('newpass') where user = 'somedude';
?
then flush privileges

`hex, it's also possible that if you don't specify a password it logs you in as an anonymous user, not as root.

does mysql support encrypted connections or not?

if it has openssl compiled in yes, it understand ssl connections

will my webhosts mysql server have that? i mean it's a proper host

http://dev.mysql.com/doc/refman/5.0/en/secure-connections.html

it depends on your webhost

ask them

the mysql version is 4.1.21

if all else fails you could always setup an SSH tunnel

yeh my original plan was an ssh tunnel
but i realised this would be much easier
because i have to run it from my php script

once you're logged into mysql run this query: show variables like '%ssl';
that'll tell you whether they have ssl enabled or not

have_openssl | NO |
disabled then..

[intra]lanman where's is this?

soundsl ike it

shabbs let me check again

prompt

is that what you're asking?

I need to type that on the mysql prompt?

the reason i'd prefer doing it this way is because windows doesn't support ssh so i'd have to install cygwin and openssh and it's a lot of hassle

`hex: yeah

if it was up to me the server would be linux but it's not

`hex: obviously changing the password and the user to your specific values

well if your host doesn't support it then there isn't anything you can do about it

i can ask him to change it

yep

hes a reasonable guy

sure, go for it. if he's willing, good, but you're not his only client probably and he's probably not too keen on messing with a working setup

yeah i know, but surely theres no risk and it's just a matter of changing a setting from no to yes

not necessarily
he might have to do a full recompile of the server if it wasn't compiled with ssl to begin with

oh :/
theres no way he'd do all that just for me then
but secure connections is such a vital component surely he would appreciate that

well, you said he's reasonable so, can't hurt to ask

i have to go through all this trouble just because SFTP doesn't support raw commands

alright well, good luck all

is that necessary to intall a rpm version of mysql server and client for every redhat linux enterprise or can i install like "mysql-standard-4.0.23-pc-linux-i686.tar.gz"?

just like any software... you can install it however you want. A package manager like rpm has some advantages and some disadvantages

hi guys, i need some help figuring out how my database should look for some job, could anyone help me?

Hey Hey
Is there a query that is SELECT FROM table WHERE w/e and that it will not allow duplicates?
Like, I have 6 rows with the same value in the same colomn, how can I only get ONE on a while loop

select DISTINCT field1 from table...

hmmm
lets see
hmm
SELECT DISTINCT cat FROM pics
cat is the column
Toobee?

Western_Digital, yes. if you need more columns you'd have a problem.

nono
but it doesn't work!
WAIT
yes it does!
wrong line
- n00b
thanks!

yepyep

Hi guys.
Let's say I don't want my table using auto_increment IDs. Is there something built into MySQL that would allow for generating some identifier that is not linked to the order in which rows are created?

Use a trigger and the UUID() function.

is it possible to write a mysql function and grant a user that function on a table?
i know about table grants but is it possible to write the custom function and grant it to the user on that table
on a table*

if i have a list of prices is there a way to select both the highest and lowest in one query?

select min(price), max(price)?
maybe a where productid =.....

thanks, my book failed to mention min and max -- it works now

lovely
what book did you get
i wanna make sure not to get that one

don gosselin php programming with mysql thompson course tech
to be fair, i guess it's more for php

ahh, so it's a php book that has mysql
that makes sense

I have a bunch of entries with time stamps in a table. I want to search and group them by time frame. I want to put a limit of an hour on each group of entries, like if there's five entries all matching a certain criteria, four happened within an hour of each other but the fifth happened the next day, I want one group for the first four and a second for the fifth. Any idea what I'm talking about, is it possible, and wh
at's the process called so I can read about it?
What about just listing all the lines with a timestamp within one hour of x?

can anyone give me a hand with my MySQL install? I'm having serious problems, and I don't have a remote term into the M$ box that it's installed on

anyone setup mysql syncing in few regions before?
i am building a content delivery network but to have mysql synced up in multiple location is a headache

I have it running from dallas to atl
multi-master (circular) replication between 3 servers

how is the performance?

no problems so far
the atl site is just a hot failover, so it does not get much traffic
but the replication seems to work fine
9mill row database

ohhh hot failover
what i am doing here is checking on client source ip and send them to the nearest CDN
i am thinking to start to test it with vbulleting since it uses php and mysql

shouldn't be too difficult

so if somebody posted on SiteA, the information needs to be replicated across the CDN

what are you using for the balancing software?

It is a commercial product

we have some setups like that locally, using lvs&vrrp, but nothing that balances remote stations

this is global load balancing
and each CDN will have its own load balancer + cache before sending the request to the real servers

Related Topic: