how to avoid precision problem w/o switching to double?
Hi, I'm having problems with floats while doing comparisons. create table fff (x float); create table ddd (x double); insert into fff (0.1); insert into ddd (0.1); select * from ddd where x = 0.1 correctly returns one row, but select * from fff where x = 0.1 does not return anything. I found on many websites that If you are comparing FLOAT or DOUBLE columns with numbers that have decimals, you can't use '='. This problem is common in most computer languages because floating-point values are not exact values. In most cases, changing the FLOAT to a DOUBLE will fix this. This solution is fine as long as involved data size is relatively small. We are planning to store ~500 millions rows per day, each containing many floats. Switching to double is not a viable option due to space overhead. I wonder if anyone has a better suggestion how to solve it. Thanks, Jacek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Split a table?
If I have a table with 200K records, is there an easy way to split it into two separate tables with 100K records each? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Avoiding complex mysql selects
On Tue, 12 Jul 2005 18:34:38 -0400, wrote: H, how about using full text indexing? It will be a lot easier to implement and a lot quicker. Can you give more details? -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: /var/lib/mysql directory full
Hello. Have you been at: http://dev.mysql.com/doc/mysql/en/symbolic-links.html Rabindra Acharya [EMAIL PROTECTED] wrote: Hi, Operating System: Debian MySQL Version: 4.0.24 /var/lib/mysql directory, where the MySQL database lives is in the root directory (defaultly installed), and it is currently full. I want to move it in other partition, may be /usr, and will create a symlink to /var/lib/mysql directory. Is there any known issue, to which i should take care...? or i can go ahead, that will not create any problem to my database. Your quick response is highly appriciated. Thanks Regards, Rabindra Acharya -- 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: Databases in a different location than the default
Hello. You could find the clues in the error log. See: http://dev.mysql.com/doc/mysql/en/error-log.html Good Afternoon: I have rebuilt by web / database server from bare metal this morning. The computer is running Fedora Core 3, and is fully patched and up to date. The MySQL version is 4.1.12-1, and I have installed the following components - Server - Client - Compat-libs All were installed from the RPMs provided by MySQL.com To start of, I am fairly new to administering database servers, and to using them, so if my question seems a little elementary, please understand and answer accordingly. My question is this. My databases are located on a different physical machine from the one running the database server - (for backup etc reasons) The databases reside in /home/mysql - that's an NFS share, which I know for a fact is valid, as I have other data being used from the share the default my.cnf is listed here: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid When I start the mysql server with this configuration, it appears to start in good order. It is my understanding that in order to have the server utilize my databases that are on the other machine, I need to change the datadir directive in the my.cnf file to read datadir=/home/mysql when I do this however, mysql fails to start - it waits for a period of time and gives me a failed error. Can someone please explain to me what is going wrong, and what I can do to fix it. - I am not interested in having someone fix this for me, I just need some guidance so that I can learn how to fix it myself, so when it happens again, I will know what to do. ~ Tim Holmes [EMAIL PROTECTED] 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]
Re: Searchable/Sortable Encrypted Fields in MySQL?
Hello.
Where are you going to store the key? You could use subqueries for
manipulations with encrypted data. Here is the example, however,
you should turn of binary logging, because insert statements are being
stored with key.
create table pwd(id int auto_increment, pass char(100), primary
key(id));
insert into pwd set pass=aes_encrypt('coolpass1','123');
insert into pwd set pass=aes_encrypt('anothercoolpass2','123');
mysql select pass from (select aes_decrypt(pass,'123') as pass from
pwd) as unencrypted order by pass asc;
+--+
| pass |
+--+
| anothercoolpass2 |
| coolpass1|
+--+
mos [EMAIL PROTECTED] wrote:
At 04:16 PM 7/12/2005, Matt McNeil wrote:
I need to securely store lots of sensitive contact information and
notes in a (MySQL or other freely available) database that will be
stored on a database server which I do not have direct access to.
This database will be accessed by a PHP application that I am
developing. However, I also need to be able to search/sort these data
with the database functions (SELECT, ORDER BY, etc) so simple PASSWORD
style encryption
of specific fields would not work. (For example, I need to encrypt
contacts' names, but need to be able to sort results by name). (I
realize I could load the entire table into memory with PHP and
process/search/sort it there, but
that's obviously not a very good solution). Ideally I would like to
encrypt entire tables. An encrypted file system is not really an
option, because the goal is to prevent loss if the database server is
hacked (in addition, I wouldn't be able to install an encrypted file
system on the database server).
Any suggestions?
Matt,
Transparent encryption is not possible with MySQL. I've pondered
this problem myself and haven't found a solution other than using a
different database.
Mike
--
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: tracing connections to mysql.
Hello. I don't remember any built-in capability of MySQL to provide such information. But it seems as not a difficult task to write a script which will gather it. todd hewett [EMAIL PROTECTED] wrote: Thanks Gleb, That was educational. Is there a way to log connections in such a way that is easy to tell how many connections were happening at one time? Thanks, Todd -Original Message- -- 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: repair table (error 27)
Hello. | eps4.inmail | repair | warning | Can't change size of indexfile, error: 27 As of MySQL 4.0.2, there is a USE_FRM mode for REPAIR TABLE. Use it if the .MYI index file's header is corrupted. See: http://dev.mysql.com/doc/mysql/en/repair-table.html Dirk Vleugels [EMAIL PROTECTED] wrote: Hi, the DB resided on a network appliance filer (happily for a year). A nfs problem corrupted some tables, all got fixed but this one: mysql repair table inmail extended; +-++--+---+ | Table | Op | Msg_type | Msg_text | +-++--+---+ | eps4.inmail | repair | warning | Can't change size of indexfile, error: 27 | | eps4.inmail | repair | error| 27 for record at pos 9037492 | | eps4.inmail | repair | error| 27 when trying to write bufferts | | eps4.inmail | repair | error| 27 when updateing keyfile | | eps4.inmail | repair | status | Operation failed | +-++--+---+ 5 rows in set (11.03 sec) perror 27 says 'file to large', but the files in question are _small_: -rw-rw 1 mysqlother9037816 Jul 12 14:51 inmail.MYD -rw-rw 1 mysqlother3377152 Jul 12 14:50 inmail.MYI -rw-rw 1 mysqlother 9358 Dec 8 2004 inmail.frm I tried myisamchk with different options, but no go. Any clues? Any way to recover the data (for re-import)? Regards, Dirk -- 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: Conditions in selects
On Wed, 13 Jul 2005 00:34:15 -0400, wrote:
Scott Haneda wrote:
I can not get this to work how I would think it should be formatted:
SELECT IF(NOW() BETWEEN sale_start AND sale_end, 'yes', 'no')
That seems to work just fine
It should, as that is correct syntax, as documented in the manual
http://dev.mysql.com/doc/mysql/en/comparison-operators.html.
However, I was under the impression that BETWEEN was to be formatted
BETWEEN(va1, v2)
Where did you get that idea?
Which leads me to:
SELECT IF(NOW() BETWEEN(sale_start AND sale_end), 'yes', 'no')
Which gives me a error of:
You have an error in your SQL syntax. Check the manual that corresponds to
your MySQL server version for the right syntax to use near ' 'yes', 'no')
FROM products WHERE id = 75
This is invalid syntax, hence the error message. Mysql expects BETWEEN
min_value AND max_value. Your parentheses mean that min_value is the result
of (sale_start AND sale_end), which will be 0 (false) if either sale_start or
sale_end is 0, otherwise it will be 1 (true). Then you are missing the 'AND
max_value' part.
Michael
Could we have the corrected sql here pls?
I guess
SELECT IF(NOW()) BETWEEN sale_start AND sale_end,'no','yes')
another example
SELECT IF(STRCMP('test','test1'),'no','yes');
- 'no'
--
zzapper
vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg?
http://www.rayninfo.co.uk/tips/ vim, zsh success tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to avoid precision problem w/o switching to double?
Hello. If the space is so important to you, what do you think about 'LIKE' operator? It seems to work corectly and it is able to use indexes. See: create table fff (x float, key(x)); insert into fff values(0.1); insert into fff values(0.11); select * from fff where x like 0.1; +--+ | x| +--+ | 0.1 | | 0.1 | | 0.1 | +--+ mysql explain select * from fff where x like 0.1 \G; *** 1. row *** id: 1 select_type: SIMPLE table: fff type: index possible_keys: x key: x key_len: 5 ref: NULL rows: 16 Extra: Using where; Using index; Jacek Becla [EMAIL PROTECTED] wrote: Hi, I'm having problems with floats while doing comparisons. create table fff (x float); create table ddd (x double); insert into fff (0.1); insert into ddd (0.1); select * from ddd where x = 0.1 correctly returns one row, but select * from fff where x = 0.1 does not return anything. I found on many websites that If you are comparing FLOAT or DOUBLE columns with numbers that have decimals, you can't use '='. This problem is common in most computer languages because floating-point values are not exact values. In most cases, changing the FLOAT to a DOUBLE will fix this. This solution is fine as long as involved data size is relatively small. We are planning to store ~500 millions rows per day, each containing many floats. Switching to double is not a viable option due to space overhead. I wonder if anyone has a better suggestion how to solve it. Thanks, Jacek -- 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]
Engine Type
-BEGIN PGP SIGNED MESSAGE- Hash: SHA512 I would like to ask what is the best type of engine for this project I will take Payroll and Daily Time Record system - - Payroll system is located in the head office (server). Daily Time Record is located in all 10 branches (clients). The connection from client to server is VPN The Daily Time Record will save the data to the server everytime an employee logs in. The server and client will be developed with Visual Basic 6. Thanks, Michael Louie Loria -BEGIN PGP SIGNATURE- Comment: GPG Public Key: https://www.biglumber.com/x/web?qs=0x4A256EC8 Comment: GPG Public Key: http://www.lorztech.com/GPG.txt Comment: Yahoo ID: michaellouieloria iQEVAwUBQtVSiLXBHi2y3jwfAQo8gwf+OrUCgzirjzpWXtTcAaXREFJTlXVV5mhJ ggN75al7O47V7hIhY+8rUu9HOYAJj/Hp3HGo3Ls5AJ8febV9uXYW9RK424iUOp8p j7DckjisWwVXGqmKlD5dd7ftjNay5C3Otx23+ggEFqA8pwNEp2yZsppxhR6n00Tq 8kwy6wuGlizwIkLgktah1Xcae1d6YvmuvC7kC1dTQypzd2/Kgz7TJKsqCYrvu7EN 0BX1lwIDkolG5ifWJtnRRebM53A5zxSahhItRl6r6MyJMPsg4sa+QeRyzM+qgdRd r2Kn6kem8gvKA5m3uNWVRqFraHf/ciENKsYXhIvYSxrvO1ScOXkdgQ== =hx6L -END PGP SIGNATURE- -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.13/47 - Release Date: 7/12/2005 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]
slow queries
Hi,
I have a query that keeps coming up in my slow queries log. The whole
database is innodb and i'm using mysql 4.1.11 on 64bit intel running red
hat linux. There are less than 100 rows in the offending table at anyone
time, and the server load rarely creeps up above 0.5
If i try to manually insert this row, i cant make it take longer than a
fraction of a second. I do get a few other random queries in the log
that take an age but this one comes up a lot.
Does anyone know if there are any issues with ON DUPLICATE KEY UPDATE,
or using varchar fields as a primary key?
if not how can i investigate this further? Table and query below
Thanks in advance
Tony
CREATE TABLE `tblSessionData` (
`sessionKey` varchar(32) NOT NULL default '',
`data` text NOT NULL,
`expiry` int(11) NOT NULL default '0',
PRIMARY KEY (`sessionKey`),
KEY `expiry` (`expiry`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# Query_time: 26 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
INSERT INTO dbseInfrastructure.tblSessionData
(sessionKey,expiry,data)
VALUES
('1dbeb00777bf1cd20f8e12d044f4fa4c',
1121252767, 'intRightBanner|i:6;formRequiredFields|a:2:{s:8:\username
\;i:0;s:8:\password\;i:0;}formValidation|a:2:{s:8:\username
\;i:0;s:8:\password\;i:0;}')
ON DUPLICATE KEY UPDATE
expiry=1121252767
,data='intRightBanner|
i:6;formRequiredFields|a:2:{s:8:\username\;i:0;s:8:\password
\;i:0;}formValidation|a:2:{s:8:\username\;i:0;s:8:\password\;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mixing conditions with NOT BETWEEN
NOW() BETWEEN sale_start AND sale_end is equivalent to sale_start = NOW() AND sale_end = NOW() NOT(A AND B) is equivalent to (NOT A OR NOT B), so NOW() NOT BETWEEN ... is equivalent to sale_start NOW() OR sale_end NOW(). Can sale_start be greater than NOW() in your data? If not, we can simplify to SELECT prod_name, sale_price, sale_schedule_status FROM products WHERE sale_end NOW() AND sale_schedule_status = 'active'; Can sale_end be = NOW()? Michael Are you sure? ;) Let's see.. NOW() NOT BETWEEN sale_start AND sale_end = (There was a 'NOT' before BETWEEN in orginal post) = NOT ( NOW() sale_start AND NOW() sale_end ) = = NOT (NOW() sale_start) OR NOT ( NOW() sale_end) = = NOW() = sale_start OR NOW() = sale_end -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Conditions in selects
zzapper wrote:
On Wed, 13 Jul 2005 00:34:15 -0400, wrote:
Scott Haneda wrote:
I can not get this to work how I would think it should be formatted:
SELECT IF(NOW() BETWEEN sale_start AND sale_end, 'yes', 'no')
That seems to work just fine
It should, as that is correct syntax, as documented in the manual
http://dev.mysql.com/doc/mysql/en/comparison-operators.html.
However, I was under the impression that BETWEEN was to be formatted
BETWEEN(va1, v2)
Where did you get that idea?
Which leads me to:
SELECT IF(NOW() BETWEEN(sale_start AND sale_end), 'yes', 'no')
Which gives me an error of:
You have an error in your SQL syntax. Check the manual that corresponds to
your MySQL server version for the right syntax to use near ' 'yes', 'no')
FROM products WHERE id = 75
This is invalid syntax, hence the error message. Mysql expects BETWEEN
min_value AND max_value. Your parentheses mean that min_value is the result
of (sale_start AND sale_end), which will be 0 (false) if either sale_start or
sale_end is 0, otherwise it will be 1 (true). Then you are missing the 'AND
max_value' part.
Michael
Could we have the corrected sql here pls?
The correct syntax was already in the 1st paragraph of the original message,
as I pointed out in my reply. The correct syntax is also clearly documented
in the manual page I referenced. Beyond that, what, precisely, do you want?
I guess
SELECT IF(NOW()) BETWEEN sale_start AND sale_end,'no','yes')
^ one too many parentheses.
another example
SELECT IF(STRCMP('test','test1'),'no','yes');
- 'no'
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Null alphabetic order
How do I use ORDER BY in a way that it list null last after Z instead of before A? I.e. instead of: Null, A, B, C result would be: X, Y, Z, null How can I get around to that? -- Power to people, Linux is here.
Re: Mixing conditions with NOT BETWEEN
Евгений Косов wrote: NOW() BETWEEN sale_start AND sale_end is equivalent to sale_start = NOW() AND sale_end = NOW() NOT(A AND B) is equivalent to (NOT A OR NOT B), so NOW() NOT BETWEEN ... is equivalent to sale_start NOW() OR sale_end NOW(). Can sale_start be greater than NOW() in your data? If not, we can simplify to SELECT prod_name, sale_price, sale_schedule_status FROM products WHERE sale_end NOW() AND sale_schedule_status = 'active'; Can sale_end be = NOW()? Michael Are you sure? ;) Yes, I am. Let's see.. NOW() NOT BETWEEN sale_start AND sale_end = (There was a 'NOT' before BETWEEN in orginal post) Yes, I saw that. NOW() NOT BETWEEN... is equivalent to NOT(NOW() BETWEEN..., as documented in the manual http://dev.mysql.com/doc/mysql/en/comparison-operators.html. = NOT ( NOW() sale_start AND NOW() sale_end ) = No. BETWEEN is inclusive of its endpoints. (Same page in the manual). Hence we start with NOT (NOW() = sale_start AND NOW() = sale_end) = NOT (NOW() sale_start) OR NOT ( NOW() sale_end) = NOT (NOW() = sale_start) OR NOT ( NOW() = sale_end) = NOW() = sale_start OR NOW() = sale_end = NOW() sale_start OR NOW() sale_end = sale_start NOW() OR sale_end NOW() Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Null alphabetic order
Scott Hamm wrote: How do I use ORDER BY in a way that it list null last after Z instead of before A? I.e. instead of: Null, A, B, C result would be: X, Y, Z, null How can I get around to that? Something like ORDER BY IF(col IS NULL, 1, 0), col Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Null alphabetic order
-Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: 13 July 2005 13:38 To: Scott Hamm Cc: 'Mysql ' Subject: Re: Null alphabetic order Scott Hamm wrote: How do I use ORDER BY in a way that it list null last after Z instead of before A? I.e. instead of: Null, A, B, C result would be: X, Y, Z, null How can I get around to that? Something like ORDER BY IF(col IS NULL, 1, 0), col Michael Or simply: ORDER BY col IS NULL, col Which will probably be *slightly* faster.. Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.13/47 - Release Date: 12/07/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Avoiding complex mysql selects
Create a full text index that encompasses the fields you want to
search in (synopsis, title, keywords).
Then format your select to take advantage of the full text index:
select * from dbname where match(synopsis, title, keywords) against
('word1 word2 phrase one etc' in boolean mode)
You should read the manual on full text indexing since there are a
lot of options. You can do relevance ranking, adjust the importance
of the words you are searching on, etc. There are also limitations
you need to be aware of, like no relevance when searching in boolean
mode, can't be used on InnoDB tables, etc.
On Jul 13, 2005, at 2:17 AM, zzapper wrote:
On Tue, 12 Jul 2005 18:34:38 -0400, wrote:
H, how about using full text indexing? It will be a lot easier
to implement and a lot quicker.
Can you give more details?
--
zzapper
vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg?
http://www.rayninfo.co.uk/tips/ vim, zsh success tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Timestamp problem.
I have the following part in a schema describe feeds . | pubdate | timestamp| YES | | CURRENT_TIMESTAMP | | show create table feeds; `pubdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, when i make an INSERT INTO feeds(, pubdate) VALUES (,NOW()+1*RAND()) is there any chance to have: mysql select count(*) from feeds where pubdate = 0; +--+ | count(*) | +--+ | 593923 | +--+ 1 row in set (5.90 sec) mysql select count(*) from feeds where pubdate 0; +--+ | count(*) | +--+ | 287532 | +--+ 1 row in set (5.33 sec) -- Sometimes life hits you in the head with a brick. Don't lose faith. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Myisamchk on really large tables.
Folks,
We are going through a nasty problem, and I hope you guys can help me out.
We are running a couple of MySQL 4.0.25 at 2 OpenBSD Opteron (246, 2GB
RAM, 36GB RAID-1 15K), and for backup purposes, 1 Linux CentOS 4.0 Pentium
4 (3GHz, 1GB RAM, 80GB SATA 10K). They are all connected through
replication, and everything works perfectly.
Our database is now close to 20GB, divided in 160 tables. There are only 2
tables that are larger than 1GB, all others are below 300MB.
These two large tables, they have about 30.000.000 rows and 11 keys of
indexing (each). Every now and then, I used to run myisamchk to fix and
optimize this table (myisamchk -r, -S, -a). All of a sudden, this process
has become slower to the point that I have given up. It used to take 3
hours to repair/optimize each table. Now we are running myisamchk for the
last 12 hours and it has completed only 1/3 of one table!
Back when things used to work, after the optimization, the size of the
index file (MYI) was about 30% smaller. And everything was much faster (at
least for one month).
And what scares me the most, is that this table is only growing. There is
no chance of trimming it.
Does anyone know what can I do to optimize this table?
Maybe I am reaching MySQL limits? Or just incorrect hardware for this task?
What happens if we get a nasty table error? No way of repairing it?
I have tried the same process over the OpenBSD's and Linux boxes. They all
show the exact same behaviour. Also, I have tried to create a new index
file (as told on MySQL docs: truncate table, copy back the MYD, then running
myisamchk -rq) and no luck... it is even slower.
This is part of my.cnf:
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 4M
write_buffer = 4M
tmpdir = /var/mysql/temp
Just to make things clear: this tmpdir is another unused HD, connected to
the RAID's 2 channel -- this way we are not losing performance of
reading/writing, at least I think. The MySQL daemon is dead while running
myisamchk, and there is no other service running at the time -- so we can
bite memory some more.
Only at the very beggining we see a CPU hog (peaking at 95%), but after a
few seconds, the CPU use drops to 5%, maybe even less, and we see only HD
activity -- and not that much. Is this right?!
Any ideas?!
Thanks for your time.
Best regards,
RV
PS: Here comes some useful(?) info. If things break, please, ask me for a
text file.
[EMAIL PROTECTED] db]# ls -la offline*
-rw-rw 1 _mysql _mysql 2552151732 Jul 13 09:46 offline.MYD
-rw-rw 1 _mysql _mysql 5300221952 Jul 13 09:46 offline.MYI
-rw-rw 1 _mysql _mysql8940 Apr 19 01:19 offline.frm
[EMAIL PROTECTED] db]# ls -la history*
-rw-rw 1 _mysql _mysql 2372474988 Jul 13 09:47 history.MYD
-rw-rw 1 _mysql _mysql 3943869440 Jul 13 09:47 history.MYI
-rw-rw 1 _mysql _mysql8926 Apr 19 01:26 history.frm
alpha mysql select count(*) from offline;
+--+
| count(*) |
+--+
| 30992996 |
+--+
1 row in set (0.02 sec)
alpha mysql select count(*) from history;
+--+
| count(*) |
+--+
| 29676533 |
+--+
1 row in set (0.01 sec)
alpha mysql describe offline;
+--+---+--+-+-++
| Field| Type | Null | Key | Default | Extra
|
+--+---+--+-+-++
| code | bigint(20)| | PRI | NULL|
auto_increment |
| history1 | int(10) | YES | MUL | NULL|
|
| desc | varchar(24) | YES | MUL | NULL|
|
| sold | int(1)| YES | MUL | NULL|
|
| emptor | int(9)| YES | MUL | NULL|
|
| shipment | int(5)| YES | MUL | NULL|
|
| share| bigint(20)| YES | MUL | NULL|
|
| sequence | bigint(20)| YES | MUL | NULL|
|
| model| enum('P','R') | YES | MUL | NULL|
|
| a4 | int(5)| YES | MUL | NULL|
|
| state| char(2) | YES | MUL | NULL|
|
+--+---+--+-+-++
alpha mysql describe history;
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-++
| code | bigint(20) | | PRI | NULL| auto_increment |
| vendor | int(9) | YES | MUL | NULL||
| prod | int(6) | YES | MUL | NULL||
| desc | varchar(24) | YES | MUL | NULL||
| price| decimal(6,2) | YES | | NULL||
| shipment | int(5) | YES | | NULL||
| share|
Re: Engine Type
Michael Louie Loria [EMAIL PROTECTED] wrote on 07/13/2005 06:42:45 AM: -BEGIN PGP SIGNED MESSAGE- Hash: SHA512 I would like to ask what is the best type of engine for this project I will take Payroll and Daily Time Record system - - Payroll system is located in the head office (server). Daily Time Record is located in all 10 branches (clients). The connection from client to server is VPN The Daily Time Record will save the data to the server everytime an employee logs in. The server and client will be developed with Visual Basic 6. Thanks, Michael Louie Loria You want to use InnoDB. As a minimum, you need the ability to have transactions in any application where accounting is involved. That way you can ENSURE that multiple changes/inserts/deletes are all committed at the same time, maintaining your data integrity. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Avoiding complex mysql selects
On Wed, 13 Jul 2005 09:24:20 -0400, wrote:
Create a full text index that encompasses the fields you want to
search in (synopsis, title, keywords).
Then format your select to take advantage of the full text index:
select * from dbname where match(synopsis, title, keywords) against
('word1 word2 phrase one etc' in boolean mode)
You should read the manual on full text indexing since there are a
lot of options. You can do relevance ranking, adjust the importance
of the words you are searching on, etc. There are also limitations
you need to be aware of, like no relevance when searching in boolean
mode, can't be used on InnoDB tables, etc.
O gosh my learning curve
Thanks
--
zzapper
vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg?
http://www.rayninfo.co.uk/tips/ vim, zsh success tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: mysql 5.07 results incorrect, mysql 5.0.6 results correct
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 If I run the first query on mysql 5.0.6 the results are correct, I get 142 rows, if I run it on 5.0.7 I get 8 rows back. If I remove the nested select, as shown in the second query, then it works fine on 5.0.7. I am trying to understand what is going on. I may try to create a test case to demonstrate this, but I don't understand why the problem is happening in the first place. This is on Solaris 8 and Solaris 9, btw. Thank you for any suggestions. SELECT i.label, i.itemtype, it.name, i.rid, i.status, l.locname, i.lid,(SELECT IF(p.fullname IS NULL, (SELECT CONCAT(sna.fname, ' ', sna.mname, ' ', sna.lname, ' ', sna.gen) FROM nams.names AS sna WHERE sna.badge=sne.badge), p.fullname) AS fullname FROM nams.netids AS sne LEFT OUTER JOIN nams.prefs AS p ON(sne.badge=p.badge) WHERE sne.badge=c.badge) AS UserFullname, i.layoutx, i.layouty, i.theta FROM items AS i INNER JOIN locations AS l ON(i.lid=l.lid) INNER JOIN itemtypes AS it ON(i.itemtype=it.itemtype) LEFT OUTER JOIN curuse AS c ON(c.rid=i.rid) WHERE l.locname='iclab' ORDER BY i.status, i.label; SELECT i.label, i.itemtype, it.name, i.rid, i.status, l.locname, i.lid,(SELECT IF(p.fullname IS NULL, i.layoutx, i.layouty, i.theta FROM items AS i INNER JOIN locations AS l ON(i.lid=l.lid) INNER JOIN itemtypes AS it ON(i.itemtype=it.itemtype) LEFT OUTER JOIN curuse AS c ON(c.rid=i.rid) WHERE l.locname='iclab' ORDER BY i.status, i.label; - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFC1R8dikQgpVn8xrARAjsfAJ4hsAKn81s0/rjP3V9N1MccjUVPIACdHbSV XiUuUvFYubdZgQnrfTGBNpw= =mAcq -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
myisam or innodb (chestnut)
Hi, I created the same database on two different servers (with different versions of mysql). I found one db had been created as innodb and the myisam without me apparently having a say in the matter. I've Googled and found that Innodb has record locking, roll back, but that MYISAM is quicker. Is myisam still dominant for web applications? -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: myisam or innodb (chestnut)
On Wed, 2005-07-13 at 15:02 +0100, zzapper wrote: Is myisam still dominant for web applications? depends on your application, if you're running a bulliten board or a simple catalogue site, then myisam is a good choice as it is faster. However for an ecommerce site i would definately go for innodb as it allows foreign keys and more importantly (imho) transactions. If what you're doing involves dealing with peoples money, its a good idea to be able to roll back if something goes wrong. tomy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: mysql 5.07 results incorrect, mysql 5.0.6 results correct
replies embedded James Black [EMAIL PROTECTED] wrote on 07/13/2005 10:03:09 AM: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 If I run the first query on mysql 5.0.6 the results are correct, I get 142 rows, if I run it on 5.0.7 I get 8 rows back. If I remove the nested select, as shown in the second query, then it works fine on 5.0.7. I am trying to understand what is going on. I may try to create a test case to demonstrate this, but I don't understand why the problem is happening in the first place. This is on Solaris 8 and Solaris 9, btw. Thank you for any suggestions. (Your original query, reformatted to eliminate most of the wrapping) SELECT i.label , i.itemtype , it.name , i.rid , i.status , l.locname , i.lid ,(SELECT IF(p.fullname IS NULL , (SELECT CONCAT(sna.fname, ' ', sna.mname, ' ', sna.lname, ' ', sna.gen) FROM nams.names AS sna WHERE sna.badge=sne.badge) , p.fullname) AS fullname FROM nams.netids AS sne LEFT OUTER JOIN nams.prefs AS p ON(sne.badge=p.badge) WHERE sne.badge=c.badge)AS UserFullname , i.layoutx , i.layouty , i.theta FROM items AS i INNER JOIN locations AS l ON(i.lid=l.lid) INNER JOIN itemtypes AS it ON(i.itemtype=it.itemtype) LEFT OUTER JOIN curuse AS c ON(c.rid=i.rid) WHERE l.locname='iclab' ORDER BY i.status, i.label; (Your second query, also reformatted to minimize wrapping) SELECT i.label , i.itemtype , it.name , i.rid , i.status , l.locname , i.lid ,(SELECT IF(p.fullname IS NULL , i.layoutx , i.layouty , i.theta FROM items AS i INNER JOIN locations AS l ON(i.lid=l.lid) INNER JOIN itemtypes AS it ON(i.itemtype=it.itemtype) LEFT OUTER JOIN curuse AS c ON(c.rid=i.rid) WHERE l.locname='iclab' ORDER BY i.status, i.label; - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] Maybe I missed something but the second query is INCOMPLETE! You are missing a Parenthesis to close the inner select you are using to populate your second query's eighth term and there is no table information (no FROM clause) for your outer select (your main query clause). I can't try to help that one when I don't have all of the information to work with. Your first query I would categorize as excessive abuse of subqueries. I would reformat it like this: SELECT i.label , i.itemtype , it.name , i.rid , i.status , l.locname , i.lid , IF(p.fullname IS NULL , CONCAT(sna.fname, ' ', sna.mname, ' ', sna.lname, ' ', sna.gen) , p.fullname ) AS UserFullname , i.layoutx , i.layouty , i.theta FROM items AS i INNER JOIN locations AS l ON i.lid=l.lid INNER JOIN itemtypes AS it ON i.itemtype=it.itemtype LEFT JOIN curuse AS c ON c.rid=i.rid LEFT JOIN nams.prefs AS p ON p.badge = c.badge LEFT JOIN nams.names AS sna ON sna.badge=c.badge WHERE l.locname='iclab' ORDER BY i.status, i.label; That should provide the same results on both versions (assuming identical data). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: how to avoid precision problem w/o switching to double?
Hi! Gleb Paharenko wrote ((re-ordered)): Jacek Becla [EMAIL PROTECTED] wrote: Hi, I'm having problems with floats while doing comparisons. create table fff (x float); create table ddd (x double); insert into fff (0.1); insert into ddd (0.1); select * from ddd where x = 0.1 correctly returns one row, but select * from fff where x = 0.1 does not return anything. I found on many websites that If you are comparing FLOAT or DOUBLE columns with numbers that have decimals, you can't use '='. This problem is common in most computer languages because floating-point values are not exact values. In most cases, changing the FLOAT to a DOUBLE will fix this. Note the term In most cases: Both FLOAT and DOUBLE are implemented as binary numbers (with different precision), so for both there are many decimal numbers which cannot be represented exactly. Roughly, this is equivalent to the fact that you cannot represent the fraction 1/3 _exactly_ with any finite number of decimal digits, you just have (increasingly good) approximations 0.3, 0.33, 0.333, ... For a detailed treatment, see this paper: http://cch.loria.fr/documentation/IEEE754/ACM/goldberg.pdf This solution is fine as long as involved data size is relatively small. We are planning to store ~500 millions rows per day, each containing many floats. Switching to double is not a viable option due to space overhead. I wonder if anyone has a better suggestion how to solve it. Thanks, Jacek Hello. If the space is so important to you, what do you think about 'LIKE' operator? It seems to work corectly and it is able to use indexes. See: [[...]] select * from fff where x like 0.1; Even though this may (seem to!) work, IMO it is the wrong thing to do: 1) LIKE is a character string operator. If it is applied to numbers, this causes conversions to strings and will be slow. 2) It suffers from the same problems of inexact conversions. The whole situation will get worse when the values are not entered but computed: While in decimal arithmetic you can be sure that 100 * 0.001 == 0.1 this cannot be guaranteed when it is calculated using binary arithmetic, because 0.001 cannot be represented exactly. The correct way to handle such searches is to do something like SELECT FROM tab WHERE col BETWEEN val - epsilon AND val + epsilon with epsilon being some value that is small enough for the precision your application needs, and large enough that the resulting interval covers the conversion errors between decimal and binary numbers. As an alternative, for some applications you could scale your values: For currencies, do not store (calculate) Euro (Dollar, Pound, ...) but Cent (Pennies, ...) and scale by 100 on both data entry and output; for times, do not use hours (like 40 per week or 8 per day) but rather minutes (2400 per week or 480 per day) or even seconds; etc. (Of course, this may fail when you get to values smaller than your stored units - same problem as you originally had.) Regards, Jörg -- Joerg Bruehe, Senior Production Engineer 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]
Re: Databases in a different location than the default
Hi Tim, all! Tim Holmes wrote: [[...]] My databases are located on a different physical machine from the one running the database server - (for backup etc reasons) IMO, this is a no-no: You add complexity and (potential) bottlenecks to your setup. You should store your data local to the machine running the server processes, because several operations (like locking) are only possible on local files / disks. Rather do remote backups than remote routine operation. (This does not imply that backup were no routine activity!) The databases reside in /home/mysql - that's an NFS share, which I know for a fact is valid, as I have other data being used from the share NFS is a good example: Typically, it does not support locking. HTH, Jörg -- Joerg Bruehe, Senior Production Engineer 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]
RE: Databases in a different location than the default
Hello. You could find the clues in the error log. See: http://dev.mysql.com/doc/mysql/en/error-log.html Good Afternoon: I have rebuilt by web / database server from bare metal this morning. The computer is running Fedora Core 3, and is fully patched and up to date. The MySQL version is 4.1.12-1, and I have installed the following components - Server - Client - Compat-libs All were installed from the RPMs provided by MySQL.com To start of, I am fairly new to administering database servers, and to using them, so if my question seems a little elementary, please understand and answer accordingly. My question is this. My databases are located on a different physical machine from the one running the database server - (for backup etc reasons) The databases reside in /home/mysql - that's an NFS share, which I know for a fact is valid, as I have other data being used from the share the default my.cnf is listed here: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid When I start the mysql server with this configuration, it appears to start in good order. It is my understanding that in order to have the server utilize my databases that are on the other machine, I need to change the datadir directive in the my.cnf file to read datadir=/home/mysql when I do this however, mysql fails to start - it waits for a period of time and gives me a failed error. Can someone please explain to me what is going wrong, and what I can do to fix it. - I am not interested in having someone fix this for me, I just need some guidance so that I can learn how to fix it myself, so when it happens again, I will know what to do. [Tim Holmes] Gleb, et. al. As you suggested, I have checked out the log files and this is what I have found: 050713 11:00:09 mysqld started 050713 11:00:09 [Warning] Asked for 196608 thread stack, but got 126976 050713 11:00:09 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use 050713 11:00:09 [ERROR] Do you already have another mysqld server running on port: 3306 ? 050713 11:00:09 [ERROR] Aborting 050713 11:00:09 [Note] /usr/sbin/mysqld: Shutdown complete 050713 11:00:09 mysqld ended This suggests to me a communications problem on either the database server, or the file server where the databases reside. I guess the next question is how do I check to see whats going on here. I tried telnet 192.168.0.5:3306 and got the following [EMAIL PROTECTED] log]# telnet 192.168.0.5:3306 192.168.0.5:3306/telnet: Name or service not known This may suggest that telnet is not installed, or it may indicate another problem Any suggestions are welcome TIM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Timestamp problem.
If you run the select SELECT NOW() + 1*RAND(); a few times, you'll see that not all values are valid timestamps, e.g.: mysql SELECT NOW() + 1*RAND(); +--+ | NOW() + 1*RAND() | +--+ | 20050713112881 | +--+ 1 row in set (0.00 sec) If you're trying to add seconds onto a timestamp, you're probably looking for something like: SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(NOW()) + 1*RAND()); -Original Message- From: Antonio Gulli [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 13, 2005 8:31 AM To: mysql@lists.mysql.com Subject: Timestamp problem. I have the following part in a schema describe feeds . | pubdate | timestamp| YES | | CURRENT_TIMESTAMP | | show create table feeds; `pubdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, when i make an INSERT INTO feeds(, pubdate) VALUES (,NOW()+1*RAND()) is there any chance to have: mysql select count(*) from feeds where pubdate = 0; +--+ | count(*) | +--+ | 593923 | +--+ 1 row in set (5.90 sec) mysql select count(*) from feeds where pubdate 0; +--+ | count(*) | +--+ | 287532 | +--+ 1 row in set (5.33 sec) -- Sometimes life hits you in the head with a brick. Don't lose faith. -- 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 crash due to page corruptions
Hi, We are using mysql 4.0.20 on RHEL3.0 with circular replication setup A -B -C -A . A is the master and all operations will be happening on A. We are facing frequent mysql crash on Master with page corruption errors. How to identify which process is causing this page corruption. After restarting the mysql it is working fine. But we want to identify the process which is causing page corruption ,Any clues will be helpful for us. Thanks Anil DBA
Getting first and last day of week
Is there an easy way of finding the first and last day of a week? I'm looping through week numbers, I.E. 2005-06-12 is week 23, but for display I would like to know the first and last day of that week. I usually just loop through some days and find them myself, but I am curious to know if there is a more efficient way with MySQL. Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Getting first and last day of week
Playing around with the date/time functions, I came up with: select subdate(now(), INTERVAL weekday(now()) DAY); select adddate(now(), INTERVAL 6-weekday(now()) DAY); So once you have a date in the desired week, it's easy to calculate the first/last days in that calendar week. -Original Message- From: Eric Jensen [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 13, 2005 12:52 PM To: mysql@lists.mysql.com Subject: Getting first and last day of week Is there an easy way of finding the first and last day of a week? I'm looping through week numbers, I.E. 2005-06-12 is week 23, but for display I would like to know the first and last day of that week. I usually just loop through some days and find them myself, but I am curious to know if there is a more efficient way with MySQL. Eric -- 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: Databases in a different location than the default
Tim Holmes wrote: [Tim Holmes] Gleb, et. al. As you suggested, I have checked out the log files and this is what I have found: 050713 11:00:09 mysqld started 050713 11:00:09 [Warning] Asked for 196608 thread stack, but got 126976 050713 11:00:09 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use 050713 11:00:09 [ERROR] Do you already have another mysqld server running on port: 3306 ? 050713 11:00:09 [ERROR] Aborting 050713 11:00:09 [Note] /usr/sbin/mysqld: Shutdown complete 050713 11:00:09 mysqld ended This suggests to me a communications problem on either the database server, or the file server where the databases reside. It suggests to me that you already have mysqld running. I guess the next question is how do I check to see whats going on here. I tried telnet 192.168.0.5:3306 and got the following [EMAIL PROTECTED] log]# telnet 192.168.0.5:3306 192.168.0.5:3306/telnet: Name or service not known This may suggest that telnet is not installed, or it may indicate That is not how telnet works. This is how telnet works. telnet 192.168.0.5 3306 another problem Any suggestions are welcome TIM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Myisamchk on really large tables.
Hello. Check if the speed of recovering changes if you run myisamchk with -n option among other parameters to force using sort recovery. However, it requires a lot of disk space. See: http://dev.mysql.com/doc/mysql/en/myisamchk-repair-options.html I guess it might help because this variable related to REPAIR command on the server could force server to use much faster sort method instead of failing to old and slow key cache method: myisam_max_sort_file_size The maximum size of the temporary file MySQL is allowed to use while re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE). If the file size would be bigger than this value, the index is created using the key cache instead, which is slower. RV Tec [EMAIL PROTECTED] wrote: Folks, We are going through a nasty problem, and I hope you guys can help me out. We are running a couple of MySQL 4.0.25 at 2 OpenBSD Opteron (246, 2GB RAM, 36GB RAID-1 15K), and for backup purposes, 1 Linux CentOS 4.0 Pentium 4 (3GHz, 1GB RAM, 80GB SATA 10K). They are all connected through replication, and everything works perfectly. Our database is now close to 20GB, divided in 160 tables. There are only 2 tables that are larger than 1GB, all others are below 300MB. These two large tables, they have about 30.000.000 rows and 11 keys of indexing (each). Every now and then, I used to run myisamchk to fix and optimize this table (myisamchk -r, -S, -a). All of a sudden, this process has become slower to the point that I have given up. It used to take 3 hours to repair/optimize each table. Now we are running myisamchk for the last 12 hours and it has completed only 1/3 of one table! Back when things used to work, after the optimization, the size of the index file (MYI) was about 30% smaller. And everything was much faster (at least for one month). And what scares me the most, is that this table is only growing. There is no chance of trimming it. Does anyone know what can I do to optimize this table? Maybe I am reaching MySQL limits? Or just incorrect hardware for this task? What happens if we get a nasty table error? No way of repairing it? I have tried the same process over the OpenBSD's and Linux boxes. They all show the exact same behaviour. Also, I have tried to create a new index file (as told on MySQL docs: truncate table, copy back the MYD, then running myisamchk -rq) and no luck... it is even slower. This is part of my.cnf: [myisamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 4M write_buffer = 4M tmpdir = /var/mysql/temp Just to make things clear: this tmpdir is another unused HD, connected to the RAID's 2 channel -- this way we are not losing performance of reading/writing, at least I think. The MySQL daemon is dead while running myisamchk, and there is no other service running at the time -- so we can bite memory some more. Only at the very beggining we see a CPU hog (peaking at 95%), but after a few seconds, the CPU use drops to 5%, maybe even less, and we see only HD activity -- and not that much. Is this right?! Any ideas?! Thanks for your time. Best regards, RV PS: Here comes some useful(?) info. If things break, please, ask me for a text file. [EMAIL PROTECTED] db]# ls -la offline* -rw-rw 1 _mysql _mysql 2552151732 Jul 13 09:46 offline.MYD -rw-rw 1 _mysql _mysql 5300221952 Jul 13 09:46 offline.MYI -rw-rw 1 _mysql _mysql8940 Apr 19 01:19 offline.frm [EMAIL PROTECTED] db]# ls -la history* -rw-rw 1 _mysql _mysql 2372474988 Jul 13 09:47 history.MYD -rw-rw 1 _mysql _mysql 3943869440 Jul 13 09:47 history.MYI -rw-rw 1 _mysql _mysql8926 Apr 19 01:26 history.frm alpha mysql select count(*) from offline; +--+ | count(*) | +--+ | 30992996 | +--+ 1 row in set (0.02 sec) alpha mysql select count(*) from history; +--+ | count(*) | +--+ | 29676533 | +--+ 1 row in set (0.01 sec) alpha mysql describe offline; +--+---+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-++ | code | bigint(20)| | PRI | NULL| auto_increment | | history1 | int(10) | YES | MUL | NULL| | | desc | varchar(24) | YES | MUL | NULL| | | sold | int(1)| YES | MUL | NULL| | | emptor | int(9)| YES | MUL | NULL| | | shipment | int(5)| YES | MUL | NULL| | | share| bigint(20)| YES | MUL | NULL| | | sequence | bigint(20)|
Re: Mysql crash due to page corruptions
Please upgrade to the newest 4.0 mysql binaries. Anil wrote: Hi, We are using mysql 4.0.20 on RHEL3.0 with circular replication setup A -B -C -A . A is the master and all operations will be happening on A. We are facing frequent mysql crash on Master with page corruption errors. How to identify which process is causing this page corruption. After restarting the mysql it is working fine. But we want to identify the process which is causing page corruption ,Any clues will be helpful for us. Thanks Anil DBA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting first and last day of week
John Trammell wrote: Playing around with the date/time functions, I came up with: select subdate(now(), INTERVAL weekday(now()) DAY); select adddate(now(), INTERVAL 6-weekday(now()) DAY); So once you have a date in the desired week, it's easy to calculate the first/last days in that calendar week. I can think of a lot of ways once I have the date, that's not really the problem. All I have is a week number, I.E. 23, 24, 25, etc. I need to find out some dates they correspond to and I'm not sure how to do that. Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Getting first and last day of week
How about adddate(20050101, INTERVAL 7*23 DAY) for getting a date in week 23? -Original Message- From: Eric Jensen [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 13, 2005 4:29 PM To: mysql@lists.mysql.com Subject: Re: Getting first and last day of week John Trammell wrote: Playing around with the date/time functions, I came up with: select subdate(now(), INTERVAL weekday(now()) DAY); select adddate(now(), INTERVAL 6-weekday(now()) DAY); So once you have a date in the desired week, it's easy to calculate the first/last days in that calendar week. I can think of a lot of ways once I have the date, that's not really the problem. All I have is a week number, I.E. 23, 24, 25, etc. I need to find out some dates they correspond to and I'm not sure how to do that. Eric -- 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: Engine Type
I don't quite understand your question. I assume you are interested in the MySQL database engine. The latest production version of MySQL should be fine for this, running on suitable hardware. The server would not actually be VB6 but I assume you mean that there may be some head office clients written using VB6 that access the same database as used by all the branches. If you give me more details I could offer some more advice. John Bonnett -Original Message- From: Michael Louie Loria [mailto:[EMAIL PROTECTED] Sent: Wednesday, 13 July 2005 8:13 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Engine Type -BEGIN PGP SIGNED MESSAGE- Hash: SHA512 I would like to ask what is the best type of engine for this project I will take Payroll and Daily Time Record system - - Payroll system is located in the head office (server). Daily Time Record is located in all 10 branches (clients). The connection from client to server is VPN The Daily Time Record will save the data to the server everytime an employee logs in. The server and client will be developed with Visual Basic 6. Thanks, Michael Louie Loria -BEGIN PGP SIGNATURE- Comment: GPG Public Key: https://www.biglumber.com/x/web?qs=0x4A256EC8 Comment: GPG Public Key: http://www.lorztech.com/GPG.txt Comment: Yahoo ID: michaellouieloria iQEVAwUBQtVSiLXBHi2y3jwfAQo8gwf+OrUCgzirjzpWXtTcAaXREFJTlXVV5mhJ ggN75al7O47V7hIhY+8rUu9HOYAJj/Hp3HGo3Ls5AJ8febV9uXYW9RK424iUOp8p j7DckjisWwVXGqmKlD5dd7ftjNay5C3Otx23+ggEFqA8pwNEp2yZsppxhR6n00Tq 8kwy6wuGlizwIkLgktah1Xcae1d6YvmuvC7kC1dTQypzd2/Kgz7TJKsqCYrvu7EN 0BX1lwIDkolG5ifWJtnRRebM53A5zxSahhItRl6r6MyJMPsg4sa+QeRyzM+qgdRd r2Kn6kem8gvKA5m3uNWVRqFraHf/ciENKsYXhIvYSxrvO1ScOXkdgQ== =hx6L -END PGP SIGNATURE- -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.13/47 - Release Date: 7/12/2005 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]
Best way to store these data?
Hi, I need to store all 50 states and there county + zip in mysql. What is the best way to do it? Should I just put them in to one table and use the states row for primary Id? or Put every state in separate table and use the zip row for primary Id? Which would be faster to find a county? The main search would be by zip or state. Thank you Laszlo Antal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Engine Type
-- I don't quite understand your question. I assume you are interested in the MySQL database engine. The latest production version of MySQL should be fine for this, running on suitable hardware. The server would not actually be VB6 but I assume you mean that there may be some head office clients written using VB6 that access the same database as used by all the branches. If you give me more details I could offer some more advice. -- My question was: What is the best engine for the project I will create? MyISAM, InnoDB, etc.. The server and client applications will be created with Visual Basic 6. The OS of server and client will be windows 2000 or XP. The clients will be scattered in different places. The client and server connection will through VPN. The Server will have a payroll and Daily Time Record (DTR) system while the client will only have the DTR system. Everytime an employee from a branch (client part) logs in. The info will be saved to the Server (MySQL) passing through VPN. But my problem is still I can't connect my client application to the server which contains the mysql database. But when I run my client application in the server, the client applications runs. I have disabled all firewalls. But still solving this problem. Hope I could get some insights regarding this problem. Thanks, Michael Louie Loria __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com signature.asc Description: 3412282408-signature.asc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to update a mysql table from access
Hey there, thanks to some help i have received right here, i have been able to access a mysql database on a linux computer from MS access on a windows computer, i was able to connect and create the tables and export all rows correctly.. i used MyODBC from mysql. ok, here is the deal, the access database gets info from another program and adds new rows every 15 seconds or so, i need some automated way to sync the two databases together every oh,,, 5 minutes or so. there is lots of documentation on how to do this by linking a table to a mysql table, however, when i do this, the access table is the one that gets updated, not the mysql table (deletes any info received since last update) and still does not provide a way to do this automatically. I cant find way to do it anywhere, little or no docs.. or i am looking in the wrong place. thanks for reading this, hope someone can help -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best way to store these data?
You did not say what these data would be used for. Will there be heavy reads? What is the time requirement for each query? Proper way to do this would be by having three different normalized tables. State: state_id PK state_name County: county_id (either abbreviate or create unique numeric id) county_name state_id FK Zip: zip_code PK county_id FK The above schema is normalized. But since portion of the data is static (state and county) it is possible to combine the first two tables into one table. However, the zip table needs to be in separate table unless you like updating records in multiple places. With proper indexing you can find a county name by a zip code with a simple where. It should be very fast. But still slower than one table solution. On 7/13/05, Laszlo Antal [EMAIL PROTECTED] wrote: Hi, I need to store all 50 states and there county + zip in mysql. What is the best way to do it? Should I just put them in to one table and use the states row for primary Id? or Put every state in separate table and use the zip row for primary Id? Which would be faster to find a county? The main search would be by zip or state. Thank you Laszlo Antal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- http://chatter.mirislam.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to update a mysql table from access
At 08:51 PM 7/13/2005, you wrote: Hey there, thanks to some help i have received right here, i have been able to access a mysql database on a linux computer from MS access on a windows computer, i was able to connect and create the tables and export all rows correctly.. i used MyODBC from mysql. ok, here is the deal, the access database gets info from another program and adds new rows every 15 seconds or so, i need some automated way to sync the two databases together every oh,,, 5 minutes or so. there is lots of documentation on how to do this by linking a table to a mysql table, however, when i do this, the access table is the one that gets updated, not the mysql table (deletes any info received since last update) and still does not provide a way to do this automatically. I cant find way to do it anywhere, little or no docs.. or i am looking in the wrong place. thanks for reading this, hope someone can help Have you tried prefixing the MySQL table with the MySQL database name? Example: select * from MySqlDb1.Table1; insert into MySqlDb1.Table1 (cust_id, cust_name) values(1,John Smith); Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to update a mysql table from access
you mean like in a script? the windows computer runs access, which i am not very familiar with and was able to accomplish what i have done so far by lots o' docs at the mysql.com site. sorry for the newbie-ness of this question. i am somewhat familliar with python, maybe there is a module i can use for this.. thanks, On Wed, 2005-07-13 at 22:10 -0500, mos wrote: At 08:51 PM 7/13/2005, you wrote: Hey there, thanks to some help i have received right here, i have been able to access a mysql database on a linux computer from MS access on a windows computer, i was able to connect and create the tables and export all rows correctly.. i used MyODBC from mysql. ok, here is the deal, the access database gets info from another program and adds new rows every 15 seconds or so, i need some automated way to sync the two databases together every oh,,, 5 minutes or so. there is lots of documentation on how to do this by linking a table to a mysql table, however, when i do this, the access table is the one that gets updated, not the mysql table (deletes any info received since last update) and still does not provide a way to do this automatically. I cant find way to do it anywhere, little or no docs.. or i am looking in the wrong place. thanks for reading this, hope someone can help Have you tried prefixing the MySQL table with the MySQL database name? Example: select * from MySqlDb1.Table1; insert into MySqlDb1.Table1 (cust_id, cust_name) values(1,John Smith); Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Split a table?
Depends on how your table is designed. You could do an 'INSERT INTO .. SELECT FROM ..' with a WHERE/ORDER BY/LIMIT combo (switch the ORDER BY for each new table). It would be probably easiest if you have an AUTO_INCREMENT field.. Atle - Flying Crocodile Inc, Unix Systems Administrator On Tue, 12 Jul 2005, Brian Dunning wrote: If I have a table with 200K records, is there an easy way to split it into two separate tables with 100K records each? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Engine Type
I think either table engine would be OK but InnoDB has some good extra features and is the default in recent versions of MySQL so I would go with that. Now about your connection problem You need to check the following things in this order. Can you get to the server machine on the network? Ping should check that. If not some routing tables may need to be changed. Consult your networking people. Can you get to the MySQL port (usually 3306 but might be different)? I'm not sure how you check that. If you get this far you should get some response from MySQL even if it is just to tell you that access is denied. If that happens it is a MySQL permissions problem. Make sure the user you are logging in as (to MySQL) has permission to log in from the host where you are running the client. How far do you get when you try to connect your client to the MySQL server? Do you get any error messages? Make sure your VB6 application fully reports any errors that happen on the database connection. (It will depend on how you are connecting how you do that.) John B. -Original Message- From: Michael Louie Loria [mailto:[EMAIL PROTECTED] Sent: Thursday, 14 July 2005 10:46 AM To: John Bonnett, RD Australia; [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Engine Type -- I don't quite understand your question. I assume you are interested in the MySQL database engine. The latest production version of MySQL should be fine for this, running on suitable hardware. The server would not actually be VB6 but I assume you mean that there may be some head office clients written using VB6 that access the same database as used by all the branches. If you give me more details I could offer some more advice. -- My question was: What is the best engine for the project I will create? MyISAM, InnoDB, etc.. The server and client applications will be created with Visual Basic 6. The OS of server and client will be windows 2000 or XP. The clients will be scattered in different places. The client and server connection will through VPN. The Server will have a payroll and Daily Time Record (DTR) system while the client will only have the DTR system. Everytime an employee from a branch (client part) logs in. The info will be saved to the Server (MySQL) passing through VPN. But my problem is still I can't connect my client application to the server which contains the mysql database. But when I run my client application in the server, the client applications runs. I have disabled all firewalls. But still solving this problem. Hope I could get some insights regarding this problem. Thanks, Michael Louie Loria __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to update a mysql table from access
At 09:32 PM 7/13/2005, you wrote: you mean like in a script? the windows computer runs access, which i am not very familiar with and was able to accomplish what i have done so far by lots o' docs at the mysql.com site. sorry for the newbie-ness of this question. i am somewhat familliar with python, maybe there is a module i can use for this.. thanks, You can use any language you like. I would have assumed you would use the same language that you used to access your Access database. Something like Visual Basic., Delphi, PHP, Perl, Python etc..You could even use MySQL.exe and execute an SQL script on that for something that is quick and dirty, but I wouldn't recommend it because you have no error checking capability (in other words you have no way of knowing if the script succeeded or not). http://sourceforge.net/projects/mysql-python Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Engine Type
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 [EMAIL PROTECTED] wrote: I think either table engine would be OK but InnoDB has some good extra features and is the default in recent versions of MySQL so I would go with that. Now about your connection problem You need to check the following things in this order. Can you get to the server machine on the network? Ping should check that. If not some routing tables may need to be changed. Consult your networking people. Can you get to the MySQL port (usually 3306 but might be different)? I'm not sure how you check that. If you get this far you should get some response from MySQL even if it is just to tell you that access is denied. If that happens it is a MySQL permissions problem. Make sure the user you are logging in as (to MySQL) has permission to log in from the host where you are running the client. How far do you get when you try to connect your client to the MySQL server? Do you get any error messages? Make sure your VB6 application fully reports any errors that happen on the database connection. (It will depend on how you are connecting how you do that.) John B. I think I stick with InnoDB. I already fixed, it was not the problem of MySQL. It was the fault of the VPN. I change my encryption to 3DES frm AES-256 and it worked. I think it had to do something with my key size which is only 1024. Thanks for da help, Michael Louie Loria -BEGIN PGP SIGNATURE- Comment: GPG Public Key: https://www.biglumber.com/x/web?qs=0x4A256EC8 Comment: GPG Public Key: http://www.lorztech.com/GPG.txt Comment: Yahoo ID: michaellouieloria iD8DBQFCr0N9EWLHf0olbsgRA3QeAJwKJEt3BLmuNRPf99AKmyZEHYMriQCfcVcd zgyStn3UcOkHx/RRcbsrY9E= =SSIa -END PGP SIGNATURE- 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]
