Mark Sargent wrote:
Hi All,
I can start mysql with the following cmd,
/etc/rc.d/init.d/mysql start
although, I had to change
bindir=./bin
to
bindir=/usr/local/mysql/bin
to get it to work. I can access the DB with,
mysql -h localhost -u root -p
successfully, but, when trying to access via
Hi,
in my table I have one varchar(20) column where I store version number.
Version looks like:
1.1.2
1.2.1
1.10.3
It is possible order this column in natural order (1.2 before 1.10)?
Stano.
--
Stanislav Paka
programtor, www skupina
KIOS s.r.o.
tel: 033 / 794 00 18
--
MySQL General Mailing List
Hi, you can continue playing. It's a true game :o)
Best Regards
Mathias FATENE
Hope that helps
*This not an official mysql support answer
-Original Message-
From: James Black [mailto:[EMAIL PROTECTED]
Sent: vendredi 29 avril 2005 02:20
To:
Hi Stano,
there was a response by Michael Stassen on the list about a year ago on
this problem, which I hope he doesn't mind if I repeat below.
/Johan
Version numbers?
CREATE TABLE ss (version VARCHAR(13));
INSERT INTO ss VALUES ('1'),('1.1'),('1.2.1.2'),('1.10.1'),('1.2'),
Hi,
select a from versions order by substring_index(a,'.',-2);
Best Regards
Mathias FATENE
Hope that helps
*This not an official mysql support answer
-Original Message-
From: Stano Paska [mailto:[EMAIL PROTECTED]
Sent: vendredi 29 avril 2005 08:21
To:
On Fri, 29 Apr 2005 08:20:58 +0200, Stano Paska [EMAIL PROTECTED]
wrote:
Hi,
in my table I have one varchar(20) column where I store version number.
Version looks like:
1.1.2
1.2.1
1.10.3
It is possible order this column in natural order (1.2 before 1.10)?
Stano.
Hello Stano,
What about divide 1
Hi,
Would this be the best way, if I was to run query to
obtain first 2 records of each category from the
database. The database could contain literally 100s
of 1000s of records.
select t1.field1, t2.field2 as CommonFld from table1
t1, table2 t2 where t1.keyfield = t2.keyfield and
t1.field1 =
after gunzipping and untarring i get to point
in instructions to run script mysql_install_db
i get the following error:
# cd /convert
# cd mysql
# scripts/mysql_install_db
exec(): 0509-036 Cannot load program ./bin/my_print_defaults because of
the foll
owing errors:
Symbol resolution failed for
On 4/26/05, Jonathan Mangin [EMAIL PROTECTED] wrote:
I would like to select several rows from one table
and insert them into another nearly identical table
using Perl/DBI:
my @array = $q-param(); # HTML checkboxes
foreach my $element (@array) {
my $sql = select col2, col3, col4 from
Can anyone please suggest a good book for MySQL 5.0 .
Also can anyone suggest how far it will be reliable if I use MySQL 5.0 to
change my database from Oracle to Mysql.
As I have a lot of Stored Procs for migration. It will also be very helpful
if someone can give an approximate date when the
I have just upgraded a mySQL installation on a Soalri 2.8 box for the source.
It compiled and installed without error.
The startup script redirect STDOUT STDERR into /dev/null so this is what I
did ...
/usr/bin/mysqld_safe --datadir=/data/mysql-data/
--pid-file=/data/mysql-data/mysql.pid
Whoops - that wasn't an error code (thanks Prasanna!)
Here is the log file..
050429 11:32:08 mysqld started
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
050429 11:32:09 InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database
I have a TEXT field in my db (4.0) that has lists of IP numbers; can a
full-text search be done for IP numbers?
If so could you show me an example query?
Thanks
Rob
I have a TEXT field in my db (4.0) that has lists of IP numbers; can a
full-text search be done for IP numbers?
If so could you show me an example query?
Thanks
Rob
I don't have any PK in this table, but If you like one, I can add
another column for PK, like,
++---+
| id | seqno |
++---+
| 1 | 00122 |
| 2 | 00123 |
| 3 | 00123 |
| 4 | 00123 |
| 5 | 00336 |
| 6 | 00346 |
| 7 | 00349 |
| 8 |
Hi,
have you read my answer yesterday ?
Mathias
Selon Vivian Wang [EMAIL PROTECTED]:
I don't have any PK in this table, but If you like one, I can add
another column for PK, like,
++---+
| id | seqno |
++---+
| 1 | 00122 |
| 2 | 00123 |
| 3 |
After almost a year of being up, my RH9 server lost power and rebooted.
When it came back up, mysql was not running. I went in and ran
safe_mysql to start it back up (I also tried the init.d script) but it
failed to start. No errors, nothing in the log. After some
investigation, it appears
Hi Sreedhar, all!
madderla sreedhar wrote:
--- Joerg Bruehe [EMAIL PROTECTED] wrote:
Hi!
[[...]]
The manual states clearly:
Stored procedures and functions are a new feature
in MySQL version 5.0.
(At the proper place: 19 Stored Procedures and
Functions)
Sreedhar, if you have a follow-up
I did like this, look good, anyone can try it.
set @a:=0;
set @b:=0;
update test set item=if([EMAIL PROTECTED], @a:[EMAIL PROTECTED], if(@b:=seqno,
@a:=1, 0));
Vivian Wang wrote:
I don't have any PK in this table, but If you like one, I can add
another column for PK, like,
++---+
|
I wish I could upgrade - believe me I would love to but the product we use
is supposedly not compatible with any higher version than 4.0.23 :-(.
Is there any workaround at all - even something that is known to be a bit
performance intensive as our application is not that heavy!!
Thanks a TON
Ron McKeever [EMAIL PROTECTED] wrote on 29/04/2005 14:09:38:
I have a TEXT field in my db (4.0) that has lists of IP numbers; can a
full-text search be done for IP numbers?
Unfortunately not, because Fulltext regards the dots as terminators. The
IP address 192.168.32.2 will therefore be keyed
Hi!
I have setup replication with one master and one slave.
What do I have to think about in order to minimize hassle
and downtime to switch this setup to a new master?
My thought was to setup the new master as a slave, and
then switch to the new master.
Is this a nice way of doing it?
--
Vivian,
Mathias was correct. I will be building on Mathias's answer by using your
PK for your original data table (since you neglected to name it I will
call it source_table) WITH THE ASSUMPTION that you cannot change your
data structure and that you already have a PK on the table:
Create a
Ron,
I have run into the issue you described in the past. Do the following:
touch path_to_mysql/name_of_pid
chown mysql:mysql path_to_mysql/name_of_pid
Hope this helps.
Quoting Ron Gilbert [EMAIL PROTECTED]:
After almost a year of being up, my RH9 server lost power and
rebooted. When it came
Hello.
Check that you don't have problems with filesystem. Start mysqld
directly, specify the corresponding variables in the command line.
If it doesn't produce errors switch to the debug version of the server
and use the trace files to find where the problem is.
Ron Gilbert [EMAIL
Hello.
See:
http://dev.mysql.com/doc/mysql/en/crashing.html
Marten Lehmann [EMAIL PROTECTED] wrote:
MySQL could die during your query. What is in error log?
Oh my god, it's really dieing. I haven't looked in the error log before,
because I though, it's just this connection
If I have a table with fields
ID, NAME, VALUE
and they may be populated like
1, SHAWN, APPLE
2, TOM, BANANA
3, SHAWN, BANANA
4, JACK, GRAPES
5, TOM, APPLE
6, SHAWN, GRAPES
and I want to query all records showing only the most recent records for
each person
Any thoughts?
--
MySQL General
I am new to MySQL. Please excuse my ignorance if this question has been
previously discussed. I was not able to
find an answer to my question by searching the archives.
I have MySQL installed on a dedicated AMD-64 computer with the MS XP Pro
operating system.
MS-SQLServer is installed on
Is it possible to do a date field validation using an sql query.
Its like we have an html field- its a free form text field and the end user
should type in a valid date, of course in a predefined format only
(MM/dd/yyy). I tried using javascript but either it works on some browsers
and not in
How do you determine most recent? Is it based on the highest ID number
for a person or some datetime value you didn't show us?
This is another form of the groupwise maximum problem described here:
http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html
If after reading the
Anoop kumar V [EMAIL PROTECTED] wrote on 04/29/2005 11:00:11 AM:
Is it possible to do a date field validation using an sql query.
Its like we have an html field- its a free form text field and the end
user
should type in a valid date, of course in a predefined format only
(MM/dd/yyy). I
jatwood [EMAIL PROTECTED] wrote on 28/04/2005 22:35:45:
I am new to MySQL. Please excuse my ignorance if this question has been
previously discussed. I was not able to
find an answer to my question by searching the archives.
I have MySQL installed on a dedicated AMD-64 computer
So is there a function in mysql that I can call to validate dates??
Or do I need to build it?
Thanks,
Anoop
On 4/29/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Anoop kumar V [EMAIL PROTECTED] wrote on 04/29/2005 11:00:11 AM:
Is it possible to do a date field validation using an sql
You could try checkdate()...
chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I did two query, can someone explain what happened?
First query:
mysql select * from customer_billing where first_name=shad;
+--+---++-+---+---+-
-+---+--+--+--+-
Hello,
I am seeking to create a table to hold prices for products. I am not sure what
type would be best. According to the docs, I have many choices, dec, double,
float, etc. Can anyone give me an idea, of which is the proper choice for
handling US currency like so: 1500.99
Thanks,
Scott K
You can, sort of. You can use a type of query like this:
select if(now()'2005-04-28', 1, 0);
Which will return a 1 or a 0 if the date is greater than the current
date/time. But that's a very weak comparison, prone to error, since the
date must be in the MySQL readable format. So while you could
[snip]
I did two query, can someone explain what happened?
First query:
mysql select * from customer_billing where first_name=shad;
mysql select first_name, customer_num from customer_billing where
first_name=shad;
Now these query is the same except for the '*'?
[/snip]
Anoop
snip
You could try checkdate()...
/snip
Apologies for erroneous advice - I am evidently suffering from list psychosis...
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
No problem - I followed up and found out that this function is not
available. THought probably you were referring to a later version of Mysql..
anyways...
We use Java - and maybe I could use that - I was just wondering if I could
help reinventing something already there.
Thanks so much for
Hi Anoop,
In order to validate a date I am using next query:
select date_format(date_sub(date_add('yourdate', interval 1 day), interval
1 day),'%Y%m%d') = date_format('yourdate','%Y%m%d');
It will give you 1 if date is valid.
Best regards,
Mikhail.
- Original Message -
From: Anoop
On Fri, Apr 29, 2005 at 10:46:16AM -0500, [EMAIL PROTECTED] [EMAIL PROTECTED]
wrote:
I did two query, can someone explain what happened?
Not with what you included:
| customer_num | title | first_name | middle_name | last_name | email |
dayphone | evenphone | address1 |
Hello,
I have this simple table of unique items.
CREATE TABLE ITEM (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
manufacturer_id varchar(50),
) TYPE=InnoDB;
Foreach item, I can have 1 to many assets. So I am trying to use this:
CREATE TABLE ITEM_ASSET_REL (
id INT,
Woww - this is great - I think this is exactly what I wanted!!
Thanks a Ton Mikhail. :-)
Anoop
On 4/29/05, Mikhail Entaltsev [EMAIL PROTECTED] wrote:
Hi Anoop,
In order to validate a date I am using next query:
select date_format(date_sub(date_add('yourdate', interval 1 day),
Ahh okay the column was cut off or something \G shows it!
Thanks!!
-Original Message-
From: Rich Lafferty [mailto:[EMAIL PROTECTED]
Sent: Friday, April 29, 2005 10:28 AM
To: mysql@lists.mysql.com
Subject: Re: Can someone explain strange query!!
On Fri, Apr 29, 2005 at 10:46:16AM
Re-read the section on foreign keys
(http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html).
It clearly says (in the second and third bullets) that you must index the
columns on both ends of a FK.
Add an index to ITEM_ASSET_REL that contains id as its first term then you
will
Hi,
I think it's because you added a comma after the second line in the create
table item. So this table was not created.
manufacturer_id varchar(50), must be manufacturer_id varchar(50))
When done, i had no problem to create my FK (4.1.11)
Mathias
Selon Scott Purcell [EMAIL PROTECTED]:
It sounds like you either have incorrect indexes or you didn't adjust
the MySQL configuration variables (i.e. key_buffer, join_buffer_size,
read_buffer_size) appropriately for your data set size and what your
query is doing (i.e. full table scan).
SHOW STATUS can help you figure out what needs
Hi,
If you're in a transactionnal env., and have a test env. of mysql with myisam
tables, and want to compare, you will not have what you want.
Myisam tables have only table locking. sql server 2000 and Yukon have row
locking mecanism. So you have to have innodb storage type.
Also, if your
I set up replication and it was working fine but I re-created my master
database (droped and created empty tables) and now my replication
doesn't work. I checked the status of master and slave and they think
they are in sync but when I add a new record to master I don't see it
in slave. It used
running: mysql Ver 12.22 Distrib 4.0.23, for Win95/Win98(i32) downloaded from
www.devside.net/web/servers/free/download.
When I run a script with the following commands I get sql syntax errors:
Create table 'test' ( 'helper' varchar(22) )
ENGINE=MYISAM DEFAULT CHARSET=latin1;
I get a sql
Scott Purcell wrote:
I am seeking to create a table to hold prices for products. I am
not sure what type would be best. According to the docs, I have many
choices, dec, double, float, etc. Can anyone give me an idea, of
which is the proper choice for handling US currency like so: 1500.99
I
Found the problem. For some reason, error messages where being sent to
hohup.out, once I found that I discovered I was using an option in
my.cnf that is not supported on 3.23 (I use 4.1 on my other server).
All is well now. Thanks for the help
Ron
On Apr 29, 2005, at 6:47 AM, Gleb Paharenko
Scott Purcell wrote:
I am in the docs trying to use the IN (13.1.8.3. Subqueries with ANY, IN, and
SOME).
Version: mysql Ver12.21 distrib 4.0.15 Win95/Win98(i32)
Look at http://dev.mysql.com/doc/mysql/en/subqueries.html
Subqueries aren't supported until 4.1.
--
Keith Ivey [EMAIL PROTECTED]
Hello,
I'm using MySQL 5.0.3 Beta, and I'm hoping for some general
tips/experience you guys may have had regarding optomistic vs
pessimistic locking in a web app (PHP 5.0.3)
I'm more of a windows programmer, and I've always implemented
pessimistic over optomistic as it's much more professional
Hi,
Try to have optimistic locking in the database server (row level locking
a.k.a. innodb storage), et let your transactions managed by the server.
Any line of code like lock table will generate a very bad web
application performances.
You can add connection pooling if you want to manage total
Hi,
This is just what you're looking for :
select item.id, item.name
from item,item_cat_rel
where item.id =item_cat_rel.id
and item_cat_rel.cat_id = 5;
+--+--+
| id | name |
+--+--+
| 5000 | Triple Cage Hook |
Your problem is that you are trying to do an 'IN' subquery in MySQL V4.0.x.
Subqueries don't become available in MySQL until V4.1.x. You'll need to
upgrade to V4.1 (or V5.0) to get that functionality.
Rhino
- Original Message -
From: Scott Purcell [EMAIL PROTECTED]
To:
Scott Klarenbach [EMAIL PROTECTED] wrote on 04/29/2005 02:28:25
PM:
Hello,
I'm using MySQL 5.0.3 Beta, and I'm hoping for some general
tips/experience you guys may have had regarding optomistic vs
pessimistic locking in a web app (PHP 5.0.3)
I'm more of a windows programmer, and I've
Does anyone know what the various lock modes mean in the show innodb
status reports?
We are investigating a dead-lock issue, and see lock_mode x and
lock_mode s. We have transaction that locks a record in mode x, and is
appears to be waiting for a lock on the same record in mode s - by the
I'm running MySQL 4.0.14 and it's locking tables for everything including
counting records and select statements. I don't use any lock statements.
Has anyone else encountered this? Any suggestions?
Thanks,
Jeff
__
Do You Yahoo!?
Tired of
Thanks Shawn, that helps a lot. I like your general idea of handling
it at application level. I guess my main concern w/ web apps and
locking the record (even at app level) is orphaned locks, resulting
from browser closes or other events that don't go through the normal
channels of updating or
Is there a way to group a field together with COUNT() and run a SUM() or
AVG() on that? I've looked all over and I can't see a way to combine
them, but maybe there is a trickier way?
Eric Jensen
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
In article [EMAIL PROTECTED],
Hassan Schroeder [EMAIL PROTECTED] writes:
Scott Purcell wrote:
I am seeking to create a table to hold prices for products. I am
not sure what type would be best. According to the docs, I have many
choices, dec, double, float, etc. Can anyone give me an idea, of
I'm using MySQL 4.1.11 with sql-mode=ONLY_FULL_GROUP_BY set in my.cnf.
This disallows things like
SELECT col1, col2, sum(col1)
FROM tbl
GROUP BY col1
as it should, and it allows
SELECT col1, sum(col1)
FROM tbl
GROUP BY col1
but it also disallows
SELECT col1, sum(col1) + 1
FROM
I could set the user's session timeout to be the same duration as the
record lock timeout...that way, in any event where the user's lock
would have expired, he would have to log back into the system
anyway...but this may be inconvenient as well, as I know a lot of
user's could be idle for some
Hi,
Normalizing some tables.
Does it make much difference to MySQL whether I separate minor, related data
into tables for a main account (MemberAccount) such as:
MemberAccount then emails, religion, hobbies, education, etc. I'll explain.
On the other hand, I could put all the information in
Harald Fuchs wrote:
I can't imagine any reason to use a type other than DECIMAL for a
currency value.
A reason could be performance. Storing cent values in an INT field is
more efficient.
Are you saying that storing and/or retrieving a DECIMAL value takes
appreciably more time than an INTEGER?
--
I am having to operating MySQL 4.0.18, I had heared that I could copy a db
folder out of the mysql/data folder to the same folder on a different machine
running its own local host server and that server would have a copy of the
database. HOWEVER, o some occasions this seemed to work Ok, and on
At 15:24 -0700 4/29/05, Hassan Schroeder wrote:
Harald Fuchs wrote:
I can't imagine any reason to use a type other than DECIMAL for a
currency value.
A reason could be performance. Storing cent values in an INT field is
more efficient.
Are you saying that storing and/or retrieving a DECIMAL value
Hi,
MySQL is a very fast, multi-threaded, multi-user and robust SQL
(Structured Query Language) database server. What's New in This Release:
Functionality added or changed: . ONLY_FULL_GROUP_BY no longer is
included in the ANSI composite SQL mode. (Bug #8510) . mysqld_safe will
create the
Here are datatypes and sizes. The problem can be storage.
http://dev.mysql.com/doc/mysql/en/storage-requirements.html
Best Regards
Mathias FATENE
Hope that helps
*This not an official mysql support answer
-Original Message-
From: Hassan Schroeder
http://dev.mysql.com/doc/mysql/en/backup.html
It's better to backup with tools. You will be sure that tables are
FULL-locked.
Best Regards
Mathias FATENE
Hope that helps
*This not an official mysql support answer
-Original Message-
From: [EMAIL PROTECTED]
I'm not entirely sure I know what you're asking when you say that you want
to group a field together with COUNT().
If you mean that you want to be able to count() and sum() or avg() the
groups, then the answer is yes. For example, let's say that you have an
Employee table with one row per
Paul DuBois wrote:
Before MySQL 5, DECIMAL values are stored as strings, so operations
are less efficient than for integers.
In MySQL 5, the representation of DECIMAL has changed to binary format,
so it's more efficient than before. It's probably safe to say that
integer is still a bit more
Yes I had thought I should use tools to copy, but I have had problems with
mysqldump. I successfully created a dump file, but when I execute this file on
an empty database I got:
C:\xampp\xampp\mysql\bingt;mysql -uroot db2 lt; test1.txtERROR 1064 at line
54: You have an error in your SQL
On Fri, 29 Apr 2005 [EMAIL PROTECTED] wrote:
I set up replication and it was working fine but I re-created my master
database (droped and created empty tables) and now my replication
doesn't work. I checked the status of master and slave and they think
they are in sync but when I add a new
Hi,
MySQL docs claim at:
http://dev.mysql.com/doc/mysql/en/ansi-diff-foreign-keys.html
that At a later stage, foreign key constraints will be implemented for
MyISAM tables as well.
Does anyone know what the timescale is?
Thanks,
Jacek
--
MySQL General Mailing List
For list archives:
I backup a db using mysqldump, but when I come to execute the sql file on an
empty db I get this
C:\xampp\xampp\mysql\bingt;mysql -uroot db2 lt; test3.txtERROR 1005 at line
54: Can't create table '.\db2\shift.frm' (errno: 150)
C:\xampp\xampp\mysql\bingt;
I have tried to fix the foreign key
Jigal van Hemert [EMAIL PROTECTED] writes:
Because the SQL standard says so.
A true observation, but still no explanation or reason why ;-P
MySQL doesn't follow the standard in every situation, so that's not an
excuse... (no offense!)
There must be a good reason other than because our ancestors
80 matches
Mail list logo