Louie Miranda [EMAIL PROTECTED] wrote:
I have one table named Category under that all values contains Books
How can i make all Books into Stocks?
Use UPDATE statement like this:
UPDATE Category SET column='Stocks' WHERE column='Books';
--
For technical support contracts, goto
Tedman Leung [EMAIL PROTECTED] wrote:
I've been unable to find any information on this issue so I'm not quite
sure if it's a bug or a new restriction. In either case I think it's a bug
due to it's inconsistency with itself.
create table foo (id int not null default -1)
does not
Table created with out any error in MySQL 4.0.17-max-debug win
mysql create table foo (id int not null default -1);
Query OK, 0 rows affected (0.22 sec)
mysql explain foo;
+---+-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
Table employee:
employeeNumber : int (primary key)
age: int
salary : float
Would an index improve performance for these queries?
SELECT * FROM USERS ORDER BY age DESC LIMIT 100,200
(index on age)
SELECT * FROM USERS ORDER BY salary DESC LIMIT 200,300
(index on salary)
Hi,
I have many smallish, discrete MySQL databases, each of which I would
like to backup individually (mysqldump seems fine for this). However,
there's no point re-backing up a database that has not changed since the
last time it was backed up. So how can I tell if when a MySQL database
was last
hi, here are the two sql logs for the same code when one is run independently
and when embedded in the other when it is embedded in the other it hangs and
the application crashes so could any one tell what the problem can be..it
crashes for the reason
0x4054afde in my_SQLPrepare () from
Hi
I have to rebuild a database for someone that currently has in excess of
twelve tables with around 20K records in the biggest table, the whole
structure needs looking at as the original designer used char and int for
everything, from dates to yes/no fields!!
The db is accessed from a VB
Hi all,
I just tried to change a field with a type of LongText to Text and with a width of 10
characters in phpmyadmin. It accepted the change of the field type top text but
didn't seem to accept the change in field length to 10. To test , I entered 20
characters into a text field in my
I have a problem with a table that is too big it contains around
35,000,000 lines and each end of month i have to take out about
20,000,000 lines from it
so my delete command is :
delete from table where column_value**
on a column that is indexed.
But each time i do that i have mysql that
I think MyODBC in this situation is irelevant. ODBC reinterpret the SQL
language (and is good for some universal data components like ADO,DAO,...),
but MySQL C API is native. Also you can't connect by MyODBC using MYSQL C
API.
So you really didn't forget some parameter??, it seems like.
Otherwise
On Fri, Feb 06, 2004 at 02:58:50PM +0400, Vinay wrote:
I have a problem with a table that is too big it contains around
35,000,000 lines and each end of month i have to take out about
20,000,000 lines from it
so my delete command is :
delete from table where column_value**
on a
It sounds a bit wierd, if you're searching thru a table getting one rec at a
time. Suely you can go Select * from myTable to retrieve all recs. it
sounds nuts to do it any other to be honest!
As for the fields, mySQL treats booleans (yes,no) as Char (1). You should of
course change the type
Vinay, u cancreatea temporary table
CREATE TEMPORARY TABLE TMP
Select * from ORIGINAL WHERE 'records to preserve';
TRUNCATE TABLE ORIGINAL;
INSERT INTO ORIGINAL SELECT * FROM TMP;
DROP TABLE TMP;
Alejandro.
---Mensaje original---
De: Vinay
Fecha: 06/02/04 08:32:04
Para:
Hi all,
now that MySQL 5 has support for Stored Procedures,
i was wondering if anyone managed to port (or migrate) the
Stored Procedures from MS Access to MySQL ??
(With Stored Procedures i'm referring to queries as they're
called in MS Access)
Regards to all / Mihalidis
--
MySQL
Hi everyone,
I am having a rough time with a query, which seems to be taking so long
it hangs the systems.
SELECT
l.CatalogNumber,
l.PDFLink,
l.PDFName,
l.Title,
p.PublisherName,
c.ComposerLname,
a.ArrangerLname,
l.Price,
l.Description,
o.Alias
Hi,
I have a table to log sessions and user (connect_id)
id's, start time etc - see below
+---+-+--+-+-+---+
| Field | Type| Null | Key | Default |
Extra |
+---+-+--+-+-+---+
| connect_id|
You will need parentheses around the 'or' clauses of your where clause.
You also don't seem to join the categories table with any other tables. If you don't
join tables you will create what is called a 'cross product' query. If table A has 10
rows and table B has 20 rows then querying A
rmck [EMAIL PROTECTED] wrote:
I understand that I need to update the db's cardinality for this table
I need speed
Should I run CHECK TABLE or ANALYZE TABLE or myismachk -a?? I need the quickest one
because with 56179085 records this could take a while...
myisamchk -a does the
For databases I usually just make a backup for each day of the month.
After all, disk space is cheap. So if a month has 31 days, I have 31
backups. That gives you about 30 days to discover any corruption that
may have occurred in a database. A crashed database is obvious, but
corruption
Hi Evelyn,
How would I do that - would something like this be what you had in mind?
left join categories o on o.CategoryID = l.CategoryID
This goes in the WHERE clause, right?
Thanks!
-Erich-
-Original Message-
From: Schwartz, Evelyn [mailto:[EMAIL PROTECTED]
Sent: Friday,
Add a timestamp field to each table.
Phil wrote:
Hi,
I have many smallish, discrete MySQL databases, each of which I would
like to backup individually (mysqldump seems fine for this). However,
there's no point re-backing up a database that has not changed since the
last time it was backed up.
I'm not 100% sure on this, but what about the .myd file timestamp?
-Original Message-
From: gerald_clark [mailto:[EMAIL PROTECTED]
Sent: Friday, February 06, 2004 9:09 AM
To: Phil
Cc: [EMAIL PROTECTED]
Subject: Re: How to determine when a MySQL database was last modified?
Add a
Thanks. But I would have thought that such information would have been
kept automatically somewhere by the server, and it's just a case of how
to get at it. I have quite a few tables in each database so I don't
really want to have to maintain a timestamp on each update, and then go
around all of
If every record in the listing table will have a corresponding record in the category
table you may just include the category clause in with the rest.
WHERE
(a.ArrangerLname like '%$Criteria%' or
p.PublisherName like '%$Criteria%' or
c.ComposerLname like '%$Criteria%' or
Thanks. I'm using InnoDB tables (for transactions) and there's no sign
of any .MYD files for them.
I'm starting to think that maybe this information isn't available :(
Anyone any other ideas?
On Fri, 2004-02-06 at 14:17, Dan Greene wrote:
I'm not 100% sure on this, but what about the .myd file
Arun,
You are back to the same issue. This has nothing to do with MyODBC. The
parameters you are passing are not the correct data types. If no username
is passed, the user 'ODBC' is supplied. If no host is passed 'localhost' is
supplied. The parameter you are passing for the password is being
You can try the 'show table status' from mysql. There is an update_time that lists
the last modified date for the table.
I also found out that these types of commands work with perl DBD::mysql. You can
treat the command like a normal sql statement and the results are returned like any
Don't know if it can be done in the database without lots of legwork.
You can just use the filesystem to do it though.
ls -la within the database directories.
It'd probably be a lot easier to use perl or php file functions, then you'd
be able to do all your calculations in epoch.
P
-Phil
From: Adam Staunton [mailto:[EMAIL PROTECTED]
Hi all,
I just tried to change a field with a type of LongText to
Text and with a width of 10 characters in phpmyadmin. It
accepted the change of the field type top text but didn't
seem to accept the change in field length to 10. To test ,
Sorry. Obviously didn't see this...
-Phil [EMAIL PROTECTED] wrote: -
To: Dan Greene [EMAIL PROTECTED]
From: Phil [EMAIL PROTECTED]
Date: 02/06/2004 09:36AM
cc: gerald_clark [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: RE: How to determine when a MySQL database was last modified?
OH MY GOD IT WORKS!!! I got 32 rows in set (0.08 sec).
That is fantastic! Thank you so much
Now, when I do an explain on this query, I get the following:
+---++---+-+
| table | type | possible_keys | key |
Hello everybody!
I need to create some simple database. I've started to build it up in Microsoft
Access. MS Access was really great for me, it was very easy and user friendly,
intuitive. Without any manual I've managed to create a table, to create some views and
queries and to prepare reports
Nice try... but 'show table status' just displays NULL for Update_time -
maybe because they're InnoDB tables. Besides, I didn't really want to
have to squirrel around all the tables to see if the DB itself has been
changed.
Since what I want to do doesn't seem possible I'll carry on as usual...
Yes , you are correct. There is no GUI with mySQL. You can down load one off
the web. look on www.mysql.com. I think they have a free one there. I use
SQLyog, but you must pay for that.
I would advise the move to mySQL from access. I did it a month ago and have
never looked back.
There is a
If offline tools works for you try './mysqlshow.exe
-vi db_name'. This provides useful information such
as 'Create Time' 'Update Time' and 'Check Time'.
Gowtham.
--- Phil [EMAIL PROTECTED] wrote:
Nice try... but 'show table status' just displays
NULL for Update_time -
maybe because they're
On 6 Feb 2004, at 14:38, Erich Beyrent wrote:
This seems really efficient, since the only large number of rows to
search against is the main listings table, if I read this right. Is
there any further optimization that I can do, or this as good as it
gets? Believe me, I am NOT complaining!!!
Yes,
From: Dusan Spisak [mailto:[EMAIL PROTECTED]
Here is my question:
Is it possible to use MySQL in the similar way to Access?
Without any servers and administration and clients and
controlcenter...? I don't need any network at all, I just
would like to create and run some small database for
mysqlshow gives the same results as SHOW TABLE STATUS, which,
unfortunately, doesn't seem to give created/updated dates for InnoDB tables.
Michael
Gowtham Jayaram wrote:
If offline tools works for you try './mysqlshow.exe
-vi db_name'. This provides useful information such
as 'Create Time'
You could try the following:
1) Perform normal backup.
2) Run sql command flush status. --Resets most status
variables to zero.
3) Next week prior to backup, run sql commands:
show status like 'Handler_delete'
show status like 'Handler_update'
show status like
Hi,
What is the easiest way to update data in a database field from withing VB.
Do I have to build an sql statement that can then be executed on a
connection or is their some way to get the update method to work as in
access?
This is probably a dimbo question but until my books arrive im
Hi List,
Is it possible to GRANT an USER to just UPDATE one specific FIELD instead
of the entire TABLE?
Is it also possible to create a GROUP with all GRANTs and then create the
USERs linked with a specific GROUP?
Thanks.
--
Andre Matos
[EMAIL PROTECTED]
--
MySQL General Mailing List
Thanks Gowtham and Ed. However, even this solution seems a bit dodgy
when it comes to backing up... I'll stick with backing up all databases
for now, and put in an enhancement request. Thanks,
Phil
On Fri, 2004-02-06 at 16:35, [EMAIL PROTECTED] wrote:
You could try the following:
1) Perform
I am using Mandrake 9.1 and MySQL 4.0.11 from the LM CD's.
In my application, I have a table that stores the sequential numbers of
invoices prepared by several stores. Every time a salesman prepares an
invoice for a customer, the system goes to this table, locks it with lock
tables table
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I still have the problem with being unable to logon to a database with a
password. When the password is entered - either on the command line (-p???)
or on the following line (-p without a password), mysql returns a Bad
handshake error message.
I'm
On Fri, 6 Feb 2004, Michael Satterwhite wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I still have the problem with being unable to logon to a database with a
password. When the password is entered - either on the command line (-p???)
or on the following line (-p without a
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Friday 06 February 2004 11:45, Harrison Fisk wrote:
The issue is that 4.1.0 used a different authentication handshake with a
different form of password encoding. Due to the fact that 4.1.0 was
alpha, it was decided not to support the
Hello Dusan:
MS Access is actually a user front end and not a database engine at all.
Many people don't understand this. A front end talks to a database
engine, known as a data source in the lingo. MS Access defaults to
using one of Microsoft's databases, I believe it is called jet or
On Fri, Feb 06, 2004 at 09:17:32AM -0500, Dan Greene wrote:
I'm not 100% sure on this, but what about the .myd file timestamp?
Well, it depends on which table type, obvously. There are several
files for each database, see what the mtime is on each of them, to
determine what's a live file.
If
I've been challenged to write a matching query in a project and do not
know how to handle a part of it. The criteria are as follows:
SELECT * from pages WHERE
changelog.agent = pages.agent AND
changelog.company = pages.company AND
changelog.magazine = pages.magazine
Now for the challenging
From: Ed Curtis [mailto:[EMAIL PROTECTED]
I've been challenged to write a matching query in a project
and do not know how to handle a part of it. The criteria are
as follows:
SELECT * from pages WHERE
changelog.agent = pages.agent AND
changelog.company = pages.company AND
First of all I admit that I am not an expert of MySQL. However, during the
last three months I have TEST installed MySQL Server software and gone
through the tutorial. My next step is to setup three computers as follows:
|--| |--| |--|
|
Yes, I think the most straight forward way is to simply put in a series of
grouped OR statements. See below.
SELECT * from pages WHERE
changelog.agent = pages.agent AND
changelog.company = pages.company AND
changelog.magazine = pages.magazine AND
(
changelog.orig_id = pages.mls_1 OR
Thanks, that seemed the sensible way to me as well. I just didn't know
for sure if you could do that in a MySQL query for sure.
Thanks,
Ed
On Fri, 6 Feb 2004, John McCaskey wrote:
Yes, I think the most straight forward way is to simply put in a series of
grouped OR statements. See below.
Just went through this.
You need to install MySQL from source.
The basic commands you must execute to install a MySQL source
distribution are:
shell groupadd mysql
shell useradd -g mysql mysql
shell gunzip mysql-VERSION.tar.gz | tar -xvf -
shell cd mysql-VERSION
shell ./configure
I am trying to run the below query, and believe that the 'IN' is not supported in
mysql. What is the 'IN' replacement? I tried exists and that doesn't work.
select * from table1
where item_id IN (select item_id from table2)
Thanks,
Scott
--
MySQL General Mailing List
For list
I have a sql file that I would like to convert to an ER diagram - any
suggestions on a tool (free if possible) that would help me do this?
Thanks for your suggestions.
IN is supported in 4.1, I'm not sure about 4.0. But any, you can do you
query like this:
select table1.* from table1
left join table2 on table1.item_id=table2.item_id
where table2.item_id is not null
I think that's right. It may actually be quicker than using IN.
On Feb 6, 2004, at 2:20 PM,
IN is supported in mysql, but subqueries do not work before 4.1.x. You
could rewrite your query with a join, though. Something like:
SELECT table1.* FROM table1, table2
WHERE table1.item_id = table2.item_id
See http://www.mysql.com/doc/en/Rewriting_subqueries.html for more.
Michael
Scott
From: Scott Purcell [mailto:[EMAIL PROTECTED]
I am trying to run the below query, and believe that the 'IN'
is not supported in mysql. What is the 'IN' replacement? I
tried exists and that doesn't work.
select * from table1
where item_id IN (select item_id from table2)
IN has been
On 06-Feb-2004 Phil wrote:
Thanks. But I would have thought that such information would have
been
kept automatically somewhere by the server, and it's just a case of
how
to get at it. I have quite a few tables in each database so I don't
really want to have to maintain a timestamp on each
Datanamic is not free but very affordable and a nice utility. Windows
only though...
Original Message
On 2/6/04, 1:22:55 PM, David Perron [EMAIL PROTECTED] wrote regarding
Erwin from sql script:
I have a sql file that I would like to convert to an ER diagram - any
suggestions on a tool
Hello all-
Is the myisampack utility supposed to be included in the 4.1.1 binaries for
Windows? It's not there - I couldn't find any explicit reference in the doc
about whether this is deliberate or maybe it was just missed?
Thanks,
Rick
I'm trying to select all the fields from a set but when the number is
large, it is returned in exponential notation. Is there a way to keep
the returned number an integer?
Example:
create simple table:
create table test1(
var1 int,
var2 set(1,2,3,4,5,6,7,8,9,10,
Doesn't seem to change the mtime on table files. It appears that for
InnoDB tables these files are only updated when the definition of a
table is changed. The content of the all InnoDB tables is kept in one or
two massive files directly under the 'data' directory!
On Fri, 2004-02-06 at 18:13,
Why are you adding 0? Try this:
SELECT var1, var2 FROM test1;
Michael
Greg Vines wrote:
I'm trying to select all the fields from a set but when the number is
large, it is returned in exponential notation. Is there a way to keep
the returned number an integer?
Example:
create simple table:
This will work, but there's no reason to use a LEFT JOIN here. With a
LEFT JOIN, you get a row for each item_id in table1 that does not have a
corresponding item_id in table2, with the table2 fields set to NULL.
You then have to filter these out with your WHERE clause. Just use a
simple
My thinking on using a left join was that there was not a one to one
relationship between the tables, otherwise the data would be in one
table. So a regular join would produce duplicate records if there was a
one to many relationship between table1 and table2. A left join would
assure a
With sets if you select the set, you get a comma delimited list, but if
you add zero, you get the number value of the entire set (which is what
I want). The response is not always in exponential notation - just when
a high order bit is set.
This seems to be a problem with the output
If you are using 4.0.2 and above you can use cast.
mysql select var1,cast(var2 as unsigned) from test1;
+--++
| var1 | cast(var2 as unsigned) |
+--++
|1 |562949953421312 |
|1 | 1 |
|1 |
Hi everybody : I'm experiencing a trouble with user permissions and
wildcards in the host field ( % ). I have several MySQL databases and we
connect to them through myodbc to Centura team developer apps . Since
the beginning we create all the users with a wildcard in the host field
because
Thanks Bernard - that fixed it!
On Fri, 2004-02-06 at 13:41, Bernard Clement wrote:
If you are using 4.0.2 and above you can use cast.
mysql select var1,cast(var2 as unsigned) from test1;
+--++
| var1 | cast(var2 as unsigned) |
Hi
Using MySQL 4x
I have two tables
Lists and members
Now for every list that a member is a member of there is list ID in the
members table so to query a members lists I would do the following
SELECT members.EmailAddr, members.ListID
FROM members INNER JOIN lists ON
Hi,
Using MySQL 4x
I have two tables
Lists and members
Now for every list that a member is a member of there is list ID in the
members table so to query a members lists I would do the following
SELECT members.EmailAddr, members.ListID
FROM members INNER JOIN lists ON members.ListID =
Or use an outer join with where members.list_id is null, as was
mentioned on the list earlier today.
--Walt
-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED]
Sent: Friday, February 06, 2004 3:18 PM
To: [EMAIL PROTECTED]
Subject: Re: A challenge I think
Hi,
In MySql is it possible to repeat through a month, for example I would like
to
Select count(user) from table where date = x
However, I want x to be a range from the first of the month to the last day
in the month, I of course would need to pass in a month and year value to
limit it to that
Good Afternoon and thank you in advance for any help that you may be able to provide.
QUESTION 1 : How do you reclaiming ibdata space?
We have 3 databases that together use several ibdata files (ie each database does not
have its own ibdata file) . We would like to drop 2 databases and
Dear MySQL'ers,
I have backed up my db using :
% mysqldump -p -u tfiedler --opt BigData ./db_backup.sql
When db_backup.sql is scp'd (secure copy) to another machine running
mysqld, then I ssh to that machine and execute :
$ mysql -u tfiedler BigData db_backup.sql
ERROR 1044: Access denied for
Hi, I am trying to create a script that automatically logs in to mysql and
chooses a db then runs a query.
I can login fine when doing it this way:
mysql -u user -p
it then asks for a pass and it works.
but if I try this:
mysql -u user -ppassword dbname
I get an access error.
any
On Fri, 6 Feb 2004, Tristan Fiedler wrote:
Dear MySQL'ers,
I have backed up my db using :
% mysqldump -p -u tfiedler --opt BigData ./db_backup.sql
When db_backup.sql is scp'd (secure copy) to another machine running
mysqld, then I ssh to that machine and execute :
$ mysql -u tfiedler
On Fri, Feb 06, 2004 at 05:06:08PM -0600, Chuck Barnett wrote:
Hi, I am trying to create a script that automatically logs in to mysql and
chooses a db then runs a query.
I can login fine when doing it this way:
mysql -u user -p
it then asks for a pass and it works.
but if I try this:
In MySql is it possible to repeat through a month, for example I would like
to
Select count(user) from table where date = x
However, I want x to be a range from the first of the month to the last day
in the month, I of course would need to pass in a month and year value to
limit it to that
Iom trying to figure out to use mysqld and set this varible so that when I
execute sql scripts, the case is considered.
Running this version of mysql in Windows XP Pro.
mysql select version();
++
| version() |
++
|
At 20:04 -0500 2/6/04, David Perron wrote:
Iom trying to figure out to use mysqld and set this varible so that when I
execute sql scripts, the case is considered.
Running this version of mysql in Windows XP Pro.
mysql select version();
++
| version() |
Hi,
I'm running MySQL 4.0.17 with RH Linux 8 on Xeon 3.0/1GB RAM.
One application has to access the database (1 connection to the DB is open
on startup and left open). However this application performs a lot of
queries on the DB.
Main InnoDB table : 50.000 Rows
Other InnoDB tables (about 8) :
In the last episode (Feb 07), Geoffrey said:
I'm running MySQL 4.0.17 with RH Linux 8 on Xeon 3.0/1GB RAM.
One application has to access the database (1 connection to the DB is
open on startup and left open). However this application performs a
lot of queries on the DB.
Thanks to top, I
At 05:38 PM 2/6/2004, you wrote:
In MySql is it possible to repeat through a month, for example I would like
to
Select count(user) from table where date = x
However, I want x to be a range from the first of the month to the last day
in the month, I of course would need to pass in a month and year
Geoffrey wrote:
Hi,
I'm running MySQL 4.0.17 with RH Linux 8 on Xeon 3.0/1GB RAM.
One application has to access the database (1 connection to the DB is open
on startup and left open). However this application performs a lot of
queries on the DB.
Main InnoDB table : 50.000 Rows
Other InnoDB
Brent Baisley wrote:
My thinking on using a left join was that there was not a one to one
relationship between the tables, otherwise the data would be in one
table. So a regular join would produce duplicate records if there was a
one to many relationship between table1 and table2. A left join
Alvaro Avello wrote:
Hi everybody : I'm experiencing a trouble with user permissions and
wildcards in the host field ( % ). I have several MySQL databases and we
connect to them through myodbc to Centura team developer apps . Since
the beginning we create all the users with a wildcard in the
hi,
I have a problem, executing queries through MySQL-4.0.15, but the same thing
goes well when run with MySQL-3.23.49,
for ODBC connectivity i'm using libmyodbc3-3.51, so is there some
compaitibility problem as the application crashes for the reason
0x4054afde in my_SQLPrepare ()
90 matches
Mail list logo
