Re: 5.03 Still crashes on win32
Warning, do not install 5.03 it still crashes on win32. This occured after very few minutes of testing. I will send more info as I locate it. Great... Is there any official word on the 5.0.2 crashes? Have there been fixes regarding this issue? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character Set problem
Gleb Paharenko wrote: Thanks for the reply. See: http://dev.mysql.com/doc/mysql/en/problems-with-character-sets.html Yeah Thanks - seen that already. Check that you have the charsets directory in c:\mysql\share. Again, thanks, but that doesn't actually solve the issue. There are entries in the Index file for the appropriate language number. There isn't, however, an xml file for the language (utf8 in this instance). I've tried changing the server default character set to cp1251. I've recreated complete databases from scratch making sure that the character set it uses is cp1251. None of the above have worked. Any other thoughts? This is mySQL 4.1.10-nt on win2003 server giving : File 'c:\mysql\share\charsets\?.conf' not found (Errcode: 22) Character set '#33' is not a compiled character set and is not specified in the 'c:\mysql\share\charsets\Index' file which is classified as Bug number 312 (http://bugs.mysql.com/bug.php?id=312) Regards Stephen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server not resolving and hostnames
Hello. All other applications properly do lookups, inclusing Apache, PHP, exim4 and courier-mta. Please check that you can find afterparty.local using `host` utility and able connect to it with mysql client. Send an output of the following statement executed on the weird server: SHOW VARIABLES; Thomas van Gulick [EMAIL PROTECTED] wrote: I'm running multiple mysql servers on a local network. One of them has an additional network interface to the outside world. This server is the only one properly resolving any hostnames. All other servers fail to resolv anything. I'm running bind9 named for my outside domain and a local domain and I've notices the Mysql daemons except the one with outside link, fail to do any domain lookup, there are no packets sent over port 53, while the Mysql daemon with outside link properly does a lookup request on port 53 to my name daemon. Using IPs all is fine, replication starts normally, but when using the hostnames, the Mysql daemon fails to connect to it's master with Error: Unknown MySQL server host 'afterparty.local' (2)' errno: 2005. All other applications properly do lookups, inclusing Apache, PHP, exim4 and courier-mta. So, to summarize. IP works, hostname doesn't. On the server with outside link, hostname works, using the same my.cnf, hosts, resolv.conf and host.conf. Same libc too. Same Mysql version too (4.1.8 icc version, I've tried 4.1.10a to no avail) Can anyone thing of anything that might cause this? Many thanks! Thomas -- 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: maximum number of indexes
Hello. You may use: http://dev.mysql.com/tech-resources/crash-me.php especially see the 'Index limits' section. Not only the number of indexes is valuable, but, say, the length of index as well. G M [EMAIL PROTECTED] wrote: [-- text/plain, encoding 7bit, charset: us-ascii, 10 lines --] hi all, in a table of say 100 fields, how many (maximum )numbers of indexes can be created... thx in adv. - Do you Yahoo!? Yahoo! Small Business - Try our new resources site! -- 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: access-trouble using root
Hello. Add '[EMAIL PROTECTED]' account. I did this; 050328 16:20:57 7 Query GRANT ALL PRIVILEGES ON *.* TO root@'%' GRANT ALL PRIVILEGES ON *.* TO root@'%' Yet still I get 050328 16:21:08 8 Connect Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) 050328 16:13:33 4 Connect Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) -- 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: ERROR 1105: Unknown error with flush logs
Hello. Could you reproduce the error with the official binaries of the latest release? Also you may switch to the debug version of the server and try to find the clues in the trace files. Check that everything is OK with OS. See: http://dev.mysql.com/doc/mysql/en/debugging-server.html Sun, Jennifer [EMAIL PROTECTED] wrote: Hi, We are running mysql-4.0.22 on Gentoo Linux. We do regular mysqlhotcopy o= f all databases and do regular flush logs for transaction logs.=20 However, My flush logs failed since yesterday,=20 the mysqladmin flush-logs gave me error: /usr/bin/mysqladmin: refresh fai= led; error: 'Unknown error' When I login to server, do 'flush logs' in command line, got error ' ERRO= R 1105: Unknown error ' Anyone has idea on how to resolve this issue, please share with us. Thank= s.=20 Jennifer -- 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: 5.03 Still crashes on win32
There's no real official word on the 5.0.2 crashes on win32, although I believe there were a number of bug reports. A lot of the crashing that I have come across when using 5.0.2 on win32 has to do with the informational functions - DESCRIBE, SHOW etc. There were also a number of crashes that I came across when trying views, but these seemed to be related the view algorithms, as with certain functions (inserting to a view etc.) the algorithm was supposed to be dynamic as I understand, but that functionality hadn't been fully written. Creating views on a join of a table and another view, then selecting from it also caused crashing. On speaking with one of the support guys, he mentioned that pretty much all of this had been fixed on win32 within 5.0.3 however. Frederick - do you have reproducible test cases for the crashes? Filing them over at bugs.mysql.com would be helpful if you do.. Cheers, Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: 29 March 2005 10:39 To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: 5.03 Still crashes on win32 Warning, do not install 5.03 it still crashes on win32. This occured after very few minutes of testing. I will send more info as I locate it. Great... Is there any official word on the 5.0.2 crashes? Have there been fixes regarding this issue? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- 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 Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.4 - Release Date: 27/03/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.4 - Release Date: 27/03/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with a mutuality check (good query exercise :)
I got stuck in this one, and i belive there's a solution, i just don't happen to see it. i have a table with conections between itens. something like +---+---+ | A | B | +---+---+ | 1 | 2 | | 1 | 3 | | 1 | 4 | | 2 | 1 | +---+---+ i'm trying to solve with one query a way to get all of the relations with 1 on the A colum but having another field, telling me if the relation is mutual. something that would return +---++ | B | mutual | +---++ | 2 |1| | 3 |0| | 4 |0| +---++ Can you think of anything that doesn't involve some big temporary tables or one extra query for every row found on the first one? , Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with a mutuality check (good query exercise :)
select l.b, r.a = l.b from tab l join tab r on l.a = r.b where l.a = 1 ; seems to produce the result you want Gabriel B. [EMAIL PROTECTED] 29/03/2005 09:30 Please respond to Gabriel B. [EMAIL PROTECTED] To mysql@lists.mysql.com cc Subject help with a mutuality check (good query exercise :) I got stuck in this one, and i belive there's a solution, i just don't happen to see it. i have a table with conections between itens. something like +---+---+ | A | B | +---+---+ | 1 | 2 | | 1 | 3 | | 1 | 4 | | 2 | 1 | +---+---+ i'm trying to solve with one query a way to get all of the relations with 1 on the A colum but having another field, telling me if the relation is mutual. something that would return +---++ | B | mutual | +---++ | 2 |1| | 3 |0| | 4 |0| +---++ Can you think of anything that doesn't involve some big temporary tables or one extra query for every row found on the first one? , Gabriel -- 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: 'Can't connect to local MySQL server....' error
after more examination, it appears that i have different versions of mysql, installed in different locations an #/rpm -q mysql says that i have mysql-3.23, which is the FC2 versionof mysql. given that i apparently have mysql files in /usr/bin, and /usr/local/bin, i'd like to first clean out the system, so i have the mysql files in the correct location, and then i'd like to upgrade mysql to the latest stable mysql version... can anyone provide pointers as to how to do this... or, can anyone tell me how to clean up my mysql duplicates? or, can i simply delete the mysql files under the /usr/local/bin dir, and somehow 'point' the system to the '/usr/bin' dir for the mysql files? it seems my environment path has both '/usr/local/bin' and '/usr/bin'. also, how do i change the environment path?? thanks bruce -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: Monday, March 28, 2005 7:54 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: 'Can't connect to local MySQL server' error On Monday, March 28, 2005 21:36, bruce wrote: hi... a server went from RH8 to FC2. it appears that the guy who did the upgrade didn't perfrom any backups... i get a 'Can't connect to local MySQL server through socket...' error. i've tried to 'fix' the tables 'mysql_fix_privilege_tables' with no luck... i've tried to start/restart with no luck. i've lloked through google/mysql with no luck... any ideas as to what might be causing the problems... if i can get the daemon started, i'll (hopefully) be ok... Is there anything in the error log? You could try starting it from the command line to see what errors you get. The following will work assuming you installed using the rpm's. Otherwise the location of mysqld and the user may differ. #su - mysql #/usr/sbin/mysqld Run this and see what errors are reported. thanks bruce [EMAIL PROTECTED] -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
load data from master
Can I run load data from master on myisam tables where my table size is approx 30G? Is there a better way to do this ? - Do you Yahoo!? Yahoo! Small Business - Try our new resources site!
Re: load data from master
I cannot stop or lock tables on the master webapps write data to it constantly. I am copying over the binlogs and applying them to the slave. It is taking a long time so I just want to know if load data or copying tables over would bring replication back to where it is. I Renato Golin [EMAIL PROTECTED] wrote: On Tuesday 29 March 2005 11:26, Shamim Shaik wrote: Can I run load data from master on myisam tables where my table size is approx 30G? - stop slave - on master do: - lock tables - tar cpf - /var/lib/mysql/tbl | ssh -C slave tar xpf - -C /var/lib/mysql/tbl - start slave - on master again: - unlock tables It's faster, but will keep you out of order untill all data is copied. --rengolin - Do you Yahoo!? Yahoo! Small Business - Try our new resources site!
Re: load data from master
On Tuesday 29 March 2005 11:26, Shamim Shaik wrote: Can I run load data from master on myisam tables where my table size is approx 30G? - stop slave - on master do: - lock tables - tar cpf - /var/lib/mysql/tbl | ssh -C slave tar xpf - -C /var/lib/mysql/tbl - start slave - on master again: - unlock tables It's faster, but will keep you out of order untill all data is copied. --rengolin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data from master
On Tuesday 29 March 2005 11:44, Shamim Shaik wrote: I cannot stop or lock tables on the master webapps write data to it constantly. I am copying over the binlogs and applying them to the slave. It is taking a long time so I just want to know if load data or copying tables over would bring replication back to where it is. Don't know if it's faster but mysqldump works fine and you can keep serving the pages... regarding the time, it'd be very slow anyway since it's 30Gb of data... unless you have gigabit link between them it should take hours... --rengolin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table join question
Although I didn't find it in the docs anywhere, I know from experience that you cannot join more than 31 tables in 4.0.21 I was wondering if anybody knew if this limit has changed in version 5 Thx Rob
RE: table join question
I suspect 5.x.x is the same as 4.1.x which is 61 tables - dependent on processor. See an earlier posting of mine on the same subject. What they really need to do is replace the #define with a struct and some 'code' to interpret its contents then you could have any limit you wanted - provided you're prepared to wait whilst it works out the result. Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk Email: [EMAIL PROTECTED] -Original Message- From: Rob Brooks [mailto:[EMAIL PROTECTED] Sent: 29 March 2005 15:57 To: 'MySQL list' Subject: table join question Although I didn't find it in the docs anywhere, I know from experience that you cannot join more than 31 tables in 4.0.21 I was wondering if anybody knew if this limit has changed in version 5 Thx Rob ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: need opinion on FmPro Migrator
The tool is well regarded. I have not used it, but many people have with success. But, most FMP databases are so poorly structured that it is often best to start over. Keep in mind the necessity of importing data from FMP to MySQL though. . Kevin Bice Foster Enterprises 512-583-0573 http://fmpweb.com -Original Message- From: Ted Zeng [mailto:[EMAIL PROTECTED] Sent: Monday, March 28, 2005 11:05 AM To: mysql@lists.mysql.com Subject: need opinion on FmPro Migrator Hi, all, I need to convert databases from FileMaker to MySQL. I searched the web and found this product FmPro Migrator I am wondering if people here have used it and know how well it works. Or are there any other way to do the conversion out there? Ted Zeng Adobe Systems Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: maximum number of indexes
G M [EMAIL PROTECTED] wrote on 03/29/2005 01:54:17 AM: hi all, in a table of say 100 fields, how many (maximum )numbers of indexes can be created... thx in adv. This is really just a math problem as the database limits are much smaller than the number of possible index combinations. Given 100 items, how many different arrangements of those 100 items can exist if taken 1 at a time + 2 at a time + 3 at a time + 4 at a time + 5 at a time + ... + 100 at a time? 1 at a time = 100 different indexes 2 at a time = 100 * 99 = 9900 different indexes 3 at a time = 100 * 99 * 98 = 970200 4 at a time = 100 * 99 * 98 * 97 = 94109400 + ... + 100 at a time = 100 * 99 * 98 * 97 * ... * 2 * 1 = 100! = 9.3326215443944152681699238856267e+157 The actual limits on how many indexes and how many columns can be in each index are different for each storage engine: http://dev.mysql.com/doc/mysql/en/storage-engines.html http://dev.mysql.com/doc/mysql/en/innodb.html MyISAM: 64 indexes per table 16 columns per index 1000 bytes max per key (unless you recompile). MEMORY: 32 indexes per table 16 columns per index 500 bytes max per key etc. If I may be curious, what is your real design issue and why are you considering so many keys? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
re: copying a db for mysql 5.0.3
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Until this version, I used to just copy the data/db directory from the old database to the new one. Now, I can't do that, and I think it is because of the data dictionary. I have a database that only exists on this machine, and I want to get it into the new database. When I tried to just copy I had complaints of not being able to find the tables .frm files, even though I see them in the data directory. Is there any way to get this to work, now? Thanx. - -- 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 iD8DBQFCSXsqikQgpVn8xrARAiwoAJ9k41oMugqdnYIj20IT2cKrhSrprACZAXFT CgG+rG4rXWl686kKyaoMTtM= =bWvN -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query question
I want to get everything from user than if record exist in admin so
user has admin(administrator) in table user with user.id =
admin.admin_id, so I need to get 'admin' first_name and last_name
If there is no record in table admin with adin.user_id = user.id ,
than I need at least all records from user
Table: user
| id| int(10) | | PRI | NULL
| auto_increment |
| email | varchar(100)| YES | | NULL
||
| password | varchar(45) | YES | | NULL
||
| first_name| varchar(100)| YES | | NULL
||
| last_name | varchar(100)| YES | | NULL
||
| type | enum('admin','user')| YES | | NULL
| auto_increment |
Table: admin
| id| int(10) | | PRI | NULL| auto_increment |
| admin_id | int(10) | YES | | NULL||
| user_id | int(10) | YES | | NULL||
| date | datetime | YES | | NULL||
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: problem with mysql-max-5.0.3 for Solaris 8 32 bit
It would appear that the package is for the 64-bit OS, even though it is listed as being for the 32-bit one. I see directories, that failed to install, of: /usr/local/mysql-max-5.0.3-beta-sun-solaris2.8-sparc-64bit/sql-bench I had gotten my file from http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-max-5.0.3-beta-sun-solaris2 .8-sparc.pkg.gz/from/http://mysql.mirrors.pair.com/ James, Sorry about this. Somehow the 64bit tarball was used for this package. I've fixed it now and uploaded a new 32bit Solaris 2.8 PKG. Note that it will take a bit to propagate out to the mirrors (probably 8-10 hrs). Thanks for notifying us about this. Matt -- Matt Wagner, Production Engineer MySQL AB, www.mysql.com Northfield, MN, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
Jerry Swanson [EMAIL PROTECTED] wrote on 03/29/2005 11:43:56 AM:
I want to get everything from user than if record exist in admin so
user has admin(administrator) in table user with user.id =
admin.admin_id, so I need to get 'admin' first_name and last_name
If there is no record in table admin with adin.user_id = user.id ,
than I need at least all records from user
Table: user
| id| int(10) | | PRI | NULL
| auto_increment |
| email | varchar(100)| YES | | NULL
||
| password | varchar(45) | YES | | NULL
||
| first_name| varchar(100)| YES | | NULL
||
| last_name | varchar(100)| YES | | NULL
||
| type | enum('admin','user')| YES | | NULL
| auto_increment |
Table: admin
| id| int(10) | | PRI | NULL| auto_increment |
| admin_id | int(10) | YES | | NULL||
| user_id | int(10) | YES | | NULL||
| date | datetime | YES | | NULL||
I think I understand your need: for all users, list the user's name and
the name of their administrator, if an administrator exists
SELECT u.type
, u.first_name
, u.last_name
, au.first_name as admin_first
, au.last_name as admin_last
FROM user u
LEFT JOIN admin a
on a.user_id = u.id
LEFT JOIN user au
on au.id = a.admin_ID;
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Why doesn't MySQL cache queries that start with parenthesis? (further info)
Hi! On Mar 28, Homam S.A. wrote: It seems that MySQL freaks out whenever it seems something that looks like a derive table and refuses it to cache. Even a non-UNION query like: SELECT * FROM (SELECT * FROM X WHERE A = 5) AS DerivedTable Won't be cached. It's a bug. Could you submit a test case to bugs.mysql.com ? Something like: create table t1 (a int); insert t1 values (1); show status like 'Qc%'; select * from ((select * from t1) union all (select * from t1) order by 1); show status like 'Qc%'; would be enough for a test case. (I could submit a bugreport myself, but then you won't be notified when the bug is fixed) I read a comment in the documentation that if you put SQL_CACHE in the SELECTs of the parenthesized queries, it will cache the individual queries: http://dev.mysql.com/doc/mysql/en/query-cache.html That's not true. It won't cache even the parenthsized queries, and the execution time is still the same with SQL_CACHE and not. That's not true. Comment is wrong. Query cache works at the very low level, basically it caches the result of the query as it is sent to the client. Raw data on the wire (almost). So it can only cache the complete query, not a part of it. By the way, there's no need to measure query execution time, you can do 'SHOW STATUS' and watch Qcache% variables - just like I did in the test case above. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie: Average Time on Server query
I am trying to determine the average time that a Distinct IP address is using the server If I have 15 thousand records of ip addresses and access times like: IP Now() media.id -- 10.1.7.205 20050329121645 67 68.252.32.7620050329095923 72 And, I want to set the cut-off time to 15 minutes... Basically, if the user has not requested media on the server in last 15 minutes, the user has logged off can someone point me in the right direction as I am very new to more advanced mysql queries many thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data from master
On Tue, 29 Mar 2005, Shamim Shaik wrote: Can I run load data from master on myisam tables where my table size is approx 30G? Is there a better way to do this ? Hi, 1) LOAD DATA FROM MASTER: From the manual: It acquires a global read lock on the master while taking the snapshot, which prevents updates on the master during the load operation. 2) mysqldump: Unless you specify that mysqldump acquires a lock on the tables, the data on the slave will not be consistent with the master. Neither of the above will work for you since your database cannot be down for an extended period and if you require that your data is consistent on both master and slave. However, depending on your setup, this may work: 1) Create a new database (say db_tmp), identical to the one you need replicated but with no data (see the --no-data option to mysqldump for instance). 2) Swap the two databases so that the newly created empty DB becomes active and turn on binary logging. 3) Copy the inactive database to your slave. 4) Make a copy of the binlogs created while the copy was taking place. Swap the two databases again; reset the master; set up replication to the newly seeded slave. 5) Using mysqlbinlog, feed the data that was updated during the copy to the live database. Around step #4 you also probably want to deny any updates from your system while the binlogs are being processed in order to maintain consistency in your dataset. I haven't tried this approach myself so keep that in mind as I may have missed some steps while plotting it out in my head; I would suggest trying this out in a test environment first. Let us know how it works out if you decide to try it out. Atle - Flying Crocodile Inc, Unix Systems Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
speed of 3.23 vs 4.1 for logging server
Fedora Core 3, which is the Linux distribution that i'm using, is still shipping with 3.23.58 The beta (test) version of Fedora 4 has mysql-4.1.10. However, FC4 is scheduled to go live on June 6th. But i need a MySQL server now. This server will be mostly used for logging (think: syslog logging to SQL), so most of the time will just receive INSERTs on a permanent basis from a couple of sources, to a few tables (just a handful, all of them in two databases) that will be rotated periodically (all tables will be append-only - when they're too big, they're just rotated away and the very old ones are deleted when disk usage hits a threshold). Every now and then, a user or two will perform searches through the logs. Is there a big performance difference between 3.23 and 4.1 in such a case? I prefer to just use whatever's offered in the current distribution because i don't have much time to spend tweaking the system (upgrade MySQL, recompile PHP, verify SELinux policies, etc.), but if there is a really big performance difference, i may do the effort to upgrade MySQL to the one offered in FC4-test. -- Florin Andrei http://florin.myip.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: Average Time on Server query
Graham Anderson [EMAIL PROTECTED] wrote on 03/29/2005 02:20:48 PM: I am trying to determine the average time that a Distinct IP address is using the server If I have 15 thousand records of ip addresses and access times like: IP Now() media.id -- 10.1.7.205 20050329121645 67 68.252.32.76 20050329095923 72 And, I want to set the cut-off time to 15 minutes... Basically, if the user has not requested media on the server in last 15 minutes, the user has logged off can someone point me in the right direction as I am very new to more advanced mysql queries many thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Since you didn't post your actual table structure, I will have to make up some information (like table and field names). I am also assuming that you store your access time in a datetime field and that your server is v4.1 or newer. SELECT accesstime FROM userlog where accesstime (NOW() - 15 minutes) and IP='10.1.7.205' LIMIT 1; If you get a record, the user is still active; No record = too late. Not only does this use the same clock that you used to create the other entries (the one on the MySQL server) but it avoids the use of MAX() or ORDER BY (both of which will slow you down) and it will use an index if you have one. If this doesn't work for you, tell us why and we can work towards a solution. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Newbie: Average Time on Server query (typo fix)
TYPO ALERT!!! I left out the all important word INTERVAL. The query should read: SELECT accesstime FROM userlog where accesstime (NOW() - INTERVAL 15 minutes) and IP='10.1.7.205' LIMIT 1; Sorry all! -S [EMAIL PROTECTED] wrote on 03/29/2005 02:50:35 PM: Graham Anderson [EMAIL PROTECTED] wrote on 03/29/2005 02:20:48 PM: I am trying to determine the average time that a Distinct IP address is using the server If I have 15 thousand records of ip addresses and access times like: IP Now() media.id -- 10.1.7.205 20050329121645 67 68.252.32.76 20050329095923 72 And, I want to set the cut-off time to 15 minutes... Basically, if the user has not requested media on the server in last 15 minutes, the user has logged off can someone point me in the right direction as I am very new to more advanced mysql queries many thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Since you didn't post your actual table structure, I will have to make up some information (like table and field names). I am also assuming that you store your access time in a datetime field and that your server is v4.1 or newer. SELECT accesstime FROM userlog where accesstime (NOW() - 15 minutes) and IP='10.1.7.205' LIMIT 1; If you get a record, the user is still active; No record = too late. Not only does this use the same clock that you used to create the other entries (the one on the MySQL server) but it avoids the use of MAX() or ORDER BY (both of which will slow you down) and it will use an index if you have one. If this doesn't work for you, tell us why and we can work towards a solution. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Newbie: Average Time on Server query (typo fix)
This is it. I quit for today... Change minutes to minute and I promise to be more careful next time -- ROFLMAO! -S [EMAIL PROTECTED] wrote on 03/29/2005 02:59:09 PM: TYPO ALERT!!! I left out the all important word INTERVAL. The query should read: SELECT accesstime FROM userlog where accesstime (NOW() - INTERVAL 15 minutes) and IP='10.1.7.205' LIMIT 1; Sorry all! -S [EMAIL PROTECTED] wrote on 03/29/2005 02:50:35 PM: Graham Anderson [EMAIL PROTECTED] wrote on 03/29/2005 02:20:48 PM: I am trying to determine the average time that a Distinct IP address is using the server If I have 15 thousand records of ip addresses and access times like: IP Now() media.id -- 10.1.7.205 20050329121645 67 68.252.32.76 20050329095923 72 And, I want to set the cut-off time to 15 minutes... Basically, if the user has not requested media on the server in last 15 minutes, the user has logged off can someone point me in the right direction as I am very new to more advanced mysql queries many thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Since you didn't post your actual table structure, I will have to make up some information (like table and field names). I am also assuming that you store your access time in a datetime field and that your server is v4.1 or newer. SELECT accesstime FROM userlog where accesstime (NOW() - 15 minutes) and IP='10.1.7.205' LIMIT 1; If you get a record, the user is still active; No record = too late. Not only does this use the same clock that you used to create the other entries (the one on the MySQL server) but it avoids the use of MAX() or ORDER BY (both of which will slow you down) and it will use an index if you have one. If this doesn't work for you, tell us why and we can work towards a solution. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
merge tables
I read the manual and i am still confused as to how the merge tables use indexes. Can someone help me with this ? - Do you Yahoo!? Yahoo! Mail - Easier than ever with enhanced search. Learn more.
MSSQL Import from MySQL
I need to imort a MySQL DB inot MSQL is this possible? Thank you Andrew -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.4 - Release Date: 27/03/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MSSQL Import from MySQL
I need to imort a MySQL DB inot MSQL is this possible? There are probably other tools that can do this, but Database Workbench can convert your schema and transfer your data: www.upscene.com With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump and missing AUTO_INCREMENT=1000 ??
I have a table that I created by hand like this:
DROP TABLE IF EXISTS testset;
CREATE TABLE testset (
id int(10) unsigned NOT NULL auto_increment,
name varchar(50) NOT NULL default '',
special enum('','all','safe','unsafe') NOT NULL default '',
PRIMARY KEY (id),
UNIQUE KEY name (name)
) TYPE=MyISAM AUTO_INCREMENT=1000;
When I do a mysqldump (version 4.0.18), I want the AUTO_INCREMENT=1000
part to be retained. Sadly, it is stripped off?!
I don't see a command line option for mysqldump to do that though?
http://dev.mysql.com/doc/mysql/en/mysqldump.html
I cannot update the mySQL server version.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
selecting fields with NULL
Hi, I understand that we should use IS instead of = for selecting fields with NULL. But then, shouldn't the statement below cause a syntax error? Please explain. select * from test2 where datecurrent=NULL; Thanks, Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting fields with NULL
leegold [EMAIL PROTECTED] wrote on 03/29/2005 04:58:11 PM: Hi, I understand that we should use IS instead of = for selecting fields with NULL. But then, shouldn't the statement below cause a syntax error? Please explain. select * from test2 where datecurrent=NULL; Thanks, Lee Nope Because the result of comparing anything to null is null, your test query evaluates to: select * from test2 where null and WHERE NULL evaluates to FALSE (because of handling subqueries that return no rows) so that you never ever get any rows. At least it didn't throw any errors on my test server (4.1.1a-alpha-nt) To allow the comparison of null to null to be true, MySQL created the comparitor =. anyvalue=null is false(0) null=null is true(1) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Syntax problem: mysql 3.23 vs 4.13
this sql works on mysql version 3.23.58...my remote server SELECT c.City, r.Region, co.Country FROM subnets s, cities c, regions r, countries co WHERE c.CityId = s.CityId AND c.RegionID = r.RegionID AND c.CountryID = co.CountryId AND s.SubNetAddress = '24.24.172' LIMIT 0 , 30 but the same syntax fails on mysql version 4.1.3-beta ...my local computer When I EXPLAIN the sql, I get the error: Impossible WHERE noticed after reading const table... anyone know what this could be ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
searching work and friends
Sir I had found your address by Denise Johnston from HIMAA.She asked me to write you in order to give me some explanations about research work and friends from your University Thank you I hope to read from you soon. - Découvrez le nouveau Yahoo! Mail : 250 Mo d'espace de stockage pour vos mails ! Créez votre Yahoo! Mail
Infinity as field value
Is there a way to represent infinity in mysql? I've got a range field in my GUI, which is x... if the user chooses this field, in the DB, I store it as: id | from | to | other 2 | x | infinity | etc... this is because there are situations of x and between x AND y, so from and to is the easiest way to store it... I could make infinity default to 100,000,000 or some other number I know will never be reached, but it seems less elegant a solution... thanx, Scott. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Infinity as field value
On Tuesday 29 March 2005 20:51, Scott Klarenbach wrote:
Is there a way to represent infinity in mysql?
I could make infinity default to 100,000,000 or some other number I
know will never be reached, but it seems less elegant a solution...
probably not the best but you could have two fields:
enum value_from { inf_neg, inf_pos, number },
int from,
enum value_to { inf_neg, inf_pos, number },
int to,
And if value is number you try to evaluate it, otherwise you use the
constants.
You could also use 2147483647 and -2147483648 as being less uglier (but still
ugly) as MaxInt.
--rengolin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Infinity as field value
Scott Klarenbach wrote: Is there a way to represent infinity in mysql? I've got a range field in my GUI, which is x... if the user chooses this field, in the DB, I store it as: id | from | to | other 2 | x | infinity | etc... this is because there are situations of x and between x AND y, so from and to is the easiest way to store it... I could make infinity default to 100,000,000 or some other number I know will never be reached, but it seems less elegant a solution... If the user enters infinity, store a NULL value in the field. In your code, if you detect a NULL value, don't use a limit. ie don't say 'between x and y', but instead say something like: where some_field x and some_field y in the case where there are 2 values, or: where some_field x in the case where a NULL value is detected in y, or: where some_field y in the case where a NULL value is detected in x -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
upgrading mysql on RH fedora core 3
hi... we're trying to install mysql/mysql-server (4.1.10a-1.i386) and are running into some serious problems. we had mysql/server (3.23.52-3.i386) running, but needed to go to the higher version... can someone tell us how/what we need to do to get this working correctly. actually, if anybody's managed to do this, can you tell us exactly what rpm packages you used? as you know, dealing with the rpms gets into dependency hell, which we believe has a lot to do with our issues... if you managed to get this version of mysql running on FC3, and you built it from source, can you provide directions/pointers on what you did, where you placed the resulting libs/etc... also, this has to be running with apache/php/perl/etc... thanks bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Group by datetime
Hi folks- This is my first post to any MySQL lists, so be gentle. If I'm posting SQL queries to the wrong forum, please direct me to the proper resources. Anyways, I have a simple schema that stores some IP accounting data. I'm attempting to extract the data, grouping by the service type (label), and also grouping by each 24-hour window/day from the datetime column (timestamp). I'm not sure how to accomplish this type of query, but I'm hoping someone here will help me do this in SQL and avoid having to do it in my Perl code. Thanks in advance! CREATE TABLE stats ( id int(10) unsigned NOT NULL auto_increment, host varchar(255) NOT NULL default '', label varchar(255) NOT NULL default '', evals bigint(20) unsigned NOT NULL default '0', packets bigint(20) unsigned NOT NULL default '0', bytes bigint(20) unsigned NOT NULL default '0', date datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (id) ) TYPE=MyISAM; mysql select * from stats limit 2\G; *** 1. row *** id: 1 host: test2 label: http-inbound evals: 149 packets: 5748 bytes: 583571 date: 2005-03-27 22:23:45 *** 2. row *** id: 2 host: test2 label: ssh-inbound evals: 149 packets: 1104 bytes: 630864 date: 2005-03-27 23:40:09 2 rows in set (0.00 sec) -- Jason Dixon DixonGroup Consulting http://www.dixongroup.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Regarding NULL and '' (null string) treatment in MYSQL
Hi,
MySQL treats NULL, '' (empty string) as different.
I mean when I select from/insert into a table, its behaviour is
different.
Select * from table1 where name='';
is different from
Select * from table1 where name=NULL;
Similarly
Insert into table1(name) values('') is different from
Insert into table1(name) values(NULL)
Please suggest me here, how to overcome this '' (null string)
problem.
Scenario:
I have two tables, one is parent and one is child.
Child is referencing 3 fields in parent table.
Parent table records are empty. Now I am trying to insert into the child
table with '' (null string) values into these 3 fields (which are
referencing to the parent table). Actually insert into the child table
in this scenario should be successful, but I am not able to make it
success because of the problem described above ( Treatment of NULL, ''
are different).
Note: If the parent is not having any records, we can insert into the
child. BCS, in this case referential integrity won't work.
Ps: In oracle, it is not the case it treats NULL,'' as same.
Please suggest me how to proceed here.
Thanks,
Narasimha
Confidentiality Notice
The information contained in this electronic message and any attachments to
this message are intended
for the exclusive use of the addressee(s) and may contain confidential or
privileged information. If
you are not the intended recipient, please notify the sender at Wipro or [EMAIL
PROTECTED] immediately
and destroy all copies of this message and any attachments.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group by datetime [SUMMARY]
On Mar 29, 2005, at 10:38 PM, Jason Dixon wrote: Anyways, I have a simple schema that stores some IP accounting data. I'm attempting to extract the data, grouping by the service type (label), and also grouping by each 24-hour window/day from the datetime column (timestamp). I'm not sure how to accomplish this type of query, but I'm hoping someone here will help me do this in SQL and avoid having to do it in my Perl code. Thanks in advance! I finally managed to grind out what I'm looking for. This is primarily for the archives... mysql SELECT label, SUM(bytes) as bytes, SUM(packets) as packets, SUM(evals) as evals, DATE_FORMAT(timestamp, '%Y-%m-%d') as date from stats GROUP BY label, date ORDER BY date asc, bytes desc; ++-+-+---++ | label| bytes | packets | evals | date | ++-+-+---++ | other-outbound | 630864 |1104 | 149 | 2005-03-27 | | other-inbound | 583571 |5748 | 149 | 2005-03-27 | | site1-inbound-ssh | 112657 | 756 |16 | 2005-03-27 | | site1-inbound-http | 38700 | 165 |16 | 2005-03-27 | | site1-inbound-default | 0 | 0 | 149 | 2005-03-27 | | site1-outbound-default | 0 | 0 | 149 | 2005-03-27 | | site1-outbound-ssh | 0 | 0 | 0 | 2005-03-27 | | site1-outbound-http| 0 | 0 | 0 | 2005-03-27 | | site2-inbound | 0 | 0 | 149 | 2005-03-27 | | site2-outbound | 0 | 0 | 149 | 2005-03-27 | | other-outbound | 637008 |1148 | 151 | 2005-03-28 | | other-inbound | 591209 |5792 | 151 | 2005-03-28 | | site1-inbound-ssh | 112657 | 756 |16 | 2005-03-28 | | site5-inbound |1900 | 23 | 149 | 2005-03-28 | | site3-inbound | 0 | 0 | 149 | 2005-03-28 | | site3-outbound | 0 | 0 | 149 | 2005-03-28 | | site4-inbound | 0 | 0 | 149 | 2005-03-28 | | site4-outbound | 0 | 0 | 149 | 2005-03-28 | | site5-outbound | 0 | 0 | 149 | 2005-03-28 | | site1-inbound-default | 0 | 0 | 151 | 2005-03-28 | | other-inbound | 1561931 | 11173 | 895 | 2005-03-29 | | site1-inbound-http | 77400 | 330 |31 | 2005-03-29 | | other-outbound | 46024 | 330 | 895 | 2005-03-29 | | site5-inbound |1900 | 23 | 1046 | 2005-03-29 | | site1-outbound-default | 0 | 0 | 1046 | 2005-03-29 | | site1-outbound-ssh | 0 | 0 | 0 | 2005-03-29 | | site1-outbound-http| 0 | 0 | 0 | 2005-03-29 | | site2-inbound | 0 | 0 | 1046 | 2005-03-29 | | site2-outbound | 0 | 0 | 1046 | 2005-03-29 | | site3-inbound | 0 | 0 | 1046 | 2005-03-29 | | site3-outbound | 0 | 0 | 1046 | 2005-03-29 | | site4-inbound | 0 | 0 | 1046 | 2005-03-29 | | site4-outbound | 0 | 0 | 1046 | 2005-03-29 | | site5-outbound | 0 | 0 | 1046 | 2005-03-29 | | site1-inbound-default | 0 | 0 | 895 | 2005-03-29 | | site1-inbound-ssh | 0 | 0 |15 | 2005-03-29 | ++-+-+---++ 36 rows in set (0.01 sec) -- Jason Dixon DixonGroup Consulting http://www.dixongroup.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need Help with 813-MDB File
I acquired a CD-ROM that lists many thousands of animal species. The main file is a 813-MB MDB file. I'm not sure if it's a spreadsheet or database, but it's apparently designed to work with Microsoft Access, which I THINK is a spreadsheet. (I don't have it.) I have just enough memory to open the file in WordPad, but it doesn't do any good because much of the data consists of unintelligible characters. Anyway, I need to figure out a way to import this monster into MySQL. Are you aware of any freeware programs that can open up files designed for Access? Is there a way to convert a MDB file directly into a csv file, which could then be imported into MySQL? I'll probably eventually break it into sections. At the very least, I'll probably divide it between vertebrates (which I'll use the most) and invertebrates. Thanks. __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Help with 813-MDB File
David Blomstrom wrote: I acquired a CD-ROM that lists many thousands of animal species. The main file is a 813-MB MDB file. I'm not sure if it's a spreadsheet or database, but it's apparently designed to work with Microsoft Access, which I THINK is a spreadsheet. (I don't have it.) I have just enough memory to open the file in WordPad, but it doesn't do any good because much of the data consists of unintelligible characters. Anyway, I need to figure out a way to import this monster into MySQL. Are you aware of any freeware programs that can open up files designed for Access? Is there a way to convert a MDB file directly into a csv file, which could then be imported into MySQL? I'll probably eventually break it into sections. At the very least, I'll probably divide it between vertebrates (which I'll use the most) and invertebrates. Thanks. mdb is a binary format, and contains data, forms, reports, queries, vb code and other 'stuff'. There is an mdbtools project on sourceforge: http://mdbtools.sourceforge.net/ I'm pretty sure it's Linux-only, but I may be wrong. It exports to various databases ( including MySQL ) and to text files. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need Help with 813-MDB File
DB Tools software will convert the file for you. You can download it at http://dbtools.com.br/EN/index.php. All you have to do is download and install the FreeWare version and then use the TOOLS DAO Import Wizard. J.R. -Original Message- From: David Blomstrom [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 29, 2005 11:29 PM To: mysql@lists.mysql.com Subject: Need Help with 813-MDB File I acquired a CD-ROM that lists many thousands of animal species. The main file is a 813-MB MDB file. I'm not sure if it's a spreadsheet or database, but it's apparently designed to work with Microsoft Access, which I THINK is a spreadsheet. (I don't have it.) I have just enough memory to open the file in WordPad, but it doesn't do any good because much of the data consists of unintelligible characters. Anyway, I need to figure out a way to import this monster into MySQL. Are you aware of any freeware programs that can open up files designed for Access? Is there a way to convert a MDB file directly into a csv file, which could then be imported into MySQL? I'll probably eventually break it into sections. At the very least, I'll probably divide it between vertebrates (which I'll use the most) and invertebrates. Thanks. __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
Re: upgrading mysql on RH fedora core 3
On Tue, 29 Mar 2005 19:28:56 -0800, bruce [EMAIL PROTECTED] wrote: we're trying to install mysql/mysql-server (4.1.10a-1.i386) and are running into some serious problems. we had mysql/server (3.23.52-3.i386) running, but needed to go to the higher version... I was about to attempt the same thing. My thinking was to grab the mysql src.rpm from Fedora Core 4 test 1 and rebuild it on FC3: rpmbuild --rebuild mysql...src.rpm Please try that and see how it goes. also, this has to be running with apache/php/perl/etc... My feeling is that apache does not need to be rebuilt. PHP certainly does, from src.rpm, after the new mysql is installed (including mysql-devel). I am not sure whether it would just work to grab the PHP src.rpm from FC3 updates and rebuild it on top of the new mysql, or get the PHP src.rpm from FC4-test and rebuild. Perl is in the same situation, but it's probably even more complex. If i were you, i would probably post on the fedora-test mailing list and ask the same question. Anyway, good luck and let us know how it goes. -- Florin Andrei http://florin.myip.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrading mysql on RH fedora core 3
bruce wrote: hi... we're trying to install mysql/mysql-server (4.1.10a-1.i386) and are running into some serious problems. we had mysql/server (3.23.52-3.i386) running, but needed to go to the higher version... can someone tell us how/what we need to do to get this working correctly. actually, if anybody's managed to do this, can you tell us exactly what rpm packages you used? as you know, dealing with the rpms gets into dependency hell, which we believe has a lot to do with our issues... if you managed to get this version of mysql running on FC3, and you built it from source, can you provide directions/pointers on what you did, where you placed the resulting libs/etc... also, this has to be running with apache/php/perl/etc... thanks bruce I would strongly suggest backing up your databases first :) I would also ( if possible ) do the import and testing on another PC, while the production server continues. You may have to recompile php and Perl's DBI drivers, or you *may* be able to find some RPMs that will just 'drop in', but I don't use rpms so I couldn't say. The DBI drivers aren't marked as stable yet on my distro ( Gentoo ), so be careful there. If you use ODBC, you will have to upgrade to the latest version of MyODBC - which has some bugs so scan the MyODBC mailing list first. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: upgrading mysql on RH fedora core 3
On Tuesday, March 29, 2005 21:29, bruce wrote: hi... we're trying to install mysql/mysql-server (4.1.10a-1.i386) and are running into some serious problems. we had mysql/server (3.23.52-3.i386) running, but needed to go to the higher version... can someone tell us how/what we need to do to get this working correctly. actually, if anybody's managed to do this, can you tell us exactly what rpm packages you used? as you know, dealing with the rpms gets into dependency hell, which we believe has a lot to do with our issues... if you managed to get this version of mysql running on FC3, and you built it from source, can you provide directions/pointers on what you did, where you placed the resulting libs/etc... also, this has to be running with apache/php/perl/etc... thanks bruce I use mysql primarily on RHEL3, but I just upgraded a FC2 box just to try it. I was able to upgrade it using the rpm's from dev.mysql.com. I did notice one thing. The rpm install didn't seem to kill the old mysqld properly, so I got an access denied right after the install. I ran the following as root: #killall mysqld #service mysql start After that I was then able to connect just fine. As far as perl, php, and apache, everything will continue to work without modification if you continue to use the old password hashes. You can force this. Refer to http://dev.mysql.com/doc/mysql/en/old-client.html, specifically the old-password option. To make perl work with the new passwords, all you need to do is build DBD::mysql from source. To do this, uninstall the dbd-mysql rpm if you have it installed. I can't remember the exact name because I don't use it. Then as root run: #perl -e shell -MCPAN cpaninstall DBD::mysql Regards, -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need Help with 813-MDB File
Thanks for both your tips. I discovered by chance that Navicat (which I have) will do the conversion - very easily. Whether or not it will be a success is hard to say; it's loaded nearly 3 million rows so far, with over 8,000 errors recorded. But I'm going to download DB Tools, as I have frequent need for data conversion tools. Thanks. --- J.R. Bullington [EMAIL PROTECTED] wrote: DB Tools software will convert the file for you. You can download it at http://dbtools.com.br/EN/index.php. All you have to do is download and install the FreeWare version and then use the TOOLS DAO Import Wizard. J.R. -Original Message- From: David Blomstrom [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 29, 2005 11:29 PM To: mysql@lists.mysql.com Subject: Need Help with 813-MDB File I acquired a CD-ROM that lists many thousands of animal species. The main file is a 813-MB MDB file. I'm not sure if it's a spreadsheet or database, but it's apparently designed to work with Microsoft Access, which I THINK is a spreadsheet. (I don't have it.) I have just enough memory to open the file in WordPad, but it doesn't do any good because much of the data consists of unintelligible characters. Anyway, I need to figure out a way to import this monster into MySQL. Are you aware of any freeware programs that can open up files designed for Access? Is there a way to convert a MDB file directly into a csv file, which could then be imported into MySQL? I'll probably eventually break it into sections. At the very least, I'll probably divide it between vertebrates (which I'll use the most) and invertebrates. Thanks. __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ 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: Regarding NULL and '' (null string) treatment in MYSQL
Narasimha,
In programming languages, a 'null string' is empty, but in ANSI SQL,
NULL means unknown, _not_ empty, so
in ANSI SQL, NULLs are never equal to anything, not even themselves: the
expressions NULL=NULL, NULLNULL and NULL='' all evaluate to NULL.
Microsoft SQL has an 'ansi_nulls' setting which turns off this feature.
With ansi_nulls turned off, NULL works as you want it to (thus breaking
a lot of traditional SQL code). Perhaps Oracle also has such a setting
too. MySQL doesn't.
Referential integrity (RI) is meant to work oppositely to your
description: absent a matching key value in the parent table, a child
row cannot be inserted. What you describe looks like what's often called
the 'zeroth row' workaround: create a parent row with an empty key
value, then add matching child rows. It wrecks RI. If you absolutely
must add child rows before adding the parent row, use empty values, not
NULLs, but most DBAs would insist that you to revise the design such
that empty parent key values are not permitted.
Peter Brawley
http://www.artfulsoftware.com
-
[EMAIL PROTECTED] wrote:
Hi,
MySQL treats NULL, '' (empty string) as different.
I mean when I select from/insert into a table, its behaviour is
different.
Select * from table1 where name='';
is different from
Select * from table1 where name=NULL;
Similarly
Insert into table1(name) values('') is different from
Insert into table1(name) values(NULL)
Please suggest me here, how to overcome this '' (null string)
problem.
Scenario:
I have two tables, one is parent and one is child.
Child is referencing 3 fields in parent table.
Parent table records are empty. Now I am trying to insert into the child
table with '' (null string) values into these 3 fields (which are
referencing to the parent table). Actually insert into the child table
in this scenario should be successful, but I am not able to make it
success because of the problem described above ( Treatment of NULL, ''
are different).
Note: If the parent is not having any records, we can insert into the
child. BCS, in this case referential integrity won't work.
Ps: In oracle, it is not the case it treats NULL,'' as same.
Please suggest me how to proceed here.
Thanks,
Narasimha
Confidentiality Notice
The information contained in this electronic message and any attachments to
this message are intended
for the exclusive use of the addressee(s) and may contain confidential or
privileged information. If
you are not the intended recipient, please notify the sender at Wipro or [EMAIL
PROTECTED] immediately
and destroy all copies of this message and any attachments.
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.8.4 - Release Date: 3/27/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Help with 813-MDB File
David Blomstrom wrote: Thanks for both your tips. I discovered by chance that Navicat (which I have) will do the conversion - very easily. Whether or not it will be a success is hard to say; it's loaded nearly 3 million rows so far, with over 8,000 errors recorded. 3 million records in an Access database! The hairs on the back of my neck are still standing on end :) Good plan on upgrading to MySQL. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrading mysql on RH fedora core 3
On Tue, 29 Mar 2005 20:39:54 -0800, Florin Andrei [EMAIL PROTECTED] wrote: On Tue, 29 Mar 2005 19:28:56 -0800, bruce [EMAIL PROTECTED] wrote: we're trying to install mysql/mysql-server (4.1.10a-1.i386) and are running into some serious problems. we had mysql/server (3.23.52-3.i386) running, but needed to go to the higher version... I was about to attempt the same thing. My thinking was to grab the mysql src.rpm from Fedora Core 4 test 1 and rebuild it on FC3: rpmbuild --rebuild mysql...src.rpm Hey, whaddayaknow, it worked! :-) I just rebuilt the FC4t1 mysql src.rpm on FC3. It's just that i cannot try it, not today. I'll see if i can play with it tomorrow. Ideally, i'd like to get a spare system, nuke all it's content, do a fresh minimal install of FC3 (unselect all package categories except development), apply all updates (yum update), rebuild mysql-4, install it, then play with Perl and PHP and see if they need to be rebuilt as well (hopefully not). -- Florin Andrei http://florin.myip.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
The best way to transfer data to another server
Hello list, I have two MySQL 4.1 servers, one local and one remote. I need to transfer database from one server to another. What actually is the best way of handling this task? Are there any standard MySQL tools available for doing that (I mean MySQL Administrator/Query Browser etc.) Best regards, Denis Gerasimov Outsourcing Services Manager, VEKOS, Ltd. www.vekos.ru -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speed of 3.23 vs 4.1 for logging server
The RPM's from mysql.com should work fine on FC3. The source RPMs should build as well. I would say it shouldn't take that much effort. I don't know about performance issues, but I always figured that you can tune InnoDB with a bit more control than myisam. For inserts, you could probably have a larger memory pool and a longer time between changelog commits, but you should probably benchmark. You'd want to use the newer versions for that, I'd assume. Plus, with MyISAM, your odd SELECT queries will lock the tables, preventing inserts. If your queries run for more than 15 seconds, that may affect a logging server.. just some thoughts.. On Tue, 29 Mar 2005 11:48:56 -0800, Florin Andrei [EMAIL PROTECTED] wrote: Fedora Core 3, which is the Linux distribution that i'm using, is still shipping with 3.23.58 The beta (test) version of Fedora 4 has mysql-4.1.10. However, FC4 is scheduled to go live on June 6th. But i need a MySQL server now. This server will be mostly used for logging (think: syslog logging to SQL), so most of the time will just receive INSERTs on a permanent basis from a couple of sources, to a few tables (just a handful, all of them in two databases) that will be rotated periodically (all tables will be append-only - when they're too big, they're just rotated away and the very old ones are deleted when disk usage hits a threshold). Every now and then, a user or two will perform searches through the logs. Is there a big performance difference between 3.23 and 4.1 in such a case? I prefer to just use whatever's offered in the current distribution because i don't have much time to spend tweaking the system (upgrade MySQL, recompile PHP, verify SELinux policies, etc.), but if there is a really big performance difference, i may do the effort to upgrade MySQL to the one offered in FC4-test. -- Florin Andrei http://florin.myip.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
power loss scenario
Again the logging server i mentioned before: it's like syslog logging to a DB, lots of INSERTs, perhaps a few SELECTs every now and then, the tables are append-only and are rotated about once a day. For reasons that i am not going to discuss here, the machine has no uninterruptible power supply. Therefore, if the power goes down, bad things might happen to the database. Also, i don't have money for funky solutions such as solid-state disks. In fact, the disks will most likely be IDE (not even SCSI). What are the techniques that work best in such a situation to increase the chances for the database to survive a crash in a consistent state? Loosing a few recent INSERTs is not a problem (since some data will not be logged anyway while the server is down), but the DB in an inconsistent state is a big problem (the system has to boot up again unattended). I do not want to do such extreme things like turning off the write cache on the disk, because that would probably kill the performance. But how about Ext3 with data=journal? Using InnoDB would be better than MyISAM? How about raw partitions? Any other tips? -- Florin Andrei http://florin.myip.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
