UNIX timestamp with microseconds
is possible to get UNIX timestamp with microseconds
when i try the following it just give 10 digits
mysqlSELECT unix_timestamp('20051114095641'+ INTERVAL 0 HOUR) as ts;
output :
1131933401 --- 10 digits
any tips?
tia!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Complex select across multiple tables
Some of these tables are rather large, I would put the schema in there, but I think it just adds to the mess. Tables `users` field id is auto inc pk `orders` field id is auto inc pk `products` field id is auto inc pk `order_items` field order_id is the key for hooking up order_items to orders, also, in `order_items` I store order_id which comes from the users table. I also have product_id which is how the product is related to an order item. Some of the products can be marked as instock or out of stock, with products.ship_status = 1 or 0 What I need, is a select, that grabs all order records, joined on the order_items for that order, where the products.ship_status is = 1. If ANY of the products do not have a ship_status of 1, it should not be include in the results. I also need to further limit this based on a date field in the orders table, which can be a date in the future, or NULL, it is called delayed_ship_date. So the limit is WHERE delayed_ship_date is within 13 days of NOW() OR where delayed_ship_date is null. Finally, only where the order status is either 'pre-order' or 'delayed' To better explain, we have orders in a system, each order can have many order items. However, each of the order items may or may not be in stock. I only want to see orders that can be fulfilled. I further want to only see orders where the delayed_ship_date is within 13 days of today, or where it is NULL, in which case, it means the user wants the stuff as soon as possible. I know it is a little strange, we are dealing with perishables, so random shipping dates do not always apply. I seem to be able to get this to sort of work, but I end up getting back thousands of records where I want just one for the order, not one for each and ever order item. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
timestamp
is it possible to get the the given (unix)timestamp in milliseconds since the epoch? tia! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timestamp
Hi Ryan, On Wed, 2005-11-16 at 19:16 +0800, Ryan Escarez wrote: is it possible to get the the given (unix)timestamp in milliseconds since the epoch? No, it is not possible. However this is on the roadmap. This is also reported as Bug #8523: http://bugs.mysql.com/bug.php?id=8523 Regards, Petr -- Petr Chardin, Software Developer MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
from bad into worse
Hi listers, [EMAIL PROTECTED]:~ rpm -qa |grep -i mysql MySQL-server-4.1.14-0 MySQL-shared-4.1.14-0 MySQL-client-4.1.14-0 [EMAIL PROTECTED]:~ [EMAIL PROTECTED]:~ uname -a Linux myhost 2.6.5-7.108-default #1 Wed Aug 25 13:34:40 UTC 2004 i686 i686 i386 GNU/Linux [EMAIL PROTECTED]:~ on 12.feb.2005 i reported a problem with the privilege system of mysql. The workaround i had found at that time was the command flush privileges; -- this morning, with the newest version of mysql before version 5, i again did changes in the privilege domain of mysql, using commands of the type revoke... grant... for which, according to the doc, no terminating flush privileges; should be necessary. -- But, alas, clients complained that they got the following error when trying to connect to their database: MySQL Error Number: 1130: Host 'myhost.mydomain.com' is not allowed to connect to this MySQL server which, from the shell client, looks like: [EMAIL PROTECTED]:~ mysql -h myhost -u myuser -p Enter password: ERROR 1130 (0): #HY000Host 'myhost.mydomain.com' is not allowed to connect to this MySQL server [EMAIL PROTECTED]:~ i then did the flush privileges; and everything again workes fine. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to use sysmbol link to link to a database
Hello. Have a look here: http://dev.mysql.com/doc/refman/5.0/en/symbolic-links.html I am new to Mysql, and we are using Mysql as the backend database in out data server, and I wonder if how I create a symbol link under the Mysql data directory to link to a database under another directory so that we can save disk space. Any help will be highly appreciated. Chris Guo wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Upgrade from MySQL 3.23.x to MySQL 4.1.x [InnoDB question]
Hi all, A little question... Can I upgrade directly a server with MySQL 3.23.x version which has some MyISAM databases (no problem with it) and some InnoDB databases towards MySQL 4.1.x? Or have I to do a dump and restore instead of re-use databases? wdyt? -- Cedric Gavage - [EMAIL PROTECTED] - OpenPGP: 0xED325C64 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't connect to MySQL v5.0.15 on 'Windows XP
I've just installed MySQL v5.0.15 on my 'Windows XP system and most ways I want to connect to it fail. I don't see what I'm doing wrong. I've searched the manual, the web, and the forums without seeing anything that clearly addressed my problem. Help, please! If I navigate through Programs / MySQL / MySQL Server v5.0 / MySQL Command Line Client, I'm prompted for a password and when I enter the password, I am connected and can work fine. If I try to run mysqlshow, I see: C:\Documents and Settings\Chrismysqlshow sql mysqlshow: Access denied for user 'ODBC'@'localhost' (using password: NO) If I run the command interpreter (cmd) and type mysql -uroot -pmypwd, it works now but I swear it didn't 10 minutes ago. Arg. My real problem is that I've used MySQLKeeper with MySQL v4 and have backups created with it that I want to restore into MySQL v5.0 and when I try to connect with MySQLKeeper, I get: CONNECT Result: Connection error Now, it may be that MySQL Keeper is trying to connect via ODBC and that's why it's failing but I didn't think that's how it connected, it's an MySQL-specific tool, why would it use ODBC? And shouldn't mysqlshow work out of the box? How could I have installed MySQL to screw up mysqlshow? Thanks for any pointers. Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Innodb Crash on 2 concurrent select
I have MySQL with about 12 billion rows when i try to create 2 process, each select count(*) on the same table after a long time about 30 minutes it crashed :( ANy information? Log file : 051116 20:27:22InnoDB: Assertion failure in thread 2366216768 in file srv0srv.c line 1873 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=12578816 max_used_connections=31 max_connections=910 threads_connected=11 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1782208 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=(nil) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x8d0992ec, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8115587 0x4004a618 (nil) 0x420de407 New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trac e. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 051116 20:27:23 mysqld restarted InnoDB: ## InnoDB: WARNING! InnoDB: The log sequence number in ibdata files is higher InnoDB: than the log sequence number in the ib_logfiles! Are you sure InnoDB: you are using the right ib_logfiles to start up the database? InnoDB: Log sequence number in ib_logfiles is 33 1628260918, log InnoDB: sequence numbers stamped to ibdata file headers are between InnoDB: 0 0 and 192 460914688. InnoDB: ## 051116 20:27:24 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 051116 20:27:24 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 33 1628260918. InnoDB: Doing recovery: scanned up to log sequence number 33 1628260918 051116 20:27:24 InnoDB: Flushing modified pages from the buffer pool... 051116 20:27:24 InnoDB: Started; log sequence number 33 1628260918 /usr/sbin/mysqld-max: ready for connections. Version: '4.1.9-Max' socket: '/var/lib/mysql/mysql.sock' port: 3306 Official MySQL RPM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database IDs
Hello.
Really, in my opinion, it contradicts with the manual. Please, send me
the bug id in case you'll report the bug.
Björn Persson wrote:
Gleb Paharenko:
Could you provide a repeatable test case for you problem?
I don't know how repeatable this is. As I said I have one computer where it=
=20
seems to work sometimes, as if there's a race condition. Right now it seems=
=20
repeatable on the computer I'm using at the moment:
mysql select version();
+---+
| version() |
+---+
| 4.1.14|
+---+
1 row in set (0.00 sec)
mysql create table parent (
- ID int unsigned not null auto_increment,
- value varchar(50),
- primary key (ID)
- );
Query OK, 0 rows affected (0.01 sec)
mysql create table child (
- ID int unsigned not null auto_increment,
- parent_ID int unsigned not null,
- value varchar(50),
- primary key (ID)
- );
Query OK, 0 rows affected (0.00 sec)
mysql insert into parent (value) values ('a');
Query OK, 1 row affected (0.00 sec)
mysql insert into child (parent_ID, value) values
- (last_insert_ID(), 'b1'), (last_insert_ID(), 'b2'),
- (last_insert_ID(), 'b3');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql insert into parent (value) values ('c');
Query OK, 1 row affected (0.00 sec)
mysql insert into child (parent_ID, value) values
- (last_insert_ID(), 'd1'), (last_insert_ID(), 'd2'),
- (last_insert_ID(), 'd3');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql select * from child;
++---+---+
| ID | parent_ID | value |
++---+---+
| 1 | 1 | b1|
| 2 | 1 | b2|
| 3 | 2 | b3|
| 4 | 2 | d1|
| 5 | 4 | d2|
| 6 | 5 | d3|
++---+---+
6 rows in set (0.00 sec)
Note how child rows 1 and 4 have the IDs of their respective parent row in =
the=20
parent_ID field, but child rows 3, 5 and 6 have the ID of the preceding chi=
ld=20
row instead. (For row 2 we can't tell the difference.)
Here's the SQL code for easy copying:
use test;
create table parent (
ID int unsigned not null auto_increment,
value varchar(50),
primary key (ID)
);
create table child (
ID int unsigned not null auto_increment,
parent_ID int unsigned not null,
value varchar(50),
primary key (ID)
);
insert into parent (value) values ('a');
insert into child (parent_ID, value) values=20
(last_insert_ID(), 'b1'), (last_insert_ID(), 'b2'),
(last_insert_ID(), 'b3');
insert into parent (value) values ('c');
insert into child (parent_ID, value) values=20
(last_insert_ID(), 'd1'), (last_insert_ID(), 'd2'),
(last_insert_ID(), 'd3');
select * from child;
Bj=F6rn Persson
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [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: How to reload my.cnf?
Hello. Why are you afraid of shutting down the server for a while? Is it so critical? If yes, send more detailed description of your situation. Replication shouldn't break. From: http://dev.mysql.com/doc/refman/5.0/en/replication-features.html It is safe to shut down a master server and restart it later. Tedy Aulia wrote: Hi Gleb, Thanks for your reply. I am using MySQL 3.23.33. The ones that I will need to add in my.cnf are the syncronisation parameters as follow: binlog-do-db=XYZ replicate-do-db=XYZ replicate-ignore-table=XYZ.table1 replicate-ignore-table=XYZ.table2 replicate-ignore-table=XYZ.table3 Database XYZ was in synch for the past 12 months, I have found database is not in synch anymore as the parameters I mentioned above are missing in my.cnf. I want to put back those parameters but I can't afford to restart the server as MySQL server is also used by other database which is currently in synch and runs happily. Cheers, TA Hello. You can change some variables without restarting the server. See: http://dev.mysql.com/doc/refman/5.0/en/dynamic-system-variables.html Tedy Aulia wrote: Hi All, I will need to change my.cnf in master machine, but I can't afford to restart MySQL server as the server has been used for heavy traffic databases. Can anyone tell me how to do it? Cheers, *Tedy Aulia* -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: UNIX timestamp with microseconds
Hello.
According to the manual Unix timestamp (seconds since '1970-01-01
00:00:00', so, in my opinion UNIX_TIMESTAMP is not designed for
obtaining microseconds. Have a look here, if you haven't done this
yet:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
Ryan Escarez wrote:
is possible to get UNIX timestamp with microseconds
when i try the following it just give 10 digits
mysqlSELECT unix_timestamp('20051114095641'+ INTERVAL 0 HOUR) as ts;
output :
1131933401 --- 10 digits
any tips?
tia!
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [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: Upgrade from MySQL 3.23.x to MySQL 4.1.x [InnoDB question]
Hello. MySQL AB recommends a gradual upgrade. See: http://dev.mysql.com/doc/refman/4.1/en/upgrade.html http://dev.mysql.com/doc/refman/5.0/en/upgrade.html Cedric Gavage wrote: Hi all, A little question... Can I upgrade directly a server with MySQL 3.23.x version which has some MyISAM databases (no problem with it) and some InnoDB databases towards MySQL 4.1.x? Or have I to do a dump and restore instead of re-use databases? wdyt? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: Complex select across multiple tables
Scott Haneda [EMAIL PROTECTED] wrote on 11/16/2005 05:44:05 AM:
Some of these tables are rather large, I would put the schema in there,
but
I think it just adds to the mess.
Tables
`users` field id is auto inc pk
`orders` field id is auto inc pk
`products` field id is auto inc pk
`order_items` field order_id is the key for hooking up order_items to
orders, also, in `order_items` I store order_id which comes from the
users
table. I also have product_id which is how the product is related to an
order item.
Some of the products can be marked as instock or out of stock, with
products.ship_status = 1 or 0
What I need, is a select, that grabs all order records, joined on the
order_items for that order, where the products.ship_status is = 1. If
ANY
of the products do not have a ship_status of 1, it should not be include
in
the results.
I also need to further limit this based on a date field in the orders
table,
which can be a date in the future, or NULL, it is called
delayed_ship_date.
So the limit is WHERE delayed_ship_date is within 13 days of NOW() OR
where
delayed_ship_date is null.
Finally, only where the order status is either 'pre-order' or 'delayed'
To better explain, we have orders in a system, each order can have many
order items. However, each of the order items may or may not be in
stock.
I only want to see orders that can be fulfilled. I further want to only
see
orders where the delayed_ship_date is within 13 days of today, or where
it
is NULL, in which case, it means the user wants the stuff as soon as
possible.
I know it is a little strange, we are dealing with perishables, so
random
shipping dates do not always apply. I seem to be able to get this to
sort
of work, but I end up getting back thousands of records where I want
just
one for the order, not one for each and ever order item.
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
## First, let's identify which products have a ship_status=1
CREATE TEMPORARY TABLE tmpProducts (KEY(id)) SELECT id
FROM products p
WHERE ship_status=1;
## Now let's make a list of all orders that meet your date criteria and
shipping status
CREATE TEMPORARY TABLE tmpOrders (KEY(ID)) (
SELECT id
FROM orders
WHERE delayed_ship_date = NOW() + INTERVAL 13 DAY
AND status IN ('pre-order','delayed')
) UNION (
SELECT id
FROM orders
WHERE delayed_ship_date is NULL
AND status IN ('pre-order','delayed')
);
## Let's combine those tables with order_items to single out only those
orders that
## not only meet your order status and date criteria but also have
products
## whose ship_status=1.
CREATE TEMPORARY TABLE tmpBackorders (KEY(ID)) SELECT DISTINCT ord.id
FROM tmpOrders ord
INNER JOIN order_items oi
on oi.order_id = ord.id
INNER JOIN tmpProducts tp
on tp.id = oi.product_id;
## now you have a list of all of the backorders that should be shipped
ASAP or within 13 days
## You can use that list to get all of the information for each order back
from the
## `orders` table
SELECT o.*
FROM orders o
inner join tmpBackorders b
on b.id = o.id
order by o.delayed_ship_date;
## Last thing: the database is not your mommy. Clean up before you leave
the session
DROP TEMPORARY TABLE tmpBackorders, tmpOrders, tmpProducts;
Make sense? I split it up this way for speed. To do this all in one
complex statement _IS_ possible (I know I can write a single statement to
return the same results) but you didn't sound like you had an hour to wait
on that query to finish. The extra time it would have taken to process the
single-statement version has to do with how many JOINs were going to be
used and how much of your existing data you would wind up throwing out
anyway. By pre-limiting ourselves to selected subsets of your data, we
drastically minimize the number of comparisons necessary to compute
tmpBackorders thus reducing how long it takes to give you the desired
information from the orders table.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: MySQL Innodb Crash on 2 concurrent select
Dear All It happens after these condition InnoDB: ## Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 1103972416 has waited at ../include/btr0btr.ic line 28 for 369.00 seconds the semaphore: S-lock on RW-latch at 0x88cdd6b8 created in file buf0buf.c line 469 a writer (thread id 1105434432) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file buf0flu.c line 562 Last time write locked in file buf0buf.c line 1674 InnoDB: ## Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 Ady Wicaksono wrote: I have MySQL with about 12 billion rows when i try to create 2 process, each select count(*) on the same table after a long time about 30 minutes it crashed :( ANy information? Log file : 051116 20:27:22InnoDB: Assertion failure in thread 2366216768 in file srv0srv.c line 1873 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=12578816 max_used_connections=31 max_connections=910 threads_connected=11 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1782208 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=(nil) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x8d0992ec, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8115587 0x4004a618 (nil) 0x420de407 New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trac e. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 051116 20:27:23 mysqld restarted InnoDB: ## InnoDB: WARNING! InnoDB: The log sequence number in ibdata files is higher InnoDB: than the log sequence number in the ib_logfiles! Are you sure InnoDB: you are using the right ib_logfiles to start up the database? InnoDB: Log sequence number in ib_logfiles is 33 1628260918, log InnoDB: sequence numbers stamped to ibdata file headers are between InnoDB: 0 0 and 192 460914688. InnoDB: ## 051116 20:27:24 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 051116 20:27:24 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 33 1628260918. InnoDB: Doing recovery: scanned up to log sequence number 33 1628260918 051116 20:27:24 InnoDB: Flushing modified pages from the buffer pool... 051116 20:27:24 InnoDB: Started; log sequence number 33 1628260918 /usr/sbin/mysqld-max: ready for connections. Version: '4.1.9-Max' socket: '/var/lib/mysql/mysql.sock' port: 3306 Official MySQL RPM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Remove all non-alpha characters?
Gleb, Thanks; using REPLACE(), as I understand it, would require me to list ALL non-alpha characters, and assuming just ASCII characters, approx (127 - 52) nested calls to REPLACE()... select REPLACE( ...REPLACE( REPLACE( REPLACE( text,'~',''), '!',''), '@','') ...) -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] ... You should think about using REPLACE function. See: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html Fan, Wellington wrote: Hello List-people , I am looking to remove all non-alphanumeric characters from a column: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld doesn't starts with Fatal Error
Hello list, I compiled I special mysql-5-1-2 with the following configuration: ./configure --with-ndbcluster --prefix=/mycomputer/mysql-5.1.2-binary --with-partition --with-row-based-replication --without-innodb --without-berkeley-db --enable-thread-safe-client --enable-shared Afterwards I configured a cluster and it works fine. But starting mysqld: /usr/local/mysql/libexec/mysqld --user=root --log-bin=udo-bin fails 051116 16:27:54 [Note] Starting Cluster Binlog 051116 16:27:54 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist Any suggestions, ideas how to solve that ? Jörg Nowak __ Verschicken Sie romantische, coole und witzige Bilder per SMS! Jetzt bei WEB.DE FreeMail: http://f.web.de/?mc=021193 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Recall: What does MIN_ROWS table option do?
David Lombardi would like to recall the message, What does MIN_ROWS table option do?.
Re: Grant confusion
Michael, Thanks for the detailed response. First off, it's a network connection, but I forgot to show the -h in my example. Sorry for the lack of clarity there. The error message that I'm getting is: ERROR 1045: Access denied for user 'myuser'@'host.mydomain.com' (using password: YES) So then, I go and check the grants using SHOW GRANT There is no such grant defined for user 'myuser' on host '%.mydomain.com' OK, so there are no grants so that's why access is denied. Then I try to re-enter the grant: grant all on my_db.* to 'myuser'@'%.mydomain.com' identified by 'foobar'; Query OK, 0 rows affected (0.00 sec) same password, user, and domain as before (just in case they're not the same in the examples I've given). If I run SHOW GRANTS again I get the same error as above?!? What am I doing wrong? I issuing these commands as root. Thanks again, Tripp --- Michael Stassen [EMAIL PROTECTED] wrote: Tripp Bishop wrote: Simple question: I'd like to create a user that has all privileges for just one database on the server. When I try the following: grant all privileges on my_db.* to 'myuser'@'%.mydomain.com' identified by 'foobar'; the statement runs fine. You can verify it worked with SHOW GRANTS FOR 'myuser'@'%.mydomain.com'; If you need to follow up, show us that output. If I think try to connect to the server through the mysql client like this: mysql -u myuser -pfoobar my_db No -h, so this is [EMAIL PROTECTED] That's fine, but could be important. Mysql users are [EMAIL PROTECTED], with hoost being primary. When you try to connect, mysql searches the user table for the *best* match of [EMAIL PROTECTED] Best match means most specific, with host taking precedence over user. When trying to connect as [EMAIL PROTECTED], then, here are some possible users which would match: [EMAIL PROTECTED] ''@localhost [EMAIL PROTECTED] ''@thismachine.mydomain.com myuser@'%.mydomain.com' ''@'%.mydomain.com' myuser@'%' ''@'%' (A blank username, '', is the anonymous user.) I've arranged those in descending order of specificity, so the first one which exists will be the one used. One possibility, then, is that you have another [EMAIL PROTECTED] definition which is taking precedence over the one you defined. I get access denied... What is the exact error message? So then if I do this: grant all privileges on *.* to 'myuser'@'%.mydomain.com' identified by 'foobar'; I can connect no problem. What am I doing wrong? I definitely don't want this user to have privileges on other databases but I also want the user to be functional. Adding privileges to other dbs shouldn't fix the problem. Another possibility, then, is a typo in the first definition. Now that you can get in, try SELECT CURRENT_USER(); to verify your actual, rather than intended, identity. Thanks, Tripp See the manual for all the details http://dev.mysql.com/doc/refman/5.0/en/privilege-system.html Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Start your day with Yahoo! - Make it your home page! http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem of VB with MySQL
i am sending the files. all details are in the source code what you have to do is to create a database name test2 and have to set the password of the server. the code will automatically create a table name test2 with two double fields and it will reture the error while adding the record through recordset and after adding a text field it will work and another error is that it doesn't updates the record which filters with where condition. please take a stress to see the code and pls solve me the problem as early as possible. From Vishal Panchamia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem of VB with MySQL
vishal wrote: i am sending the files. all details are in the source code what you have to do is to create a database name test2 and have to set the password of the server. the code will automatically create a table name test2 with two double fields and it will reture the error while adding the record through recordset and after adding a text field it will work and another error is that it doesn't updates the record which filters with where condition. please take a stress to see the code and pls solve me the problem as early as possible. From Vishal Panchamia The code didn't come through. If you could, post it on a website. How are you connecting? What does your connecting string look like? If you are using a DSN, what are parameters? I moved away from a pure ADO way of doing things to directly passing the SQL statements using the ADODB.Connection.Execute parameter (you will need to sanitize the inputs from users, as not to cause SQL injection attacks.) -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database IDs
Logan, David (SST - Adelaide): It looks like the LAST_INSERT_ID() is returning the id of the last auto_increment INSERT, this seems to be in line with the documentation. quote The ID that was generated is maintained in the server on a per-connection basis. This means that the value which the function returns to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. /quote It all depends on what [the] most recent statement means – or the last INSERT or UPDATE query in the part Gleb quoted. When I do this: insert into child (parent_ID, value) values (last_insert_ID(), 'b1'), (last_insert_ID(), 'b2'), (last_insert_ID(), 'b3'); is that one statement, or three? I think all of it, from insert to the semicolon, is a single statement. Do you mean that (last_insert_ID(), 'b2') is a statement on its own? Björn Persson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database IDs
Morning Bjorn, I would have throught the insert would translate as three separate inserts to the internals of MySQL. As this is really a convenient shorthand to allow us to bunch up a lot of inserts. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Björn Persson [mailto:[EMAIL PROTECTED] Sent: Thursday, 17 November 2005 4:42 AM To: mysql@lists.mysql.com Subject: RE: Database IDs Logan, David (SST - Adelaide): It looks like the LAST_INSERT_ID() is returning the id of the last auto_increment INSERT, this seems to be in line with the documentation. quote The ID that was generated is maintained in the server on a per-connection basis. This means that the value which the function returns to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. /quote It all depends on what [the] most recent statement means - or the last INSERT or UPDATE query in the part Gleb quoted. When I do this: insert into child (parent_ID, value) values (last_insert_ID(), 'b1'), (last_insert_ID(), 'b2'), (last_insert_ID(), 'b3'); is that one statement, or three? I think all of it, from insert to the semicolon, is a single statement. Do you mean that (last_insert_ID(), 'b2') is a statement on its own? Björn Persson -- 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 Administrator crash on Mac OS X
Hi, I just downloaded MySQL Administrator 1.1.0 for Mac OS X. When I run it, I can see the connection dialogue, when I click to connect, it crashes. MySQL Query Browser, also latest version, just installed, too, works perfectly with the same (and only) connection. Crash report from OS X is attached. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Does the movement of the trees make the wind blow? http://newsboard.unclassified.de - Unclassified NewsBoard Forum Date/Time: 2005-11-16 21:38:10.722 +0100 OS Version: 10.4.3 (Build 8F46) Report Version: 3 Command: MySQL Administrator Path:/Applications/MySQL Administrator.app/Contents/MacOS/MySQL Administrator Parent: WindowServer [67] Version: ??? (1.1.0) PID:1028 Thread: 0 Exception: EXC_BAD_ACCESS (0x0001) Codes: KERN_PROTECTION_FAILURE (0x0002) at 0x Thread 0 Crashed: 0 libSystem.B.dylib 0x90003008 strlen + 8 1 libSystem.B.dylib 0x9001fac0 sscanf + 88 2 com.mysql.MySQLToolsCommon 0x100354e0 myx_get_mysql_version + 96 3 com.mysql.MySQLToolsCommon 0x100355b4 myx_get_mysql_major_version + 24 4 com.mysql.MySQLToolsCommon 0x10001e50 -[MConnectionPanel(Private) connectionFinished:] + 216 5 com.apple.Foundation0x928e8f00 __NSFireMainThreadPerform + 276 6 com.apple.CoreFoundation0x9078df20 __CFRunLoopPerformPerform + 104 7 com.apple.CoreFoundation0x9075da5c __CFRunLoopDoSources0 + 384 8 com.apple.CoreFoundation0x9075cf8c __CFRunLoopRun + 452 9 com.apple.CoreFoundation0x9075ca0c CFRunLoopRunSpecific + 268 10 com.apple.HIToolbox 0x931831e0 RunCurrentEventLoopInMode + 264 11 com.apple.HIToolbox 0x931827ec ReceiveNextEventCommon + 244 12 com.apple.HIToolbox 0x931826e0 BlockUntilNextEventMatchingListInMode + 96 13 com.apple.AppKit0x93680904 _DPSNextEvent + 384 14 com.apple.AppKit0x936805c8 -[NSApplication nextEventMatchingMask:untilDate:inMode:dequeue:] + 116 15 com.apple.AppKit0x937213d8 -[NSButtonCell performClick:] + 668 16 com.apple.AppKit0x93720b38 -[NSButton performKeyEquivalent:] + 424 17 com.apple.AppKit0x9372093c -[NSView performKeyEquivalent:] + 140 18 com.apple.AppKit0x9372089c -[NSWindow performKeyEquivalent:] + 32 19 com.apple.AppKit0x9375f4b0 -[NSTextField textDidEndEditing:] + 684 20 com.apple.Foundation0x928db018 _nsnote_callback + 180 21 com.apple.CoreFoundation0x907844b8 __CFXNotificationPost + 368 22 com.apple.CoreFoundation0x9077c594 _CFXNotificationPostNotification + 684 23 com.apple.Foundation0x928c5420 -[NSNotificationCenter postNotificationName:object:userInfo:] + 92 24 com.apple.AppKit0x937b0cb4 -[NSTextView(NSPrivate) _giveUpFirstResponder:] + 512 25 com.apple.AppKit0x937b0aa0 -[NSTextView(NSKeyBindingCommands) insertNewline:] + 512 26 com.apple.AppKit0x937b0500 -[NSTextView doCommandBySelector:] + 212 27 com.apple.AppKit0x93763b60 -[NSKeyBindingManager(NSKeyBindingManager_MultiClients) interpretEventAsCommand:forClient:] + 1700 28 com.apple.AppKit0x93763440 -[NSKeyBindingManager(NSKeyBindingManager_MultiClients) interpretKeyEvents:forClient:] + 200 29 com.apple.AppKit0x9376163c -[NSView interpretKeyEvents:] + 64 30 com.apple.AppKit0x93761490 -[NSTextView keyDown:] + 756 31 com.apple.AppKit0x936dcd70 -[NSWindow sendEvent:] + 6424 32 com.apple.AppKit0x936856f4 -[NSApplication sendEvent:] + 4172 33 com.apple.AppKit0x9367cb30 -[NSApplication run] + 508 34 com.apple.AppKit0x9376d618 NSApplicationMain + 452 35 com.mysql.Administrator 0x5a24 _start + 392 (crt.c:267) 36 com.mysql.Administrator 0x5898 start + 48 Thread 1: 0 libSystem.B.dylib 0x90049748 syscall_thread_switch + 8 1 com.apple.Foundation0x928f3ad0 +[NSThread sleepUntilDate:] + 152 2 com.apple.AppKit0x9371d7e4 -[NSUIHeartBeat _heartBeatThread:] + 1100 3 com.apple.Foundation0x928db6d4 forkThreadForFunction + 108 4 libSystem.B.dylib 0x9002b200 _pthread_body + 96 Thread 2: 0 libSystem.B.dylib 0x9002b8a8 semaphore_wait_signal_trap + 8 1 libSystem.B.dylib 0x9003001c pthread_cond_wait + 488 2 com.apple.Foundation0x928debe8 -[NSConditionLock lock] + 52 3 com.apple.Foundation0x928e8d44 -[NSObject(NSMainThreadPerformAdditions) performSelectorOnMainThread:withObject:waitUntilDone:modes:] + 716 4 com.apple.Foundation0x928e8a58 -[NSObject(NSMainThreadPerformAdditions) performSelectorOnMainThread:withObject:waitUntilDone:] + 120 5 com.mysql.MySQLToolsCommon 0x10002040 -[MConnectionPanel(Private) connectThread:] + 144 6 com.apple.Foundation0x928db6d4 forkThreadForFunction + 108 7 libSystem.B.dylib 0x9002b200 _pthread_body + 96 Thread 0 crashed
Re: Can't connect to MySQL v5.0.15 on 'Windows XP
Hello. If I try to run mysqlshow, I see: C:\Documents and Settings\Chrismysqlshow sql mysqlshow: Access denied for user 'ODBC'@'localhost' (using password: NO) ODBC is default username that is used on Windows. Create a new user account and use it to connect to the MySQL server: http://dev.mysql.com/doc/mysql/en/Adding_users.html If mysql command line client works, it is rather MySQL Keeper program. Check if you have specified the correct connection parameters for it. [EMAIL PROTECTED] wrote: I've just installed MySQL v5.0.15 on my 'Windows XP system and most ways I want to connect to it fail. I don't see what I'm doing wrong. I've searched the manual, the web, and the forums without seeing anything that clearly addressed my problem. Help, please! If I navigate through Programs / MySQL / MySQL Server v5.0 / MySQL Command Line Client, I'm prompted for a password and when I enter the password, I am connected and can work fine. If I try to run mysqlshow, I see: C:\Documents and Settings\Chrismysqlshow sql mysqlshow: Access denied for user 'ODBC'@'localhost' (using password: NO) If I run the command interpreter (cmd) and type mysql -uroot -pmypwd, it works now but I swear it didn't 10 minutes ago. Arg. My real problem is that I've used MySQLKeeper with MySQL v4 and have backups created with it that I want to restore into MySQL v5.0 and when I try to connect with MySQLKeeper, I get: CONNECT Result: Connection error Now, it may be that MySQL Keeper is trying to connect via ODBC and that's why it's failing but I didn't think that's how it connected, it's an MySQL-specific tool, why would it use ODBC? And shouldn't mysqlshow work out of the box? How could I have installed MySQL to screw up mysqlshow? Thanks for any pointers. Chris -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
MySQL server has gone away
First let me say that I'm a newbie and that I've seen a few instances of this error in the list, but I haven't seen my case. I'm running WAMP5, v. 1.4.4. I'm trying to restore an Eventum v. 1.4 backup done with mysqldump in a LAMP machine. The restoration process invariably stops at line 517. I've split the 4.19GB backup file into 16MB portions for easy view and looked at the first file portion in WordPad. There's nothing esoteric in the neighborhood of line 517. I've tried to do this restoration through the following command line: shell mysql -uusername -ppassword eventum sourcefile.sql. The process also bombs out if I try to use MySQL Administrator. Any suggestions? Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp 1417 N Magnolia Avenue Ocala, FL 34470 Ph. 352-671-0678 Fax 352-671-0778 Int. x11678 For change requests, enhancements, or design changes, please email Business Systems at [EMAIL PROTECTED] For all support issues, please contact Application Support at [EMAIL PROTECTED] or call 352-671-0262 (internal ext. 11262). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database IDs
Hi Again Bjorn, Further to my thoughts below, I had a look at the source (5.0.13 - sql/sql_insert.cc) and the last_insert_id is set for each iteration of the INSERT loop. There would be three inserts for the three values that you proposed INSERTing and the last_insert_id seems to be set each time giving you the result that you saw. By my reading, each time you do an INSERT on an auto_increment field, you will set the last_insert_id to that value. Regards Morning Bjorn, I would have throught the insert would translate as three separate inserts to the internals of MySQL. As this is really a convenient shorthand to allow us to bunch up a lot of inserts. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Björn Persson [mailto:[EMAIL PROTECTED] Sent: Thursday, 17 November 2005 4:42 AM To: mysql@lists.mysql.com Subject: RE: Database IDs Logan, David (SST - Adelaide): It looks like the LAST_INSERT_ID() is returning the id of the last auto_increment INSERT, this seems to be in line with the documentation. quote The ID that was generated is maintained in the server on a per-connection basis. This means that the value which the function returns to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. /quote It all depends on what [the] most recent statement means - or the last INSERT or UPDATE query in the part Gleb quoted. When I do this: insert into child (parent_ID, value) values (last_insert_ID(), 'b1'), (last_insert_ID(), 'b2'), (last_insert_ID(), 'b3'); is that one statement, or three? I think all of it, from insert to the semicolon, is a single statement. Do you mean that (last_insert_ID(), 'b2') is a statement on its own? Björn Persson -- 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: MySQL server has gone away
Kraer, Joseph [EMAIL PROTECTED] wrote on 11/16/2005 04:17:17 PM: First let me say that I'm a newbie and that I've seen a few instances of this error in the list, but I haven't seen my case. I'm running WAMP5, v. 1.4.4. I'm trying to restore an Eventum v. 1.4 backup done with mysqldump in a LAMP machine. The restoration process invariably stops at line 517. I've split the 4.19GB backup file into 16MB portions for easy view and looked at the first file portion in WordPad. There's nothing esoteric in the neighborhood of line 517. I've tried to do this restoration through the following command line: shell mysql -uusername -ppassword eventum sourcefile.sql. The process also bombs out if I try to use MySQL Administrator. Any suggestions? Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp 1417 N Magnolia Avenue Ocala, FL 34470 Ph. 352-671-0678 Fax 352-671-0778 Int. x11678 For change requests, enhancements, or design changes, please email Business Systems at [EMAIL PROTECTED] For all support issues, please contact Application Support at [EMAIL PROTECTED] or call 352-671-0262 (internal ext. 11262). I saw that error frequently until I discovered what the max_allowed_packet setting was for. It sounds as though you have an extended insert command at that location of your file and that the length of the command exceeds the max_allowed_packet setting. SHOW VARIALBES LIKE 'max%'; Reset it to meet or exceed the largest INSERT command in your dump files. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
How to search the manual
Hi folks, You might be amazed getting answers that link right to the MySQL documentation. The documentation is fabulous, but of course we cannot memorize it! One tip I picked up in a MySQL course (which was worth the $$ of the course itself), which is IMMENSELY useful, is the following: If you go to http://www.mysql.com/FOO, where FOO is any word or phrase, you will either get directed to a page dealing with it, or mysql.com will search through the manual for it. Try it yourself -- http://www.mysql.com/select goes to the documentation page for select; similarly, http://www.mysql.com/set option works -- spaces, dashes, underscores, etc are all just handled. http://www.mysql.com/replication goes to the chapter on replication. Go to http://www.mysql.com/archive engine and you get the search results of mysql.com. It is very handy, and eliminates all of my how do I do this? kinds of questions. -Sheeri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A few questions about triggers in MySQL 5
Hi, Does the current latest MySQL 5.0 support: 1. column based triggers 2. trigger restriction 3. multiple triggers of same type of the table 4. triggers reference another table And any information on in which release they will be supported? Thanks, Jenny
Backward-compatible authentication in v5.0?
I'm trying to upgrade MySQL from v4.x to v5.0 under an existing Tcl-based application that uses MySQLTcl and I'm getting: mysqlconnect/db server: Client does not support authentication protocol requested by server; consider upgrading MySQL client Is there something I can do to MySQL v5 server to make it compatible with older clients? Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backward-compatible authentication in v5.0?
Hi! It is a common error, have a look here: http://dev.mysql.com/doc/refman/5.0/en/old-client.html I have had the same error. ++ | ISC Edwin Cruz García | | IT Manager | | Transportes Medel Rogero SA de CV | | Desk: 01 (449) 910 30 90 x3054| | Movil: 01 (449) 111 29 03 | | email: [EMAIL PROTECTED] | ++ -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 16 de Noviembre de 2005 07:40 p.m. Para: mysql@lists.mysql.com Asunto: Backward-compatible authentication in v5.0? I'm trying to upgrade MySQL from v4.x to v5.0 under an existing Tcl-based application that uses MySQLTcl and I'm getting: mysqlconnect/db server: Client does not support authentication protocol requested by server; consider upgrading MySQL client Is there something I can do to MySQL v5 server to make it compatible with older clients? 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]
Re: RE: Backward-compatible authentication in v5.0?
Hi! It is a common error, have a look here: http://dev.mysql.com/doc/refman/5.0/en/old-client.html I have had the same error. Tbanks. I found that -- eventually -- in the manual. It took several searches to find it but it seems to work now, not only for my Tcl application but for MySQL Keeper, too! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timestamp
Ryan,
Try to use this date functions.
* DATE_ADD(date,INTERVAL expr type) , DATE_SUB(date,INTERVAL expr type)
These functions perform date arithmetic. date is a DATETIME or DATE value
specifying the starting date. expr is an expression specifying the interval
value to be added or subtracted from the starting date. expr is a string;
it may start with a '-' for negative intervals. type is a keyword
indicating how the expression should be interpreted.
The INTERVAL keyword and the type specifier are not case sensitive.
The following table shows how the type and expr arguments are related:
type Value Expected expr Format MICROSECOND MICROSECONDS SECOND SECONDS
MINUTE MINUTES HOUR HOURS DAY DAYS WEEK WEEKS MONTH MONTHS QUARTER QUARTERS
YEAR YEARS SECOND_MICROSECOND 'SECONDS.MICROSECONDS' MINUTE_MICROSECOND
'MINUTES.MICROSECONDS' MINUTE_SECOND 'MINUTES:SECONDS' HOUR_MICROSECOND
'HOURS.MICROSECONDS' HOUR_SECOND 'HOURS:MINUTES:SECONDS' HOUR_MINUTE
'HOURS:MINUTES' DAY_MICROSECOND 'DAYS.MICROSECONDS' DAY_SECOND 'DAYS
HOURS:MINUTES:SECONDS' DAY_MINUTE 'DAYS HOURS:MINUTES' DAY_HOUR 'DAYS
HOURS' YEAR_MONTH 'YEARS-MONTHS'
The type values DAY_MICROSECOND, HOUR_MICROSECOND, MINUTE_MICROSECOND,
SECOND_MICROSECOND, and MICROSECOND are allowed as of MySQL 4.1.1. The
values QUARTER and WEEK are allowed as of MySQL 5.0.0.
MySQL allows any punctuation delimiter in the expr format. Those shown
in the table are the suggested delimiters. If the date argument is a DATE
value and your calculations involve only YEAR, MONTH, and DAY parts (that
is, no time parts), the result is a DATE value. Otherwise, the result is a
DATETIME value.
As of MySQL 3.23, INTERVAL expr type is allowed on either side of the +
operator if the expression on the other side is a date or datetime value.
For the - operator, INTERVAL expr type is allowed only on the right side,
because it makes no sense to subtract a date or datetime value from an
interval. (See examples below.)
mysql SELECT '1997-12-31 23:59:59' + INTERVAL 1 SECOND;
- '1998-01-01 00:00:00'
mysql SELECT INTERVAL 1 DAY + '1997-12-31';
- '1998-01-01'
mysql SELECT '1998-01-01' - INTERVAL 1 SECOND;
- '1997-12-31 23:59:59'
mysql SELECT DATE_ADD('1997-12-31 23:59:59',
- INTERVAL 1 SECOND);
- '1998-01-01 00:00:00'
mysql SELECT DATE_ADD('1997-12-31 23:59:59',
- INTERVAL 1 DAY);
- '1998-01-01 23:59:59'
mysql SELECT DATE_ADD('1997-12-31 23:59:59',
- INTERVAL '1:1' MINUTE_SECOND);
- '1998-01-01 00:01:00'
mysql SELECT DATE_SUB('1998-01-01 00:00:00',
- INTERVAL '1 1:1:1' DAY_SECOND);
- '1997-12-30 22:58:59'
mysql SELECT DATE_ADD('1998-01-01 00:00:00',
- INTERVAL '-1 10' DAY_HOUR);
- '1997-12-30 14:00:00'
mysql SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
- '1997-12-02'
mysql SELECT DATE_ADD('1992-12-31 23:59:59.02',
-INTERVAL '1.99' SECOND_MICROSECOND);
- '1993-01-01 00:00:01.01'
If you specify an interval value that is too short (does not include
all the interval parts that would be expected from the type keyword), MySQL
assumes that you have left out the leftmost parts of the interval value.
For example, if you specify a type of DAY_SECOND, the value of expr is
expected to have days, hours, minutes, and seconds parts. If you specify a
value like '1:10', MySQL assumes that the days and hours parts are missing
and the value represents minutes and seconds. In other words, '1:10'
DAY_SECOND is interpreted in such a way that it is equivalent to '1:10'
MINUTE_SECOND. This is analogous to the way that MySQL interprets TIME
values as representing elapsed time rather than as time of day.
If you add to or subtract from a date value something that contains a
time part, the result is automatically converted to a datetime value:
mysql SELECT DATE_ADD('1999-01-01', INTERVAL 1 DAY);
- '1999-01-02'
mysql SELECT DATE_ADD('1999-01-01', INTERVAL 1 HOUR);
- '1999-01-01 01:00:00'
If you use really malformed dates, the result is NULL. If you add
MONTH, YEAR_MONTH, or YEAR and the resulting date has a day that is larger
than the maximum day for the new month, the day is adjusted to the maximum
days in the new month:
mysql SELECT DATE_ADD('1998-01-30', INTERVAL 1 MONTH);
- '1998-02-28'
Thank you,
At 07:47 PM 11/16/2005, Petr Chardin wrote:
http://bugs.mysql.com/bug.php?id=8523
Ehrwin C. Mina
9/F Tower 2
RCBC Plaza
6819 Ayala Avenue cor. Sen. Gil J. Puyat Avenue
Makati City 1200 Philippines
Cell (63 918) 930 4383
Tel/Fax(63 2) 757 2633
Email [EMAIL PROTECTED]
Webwww.chikka.com
This message and any attachment are confidential and may be privileged
Aborting slipped keys
Sometimes I paste in something in mysql CLI and it just does this: WHERE products.ship_status != 1 ); /c \c \c; I tried all the \c commands and nothing will get me out of it, I can ^c and it will get me out, but then I have to login again, which is a pain, how can I tell mysql to drop me back to a clean prompt? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Aborting slipped keys
On Wed, Nov 16, 2005 at 06:33:40PM -0800, Scott Haneda wrote: Sometimes I paste in something in mysql CLI and it just does this: WHERE products.ship_status != 1 ); /c \c \c; I tried all the \c commands and nothing will get me out of it, I can ^c and it will get me out, but then I have to login again, which is a pain, how can I tell mysql to drop me back to a clean prompt? Just close the open quote before you use \c mysql select \c mysql Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Aborting slipped keys
On 11/16/05, Scott Haneda [EMAIL PROTECTED] wrote: Sometimes I paste in something in mysql CLI and it just does this: WHERE products.ship_status != 1 ); /c \c \c; I tried all the \c commands and nothing will get me out of it, I can ^c and it will get me out, but then I have to login again, which is a pain, how can I tell mysql to drop me back to a clean prompt? The prompt is telling you it needs a double quote closed. Try \c at the prompt to close the quotes and cancel the SQL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Aborting slipped keys
At 18:33 -0800 11/16/05, Scott Haneda wrote: Sometimes I paste in something in mysql CLI and it just does this: WHERE products.ship_status != 1 ); /c \c \c; I tried all the \c commands and nothing will get me out of it, I can ^c and it will get me out, but then I have to login again, which is a pain, how can I tell mysql to drop me back to a clean prompt? The prompt tells you that you're in the middle of entering a double-quoted string. Enter the closing double quote and then \c. Similar logic applies to the ', `, and /* prompts. See end of this page: http://dev.mysql.com/doc/refman/5.0/en/entering-queries.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql docs
Hello, with the release of 5, I seem to have all dead links to my old docs for mysql 4. The way they keep changing the urls is a little maddedning :-) Can someone step me through this, how, on the mysql site, do I find out about subselects, and more specifically, what version of mysql they were added to, and what versions do not support them? Google this: subselect site:dev.mysql.com And I get mostly non English stuff, limiting to english and I get a whopping 37 pages, none of which seem to help me much. Docs guidance appreciated :-) -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LIMIT in subquery or GROUP_CONCAT
Hi, (mysql server 5.0.15 running under linux) I suppose this is a classic task. I just hope MySQL 5.0 is able to give the right result. I would like to select top three from different parts in the same table, e.g. for the following data set: id,value 1,10 1,20 1,30 1,40 1,50 2,10 2,20 2,30 2,40 3,10 3,20 4,10 .. I would like the output to be: id,value 1,50 1,40 1,30 2,40 2,30 2,20 3,20 3,10 4,10 That is, the top 3 from each id. The id could be a key in another table instead (just containing 1,2,3,4 as rows). The numbers of different ids might vary thus a static set of UNIONs is no answer. I tried using a subquery using LIMIT inside, but I just got the following result: ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery' It seems like this was a possibility in very early versions of MySQL 4.1. I tried with GROUP_CONCAT() as well, but even though there is an ORDER option, there is no LIMIT option here (feature request? :-) ) Maybe using a variable to keep track of internal count... -- - Peter Brodersen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql docs
Scott Haneda wrote: Google this: subselect site:dev.mysql.com And I get mostly non English stuff, limiting to english and I get a whopping 37 pages, none of which seem to help me much. Try googling for subquery, considering that's what they're called... Jasper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Usage of quick keyword
Hello everybody, I use a table for caching, the table is quite simple there is id field which is a md5 on some attributes of an object, a timestamp field and a text field for storing the data. | id char(32) | timestamp | data (text) | We clean the table every 2 mins for data older that 10 minutes. The usage of the QUICK keyword on the delete statement seems very appropriate to me since the index on the id doesn't need to be merged since the md5 will probably be reuse sometimes in a near future. My concerns is about the second index on the timestamp field that we use to be able to delete quickly the rows that are older than 10 minutes. What would be the impact of the quick keywords on that index ? Should we schedule an optimize / analyze on the table every day / week ? Since the data in that field is always incrementing will it becomes fragmented very fast if the index is not merge ? (For information the table has around 400 megs of data + index ) and every 2 mins we delete 800-900 rows in average. -- Math aka ROunofF == argontechnologies.ca -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.3/173 - Release Date: 2005-11-16 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld crash with archive engine 2gb
I am trying to populate a table using the archive engine that I estimate will take up ~ 8gb of disk space when finished. Right now I am crashing the server as soon as my file gets to the 2gb mark. OS is linux and there are other files on the same filesystem that are 30gb+ so I know the fs has support. Any ideas? I tried to do the ALTER TABLE x AVG_ROW_LENGTH=x MAX_ROWS=y using inputs that would exceed 2GB, same results. TIA, Marc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql docs
Actually, many people use the term subselect where you prefer subquery; I've heard subselect quite a bit in 20+ years of working with relational databases. Not the MySQL people perhaps but it is a reasonable term to use in a search Rhino - Original Message - From: Jasper Bryant-Greene [EMAIL PROTECTED] To: Scott Haneda [EMAIL PROTECTED] Cc: MySql mysql@lists.mysql.com Sent: Wednesday, November 16, 2005 10:06 PM Subject: Re: Mysql docs Scott Haneda wrote: Google this: subselect site:dev.mysql.com And I get mostly non English stuff, limiting to english and I get a whopping 37 pages, none of which seem to help me much. Try googling for subquery, considering that's what they're called... Jasper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.3/173 - Release Date: 16/11/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.3/173 - Release Date: 16/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Joins - where clause, booking system
Hello, I'm running mysql 4.0.12 I have a bookings database in which I'm having problems pulling out the available accomodation. Tables are: Accomodation - accomid (primary key), town, address, contactnumber etc student_accom - studentaccomid (primary key), accomid, studentid My query is as follows so far, this is pulling out all the accomodation that is booked between specified dates - not what I'm after - SELECT distinct accomodation.accom_id FROM accomodation LEFT JOIN student_accom ON accomodation.accom_id = student_accom.accom_id where ((student_accom.start_date Between '2005-10-01' And '2005-11-30') OR (student_accom.leave_date Between '2005-10-01' And '2005-10-30')) Appreciate any assistance. Thanks Gavin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to reload my.cnf?
Hi Gleb, I understand that the replication shouldn't break, but restarting master means losing the updates made to the master database, which we can't afford to do that. TA Hello. Why are you afraid of shutting down the server for a while? Is it so critical? If yes, send more detailed description of your situation. Replication shouldn't break. From: http://dev.mysql.com/doc/refman/5.0/en/replication-features.html It is safe to shut down a master server and restart it later. Tedy Aulia wrote: Hi Gleb, Thanks for your reply. I am using MySQL 3.23.33. The ones that I will need to add in my.cnf are the syncronisation parameters as follow: binlog-do-db=XYZ replicate-do-db=XYZ replicate-ignore-table=XYZ.table1 replicate-ignore-table=XYZ.table2 replicate-ignore-table=XYZ.table3 Database XYZ was in synch for the past 12 months, I have found database is not in synch anymore as the parameters I mentioned above are missing in my.cnf. I want to put back those parameters but I can't afford to restart the server as MySQL server is also used by other database which is currently in synch and runs happily. Cheers, TA Hello. You can change some variables without restarting the server. See: http://dev.mysql.com/doc/refman/5.0/en/dynamic-system-variables.html Tedy Aulia wrote: Hi All, I will need to change my.cnf in master machine, but I can't afford to restart MySQL server as the server has been used for heavy traffic databases. Can anyone tell me how to do it? Cheers, *Tedy Aulia*
Asteriks in password field
Dear All,
When I register a new user in my (php4.1 MySQL 4) application, using:
$insertSQL = INSERT INTO tblusers (uname, usurname, uusername, upassword)
VALUES ('$name', '$surname', '$username', password('$password'));
an asteriks (*) is posted with the encrypted password to the password field
of the table in the DB. The password field then displays it as
*667F407DE7C6AD0 and not as 667F407DE7C6AD0. This only happens when using
Apache server on my local machine but not when using ISS.
This causes validation to fail when using:
$sql = SELECT uusername FROM tblusers WHERE uusername = '$_POST[username]
AND upassword = password('$password')';
but validates if I remove 'AND upassword = password('$password')'.
Thank you for your time.
Re: How to reload my.cnf?
Are you trying to restart the master server, or 'reseting' the master? my master server always shutdown daily, and the slave happily catch up the next day, it's been goin for some 6 months with heavy traffic it's a different story if you want to reset the master, you should capture the master and send it again to the slave (i've done this several times too, and it's painfully slow) changing the binlog-do-db afaik would not trouble the replication, unless it's a new database that hasnt been replicated earlier - leo Tedy Aulia wrote: Hi Gleb, I understand that the replication shouldn't break, but restarting master means losing the updates made to the master database, which we can't afford to do that. TA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
