Mysql Date column type

[p shah]

Hi,

I have one question regarding date column type in
MYSQL.

Can I use the column type for date as Integer instead
of DateTime or Date to store the date as
mmddhhmmss or mmdd?

As I know MYSQL accepts this format. But I am not too
sure about the column type.

What are the pros and cons for using Integer column
type for the date?

Please reply at the earliest.

Regards,


__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Drop foreign key replication problem

[Batara Kesuma]

Hi,

I am running a master and a slave. When I created a foreign key in
master, it looked like this:
CONSTRAINT `0_373` FOREIGN KEY (`prefecture_id`) REFERENCES `prefecture`
(`id`)

But on the slave it was like:
CONSTRAINT `0_451` FOREIGN KEY (`prefecture_id`) REFERENCES `prefecture`
(`id`)

The problem is, when I deleted this foreign key on master using command:
ALTER TABLE input DROP FOREIGN KEY 0_373

The slave produced an error:
ERROR: 1025  Error on rename of './new_atpress/input' to
'./new_atpress/#sql2-3a54-2' (errno: 140)
031203 13:27:30  Slave: Error 'Error on rename of './new_atpress/input'
to './new_atpress/#sql2-3a54-2' (errno: 140)' on query 'alter table
input drop foreign key 0_373'. Default database: 'new_atpress',
Error_code: 1025
031203 13:27:30  Error running query, slave SQL thread aborted. Fix the
problem, and restart the slave SQL thread with "SLAVE START". We stopped
at log 'borneo-bin.010' position 901130237

Versions:
MySQL 4.0.15
Linux 2.4.18

I guess this is because the constraint name difference of the foreign
keys. How do I fix this problem? Can I just manually drop the foreign
key on slave and skip this "alter table input drop foreign key 0_373"?
How to do it? Please help, and thank you very much.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Connection problems in MySQL

[Sudheer]

Hi,
I am trying use MySQL on RedHat 9 Linux box. I
installed the following rpms for this.
mysql-3.23.54a-11.i386.rpm   
mysql-server-3.23.54a-11.i386.rpm
mysql-devel-3.23.54a-11.i386.rpm

After installing I executed the mysql_install_db
script, which executed without giving any error. I
restarted MySQL Server(through service mysqld restart 
command). But when I tried to use mysqladmin command,
I am always getting this error.
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user: '[EMAIL PROTECTED]'
(Using password: NO)'

When I execute 'mysqladmin version' or 'mysqladmin
ping', that time also I am getting the same error. My
machine have an entry for the localhost in /etc/hosts 
file ( I can ping to localhost and 127.0.0.1)/At the
same time I can telnet to localhost on port 3306 which
gives the following output
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
(
3.23.543V8T~>W{,
Connection closed by foreign host

I am doing all these things in super user mode. Also I
noted that my 
/var/log/mysqld.log has the following lines.
031203 11:13:26  mysqld started
Cannot initialize InnoDB as 'innodb_data_file_path' is
not set.
If you do not want to use transactional InnoDB tables,
add a line
skip-innodb to the [mysqld] section of init parameters
in your my.cnf or my.ini. 
If you want to use InnoDB tables, add to the [mysqld]
section, for example,
innodb_data_file_path = ibdata1:10M:autoextend
But to get good performance you should adjust for your
hardware
the InnoDB startup options listed in section 2 at
http://www.innodb.com/ibman.html
/usr/libexec/mysqld: ready for connections

I went through MySQL Installation docs, but could not
fix this problem. Any body can help me to fix this
problem?
Thanks in advance

Sudheer


__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Replication

[Chuck Gadd]

rubn ruvalcaba wrote:

I want to know how could solve the next replication scenario:

I have a master.
I have 5 slaves.
At start the slaves has a master snapshot.

Now imagine slave 1, inserts a record. When it gets connected to the 
lan, it must replicate it's changes to the master.
No, a slave receives changes that occur at the master.   That's
why it's a slave.
I suspect you want each machine to be a Master and a slave.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

[Solaris 8.2/x86 compile bug]

[David John PENTON]

>Description:
Mysql fails to install due to compile error on line 655. Unable to find
constant VIS_WHITE. This is due to not having the correct vis.h.

ie. HAVE_VIS_H should be false and this library should not be included line 49.
(although there is a different vis.h library installed - from the BSD ver)
>How-To-Repeat:
Install on a protected Solaris 8.2/x86 machine using 3.2 where system
libraries cannot be installed in standard positions.
>Fix:
wrap HAVE_VIS_H tests around line 655 and 617. If not true just do strcpy.
It at then allows it to compile and run - though it may cause trouble for
those people without vis.h later on.
>Submitter-Id:  
>Originator:David John PENTON
>Organization:
Research Assistant - The University of Melbourne
>MySQL support: [none]
>Synopsis:  Compile error due to vis.h existance assumption
>Severity:  serious 
>Priority:  high
>Category:  mysql
>Class: sw-bug
>Release:   mysql-4.1.0-alpha (Source distribution)

>C compiler:gcc (GCC) 3.2
>C++ compiler:  g++ (GCC) 3.2
>Environment:

System: SunOS queeg 5.8 Generic_108529-21 i86pc i386 i86pc
Architecture: i86pc

Some paths:  /bin/perl /usr/local/bin/make /usr/local/bin/gmake /usr/local/bin/gcc 
/opt/SUNWspro/bin/cc
GCC: Reading specs from /usr/local/bin/../lib/gcc-lib/i386-pc-solaris2.8/3.2/specs
Configured with: ../gcc-3.2/configure --prefix=/usr/local/apps/gcc-3.2 
--enable-version-specific-runtime-libs
Thread model: posix
gcc version 3.2
Compilation info: CC='gcc'  
CFLAGS='-I/mount/autofs/home_se440/s440gf/workspaces/djpenton/app/include/'  CXX='g++' 
 CXXFLAGS=''  LDFLAGS='-L/mount/autofs/home_se440/s440gf/workspaces/djpenton/app/lib/' 
 ASFLAGS=''
LIBC: 
-rw-r--r--   1 root bin  1608148 Jun 20 11:06 /lib/libc.a
lrwxrwxrwx   1 root root  11 Nov 11  2002 /lib/libc.so -> ./libc.so.1
-rwxr-xr-x   1 root bin   956112 Jun 20 11:07 /lib/libc.so.1
-rw-r--r--   1 root bin  1608148 Jun 20 11:06 /usr/lib/libc.a
lrwxrwxrwx   1 root root  11 Nov 11  2002 /usr/lib/libc.so -> ./libc.so.1
-rwxr-xr-x   1 root bin   956112 Jun 20 11:07 /usr/lib/libc.so.1
Configure command: ./configure '--prefix=/home/projects/lt/summer0304/mysql-queeg' 
'--without-innodb' '--with-unix-socket-path=/tmp/mysqld-djpenton.sock' 
'--with-tcp-port=33303' '--with-mysqld-user=djpenton' '--without-debug' 
'--disable-largefile' 
'CFLAGS=-I/mount/autofs/home_se440/s440gf/workspaces/djpenton/app/include/' 
'LDFLAGS=-L/mount/autofs/home_se440/s440gf/workspaces/djpenton/app/lib/'
Perl: This is perl, version 5.005_03 built for i86pc-solaris



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql command to rename database

[Jared Klett]

hi Joffrey,

	You can manually rename the directory that contains the file for the 
database you want to rename. Make sure to do this while mysqld is off. 
So if we wanted to rename database "foo":

% cd /usr/local/mysql/data
% ls -l
drwx--   2staff512 Sep 28 19:58 mysql/
drwx--   2staff   1.0K Nov 24 14:45 foo/
-rw-rw   1staff   2.7K Nov 24 14:45 .err
-rw-rw   1staff  5 Nov 24 14:45 .pid
drwx--   2staff512 Sep 28 19:58 test/

% mv foo newfoo

	When you bring mysqld back up, it should use the new name.

cheers,

- Jared

On Dec 2, 2003, at 7:48 PM, joffrey leevy wrote:

I am ashamed to ask this but I really did not see this
in the manual or elsewhere.
What is the command to rename a database?

__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Replication

[rubn ruvalcaba]

I want to know how could solve the next replication scenario:

I have a master.
I have 5 slaves.
At start the slaves has a master snapshot.

Now imagine slave 1, inserts a record. When it gets connected to the lan, it 
must replicate it's changes to the master.
Then the slave 2 gets connected to the lan too, and replicate their own 
changes to the master, but it also must receive the changes made on the 
slave 1.

Is it possible to do with mysql replication?

Thanks in advance.

_
Charla con tus amigos en línea mediante MSN Messenger:  
http://messenger.microsoft.com/es

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysql command to rename database

[joffrey leevy]

I am ashamed to ask this but I really did not see this
in the manual or elsewhere.

What is the command to rename a database?  


__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Any word on G5/64bit compiles of mysql?

[Gabriel Ricard]

Here are benchmarks for an optimized build of MySQL 4.0.16: (used 
-mpowerpc-gpopt and -mpowerpc64 gcc arguments)

alter-table: Total time: 18 wallclock secs ( 0.03 usr  0.06 sys +  0.00 
cusr  0.00 csys =  0.09 CPU)
ATIS: Total time: 19 wallclock secs ( 8.65 usr  8.40 sys +  0.00 cusr  
0.00 csys = 17.05 CPU)
big-tables: Total time: 31 wallclock secs ( 6.74 usr 19.63 sys +  0.00 
cusr  0.00 csys = 26.37 CPU)
connect: Total time: 172 wallclock secs (39.00 usr 53.37 sys +  0.00 
cusr  0.00 csys = 92.37 CPU)
create: Total time: 109 wallclock secs ( 3.52 usr  2.45 sys +  0.00 
cusr  0.00 csys =  5.97 CPU)
insert: Total time: 1345 wallclock secs (376.74 usr 339.12 sys +  0.00 
cusr  0.00 csys = 715.86 CPU)
select: Total time: 125 wallclock secs (39.83 usr 32.29 sys +  0.00 
cusr  0.00 csys = 72.12 CPU)
transactions: Test skipped because the database doesn't support 
transactions
wisconsin: Total time: 10 wallclock secs ( 2.42 usr  2.61 sys +  0.00 
cusr  0.00 csys =  5.03 CPU)

Here are the benchmarks for the unoptimized version:

alter-table: Total time: 20 wallclock secs ( 0.05 usr  0.03 sys +  0.00 
cusr  0.00 csys =  0.08 CPU)
ATIS: Total time: 20 wallclock secs ( 8.26 usr  9.26 sys +  0.00 cusr  
0.00 csys = 17.52 CPU)
big-tables: Total time: 31 wallclock secs ( 6.91 usr 19.21 sys +  0.00 
cusr  0.00 csys = 26.12 CPU)
connect: Total time: 167 wallclock secs (39.32 usr 52.16 sys +  0.00 
cusr  0.00 csys = 91.48 CPU)
create: Total time: 110 wallclock secs ( 2.55 usr  2.49 sys +  0.00 
cusr  0.00 csys =  5.04 CPU)
insert: Total time: 1307 wallclock secs (376.21 usr 328.16 sys +  0.00 
cusr  0.00 csys = 704.37 CPU)
select: Total time: 136 wallclock secs (40.63 usr 30.53 sys +  0.00 
cusr  0.00 csys = 71.16 CPU)
transactions: Test skipped because the database doesn't support 
transactions
wisconsin: Total time: 11 wallclock secs ( 2.35 usr  2.75 sys +  0.00 
cusr  0.00 csys =  5.10 CPU)

For additional reference, here are the same benchmarks that were run on 
the same machine, but using an Xserve RAID (two software raid mirrored 
RAID 5 arrays with three drives each) for the storage instead of the 
internal SATA drive:

Unoptimized, with Xserve RAID:

alter-table: Total time: 19 wallclock secs ( 0.05 usr  0.07 sys +  0.00 
cusr  0.00 csys =  0.12 CPU)
ATIS: Total time: 19 wallclock secs ( 8.07 usr  9.16 sys +  0.00 cusr  
0.00 csys = 17.23 CPU)
big-tables: Total time: 32 wallclock secs ( 6.61 usr 19.46 sys +  0.00 
cusr  0.00 csys = 26.07 CPU)
connect: Total time: 165 wallclock secs (38.22 usr 50.03 sys +  0.00 
cusr  0.00 csys = 88.25 CPU)
create: Total time: 106 wallclock secs ( 6.15 usr  3.01 sys +  0.00 
cusr  0.00 csys =  9.16 CPU)
insert: Total time: 1284 wallclock secs (376.06 usr 325.79 sys +  0.00 
cusr  0.00 csys = 701.85 CPU)
select: Total time: 133 wallclock secs (39.64 usr 29.83 sys +  0.00 
cusr  0.00 csys = 69.47 CPU)
transactions: Test skipped because the database doesn't support 
transactions
wisconsin: Total time: 10 wallclock secs ( 2.30 usr  2.74 sys +  0.00 
cusr  0.00 csys =  5.04 CPU)

Optimized:

alter-table: Total time: 18 wallclock secs ( 0.03 usr  0.11 sys +  0.00 
cusr  0.00 csys =  0.14 CPU)
ATIS: Total time: 19 wallclock secs ( 8.32 usr  9.05 sys +  0.00 cusr  
0.00 csys = 17.37 CPU)
big-tables: Total time: 31 wallclock secs ( 6.65 usr 18.93 sys +  0.00 
cusr  0.00 csys = 25.58 CPU)
connect: Total time: 167 wallclock secs (39.39 usr 50.78 sys +  0.00 
cusr  0.00 csys = 90.17 CPU)
create: Total time: 109 wallclock secs ( 6.35 usr  3.04 sys +  0.00 
cusr  0.00 csys =  9.39 CPU)
insert: Total time: 1314 wallclock secs (371.49 usr 333.26 sys +  0.00 
cusr  0.00 csys = 704.75 CPU)
select: Total time: 124 wallclock secs (39.33 usr 30.75 sys +  0.00 
cusr  0.00 csys = 70.08 CPU)
transactions: Test skipped because the database doesn't support 
transactions
wisconsin: Total time: 11 wallclock secs ( 2.40 usr  2.61 sys +  0.00 
cusr  0.00 csys =  5.01 CPU)

Either way, there didn't seem to be a whole lot of difference.

- Gabriel

On Nov 24, 2003, at 2:22 AM, Adam Goldstein wrote:

Any word on G5/64bit compiles of mysql?

I know I am not the only one wanting to know Does it work, and How to 
compile best for it.

or am I alone in the universe?
--
Adam Goldstein
White Wolf Networks
http://whitewlf.net


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

More database replication

[Richard Bewley]

Hi,

I have a "mission critical" database, that I want to be redundant, so I'm
using, as discussed in another thread, the Cisco LocalDirector, two mysql
machines, and db replication.  mySQL version 4.0.16, compiled from source.

Should I make both servers both master and slave, or will this cause
problems?  Would it be better, to make one primary server master, and just
have a slave as a "failover" db server for the LocalDirector to send traffic
to only if the primary is down?

Also, the problem I've come across is, I have gotten replication setup
correctly, and it works, but only when I type

mysql> LOAD DATA FROM MASTER;

I'd rather have it automatically be replicating rather than have to run that
command every time, or rather than having a cron job do it, which I see as
messy at best.

Any suggestions?

Thanks,
Richard



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Help

[Stephen Fromm]

- Original Message - 
From: "Chris Boget" <[EMAIL PROTECTED]>
To: "Greg Jones" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, December 02, 2003 8:32 AM
Subject: Re: Query Help


> > Access.  However, when I run it against MySQL I get an error.
> > select l.ltsysid,l.lientraknum, c.name from lientrak as l, customer as c
> > where l.custsysid=c.custsysid
> > and l.ltsysid in (select l2.ltsysid from lientrak as l2 where
> > l2.lientraknum
> > like '2003-%')
>
> Sub queries are not going to be available until version 4.1.  You'll need
> to re-write the above query using an outer (?) join.  I'm not sure what
the
> exact syntax should be and I'm sure someone will pipe up with that info.

First impression:  it looks like it might be messy if ltsysid isn't unique
(i.e., isn't a key).

> Chris
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

replication: one slave for multiple masters

[Lloyd Kvam]

This is not directly supported by MySQL.  I used FTP to collect files
from the various databases to be replicated and applied them to the
"central" database.  If the central database is NOT a master for
aany other replication, then you can simply use mysqlbinlog to
update your central database.
If you need to preserve the server ID, things become more complicated.
I set up a second mysql server to act as a single master for the
central database.  It had no updates and no real data of its own.
I spliced the collected binlog files into its output logfile.  This way
they went through the more normal processing and the originating server ID
was preserved in the central output binlog.
--
Lloyd Kvam
Venix Corp.
1 Court Street, Suite 378
Lebanon, NH 03766-1358
voice:  603-653-8139
fax:801-459-9582
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

table restructure

[Wesley Baker]

We have moved a production application over to a MySQL database and are
experiencing some issues.  I'm not the database guy myself, but thought
I'd try the list to see if we could get some help.

 

The application does general business functions such as AR/AP/SO
Entry/Payroll, etc.

 

The system is functioning well and normally under light loads.

 

We have had multiple instances where the server response time degregates
to the point that it seems locked up.  It might take an hour to return a
record for a simple query.  The condition is brought on by load.  The
best example is today when we committed to restructure a small table
(<4000 records) and remove the null fields and replace them with zeros.
We use the InnoDB file system.

 

Top shows

Iowait 90%

Cpu 10%

Idle 0%

 

The machine has 1 gb of physical memory.  I'm not a real linux guru.
Size shows something like 1.4gb and RSS shows like 750 Mb for the mysql
process.

 

Wesley Baker

 

 

validate replication by comparing input and output logs

[Lloyd Kvam]

We have discovered that a small number of transactions appear to have
been omitted from the replication process.  The input and output
binlogs have been saved and I plan to write a program to compare them
to determine which transactions were not replicated.  At this point,
we are expecting to find an error with our processing, but I need to
determine which transactions must be resubmitted.
I wondered if anyone has already written such a comparison program.  If
not, I'll be busy writing one.  Let me know if it would be useful to
anyone else.
--
Lloyd Kvam
Venix Corp.
1 Court Street, Suite 378
Lebanon, NH 03766-1358
voice:  603-653-8139
fax:801-459-9582
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: notification service

[colbey]

Might just create a common table that stores messages back and forth..  it
stores sender id, recipent, message, etc..

each server polls the table ever so often (cronjob) for messages for it
and processes them, removing them from the queue.. it's like a simple
message broker..


On Tue, 2 Dec 2003, Jinsong Zhu wrote:

> I'm looking at MySQL for a project where multiple applications sharing the same
database need to collaborate. For example, if one application made a change to a table,
another application would be notified via an event (the applications may run on 
different host machines). However, I have not been able to find this kind of feature 
(eg. a notification service) in MySQL. Can MySQL do that at all?
>
> Thanks.
>
>
> -
> Do you Yahoo!?
> Free Pop-Up Blocker - Get it now

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CONCAT on BLOB

[Colbey]

This is the article for you:

http://php.dreamwerx.net/forums/viewtopic.php?t=6

Shows how to store large files in database... I've currently got gigs and
gigs of files in mysql using this method..

On Tue, 2 Dec 2003, Jim Kutter wrote:

> Hi folks.
>
> I'm storing files in a BLOB table for a number of reasons for my web
> app, and I'm trying to break up uploads so I insert smaller chunks (my
> app imposes restrictions on memory usage, so reading the whole file at
> once and doing an insert doesn't work).
>
> What I do is insert the record, then call a series of UPDATE queries on
> the record concating the new chunk with the existing blob column.
>
> The problem I'm having is that the size of the blob column is WAY off
> for larger files (2MB or so). My app reports that the entire file was
> read properly, but not all of it was inserted properly into the DB.
>
> Any ideas on why this is happening? Any suggestions for alternatives?
> Storing the files on the filesystem is not an option for me.
>
> Thanks
>
> I'm using 3.23.36 (linux RedHat) with MyISAM tables.
>
> -jim kutter
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: check point command

[kp gbr]

Hi,
 
I am working on a database replacement project and evaluating Mysql with Innodb for 
the same. Mysql document says, Innodb supports checkpointing but that happens in the 
background. That is, when the size of the log file exceeds a limit, it happens in the 
back ground.
 
Is there way to force this to happen whenever we want?
 
Regards,
kp.

Jeremy Zawodny <[EMAIL PROTECTED]> wrote:
On Wed, Nov 19, 2003 at 05:09:26PM -0800, kp gbr wrote:
> How do I perform checkpoint in MySQL.

You cannot.
-- 
Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
| http://jeremy.zawodny.com/

MySQL 4.0.15-Yahoo-SMP: up 74 days, processed 2,914,655,888 queries (455/sec. avg)

-
Do you Yahoo!?
Free Pop-Up Blocker - Get it now

notification service

[Jinsong Zhu]

I'm looking at MySQL for a project where multiple applications sharing the same 
database need to collaborate. For example, if one application made a change to a 
table, another application would be notified via an event (the applications may run on 
different host machines). However, I have not been able to find this kind of feature 
(eg. a notification service) in MySQL. Can MySQL do that at all?
 
Thanks.


-
Do you Yahoo!?
Free Pop-Up Blocker - Get it now

CONCAT on BLOB

[Jim Kutter]

Hi folks.

I'm storing files in a BLOB table for a number of reasons for my web
app, and I'm trying to break up uploads so I insert smaller chunks (my
app imposes restrictions on memory usage, so reading the whole file at
once and doing an insert doesn't work).

What I do is insert the record, then call a series of UPDATE queries on
the record concating the new chunk with the existing blob column.

The problem I'm having is that the size of the blob column is WAY off
for larger files (2MB or so). My app reports that the entire file was
read properly, but not all of it was inserted properly into the DB.

Any ideas on why this is happening? Any suggestions for alternatives?
Storing the files on the filesystem is not an option for me.

Thanks

I'm using 3.23.36 (linux RedHat) with MyISAM tables.

-jim kutter

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: mysql disaster recovery

[John Griffin]

Hi Andrew,

I am not a Guru. I would suggest that you look at MySQL's excellent replication 
facility rather than NFS mount a drive. Having your data on an NFS mounted drive will 
significantly degrade the performance of your database. Replication will not.


John Griffin

-Original Message-
From: Andrew Hall [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 4:51 PM
To: [EMAIL PROTECTED]
Subject: mysql disaster recovery


Greetings Gurus,

I have a mysql server that I need to create a disaster recovery system
for.  What I am planning on doing is putting the data dir on a NFS
mounted directory so that I can start mysql on either of two servers in
case one dies.  The inbound connections would be load balanced in a fail
over scenario, so the IP that clients will connect to will be on the
load balancer.

I'm wondering if there is anything already developed that would test
mysql on the primary server, and if its not functioning, kill any
remaining mysql processes if necessary, and start it on the secondary.  
This logic seems to be the biggest problem.

Any suggestions, or other methodologies to implement this would be
welcome. 

Thank you for your time in advance,

Andrew


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

AUTO_INCREMENT Starting over

[Dan Muey]

Hello group,

I have a table that I create with 

CREATE TABLE SuperTest (
ID int(11) NOT NULL  AUTO_INCREMENT,
Name varchar(64) NOT NULL,
Domain varchar(64) NOT NULL,
PRIMARY KEY(ID)
);

I use that same thing to create a the same table on two servers.

On one server (mysql  Ver 11.16 Distrib 3.23.49, for portbld-freebsd4.6 (i386))
If I add, say five records I get ID to be 1,2,3,4,5.
Then I delete ID 5 and insert a new record and it's id is 6. So now I have 1,2,3,4,6.
That's what I want it to do.
However on the other server (mysql version ???) if I have 1,2,3,4,5, delete ID 5 
and insert a new one it makes the new one ID 5. I suppose because it's the next number.

I'm not really even sure what to call it besides 'non reusable auto_increment'.

I can't seem to find anything on mysql.com about what settings, version, etc cause it 
to work either way.

Any insight would be greatly appreciated as in some situations I really need it to be 
one way or the other consistently across servers.

TIA

DMuey

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: stress testing

[Gabriel Ricard]

On Dec 2, 2003, at 3:48 PM, Ari Davidow wrote:

We have just put up a new MySQL 3.23.x on a modest Sun V120 with 1GB 
RAM and a few gig of hard disk space. We're running Solaris 2.8.
Why are you using an old version of MySQL? MySQL4.0 is the recommend 
production version currently.


We seem to be clueless (I am certainly clueless) about testing various 
configuration options so that we are reasonably optimizing the 
resources available to this server. We ran through (approximately) the 
four configuration files included with the MySQL distro, and then 
added more memory (we had, after all, 1GB).

We set up a script to repeated parse some common documents with lots 
of queries. Then we tried the sort of exercise where we'd create 
temporary tables and copy back and forth. Finally, we ran a load of 
SELECTs using full-text search (3 explicitly joined tables).

We did not record statistically significant results.

In truth, we have just begun using MySQL, and don't know what our 
pattern of usage is. Is there an idealized generalized configuration 
model and a tool or method available to test our server against that 
ideal? The online reference doesn't say much about optimization, and 
it is surely messier when we don't really know what we are optimizing 
for (or how to test, assuming we did know).

Any suggestions? stress testers that have matched up against specific 
types of configuration?


If you're interested in seeing how it stacks up with a multi-threaded 
client, or just want to test how efficiently it works with various 
numbers of threads running, you can use the mysqlsyseval utility, which 
is part of a tarball available at this site:

http://www.wiley.com/legacy/compbooks/pachev/

You can modify the mysqlsyseval.c source to add different types of 
queries to test.

- Gabriel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysql disaster recovery

[Andrew Hall]

Greetings Gurus,

I have a mysql server that I need to create a disaster recovery system
for.  What I am planning on doing is putting the data dir on a NFS
mounted directory so that I can start mysql on either of two servers in
case one dies.  The inbound connections would be load balanced in a fail
over scenario, so the IP that clients will connect to will be on the
load balancer.

I'm wondering if there is anything already developed that would test
mysql on the primary server, and if its not functioning, kill any
remaining mysql processes if necessary, and start it on the secondary.  
This logic seems to be the biggest problem.

Any suggestions, or other methodologies to implement this would be
welcome. 

Thank you for your time in advance,

Andrew


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: mysql remote access on linux

[Dan Greene]

from what I've read in the docs, if you use 'grant' you don't have to flush, but if 
you insert into user tables directly, you do

> -Original Message-
> From: Skippy [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, December 02, 2003 11:56 AM
> To: [EMAIL PROTECTED]
> Subject: Re: mysql remote access on linux
> 
> 
> On Tue, 2 Dec 2003 10:17:24 -0500 [EMAIL PROTECTED] wrote:
> > You should not need to restart , you  will need to 'flush 
> privileges'
> > though.  has that been done?
> 
> I was under the impression that the latest versions don't even need
> 'flush privileges' anymore, that any modifications to the 
> mysql database
> is taken into account immediately.
> 
> -- 
> Skippy - Romanian Web Developers - http://ROWD.ORG
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql remote access on linux

[Skippy]

On Tue, 2 Dec 2003 10:17:24 -0500 [EMAIL PROTECTED] wrote:
> You should not need to restart , you  will need to 'flush privileges'
> though.  has that been done?

I was under the impression that the latest versions don't even need
'flush privileges' anymore, that any modifications to the mysql database
is taken into account immediately.

-- 
Skippy - Romanian Web Developers - http://ROWD.ORG

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Reducing and counting duplicate rows

[Skippy]

On Tue, 2 Dec 2003 10:47:07 +0100 "Wouter van Vliet" <[EMAIL PROTECTED]>
wrote:
> If you want to select those who HAVE BEEN at level 2 in the yeaer Y,
> you can just do "level_id = 2". But I guess you want to know who
> currently IS at level 2 IN the year Y? In that case, when using a

Yes, that's what I'm trying to do.

> 4.0.x I guess there is no way you are going to do that. I tried and
> couldn't. I know though, that with a little more puzzling I could do
> it in 4.1.x since (as I heard) that one supports the so-called
> "subselects". 
> 
> For now, you'd be stuck with a little bit of some sort of scripting
> language to parse the results... i'm sorry.

>From what I've gathered, I was afraid that I would probably need to
resort to subselects, but switching to 4.1.x is not acceptable right
now. Post-processing results in the client would also mean a huge
performance hit, but I guess I'll have to look into it.

Could you give me an example of such subselects so I can see what to
make of it? Can this be even done in any SQL implementation without the
aid of client post-processing or procedures?

-- 
Skippy - Romanian Web Developers - http://ROWD.ORG

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

replication: one slave for multiple masters

[Moritz von Schweinitz]

hi, all.

anybody know whether (and how) it's possible to have one server acting 
as a backup-server for various other servers, so that it basically does 
am incremental backup of ONE database on the to-be-backed-up server?

i was guessing to tell the backup-server to act as a slave for the 
various master-servers, but couldn't find anything in the manual 
explaining whether (or how) this is possible..

if using mysql's replication-feature is not an option for doing this, 
does anybody have any experience doing this the 'manual' way? my "plan 
B" is to simply download the bin-log every day via ftp, delete it on the 
to-be-backed-up-servers, and read it in at the backup-server.

thanks for any help,

Moritz.

P.S.: if this is the wrong list for this question, then ehich list would 
be appropiate?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: [OT] Importing content from Word

[Dan Nelson]

In the last episode (Dec 02), Matt MacLeod said:
> I'm building a recruitment website using PHP and mySQL and would like
> to be able to search CVs/resumes that people have uploaded to the
> server. Most people have them in Word format - what I'd like to be
> able to do is copy the text of the document and insert it into a
> mysql database so that it can be searched by the site administrators.
> 
> There would also be an original copy of the word doc uploaded to the
> server.

wvware is great for converting Word documents into other formats.  Run
wvText to generate plaintext output. 

http://sourceforge.net/projects/wvware

-- 
Dan Nelson
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

stress testing

[Ari Davidow]

We have just put up a new MySQL 3.23.x on a modest Sun V120 with 1GB RAM 
and a few gig of hard disk space. We're running Solaris 2.8.

We seem to be clueless (I am certainly clueless) about testing various 
configuration options so that we are reasonably optimizing the resources 
available to this server. We ran through (approximately) the four 
configuration files included with the MySQL distro, and then added more 
memory (we had, after all, 1GB).

We set up a script to repeated parse some common documents with lots of 
queries. Then we tried the sort of exercise where we'd create temporary 
tables and copy back and forth. Finally, we ran a load of SELECTs using 
full-text search (3 explicitly joined tables).

We did not record statistically significant results.

In truth, we have just begun using MySQL, and don't know what our pattern 
of usage is. Is there an idealized generalized configuration model and a 
tool or method available to test our server against that ideal? The online 
reference doesn't say much about optimization, and it is surely messier 
when we don't really know what we are optimizing for (or how to test, 
assuming we did know).

Any suggestions? stress testers that have matched up against specific types 
of configuration?

ari

Ari Davidow
[EMAIL PROTECTED]
http://www.ivritype.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

[OT] Importing content from Word

[Matt MacLeod]

Hi,

I'm building a recruitment website using PHP and mySQL and would like 
to be able to search CVs/resumes that people have uploaded to the 
server. Most people have them in Word format - what I'd like to be able 
to do is copy the text of the document and insert it into a mysql 
database so that it can be searched by the site administrators.

There would also be an original copy of the word doc uploaded to the 
server.

Is there a way of doing this?

Cheers,
Matt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL 4.1 : curious privilege problems (grant, use, show databases)

[Sergei Golubchik]

Hi!

On Nov 25, Christophe DIARRA wrote:
> Hello.
> 
> MySQL 4.1.0 is not respecting the registered privileges. 
... 
> "show databases" displays an incorrect list of databases. Each user lists a
> database it should not, and doesn't show all the databases it should.
> Same thing with 'use ' : the access is denied for some databases
> for which the user s granted 'all privileges'.

Sorry, I don't have any idea so far :(

Some things you can do:

1. upgrade to 4.1.1 (should be out very soon)
   and try if the bug dissapears
2. try to create a complete repeatable test case that I can use to
   repeat this behaviour and submit it to bugs.mysql.com.
   Then the bug will be fixed asap.
 
Regards,
Sergei

-- 
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   <___/  www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: INSERT INTO tbl (SELECT) UNION (SELECT) ORDER BY field-does not work. Why?

[Sergei Golubchik]

Hi!

On Dec 02, George Georgeus wrote:
> Hi!
> I do not know how to use INSERT and UNION together.
> For example:
> INSERT INTO tmp_table_name (a)
> (SELECT a FROM table_name WHERE a=10 AND B=1 ORDER BY
> a LIMIT 10)
> UNION
> (SELECT a FROM table_name WHERE a=11 AND B=2 ORDER BY
> a LIMIT 10)
> ORDER BY a;
> It does not work. But I think it should. Have the
> MySQL
> developers forgotten something??
> I use MySQL 4.012

It's parser deficiency which was fixed in 4.0.14:
Manual: News-4.0.14

   * Allow `CREATE TABLE' and `INSERT' from any `UNION'.

before 4.0.14 you could only use a union without parenthesis in INSERT
or CREATE.
 
Regards,
Sergei

-- 
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   <___/  www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: unixtime update syntax

[Michael Stassen]

UNIX_TIMESTAMP takes a DATETIME and returns a Unix timestamp which 
matches your definition (seconds since '1970-01-01 00:00:00' GMT).

FROM_UNIXTIME takes a Unix timestamp and returns a DATETIME.

I believe the problem you are having is that mysql expects all DATETIMEs 
to be in your timezone.

For example,

  mysql> select from_unixtime(1);
  +-+
  | from_unixtime(1)|
  +-+
  | 1969-12-31 19:00:01 |
  +-+
  1 row in set (0.00 sec)
  mysql> select unix_timestamp('1970-01-01 00:00:01');
  +---+
  | unix_timestamp('1970-01-01 00:00:01') |
  +---+
  | 18001 |
  +---+
  1 row in set (0.00 sec)
  mysql> select from_unixtime(18001);
  +--+
  | from_unixtime(18001) |
  +--+
  | 1970-01-01 00:00:01  |
  +--+
  1 row in set (0.00 sec)
I'm in EST, so I'm 5 hours behind.  5 hours = 18000 seconds, so these 
make sense when you consider that the timezone is always part of DATETIME.

As I see it, the problem you are having is that you've told mysqld at 
startup that you are in PST (-8), but you are expecting DATETIMES to be 
in GMT in your urtime column.  You could set your timezone to GMT at 
startup, but then ALL your DATETIMES will be in GMT.  Assuming you don't 
want that, if you want your urtime column to be in GMT, then you must 
take the offset (PST=-8) into account when you assign values to the 
column.  Try this:

  UPDATE t_test
  SET urtime = DATE_ADD(FROM_UNIXTIME(utime), INTERVAL 8 HOUR)
  WHERE urtime is NULL;
Michael

Ron McKeever wrote:
Thats seems like a bug to me.

I would think FROM_UNIXTIME would take a unixtime stamp
and covert it to what it is. We know its from GMT
UNIX timestamp =
The timestamp is the current time measured in the number
of seconds since
the Unix Epoch (January 1 1970 00:00:00 GMT).
Ron

-Original Message-
From: Keith C. Ivey [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 8:55 AM
To: [EMAIL PROTECTED]
Subject: Re: unixtime update syntax
On 2 Dec 2003 at 7:57, Ron McKeever wrote:


I have a db that gets data dumped into it. One of the columns gets
unix timestamp data "utime". I what to covert that into a datetime
column so I can utlize indexes and such. But I still what the unixtime
to remain.


You can use indexes with a Unix time column about as well as you can
with DATETIME.  What sort of queries are you wanting to do?  Having
the extra column may be unnecessary.

I believe I have a good way to do this but I'm not sure why it's not
converting the date right ( see at bottom ):


It seems to be converting the date right.  FROM_UNIXTIME() does the
opposite of UNIX_TIMESTAMP().  It takes an integer representing a
Unix time and converts it to a DATETIME in local time (not GMT).  It
would be nice if there were a FROM_UNIXTIME_TO_GMT() function, but
there isn't.
--
Keith C. Ivey <[EMAIL PROTECTED]>
Tobacco Documents Online
http://tobaccodocuments.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: unixtime update syntax

[Keith C. Ivey]

On 2 Dec 2003 at 9:12, Ron McKeever wrote:

> Thats seems like a bug to me.
> 
> I would think FROM_UNIXTIME would take a unixtime stamp
> and covert it to what it is. We know its from GMT

A Unix timestamp represents a particular second in time.  It doesn't 
have a time zone associated with it.  Yes, the definition of 0 time 
is based on GMT, but you could just as well say it's the number of 
seconds since 7 pm on 31 Dec 1969 Eastern Standard Time.  The number 
would be the same.  You can represent the time in whatever zone you 
like; it doesn't change what time you're talking about.

DATETIME columns in MySQL are in the local time zone.  It wouldn't 
make sense to convert to a GMT DATETIME, at least not by default.
MySQL's handling of time zones leaves something to be desired, but 
given the way it works the behavior of FROM_UNIXTIME() makes perfect 
sense.  It's not a bug.

Avoiding time zone and daylight time issues is the main reason to use 
Unix time.  I don't see the advantage of keeping your time in two 
different formats.  It seems like sticking to one would be simpler.
But then I don't know your system.

-- 
Keith C. Ivey <[EMAIL PROTECTED]>
Tobacco Documents Online
http://tobaccodocuments.org


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: FullText search CJK in UTF-8

[Sergei Golubchik]

Hi!

On Dec 02, Sergei Golubchik wrote:
> Hi!
> 
> On Dec 02, Hu, Yiguang wrote:
> > Is the fulltext search working for cjk text (use UTF-8) in 4.1.0 alpha ? I
> > have trouble doing search on cjk text in 4.1.0alpha, though it seems should
> > be working according to the following doc:
> > http://www.mysql.com/doc/en/Fulltext_Restrictions.html
> 
> Nope. The manual says "it works with UTF-8 from 4.1.1".
> You have 4.1.0.

Oops, sorry my fault.
Looks like this "as of 4.1.1" part was added only today :)
Thank you for the hint, though.

Regards,
Sergei

-- 
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   <___/  www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: FullText search CJK in UTF-8

[Sergei Golubchik]

Hi!

On Dec 02, Hu, Yiguang wrote:
> Is the fulltext search working for cjk text (use UTF-8) in 4.1.0 alpha ? I
> have trouble doing search on cjk text in 4.1.0alpha, though it seems should
> be working according to the following doc:
> http://www.mysql.com/doc/en/Fulltext_Restrictions.html

Nope. The manual says "it works with UTF-8 from 4.1.1".
You have 4.1.0.
 
Regards,
Sergei

-- 
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   <___/  www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

AW: read (Off subject Help) ACCESS!!

[Freddie Sorensen]

Karl

I have quite a lot of experience with MS Access - write to me off list if
you want me to help you

Freddie

> -Ursprüngliche Nachricht-
> Von: karl james [mailto:[EMAIL PROTECTED] 
> Gesendet: Dienstag, 2. Dezember 2003 15:16
> An: [EMAIL PROTECTED]
> Betreff: read (Off subject Help) ACCESS!!
> 
> Does anyone use Microsoft access?
> I need help with creating a query that will delete records, 
> If you can help me please email me directly!
> 
> Karl James
> [EMAIL PROTECTED]
> http://www.theufl.com/
> 
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: administering database on server

[Mike Blezien]

We just downloaded MySQL Front the other day... and it's one 
of the best that I have seen in awhile :)

just my $0.02

--
MikeBlezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://www.thunder-rain.com
Quality Web Hosting
http://www.justlightening.net
MSN: [EMAIL PROTECTED]
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Nitin wrote:
In my opinion MySql front is best and the best thing is further development
of already enriched software started again.
Nitin

- Original Message - 
From: "Colleen Dick" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, December 02, 2003 8:42 PM
Subject: Re: administering database on server



phpmyadmin is not reliable enough?

joffrey leevy wrote:

Hi all:

Anyone know a RELIABLE software program for
administering to a mysql database on a webhosting unix
server?
Thanks

__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: INSERT INTO tbl (SELECT) UNION (SELECT) ORDER BY field-does not work. Why?

[Wouter van Vliet]

George Georgeus wrote:
> Hi!
> I do not know how to use INSERT and UNION together.
> For example:
> INSERT INTO tmp_table_name (a)
> (SELECT a FROM table_name WHERE a=10 AND B=1 ORDER BY a LIMIT
> 10) UNION (SELECT a FROM table_name WHERE a=11 AND B=2 ORDER
> BY a LIMIT 10) ORDER BY a; It does not work. But I think it
> should. Have the MySQL developers forgotten something??
> I use MySQL 4.012
> 
> __
> __ Download Yahoo! Messenger now for a chance to win Live At
> Knebworth DVDs http://www.yahoo.co.uk/robbiewilliams

For as far as I know (and the error in my console expresses), the UNION is
not yet implemented in MySQL. This is not something the developers forgot,
but something they left out on purpose (up till now, I've heard word that
it's in development), to maintain the speed mysql is so proud of.

Besides that, why don't you just use:

> INSERT INTO tmp_table (a)
> SELECT a FROM table_name WHERE (a=10 AND b=1) OR (a=11 AND b=2);

Your example looks a bit odd, though .. What are you trying to do?



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

INSERT INTO tbl (SELECT) UNION (SELECT) ORDER BY field-does not work. Why?

[George Georgeus]

Hi!
I do not know how to use INSERT and UNION together.
For example:
INSERT INTO tmp_table_name (a)
(SELECT a FROM table_name WHERE a=10 AND B=1 ORDER BY
a LIMIT 10)
UNION
(SELECT a FROM table_name WHERE a=11 AND B=2 ORDER BY
a LIMIT 10)
ORDER BY a;
It does not work. But I think it should. Have the
MySQL
developers forgotten something??
I use MySQL 4.012


Download Yahoo! Messenger now for a chance to win Live At Knebworth DVDs
http://www.yahoo.co.uk/robbiewilliams

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: FullText search CJK in UTF-8

[Paul DuBois]

At 11:55 -0500 12/2/03, Hu, Yiguang wrote:
Is the fulltext search working for cjk text (use UTF-8) in 4.1.0 alpha ? I
have trouble doing search on cjk text in 4.1.0alpha, though it seems should
be working according to the following doc:
http://www.mysql.com/doc/en/Fulltext_Restrictions.html
UTF-8 support was added in 4.1.1:

http://www.mysql.com/doc/en/News-4.1.1.html

Any trick there ?

Thanks


--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: unixtime update syntax

[Ron McKeever]

Thats seems like a bug to me.

I would think FROM_UNIXTIME would take a unixtime stamp
and covert it to what it is. We know its from GMT

UNIX timestamp =
The timestamp is the current time measured in the number
of seconds since
the Unix Epoch (January 1 1970 00:00:00 GMT).

Ron

-Original Message-
From: Keith C. Ivey [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 8:55 AM
To: [EMAIL PROTECTED]
Subject: Re: unixtime update syntax


On 2 Dec 2003 at 7:57, Ron McKeever wrote:

> I have a db that gets data dumped into it. One of the columns gets
> unix timestamp data "utime". I what to covert that into a datetime
> column so I can utlize indexes and such. But I still what the unixtime
> to remain.

You can use indexes with a Unix time column about as well as you can
with DATETIME.  What sort of queries are you wanting to do?  Having
the extra column may be unnecessary.

> I believe I have a good way to do this but I'm not sure why it's not
> converting the date right ( see at bottom ):

It seems to be converting the date right.  FROM_UNIXTIME() does the
opposite of UNIX_TIMESTAMP().  It takes an integer representing a
Unix time and converts it to a DATETIME in local time (not GMT).  It
would be nice if there were a FROM_UNIXTIME_TO_GMT() function, but
there isn't.

--
Keith C. Ivey <[EMAIL PROTECTED]>
Tobacco Documents Online
http://tobaccodocuments.org


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

FullText search CJK in UTF-8

[Hu, Yiguang]

Is the fulltext search working for cjk text (use UTF-8) in 4.1.0 alpha ? I
have trouble doing search on cjk text in 4.1.0alpha, though it seems should
be working according to the following doc:
http://www.mysql.com/doc/en/Fulltext_Restrictions.html

Any trick there ?

Thanks

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: unixtime update syntax

[Keith C. Ivey]

On 2 Dec 2003 at 7:57, Ron McKeever wrote:

> I have a db that gets data dumped into it. One of the columns gets
> unix timestamp data "utime". I what to covert that into a datetime
> column so I can utlize indexes and such. But I still what the unixtime
> to remain.

You can use indexes with a Unix time column about as well as you can 
with DATETIME.  What sort of queries are you wanting to do?  Having 
the extra column may be unnecessary.

> I believe I have a good way to do this but I'm not sure why it's not
> converting the date right ( see at bottom ):

It seems to be converting the date right.  FROM_UNIXTIME() does the
opposite of UNIX_TIMESTAMP().  It takes an integer representing a
Unix time and converts it to a DATETIME in local time (not GMT).  It
would be nice if there were a FROM_UNIXTIME_TO_GMT() function, but
there isn't.

-- 
Keith C. Ivey <[EMAIL PROTECTED]>
Tobacco Documents Online
http://tobaccodocuments.org


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Mysql to LaTeX tables

[Thomas Spahni]

On Mon, 1 Dec 2003, Xavier Fernández i Marín wrote:

> Is there any application to export MySQL results of queries to LaTeX
> tables?  (preferably under GPL)

Xavier,

I have a hack for one of my applications. You can use this as a starting
point, but you will have to adjust directories. It converts to TeX (not
LaTeX) but prints very fancy tables (within my scope). Note: it has to fit
on one page with (using landscape mode). There is a limit on the numer of
columns which can be printed, depending on what data they contain.

Beware: this script can be dead slow. Someone should recode this in perl.

Thomas Spahni
-- 
filter: MySQL, Query

Shell script:

#!/bin/bash

# printtable

#
# usage: printtable  []
#
# use a select query to generate desired 
# example:
# echo "SELECT * FROM mytable;" | mysql --batch mydbase > filename
#
DB_VERSION='0.9.20 of 2002-01-07'
PROG_VERSION='1.0.0'
#
# print nice listings from tab delimited table data
#
##  --
##  This program is free software; you can redistribute it and/or
##  modify it under the terms of the GNU General Public License
##  as published by the Free Software Foundation; either version 2
##  of the License, or (at your option) any later version.
##
##  This program is distributed in the hope that it will be useful,
##  but WITHOUT ANY WARRANTY; without even the implied warranty of
##  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
##  GNU General Public License for more details.
##
##  You should have received a copy of the GNU General Public License
##  along with this program; if not, write to the Free Software
##  Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
##  --
#
# In a first run everything is deleted except delimiting TABs
#   and these TABs are converted to "X"s;
# Then sort rows and eliminate duplicates;
# One single line should remain, otherwise the
#   dataset is inconsistent.
# For the time we do not handle escaped TAB's within data
#   or TAB's within quoted datafields  ;-((
#   They appear as \t and will be treated as a space.

#  Section 1
# some user definable values

# how to print
# get a default; use global variable if set
# adjust argument to PRINTOPT as desired
if test -n "$PRINTER" ; then
PRINTOPT="-P$PRINTER"
else
PRINTOPT="-Plp"
fi

# Temporary space; must be writeable for all users!
TMPDIR=/tmp

# A place to keep user's output and a personal copy
# of listing.hyph
PRINT_DIR=~/.myprinttable

# Where the TeX templates can be found
# This is possibly changed by make during installation
TEMPLATES=/var/tmp/db

# printtable will load macros for some multilanguage characters
# by default and a package named german.sty.
# Make sure that the required hyphenation tables are loaded.
TEXPROG=tex

# Style file to process national language
TEXSTYLE=german.sty

# Space distribution mode;
# If it doesn't fit otherwise this may be set to "tight"
#SPACEMODE="normal"
SPACEMODE="narrow"
#SPACEMODE="tight"

# Turning this on will print some internal data on the screen
# Values: on / off
DEBUG=on

# - END of user configuration

if test "$1" = -h -o "$1" = --help -o "$1" = "?" -o "$1" = "-?" \
   -o "$1" = -V -o "$1" = -v -o "$1" = --version ; then
   echo "printtable Version $PROG_VERSION"
   echo "Print pretty formatted tables from TAB delimited data"
   echo ""
   echo "usage: printtable -h | --help | ? | -? | -V | -v | --version"
   echo "   printtable  [\"Title for the table\"]"
   exit 0
fi

#  Section 2 Check for required files/directories

# test for temporary space
if ! test -w "$TMPDIR" ; then
   echo "[$0] ERROR: can not write to tmp dir ${TMPDIR}; giving up"
   exit 1
fi

# If this user has no private print directory it is created
# with permissions restricted to the user. Then writability
# is tested for.
if ! test -d "$PRINT_DIR" ; then
   mkdir -p -m 0700 "$PRINT_DIR"
fi
if ! test -w "$PRINT_DIR" ; then
   echo "[$0] ERROR: can not write to personal dir ${$PRINT_DIR}; giving up"
   exit 1
fi

# Having a running TeX installation is a must
# let's check for it
if ! $TEXPROG --version >/dev/null 2>&1 ; then
   echo "[$0] ERROR: no executable $TEXPROG found; giving up"
   exit 1
fi

# See if there is the TeX style file at the right place
if ! test -r "${PRINT_DIR}/$TEXSTYLE" ; then
   if test -r "${TEMPLATES}/$TEXSTYLE" ; then
  cp "${TEMPLATES}/$TEXSTYLE" "${PRINT_DIR}/$TEXSTYLE"
  echo "installing $TEXSTYLE in $PRINT_DIR"
   else
  echo "[$0] WARNING: no TeX style $TEXSTYLE found; continuing ..."
   fi
fi

# See if there is a local list of additional hyphenations
if ! test -r "${PRINT_DIR}/listing.hyph" ; then
   # no personal hyphenations
   if test -r "${TEMPLATES}/listing.hyph" ; then
  # but systemwide hyphen patterns;
  # use these.
  cp ${TEMPLATES}/listing.hy

RE: unixtime update syntax

[Ron McKeever]

Here is those results:

mysql> select unix_timestamp(urtime) from t_test;
++
| unix_timestamp(urtime) |
++
| 1070296560 |
| 1070292960 |
++
2 rows in set (0.01 sec)

mysql> select * from t_test;
+---+--++-+
| a | b| utime  | urtime  |
+---+--++-+
| 1 | test | 1070296560 | 2003-12-01 08:36:00 |
| 2 | test | 1070292960 | 2003-12-01 07:36:00 |
+---+--++-+
2 rows in set (0.00 sec)

Any ideas?
Ron


-Original Message-
From: Dan Greene [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 8:30 AM
To: Ron McKeever; [EMAIL PROTECTED]
Subject: RE: unixtime update syntax


The time zone matters... your results are exactly 8 hours off... PST is
gmt -8.  So it looks like the from_unixtime function is converting to what
the time was locally at that moment in GMT.  Not what I would have expected
either

What do you get when you run-

select unix_timestamp(urtime) from t_test;



> -Original Message-
> From: Ron McKeever [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, December 02, 2003 10:58 AM
> To: [EMAIL PROTECTED]
> Subject: unixtime update syntax
>
>
> Anyone have a response to the following:
>
> Hi
>
> I have a db that gets data dumped into it. One of the columns
> gets unix
> timestamp data "utime".
> I what to covert that into a datetime column so I can utlize
> indexes and
> such.
> But I still what the unixtime to remain.
>
> I know I can get the data I want with php or mysql to convert
> it, but I need
> both columns for this.
> One with the unixtime, and one with it converted.
>
> I believe I have a good way to do this but I'm not sure why it's not
> converting the date right ( see at bottom ):
>
>
> mysql> desc t_test;
> ++-+--+-+-+---+
> | Field  | Type| Null | Key | Default | Extra |
> ++-+--+-+-+---+
> | a  | int(11) |  | PRI | 0   |   |
> | b  | varchar(10) | YES  | | NULL|   |
> | utime  | varchar(10) | YES  | | NULL|   |
> | urtime | datetime| YES  | | NULL|   |
>
> the data being inserted:
> insert into t_test (a,b,utime) values ('1','test','1070296560');
> insert into t_test (a,b,utime) values ('2','test','1070292960');
>
> Monday, December 1st 2003, 16:36:00 (GMT) = 1070296560
> Monday, December 1st 2003, 15:36:00 (GMT) = 1070292960
>
> mysql> select * from t_test;
> +---+--+++
> | a | b| utime  | urtime |
> +---+--+++
> | 1 | test | 1070296560 | NULL   |
> | 2 | test | 1070292960 | NULL   |
> +---+--+++
> 2 rows in set (0.00 sec)
>
>
> Syntax I'm using to update the datetime column from the
> varchar columnis:
> mysql> UPDATE t_test SET urtime = FROM_UNIXTIME(utime) WHERE
> urtime is NULL;
>
> mysql> select * from t_test;
> +---+--++-+
> | a | b| utime  | urtime  |
> +---+--++-+
> | 1 | test | 1070296560 | 2003-12-01 08:36:00 |
> | 2 | test | 1070292960 | 2003-12-01 07:36:00 |
> +---+--++-+
> 2 rows in set (0.01 sec)
>
> the urtime I thought should read:
> 2003-12-01 16:36:00
> 2003-12-01 15:36:00
>
> Does it matter if im on the PST if I get the data from GMT???
>
> Help
> Ron
>
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Escaping single quotes

[Jay Blanchard]

[snip]
> The site is a news based site and has the use of the single quote or
> apostrophe (') through most of it's articles. I think that each
article
> at present is an external .txt file that is pulled in to Oracle. If I
> carried on this method of having an external .txt file would that over
> come the necessity to escape (\') every single quote in each article?
>
> If so, how might I be able to pull that data through so that it loads
> into the web browser. I asume it is some sort of http:// based link as
> it would be for an image. Is there any special kind of formatting I
> have to do to the text file for it to show as html?
[/snip]

What language are you using to process the DB requests.

DB-->*something here to process*>HTML

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: unixtime update syntax

[Dan Greene]

The time zone matters... your results are exactly 8 hours off... PST is gmt -8.  So it 
looks like the from_unixtime function is converting to what the time was locally at 
that moment in GMT.  Not what I would have expected either

What do you get when you run-

select unix_timestamp(urtime) from t_test;



> -Original Message-
> From: Ron McKeever [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, December 02, 2003 10:58 AM
> To: [EMAIL PROTECTED]
> Subject: unixtime update syntax
> 
> 
> Anyone have a response to the following:
> 
> Hi
> 
> I have a db that gets data dumped into it. One of the columns 
> gets unix
> timestamp data "utime".
> I what to covert that into a datetime column so I can utlize 
> indexes and
> such.
> But I still what the unixtime to remain.
> 
> I know I can get the data I want with php or mysql to convert 
> it, but I need
> both columns for this.
> One with the unixtime, and one with it converted.
> 
> I believe I have a good way to do this but I'm not sure why it's not
> converting the date right ( see at bottom ):
> 
> 
> mysql> desc t_test;
> ++-+--+-+-+---+
> | Field  | Type| Null | Key | Default | Extra |
> ++-+--+-+-+---+
> | a  | int(11) |  | PRI | 0   |   |
> | b  | varchar(10) | YES  | | NULL|   |
> | utime  | varchar(10) | YES  | | NULL|   |
> | urtime | datetime| YES  | | NULL|   |
> 
> the data being inserted:
> insert into t_test (a,b,utime) values ('1','test','1070296560');
> insert into t_test (a,b,utime) values ('2','test','1070292960');
> 
> Monday, December 1st 2003, 16:36:00 (GMT) = 1070296560
> Monday, December 1st 2003, 15:36:00 (GMT) = 1070292960
> 
> mysql> select * from t_test;
> +---+--+++
> | a | b| utime  | urtime |
> +---+--+++
> | 1 | test | 1070296560 | NULL   |
> | 2 | test | 1070292960 | NULL   |
> +---+--+++
> 2 rows in set (0.00 sec)
> 
> 
> Syntax I'm using to update the datetime column from the 
> varchar columnis:
> mysql> UPDATE t_test SET urtime = FROM_UNIXTIME(utime) WHERE 
> urtime is NULL;
> 
> mysql> select * from t_test;
> +---+--++-+
> | a | b| utime  | urtime  |
> +---+--++-+
> | 1 | test | 1070296560 | 2003-12-01 08:36:00 |
> | 2 | test | 1070292960 | 2003-12-01 07:36:00 |
> +---+--++-+
> 2 rows in set (0.01 sec)
> 
> the urtime I thought should read:
> 2003-12-01 16:36:00
> 2003-12-01 15:36:00
> 
> Does it matter if im on the PST if I get the data from GMT???
> 
> Help
> Ron
> 
> 
> 
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Escaping single quotes

[Thomas Spahni]

Matthew,

I really don't understand the question. Apostrophes must be properly
escaped when text is inserted into the MySQL db, but any perl script will
easily do this for you. You may convert to HTML at the same time.

If the database gives nothing but a path to a *.txt source then your HTML
code should invoke a cgi script which in turn pulls a pathname from the
database, gets the data from the file, converts it to HTML and returns it
to the httpd.

Sorry, but I can't be more specific

Thomas Spahni

On Mon, 1 Dec 2003, Matthew Stuart wrote:

> I am going to take over an existing website and in its present format
> it is a site powered by an Oracle DB. I will be migrating to MySQL.
>
> The site is a news based site and has the use of the single quote or
> apostrophe (') through most of it's articles. I think that each article
> at present is an external .txt file that is pulled in to Oracle. If I
> carried on this method of having an external .txt file would that over
> come the necessity to escape (\') every single quote in each article?
>
> If so, how might I be able to pull that data through so that it loads
> into the web browser. I asume it is some sort of http:// based link as
> it would be for an image. Is there any special kind of formatting I
> have to do to the text file for it to show as html?
>
> TIA
> Mat


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: administering database on server

[Nitin]

In my opinion MySql front is best and the best thing is further development
of already enriched software started again.

Nitin

- Original Message - 
From: "Colleen Dick" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, December 02, 2003 8:42 PM
Subject: Re: administering database on server


> phpmyadmin is not reliable enough?
>
> joffrey leevy wrote:
> > Hi all:
> >
> > Anyone know a RELIABLE software program for
> > administering to a mysql database on a webhosting unix
> > server?
> >
> > Thanks
> >
> > __
> > Do you Yahoo!?
> > Free Pop-Up Blocker - Get it now
> > http://companion.yahoo.com/
> >
>
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Date data type issue

[Nitin]

now() gives current date and time. use current_date() instead.

hope it helps
Nitin

- Original Message - 
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, December 02, 2003 9:32 PM
Subject: Date data type issue


> Having a problem with a Date data type.
>
> Here's the query:
>
> SELECT distinct date_format(auditdate, "%Y%M" ) as listUrl, date_format(
> auditdate, "%Y%m" ) as blank
>   FROM quality_history
>   WHERE auditdate <> Now()
>   ORDER BY listUrl desc
>   LIMIT 6
>
> auditdate is a DATE datatype
>
> When I run this query the result datatype for both listUrl and blank is a
> BINARY CHAR! Is that correct? Why a BINARY? Also, when I take out the
> DISTINCT I get back a TIMESTAMP (pretty weird, huh?). I'm obviously
missing
> something. Any ideas on how I can return just a regular CHAR (or TEXT, or
> VARCHAR, or DATE, or anything that looks like a DATE with the format of
> %Y%M)? By the way, I'm running 3.23.51 on Windows 2000
>
> Thanks in advance!
>
>
> T.J. Kuhn
>
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: administering database on server

[Colleen Dick]

phpmyadmin is not reliable enough?

joffrey leevy wrote:
Hi all:

Anyone know a RELIABLE software program for
administering to a mysql database on a webhosting unix
server?
Thanks

__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Date data type issue

[tjkuhn]

Having a problem with a Date data type.

Here's the query:

SELECT distinct date_format(auditdate, "%Y%M" ) as listUrl, date_format(
auditdate, "%Y%m" ) as blank
  FROM quality_history
  WHERE auditdate <> Now()
  ORDER BY listUrl desc
  LIMIT 6

auditdate is a DATE datatype

When I run this query the result datatype for both listUrl and blank is a
BINARY CHAR! Is that correct? Why a BINARY? Also, when I take out the
DISTINCT I get back a TIMESTAMP (pretty weird, huh?). I'm obviously missing
something. Any ideas on how I can return just a regular CHAR (or TEXT, or
VARCHAR, or DATE, or anything that looks like a DATE with the format of
%Y%M)? By the way, I'm running 3.23.51 on Windows 2000

Thanks in advance!


T.J. Kuhn




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

unixtime update syntax

[Ron McKeever]

Anyone have a response to the following:

Hi

I have a db that gets data dumped into it. One of the columns gets unix
timestamp data "utime".
I what to covert that into a datetime column so I can utlize indexes and
such.
But I still what the unixtime to remain.

I know I can get the data I want with php or mysql to convert it, but I need
both columns for this.
One with the unixtime, and one with it converted.

I believe I have a good way to do this but I'm not sure why it's not
converting the date right ( see at bottom ):


mysql> desc t_test;
++-+--+-+-+---+
| Field  | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| a  | int(11) |  | PRI | 0   |   |
| b  | varchar(10) | YES  | | NULL|   |
| utime  | varchar(10) | YES  | | NULL|   |
| urtime | datetime| YES  | | NULL|   |

the data being inserted:
insert into t_test (a,b,utime) values ('1','test','1070296560');
insert into t_test (a,b,utime) values ('2','test','1070292960');

Monday, December 1st 2003, 16:36:00 (GMT) = 1070296560
Monday, December 1st 2003, 15:36:00 (GMT) = 1070292960

mysql> select * from t_test;
+---+--+++
| a | b| utime  | urtime |
+---+--+++
| 1 | test | 1070296560 | NULL   |
| 2 | test | 1070292960 | NULL   |
+---+--+++
2 rows in set (0.00 sec)


Syntax I'm using to update the datetime column from the varchar columnis:
mysql> UPDATE t_test SET urtime = FROM_UNIXTIME(utime) WHERE urtime is NULL;

mysql> select * from t_test;
+---+--++-+
| a | b| utime  | urtime  |
+---+--++-+
| 1 | test | 1070296560 | 2003-12-01 08:36:00 |
| 2 | test | 1070292960 | 2003-12-01 07:36:00 |
+---+--++-+
2 rows in set (0.01 sec)

the urtime I thought should read:
2003-12-01 16:36:00
2003-12-01 15:36:00

Does it matter if im on the PST if I get the data from GMT???

Help
Ron






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: administering database on server

[joffrey leevy]

wow!

thanks all for the superquick response.

J

__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: administering database on server

[Jay Blanchard]

[snip]
Anyone know a RELIABLE software program for
administering to a mysql database on a webhosting unix
server?
[/snip]

phpmyadmin

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

administering database on server

[joffrey leevy]

Hi all:

Anyone know a RELIABLE software program for
administering to a mysql database on a webhosting unix
server?

Thanks

__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Debug options for mysqlimport?

[Greg G]

I'm having a tough time with the debug options for mysqlimport.  I've 
check the docs, but they're as clear as mud.  I've tried a number of 
combinations of -#d:t:o,filename and everything else I can think of, but 
I can't get any debug information.

What I'm really looking for is to get the text of warnings that were 
generated during and import.

-Greg G



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql remote access on linux

[jeffrey_n_Dyke]

You should not need to restart , you  will need to 'flush privileges'
though.  has that been done?

Jeff


   
 
  John Nichel  
 
  <[EMAIL PROTECTED]To:   Louis van der Merwe <[EMAIL 
PROTECTED]>  
  om>  cc:   [EMAIL PROTECTED] 
 
   Subject:  Re: mysql remote access on 
linux   
  12/02/2003 10:14 
 
  AM   
 
   
 
   
 




Louis van der Merwe wrote:
> Hi,
>
> Can someone please tell me how to set up a linux mysql server to accept
> connections from remote machines.
>
> I have tried creating users for all of the remote host, and creating
> users with host '%' and '*', everytime I try to connect using myodbc or
> mysqlcc, I get the error "host {host name} is not allowed to connect to
> this mysql server"

Did you restart/reload MySQL after adding the users?

--
By-Tor.com
It's all about the Rush
http://www.by-tor.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]






-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: mysql remote access on linux

[Peter Lovatt]

Hi

try running

 GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER
   ON database.*
   TO [EMAIL PROTECTED]
   IDENTIFIED BY 'pass';

where 'hostname' is the one in the error message


HTH

Peter




-Original Message-
From: Louis van der Merwe [mailto:[EMAIL PROTECTED]
Sent: 02 December 2003 15:01
To: [EMAIL PROTECTED]
Subject: mysql remote access on linux


Hi,

Can someone please tell me how to set up a linux mysql server to accept
connections from remote machines.

I have tried creating users for all of the remote host, and creating
users with host '%' and '*', everytime I try to connect using myodbc or
mysqlcc, I get the error "host {host name} is not allowed to connect to
this mysql server"

I really need to resolve this problem soon.

Why don't I get the same error when running mysql on windows ?


Thanks

Louis


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql remote access on linux

[John Nichel]

Louis van der Merwe wrote:
Hi,

Can someone please tell me how to set up a linux mysql server to accept
connections from remote machines.
I have tried creating users for all of the remote host, and creating
users with host '%' and '*', everytime I try to connect using myodbc or
mysqlcc, I get the error "host {host name} is not allowed to connect to
this mysql server"
Did you restart/reload MySQL after adding the users?

--
By-Tor.com
It's all about the Rush
http://www.by-tor.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysql remote access on linux

[Louis van der Merwe]

Hi,

Can someone please tell me how to set up a linux mysql server to accept
connections from remote machines.

I have tried creating users for all of the remote host, and creating
users with host '%' and '*', everytime I try to connect using myodbc or
mysqlcc, I get the error "host {host name} is not allowed to connect to
this mysql server"

I really need to resolve this problem soon.

Why don't I get the same error when running mysql on windows ?


Thanks

Louis


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Non-conditional LEFT JOIN

[Wouter van Vliet]

On dinsdag 2 december 2003 14:44 news told the butterflies:
> In article <[EMAIL PROTECTED]>,
> "Director General: NEFACOMP" <[EMAIL PROTECTED]> writes:
> 
> > [1  ] Hi group,
> 
> > I want to do a LEFT JOIN that takes no condition.
> 
> > For example I have the following tables:
> 
> > table_1table_2
> > ---  -
> > 1A
> > 2B
> > 3C
> > 4D
> > 5E
> 
> > And I want my result to be:
> 
> > table_result
> > ---
> > 1A
> > 2B
> > 3C
> > 4D
> > 5E
> 
> 
> > The result table has got two fields!!
> > By doing SELECT field_1, field_2 FROM table_1, table_2 I get several
> > records because it does a full join.
> > I want MySQL to just pick a record from table_1 and picks
> another one from table_2 without a specified condition.
> 
> > Which type of JOIN should I use?
> 
> That's not a JOIN at all.  How should MySQL know which row of
> table_1 belongs to which row of table_2?

There is, however, a way to do it. You will need some sort of
scripting/programming language to do it. Example in PHP.

$Link = mysql_query('SELECT * FROM table_1');
$Result = Array();
for($i=0;$Row=mysql_fetch_row($Result);$i++) $Result[$i] = $Row;
$Link = mysql_query('SELECT * FROM table_2');
for($i=0;$Row=mysql_fetch_row($Result);$i++) $Result[$i] = $Row;

And there it is .. right in the $Result array. But tell me, what do you want
to do with it? Maybe there's a (conceptually speaking) better way to do it,
with which I'd be happy to help you ;)

Wouter




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB "lock in share mode" on union

[Heikki Tuuri]

Bill,

I tested this on the latest 4.1.1 snapshot, and it worked ok: client 2
waited for client 1 to commit. Please test again.

Best regards,

Heikki
Innobase Oy
http://www.innodb.com
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM
tables

Order MySQL support from http://www.mysql.com/support/index.html



Client 1:

mysql> CREATE TABLE `table1` (
->   `id` int(11) NOT NULL default '0',
->   PRIMARY KEY  (`id`)
-> ) TYPE=InnoDB
->
-> ;
Query OK, 0 rows affected (0.10 sec)

mysql> CREATE TABLE `table2` (
->   `id` int(11) NOT NULL default '0',
->   PRIMARY KEY  (`id`)
-> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into table2 values (2)
-> ;
Query OK, 1 row affected (4.78 sec)

mysql> insert into table1 values (3);
Query OK, 1 row affected (5.66 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> (select id from table1 where id > 1 lock in share mode) union (select
-> id from table2 where id > 1);
++
| id |
++
|  3 |
|  2 |
++
2 rows in set (33.36 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>

Client 2:

mysql> delete from table1
-> where id=3;
Query OK, 1 row affected (22.33 sec)

mysql>




From: "Bill Easton" ([EMAIL PROTECTED])
Subject: InnoDB "lock in share mode" on union
This is the only article in this thread
View: Original Format
Newsgroups: mailing.database.myodbc
Date: 2003-11-10 07:05:16 PST

How do I lock rows in a union query so that I know they won't change during
the rest of my transaction?

I want to do the following query, using "LOCK IN SHARE MODE":
(select id from table1 where id > 1)
union
(select id from table2 where id > 1);

If I try:
(select id from table1 where id > 1 LOCK IN SHARE MODE)
union
(select id from table2 where id > 1);
it doesn't appear to do the locking.  Another process can delete a record
from table1 which was contained
in the result.  It appears that I see a consistent snapshot, and the record
is gone after I commit.
(See below.)  I get the same result if I use FOR UPDATE.

MySQL doesn't allow me to put LOCK IN SHARE MODE after the second select or
after the whole union.

I'm using MySQL 4.0.13-nt-log.

--  Example follows

In the following, table1 and table2 have the following structure:

CREATE TABLE `table1` (
  `id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=InnoDB

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> (select id from table1 where id > 1 LOCK IN SHARE MODE) union (select
id from table2 where id > 1);
++
| id | ++
|  3 |
|  2 |
++
2 rows in set (0.00 sec)

*** another process does: "delete from table1
where id=3;"

mysql> (select id from table1 where id > 1 lock in share mode) union (select
id from table2 where id > 1);
++
| id | ++
|  3 |
|  2 |
++
2 rows in set (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> (select id from table1 where id > 1 lock in share mode) union (select
id from table2 where id > 1);
++
| id | ++
|  2 |
++
1 row in set (0.00 sec)


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Help

[Chris Boget]

> Access.  However, when I run it against MySQL I get an error. 
> select l.ltsysid,l.lientraknum, c.name from lientrak as l, customer as c
> where l.custsysid=c.custsysid
> and l.ltsysid in (select l2.ltsysid from lientrak as l2 where
> l2.lientraknum
> like '2003-%')

Sub queries are not going to be available until version 4.1.  You'll need
to re-write the above query using an outer (?) join.  I'm not sure what the
exact syntax should be and I'm sure someone will pipe up with that info.

Chris


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Non-conditional LEFT JOIN

[Wouter van Vliet]

On dinsdag 2 december 2003 14:06 Director General: NEFACOMP told the
butterflies: 
> Hi group,
> 
> I want to do a LEFT JOIN that takes no condition.
> 
> For example I have the following tables:
> 
> table_1table_2
> ---  -
> 1A
> 2B
> 3C
> 4D
> 5E
> 
> And I want my result to be:
> 
> table_result
> ---
> 1A
> 2B
> 3C
> 4D
> 5E
> 
> 
> The result table has got two fields!!
> By doing SELECT field_1, field_2 FROM table_1, table_2 I get
> several records because it does a full join.
> I want MySQL to just pick a record from table_1 and picks
> another one from table_2 without a specified condition.
> 
> 
> Which type of JOIN should I use?
> 
> 

use
(..)
FROM table_1 IMAGINARY JOIN table_2 ON table_1.getRowNumber() =
table_2.getRowNumber()

... no, seriously ... a DBMS doesn't implement illogical things.

:P,
Wouter




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Query Help

[Greg Jones]

I am in the process of migrating a database and some code from an MSSQL
database and am running into a little problem with some queries.  Would
appreciate any help anyone could offer.
 
I currently have this query which runs fine when using either MSSQL or
Access.  However, when I run it against MySQL I get an error. 
 
select l.ltsysid,l.lientraknum, c.name from lientrak as l, customer as c
where l.custsysid=c.custsysid
and l.ltsysid in (select l2.ltsysid from lientrak as l2 where l2.lientraknum
like '2003-%')
 
Normally I pass in the '2003-%' as a variable.
 
MySql Front gives me the following error:
 
'You have an error in your SQL syntax.  Check the manual that corresponds to
your MySQL server version for the right syntax to use near 'select
l2.ltsysid from lientrak as l2 where l2.lientraknum like'
 
I am running MySQL Front version 2.5, and MySql version 4.0.15.
 
Thanks,
 
Greg

RE: Non-conditional LEFT JOIN

[Jay Blanchard]

[snip]
I want to do a LEFT JOIN that takes no condition.

For example I have the following tables:

table_1table_2
---  -
1A
2B
3C
4D
5E

And I want my result to be:

table_result
---
1A
2B
3C
4D
5E
[/snip]

Try ...

SELECT a.field_1, b.field_2 
FROM table_1 a LEFT OUTER JOIN table_2 b
WHERE b.field_2 IS NOT NULL

You are returning a Cartesian product (http://www.mysql.com/join) in
which every row in the first table will be joined onto all rows in the
second table. The LEFT OUTER JOIN in my example may not save you here as
there is no ON condition (it may throw an error even)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Non-conditional LEFT JOIN

[Director General: NEFACOMP]

Hi group,

I want to do a LEFT JOIN that takes no condition.

For example I have the following tables:

table_1table_2
---  -
1A
2B
3C
4D
5E

And I want my result to be:

table_result
---
1A
2B
3C
4D
5E


The result table has got two fields!!
By doing SELECT field_1, field_2 FROM table_1, table_2
I get several records because it does a full join.
I want MySQL to just pick a record from table_1 and picks another one from table_2 
without a specified condition.


Which type of JOIN should I use?


Thanks,
__
NZEYIMANA Emery Fabrice
NEFA Computing Services, Inc.
P.O. Box 5078 Kigali
Office Phone: +250-51 11 06
Office Fax: +250-50 15 19
Mobile: +250-08517768
Email: [EMAIL PROTECTED]
http://www.nefacomp.net/

Re: Two version

[Egor Egorov]

"Arnoldus Th.J. Koeleman" <[EMAIL PROTECTED]> wrote:
> 
> I try to run to different version 3.23 & 4.0 with ome my.cnf file with
> different mysqld section but
> 
> Only one is started.
> 
> Anybody have any idea how to run two versions

There are several ways and they are described at:
http://www.mysql.com/doc/en/Multiple_servers.html



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Backup is Alphabetical

[Heikki Tuuri]

Ian,

when you import the dump(s), put

SET FOREIGN_KEY_CHECKS=0;

at the start of the dump file. I think that the latest version of mysqldump
in upcoming 4.1.1 will put that automatically.

Best regards,

Heikki
Innobase Oy
http://www.innodb.com
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM
tables

Order MySQL support from http://www.mysql.com/support/index.html


- Alkuperäinen viesti - 
Lähettäjä: "Dwight Ian" <[EMAIL PROTECTED]>
Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Kopio: <[EMAIL PROTECTED]>
Lähetetty: Saturday, November 29, 2003 8:33 AM
Aihe: Re: Backup is Alphabetical


> Hi,
> I tried to set it in mysqld and it still alphabetize the tables...
> my mysqldump syntax is this.
>
> mysqldump -acr c:\backup.sql prism
> where prism is the database.
>
> All I wanted to happen is for the the tables to be in order of referential
integrity NOT alphabetical
>
> Heikki Tuuri <[EMAIL PROTECTED]> wrote:
> Dwight,
>
> SET FOREIGN_KEY_CHECKS=0;
>
> please address these general MySQL/InnoDB questions to
> [EMAIL PROTECTED]
>
> Regards,
>
> Heikki
>
>
>
> - Alkuperäinen viesti - 
> Lähettäjä: "Dwight Ian"
> Vastaanottaja: "Heikki Tuuri"
> Lähetetty: Thursday, November 27, 2003 3:35 AM
> Aihe: Backup is Alphabetical
>
>
> >
> > Hi.
> >
> > I have a table in innodb. I backup it using mysqldump and it did work.
But
> the problem is that the tables are listed in alphabetical order!. Since
the
> tables need to be in order of referencial integrity, it will not work if i
> restore it.
> >
> > What will I do.
> >
> > Thanks.
> >
> >
> >
> > -
> > Do you Yahoo!?
> > Free Pop-Up Blocker - Get it now
>
>
>
> -
> Do you Yahoo!?
> Free Pop-Up Blocker - Get it now


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Error 2002 egroka

[Victoria Reznichenko]

jeepcreep <[EMAIL PROTECTED]> wrote:
> 
> That is what I did.

Check with

ps ax | grep mysqld

if MySQL server is really running.

> That's when I get the error 2002 message. Maybe my problem is not with the 
> mysql.sock file but something else. 
> 
> Daniel Kasak <[EMAIL PROTECTED]> wrote:jeepcreep wrote:
> 
>>Just loaded mySQL v4 to LINUX Mandrake v9.2 via RPM. The error 2002 talks
>>of a mysql.sock file. I can't find this file anywhere on my box. Can't
>>figure out why I can't find it as I'm preety sure the install was
>>completed successfully. If I want to rebuild it, though, what's in it?
>>
>> 
>>
> The mysql.sock file only exists while MySQL is running.
> Maybe you installed MySQL but haven't started it.
> I don't know how Mandrake handles starting & stopping services, but you 
> should have a script: /etc/init.d/mysql ( or something like that ).
> Try:
> 
> /etc/init.d/mysql start
> 
> That's what starts it on mine anyway.
> 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Export in XML

[Wouter van Vliet]

On maandag 1 december 2003 15:40 Jay Blanchard told the butterflies:
> [snip]
> Is there a way to export/import MySQL tables in XML format? [/snip]
> 
> Well Todd, it is a little more complex than this. How much do
> you know about XML?

In fact, exporting is quite simple. Just give

> mysqldump --xml --password=[password] \
[-u username] \
databasename \
[tables]

A try. Of course, substitute the correct values and stuff. Will give you
pretty neat output.

;),
Wouter





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Reducing and counting duplicate rows

[Wouter van Vliet]

On dinsdag 2 december 2003 4:07 Skippy told the butterflies:
> I must admit I'm pretty stumped here. I'm using MySQL 4.0.12.
> 
> I have several tables with info, and one which servers as a
> link between them (has id's that refer the id's in all the others).
> 
> The info tables hold people(table 1), which get assigned to groups(2)
> by directives(3) and each time they are [re]assigned they get a
> level(4) increase (levels vary from 5->1). The central link
> table is called advancements.
> 
> I'm trying to pick one year Y (the year is in the directives
> table) and count how many people from group G were at level L
> that year. So I have [G]roup, [Y]ear and [L]evel as given parameters.
> 
> My problems:
> * I can't count for year=Y because I would miss people who
> got a level increase in earlier years (they now count as
> being at that level). So I thought I'd look for year<=Y.
> * But this creates another problem: people who got more than
> one level increase in years <= Y appear once for every level
> change! So this increases the total count when it shouldn't.
> * I can't add a condition level=L because this would miss
> later levels that the person got.
> 
> select person_id,level_id from directives d join advancements a on
> (d.id=a.directive_id) where d.year<=2002 and group_id=1;
> 
> This gives:
> 
> > person_id | level_id |
> ...
> >   179 |5 |
> >   180 |2 |
> >   180 |3 |
> ...
> 193 rows in set (0.02 sec)
> 
> Notice how person 180 appears twice, once for level 2 and
> once for level 3. This inflates the total row count (they
> should really only be 179).
> 
> How can I do this so that I only get 180 at level 2, and how
> can I then apply a condition that will only show me those at level 2?
> 
> I'm not even sure that I'm right about this join anymore.
> Everything I tried either gives me the inflated results due
> to duplicate persons, or I get the right amount but can't
> filter properly for a certain level.
> 
> --
> Skippy

If you want to select those who HAVE BEEN at level 2 in the yeaer Y, you can
just do "level_id = 2". But I guess you want to know who currently IS at
level 2 IN the year Y? In that case, when using a 4.0.x I guess there is no
way you are going to do that. I tried and couldn't. I know though, that with
a little more puzzling I could do it in 4.1.x since (as I heard) that one
supports the so-called "subselects". 

For now, you'd be stuck with a little bit of some sort of scripting language
to parse the results... i'm sorry.




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Sub--

[Avenger]

Best regards

-- 
Avenger <[EMAIL PROTECTED]>
Exceed PHP (http://www.phpe.net)


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Select Records From the Database

[Wouter van Vliet]

On dinsdag 2 december 2003 9:41 Nitin told the butterflies:
> is this declared in php or javascript??
> depends on the syntax.
> 
> - Original Message -
> From: "Caroline Jen" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Tuesday, December 02, 2003 9:38 AM
> Subject: Select Records From the Database
> 
> 
> > I got "null" after selecting records from a table in
> > the database.  The table is created by me.  I know how
> > many records will be selected.  Just cannot be null.
> > 
> > I cannot help wonder if my SELECT statement is correct
> > - I want records to be selected if user_role is equal
> > to the userrole I supplied AND if journal_category is
> > equal to the category I supplied.  My statement is:
> > 
> > String query = "SELECT user_name FROM members WHERE
> > user_role = '" + userrole + "' AND journal_category = '" + category
> > + "'"; 
> > 
> > Do anybody see any mistake?
> > 

Looks like JavaScript to me .. 
- php and Perl use $Var for variables and concats with a .
- ASP concats with the & sign, not sure about the "Variable name
prepending character" there might be none..
- JavaScript concats with + and has no "variable name prepending
character"

But unless you're sending the query over the QueryString or some other cool
thing in JavaScript I don't think I see anything happening with this little
fella.

Where and how are you trying to do something, Caroline?

Wouter




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Select Records From the Database

[Nitin]

is this declared in php or javascript??
depends on the syntax.

- Original Message - 
From: "Caroline Jen" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, December 02, 2003 9:38 AM
Subject: Select Records From the Database


> I got "null" after selecting records from a table in
> the database.  The table is created by me.  I know how
> many records will be selected.  Just cannot be null.
>
> I cannot help wonder if my SELECT statement is correct
> - I want records to be selected if user_role is equal
> to the userrole I supplied AND if journal_category is
> equal to the category I supplied.  My statement is:
>
> String query = "SELECT user_name FROM members WHERE
> user_role = '" + userrole + "' AND journal_category =
> '" + category + "'";
>
> Do anybody see any mistake?
>
> __
> Do you Yahoo!?
> Free Pop-Up Blocker - Get it now
> http://companion.yahoo.com/
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]