SQL problem
Hello all, Please help a newbe ;-) Have a little problem with to make a sql-query as I want to have it... The problem is I need to check in 3 tables and count out and get it presentated per n_id like n_id counted 01 5 02 10 03 2 My tables look as follows... In sub: id n_id In us: id email In sub_del: n_id id My problem is that I want to be able to get out the result per sub.n_id. Please help! /Joppe -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: set auto_increment does not work?
Hi! For InnoDB, CREATE TABLE ... AUTO_INCREMENT=... works starting from 5.0.3. Until then, you have to use the 'insert + delete a dummy row' method to init the counter. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL Network from http://www.mysql.com/network/ - Original Message - From: Rich Lafferty [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, February 23, 2005 5:19 AM Subject: Re: set auto_increment does not work? On Tue, Feb 22, 2005 at 03:46:34PM -0600, Scott Purcell [EMAIL PROTECTED] wrote: Hello, ALTER TABLE tbl_name AUTO_INCREMENT = 1000 will start your records at 1000 But it does not work for myself. How can I get the auto_increment to begin at a set starting point? I know I could insert a bogus record, but it just seems sloppy. Does it work if you use MyISAM tables? Just a hunch, based on something I ran into in 2002: http://www.lafferty.ca/stuff/misc/innodb-autoincrement-bug but never had time/reason to investigate further (MyISAM proved sufficient in that application). If you can duplicate what I saw then it might be worth following up with MySQL AB after testing in the latest MySQL release. -Rich -- Rich Lafferty --+--- Ottawa, Ontario, Canada| Save the Pacific Northwest Tree Octopus! http://www.lafferty.ca/|http://zapatopi.net/treeoctopus.html [EMAIL PROTECTED] ---+--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best way to order mysql results with limit
Hey list I have a table like this: Act_name Act_type_id Act_date Heyhey 1 22-06-05 Aloha 2 22-06-05 Tralala 2 22-06-05 Wuhu 1 22-06-05 Hehe 3 22-06-05 Olalal 3 22-06-05 Pompom 1 22-06-05 Wuhu 2 22-06-05 Now i want to do a select which returns the two most near (in the future) activities of each act_type_id Is there a way to do this cleanly or should I better do a full select and only get what I want with php? Thx in advance. Btw Im using MySQL 4.0.1-alpha-nt Reinhart Viane D-studio [EMAIL PROTECTED] Graaf van Egmontstraat 15/3 2800 Mechelen Tel: +32 (0)15 448 901 STRICTLY PERSONAL AND CONFIDENTIAL This message may contain confidential and proprietary material for the sole use of the intended recipient. Any review or distribution by others is strictly prohibited. If you are not the intended recipient please contact the sender and delete all copies. No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.4.0 - Release Date: 22/02/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
where mysql_config?
Hi listers i am fiddling around with the problem of not being able to access mysql 4.1.* from php with new users created in mysql 4.1. no official statement can be found nor from mysql nor from php concerning this problem. both just avoid to talk about it. the only hint i found is create php with the mysqli api. but to create this type of api i need a program called mysql_config, which is said to come with any mysql distro later than 4.1. this is not true. it is not included in mysql 4.1.9 which i am currently using. in the news, everyone was talking about mysql_config, nowone knew where to find it. so, where is it? suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Effect of VARCHAR length?
Hello. Really, a varchar(255) column gives you a big flexibility and may save a lot of space. Usually such records uses as many characters as needed plus one byte for it's length. When you use MyISAM tables you can get some performance disadvantages and use more space when your table is fragmented (you can solve this using OPTIMIZE TABLE). But for InnoDB tables it is recommended to use varchar columns, however this storage engine is not so fast as MyISAM. If you need a column for which trailing spaces are not removed, consider using a BLOB or TEXT type. Yves Goergen [EMAIL PROTECTED] wrote: Hi list, I've just been wondering if the length parameter of a VARCHAR column has any effect on storage efficiency or space requirements. Afaik, VARCHAR columns only store the amount of data actually written into them and require no significantly more memory. So to be especially flexible with a particular table column, could I just define it VARCHAR(255) and face no further disadvantage of it? Thanks for the info... -- 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: Unable to install 4.1 on Fedora Core 2
Hello.
Package Not Found dialog box saying Unlocatable Package
libmysqlclient.so.10 Required
Install MySQL-shared-compat-4.1.10-0.i386.rpm. If error remains use --force
--nodeps options for the rpm command.
[snip]
I'm trying to install MySQL-server-4.1.10-0.i386.rpm on Fedora Core 2 but I
keep getting a
Package Not Found dialog box saying Unlocatable Package
libmysqlclient.so.10 Required
By ('perl-DBD-MySQL', '2.9003', '4').
I've installed the client, devel and shared packages but still get the same
error.
What do I need to get this installed?
[snip]
Billy Yard [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: connect /sellect to 2 dbs
Hello. Can I select from one db and insert into another? Yes. For example: insert into test2.t2 select a from test.t2; We insert values from table t2 of the database test into table t2 of the test2 database. See: http://dev.mysql.com/doc/mysql/en/identifier-qualifiers.html also...Can I join to a remote db? No. Vic [EMAIL PROTECTED] wrote: Can I select from one db and insert into another? also...Can I join to a remote db? tia, .V -- 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: Merging / Moving InnoDB Databases
Hi Heikki, Thanks for the reply. If you were charged with moving an InnoDB database with 40,000,000 records (based on an old version of one of our databases, has since grown) within a standard maintenance slot (say up to 4 hrs) what would you do? Are we looking at requiring a separate hardware target? Last time we attempted an import it took 48hrs on our spare production servers (dual xeons with gigs of ram otherwise idle). Ordinarily I would be looking to use InnoDB Hot backup to copy the binary files across, you say this is not possible when the target host already runs InnoDB? Thanks, James Heikki Tuuri wrote: James, unfortunately, you cannot move InnoDB tables in that way, like you would be able to move MyISAM tables just by copying the .MYI, .MYD, and .frm files over to the other database installation. In the future, we may add a feature that allows one to copy 'clean' .ibd files across installations. But presently, you must dump the tables and import them to the other installation. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL Network from http://www.mysql.com/network/ - Original Message - From: James Green [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, February 21, 2005 1:16 PM Subject: Merging / Moving InnoDB Databases Hi, Server A - Multiple InnoDB databases Server B - Replication of Server A Server C - Other InnoDB databases I need to be able to move Server C's databases onto Server A and continue to replicate (with new databases) to Server B. Can I: 1. Take down Server C, use ibbackup to back up the innodb data files and frm data 2. Taken down Servers A B 3. Put on Servers A B the data files from Server C, but under different InnoDB names (ibdata3 for example) 4. Start Servers A B and watch all the databases fly happily? Is this the correct procedure? Thanks, -- James Green -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- James Green Systems Administrator, StealthNET Ltd, www.stealthnet.co.uk Tel: 0870 800 1777 Intl: +44 1493 660066 Fax: 0870 135 1069 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: referencial integrity problem
Hello. Use InnoDB tables instead of MyISAM. See: http://dev.mysql.com/doc/mysql/en/ansi-diff-foreign-keys.html Philipp Snizek [EMAIL PROTECTED] wrote: Hi I run a Postfix MTA attached to a mysql DB with various domains on it. A domain consists of email addresses. When I want to delete the domain the referenced email addresses should be deleted, too. But that doesn't work and I don't know why. here are the two tables domains and users: CREATE TABLE domains ( ID_DOMAINS int(11) auto_increment, active int(1) not null, domain varchar(50) NOT NULL, PRIMARY KEY (ID_DOMAINS) ) TYPE=3DMyISAM; create table users ( email varchar (80) primary key unique not null, belongs_to integer not null, foreign key (belongs_to) references domains on delete cascade ); if I use the delete command like delete from domains where id_domains=3D'1' the dataset that belongs to id 1 in domains is deleted while the email addresses belonging to this domain are left untouched. What am I missing? thanks Philipp -- 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: referencial integrity problem
Hello. What output does the following statement produce: show variables like 'have_innodb'; Philipp Snizek [EMAIL PROTECTED] wrote: =20 You need to make *both* of your table definitions include=20 Type=3DInnoDB; *then*, the cascading delete should work fine. This is what I have done upon Keith's suggestion. I have changed all my tables to Type=3Dinnodb. Still nothing. Maybe mysqlcc or mysql administrator deliver wrong information? Deleteting the record in Table domains leaves the record in Table users referencing domains untouched.=20 Or is my sql script bad? I'm already spending hours on that. CREATE TABLE domains ( ID_DOMAINS int(11) auto_increment, active int(1) not null, domain varchar(50) NOT NULL, PRIMARY KEY (ID_DOMAINS) ) type=3Dinnodb; Either this 'users' Table: create table users ( email varchar (80) primary key unique not null, belongs_to integer not null references domains on delete cascade ) type=3Dinnodb; =20 or this 'users' Table: create table users ( email varchar (80) primary key unique not null, belongs_to integer not null, foreign key (belongs_to) references domains on delete cascade ) type=3Dinnodb; Philipp=20 =20 Rhino =20 - Original Message - From: Philipp Snizek [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Mysql List (E-mail)=20 mysql@lists.mysql.com Sent: Tuesday, February 22, 2005 7:49 AM Subject: RE: referencial integrity problem =20 =20 =20 Foreign keys are only supported within InnoDB tables (on both sides).. =20 so using a table 'users' like =20 create table users ( email varchar (80) primary key unique not null, belongs_to integer not null references domains on delete cascade ); =20 without foreign keys could help? I couldn't make it work that way either. =20 Philipp =20 Hi I run a Postfix MTA attached to a mysql DB with various domains on it. A domain consists of email addresses. When I want to delete the domain the referenced email addresses should be deleted, too. But that doesn't work and I don't know why. here are the two tables domains and users: CREATE TABLE domains ( ID_DOMAINS int(11) auto_increment, active int(1) not null, domain varchar(50) NOT NULL, PRIMARY KEY (ID_DOMAINS) ) TYPE=3DMyISAM; create table users ( email varchar (80) primary key unique not null, belongs_to integer not null, foreign key (belongs_to) references domains on delete cascade ); if I use the delete command like delete from domains where id_domains=3D'1' the dataset that belongs to id 1 in domains is deleted while the email addresses belonging to this domain are left untouched. What am I missing? thanks Philipp --=20 MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --=20 No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.3.0 - Release Date: 21/02/2005 --=20 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.3.0 - Release Date: 21/02/2005 --=20 MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] =20 --=20 MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: =20 http://lists.mysql.com/[EMAIL PROTECTED] =20 =20 =20 --=20 No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.2.0 - Release Date: 21/02/2005 =20 =20 =20 =20 --=20 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.2.0 - Release Date: 21/02/2005 =20 =20 -- 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: Upgrading mySql from 3.23 to 4.1.10
Hello. Install MySQL-client-4.1.10-0.i386.rpm. Troy Richard [EMAIL PROTECTED] wrote: I have upgraded from mysql 3.23 to 4.1.10 everything seems to be working. I'm trying to run the mysql_fix_privilege_tables script to update the privileges and I'm getting the following error: /usr/bin/mysql_fix_privilege_tables: line 185: /usr/bin/mysql: No such file or directory /usr/bin/mysql existed before I upgraded. I'm not sure what happen to it. I just downloaded the rpms and ran the upgraded. Files downloaded: MySQL-server-4.1.10-0.i386.rpm MySQL-shared-compat-4.1.10-0.i386.rpm Command: rpm -Uvh MySQL* Any help would be a big hand, Thanks Troy -- 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: missing header file?
Hello. You should run configure in the source directory, which will create a Makefile in the include/ directory. Then cd into it and run: make my_config.h [snip] I'm trying to modify and recompile some UDFs for MySQL 4.1. I'm using the header files from the version 4.1.10 Linux source archive and am getting the following error: home/sskarupo/mysql-4.1.10/include/my_global.h:70: my_config.h: No such file or directory my_config.h isn't there. It is included in the Windows source archive, though. Can anyone explain this? Thanks in advance, SergeiSergei Skarupo [EMAIL PROTECTED] wrote: [snip] -- 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: SQL problem
Sorry, I have missed information in my earlir question, that I have added below. Joppe A wrote: Hello all, Please help a newbie ;-) Have a little problem with to make a sql-query as I want to have it... The problem is I need to check in 3 tables and count out and get it presentated per n_id like n_id counted 015 0210 032 My tables look as follows... In sub: id n_id In us: id email In sub_del: n_id id My problem is that I want to be able to get out the result per sub.n_id and that I have to count it together, and se the result per n_id. I have since earlier a query that check the total sum of it, but that is not enough... SELECT (SELECT count(sub_id) from sub) - (SELECT count(us.id) FROM us LEFT JOIN sub ON sub.id = us.id)+(SELECT count(*) FROM sub_del); As you can see I have to count all id in my sub-table and then minus the id from US-table and then add what I have in sub_del-table, and listed pro n-id instead of only get the total sum. Please help! /Joppe -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with mysqlhotcopy
Hello. e:\mysql\scripts\mysqlhotcopy.pl -u root -p password db_name /backup_test I think, the problem is in the missing period in /backup_test, it should be ./backup_test. But generally Windows isn't listed in operating systems list on which mysqlhotcopy works. Maybe cygwin will help. MightyData [EMAIL PROTECTED] wrote: I am running MySQL and Perl on Windows 2003. I am trying to use the mysqlhotocpy script for backup. Mysqlhotcopy will execute but returns an error. Command: e:\mysql\scripts\mysqlhotcopy.pl -u root -p password db_name /backup_test Error: The system cannot find the path specified. Executing command failed (256) Trying backtick execution... ...list_of_files... Failed (32768) while copying files - Kirk Bowman Phone: 972-390-8600 MightyData, LLC http://www.mightydata.com FileMaker 7 Certified Developer FileMaker Authorized Trainer Check out our FileMaker 7 training classes! - -- 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] Got error 127 when reading table
Hello. I went so far as to delete entire tables and recreate them. Can you create a new table (with name which you didn't use before) and check if the errors remain on the new table? Does REPAIR TABLE solve the problem? Also ensure that there are no another MySQL instances running? Or may be some other software has access to your files (antivirus)? Dan Tappin [EMAIL PROTECTED] wrote: I had a previously stable 4.1.8-standard MYSQL install on OS X 10.3.x Server using the binaries supplied by MySQL. Recently I have had multiple corruption issues with a database on multiple tables. I keep getting [ERROR] Got error 127 when reading table when trying to write to an effected table. I went so far as to delete entire tables and recreate them. I could reproduce the error from my default PHP and CLI attempts to write to the tables. The only thing that I recently changed on the server was the location of the data directory. The Mac OS X install / upgrade places the data in '/usr/local/mysql/data' where mysql is a symbolic link to the most recent version installed in /usr/local/. I got tired of manually moving the data directory each time I upgraded so I created a /etc/my.cnf file and specified the new data dir location in /var/mysql/data/. I moved the old data for the last time checked the permissions and ownership and restarted mysql. When I created the new my.cnf file I used the my-medium.cnf default example as detailed below: [client] port= 3306 socket = /tmp/mysql.sock [mysqld] port= 3306 socket = /tmp/mysql.sock datadir = /var/mysql/data/ #skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout I have changed my my.cnf file to simply: [mysqld] datadir = /var/mysql/data/ to see is one of these new parameters caused the problem Because I had no my.cnf I suspect that this is the problem. I tried some Google and list searches with no meaningful results. Any tips / pointers / suggestions would be great. Dan T -- 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 to mysql in linux
Hello. It is a frequently asked question. Search in the list. For example see thread: http://lists.mysql.com/mysql/160391 Sebastian Luque [EMAIL PROTECTED] wrote: Dear List members, Having relatively recently moved to GNU/Debian Linux from Windows, I'd like to move my MS Access databases to MySQL. I'm using the latest unstable MySQL version and am all setup with user, passwords, and privileges, so can create and modify databases in MySQL. Searching for tools to migrate my MS Access databases, I've come across mdbtools, with which I can see and export the tables to csv, but the process of getting them into MySQL is tedious and cumbersome. The script mdb-schema in mdbtools can export the tables to other DBMS formats, excluding MySQL unfortunately. Can somebody please suggest other, more convenient, tools (free preferably) to make this transition? Best wishes, -- 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: mysqlbug
Hello. See: http://dev.mysql.com/doc/mysql/en/can-not-connect-to-server.html Jerome Campbell [EMAIL PROTECTED] wrote: Hello, I've been using MySQL for a while on my computer to host a couple of things and since today I've got this error: 2003 - Can't connect to MySQL server on 'localhost' (10061) I have no clue why MySQL isn't even connecting any more, if you have any information why, please email me. Thanks. - Jerome -- 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: Which version for fastest simple inserts, selects etc
Hi Pete! Am Mi, den 23.02.2005 schrieb Pete Lancashire um 4:43: If I needed a brute force DB on an x86 platform would using Version 3 vs 4 buy me any performance today ? IMO, you should not start with version 3 any more - it is too old. Probably, you should not even start with 4.0 but use 4.1. If you like experimenting, try 5.0 - if you want production quality, take one of 4.0 or 4.1 (preferred). 90% inserts, 5% simple selects, 5% other. My position is not based on features but on development status. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: referencial integrity problem
Hi Hello. What output does the following statement produce: show variables like 'have_innodb'; mysql show variables like 'have_innodb'; +---+---+ | Variable_name | Value | +---+---+ | have_innodb | YES | +---+---+ 1 row in set (0.00 sec) Philipp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where mysql_config?
Hello. i am fiddling around with the problem of not being able to access mysql 4.1.* from php with new users created in mysql 4.1. What error does the php produce? this type of api i need a program called mysql_config, which is said to Usually it is included with the official binaries. If you use rpms, install a development package. schlubediwup [EMAIL PROTECTED] wrote: Hi listers i am fiddling around with the problem of not being able to access mysql 4.1.* from php with new users created in mysql 4.1. no official statement can be found nor from mysql nor from php concerning this problem. both just avoid to talk about it. the only hint i found is create php with the mysqli api. but to create this type of api i need a program called mysql_config, which is said to come with any mysql distro later than 4.1. this is not true. it is not included in mysql 4.1.9 which i am currently using. in the news, everyone was talking about mysql_config, nowone knew where to find it. so, where is it? suomi -- 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: Which version for fastest simple inserts, selects etc
Hello. Use the latest release (4.1.10 now). You'll get a huge speed up using bulk inserts. Pete Lancashire [EMAIL PROTECTED] wrote: If I needed a brute force DB on an x86 platform would using Version 3 vs 4 buy me any performance today ? 90% inserts, 5% simple selects, 5% other. Sorry if a lame question -pete -- 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: Strange Issues
Hello. If important I am version running 4.1 on Win XP SP2 IIS. Use the latest release (4.1.10 now) and hexademical values. See: http://dev.mysql.com/doc/mysql/en/hexadecimal-values.html Rob Cochrane [EMAIL PROTECTED] wrote: I am new to this list and to MySQL returning to Data Bases after many years away. I am developing multilingual web sites with all the info extracted from databases. In using MySQLCC/SQLyog to stack up some static base data into MyISAM tables the field type is text I discovered that some of the data was truncated immediately prior to a 'n [single quote n] the balance of the data is lost. removing the quote solves the problem. The data is multilingual text requiring a number of non standard codings. The problem also appears with the character e with ^ above. [alt 0234] ? and also ? [alt 0235]. I have the coding set to Unicode and as far as I can see have set up the system correctly. If important I am version running 4.1 on Win XP SP2 IIS. I have managed a temporary workaround but it required a significant amount of additional work. Many thanks in advance Rob -- 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]
Lost InnoDB Data
Hello all. I have a problem which is very serious for us. After shutting down our mysql database and starting it up again, ALL our innodb-tables are just as they were several weeks ago (january 31). All the changes, additions, deletions that have happened since are gone. The same thing happens if we go back to backups which are not entirerly new but later than jan 31. I have no idea what's causing this, and I have absolutely no clue what to do about it. Any suggestions? We're running MySQL 4.0.21 on Red Hat. Any help with this would be greatly appreciated! Best regards, -- Henrik Skotth
Re: Avoiding filesort #2
Thanks Mike for your feedback. Unfortunately the HANDLER statement has the same limitation, i.e. it doesn't allow you to specify range criteria for index key parts. You have to specify constants in the index_name (...) specifier. --- mos [EMAIL PROTECTED] wrote: At 08:47 PM 2/22/2005, you wrote: Even if I used ColA in the query, it still uses filesort if any keypart uses something other than an equal operator, like a range, IN operator, IS NOT NULL, IfNull(), etc. Rearranging the composite index to make the sort column the first one won't help because: 1) It's not part of the WHERE clause, so MySQL will have to scan the whole index to find matches on the other key parts. 2) The query optimizer won't use it (reporting NULL in the possible keys), even if I used FORCE INDEX (). It'd rather use a singlular index created on ColD instead. In addition, if I took ColD and put it in another table, it won't use it for sorting unless I'm sorting on the join column AND ColD. For example, this query will use filesort instead of the order of an index created on Y (ColA, ColD): SELECT X.* FROM X JOIN Y ON X.ColA = Y.ColB ORDER BY Y.ColD It will only use the index order if I included both keys of the index in the query: SELECT X.* FROM X JOIN Y ON X.ColA = Y.ColB ORDER BY Y.ColA, Y.ColD But this is obviously not what I want. This is a huge problem with multi-million-row tables because I'm gonna have to live with either table scans or quick sorts of large memory buffers, and both of which are evil and performance/scalability killers. How did you guys solve this sorting problem? It's a severe limitation of MySQL. I know that 5.x could use more than one index per table and could solve this problem, but it's still in Alpha stage, too risky for production use. Try looking at Handler http://dev.mysql.com/doc/mysql/en/handler.html which creates a cursor into a MISAM/Innodb table. It will traverse the table using the index without sorting and without creating huge buffers. It is a bit difficult to understand at first but it is fast. Remember to fetch 100 to 1000 rows at a time. You don't want to fetch just 1 row from each call. Mike --- Mike OK [EMAIL PROTECTED] wrote: Hi First, I am pretty sure that what the manual says is that MySQL only USES one index per request, not one index per table. I would try adding an index that starts with ColC (and maybe only ColC). Your index starts with ColA but you do not use it in your WHERE portion of the statement. This could confuse the index selection process and have MySQL decide to use no index in some situations. Mike - Original Message - From: Homam S.A. [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: February 22, 2005 8:12 PM Subject: Avoiding filesort #2 Actually with the query below it does avoid filesort, but once I use anything other than the equal operator (e.g. ColC 5), it reverts back to filesort. Any thoughts? --- Homam S.A. [EMAIL PROTECTED] wrote: I read How My SQL Optimizes Order By (http://dev.mysql.com/doc/mysql/en/order-by-optimization.html), and I'm aware of its severe limitation due to the one-index-per-table rule. However, even when I follow all the roles, I'm still getting filesort instead of using the index order. So I created an index as follows: CREATE INDEX IX_MyTable on MyTable (ColA, ColB, ColC, ColD DESC) Then I run the following query: SELECT ColA FROM MyTABLE WHERE ColB = 'CONSTANT' AND ColB = 1 AND ColC = 'C' ORDER BY ColdD DESC This is obviously a covered query by the index IX_MyTable. MySQL isn't supposed to touch the table. But MySQL insists on filesort! (I can this that int he Extra column of the EXPLAIN command). Why? I'm abiding by all the rules that should let MySQL use the index order instead of perform an expensive quick sort on a large memory buffer. I appreciate your feedback! __ Do you Yahoo!? Yahoo! Mail - Find what you need with new enhanced search. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail - Easier than ever with enhanced search. Learn more. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
Authorizing Problem in MySQL 4 with Mandrake 10
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hash: SHA1 Hi All, I installed a fresh copy of Mandrake Linux 10.0 with MySQL. I tried to login to MySQL server ,But it says that authorization is failed. My hostname is localhost and user is root.I entered the root password. Here is the log :- [EMAIL PROTECTED] html]# mysql -h localhost -u root -p Enter password: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Please help me to solve this problem. Best Regards, Prabath. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFCHRXVIlUQPx/AjvYRAo2fAKCJ+DWXNgSbfW3RySeJGADUSytjWACgxHYt SHHwE2P22LankU/ovx/s28M= =k/T0 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Authorizing Problem in MySQL 4 with Mandrake 10
Prabath Ranasinghe (by way of Prabath Ranasinghe [EMAIL PROTECTED]) ha scritto: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hash: SHA1 Hi All, I installed a fresh copy of Mandrake Linux 10.0 with MySQL. I tried to login to MySQL server ,But it says that authorization is failed. My hostname is localhost and user is root.I entered the root password. Here is the log :- [EMAIL PROTECTED] html]# mysql -h localhost -u root -p Enter password: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Please help me to solve this problem. Best Regards, Prabath. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFCHRXVIlUQPx/AjvYRAo2fAKCJ+DWXNgSbfW3RySeJGADUSytjWACgxHYt SHHwE2P22LankU/ovx/s28M= =k/T0 -END PGP SIGNATURE- If you still have NO password trying to use it will give you an error ;) try # mysql -h localhost -u root regards Francesco Riosa -- No problem is so formidable that you can't walk away from it. ~ Charles M. Schulz But sometimes run fast is better ~ Francesco R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connect to MYSQL server from Wi-Fi enabled Windows CE device
Hi All, I am developing a embedded VB application running on a hp ipaq running Windows CE 2003. This app has to connect to a MYSQL DB over a wireless network. I have surfed the internet for hours with no luck. Can anyone please help me with this problem. Regards Hough -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UNIQUE Key Allowing Duplicate NULL Values
Is there any flag I can set on the column or key to not allow duplicate nulls? Tim Gustafson MEI Technology Consulting, Inc [EMAIL PROTECTED] (516) 379-0001 Office (516) 480-1870 Mobile/Emergencies (516) 908-4185 Fax http://www.meitech.com/ -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 22, 2005 3:13 PM To: Gustafson, Tim; mysql@lists.mysql.com Subject: Re: UNIQUE Key Allowing Duplicate NULL Values At 15:00 -0500 2/22/05, Gustafson, Tim wrote: Hi there! I have a table, defined as follows: CREATE TABLE `WebSiteDomainNames` ( `ID` int(10) unsigned NOT NULL auto_increment, `WebSite` int(10) unsigned NOT NULL default '0', `DomainName` int(10) unsigned NOT NULL default '0', `Alias` char(16) default NULL, PRIMARY KEY (`ID`), UNIQUE KEY `DomainName` (`DomainName`,`Alias`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1 The way I read this definition, it should be impossible for someone to put in two rows with the same DomainName and Alias, however, right now I have the following rows in the table: +-+-++---+ | ID | WebSite | DomainName | Alias | +-+-++---+ | 543 |1086 | 1334 | NULL | | 545 |1086 | 1334 | NULL | | 509 |1086 | 1334 | * | +-+-++---+ And I can insert even more NULL rows if I want to. Shouldn't the UNIQUE key prevent this from happening? Not for NULL values, no. See the description for UNIQUE indexes here: http://dev.mysql.com/doc/mysql/en/create-table.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com smime.p7s Description: S/MIME cryptographic signature
RE: UNIQUE Key Allowing Duplicate NULL Values
At 8:10 -0500 2/23/05, Gustafson, Tim wrote: Is there any flag I can set on the column or key to not allow duplicate nulls? Tim Gustafson MEI Technology Consulting, Inc [EMAIL PROTECTED] (516) 379-0001 Office (516) 480-1870 Mobile/Emergencies (516) 908-4185 Fax http://www.meitech.com/ No, you'd have to use a BDB table, because only BDB allows a single NULL per UNIQUE index. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 22, 2005 3:13 PM To: Gustafson, Tim; mysql@lists.mysql.com Subject: Re: UNIQUE Key Allowing Duplicate NULL Values At 15:00 -0500 2/22/05, Gustafson, Tim wrote: Hi there! I have a table, defined as follows: CREATE TABLE `WebSiteDomainNames` ( `ID` int(10) unsigned NOT NULL auto_increment, `WebSite` int(10) unsigned NOT NULL default '0', `DomainName` int(10) unsigned NOT NULL default '0', `Alias` char(16) default NULL, PRIMARY KEY (`ID`), UNIQUE KEY `DomainName` (`DomainName`,`Alias`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1 The way I read this definition, it should be impossible for someone to put in two rows with the same DomainName and Alias, however, right now I have the following rows in the table: +-+-++---+ | ID | WebSite | DomainName | Alias | +-+-++---+ | 543 |1086 | 1334 | NULL | | 545 |1086 | 1334 | NULL | | 509 |1086 | 1334 | * | +-+-++---+ And I can insert even more NULL rows if I want to. Shouldn't the UNIQUE key prevent this from happening? Not for NULL values, no. See the description for UNIQUE indexes here: http://dev.mysql.com/doc/mysql/en/create-table.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: referencial integrity problem
Hi thanks for your help and your answers, it works now. foreign key (ownerdomain) references domains(domain) on delete cascade ^^ was missing. Philipp -Original Message- From: Philipp Snizek [mailto:[EMAIL PROTECTED] Sent: Mittwoch, 23. Februar 2005 11:22 To: mysql@lists.mysql.com Subject: RE: referencial integrity problem Hi Hello. What output does the following statement produce: show variables like 'have_innodb'; mysql show variables like 'have_innodb'; +---+---+ | Variable_name | Value | +---+---+ | have_innodb | YES | +---+---+ 1 row in set (0.00 sec) Philipp -- 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: UNIQUE Key Allowing Duplicate NULL Values
Oh well, thanks anyhow. If I could put in a feature suggestion, it would be a flag that I could set to disallow duplicate nulls. :) Tim Gustafson MEI Technology Consulting, Inc [EMAIL PROTECTED] (516) 379-0001 Office (516) 480-1870 Mobile/Emergencies (516) 908-4185 Fax http://www.meitech.com/ -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 23, 2005 8:14 AM To: Gustafson, Tim; mysql@lists.mysql.com Subject: RE: UNIQUE Key Allowing Duplicate NULL Values At 8:10 -0500 2/23/05, Gustafson, Tim wrote: Is there any flag I can set on the column or key to not allow duplicate nulls? Tim Gustafson MEI Technology Consulting, Inc [EMAIL PROTECTED] (516) 379-0001 Office (516) 480-1870 Mobile/Emergencies (516) 908-4185 Fax http://www.meitech.com/ No, you'd have to use a BDB table, because only BDB allows a single NULL per UNIQUE index. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 22, 2005 3:13 PM To: Gustafson, Tim; mysql@lists.mysql.com Subject: Re: UNIQUE Key Allowing Duplicate NULL Values At 15:00 -0500 2/22/05, Gustafson, Tim wrote: Hi there! I have a table, defined as follows: CREATE TABLE `WebSiteDomainNames` ( `ID` int(10) unsigned NOT NULL auto_increment, `WebSite` int(10) unsigned NOT NULL default '0', `DomainName` int(10) unsigned NOT NULL default '0', `Alias` char(16) default NULL, PRIMARY KEY (`ID`), UNIQUE KEY `DomainName` (`DomainName`,`Alias`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1 The way I read this definition, it should be impossible for someone to put in two rows with the same DomainName and Alias, however, right now I have the following rows in the table: +-+-++---+ | ID | WebSite | DomainName | Alias | +-+-++---+ | 543 |1086 | 1334 | NULL | | 545 |1086 | 1334 | NULL | | 509 |1086 | 1334 | * | +-+-++---+ And I can insert even more NULL rows if I want to. Shouldn't the UNIQUE key prevent this from happening? Not for NULL values, no. See the description for UNIQUE indexes here: http://dev.mysql.com/doc/mysql/en/create-table.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com smime.p7s Description: S/MIME cryptographic signature
SELECT ERROR
I'm trying to compare 2 tables and keep getting an error. SELECT * from listings where listings.id = fake.id; The error is Error 1109: Unknown table 'fake' in where clause or Error 1109: Unknown table 'listings' in where clause depending on the table order at the end of the query. Both tables do exist and I can select any or all contents from either of them seperately just not using the command above that practically comes straight from the documentation. Any ideas? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Authorizing Problem in MySQL 4 with Mandrake 10
Be careful maybe you are mistaking here: the mysql root user as nothing to see with the mandrake root user. For exemple I lauch the mysql server as root from command line whereas I'm just a user on my desktop. So the mandrake root password and the mysql root password are also completly different. maybe this can be helpful: http://dev.mysql.com/doc/mysql/en/unix-post-installation.html From: Prabath Ranasinghe [EMAIL PROTECTED] (by way of Prabath Ranasinghe [EMAIL PROTECTED]) Reply-To: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Authorizing Problem in MySQL 4 with Mandrake 10 Date: Wed, 23 Feb 2005 18:46:29 -0500 -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hash: SHA1 Hi All, I installed a fresh copy of Mandrake Linux 10.0 with MySQL. I tried to login to MySQL server ,But it says that authorization is failed. My hostname is localhost and user is root.I entered the root password. Here is the log :- [EMAIL PROTECTED] html]# mysql -h localhost -u root -p Enter password: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Please help me to solve this problem. Best Regards, Prabath. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFCHRXVIlUQPx/AjvYRAo2fAKCJ+DWXNgSbfW3RySeJGADUSytjWACgxHYt SHHwE2P22LankU/ovx/s28M= =k/T0 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UNIQUE Key Allowing Duplicate NULL Values
Tim, Oh well, thanks anyhow. If I could put in a feature suggestion, it would be a flag that I could set to disallow duplicate nulls. :) What for? NULL is not equal to NULL. Period. If you don't want NULLs, make the column not null. The specification is correct. 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: SELECT ERROR
If you want to compare the 2 tables you have to join them: select * from listings, fake where listings.id=fake.id; If you do your query SELECT * from listings where listings.id = fake.id; it simply doesn't know where to get fake.id From: Ed Curtis [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: SELECT ERROR Date: Wed, 23 Feb 2005 08:02:21 -0500 (EST) I'm trying to compare 2 tables and keep getting an error. SELECT * from listings where listings.id = fake.id; The error is Error 1109: Unknown table 'fake' in where clause or Error 1109: Unknown table 'listings' in where clause depending on the table order at the end of the query. Both tables do exist and I can select any or all contents from either of them seperately just not using the command above that practically comes straight from the documentation. Any ideas? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT ERROR
Thanks, as soon as I seen it I slapped myself really hard :) On Wed, 23 Feb 2005, mel list_php wrote: If you want to compare the 2 tables you have to join them: select * from listings, fake where listings.id=fake.id; If you do your query SELECT * from listings where listings.id = fake.id; it simply doesn't know where to get fake.id From: Ed Curtis [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: SELECT ERROR Date: Wed, 23 Feb 2005 08:02:21 -0500 (EST) I'm trying to compare 2 tables and keep getting an error. SELECT * from listings where listings.id = fake.id; The error is Error 1109: Unknown table 'fake' in where clause or Error 1109: Unknown table 'listings' in where clause depending on the table order at the end of the query. Both tables do exist and I can select any or all contents from either of them seperately just not using the command above that practically comes straight from the documentation. Any ideas? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- 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: UNIQUE Key Allowing Duplicate NULL Values
Martijn, The problem is that I don't want more than one row in the table that has a null value in the column. As you've pointed out in your e-mail, there's a difference between NULL and BLANK. It's not that I don't want NULL values, it's that I don't want MORE THAN ONE. Tim Gustafson MEI Technology Consulting, Inc [EMAIL PROTECTED] (516) 379-0001 Office (516) 480-1870 Mobile/Emergencies (516) 908-4185 Fax http://www.meitech.com/ -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 23, 2005 8:37 AM To: Gustafson, Tim; Paul DuBois; mysql@lists.mysql.com Subject: Re: UNIQUE Key Allowing Duplicate NULL Values Tim, Oh well, thanks anyhow. If I could put in a feature suggestion, it would be a flag that I could set to disallow duplicate nulls. :) What for? NULL is not equal to NULL. Period. If you don't want NULLs, make the column not null. The specification is correct. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com smime.p7s Description: S/MIME cryptographic signature
list
Checking if this message gets through Reinhart Viane D-studio [EMAIL PROTECTED] Graaf van Egmontstraat 15/3 2800 Mechelen Tel: +32 (0)15 448 901 STRICTLY PERSONAL AND CONFIDENTIAL This message may contain confidential and proprietary material for the sole use of the intended recipient. Any review or distribution by others is strictly prohibited. If you are not the intended recipient please contact the sender and delete all copies. No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.4.0 - Release Date: 22/02/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BIGINT UNSIGNED issue?
Hi I am testing against MySQL 4.1 current release on Windows and there seems to be a fault with unsigned bigint datatypes. Using a BIGINT UNSIGNED datatype, the maximum value that will be accepted on insert query is 9223372036854775807 , which is actually the published maximum for signed Bigints. Also - with any other unsigend integer, an input value which exceeds the maximum will be corrected and be set to the maximum value (eg. input 7 to a smallint unsigned and you get 65535). However, with BigInt only the resulting value is set to zero. Is this a genuine fault or am misunderstanding something? Tim Hayes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Merging / Moving InnoDB Databases
(forwarded to the list) - Forwarded by Shawn Green/Unimin on 02/23/2005 09:08 AM - [EMAIL PROTECTED] wrote on 02/22/2005 04:59:39 PM: Regardless of the Mysql angle - a windows editor that allows you to view large text files is an absolute necessity. Notepad tries to load the entire file into memory before opening it which is a problem with large text files. There used to be a great text editor - Multi-Edit 8.0 made by American Cybernetics that could easily handle large text files. It would open the first block right away and then read ahead as you paged down. It was very clever software with lots of features. We couldn't live without it. Looking at the Web all I see are old references. There's a multieditsoftware.com but their product appears to be more of a programming editor. Maybe you can still find it somewhere. Another product is SPF/PC by commandtechnology.com which is a windows version of the venerable Mainframe SPF editor. At the time it was more limited than Multi- Edit but things may have changed. HTH. Udi This message and any files or text attached to it are intended only for the recipients named above, and contain information that may be confidential or privileged. If you are not an intended recipient, you must not read, copy, use, or disclose this communication. Please also notify the sender by replying to this message, and then delete all copies of it from your system. Thank you. [EMAIL PROTECTED] 02/22/2005 12:55 PM To:phpninja [EMAIL PROTECTED] cc:[EMAIL PROTECTED], mysql@lists.mysql.com Subject:RE: Merging / Moving InnoDB Databases phpninja [EMAIL PROTECTED] wrote on 02/22/2005 03:37:37 PM: I have a small question. Whenever I try to dump a sizeable Innodb table, lets say 33,000,000 records I find that mysqldump cannot handle that kind of load and usually freezes. I am not sure if it is my system, as its only a pentium 4 1.7ghz celeron running on windows server, but with my mysql tables nice and optimized mysql still flys in this heavy load envirornment. I decided testing it with smaller tables, maybe 2,000,000 records and it produced the .sql file successfully after giving it some time. Unfortunately when I loaded that .sql file I was getting all kinds of SQL errors about the syntax in that huge .sql file. I was kind of expecting a clean load in with the .sql file without any errors. At this point i decided to open the .sql file and attempt to see that lines it was complaining about with the error. Unfortunatlely, windows text editors do not handle displaying 2,000,000 of anything in 1 scrollable window, so i could not find the line errors. What do you suggest is the best way to backup an InnoDB table of this size? I have been thinking about an upgrade for quite a while now (moving from mysql 4.0 branch to 4.1) but I need a good solution to backup the data, and I am not sure if I rely on mysqldump after these tests. I've seen that Tool thats available at for purchase at innodb.com and I am considering it, but is that the only way to backup the data and have it all in tact 100%? I read that mysqlhotcopy does not cut it for InnoDB, and I have not tested it much on very big myISAM tables. -phpninja Read about and learn how to use the following mysqldump options: --quick --max_allowed_packet= http://dev.mysql.com/doc/mysql/en/mysqldump.html Quick will avoid putting the entire dump file into memory before it gets written to disk (straight write = faster finish) and max_allowed_packet will keep the extended INSERT statements to a reasonable length. Ask your destination server what that length should be (SHOW VARIABLES LIKE 'max%';) then tell mysqldump to not make INSERT packets any larger than that. There may be other options that could help you go faster but those are the two that pop to mind based on your problem descriptions. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 22, 2005 11:59 AM To: mysql@lists.mysql.com Subject: Re: Merging / Moving InnoDB Databases James, unfortunately, you cannot move InnoDB tables in that way, like you would be able to move MyISAM tables just by copying the .MYI, .MYD, and .frm files over to the other database installation. In the future, we may add a feature that allows one to copy 'clean' .ibd files across installations. But presently, you must dump the tables and import them to the other installation. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php
Re: UNIQUE Key Allowing Duplicate NULL Values
The problem is that I don't want more than one row in the table that has a null value in the column. As you've pointed out in your e-mail, there's a difference between NULL and BLANK. It's not that I don't want NULL values, it's that I don't want MORE THAN ONE. I can easily continue arguing about this ... :-) NULL is not a value. There's no such thing as a null value. NULL is a state. NOT NULL is a state. That's the two possible states of a column. Why do you allow NULL, in this case? And what does it mean? 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: UNIQUE Key Allowing Duplicate NULL Values
Tim, The table in question is used to store aliases to web sites. That is, meitech.com is the main web site, and www.meitech.com is the alias. So, the first column is the domain name in question, and the second on is the host name part of the alias, in this example www. Now, I also have mrpc.com, and I want the web site to respond on both www.mrpc.com and mrpc.com, so I add two more aliases - one with the alias set to NULL and one with the alias set to www. This causes my Apache configuration script to add the proper lines to my httpd.conf file. Storing it this way doesn't make sense then ... If you need to be able to store multiple aliasses, use a parent-child relationship. Now, suppose I try to add an alias to another web site - mrpcusa.net. Because you can have duplicate null values right now, I can add another mrpc.com alias and the database will allow it, but it confuses Apache and sometimes you'll get the correct web site, and sometimes you do not. One could argue that I could just use a blank value instead of null, but I have a function right now that I use to escape all user-inputted strings before I add them to the database, and that function is set to return NULL if the user-inputted string is empty. I use this function all over the place, and I can't just rewrite the function to return a blank string in the case that the user-inputted string is empty, because I don't know what it might break elsewhere. In general, think of not storing NULLs at all if you don't have to. There's no point in storing NULLs, as this is the _absence_ of data, not data in itself. What point is there in storing nothing or unknown? NULL means unknown. Not empty. If the input is empty, store empty. All IMO, of course, but avoiding NULL usually helps avoiding many other problems as well :-) 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]
Change default character_set_client, connection, results
Hello, could someone tell me if it is possible to change default settings for character_set_client, character_set_connection and character_set_results variables? They are always set to latin1 and I didn't find any way how to change their default value. I'm using MySQL 4.1.9-nt and MySQL ODBC 3.51. My applications are written in C++ Builder and I don't want to send command SET NAMES 'cp1250' whenever application opens connection. Thanks in advance Regards, Dusan Pavlica
Re: Avoiding filesort #2
At 05:12 AM 2/23/2005, Homam S.A. wrote: Thanks Mike for your feedback. Unfortunately the HANDLER statement has the same limitation, i.e. it doesn't allow you to specify range criteria for index key parts. You have to specify constants in the index_name (...) specifier. Are you saying you can't reference column values from another table to do a table join? On large tables we routinely don't use a table joins in MySQL and opt instead to write our own table sync procedures to go through each table and synchronize the rows as we move through the primary table (they have an index in common). This is much faster than doing table joins. If you're not doing table joins, then why can't you just build your Handler command using a programming language (PHP, Perl, etc) and insert whatever constants you want? Mike --- mos [EMAIL PROTECTED] wrote: At 08:47 PM 2/22/2005, you wrote: Even if I used ColA in the query, it still uses filesort if any keypart uses something other than an equal operator, like a range, IN operator, IS NOT NULL, IfNull(), etc. Rearranging the composite index to make the sort column the first one won't help because: 1) It's not part of the WHERE clause, so MySQL will have to scan the whole index to find matches on the other key parts. 2) The query optimizer won't use it (reporting NULL in the possible keys), even if I used FORCE INDEX (). It'd rather use a singlular index created on ColD instead. In addition, if I took ColD and put it in another table, it won't use it for sorting unless I'm sorting on the join column AND ColD. For example, this query will use filesort instead of the order of an index created on Y (ColA, ColD): SELECT X.* FROM X JOIN Y ON X.ColA = Y.ColB ORDER BY Y.ColD It will only use the index order if I included both keys of the index in the query: SELECT X.* FROM X JOIN Y ON X.ColA = Y.ColB ORDER BY Y.ColA, Y.ColD But this is obviously not what I want. This is a huge problem with multi-million-row tables because I'm gonna have to live with either table scans or quick sorts of large memory buffers, and both of which are evil and performance/scalability killers. How did you guys solve this sorting problem? It's a severe limitation of MySQL. I know that 5.x could use more than one index per table and could solve this problem, but it's still in Alpha stage, too risky for production use. Try looking at Handler http://dev.mysql.com/doc/mysql/en/handler.html which creates a cursor into a MISAM/Innodb table. It will traverse the table using the index without sorting and without creating huge buffers. It is a bit difficult to understand at first but it is fast. Remember to fetch 100 to 1000 rows at a time. You don't want to fetch just 1 row from each call. Mike --- Mike OK [EMAIL PROTECTED] wrote: Hi First, I am pretty sure that what the manual says is that MySQL only USES one index per request, not one index per table. I would try adding an index that starts with ColC (and maybe only ColC). Your index starts with ColA but you do not use it in your WHERE portion of the statement. This could confuse the index selection process and have MySQL decide to use no index in some situations. Mike - Original Message - From: Homam S.A. [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: February 22, 2005 8:12 PM Subject: Avoiding filesort #2 Actually with the query below it does avoid filesort, but once I use anything other than the equal operator (e.g. ColC 5), it reverts back to filesort. Any thoughts? --- Homam S.A. [EMAIL PROTECTED] wrote: I read How My SQL Optimizes Order By (http://dev.mysql.com/doc/mysql/en/order-by-optimization.html), and I'm aware of its severe limitation due to the one-index-per-table rule. However, even when I follow all the roles, I'm still getting filesort instead of using the index order. So I created an index as follows: CREATE INDEX IX_MyTable on MyTable (ColA, ColB, ColC, ColD DESC) Then I run the following query: SELECT ColA FROM MyTABLE WHERE ColB = 'CONSTANT' AND ColB = 1 AND ColC = 'C' ORDER BY ColdD DESC This is obviously a covered query by the index IX_MyTable. MySQL isn't supposed to touch the table. But MySQL insists on filesort! (I can this that int he Extra column of the EXPLAIN command). Why? I'm abiding by all the rules that should let MySQL use the index order instead of perform an expensive quick sort on a large memory buffer. I appreciate your feedback! __ Do you Yahoo!? Yahoo! Mail - Find what you need with new
RE: Odd rounding errors with 4.1
This reminded me of one more difference between Windows and Linux/Unix. MySQL use the round function out of the host libraries. If you are on a Windows box the rule for rounding is if the column immediately to the right of the column you are rounding to is a 5 then round up i.e. make 2.485 2.49 make 2.595 2.60 If you are on a Linux/Unix box the rule for rounding is if the column immediately to the right of the column you are rounding to is a 5 then round up if the column you are rounding to is odd and round down if the column you are rounding to is even i.e. make 2.485 2.49 make 2.595 2.59 Windows Linux/Unix 2.4850 2.492.48 2.5950 2.602.60 2.7700 2.772.77 7.8500 7.867.85 This was run on a RedHat server mysql select round(2.4850,2), round(2.5950,2), round(2.7700,2), round(2.4850,2)+round(2.5950,2)+round(2.7700,2); +-+-+-+- + | round(2.4850,2) | round(2.5950,2) | round(2.7700,2) | round(2.4850,2)+round(2.5950,2)+round(2.7700,2) | +-+-+-+- + |2.48 |2.60 |2.77 | 7.85 | +-+-+-+- + 1 row in set (0.00 sec) -Original Message- From: Martin [mailto:[EMAIL PROTECTED] Sent: Monday, February 21, 2005 7:49 PM To: Hassan Schroeder; mysql@lists.mysql.com Subject: Re: Odd rounding errors with 4.1 Huh, you know. Now that I'm not at work and therefore don't have my numbers to check against, you're right. Man, I must need more coffee. Never mind me. :) May be back tomorrow, though, when I have the numbers in front of me. I know they didn't add up earlier... Martin Hassan Schroeder wrote: Martin wrote: My recent test involved the following three values from the column: 2.4950 2.5950 2.7700 When I use a SUM() on these I get: 7.860 Sounds good to me... If I switch the column over to a FLOAT, then the SUM() becomes 7.858950958 Using Excel to test the numbers, or hand-calculating, I get: 7.8550. Time for a hand upgrade, I think :-) 5 + 5 = 5??? I don't even want to think about how Excel would come up with this... Shouldn't the SUM() remain with the precision of the DECIMAL type and not try to round to 2 decimal places? My own, possibly suspect, hand calculations show that SUM() is right; and it's common knowledge that floating point isn't the right thing to use for situations like this -- that's why there *is* a DECIMAL type. FWIW! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL constraint question
Thanks for these answers. I can now write this in the application and be OK with it without that nagging feeling that somebody will say upa if you just put xxx in the database then you could filter all that in your query. If it ever happens I can say, ah yes but when I wrote that we were only on version 4. When you have a problem set that pushes the envelope of what it can do... that's how people get the ideas to make it do a new level of things in the future, such as you are envisioning. Martijn Tonies wrote: I have a database thats full of ingredients that are placed in various categories. and then there are mixtures that are allowed to be labelled with a certain grade based on the quality and composition of the ingredients it is comprised from. But the formulas are not always the same, as long as the profile matches certain criterian. For example, in order to be label grade Premium, it must have between 70% and 95% ingredients from group A between 0 and 15 % from group B between 5 and 15% from group C between 0 and 15% from group D What you actually put in the mix is determined by price and availability and other factors. So I implement these profiles with a mySQL table I have grade_id points to main grade record category_idpoints to category min_percent max_percent and the individual ingrediants ing_id name price category_idamong others... The question is Is there some way mysql can assure the integrity of these profiles, so that there is no way to use a set of records for a grade that can't add upto 100%. Or is this pretty much application logic? Nope, the combined total 100% calculation will have to be application-based as it requires validating an inserted or updated row based on the value(s) contained in other row(s). CHECK constraints, which are in the development pipeline, can only be used to validate a row against constant values or some combination of values from within the row being evaluated. Any time you need to compare a group of rows in order to validate the group, you have left the automation of SQL logic and are in the realm of application logic, as you guessed. Disagreed :-) A decent database system would be able to create multi-row check constraints - there are several types of constraints. 1) column constraints 2) table constraints 3) database/schema constraints These would fall under (3) I guess. Firebird allows queries in its check constraints, but only enforces the constraints at INSERT or UPDATE time. A database system that would support deferred constraints should be able to create multi-table, multi-row check constraints just fine. However, I don't know any DBMS that currently does that. Perhaps Mimer or ThinkSQL... 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: Is there a Library of complex queries/inserts/queries?
On Sun, 13 Feb 2005 10:34:39 +, wrote: Hi, Sometimes an example is worth a 1000 words. Does anyone know of a website with lists of mysql statement examples? ie a list of queries, a list of updates, list of inserts from simple examples to joins regexps etc From Shantanu http://en.wikibooks.org/wiki/Appendix mysql statement examples zzapper (vim, cygwin, wiki zsh) -- vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Change default character_set_client, connection, results
Hello. Use MYSQL_SET_CHARSET_NAME option for mysql_options() function. See: http://dev.mysql.com/doc/mysql/en/mysql-options.html [snip] Hello, could someone tell me if it is possible to change default settings for character_set_client, character_set_connection and character_set_results variables? They are always set to latin1 and I didn't find any way how to change their default value. I'm using MySQL 4.1.9-nt and MySQL ODBC 3.51. My applications are written in C++ Builder and I don't want to send command SET NAMES 'cp1250' whenever application opens connection. Thanks in advance [snip] -- 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]
Date Functions in MySQL
I have a log table that records certain transactions on one of my sites. I'm using a timestamp field to mark the date/time of each transaction. I'm trying to run a query that will display the transactions by date using the following SQL: SELECT COUNT(log_id) AS total, WEEK(FROM_UNIXTIME(UNIX_TIMESTAMP(cdate), '%Y-%m-%d'), 3) AS week, MONTHNAME(FROM_UNIXTIME(UNIX_TIMESTAMP(cdate), '%Y-%m-%d')) AS month, FROM_UNIXTIME(UNIX_TIMESTAMP(cdate), '%Y') AS year FROM log However, when I run this query I get very strange results for the week and month. They don't seem to match up. For example: +---+--+---+--+ | total | week | month | year | +---+--+---+--+ | 16869 | 49 | December | 2003 | | 16782 | 50 | December | 2003 | | 14668 | 51 | December | 2003 | | 14121 | 52 | December | 2003 | | 15453 |2 | January | 2004 | | 20332 |3 | January | 2004 | | 41788 |4 | January | 2004 | | 29223 |5 | January | 2004 | | 23143 |6 | February | 2004 | | 23463 |7 | February | 2004 | | 4463 | 53 | December | 2004 | +---+--+---+--+ I guess I'm wondering if there is a known bug with the way weeks are calculated as they don't seem to match up with the months. In the results above - what happened to week 1 in January 2004? Any ideas would be greatly appreciated. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting with a list of keys
I would like to construct a query that searces on a variable list of primary keys. Example: Lkeys = [12, 23, 15, 17]## python - style list of integers Select column from table where ID in Lkeys; ## mysql statement? Is this possible, or does one have to generate a query for each key or generate a query with multiple ORs? This will have to be run on a *nix server with version prior to 4.0, I believe. Solutions, comments and pointers to relevant documentation is all welcome. Thanks Tim Johnson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UNIQUE Key Allowing Duplicate NULL Values
In article [EMAIL PROTECTED], Gustafson, Tim [EMAIL PROTECTED] writes: Martijn, The problem is that I don't want more than one row in the table that has a null value in the column. As you've pointed out in your e-mail, there's a difference between NULL and BLANK. It's not that I don't want NULL values, it's that I don't want MORE THAN ONE. You really should change your requirements. Since NULL != NULL, every DB enforcing a single NULL row by a unique index would not be SQL. You seem to want some special value to occur only once, but NULL is no value at all. Can't you make 0 or (the empty string) that special value? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting with a list of keys
Tim Johnson wrote:
I would like to construct a query that searces on a variable list of
primary keys.
Example: Lkeys = [12, 23, 15, 17]## python - style list of integers
Select column from table where ID in Lkeys; ## mysql statement?
Is this possible, or does one have to generate a query for each key or
generate a query with multiple ORs?
MySQL supports the IN operator, you can write:
Select column from table where ID in (12, 23, 15, 17)
In Python this could be done like this:
Lkeys = [12, 23, 15, 17]
res = query('Select column from table where ID in (%s)' %
(','.join(map(str,Lkeys
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Functions in MySQL
Clarence wrote: I have a log table that records certain transactions on one of my sites. I'm using a timestamp field to mark the date/time of each transaction. I'm trying to run a query that will display the transactions by date using the following SQL: SELECT COUNT(log_id) AS total, WEEK(FROM_UNIXTIME(UNIX_TIMESTAMP(cdate), '%Y-%m-%d'), 3) AS week, MONTHNAME(FROM_UNIXTIME(UNIX_TIMESTAMP(cdate), '%Y-%m-%d')) AS month, FROM_UNIXTIME(UNIX_TIMESTAMP(cdate), '%Y') AS year FROM log However, when I run this query I get very strange results for the week and month. They don't seem to match up. The TIMESTAMP column type is NOT a UNIX timestamp...! It is a 'normal' datetime column, you should use just WEEK(cdate), MONTHNAME(cdate) and so on. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Functions in MySQL
Shoot me now, please. Thanks - I don't know how I missed that! Thanks - re-ran the query and things seem to be a-ok! On Wed, 23 Feb 2005 18:42:17 +0100, Roger Baklund [EMAIL PROTECTED] wrote: Clarence wrote: I have a log table that records certain transactions on one of my sites. I'm using a timestamp field to mark the date/time of each transaction. I'm trying to run a query that will display the transactions by date using the following SQL: SELECT COUNT(log_id) AS total, WEEK(FROM_UNIXTIME(UNIX_TIMESTAMP(cdate), '%Y-%m-%d'), 3) AS week, MONTHNAME(FROM_UNIXTIME(UNIX_TIMESTAMP(cdate), '%Y-%m-%d')) AS month, FROM_UNIXTIME(UNIX_TIMESTAMP(cdate), '%Y') AS year FROM log However, when I run this query I get very strange results for the week and month. They don't seem to match up. The TIMESTAMP column type is NOT a UNIX timestamp...! It is a 'normal' datetime column, you should use just WEEK(cdate), MONTHNAME(cdate) and so on. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Shell execution of mysql query
I am executing a mysql query through shell and expecting to see a return of SQL execution. mysql -D $dbName --vertical -u $DBUSER -p$DBPASS $queryFile $opFile rc=$? Here rc is always 0 and so are $opFile entries. $queryFile contains exactly the same query as listed below. If the same query is run in mysql client, I see the result as Empty set (0.00 sec). Why don't I get a return in shell. mysql select * from usrtbl where username='vou_0004016'; Empty set (0.00 sec) Thanks, Nupur ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- 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]
LOAD INDEX INTO CACHE behaviour
Hi, Working on v4.1 (and from what I read, soon to upgrade to 4.1.18) and I'm dealing with an MyISAM table expected to idle at around 400 million records. Since it is the back-end to a high volume web site, we are trying to optimize the performance of the indexes. I read in the manual that LOAD INDEX INTO CACHE has a option which excludes the pre-loading of the leaf nodes. What I can't figure out from reading the documentation is how much of the BTREE will be made of leaves and how much of the higher index nodes. For the key, I am considering either using the prefix of the user name ie. CREATE INDEX username_ndx on username(8)... or create an additional INTEGER field which will store a CRC32 of username, and use that field as an index. For an 8 byte key and the 20M records mentioned above, the calculations provided in the manual show me that the resulting index will be (worst case) 3.2-10e bytes but I certainly don't have 32G of RAM to load it into. How is LOAD INDEX INTO CACHE going to keep index access from disk-thrashing and do I need to manually tune key_buffer_size to take advantage of it? -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shell execution of mysql query
On Wed, 23 Feb 2005, Nupur Jain wrote: Hi Nupur, I am executing a mysql query through shell and expecting to see a return of SQL execution. mysql -D $dbName --vertical -u $DBUSER -p$DBPASS $queryFile $opFile rc=$? Here rc is always 0 and so are $opFile entries. $queryFile contains exactly the same query as listed below. the following egrep (or grep -e) should do the trick: mysql -D $dbName --vertical -u $DBUSER -p$DBPASS $queryFile $opFile egrep -qv Empty set (0.00 sec) $opFile rc=$? Olivier Kaloudoff CKR Solutions Open Source Mandelieu, France http://www.ckr-solutions.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Shell execution of mysql query
On Wednesday, February 23, 2005 12:30, Nupur Jain wrote: I am executing a mysql query through shell and expecting to see a return of SQL execution. mysql -D $dbName --vertical -u $DBUSER -p$DBPASS $queryFile $opFile rc=$? Here rc is always 0 and so are $opFile entries. $queryFile contains exactly the same query as listed below. If the same query is run in mysql client, I see the result as Empty set (0.00 sec). Why don't I get a return in shell. mysql select * from usrtbl where username='vou_0004016'; Empty set (0.00 sec) It makes sense the the return code from mysql is zero, since it sucessfully executed. If it is an empty set, your output file will be empty. If you want the file to show you the query and empty set, change your command to the following: mysql -vv -D $dbName --vertical -u $DBUSER -p$DBPASS $queryFile $opFile The -vv controls sets the verbosity of the output from the client. Thanks, Nupur -- 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: Shell execution of mysql query
At 10:29 -0800 2/23/05, Nupur Jain wrote: I am executing a mysql query through shell and expecting to see a return of SQL execution. mysql -D $dbName --vertical -u $DBUSER -p$DBPASS $queryFile $opFile rc=$? Here rc is always 0 and so are $opFile entries. $queryFile contains exactly the same query as listed below. If the same query is run in mysql client, I see the result as Empty set (0.00 sec). Why don't I get a return in shell. mysql select * from usrtbl where username='vou_0004016'; Empty set (0.00 sec) The verbosity of mysql changes when its input isn't the terminal. You can increase it with -v: % echo select 1 |mysql 1 1 % echo select 1 | mysql -v -- select 1 -- 1 1 % echo select 1 | mysql -vv -- select 1 -- 1 1 1 row in set Bye % echo select 1 | mysql -vvv -- select 1 -- +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) Bye -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Avoiding filesort #2
--- mos [EMAIL PROTECTED] wrote: At 05:12 AM 2/23/2005, Homam S.A. wrote: Thanks Mike for your feedback. Unfortunately the HANDLER statement has the same limitation, i.e. it doesn't allow you to specify range criteria for index key parts. You have to specify constants in the index_name (...) specifier. Are you saying you can't reference column values from another table to do a table join? On large tables we routinely don't use a table joins in MySQL and opt instead to write our own table sync procedures to go through each table and synchronize the rows as we move through the primary table (they have an index in common). This is much faster than doing table joins. The joins aren't just for synching tables, but mostly for lookups. Sometimes the join interestion result is huge (multi-million), depending on how loose the user criteria is, and this result needs to be sorted to pull out the top several thousands, where the user can page through a web interface. I'm not sure if it's more efficient to take in a multi-million row result from the first table, manually join it with a second table, sort the result, and page through the top few thousands. I was hoping that I could take advantage of MySQL's index pres-sorting to do so, but obviously, in many cases, I can't. I can do so just fine with other servers, like MS SQL Server. If you're not doing table joins, then why can't you just build your Handler command using a programming language (PHP, Perl, etc) and insert whatever constants you want? This is one of the options I will look at for the next release. It seems with MySQL you have dive in and tinker with its plumbing to get the most out of it. Mike Thanks! --- mos [EMAIL PROTECTED] wrote: At 08:47 PM 2/22/2005, you wrote: Even if I used ColA in the query, it still uses filesort if any keypart uses something other than an equal operator, like a range, IN operator, IS NOT NULL, IfNull(), etc. Rearranging the composite index to make the sort column the first one won't help because: 1) It's not part of the WHERE clause, so MySQL will have to scan the whole index to find matches on the other key parts. 2) The query optimizer won't use it (reporting NULL in the possible keys), even if I used FORCE INDEX (). It'd rather use a singlular index created on ColD instead. In addition, if I took ColD and put it in another table, it won't use it for sorting unless I'm sorting on the join column AND ColD. For example, this query will use filesort instead of the order of an index created on Y (ColA, ColD): SELECT X.* FROM X JOIN Y ON X.ColA = Y.ColB ORDER BY Y.ColD It will only use the index order if I included both keys of the index in the query: SELECT X.* FROM X JOIN Y ON X.ColA = Y.ColB ORDER BY Y.ColA, Y.ColD But this is obviously not what I want. This is a huge problem with multi-million-row tables because I'm gonna have to live with either table scans or quick sorts of large memory buffers, and both of which are evil and performance/scalability killers. How did you guys solve this sorting problem? It's a severe limitation of MySQL. I know that 5.x could use more than one index per table and could solve this problem, but it's still in Alpha stage, too risky for production use. Try looking at Handler http://dev.mysql.com/doc/mysql/en/handler.html which creates a cursor into a MISAM/Innodb table. It will traverse the table using the index without sorting and without creating huge buffers. It is a bit difficult to understand at first but it is fast. Remember to fetch 100 to 1000 rows at a time. You don't want to fetch just 1 row from each call. Mike --- Mike OK [EMAIL PROTECTED] wrote: Hi First, I am pretty sure that what the manual says is that MySQL only USES one index per request, not one index per table. I would try adding an index that starts with ColC (and maybe only ColC). Your index starts with ColA but you do not use it in your WHERE portion of the statement. This could confuse the index selection process and have MySQL decide to use no index in some situations. Mike - Original Message - From: Homam S.A. [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: February 22, 2005 8:12 PM Subject: Avoiding filesort #2 Actually with the query below it does avoid filesort, but once I use anything other than the equal operator (e.g. ColC 5), it reverts back to filesort. Any thoughts? --- Homam S.A. [EMAIL PROTECTED] wrote: I read How My SQL Optimizes Order By
Difficulty starting mysql
Hello, I'm attempting to start mysql 4.1.10 compiled source code on Red Hat Advanced Server 2.1 with apache 2.0.49. ./configure, make and make install went successfully. But when I attempt to start mysql using this command: sudo mysqld_safe --user=mysql I get the following errors: Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 050223 11:31:46 mysqld ended The error log states: 050223 11:16:03 mysqld started 050223 11:16:03 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 050223 11:16:03 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43634. InnoDB: Doing recovery: scanned up to log sequence number 0 43634 050223 11:16:03 InnoDB: Flushing modified pages from the buffer pool... 050223 11:16:03 InnoDB: Started; log sequence number 0 43634 050223 11:16:03 [ERROR] /usr/local/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 050223 11:16:03 [ERROR] Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 050223 11:16:03 mysqld ended I also did a find on mysld and found this: ./var/run/mysqld ./etc/rc.d/init.d/mysqld ./etc/logrotate.d/mysqld ./usr/libexec/mysqld ./usr/local/libexec/mysqld ./home/webcollab/mysql-4.1.10/sql/mysqld What do I need to do to make it start properly? Thanks in advance! Kelly S. Brace Information Technology Exchange Center Twin Rise 200 1300 Elmwood Avenue Buffalo, NY 14222 http://www.itec.suny.edu Main: 716-878-4832 Fax: 716-878-3485 Office: 716-878-3984 Cell: 716-432-4978 [EMAIL PROTECTED]
Re: Lost InnoDB Data
Henrik, the most probable reason is that someone has edited my.cnf meanwhile, and you are now reading ibdata files from somewhere else. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL Network from http://www.mysql.com/network/ - Original Message - From: Henrik Skotth [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, February 23, 2005 1:16 PM Subject: Lost InnoDB Data --=_NextPart_000_0076_01C519A0.CE3C7EA0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hello all. I have a problem which is very serious for us. After shutting down our = mysql database and starting it up again, ALL our innodb-tables are just = as they were several weeks ago (january 31). All the changes, additions, = deletions that have happened since are gone. The same thing happens if = we go back to backups which are not entirerly new but later than jan 31. = I have no idea what's causing this, and I have absolutely no clue what = to do about it. Any suggestions? We're running MySQL 4.0.21 on Red Hat. Any help with this would be greatly appreciated! Best regards, -- Henrik Skotth --=_NextPart_000_0076_01C519A0.CE3C7EA0-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Difficulty starting mysql
Did you follow the procedure in http://dev.mysql.com/doc/mysql/en/quick-install.html ? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 23, 2005 1:29 PM To: mysql@lists.mysql.com Subject: Difficulty starting mysql Hello, I'm attempting to start mysql 4.1.10 compiled source code on Red Hat Advanced Server 2.1 with apache 2.0.49. ./configure, make and make install went successfully. But when I attempt to start mysql using this command: sudo mysqld_safe --user=mysql I get the following errors: Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 050223 11:31:46 mysqld ended The error log states: 050223 11:16:03 mysqld started 050223 11:16:03 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 050223 11:16:03 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43634. InnoDB: Doing recovery: scanned up to log sequence number 0 43634 050223 11:16:03 InnoDB: Flushing modified pages from the buffer pool... 050223 11:16:03 InnoDB: Started; log sequence number 0 43634 050223 11:16:03 [ERROR] /usr/local/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 050223 11:16:03 [ERROR] Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 050223 11:16:03 mysqld ended I also did a find on mysld and found this: ./var/run/mysqld ./etc/rc.d/init.d/mysqld ./etc/logrotate.d/mysqld ./usr/libexec/mysqld ./usr/local/libexec/mysqld ./home/webcollab/mysql-4.1.10/sql/mysqld What do I need to do to make it start properly? Thanks in advance! Kelly S. Brace Information Technology Exchange Center Twin Rise 200 1300 Elmwood Avenue Buffalo, NY 14222 http://www.itec.suny.edu Main: 716-878-4832 Fax: 716-878-3485 Office: 716-878-3984 Cell: 716-432-4978 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backup User
Is it recommended to create a MySQL user for doing backups? Running mysqldump from a script, the username and password will have to be embedded in plain text. If I were to create a MySQL user expressly for doing nightly backups what are the minimum permissions needed? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup User
Jim McAtee wrote: Is it recommended to create a MySQL user for doing backups? Running mysqldump from a script, the username and password will have to be embedded in plain text. If I were to create a MySQL user expressly for doing nightly backups what are the minimum permissions needed? You don't need to create a special MySQL user for backups. If you like, you can create a Linux account that only you know the password to, and then make sure the backup scripts is only readable by you ( root will also be able to read it ). Or just run the backup script as root, and then root can only read it ( you have to set the correct permissions here too of course ). -- 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: Backup User
On Thu, Feb 24, 2005 at 08:39:00AM +1100, Daniel Kasak [EMAIL PROTECTED] wrote: You don't need to create a special MySQL user for backups. If you like, you can create a Linux account that only you know the password to, and then make sure the backup scripts is only readable by you ( root will also be able to read it ). Or just run the backup script as root, and then root can only read it ( you have to set the correct permissions here too of course ). What you really want to avoid is having the password on the commandline. File permissions won't matter at all if you end up running a command that puts your password in the output of 'ps'! Command lines are always public information. Put the password for mysqldump in the running user's ~/.my.cnf instead, and tighten the permissions on *that* file. [client] password=Your password goes here -Rich -- Rich Lafferty --+--- Ottawa, Ontario, Canada| Save the Pacific Northwest Tree Octopus! http://www.lafferty.ca/|http://zapatopi.net/treeoctopus.html [EMAIL PROTECTED] ---+--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb - raw partition vs filesystem store?
What are pros/cons as far as performance, reliability, and ease of backup/restore? Anyone have any experience running Innodb on raw partition? Any thoughts as to best filesystem for Innodb? What about pros/cons of journaled filesystems when in use with Innodb (i.e. transactions)? How do the recent experiences of LiveJournal/Wikipedia sway these answers? Just going over some thoughts in my head and want to see if any good discussion can come from this? Greg -- [EMAIL PROTECTED] Meetup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: missing header file?
Thanks, I'll try that. I haven't actually tried to compile MySQL from source and haven't read the instructions. Sorry. It seems that the file in question is present in MySQL Linux binary distribution, missing from source (i.e. configure should be run), missing from Windows binary, present in Windows source. Interesting symmetry. -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 22, 2005 2:14 AM To: mysql@lists.mysql.com Subject: Re: missing header file? Hello. You should run configure in the source directory, which will create a Makefile in the include/ directory. Then cd into it and run: make my_config.h [snip] I'm trying to modify and recompile some UDFs for MySQL 4.1. I'm using the header files from the version 4.1.10 Linux source archive and am getting the following error: home/sskarupo/mysql-4.1.10/include/my_global.h:70: my_config.h: No such file or directory my_config.h isn't there. It is included in the Windows source archive, though. Can anyone explain this? Thanks in advance, SergeiSergei Skarupo [EMAIL PROTECTED] wrote: [snip] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup User
- Original Message - From: Daniel Kasak [EMAIL PROTECTED] To: Jim McAtee [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Wednesday, February 23, 2005 2:39 PM Subject: Re: Backup User Jim McAtee wrote: Is it recommended to create a MySQL user for doing backups? Running mysqldump from a script, the username and password will have to be embedded in plain text. If I were to create a MySQL user expressly for doing nightly backups what are the minimum permissions needed? You don't need to create a special MySQL user for backups. If you like, you can create a Linux account that only you know the password to, and then make sure the backup scripts is only readable by you ( root will also be able to read it ). Or just run the backup script as root, and then root can only read it ( you have to set the correct permissions here too of course ). Thanks. Lets say I did this but still wanted to use a MySQL user with minimum privileges. The server is running MySQL 3.23.49 and all tables are MyISAM. It appears that I can do backups with just Select and File privileges. Somoene else suggested those privileges plus 'Lock Tables'. I'm guessing Lock Tables was added to user privileges in v4.0. If I'm using mysqldump --opt, will tables be locked if the user only has Select File privileges? I can't really tell if the tables are being locked when I run the command. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access Denied For User
Hello, I have a Linux server running Apache, MySQL, unixODBC, MyODBC In my odbcinst.ini file I have the following: [MySQL] Description= ODBC for MySQL Driver = /usr/local/lib/libmyodbc3.so FileUsage = 1 I have setup the following system DSN in the odbc.ini file: [PbDatabase] Description = System DSN Driver = /usr/local/lib/libmyodbc3.so Host = localhost Server = localhost User = username Password = password Port = 3306 Database = Database01 Option = 3 However, when I use the System DSN name in my scripts I get the following error: 350 Native SQL Error Code [unixODBC][MySQL][ODBC 3.51 Driver]Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) The user apacheusr is the username that the Apache webserver is running under. I've tried adding this user to the MySQL user table but the error still shows. However, if I add the Username and Password along with the DSN name to my scripts within my HTML pages then the connection is made and records from the database returned. Any ideas why this might be happening?? Is it a permissions issue with MySQL or with unixODBC or Apache?? Any help would be greatly appreciated. Thanks in advance [EMAIL PROTECTED] -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.306 / Virus Database: 266.4.0 - Release Date: 22/02/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difficulty starting mysql
errno 13 is permission denied. It looks like mysql doesn't have permission to access your grant tables. -Eric On Wed, 23 Feb 2005 14:00:58 -0600, John Trammell [EMAIL PROTECTED] wrote: Did you follow the procedure in http://dev.mysql.com/doc/mysql/en/quick-install.html ? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 23, 2005 1:29 PM To: mysql@lists.mysql.com Subject: Difficulty starting mysql Hello, I'm attempting to start mysql 4.1.10 compiled source code on Red Hat Advanced Server 2.1 with apache 2.0.49. ./configure, make and make install went successfully. But when I attempt to start mysql using this command: sudo mysqld_safe --user=mysql I get the following errors: Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 050223 11:31:46 mysqld ended The error log states: 050223 11:16:03 mysqld started 050223 11:16:03 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 050223 11:16:03 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43634. InnoDB: Doing recovery: scanned up to log sequence number 0 43634 050223 11:16:03 InnoDB: Flushing modified pages from the buffer pool... 050223 11:16:03 InnoDB: Started; log sequence number 0 43634 050223 11:16:03 [ERROR] /usr/local/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 050223 11:16:03 [ERROR] Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 050223 11:16:03 mysqld ended I also did a find on mysld and found this: ./var/run/mysqld ./etc/rc.d/init.d/mysqld ./etc/logrotate.d/mysqld ./usr/libexec/mysqld ./usr/local/libexec/mysqld ./home/webcollab/mysql-4.1.10/sql/mysqld What do I need to do to make it start properly? Thanks in advance! Kelly S. Brace Information Technology Exchange Center Twin Rise 200 1300 Elmwood Avenue Buffalo, NY 14222 http://www.itec.suny.edu Main: 716-878-4832 Fax: 716-878-3485 Office: 716-878-3984 Cell: 716-432-4978 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is there a Library of complex queries/inserts/queries?
MySQL Cookbook by DuBois. -Original Message- From: zzapper [mailto:[EMAIL PROTECTED] Sent: Sunday, February 13, 2005 2:35 AM To: mysql@lists.mysql.com Subject: Is there a Library of complex queries/inserts/queries? Hi, Sometimes an example is worth a 1000 words. Does anyone know of a website with lists of mysql statement examples? ie a list of queries, a list of updates, list of inserts from simple examples to joins regexps etc zzapper (vim, cygwin, wiki zsh) -- vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- 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_FIELD
Dears,
My code is :
void CMysql::ReadingFld(const char *tblname)
{
MYSQL_RES *fldres;
int i;
fldres=mysql_list_fields(connection2db,tblname,%);
ptr.srcFldNumber=mysql_num_fields(fldres);
FieldsRow=mysql_fetch_fields(fldres);
for (i=0;iptr.srcFldNumber;i++)
cout FieldsRow[i];
}//end of RedingFld method
I defined FieldsRow from MYSQL_FIELD type at my class.
Now,I receive following error:
[EMAIL PROTECTED] sql2sql]# make
g++ -c -I/usr/include -I/usr/include/mysql sql2sql.cpp;
sql2sql.cpp: In member function `void CMysql::ReadingFld(const char*)':
sql2sql.cpp:208: no match for `MYSQL_FIELD = MYSQL_FIELD*' operator
/usr/include/mysql/mysql.h:92: candidates are: st_mysql_field
st_mysql_field::operator=(const st_mysql_field)
sql2sql.cpp:210: no match for `MYSQL_FIELD [int]' operator
make: *** [sql2sql.o] Error 1
Please help me
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Images to MySql Database in PHP or JAVA
Hello list- I am programing both in Java and PHP. Can anyone point me to sample code for either on saving an image to a table? Thanks, -Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Images to MySql Database in PHP or JAVA
Dear member,you can visit: http://dev.mysql.com/doc/connector/j/en/index.html http://dev.mysql.com/doc/mysql/en/php.html Hello list- I am programing both in Java and PHP. Can anyone point me to sample code for either on saving an image to a table? Thanks, -Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Excessive Server Load Problems
Hi all, I have just created a databased website that is working with a fairly large number of records (well, 10 million records is the biggest database ive ever worked on so far) but also have a large number of searches of these records happening regularly. The problem ive come across, is my hosting providers box is setup so that if the server load gets to 5, sendmail packs it in. When someone does a search of the records, mysql jumps up to 100% cpu usage (which is fine) but the more searches that are being done (the website is getting more users daily) the longer the cpu is at 100% and the higher the load gets. I have been suggested to possibly have the hosting provider look at piping mysql through the nice application to keep its cpu usage under control. Is this a good method and will this make a difference, or is there a better way to tune mysql to keep load down? My understanding of nice is that it puts mysql's cpu usage priority at the lowest. But if nothing else on the box needs the CPU, it'll still run full and bring load over 5 making sendmail crash anyway. Any responses would be appreciated. Regards Chris Aitken The Web Hub Designer and Programmer Phone : 02 4648 0808 Mobile : 0411 132 075 - Turning your business into an e-business today. The Web Hub http://www.thewebhub.com.au/ [EMAIL PROTECTED] - Confidentiality Statement: This message is intended only for the use of the Addressee and may contain information that is PRIVILEDGED and CONFIDENTIAL. If you are not the intended recipient, dissemination of this communication is prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately
