CheapWebHosting.co.nz

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

uh, could someone help me with creating a column in a table that references the primary column of the same talbe?
i couldn't do it from the query browser
because i can't list the table i'm about to create...

so i think what i need is to restore my database hosting from my ibdata and .frm files

boobsbr, create table tree ( id mediumint sungined not null auto_increment primary key, parent_id mediumint unsigned not null, foreign key (parent_id) references tree(id) on delete restrict on update restrict ) engine=innodb;

ok piuiabacaxi, it worked
piuiabacaxi, are you from brazil?

boobsbr, indeed

de onde?

hi, i'm an cms mysql hosting and web n00b and just wondered the usual way of copying a hosted database on a webserver

rio de janeiro, boobsbr

i have OsCommerce running off a database
and just wanted to locate that database

piuiabacaxi, eu sou do espirito santo, obrigado pela ajuda

boobsbr, there is a ##php-br channel, mysql questions are welcome there as well

piuiabacaxi, okeydokey, tnx a lot

i was able to restore innodb following this.. http://forums.mysql.com/read.php?22,47078,51428#msg-51428
i was able to restore innodb following this.. a href="http://forums.mysql.com/read.php?22,47078,51428#msg-51428"http://forums.mysql.com/read.php?22,47078,51428#msg-51428/a

I want to delete some rows in a table if a timestamp is x number of hours old. I have tried DELETE FROM ispwatch_log WHERE time_created = '".$timedate."', but that doesnt seem to work
It deletes everything
I have tried less than and greater than, with no luck
Any tips?

hey i have this how can i select the reverse of this ??? SELECT distinct username FROM radcheck where username regexp '.(pri|mtz|vcl|cfg|ssp|cav|cmw|ltu|hlg|grm|scu|gtm|ijv)$' order by username asc
?

MACscr, delete from t1 where time_created = now() - interval 1 hour // deletes more than an hour old rows

Hey folks, I cant seem to get MySQL to use an index on a date column, even if I use the USE INDEX or FORCE INDEX hints... here's the structure and an explaination: http://pastebin.ca/559426 Can someone please give me some ideas?

endikos, the column must be as the first column in the index
endikos, or the other columns should be used too.
endikos, select * from t1 where b = 123 // if the index is (a,b) then the index isn't going to be used.
endikos, select * from t1 where a = 123 and b = 456 // this way the (a,b) index will be used, also if there was no b=456 in the where clause.

so I need to add individual indexes to all the columns, instead of one index that covers them al?

endikos, the index must cover the select, sometimes not only the where clause, but order by and group by too.
endikos, creating an index like you did is just wasting disk space and slowing down the inserts.

ok, thank you very much

endikos, select * from t1 where a = 123 and b = 456 -- if there are two indexes (a) and (b), only one of them will be used, so there is no point to individually index all the columns. index as you need.

for testing purposes, i tried: "DELETE FROM ispwatch_log WHERE time_created = now() - interval 1 minute", but no luck

ok, Thanks again!

woops
Forget it
Can i enter 1 day as the interval?

try it.

I dont have data that old =/
Howdy nils_

howdy

hiya
hey, I need to do a full mysql database dump
what's the right form of the command
mysqldump --alldatabases textfile; ?

mysqldump -A might do the job

just -A?

redirect the output to a file
yes, it's short for --all-databases

mysqldump -A OUTFILE /var/textfile for example?
into outfile
mysqldump -A INTO OUTFILE 'var/lib/mysql/backup';
didn't do the trick

is there a way to rename a database?

that's bullshit
mysqldump is an external tool

lol

RichiH, seems to be in 5.1 only.

then how can I get the whole cms mysql hosting to one file?
i need to change the server

with mysqldump...
you are mixing up shell and sql

haha, could anyone provide me the right command?

bleh

i haven't slept for 31 hrs and im really really tired

what is the 5.1 command?

dam... that sux.

file.sql

nope
it says there is an error in it

where should there be an error?

dunno
that's what it says

not exactly, I guess.
if you tell me it's "You have an error in your SQL Syntax" I'll slap you, I hope you know that

well i got it
shut up nils

hehe

come on,, my brains are working on coffein tablets
I had the right command in the beginning but i did it in the fucking mysql

switch to ephedrine or even cocaine

can you concatenate 2 columns in a select without using the concat function?

yeah, cocaine should do it

why would you want to do that? I think you can enable some options to use || as concatenation operator, I'll look it up.

becuase || looks better IMO

"looks better" ? who CARES

who cares? It makes sql hosting more readable

it's hard to change the server host because the god damn internet operator is caching the dns

set sql_mode = 'PIPES_AS_CONCAT'

Hi I have a table in utf8 that stores text parts of email bodys that I decode using pear mime decode. It seems that no data in inserted after some specific characters even though the insert that php does shows them. This clearcly had something to do with charactersets but i'm having a hard time grasping this. I know what charset the original text is in (pear's mime decode tells me). How do I perform the insert i a way that the charsets
are converted to utf8 properly. I've read the convert() function in the docs but i dont get it.

thanks

you can set that in my.cnf

i'm reading this book, but it keeps referencing COLUMNS, but describing them as a Horizontal thing.... I'm so confused!

hmm tcp forwarding might be helpful... for database server I'd rather use the actual IP.

anyone here running MySQL 5.1 in production? any probs with it? we are seeing versions like 5.1.16 having replication break.

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html -- maybe there is some more prettyness stuff in for you

the replication probs on 5.1.16 have to do with duplicate keys...

you are seeing duplicate key errors in show slave status?

yep nils_
let me go dig up the exact error.

thanks

that's weird... could happen with some odd transaction issues

5 [ERROR] Slave: Error 'Duplicate entry '4137854' for key 'fkCategoryKeywordCategory'' on query. Default database:
m'. Query: 'insert into tmtmCategoryKeyword (idfCategoryID,idfKeywordID,idfMerchantID,blnUse,bFloat) values (4137854,185902,5168,0,1
1582
5 [Warning] Slave: Duplicate entry '4137854' for key 'fkCategoryKeywordCategory' Error_code:
5 [Warning] Slave: Unknown error Error_code:
5 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave sql thread with
START". We stopped at log 'db1-bin.000355' position 330415989
sorry for the spammage...

!paste
!pastebin ?

oh that is a command?

pastebin ?

sorry.
ok ok

erm, no

pastebin

try http://pastebin.ca

no word boundaries ? silly bot...

no she juts likes me more than you.

I knew it had to be something like that

how would you guys like to organize a server change?

heh funny, a flood to explain how not to flood

all in one time or in series of clients?

well, we are with teh funny

http://pastebin.ca/559650
thx

Pallofooni zero downtime, of course

i mean that would you like to transfer all users at once or transfer the users in, lets say 10 users/day

well you could use some clients for beta testing the new system... better most times but it really depends on the situation

Pallofooni and I'm saying why would you want to do that ? what is the downside, in your situation, to either method ?

i think i need to get some sleep

I'd thinnk that's an obvious question you need to ask yourself first

yes

Hi, I have a fairly large sql file.. probably around 100mb and I was wondering if there is a quick way to split it into lots of smaller files so that I can upload it with phpmyadmin. I know it sounds like a lame way to do it but apparently that's the only way I can do it with my host.

yes there is

with the gnu split?

If you can dump it again, you can get it in db splits

bapsa 100mb is smallish - quite smallish, sorry

I just transfered a mysqldump of 842mb

on the 5.1 replication issue... we see this a fair amount. maybe once every 3 days on one of our 5.1 slaves.

ok lol but still it has to be split into a load of 2mb files right? Pallofooni, I am downloading a full database hosting backup using the backup facility on my host.. how can I get it to split with that?

afgh.. please don't ask me
my brain don't work

lol
is there no way to do it on my home comp
now i have the .sql file in front of me

bapsa can you connect to the database ?

is there no program that i can download which will split it for me?

burn it to a cd and slice the cd

lol
yes adaptr

really strange stuff, I think you can rule out writes on the slave, so why does it get it's relay/master log offset wrong... Did you check the Bug database?

bapsa then run mysqldeump from your local machine
*mysqldump

or print it and slice it...
can you connect to the mysql hosting server via tcp?

I'd print it in 24" letters on chain paper

haha

then fax it to the host to make them restore it

it'll be trucks full of toiletr paper

perhaps we have been too long on our computers

isn't that what IBM did when SCO requested them to sent them their AIX sourcecodes?

lol.. adaptr i don't think i can do it like that. I'm hosted on a shared server so i can't use mysqldump?

bapsa because..?

yes, checked the MySQL bug database and nothing that exactly matches our issue. i am not the senior DB guy at the company. i am a network dude but thought i would check IRC for help as the senior guy goes to Spain for a month soon.... ack

bapsa I *asked* you if you can connect to it, this has a vbery specific meaning

sorry.. i haven't done this before :x

as in open a TCP connection to the remote database server
if you cannot, then you're basically shafted

oh.. i'm not sure, how can i test?

why do you want to slice it in the first place?

erm... by connecting ?

hey

oh wait but i need to split it on my home computer
basically.. i download the large sql file from my server... i convert the phpbb2 database to phpbb3 on my computer. (i can do that fine)
but then i need to reupload it
and apparently i can only do this with phpmyadmin
which i need to split the file into smaller files for

my script runs in linux
but not in windows
Call to undefined function mysql_connect() in CDocuments and
Settings\Hennah_T\My Documents\My Albums\bot.php on line 7
De bot is afgesloten
anyone know why ?

you didn't load php_mysql.dll
check php.ini.

your php module lacks mysql sipprt
support, too

ok nils_
then what in the .ini
what should i add

search for extension=, there are examples already given, you just need to uncomment them

and thumbs i got php_mysql.dll

do you have those conditions randomly on all slaves or only one particular? As always with beta stuff it might be best to get the latest version if you might think you ran into a bug...
but obviously you won't want to do that behind the Senior DBAs back

nils_

a drunk guy who doesn't know anything about databases

extension=php_mysql.dll;
its in the ini

you guys given up with helping me already? ;p

no semicolon!

what did ##php say about it?

oh wait it doesn't matter

we have also tried upgrading from 5.1.16 to 5.1.17, 5.1.18 and 5.1.19 and when we do that the MySQL DB Daemon can't start as it says we have corrupt tables.... has anyone had probs upgrading from 5.1.16 to the newer versions...?

did you restart your webserver? what does phpinfo(); tell about mysql support?
that's nasty, which storage engine do you use?
maybe there *is* corruption

MyISAM.

it should be possible to repair the tables without the mysql server running using the myisamchk tool
you seriusly want to upload over 50 2MB files?

no but if it's the only way...
btw i can dump it on my home machine.. so I could use mysqldump and/or phpmyadmin.. can I split it like that somehow?

hmm it's difficult as it's text data you have to split it so no query gets truncated... Can you call shell programs via php?

hmm i'm not sure what you mean.. but if it's on my home computer then i should be able to do anything right?

the replication probs with duplicate keys never happen on our 5.0.X slaves. our master is 5.0.X the replication probs only happen on 5.1.X slaves and only to a few of them.

hmm so only on some special 5.1. slaves... how do you address the duplicate key issue? Complete resync?

nils_

nils_ accepts donations via paypal or wire transfer

i got another problem now

how unfortunate!
tell us about it

the procedure entry point mysql_thread_init could not be located in the dynamic linke LIBMYSQL.dll
xD

aiiieee!

i think so, we SCP over the tables from a known good slave and restsrt MySQL on the bad slave with the new tables.

oh well and don't forget relay logs and relay log index...

u know why ?

hmm wrong version of libmysql.dll floating around on your system

nils_ do you know how i can do it?

oh nils_

hi all
howto migrate mysql 3 to mysql 5?

well I mean on your host, if you can use phps system call you can upload your dump via ftp and then run a small php script that loads the dump via system("mysql database dump.sql");
php should ship one, get rid of any other on your system and try again

okay

but nils_ the database which i need to split is on my home computer, not on my host. i am running wAMP server on my pc
does that make it easier?

hardly

thanks for help.... i am gonna go confab with my coworker and see what he says. blessings upon you and your oxen.

I think splitting on your home computer is the way to go, better sneak it in the fancy shell way... there actually are shells written in php too!

any can helpme ?

but I don't know for sure, I only use windows for the IE 7 and that only when forced and drunk

hehe idd nickm_
nils_

nils_ is the official bot database trasher. He accepts donations via paypal or wire transfer (any currency accepted including beer).

i use ubuntu
but i need to get to work on windows

ok... well where can i find a php script that will do it for me?
uok... well where can i find a php script that will do it for me?/u

Hmmmm, websurfing with IE7 when drunk...

hard stuff. You probably gonna run into encoding issues
don't worry, virtual machine

______ :p

Innotek VirtualBox on Ubuntu here

nils_, :'(

mysql 3 and 4 don't care about encodings, mysql 4.1 does so it might double convert your data (for example you have utf8 stored in your mysql 3 db, then you load them into mysql 4.1, mysql 4.1 thinks it's latin1, boom).

aa

it's treatable. There is a default-character-set option for mysql command line client. It can get really funny with differently encoded databases on one server

btw mysql 3 to mysql 4 and 4 to 5?
in this steps?

I'd skip 4.0 and get right to 4.1

How small do your files need to be?

oks
thanks very much
i will try

hmm they need to be small enough for phpmyadmin to take them

:'(

i'm not sure exactly what but I think it's around 2mb

Ouch.

there are also some syntactical changes, so look at the what's new part in mysql 4.1 and mysql 5.0 doc and check for the word "incompatible change". Known to break: osCommerce and related junk (xtCommerce, zencart)
1.44M would fit on a 3.5 floppy disk

Xgc why ouch???
2,048KiB)

hmm you could put structure in one file and then work with max_allowed_packet...

Trying to chop up your SQl file into 50 or more valid SQL fragments isn't going to be fun.

there is no script that can do it for me? !

if max_allowed_packet is set to under 2M and extended inserts are used every insert should be 2M large

Unlikely.

then split by semicolon, there you go, but you'll still have to upload them one by one

nils_ are you talking about phpmyadmin
oh

mysqldump off course, which is available on your local computer

and if any single table requires more than 2MB, you have to pull that apart as well.

nils_ how will i do this "set to under 2M and extended inserts are used" - i don't know the command

Better to find a way to submit the entire file or to recreate a set of files in the right form to begin with.

Xgc are you saying I can't do it like nils_ said?

Your question involves an existing SQL file.

I know I have tried for 2 weeks but my host says that this is the only way to do it

mysqldump --opt --max_allowed_packet=2000000 --data-only [database]
can't you have your host load it?

SELECT person_id, fname,blah FROM person; does it show ALL person_ids or just person_id = 1?

all, you would need a where or limit clause to get less then all

hm how can i get them to load it? you mean upload it to my host account and ask them to do it?

If you can recreate the correct file(s), that might be your only option.

Does anyone know what Toplink is?

exactly!

ok nils_ I will ask them.. but they take 5 days to respond

the new drug addict meeting I do on wednesdays. It's fun.

and it's always no more than one sentence
(the response)

well maybe you should spend more on your hosting if your free time is important to you

nils, so it only shows 1 from the person_id row ?

it's gives you person_id, fname, blah for EVERY record in the 'person' table.
if you think it's not, it's probably because you only fetch one record from the result set returned by the query function.
and as always
!tell ezE about tias

ezE Try it and see, its quicker to type it on your system and try it than wait for one of us to tell you its ok

nils_ i would spend more on my hosting but they won't let me switch to another host

no shit i realized this thanks
not being sarcastic, i should just try commands myself

did they take hostages?

no but they won't let me transfer my domain
they took my domain hostage

hmm I know of a Dr. Joe Eze... He knows one of my relatives who has deposited like 22 mio bucks in nigeria and died in a car accident
at least that's what he wrote me

i will have to change my site domain if i am going to switch

sue the shit out of them, they didn't register it to your name?

hi @ all

nope they don't... look this guy had the same problem as me http://www.namepros.com/domain-name-discussion/211032-warning-do-not-register-names-hostultra.html

put a bittorrent tracker on your site
that's how I got rid of a hosting plan once

host russian kiddie pr0n and notify the fbi

lol
if i put a bittorrent tracker on my site they will probably delete my account and all my stuff
someone else had that problem

that would be illegal

oh

in most countries

i've probably just a little problem but i cant found a solution .. i can't connect to my sqlserver, which is running on a server in my lan. i get the message that "host.name.xx" is not allowed to this sql server ... have someone an idea

ok i mailed them asking to load the database for me... but when they respond it will probably be 'sorry we cannot do that' in 3+ days from now

.... sorry for my bad english ^^

]s0x[: well you are close. Look at the grant command
!m ]s0x[ grant

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

on the host you have to grant the needed privileges to the specific user@yourhost
great company.

eat the loss and move on. life is too short.

oky

nice business model, getting clients pissed of and then use their domain names to boost their pagerank...
start evil hosting company

lol

using your domain name infriges your copyright

Does anyone know how to make Toplink use the auto_increment feature?

what's toplink?

I want to do a "where threadid=1,2,3,4" etc - what is the correct syntax?

but whatever i do i get the message that acces denied for 'root' @ 'localhost'

threadid IN(1, 2, 3, 4)

HI
good afternon

]s0x[: wait you told me the server is in your lan how does the localhost fit in?

i connected via ssh

ok
did you somehow manage to forget your mysql root password?

I need some advice to setup a reporting server under mysql 4.1.20
my current configuration is two masters foo and var, and 1 replicating machine x, that's the slave of foo and var (two instances of mysql)
but now I need to setup another machine that must be the slave of X
what can be the best method to copy all the data in X's Dbs back to the new machine

oh it seems that i forget the -p param

Im talking about 100Gbs

it's nice how you adopt the master-slave relationship languagewise
so you don't want to dump I guess

y
that will take 4ever
to dump and then import

if some preconditions are met you can simply copy the datafiles directly, (require the server to be down)

Im using InnoDb
I think that I can be done

same version (or close), same arch (i'm not sure about that, endianess might not be an issue with mysql), same settings for innodb
at least the data_file_path and log_file_size

I have plans to use the same setup in the new machine (ie, same my.cnf) of X

just shut down the mysqld, I then tend use netcat and tar to get the data over to the other server, but it in the datadir, match my.cnf (only the replication stuff needs to be changed, write down binlog position before shutting down x)

This weekend I tried using mysqldump -u -P -h database | mysql -h -C -u
but for some reason it died

it takes forever or it even dies...

y, I was thinking taht

had to repair 4GB innodb table data once, updating from 4.1 to 5.0, took ages

can you write here an example of netcat?
I haven't used it b4re

it's easy
on the target machine you run netcat as server

any reason for choosing netcat v. ssh?

speed.

I usually use scp or rsync

nils_ ... yeah it works ... thanks for your help

you can also use that if you are more familiar with that

because of avoiding the encryption step?

well that might not be so expensive, just my observation.
less protocol overhead I guess

I used to play with netcat and tar - now I use rsync. But this howto on netcat tar might be of use: http://compsoc.dur.ac.uk/~djw/tarpipe.html

that's basically what I do

cool

hi all

tnks

i have a field:datetime how select only the date, how select only the time? thanks

bleh.
Having trouble with a left join....

not having so much data as hertzel, but this is not about who's datasets are larger

date(col) or time(col)

In a table, I have a cuid column and a snapshot column and various other columns - but these are the ones that I'm concerned with. I'm trying to compare cuids that are associated with a specific snapshot in the table itself.

ok

So its like a LEFT JOIN I'm sure but also taking the two joined elements, from the same table, but selecting them according to their snapshot

jeje
having tons of gbs of data is not funny to handle at all
uhaving tons of gbs of data is not funny to handle at all/u

SELECT transactions.cuid WHERE snapshot = "200705081239131513784179" AS oldtransactions, transactions.cuid WHERE snapshot = "200706101155351190215469" AS newtransactions LEFT JOIN ON oldtransactions.cuid = newtransactions.cuid;

try to do an optimize over that and tell me later

5.1 has partitioning, so maybe it gets less pain in the ass soon... on the other hand, considering that most don't even use 5.0 these days

y...
we have 20 servers all of them running under 4

I have a table with UIDs. I have another table called USERS whose rows are keyed on the UIDs. How can I select UID from table 1, WHERE uid EXISTS in USERS?
maybe an INTERSECT is appropriate here?

tnks for your help nils_ I will try it now

Does anybody have a more efficient way of doing the query than..
select subscriber from user_subscribers where subscriber=".$user_id." intersect select user_id from user
because that's pretty inefficient.

intersect?

yeah

zackattack, what does that mean? what result do you expect?

are you sure you don't want to join?

Maybe...
I have a table of UIDs.

hehe

I only want to select the UIDs that ALSO appear in the table users.

inner join

classic case

What's the specific query here, then?

select * from tablea join tableb where tablea.id = tableb.id;

oh. ok

does that interselect thing exist?

thx
yes

substitute the columns you want for *

in mysql?

yes
i got it from the manual.

poor search in the manual then No results were found for your query.

OK it looks like redhat donest have netcat
there's something similar?

yum install netcat?

or I must be forced to rsync
nope

hmm
threnody, this query is failing --
select subscribe_to from user_subscribers where subscriber=".$user_id." join user where user_subscribers.subscribe_to=user.user_id
I think I'll shuffle the first WHERE back to the end...
brilliant. fixed. thanks.

the DuBois Third Edition of MySQL doesn't have an interselect in its index.

Intersect.
not interselect.
http://dev.mysql.com/doc/maxdb/en/ed/1f2a40ce93185de10000000a1550b0/content.htm

ohh

Yeah.

anyway it's not available in the community server package, is it?

it does have an intersect, related to spatial relation functions
s/intersects/intersect/
different thingee

if you have a table full of duplicate rows (each with there own primary key)....how do you always pull out the second row ?

are you sure there are ONLY two rows?

zackattack yeah

select distinct (column) from table;
select distinct whtaever_column_is_the_primary_key from table;
try that?

well I mean theres 60 rows.....but each one has a row thats a copy
only 30 unique

right

the primary key is distict

so my query fails?
what're the columns

Does anyone here run MySQL on itanium? Any better than something like opteron or anything?

customerID, forname, Surname
1, Andy, Smith
2, Andy, Smith
3, Zack, Attack

you can throw in a lot of opterons for the price of an itanium I guess

got it
so why not do

4, Zack, Attack
etc

If you were comparing a LEFT JOIN like visually, would the data that you're joining to appear on the "left" or the "right" of the data that you're comparing from?

select distinct (forname,surname) from table;

Have you run on Itanium at all?

I won't even consider non x86 hardware if I'm not to scale up... Itanium seems to be pretty dead anyways
checked once on really old itanium but long ago

Zackattack, Im after removing the second columns, but not the forst

does mysql running on one host have the ability to pass off queries to another host?
I wanted to tunnel to another server via ssh, but doesn't look like Qt's sql can do that
thinking about using mysql to do that itself

you normally handle this in your application, federated is a way but a very special thing
!m BadMagic federated

BadMagic see http://dev.mysql.com/doc/refman/5.0/en/federated-description.html

agore, Ok.
I can't help you, sorry.

ok, thanks anyhow

can anyone tell me why select posts.* from posts Group by threadUid ORDER BY posts.uid DESC; doesn't return the highest uid value against its grouped item?

as far as I know it just doesn't work that way...
you want the last post in any thread?
why not select max(posts.uid) [..] group by threadUid?
ordering happens after grouping

thanks the_wrench, I was able to create and no errors given
but, a simple count(*) query returns no rows, and there are some on the remote table
a show create table lists the table as engine=myisqm, but he sql script used to create it has engine=federated specified
is there a way to debug the connection?

thank you ever so much, iive been pulling my hair out for hours!!

sounds fun

Is there a recommended book listing somewhere? looking for database design specifically, if anyone knows some great ones

SELECT CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central');
i got it straight from the mysql docs

Tm218, Named time zones can be used only if the time zone information tables in the mysql database have been created and populated.

ah

man this is frustrating
I'm using the ENGINE=FEDERATED option to CREATE TABLE but it creates them as MyIsam....
supposed to error out if something wrong with connection string or table federated points to not there....
but no errors, and no engine=federated

I get:

CREATE TABLE a (id INT) ENGINE=federated;

Unknown table engine 'federated'
Did you somehow add an engine?

is there a way to have mysql list the engines?

I'd check the documentation-- but I've never heard of "federated"

Federated tables are tables with storage in a remote server. When defining a table with this engine, you refer to another table in a different server, using the same structure. The only thing that resides in your local server is the definition of the table, which must be identical to the remote one, except for the engine specification.

http://www.oreillynet.com/pub/a/databases/2006/08/10/mysql-federated-tables.html

that is what I have, and I don't get the error, it create the table
I WANT the federated table. I need to be able to access a table on remote server, through table on local server, just what federated does
but uses the myisam engine

did you add the connect string to the table definition?
connection*

create table tab (id int(11)) ENGINE=MEMORY CONNECTION='mysl://root@localhost/fed/tab2';
Query OK, 0 rows affected (0.09 sec)
oops, just testing the engine directive with memory, hang on

connection='mysql://user:pass@remote.com:3306/world/City';

create table tab (id int(11)) ENGINE=FEDERATED CONNECTION='mysl://root@localhost/fed/tab2';
Query OK, 0 rows affected, 1 warning (0.09 sec)
needs the port?

show warnings;

Hello

Warning | 1266 | Using storage engine MyISAM for table 'tab'
like the show warnings, is helpful

Im having a problem with MySQL's spatial and C#. When I call a query "SELECT ASTEXT(geom) FROM..." Im getting "System.Byte[]" as a result in C# even though everything seems to be in order using QueryBrowser

there is a lot of data at the O'Reilly link I posted on federated tables. Perhaps it will be of use. I can offer nothing more.

thanks threnobdy, appreciated
grrr, show engines lists 'NO' under the FEDERATED row....

I would point out that the remote table has to exist, and the connection has to work.

ya, but now the question is how to enable the FEDERATED engine....

http://pastie.caboo.se/69629

Doesnt anybody knows nothing of the pstial expension?

I was reading on the mysqlimport client today. It expects filenames of data to be the same as table names. 'mysqlimport --local db_name table_name.txt' sort of thing.
are mysqld and the import data file on the same computer?

How do I set a foreign key in phpmyadmin?

diana, alter table t1 add foreign key (myfkcolumn) references t2 (id) -- donno how to do it in phpmyadmin tho

in the above example, myfkcolumn has to be indexed, or be the first listed column in a multi-column index.

hello
i need help with datetime field
i need search between years

!m il_padrino data and time functions

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

!m il_padrino data and time function

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

!m il_padrino date and time function

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

1998 and year(datetime) 2003;'

anyone dutch here by chance?

thanks

or use BETWEEN

between exact date is better, can use index

who knows how often are the relay logs rotated when max_relay_log_size = 0 ?

threnody, wouldn't it be better to do something like where datetime between '1998-01-01' and '2003-12-31'? just to take advantage of any index that could exist

year(datetime) - i need that xD

yes, but he asked for year specifically.

actually = and is usually better than between in year

threnody, the filenames *are* the same as the table names

but your solution is faster.

9' in the

threnody, and yes they are on the same computer

then you don't need the '--local' either

threnody, i didn't have --local in my paste

well, your error says it cannot find the file. Permission issue?

nope
running as sudo is the same
and root is the user running the command

the data columns in the file have to be in the same order as the table structure.

the file is read by mysql server, not the user if you use load data infile, with --local then it read the file as your user

threnody, they are.

your *.csv file is owned and readable by the system mysql user?

threnody, adding --local did something. but it's only adding one row, so i think i have to change the delimeters
got it

Then I need some educating. I thought 'local' referred to the use of a file on a remote client.

same as localhost too, the client will read the file, with local client, not much different from without local
other than permission issue

paypal sucks big time

so 'local' instructs the client to read the file and pass the data to the server?
even if the client and server are on the same box?

yes, just like the case of remote

ah.
so if client is launched by system user 'knucklhead', and that user has read permissions on the file, the mysql system user doesn't have to have privileges for the file.

local will work in this case

good, thanks.

How do I set a foreign key in phpmyadmin?

use alter table statment

kimseong, where/how?

you have to create the table using the proper syntax, or add the syntax after the fact using alter table.

use a full sql statement, don't think phpmyadmin gui has foreign key constraint feature

Why does this one return "#1054 - Unknown column 'Forum.lastpost_user_id' in 'on clause'" in line 17, but not in line 8? http://pastebin.ca/560125

thanks (yeah...i thought i could use the gui more...i guess not)

Almaron, I had this issue yesterday and my conclusion was that when you use this comma FROM `forums` AS `Forum`, here then the join can be done on the table after that join.
like you can join from `parent` but not from `forums`

Oh my god, that was right oO

read the join syntax of mysql 5.0
!m Almaron join

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

Error: The table 'worldcities' is full, when using table: worldcities"
disk space seems ok. why would i get that?

show table status like 'worldcities';

Almaron, did you get to achieve the same result in another way/

depends to what storage engine

data size vs. o/s? http://dev.mysql.com/doc/refman/5.1/en/full-table.html

I just switched 'parent' and 'Forum' in FROM clause
And it works fine now, thanks

Almaron garotosopa depends to which you want to join first, (a,b) left join c or a , (b left join c)

as Monica Lewinsky said from under the desk, "How big is that thing?"

stopped at 16MB
Data_length = 16MB

Almaron garotosopa 5.0 default to a , (b left join c), older version default to (a,b) left join c if you do not have the pranthesis
what storage engine? memory?

InnoDB

Great, thanks, dude.
Thank you, too!

look at the comment of table status

hmmm...sounds like a default max_allowed_packet size?

maybe your tablespace is fixed to a certain size without autoextend

kimseong, so it would be possible to (a,b) left join c on c.a_id = a.id and c.b_id = b.id ?

oh max_allowed_packet can be an issue
yes

no idea
how do i find out?
http://pastie.caboo.se/69639

great. many thanks

check your innodb tablespace config in my.cnf
usually max_allowed_packet issue don;t give table full error message

they usually give 'server went away' errors

but you have 100MB free in tablespace

http://pastie.caboo.se/69640
there's my config

it's just curious that the default setting for max_allowed_packet is 16MB.

innodb_data_file_path = ibdata1:10M:autoextend:max:128M
guess it had hit the max with your free space in the table status comment
maybe it hit the max and then rollback
remove the :max:128M

hi all

I have 22 results from SELECT email,uname FROM user WHERE email REGEXP '^.*.@gmail.com'; ... how would I just get the # 22 back?

kimseong, trying...
w00t
worked!
thanks guys

don't forget to thank the bots!

thanks bots

i have problem in setting up mysql password for 1st time on ubuntu linux

order by something, limit 1;

select count(*) from ...

hrmm

sorry, I misunderstood.

depends to your definition of # 22

oh duh
yea I'm just a moron

since there is no ordering, I bet it could be just limit 1

nevermind me

Ok, I'm accessing the backend of an ecommerce solution - I have about 500 tables (it's not a very good solution I should add) and in 5 of these tables, I have columns named "CUSTID", could somebody direct me to some documentation on searching for specific columns? Or what should I google for?

dot notation. table_name.column_name?
(500 tables! wow)

~11GB of text data... *sigh*

lol

I was hoping that someone could kindly tell me why this isn't working

I think I got it...

$category = $_GET["category"];
$sql = "SELECT * FROM product_yahoo INNER JOIN category ON product_yahoo.id = category.prodid AND '%$category%' = 'T' ORDER BY price DESC";
can't seem to get the GET variable to work

how are you passing the data to the page? Try $_REQUEST["category"]; that will check POST and GET

singlequote? $GET['whatever']

i've tried both

when will '%$category%' = 'T' ever be true?

why "AND '%$category%'

If $category is empty, we are left with AND '%%' = 'T'
.. which is false. If $category is 'T' we are still left with AND '%T%' = 'T' which is false as well

ROFL! 52 freaking tables with that column =-\ oh god does this suck.

nice challenge to unfrack. Charge them a lot.

the metadatabase INFORMATION_SCHEMA is your friend, by the way

you can't use wildcards with =, you need LIKE for that

.. if you would like to search for e.g. tables with specific column names

that is not a legitmate reference for a column name.

opps, i was kicked off
I might have missed that

that is not a legitmate reference for a column name.

oh, that too

is there a way I can use a variable there? --

links

what is it you're trying to do? what's 'T'?

normally web form users submit column values, not column names.

there is a table with columns of category names... if the row has a T under the specific category

but if it is a column name, get rid of the %%
and prepare to be hacked.
and use backticks, not singlequotes

how is that a security issue?

accepting a column name via querystring?

oh
well that did the trick
thank you very much
although, if there is a better way to do this
your enlightened input would be greatly appreciated

preventing sql injection is an education in itself

use a numeric equivelant for your columns then eval the number to see which column was picked

ah!

your php connection user should only have select privileges on the table(s) involved. Try to use only integers in GET and POST. Screen them using is_numeric(). Etc.

alright. thank you very much

http://ubuntuguide.org/wiki/Ubuntu:Feisty#Database_Server , help needed with setup of passwords

How do I link tables in mysql via phpmyadmin?

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

use join when you need them linked, other than foreign keys there is no other way of linking tables

kimseong, I need to link them by foreign keys.

apparently phpmyadmin doesn't automate the foreign key/references syntax, and she cannot do the directly sql query. She's asked a couple of times today.

if you use a recent version of phpmyadmin you can edit foreign key constraints. provided the storage engine for that table is InnoDB

arjenAU, where?

phpmyadmin has menus for it

ty threnody i check

above my pay grade, sorry.

structure for a table, then click on "relations view" which is at the bottom of the list of columns

there's instructions at that link on setting mysql root user password using mysqladmin.

there you can define the eferences, and any on update/delete cascade magic
?

lol, I dk phpmyadmin.

?

American slang.

thernody , http://paste.ubuntu-nl.org/25178/ this is little problem :/

notice how you end up writing more to say less? is that really efficient?

ignore.
I rarely think of efficiency on irc.

clarity at least!

arjenAU, ok, I check the box for categories table(I want to link this to entries), and I click on "structure" but I don't see any relations view

an excessive devotion to efficiency might result in shortened horizons.

i have a 2.7.something version. yours may be too old
on irc, clarity and context is most important.

I'm using mysql 5.0.37, phpmyadmin 2.10.0.52
I'm using mysql 5.0.37, phpmyadmin 2.10.0.2

or clarity.
but I will withdraw for the evening. Ciao.

sounds ok. well, I can't see what you see or don't see there. however, it may be that your tables are not InnoDB but MyISAM. in which case phpmyadmin knows that you can't have foreign key constraints. go to operations and change the tables to innodb, if that's enabled.

arjenAU, yea, they're MyISAM. all 4 of my tables

there ya go then
that's just like tring to find the petrol cap on a bicycle, it's not going to be there

arjenAU, How do I change the 4 tables to Innodb?

can a mysql datetime field handle and convert a unix timestamp - or do I have to put it in the datetime format?
diana, alter SQL and TYPE maybe...

I don't have Innodb storage engine listed.

are you on a shared host? some do not allow it

I'm using XAMPP, I'm following a guide on this book written by Jono Bacon.

if it's not an option in perations and changing storage engine, then the server might have it disabled. if you go to the server variables overview from the phpmyadmin mainpage, yo can see with the have_variable being no, disabled or yes.
xampp should have it enabled i wuld think

FROM_UNIXTIME
!m lig date-time functions

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

!m lig date and time functions

lig see http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
lig see a href="http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html"http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html/a

arjenAU, It's disabled.

thanks... wasn't sure if it could handle it automatically

then that's smth to fix in your xampp configuration

arjenAU, "smth"?

something

is there much point in using 64bit mysql over 32bit ?
Im guessing its not limited to 4Gb, but apart from that ?

should be faster in 64bit

kimseong, really, way is that ?

there was some benchmark at anandtech.com

ok
well Im currently on a 64bit machine, but the os and mysql etc is all 32 bit versions installed

many internal datatypes a longlongs (64-bit), so the # of operations on a 32 bit box is higher

gotcha, so mysql uses longints internally in its code

think of a 10 litre tub of water and having only a 5 litre bucket...
of course it does.
and when the compilers start doing 128-bit it will use that soon
it needs the adressing and value range

arjenAU, but the 32bit version of mysql doesnt use 64bit ints internally does it ?

yes it does.
it has to.
you're not a programmer ar eyou ;-)

I am actually, but not familar with mysql internals

compilers tend to offer one hop more than the hardware supports.
on old 16-bit machines you had 32-bit types
this is just C coding, nothing mysql specific.
if you need 32 or 64 bit integers, why would you code around that if the compiler will do it for you
on a 32-bit build, the compiler deals with the stuff. on a 64-bit build, the CPU does. all fine without extra code.
source code that is
making sense?
of course, if your OS is 32-bit you can't run 64bit mysql.

I get your point, but what I mean is that it is possible a 32 bit app doesnt use 64bit ints

agore: if it has the type defined in that compiler, it is handled.

a 64 bit int is a long

how do i change mysql password?

sure. but not if you need to do stuff like mysql has to. data juggling

shabbs: long long

SET PASSWORD FOR 'host'@'host' = PASSWORD('yourpwd');
let's not debate C semantics here ;-)

long long long!

I hope the gcc people will come up with a better naming

long long short long! (gee, starting to look like morse code)

because that's just getting silly

arjenAU: I prefer uint64_t or int64_t types.

require_once() [function.require]:"

it's nice and explicit.

agree.
tat's a php error.
and it sems like you don't have all the errormsg there.

so basicaly 64bit mysql builds are better

indeed. see ##php

require_once() [function.require]: Failed opening required 'DB.php' (include_path='.:/usr/share/php:/usr/share/pear')

if you're on a 64bit box, it makes sense to use it yes. and havin more memory is not bad

agore: for dealing with large types such as bigints
plus you got more registers on the cpu too, why not take advantage of it?

there ya go. it can't find the db.php file. nothing to do with mysql as such.

k thx

and large tables, as it takes ages to optimize them when there over xGB and your 32bit mysql is restricted to 4GB ram

how do i change mysql password?

zabi1: man mysqladmin

on a 32bit mysql build I can only allocate 3GB to the innodb pool buffer

at least I think it has a man page

mysqladmin --help

which makes large tables impracticle to maintain

with 3GB innodb buffer pool, you will likely get out of memory when other stuffs need more memory
on a 32bit system

hence 64bit is the way

sure it is, time to upgrade your os

Hi there! Is there a query that could return nothing? no row? SELECT '' for example, still returns one row with an empty string

this reminds me of all those people I know who keep telling me that there's no need for 64 bit...
I just want to hit them with a stupid stick
or an ibm type m keyboard

take it all you guys all use 64bit builds then

erw.. 64!
duh..

agore: on my x86_64 capable systems, yes.

is it just a reliable, solid etc as 32

probably better

hey
I'm trying to use MySQL Administrator to create a table.. how come in the indices tab I cant select UNIQUE?
its disabled

Any idea on how to have a query return no rows? something like SELECT '', but that one returns one row with an empty string.. I need NO results, independantly from any DB, available tables, etc..

perhaps the user you logged in as doesnt not have certain permissions ?

agore, I'm logged in as root.. I have full permissions
and if I manually type in the SQL query it works

select schema_name from information_schema.schemata where schema_name is null
whats the purpose?

hey, how can i kill gremlin chars in my DB?
phoenixz, create an impossible where clause

What is schema_name?

phoenixz, i guess that might be table/column dependent though
i need to remove all non UTF-8 chars from my DB
apparently a few have slipped in, like the — character from MS word

a where clause requires tables yeah or not?

WHERE false

phoenixz, yeah, where false should work

where requires a table

phoenixz, WHERE 3209834 = 45759827348
phoenixz, where 0 = 1
phoenixz, where true = false

use the information_schema table, since it is always there, and accesible

I have a php mysql query wrapper that needs to return nothing in case an empty query is given (that would cause an error with php_mysql..

then don;t make the query call to mysql server

if you want to select nothing.. dont query

phoenixz, errr, why don't you do the appropriate thing and return a status message

works perfectly, thanks!
Because for this wrapper its expected behaviour.. long story

anyone know how to zap non UTF-8 friendly chars out of a DB, ala?

explain what you want

kimseong, i've got some records that have the mdash in them, e.g. the slightly longer minus sign that MS word gives you

then?

kimseong, and i validated one of my pages of db results from a search page of mine and it wouldn't even validate because the w3 validator complained of "non-utf-8" characters
i looked at the records in question, one of them had mdash in it
the other two had y-umlaut

do a select hex(colname) and see what is the exact hex value for those invalid char

is there an easy way of setting "field comments" or can this only be done through updating INFORMATION_SCHEMA.COLUMNS ?

if a query finds no rows there's no rows. no error. perfectly valid. just code properly

Zycon information_Schema is not updatable

kimseong, yeah but how can i do a query to find all the records that have characters that aren't UTF-8 compliant in them? (as far as the w3 validator is concerned)

how do you set a field comment then?

kimseong, for example i know of those three records, but i'm sure there are probably more

Zycon i guess alter table can do it

kimseong, and thx for the hex() solution, i'll look into that as well, but that will need to be step two

find the problem, why it fail

cheers, i'll check it out

kimseong, step 1 is finding all the offending problems
kimseong, i don't understand
kimseong, i "know why it fails" because the validator tells me fairly precisely what it was failing on

on the web page,can you save it into a text file, then compare the hex value of your data

for finding rows, I would need to know for sure a table is available. In this case I don't know if a table iwll be there or not

kimseong, thing is the web page in question only has a small portion of all the records in the database

select hex(column) to get the hex value from the data, compare with the data on the browser
1 possible reason is the the connection is not using utf8 charset and the utf8 char got converted to latin1 and broswer expect utf8
maybe you can try change the browser encoding to latin1 and see if the char appears

im trying to improve a query - can the outer 2 queries be combined into a self join? http://deadbeefbabe.org/paste/5100 ?
all Part IDs with most resent OrderID

if it's not, you get a table does not exist errror ;-)
if the table xeists and no row matches, you get no error but 0 result rows. it's really quite simple, if your code does not suck ;-)
SET NAMES=...

I already have code that does not suck for this part

hello
how to import a db and convert it from csv to .sql, please?

from your questions, I'm rather worried
use the CSV engine

eheh
i wanna try to import a complete db (.txt) into a my own empty db, i tried with phpmyadmin, cause i think should be easier, but failed: max_upload exceded. checked php.ini, and i didn't found why the max_upload filesize is still 2mb

phoenixz arjenAU set is a good idea, better than select in this case

SET?

returns 0 row, but not sure if there is any resultset
set @a = 1
maybe not, since it returned 0 rows affected

looks better yeah.. let me try

I have two tables, one with a list of unique items, and one with a list of urls. Each url can contain multiple items. There can also be multiple urls referring to the same item. Should I have a seperate list table that links the two together, or how would I go about making this relationship?

kimseong, well the interesting anomaly is that the table is in latin1
kimseong, and I'm pretty sure the page is trying to use utf-8 as it's characterset

SELECT * FROM tbl WHERE 1=0 return no error, 0 rows result.

so the connection charset is wrong, use "set names utf8" as arjenAU pointed out

SET does not return a resultset regardless.

realise that later on

kimseong, so is the SET NAMES a separate query?

mess. you can really confuse yourself with charsets. fi you can keep the entire server in the same charset, that'd be really great.

yes, after connect before your query

arjenAU, if it was my project and i had had control over this nonsense, that would've been really great, but we can't all have our'druthers :P

something better since set may not work, show variables like 'zzz';

doesnt hurt to mention ;-)

That would not give a "variable not found" like error?

no, Empty set (0.00 sec) is what I got

or should I use a bit field? is that kind of thing generally used in mysql?

To enable the CSV storage engine if you build MySQL from source, invoke configure with the --with-csv-storage-engine option.

does mysql have any correction features built in.... ? like Google's "did you mean..."

best would be SHOW VARIABLES LIKE 'zzz'; ?

i guess so

hi, my /var/log/mysql.log is about 900mb now its taking up my "/" space, how do i make it gzipped by itself?

uhmmm

rename it, then flush logs, then gzip the renamed file

SHOW VARIABLES WHERE Variable_name IS null; would be better.. just in case zzz would exist.. you never know

Got a simple SQL question. I want my blog to have a MONTH HEADER then that months post. I was wondering how I would do this. I don't think a group by will do this ...

how to flush logs?
is there a script to do that automatically?

if that is your worry, what if the variable name really contain null value

hey

no way i have no future

Luckly all my post have time stamps on them, so thats not a problem, I just don't know how to go about writing a query that will return the month only ONCE

is there any way to export a database schema?
like.. by using MySQL Administrator

you can easily write one, you can use mysqladmin to flush logs

I doubt a variable name could / would be null.. mmmmm.. *paranoid mode*

you get unknown column

What do you mean with unknown column?

try the show varibles like columnname is null

will mysql do that automatically by default?

got no such col error

instead of selecting a value, can I instead increment a value using a case statments or something similar like I can in PGSQL? basically I have a column "is_selected", its either true or false. I'd like to select the number of trues, and number of falses.

SHOW VARIABLES WHERE Variable_name IS null; did not give any error here..

i saw some mysql.log.2.gz etc, they are not as big, like only a few kb

3 steps, mv, flush logs, gzip
maybe other scripts or whatever, not by mysql automatically

so in the select statement id like to increments a value based on a value in the db

nite then

SELECT id + 1 AS new_id FROM my_table;

that'll be the logrotate from your linux distro doing that in a cronjob

How would I select the month of on of my post and only print it once ^
?

is 'tagging' best done with two tables, one for a list of object references and tag references, a list of objects, and a list of tags, or some kind of space delimited list?
*thats actually 3 tables, sorry

$query = 'SELECT * FROM `news` ORDER BY `ID` GROUP BY `DATE` DESC';

illyum syntax error

Thats not working
k

group by before order by

never some kind of space delimited list.

could he just move the DESC before the GROUP BY? or would that not work either?

I ideally would like my blog to print out JULY post post post post JUNE post post post post MAY post post post ...

always add more tables instead?
or are comma delimiters more generally accepted over space ones? =P

order by date should fo
*do

always more tables

if space is not used in your data

unless you are using excel for your db engine

but space is very common in strings data as compared to , or tab

OK , here is my second delema ... my dates are time stamped like so ... 2007-06-11
9999-99-99

so?

Can I slice this appart with sql?

!man date time functions

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

I would only like to group by MONTH

group by will collapse all row of the month to 1 row, and you lost your posting data

!man date

see http://dev.mysql.com/doc/refman/5.0/en/using-date.html

maybe I should be asking the PHP room

you just need order by, not group by

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

But what if I make multiple post in one day? Will it ordey by ID after date ?

I think do ORDER BY 'id','time'

I don't think group by does what you think it does

inconceivable!

k Im gonna try this thanks
Hmmm.
This still isn't what I want... How do I prevent mysql from repeating my month header?

what SQL are you executing?

I don't want it to say JULY post JULY post JULY .... I just want it to print that month and thats it
$query = 'SELECT * FROM `news` ORDER BY `ID`,`DATE` DESC';
my website is www.oxygenfad.com

GROUP BY!

k
hahaha

add LIMIT 1?

Yes, that might work

also, if you only want the month, do SELECT `Month`
or whatever you have it as

SO you think I should do a sub query? Like do my month query first, and then do a select for my months posts?

no
if you want the latest post, and you can grab the month info while you're at it, go for it
I didn't know you wanted the latest post too

So like HEADER JULY -Post made in july 1- -Post made in july 2- -Post made in july 3- HEADER JUNE -Post made in JUNE1- -Post made in JUNE2- -Post made in JUNE3-
Maybe it would be easier if I make a new table for each months post ?

you're db will grow extremely long then... that seems like a bad idea

k

you can just loop queries if you want, loop for each month. Or it would be easier to do one big query, then have whatever language you're using just list a header when it comes to a new month

if you were doing an insert into a table at the same time you were doing a select from it, could it possible make the select not return all the columns it should?

Related Topic: