Server Configuration Help
Greetings, I am running MySQL (version 4.0.15 max) database on Linux (RH9) box. This linux box is a dedicated database server with following h/w configuration: CPU: 2 * 2.4 Ghz Xeon Processor, 512 K 533 FSB Ram :6GB Hdd:36GB * 5 raid config Typically, this database has less number of client connections but those who connect generally run highly analytical stuff off the database. Also the database size is pretty huge (around 40 gb). After reading though the manuals, specifically some of the performance enhancement tips, I build the my.cnf as show below. Now on to the real question, Do you MySQL gurus think that given all the details, Is there anyway to enhance the my.cnf file for better performance/speed ? Your kind help would be greatly appreciated. Best Regards Manoj --- my.cnf file - [client] port=3306 socket=/tmp/mysql.sock [mysqld] user=mysql port=3306 key_buffer=512M table_cache=512 sort_buffer=2M read_buffer_size=4M read_rnd_buffer_size=4M max_connection=100 max_allowed_packet= 1M default-table-type=innodb log_slow_queries=/home/mysql/log/slow.query.log log_error=/home/mysql/log/mysqld.err.log log_long_format # innodb_options innodb_data_home_dir=/usr/local/mysql innodb_data_file_path=ibdata/ibdata1:3G;ibdata/ibdata2:3G:autoextend innodb_mirrored_log_groups=1 innodb_log_group_home_dir=/usr/local/mysql/ibdata/log innodb_log_arch_dir=ibdata/log innodb_log_files_in_group=2 innodb_log_file_size=512M innodb_log_buffer_size=8M innodb_buffer_pool_size=1G innodb_additional_mem_pool_size=4M innodb_flush_log_at_trx_commit=0 innodb_flush_method=O_DIRECT --- End of my.cnf file - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup and Restore
[EMAIL PROTECTED] wrote: Hi All, Mysqldump -h host name -u username -p password --databases database name dump.dmp My doubts are as follows. 1. How to take the backup and restore from the client machine using ODBC? mysqldump is a command line utility for making backups. You will have to exec (iirc, it is called system in VB) your mysqldump statement. Providing the full paths of your mysqldump binary and your dump file is a good idea. 2. If I have connection already established using ODBC, can I run the Mysqldump command with the connection id? If yes, do let me know the solution. You do not need an ODBC connection to run mysqldump, rathery ou need permissions to exec the process. If you want to use the sql statements to get the mysql dump, it is a long tedious process (to be indulged in only if you have time and energy to rebuild the wheel). If you have the patience, have a look at the source code of phpmyadmin specifically the function PMA_exportData in the file /libraries/export/sql.php) . The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. By reading this message you confirm that you owe me two large pizzas -- Raj Shekhar, System Administrator Media Web India http://www.netphotograph.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is this the best/fastest solution?
- Original Message -
From: Harald Fuchs [EMAIL PROTECTED]
In article [EMAIL PROTECTED],
Jigal van Hemert [EMAIL PROTECTED] writes:
SELECT t1.`msg_id`
FROM `msg_content` AS t1
JOIN `msg_addressee` AS t2 ON t1.`msg_id` = t2.`msg_id` AND
FIND_IN_SET( t2.`status` , 'deleted' ) 0
LEFT JOIN `msg_addressee` t3 ON t1.`msg_id` = t3.`msg_id` AND
FIND_IN_SET( t3.`status` , 'deleted' ) =0
GROUP BY t2.`msg_id` , t3.`msg_id`
HAVING COUNT( t3.`msg_id` ) =0
Which contains an error in de FIND_IN_SET(), I noticed...
Try the following:
SELECT t1.msg_id
FROM msg_content t1
LEFT JOIN msg_addressee t2 ON t1.msg_id = t2.msg_id
AND FIND_IN_SET ('deleted', t2.status) = 0
WHERE t2.id IS NULL
Thanks, I wasn't thinking so clearly anymore last Friday ;-)
Regards, Jigal.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Error 1005:150
Michael, Thank you for your reply. Here is a bit more info. I changed the default table type to innodn in the my.ini file before creating the database, so all tables are innodb. I tried the create statements with and without explicit index clauses with all permutations - same result each time. I agree that something is wrong. Did you try running the ddl you suggested below? If so, did it work for you? I downloaded the latest release from thr ANL mirror which says v 4.1.2 in the file name. When I run MySQL, the system says it is 4.0.22. Is the engine version different than the release version? This is a secondaary issue however. Steve -- Original message -- Something is wrong, but it's hard to say what. It seems unlikely you entered exactly those commands and got an error only on the last ALTER TABLE. First, you need InnoDB tables to support foreign keys, but you don't specify the table engine in your CREATE statements. The default is MyISAM, unless you've changed it. But that's not it. If they were MyISAM tables, neither ALTER would work, but if they're all InnoDB, then all should work. Is it possible that just table address is MyISAM? In order to create a foreign key, you must have an index on the columns on each side of the relationship. That is, you need person_id and address_id to be indexed in both tables. Prior to 4.1.2, you had to do that by hand, but in 4.1.2 and later it's automatic. Again, all or nothing, so not likely relevant here. Some other things to note (which are unrelated to the error): There is no need to put an index on a column which has already been indexed as the primary key. It's a waste of space that adds overhead to inserts. You are relying on MySQL to create indexes for you in table person_address, but I don't think it will make the best choices in this case. You need an index on each column, but you most likely also need the combination of person_id and address_id to be unique. In other words, if you let mysql create indexes for you to satisfy the foreign key needs, you get separate single-column indexes, but you need a combined column unique constraint which renders one of the single column indexes redundant. How about: CREATE TABLE person ( person_id INT UNSIGNED NOT NULL AUTO_INCREMENT, constraint person_pk PRIMARY KEY (person_id) ) ENGINE=InnoDB; CREATE TABLE address ( address_id INT UNSIGNED NOT NULL AUTO_INCREMENT, constraint address_pk PRIMARY KEY (address_id) ) ENGINE=InnoDB; CREATE TABLE person_address ( person_id INT UNSIGNED NOT NULL, address_id INT UNSIGNED NOT NULL, CONSTRAINT person_address_pk PRIMARY KEY (person_id, address_id), INDEX (address_id), CONSTRAINT person_person_address_FK1 FOREIGN KEY (person_id) REFERENCES person (person_id); CONSTRAINT address_person_address_FK1 FOREIGN KEY (address_id) REFERENCES address (address_id); ) ENGINE=InnoDB; Michael [EMAIL PROTECTED] wrote: I am unable to define a foreign key with the following three tables. I am unable to find the error having searched the documentation and tried several variations. Note that I created the first two tables with and without the index clause in the table ddl with no difference in outcome. The three tables and the first foreign key, person_person_address_FK1, create properly. The second foreign key, address_person_address_FK1, causes the error. Please help. create table person ( person_id int unsigned not null auto_increment, constraint person_pk primary key (person_id), index(person_id)); create table address ( address_id int unsigned not null auto_increment, constraint address_pk primary key (address_id), index(address_id)); create table person_address ( person_id int unsigned not null, address_id int unsigned not null); -- This statement works. alter table person_address add constraint person_person_address_FK1 foreign key (person_id) references person (person_id); -- This statement fails. alter table person_address add constraint address_person_address_FK1 foreign key (address_id) references address (address_id); Replies may be sent to [EMAIL PROTECTED] Thank you! Steve
RE: Server Configuration Help
In your my.cnf there is no: Query_cache_size - http://dev.mysql.com/doc/mysql/en/Query_Cache_Configuration.html Thread_cache_size - http://dev.mysql.com/doc/mysql/en/Server_system_variables.html Marc. -Message d'origine- De : ManojSW [mailto:[EMAIL PROTECTED] Envoyé : lundi 6 décembre 2004 09:21 À : [EMAIL PROTECTED] Objet : Server Configuration Help Greetings, I am running MySQL (version 4.0.15 max) database on Linux (RH9) box. This linux box is a dedicated database server with following h/w configuration: CPU: 2 * 2.4 Ghz Xeon Processor, 512 K 533 FSB Ram :6GB Hdd:36GB * 5 raid config Typically, this database has less number of client connections but those who connect generally run highly analytical stuff off the database. Also the database size is pretty huge (around 40 gb). After reading though the manuals, specifically some of the performance enhancement tips, I build the my.cnf as show below. Now on to the real question, Do you MySQL gurus think that given all the details, Is there anyway to enhance the my.cnf file for better performance/speed ? Your kind help would be greatly appreciated. Best Regards Manoj --- my.cnf file - [client] port=3306 socket=/tmp/mysql.sock [mysqld] user=mysql port=3306 key_buffer=512M table_cache=512 sort_buffer=2M read_buffer_size=4M read_rnd_buffer_size=4M max_connection=100 max_allowed_packet= 1M default-table-type=innodb log_slow_queries=/home/mysql/log/slow.query.log log_error=/home/mysql/log/mysqld.err.log log_long_format # innodb_options innodb_data_home_dir=/usr/local/mysql innodb_data_file_path=ibdata/ibdata1:3G;ibdata/ibdata2:3G:autoextend innodb_mirrored_log_groups=1 innodb_log_group_home_dir=/usr/local/mysql/ibdata/log innodb_log_arch_dir=ibdata/log innodb_log_files_in_group=2 innodb_log_file_size=512M innodb_log_buffer_size=8M innodb_buffer_pool_size=1G innodb_additional_mem_pool_size=4M innodb_flush_log_at_trx_commit=0 innodb_flush_method=O_DIRECT --- End of my.cnf file - -- 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]
inodb: large old ibdata1 and multiple tablespaces
Hi! Running version 4.0 we created a fairly big innodb table (10GB, 72.950.601 rows). After upgrading to 4.1.7 we switched to multiple tablespaces. Then somebody ALTERed the table and innodb created a new idb file for the table. So right now we have 3 large files (old ibdata1|2 and the new *.ibd file). How can I get rid of the old main ibdata files? The documentation in 15.8 talks about deleting the file and recovering from a mysqldump. Is this still necessary, when I'm using multiple tablespaces? All the data would still be in the new .ibd file. Can anyone tell me, what would happen if I deleted the main ibdata1 file but left the .ibd intact? Would innodb recover gracefully? bye, Paul. -- Paul Mallach ARIVA.DE AG Ostseekai 2 D - 24103 Kiel Tel: +49 (0)431/97108-24 E-Mail: [EMAIL PROTECTED] Fax: +49 (0)431/97108-29 Internet: http://www.ariva.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Admin Keeps crashing on FC3
Hi all! I recently updated my system to FC3, i installed MySQL Administrator from the mysql.com binaries, but they keep crashing every time. This is the error message: [EMAIL PROTECTED] ~]$ /opt/mysql-administrator/bin/mysql- administrator *** glibc detected *** free(): invalid pointer: 0x08522568 *** /opt/mysql-administrator/bin/mysql-administrator: line 9: 4720 Aborted $MYPATH/mysql-administrator-bin [EMAIL PROTECTED] ~]$ Does any body can help me out with this one? Best Regards -- Victor Medina M. Linux - Java - MySQL Telf.: 0241-8507325 Ext.: 325 Cell.: 0412-3640959 mail: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug? Can't create/write to file '/root/tmp/ibu6vdue' (Errcode: 13)
Heikki I do it, in my.cnf on [mysqld] section I add: tmpdir=/tmp then I try: /usr/bin/mysqld_safe --tmpdir=/tmp --datadir=/var/lib/mysql --user=mysql with the same result If I change TMPDIR enviroment variable to /tmp works fine. I forgot to say I install mysql from rpm. Alejandro On Fri, 3 Dec 2004 22:01:37 +0200, Heikki Tuuri [EMAIL PROTECTED] wrote: Alejandro, - Original Message - From: Alejandro D. Burne [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, December 03, 2004 7:20 PM Subject: Bug? Can't create/write to file '/root/tmp/ibu6vdue' (Errcode: 13) After installing MySQL 5.0.2 on MDK10.0 mysqld doesn't start with error: Can't create/write to file '/root/tmp/ibu6vdue' (Errcode: 13) InnoDB, and mysqld in general, must be able to create temporary files. http://dev.mysql.com/doc/mysql/en/Temporary_files.html MySQL uses the value of the TMPDIR environment variable as the pathname of the directory in which to store temporary files. If you don't have TMPDIR set, MySQL uses the system default, which is normally `/tmp', `/var/tmp', or `/usr/tmp'. If the filesystem containing your temporary file directory is too small, you can use the --tmpdir option to mysqld to specify a directory in a filesystem where you have enough space. I think it's a problem with innodb, if I add skip-innodb on my.cnf mysqld starts up: 041203 14:04:01 mysqld started 041203 14:04:01 [Warning] Asked for 196608 thread stack, but got 126976 /usr/sbin/mysqld: ready for connections. Version: '5.0.2-alpha-standard-log' socket: '/var/lib/mysql/mysql.sock' port: But when I enable innodb (#skip-innodb on my.cnf); 041203 14:04:56 mysqld started 041203 14:04:56 [Warning] Asked for 196608 thread stack, but got 126976 ./usr/sbin/mysqld: Can't create/write to file '/root/tmp/ibu6vdue' (Errcode: 13) 041203 14:04:56 InnoDB: Error: unable to create temporary file; errno: 13 041203 14:04:56 [ERROR] Can't init databases 041203 14:04:56 [ERROR] Aborting 041203 14:04:56 [Note] /usr/sbin/mysqld: Shutdown complete 041203 14:04:56 mysqld ended The only way to do work innodb is chmod 777 /root and /root/tmp, =( Alejandro 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 technical support from https://order.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: Yet another LEFT JOIN question
Try something like this SELECT A1.ID, SUM(IF(ISNULL(C.AdID),0,1)) AS Clicks, SUM(IF(ISNULL(V.AdID),0,1)) AS Views FROM Ads A1 LEFT JOIN Clicks C ON A1.ID = C.AdID LEFT JOIN Views V ON A1.ID = V.AdID GROUP BY A1.ID -Original Message- From: Ron Gilbert [mailto:[EMAIL PROTECTED] Sent: Saturday, December 04, 2004 2:09 PM To: [EMAIL PROTECTED] [EMAIL PROTECTED] Subject: Yet another LEFT JOIN question I have three tables, 'Ads' is a list of ads, 'Clicks' is a simple list of every time a Ad was clicked on with the Ads ID, and 'Views' is a simple list of views that ad got, with the Ads ID. I am trying to SELECT a list of all the ads, with a count for clicks and a count for views, but my LEFT JOIN is not producing what I thought. If the click count is 0, then the view count is OK, but if not, then the Click count and view count are equal, but a much too large number. If I just SELECT for views or clicks, then it works OK, it's when they are combined that it falls apart. SELECT A1.ID, Count(C.AdID) AS Clicks, Count(V.AdID) AS Views FROM Ads A1 LEFT JOIN Clicks C ON A1.ID = C.AdID LEFT JOIN Views V ON A1.ID = V.AdID group by A1.ID CREATE TABLE `Clicks` ( `AdID` int(10) NOT NULL default '0' [snip] ) CREATE TABLE `Views` ( `AdID` int(10) NOT NULL default '0' [snip] ) CREATE TABLE `Ads` ( `ID` int(10) NOT NULL default '0' [snip] ) I have tried a lot of combinations for LEFT JOIN with no luck. I've read all the posts on this list and they don't seem to be doing what I am, or else I'm not seeing it. Thanks, Ron -- 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: Foreign Key Error 1005:150
Dear Steve! You must set the column address_id as primary key in the table person_address. That should solve your problem. Generally table, you want to join with foreign key, should have primary key. The primary key should include the column that you use for the foreign key. Also, you should have an index on the table including this column. This column should be the first in the index. If the primary key is equal with this column, you must not create an index. Hope this will help you. Regards Bela, Kocsis -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Sunday, December 05, 2004 2:42 PM To: [EMAIL PROTECTED] Subject: Foreign Key Error 1005:150 I am unable to define a foreign key with the following three tables. I am unable to find the error having searched the documentation and tried several variations. Note that I created the first two tables with and without the index clause in the table ddl with no difference in outcome. The three tables and the first foreign key, person_person_address_FK1, create properly. The second foreign key, address_person_address_FK1, causes the error. Please help. create table person ( person_id int unsigned not null auto_increment, constraint person_pk primary key (person_id), index(person_id)); create table address ( address_id int unsigned not null auto_increment, constraint address_pk primary key (address_id), index(address_id)); create table person_address ( person_id int unsigned not null, address_id int unsigned not null); -- This statement works. alter table person_address add constraint person_person_address_FK1 foreign key (person_id) references person (person_id); -- This statement fails. alter table person_address add constraint address_person_address_FK1 foreign key (address_id) references address (address_id); Replies may be sent to [EMAIL PROTECTED] Thank you! Steve --- Ez az elektronikus zenet s minden csatolt file bizalmas informcit tartalmaz, kizrlag a cmzett/ek rszre, gy ha valamilyen hiba folytn tves helyre rkezne meg, krjk trlje az zenetet. Ilyen esetben nem jogosult az elektronikus zenetet s a csatolt file-okat brmilyen mdon felhasznlni, nem teheti kzz s nem msolhatjak le azokat. Valamennyi elektronikus zenet, amelyet a dm Kft-hez, vagy annak munkavllaljhoz cmeztek, vagy onnan kldtek, zleti jellegnek tekintend. Ennek megfelelen az zenet kldje vagy cmzettje hozzjrul ahhoz, hogy az dm Kft. msik, az eredeti cmzettl vagy kldtl eltr vezetje vagy alkalmazottja ltal az zenet megismerhet legyen annak rdekben, hogy a dm Kft. tevkenysgnek folyamatossga s felgyelete biztosthat legyen. A dm Kft. nem vllal felelssget az informcik hibtlan s teljes kzvettsrt, illetve az elektronikus zenet vrusmentessgrt. Azon elektronikus zenetek tartalma, amelyek nem vonatkoznak a dm Kft. zleti mkdsre, a dm Kft. hivatalos mkdse krben sem kiadsra sem jvhagysra nem kerltek. --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup problems
Hello. What version of MySQL Administrator and operating system do you use? I haven't found any open bugs similar to yours. Are you sure that your database contains data? Steve Grosz [EMAIL PROTECTED] wrote: I am using the MySql Administrator tool to schedule weekly backups on my databases. I have defined the databases I want backed up and how often, plus where to store the data. I ran a sample, but it appears that just the structure is being backed up, not the data in the tables as well. How do you define this? What am I missing? Steve -- 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: Load data question in cross database replication
Hello. --replicate-rewrite-db is not taken into account while executing LOAD DATA FROM MASTER. See: http://dev.mysql.com/doc/mysql/en/LOAD_DATA_FROM_MASTER.html Sanjeev Sagar [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: Cannot GRANT REPLICATION SLAVE
Hello. Looks strange. Have you upgraded from 3.xx to 4.xx? If so, then may be you forgot to run mysql_fix_privilege_tables script. Your mysql client shows 4.0.21 version, but check the exact version: select version(); Send us your my.cnf file, output of show variables. Can you reproduce such error on latest release? Batara Kesuma [EMAIL PROTECTED] wrote: I can grant another previleges, but not replication slave and replication clients. How do I fix this? Thank you in advance. I checked my mysql.user table, and I just found out that it didn't have Repl_slave_priv and Repl_client_priv columns. How is this possible? mysql select * from mysql.user\G *** 14. row *** Host: 192.168.1.32 User: backup1 Password: 2cd93c3e746362cf Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N That is all the colums in mysql.user. Please help, and thank you in advance. -- 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: MySQL 4.1.7: cast(1-2, unsigned) != cast('18446744073709551615' as unsigned)
Hello.
I've submitted a bug http://bugs.mysql.com/7036.
Robin Bryce [EMAIL PROTECTED] wrote:
Hi,
I'm having trouble converting to and from strings that represent
unsigned BIGINT's.
My server is MySQL 4.1.7-standard and
the following selects were entered at the prompt of my client with
version mysql Ver 14.7 Distrib 4.1.7, for pc-linux (i686).
select cast(1-2, unsigned);
gives the expected 18446744073709551615. This is one of the 'cast'
examples on http://dev.mysql.com/doc/mysql/en/Cast_Functions.html.
select cast(18446744073709551615 as char);
yields the expected '18446744073709551615'.
The converse doesn't hold:
cast('18446744073709551615' as unsigned);
yields '9223372036854775807' better known as '0x7fff'.
Can anyone tell me what is happening here ?
Thanks,
Robin Bryce
--
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]
Temporary tables rights
Time ago I submit a post about temp tables and rights privileges and it's in my head still. There is an user privilege to create temporary table (create_tmp_table_priv) but when the owner of the table need drop this table can't do (if have drop priv can, but it's a bomb time). Someone have an idea how to deal with this? Alejandro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb: TRUNCATE vs. DELETE FROM
Hi! Is TRUNCATE optimized for innodb tables in MySQL 4.1.7? http://dev.mysql.com/doc/mysql/en/TRUNCATE.html says: For InnoDB, TRUNCATE TABLE is mapped to DELETE, so there is no difference.. But http://dev.mysql.com/doc/mysql/en/InnoDB_tuning.html claims: Beware also of other big disk-bound operations. Use DROP TABLE or TRUNCATE TABLE (from MySQL 4.0 up) to empty a table, not DELETE FROM tbl_name. bye, Paul. -- Paul Mallach ARIVA.DE AG Ostseekai 2 D - 24103 Kiel Tel: +49 (0)431/97108-24 E-Mail: [EMAIL PROTECTED] Fax: +49 (0)431/97108-29 Internet: http://www.ariva.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Yet another LEFT JOIN question
Ron, What's happening is that, when there are clicks and views for an ad, you are getting the number of clicks TIMES the number of views. A quick and dirty solution is to put a column, say id, in clicks which is different for each click, and similarly for views. Then, you can change your counts to count(distinct clicks.id) and count(distinct views.id). Note that, internally, MySQL will still find all of the (click, view) pairs, then sort them and remove duplicates--this may or may not be a problem, depending on usage. If you are using 4.1 or later, you could do a subquery to count the clicks, then left join that with the views. HTH Bill From: Ron Gilbert [EMAIL PROTECTED] Subject: Yet another LEFT JOIN question Date: Sat, 4 Dec 2004 12:08:43 -0800 I have three tables, 'Ads' is a list of ads, 'Clicks' is a simple list of every time a Ad was clicked on with the Ads ID, and 'Views' is a simple list of views that ad got, with the Ads ID. I am trying to SELECT a list of all the ads, with a count for clicks and a count for views, but my LEFT JOIN is not producing what I thought. If the click count is 0, then the view count is OK, but if not, then the Click count and view count are equal, but a much too large number. If I just SELECT for views or clicks, then it works OK, it's when they are combined that it falls apart. SELECT A1.ID, Count(C.AdID) AS Clicks, Count(V.AdID) AS Views FROM Ads A1 LEFT JOIN Clicks C ON A1.ID = C.AdID LEFT JOIN Views V ON A1.ID = V.AdID group by A1.ID CREATE TABLE `Clicks` ( `AdID` int(10) NOT NULL default '0' [snip] ) CREATE TABLE `Views` ( `AdID` int(10) NOT NULL default '0' [snip] ) CREATE TABLE `Ads` ( `ID` int(10) NOT NULL default '0' [snip] ) I have tried a lot of combinations for LEFT JOIN with no luck. I've read all the posts on this list and they don't seem to be doing what I am, or else I'm not seeing it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB tablespace Question.
Hello! I have what seems to be a trivial question, but have not been able to find a definite answer and your help would be greatly appreciated. Question: When creating InnoDB table spaces, are there any advantages to using multi table spaces for each table or is it better to create a few large table spaces for all tables? If the latter, then is it best to create a very large table space, say 30G, (my OS supports LFS) rather then using the auto extend feature for table spaces in InnoDB? What is the over head of the InnoDB auto extend? Thanks! --Dave J. David W. Juntgen Medical Informatics Engineering Inc. Phone: 260.459.6270 Fax : 260.459.6271
Re: Yet another LEFT JOIN question
If you are using 4.1 or later, you could do a subquery to count the clicks, then left join that with the views. I am using 4.1. I tried to do a sub-query, but never got it run. Can you give me a quick example? Is the sub-query a better (faster) way to do this? Ron On Dec 6, 2004, at 6:19 AM, Bill Easton wrote: Ron, What's happening is that, when there are clicks and views for an ad, you are getting the number of clicks TIMES the number of views. A quick and dirty solution is to put a column, say id, in clicks which is different for each click, and similarly for views. Then, you can change your counts to count(distinct clicks.id) and count(distinct views.id). Note that, internally, MySQL will still find all of the (click, view) pairs, then sort them and remove duplicates--this may or may not be a problem, depending on usage. If you are using 4.1 or later, you could do a subquery to count the clicks, then left join that with the views. HTH Bill From: Ron Gilbert [EMAIL PROTECTED] Subject: Yet another LEFT JOIN question Date: Sat, 4 Dec 2004 12:08:43 -0800 I have three tables, 'Ads' is a list of ads, 'Clicks' is a simple list of every time a Ad was clicked on with the Ads ID, and 'Views' is a simple list of views that ad got, with the Ads ID. I am trying to SELECT a list of all the ads, with a count for clicks and a count for views, but my LEFT JOIN is not producing what I thought. If the click count is 0, then the view count is OK, but if not, then the Click count and view count are equal, but a much too large number. If I just SELECT for views or clicks, then it works OK, it's when they are combined that it falls apart. SELECT A1.ID, Count(C.AdID) AS Clicks, Count(V.AdID) AS Views FROM Ads A1 LEFT JOIN Clicks C ON A1.ID = C.AdID LEFT JOIN Views V ON A1.ID = V.AdID group by A1.ID CREATE TABLE `Clicks` ( `AdID` int(10) NOT NULL default '0' [snip] ) CREATE TABLE `Views` ( `AdID` int(10) NOT NULL default '0' [snip] ) CREATE TABLE `Ads` ( `ID` int(10) NOT NULL default '0' [snip] ) I have tried a lot of combinations for LEFT JOIN with no luck. I've read all the posts on this list and they don't seem to be doing what I am, or else I'm not seeing it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
wanted: back up script
This is on windows 2000.
I did the following as a temp solution for the full back up of a database. I
know this is not safe and possibly
not complete. Any suggestions (how to lock/unlock a db for read here)?
Thanks
#include stdio.h
#include stdlib.h
int main() {
printf(start backup ESite data\n);
system(tar cf ESite_dt.tar F:/DBData/MySQLdata/ESite);
system(gzip ESite_dt.tar);
system(mv ESite_dt.tar.gz ESite_dt.tgz);
printf(done\n);
return 1;
}
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.289 / Virus Database: 265.4.6 - Release Date: 12/5/2004
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Load data question in cross database replication
Hello Gleb, My question was related to LOAD DATA INFILE, not LOAD DATA FROM MASTER. LOAD DATA INFILE work those slaves which are not using --replicate-rewrite-db. It do not work for those which are using this. Thanks for you reply. -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Friday, December 03, 2004 4:14 AM To: [EMAIL PROTECTED] Subject: Re: Load data question in cross database replication Hello. --replicate-rewrite-db is not taken into account while executing LOAD DATA FROM MASTER. See: http://dev.mysql.com/doc/mysql/en/LOAD_DATA_FROM_MASTER.html Sanjeev Sagar [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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A newbie and his first MySQL schema
Dear list, So I'm having a bit of trouble with my first schema. I'm sure I'm missing something idiotic here, but days of learning MySQL and setting up servers and working in UNIX have kind of fried my brain. Okay, here goes: My project management system includes (among others) two tables: people and projects. I want each project to have a list of people that are authorised to view it. To my mind, the field ought to look a bit like this: -- Authorised list: Chris Kavanagh Joe Schmoe Jane Doe -- But fields can't hold multiple values, can they? And on my schema, it seems to be a many-to-many relationship between the two tables, and I heard that they are the work of the Devil and must be shunned. I'm sure I need to make a new table or something, but I'm not really sure which one. Can anyone help me? Many thanks in advance, CK. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[sql]There was to be a simpeler way
I'm currently using the following query: SELECT SUM(IF(`01`=1, 1,0)) AS 01A, SUM(IF(`01`=2, 2, 0)) AS 01B..., SUM(IF(`55`=1, 1,0)) AS 55A, SUM(IF(`55`=2, 1,0)) AS 55B FROM tableA INNER JOIN tableB ON tableA_ID = tableB_ID INNER JOIN tableCON tableB_aID = tableC_aID INNER JOIN tableD ON tableD_ID =tableC_ID INNER JOIN tableE ON tableD_ID = tableE_ID GROUP BY tableA_ID The result has to the following. There mutiple rows of tableA_ID, if one of the fields 01 in this set has a 0 the result of the group by has to be 0. If one of the result has no 0 but a 1, the result of the group by has to be 1 and finally if there is a 2 in the result, the group by has to be a 2. I'm currently using the query as showned above, and another query to examing the results of above the get the right value. But there has to be a more efficient way (I hope). Can anybody here show me the better way. TIA AC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Locking Issue?
Can someone help, please? We set up a server to handle a coldfusion web application (CFMX 6.1) running against MySQL 4.1.3b-beta on WinNT. When it is a little stretched, we are finding many instances of queries listed as either 'Sending...' or 'Copying...' in the processlist, with the time going ever upwards (last check was at 1000 seconds and rising). All the tables in the database concerned are InnoDB, and none of the queries concerned are, as far as I know, involved in any transaction - they are straight selects (albeit complex ones, perhaps). When this happens, the other requests to the server are inevitably slow, and these seem never to be cleared unless I kill the threads - and I'm not 100% sure how much damage I'm doing in that action. Is this a possible locking issue? If so, how do I get around it. The settings for the server are at default, except where noted. This is the my.ini file: [mysqld] max_connections=1000 basedir=e:/mysql datadir=e:/mysql/data wait_timeout=60 # TR added next 6 lines on 27/07/04, after instal of v4.1.3b old-passwords local-infile query_cache_size=25M query_cache_type=1 set-variable=max_allowed_packet=16M set-variable=key_buffer=8M log-bin= log_slow_queries= [mysql] local-infile=1 Any pointers as to what I may be doing wrong? Please? Yes, I know we should upgrade to 4.1.7, and we will - soon. Cheers Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading 3.23 to 4.1
Hello All, I'm having trouble upgrading 3.23. to 4.1. Since I don't want to break production, I'm trying to start a test instance on the machine. The problem I'm having seems to be a missing .frm file. The following details invocation and the log file. Any help would be greatly appreciated. Thanks in advance, Aaron /opt/csw/mysql4/bin/mysqld_safe --basedir=/opt/csw/mysql4 --datadir=/opt/csw/mysql4/data --port=3307 Log File: 041206 11:19:53 mysqld started 041206 11:19:53 InnoDB: Started 041206 11:19:53 Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 041206 11:19:54 Aborting 041206 11:19:54 InnoDB: Starting shutdown... 041206 11:19:56 InnoDB: Shutdown completed 041206 11:19:56 /opt/csw/mysql4/libexec/mysqld: Shutdown Complete 041206 11:19:56 mysqld ended -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Master_log_pos in replication
Hello All,
I am having problem in finding out the exact position in master bin log
file in replication setup for point-in-time recovery process. Let me
explain the problem in detail.
I have log_position table on all slaves, see the definition below
Table: log_position
Create Table: CREATE TABLE `log_position` (
`host` varchar(60) NOT NULL default '',
`time_stamp` timestamp(14) NOT NULL,
`log_file` varchar(32) default NULL,
`log_pos` int(11) default NULL,
`master_host` varchar(60) default NULL,
`master_log_file` varchar(32) default NULL,
`master_log_pos` int(11) default NULL,
PRIMARY KEY (`host`,`time_stamp`)
) TYPE=MyISAM
I am running a script every minute to insert a record into this table by
using the information from show slave status.
my $mastersql=SHOW MASTER STATUS;
my $slavesql=SHOW SLAVE STATUS;
my $masterinfo = $conn-Hash($mastersql) if (defined $conn);
my $slaveinfo = $conn-Hash($slavesql) if (defined $conn);
my $logfile = $masterinfo-{File} ;
my $logpos = $masterinfo-{Position} ;
my $masterhost = $slaveinfo-{Master_Host} ;
my $masterlogfile = $slaveinfo-{Relay_Master_Log_File} ;
my $masterlogpos = $slaveinfo-{Exec_master_log_pos} ;
Also I am taking log snapshot every hour to my backup server.
I am in a situation where every minute I get 3000 transactions in my
database, mostly inserts.
When I am doing a point-in-time recovery, I am not able to see the
correct position in master_log_file during that minute interval.
My question is that is there any way which can help me if I have to
locate the position in master_log_file during that one minute interval.
I do not have option of running the script 30 sec. We are looking for
something where there is a unique identifier for every record in binlog
file.
Any idea or help will be highly appreciable.
Regards,
Re: Yet another LEFT JOIN question
Try: select id, clicks, count(views.adId) as views from (select ads.id, count(clicks.adId) as clicks from ads inner join clicks on ads.id=clicks.adId group by id) as adsclicks left join views on id=views.adid group by id; Explanation: -- the following gives you a count of clicks for each ad select ads.id, count(clicks.adId) as clicks from ads inner join clicks on ads.id=clicks.adId group by id) -- if you save it to a temporary table, create temporary table adsclicks select ads.id, count(clicks.adId) as clicks from ads inner join clicks on ads.id=clicks.adId group by id) -- you then have a temporary table with a row for each ad and the click counts -- you can then left join that with the views table to get the views count, too. select id, clicks, count(views.adId) as views from adsclicks left join views on id=views.adid group by id; -- the query at the beginning of this message uses a subquery instead of creating and using a temporary table. Is the subquery better or faster? Try it and see--depends partly on whether you have to add a column to identify individual clicks and views. On the one hand, the count(distinct) solution looks at more rows; on the other hand, subqueries may not get as much optimization. I'd claim that the subquery describes better what you want, while the count(distinct) is a kludge to avoid the subquery. - Original Message - From: Ron Gilbert [EMAIL PROTECTED] To: Bill Easton [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, December 06, 2004 11:05 AM Subject: Re: Yet another LEFT JOIN question If you are using 4.1 or later, you could do a subquery to count the clicks, then left join that with the views. I am using 4.1. I tried to do a sub-query, but never got it run. Can you give me a quick example? Is the sub-query a better (faster) way to do this? Ron On Dec 6, 2004, at 6:19 AM, Bill Easton wrote: Ron, What's happening is that, when there are clicks and views for an ad, you are getting the number of clicks TIMES the number of views. A quick and dirty solution is to put a column, say id, in clicks which is different for each click, and similarly for views. Then, you can change your counts to count(distinct clicks.id) and count(distinct views.id). Note that, internally, MySQL will still find all of the (click, view) pairs, then sort them and remove duplicates--this may or may not be a problem, depending on usage. If you are using 4.1 or later, you could do a subquery to count the clicks, then left join that with the views. HTH Bill From: Ron Gilbert [EMAIL PROTECTED] Subject: Yet another LEFT JOIN question Date: Sat, 4 Dec 2004 12:08:43 -0800 I have three tables, 'Ads' is a list of ads, 'Clicks' is a simple list of every time a Ad was clicked on with the Ads ID, and 'Views' is a simple list of views that ad got, with the Ads ID. I am trying to SELECT a list of all the ads, with a count for clicks and a count for views, but my LEFT JOIN is not producing what I thought. If the click count is 0, then the view count is OK, but if not, then the Click count and view count are equal, but a much too large number. If I just SELECT for views or clicks, then it works OK, it's when they are combined that it falls apart. SELECT A1.ID, Count(C.AdID) AS Clicks, Count(V.AdID) AS Views FROM Ads A1 LEFT JOIN Clicks C ON A1.ID = C.AdID LEFT JOIN Views V ON A1.ID = V.AdID group by A1.ID CREATE TABLE `Clicks` ( `AdID` int(10) NOT NULL default '0' [snip] ) CREATE TABLE `Views` ( `AdID` int(10) NOT NULL default '0' [snip] ) CREATE TABLE `Ads` ( `ID` int(10) NOT NULL default '0' [snip] ) I have tried a lot of combinations for LEFT JOIN with no luck. I've read all the posts on this list and they don't seem to be doing what I am, or else I'm not seeing it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: myisamchk sort buffer too small, check table has ran 1 week and no end in sight
Gleb Paharenko wrote: Hello. I've taken this information from documentation at http://dev.mysql.com/doc/mysql/en/myisamchk_syntax.html Did it solve your problem? If didn't, send me you my.cnf file and information about version of MySQL and operating system. The docs are old, they changed the syntax mysql 4.1.7 running on windows 2000 server, dual 2.8 xeon with 1 gig of ram I do not have any myisamchk options specified in the my.ini I let it run for 2 weeks, and I killed it the data file is good, it sat for 2 weeks indexing, at least as far as I can tell is this normal for mysql? I have some bigger tables, guess I better migrate to a database that handles big files quick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A newbie and his first MySQL schema
- Original Message - From: Chris Kavanagh [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 06, 2004 12:11 PM Subject: A newbie and his first MySQL schema Dear list, So I'm having a bit of trouble with my first schema. I'm sure I'm missing something idiotic here, but days of learning MySQL and setting up servers and working in UNIX have kind of fried my brain. Okay, here goes: My project management system includes (among others) two tables: people and projects. I want each project to have a list of people that are authorised to view it. To my mind, the field ought to look a bit like this: -- Authorised list: Chris Kavanagh Joe Schmoe Jane Doe -- But fields can't hold multiple values, can they? And on my schema, it seems to be a many-to-many relationship between the two tables, and I heard that they are the work of the Devil and must be shunned. I'm sure I need to make a new table or something, but I'm not really sure which one. Can anyone help me? First of all, there is no problem with storing a value like Chris Kavanagh in a single column; a column defined as char() or varchar() or even the BLOB should store that data just fine. You just have to make sure that the column is defined large enough to hold the largest value that you expect to store. Of course, it is often a good idea to store the different parts of a name in separate columns so that you can search on them individually. This can also help with your understanding of the data. For instance, some Chinese people I meet give me their last name first and then their first name, e.g. Lee Xian, in the Chinese fashion (Remember that in the name Mao Tse Tung, Mao was his family name, not his first name). Other Chinese people give me their first name first and then their last name, e.g. Xian Lee, the way we usually do in the West. However, if you simply stored Xian Lee (or Lee Xian) in a single column and then had a requirement to return all of the rows were the first name was Lee, you might get confused when it comes to Lee Xian: is Lee his/her first name or family name? This situation could easily happen if the input form that provided the data in the first place simply called for the entire customer name to be entered in a single field. On the other hand, if the form (and the underlying table) separated first name and last name into two separate fields, you also know if Lee was the person's first name or last name. As for your other question, yes, many-to-many relationships are virtually always split into a pair of one-to-many relationships for many good reasons. In your case, you will have a table for people, a table for projects, and a new table, usually called an intersection (or association) table, to show the relationships between people and projects. You're going to end up with something like this: People - one row for each employee, primary key employee ID EmpIDLastnameFirstname... 1KavanaghChris 2Schmoe Joe 3Doe Jane Project - one row for each project, primary key project ID = ProjIDProjName A Marketing System B Shipping System C Purchasing System People_Project - one row for each person/project combination that actually exists == EmpIDProjID 1B 1C 2A 3A 3C In other words, employee 1 works on projects B and C but not A. Employee 2 works only on project A. Employee 3 works on projects A and C but not B. The primary key for the intersection table is the COMBINATION of EmpID and ProjID! Neither column by itself would make sense as the primary key of the table; the EmpID and ProjID need to be combined to form the primary key. It is now possible to store as many projects for an employee as you like but no way to say that Employee 1 is on Project B *twice*. That's exactly what you want. As a bonus feature, the intersection table can have additional columns if that is appropriate. For example, each employee was dedicated to each project for a dedicated percentage of their time, you could put that in the intersection table since it is information about the person/project combination. Then, you might end up with something like this: People_Project == EmpIDProjIDPercent 1B 50 1C 50 2A 100 3A25 3C75 In other words, employee 1 splits there time 50/50 between projects B and C; employee 2 spends all of their time on project A; employee 3 spends 25% of their time on project A and 75% on project C. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Locking Issue?
-Original Message- From: Terry Riley [mailto:[EMAIL PROTECTED] Sent: Monday, December 06, 2004 10:12 AM To: [EMAIL PROTECTED] Subject: Locking Issue? Can someone help, please? We set up a server to handle a coldfusion web application (CFMX 6.1) running against MySQL 4.1.3b-beta on WinNT. When it is a little stretched, we are finding many instances of queries listed as either 'Sending...' or 'Copying...' in the processlist, Sending Data means stream the result set back, mysql found the rows and is still searching. Copying to tmp table means that it's using the tmp_table_size variable and if it busts past that will write to a temp table. Since you using innodb you need to increase your innodb buffer pool. Additionaly increase your tmp_table_size buffer, and verify your queries. You might need to tweak innodb_io_threads a feature specific for windows, and the awe memory setting. You might be system bound. [mysqld] max_connections=1000 basedir=e:/mysql datadir=e:/mysql/data wait_timeout=60 # TR added next 6 lines on 27/07/04, after instal of v4.1.3b old-passwords local-infile query_cache_size=25M query_cache_type=1 set-variable=max_allowed_packet=16M set-variable=key_buffer=8M log-bin= log_slow_queries= [mysql] local-infile=1 Any pointers as to what I may be doing wrong? Please? Yes, I know we should upgrade to 4.1.7, and we will - soon. Cheers Terry Riley -- 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: InnoDB tablespace Question.
Depends on your disk setup. Remember a table space is a virtual filesystem that sits on top of the OS. Having one large file and chopping a contiguous block of the disk out enables better seeks as well as caching if the file doesn't bust the system cache. In your case it will. One file needs to be autoextended else your application will run into errors once the data needs to grow pass the tablespace. Having multiple table spaces on different spindles enable the data to be segmented a bit more getting a few more bits of speed, but at the possible detriment of needed to access both separate data spaces if the data requested spans multiple files. In essence I have found that using multiple table spaces is best used when the disk is starting to fill up and I need to put the data on a different disk. You'll get a constant boost in performance if you put the innodb log files on a different spindle or set of spindles as your data file. -Original Message- From: Dave Juntgen [mailto:[EMAIL PROTECTED] Sent: Monday, December 06, 2004 6:30 AM To: [EMAIL PROTECTED] Subject: InnoDB tablespace Question. Hello! I have what seems to be a trivial question, but have not been able to find a definite answer and your help would be greatly appreciated. Question: When creating InnoDB table spaces, are there any advantages to using multi table spaces for each table or is it better to create a few large table spaces for all tables? If the latter, then is it best to create a very large table space, say 30G, (my OS supports LFS) rather then using the auto extend feature for table spaces in InnoDB? What is the over head of the InnoDB auto extend? Thanks! --Dave J. David W. Juntgen Medical Informatics Engineering Inc. Phone: 260.459.6270 Fax : 260.459.6271 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: wanted: back up script
Um its better to use the SQL backup table if the table is a myISAM
table.
BACKUP TABLE [tables] to [LOC].
Or a more sophisticated approach
FLUSH TABLE WITH READ LOCK;
Fork out copy myISAM datafile out
UNLOCK TABLES;
If it's innodb then use the innodb backup script offered by Heikki.
-Original Message-
From: Elim Qiu [mailto:[EMAIL PROTECTED]
Sent: Monday, December 06, 2004 9:44 AM
To: [EMAIL PROTECTED]
Subject: wanted: back up script
This is on windows 2000.
I did the following as a temp solution for the full back up of a
database. I know this is not safe and possibly not complete. Any
suggestions (how to lock/unlock a db for read here)?
Thanks
#include stdio.h
#include stdlib.h
int main() {
printf(start backup ESite data\n);
system(tar cf ESite_dt.tar F:/DBData/MySQLdata/ESite); system(gzip
ESite_dt.tar); system(mv ESite_dt.tar.gz ESite_dt.tgz);
printf(done\n); return 1; }
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.289 / Virus Database: 265.4.6 - Release Date: 12/5/2004
--
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: A newbie and his first MySQL schema
Don't think of it a column must hold mutiple values (unless your using Sets or bitmasks) think that this table will hold mutiple rows, and each person is a row with permissions for each project. So, a basic approach is forevery authorized project a person is able to see that person has a row indicating that they can use said project. -Original Message- From: Chris Kavanagh [mailto:[EMAIL PROTECTED] Sent: Monday, December 06, 2004 9:11 AM To: [EMAIL PROTECTED] Subject: A newbie and his first MySQL schema Dear list, So I'm having a bit of trouble with my first schema. I'm sure I'm missing something idiotic here, but days of learning MySQL and setting up servers and working in UNIX have kind of fried my brain. Okay, here goes: My project management system includes (among others) two tables: people and projects. I want each project to have a list of people that are authorised to view it. To my mind, the field ought to look a bit like this: -- Authorised list: Chris Kavanagh Joe Schmoe Jane Doe -- But fields can't hold multiple values, can they? And on my schema, it seems to be a many-to-many relationship between the two tables, and I heard that they are the work of the Devil and must be shunned. I'm sure I need to make a new table or something, but I'm not really sure which one. Can anyone help me? Many thanks in advance, CK. -- 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: Locking Issue?
- Original Message - Thanks for those hints, Dathan (see below): -Original Message- From: Terry Riley [mailto:[EMAIL PROTECTED] Sent: Monday, December 06, 2004 10:12 AM To: [EMAIL PROTECTED] Subject: Locking Issue? Can someone help, please? We set up a server to handle a coldfusion web application (CFMX 6.1) running against MySQL 4.1.3b-beta on WinNT. When it is a little stretched, we are finding many instances of queries listed as either 'Sending...' or 'Copying...' in the processlist, Sending Data means stream the result set back, mysql found the rows and is still searching. Copying to tmp table means that it's using the tmp_table_size variable and if it busts past that will write to a temp table. Since you using innodb you need to increase your innodb buffer pool. Additionaly increase your tmp_table_size buffer, and verify your queries. You might need to tweak innodb_io_threads a feature specific for windows, and the awe memory setting. You might be system bound. I've already increased the tmp_table_size a little, but now that hits have trailed off (it's 8pm here), I'll have to wait till tomorrow to test this and other suggestions you've made. [mysqld] max_connections=1000 basedir=e:/mysql datadir=e:/mysql/data wait_timeout=60 # TR added next 6 lines on 27/07/04, after instal of v4.1.3b old-passwords local-infile query_cache_size=25M query_cache_type=1 set-variable=max_allowed_packet=16M set-variable=key_buffer=8M log-bin= log_slow_queries= [mysql] local-infile=1 Any pointers as to what I may be doing wrong? Please? Yes, I know we should upgrade to 4.1.7, and we will - soon. Cheers Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL C API questions
Hi all. I'm using the MySQL C API to interface with my MySQL 4.1 server and I have the following questions: When I do a SELECT of some integer data value what is actually returned is a string representation of the integer value and since I need this integer value in my client I have to convert it. Is it possible to get MySQL to return the actual integer value instead? If not, and I actually have to convert the value every time, I have another related question: Are the values returned zero-terminated? Or do I have to use the lengths that I can fetch with mysql_fetch_lengths? The reason that I ask this question is that if the fields are not zero-terminated I have to copy the values into a temporary buffer, zero-terminate that buffer and the do the conversion every time... This seems like a lot of wasted work ;-) Best regards, Mads Kristensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting a random row
Hi All, I understand that I can get a random row out of a table by doing something like SELECT * FROM fortunes ORDER BY RAND() LIMIT 1 But I have also been told that this is a very slow operation. I am building a script that will display a random saying, user testimonial, whatever, on a web page. Since this is a public page (i.e., not an admin backend), I have to be concerned about speed. What is the best way to get a random row out of a database for this sort of application? It's the sort of thing you see all the time, so I'm sure others have thought about this before. Any pointers? Sincerely, -Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL C API questions
Prepared Statements, only offered in 4.1 API's. http://dev.mysql.com/doc/mysql/en/C_API_Prepared_statements.html David W. Juntgen Medical Informatics Engineering Inc. Phone: 260.459.6270 Fax : 260.459.6271 -Original Message- From: Mads Kristensen [mailto:[EMAIL PROTECTED] Sent: Monday, December 06, 2004 3:32 PM To: [EMAIL PROTECTED] Subject: MySQL C API questions Hi all. I'm using the MySQL C API to interface with my MySQL 4.1 server and I have the following questions: When I do a SELECT of some integer data value what is actually returned is a string representation of the integer value and since I need this integer value in my client I have to convert it. Is it possible to get MySQL to return the actual integer value instead? If not, and I actually have to convert the value every time, I have another related question: Are the values returned zero-terminated? Or do I have to use the lengths that I can fetch with mysql_fetch_lengths? The reason that I ask this question is that if the fields are not zero-terminated I have to copy the values into a temporary buffer, zero-terminate that buffer and the do the conversion every time... This seems like a lot of wasted work ;-) Best regards, Mads Kristensen -- 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: Selecting a random row
[snip] I understand that I can get a random row out of a table by doing something like SELECT * FROM fortunes ORDER BY RAND() LIMIT 1 But I have also been told that this is a very slow operation. I am building a script that will display a random saying, user testimonial, whatever, on a web page. Since this is a public page (i.e., not an admin backend), I have to be concerned about speed. What is the best way to get a random row out of a database for this sort of application? It's the sort of thing you see all the time, so I'm sure others have thought about this before. Any pointers? [/snip] How many rows do you anticipate that the table will have? Have you tested this on your server? I would have to bet that if you have only a few K rows that speed/performance will not be an issue. The way to enhance this is by selecting an indexed value, such as the following where `foo` is indexed SELECT `foo` FROM fortunes ORDER BY RAND() LIMIT 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL C API questions
Hey check the MySQL C API By Example site. http://www.geocities.com/jahan.geo Yes you have to convert the data always and its zero terminated. Mads Kristensen wrote: Hi all. I'm using the MySQL C API to interface with my MySQL 4.1 server and I have the following questions: When I do a SELECT of some integer data value what is actually returned is a string representation of the integer value and since I need this integer value in my client I have to convert it. Is it possible to get MySQL to return the actual integer value instead? If not, and I actually have to convert the value every time, I have another related question: Are the values returned zero-terminated? Or do I have to use the lengths that I can fetch with mysql_fetch_lengths? The reason that I ask this question is that if the fields are not zero-terminated I have to copy the values into a temporary buffer, zero-terminate that buffer and the do the conversion every time... This seems like a lot of wasted work ;-) Best regards, Mads Kristensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Selecting a random row
How many rows do you anticipate that the table will have? Have you tested this on your server? I would have to bet that if you have only a few K rows that speed/performance will not be an issue. I doubt I will have more than 100. Perhaps I shouldn't worry about it, then. The way to enhance this is by selecting an indexed value, such as the following where `foo` is indexed SELECT `foo` FROM fortunes ORDER BY RAND() LIMIT 1 Why does this help? From the MySQL book I have, the reason ORDER BY RAND() is slow is because for each record in the table a random number must be generated. Then all random numbers are sorted so that the first n records can be returned. Is this correct? If so, how does selecting an indexed column help this, won't it still need to perform all those operations (generate rand-nums, then sort) regardless of the index? Jfyi the book I am referring to is MySQL by Michael Kofler. I'd give the Amazon link but they appear to be down at the moment. The ISBN is 1-893115-57-7 for any who want to look it up elsewhere/when amazon.com comes back up. -jb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Selecting a random row
[snip] The way to enhance this is by selecting an indexed value, such as the following where `foo` is indexed SELECT `foo` FROM fortunes ORDER BY RAND() LIMIT 1 Why does this help? From the MySQL book I have, the reason ORDER BY RAND() is slow is because for each record in the table a random number must be generated. Then all random numbers are sorted so that the first n records can be returned. Is this correct? If so, how does selecting an indexed column help this, won't it still need to perform all those operations (generate rand-nums, then sort) regardless of the index? [/snip] Using indexes for selection criteria does have an impact, even with RAND. On a table such as yours it shouldn't matter due to the small size. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Clustering and a large database
I beginning to use MySQL clustering abilities for a large records keeping solution. I have installed 4.1.7 with the clustering components. The ndbd and ndb_mgmd processes are running. I can create the database and tables using the ndb engine. I have started to import our data. I gather from the manual that tables are stored in RAM. I am trying to import a database with 11 tables with about 7 million rows. If I follow the math in the manual, one row will use 32KB, I would need 224 TB of RAM. Does this make sense or am I way off? In version 4.0, the data length of my largest table is 2,671,788,032 (I assume bytes, using SHOW TABLE STATUS), about 2.5 GB. So my entire version 4.0 database is about 3 GB is size. kib -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie, MySQL test fails, spent hours, please help
Hey.
The code that I'm trying to get to work is as follows. The problem is
with the 7th, 8th, and 9th lines...
html
head
titleTest MySQL/title
body
!-- mysql_up.php --
?php
$host=;
$user=;
$password= ;
mysql_connect($host,$user,$password);
$sql=show status;
$result = mysql_query($sql);
if ($result == 0)
echo(bError . mysql_errno() . : . mysql_error() . /b);
elseif (mysql_num_rows($result) == 0)
echo(bQuery executed successfully!/b);
else
{
?
!-- Table that displays the results --
table border=1
trtdbVariable_name/b/tdtdbValue/b/td/tr
?php
for ($i = 0; $i mysql_num_rows($result); $i++) {
echo(TR);
$row_array = mysql_fetch_row($result);
for ($j = 0; $j mysql_num_fields($result); $j++) {
echo(TD . $row_array[$j] . /td);
}
echo(/tr);
}
?
/table
?php } ?
/body
/html
The server that I'm uploading to is www.allbutnothing.com. I know my
computer name, is the correct form for the host value
computername.webserver.com?? How do I find my username and password,
the only one I entered was the one when MySQLadmin.exe installed... is
that the username and password that should be used?
Thanks very much.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database values to variables
I need to convert database values into php variables. Let me explain: i.e. select firstbase, secondbase, thirdbase, home from allstars where firstbase = 122; Now I want to use all those field names as variables with the same record information they would have if you just ran the above statement. i.e $fb = $firstbase $sb = $secondbase $tb = $thirdbase I just can't figure out how to do this. I've already driven the people in php-db mad. j/k What I need is to pull variables out for an email. Having the damdest time doing so. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT based on TIMESTAMP (DATETIME)
Hello! I need to pull records from the db where the timestamp column (2004-11-01 00:00:00) greater than November 1, 2004 WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) = a.createdate doesn't work DATE_SUB(CURDATE(),INTERVAL 30 DAY) = a.createdate doesn't work a.createdate '2004-11-01 00:00:00' doesn't work a.createdate LIKE '2004-11%' OR a.createdate LIKE '2004-12%' doesn't work MySQL 4.1.5-gamma-standard Thanks for any help R. Whitney Transcend Development Producing the next phase of your internet presence http://xend.net Premium Quality Web Hosting http://hosting.xend.net rw AT xend.net Net Binder http://netbinder.net 310-943-6498 602-288-5340 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Clustering and a large database
Klaus Berkling [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I have started to import our data. I gather from the manual that tables are stored in RAM. I am trying to import a database with 11 tables with about 7 million rows. If I follow the math in the manual, one row will use 32KB, I would need 224 TB of RAM. What part of the manual leads you to believe that the entire contents of every table needs to be stored in RAM? I was not aware of this requirement. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error documentation
Probably a real old question, but where can I go to look up a numbered error, as in ERROR 1005 at line 333502: Can't create table './warehouse/tblGTprojConfigs.frm' (errno: 150) thanks titus sends -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Clustering and a large database
Yes, mysql clustering is a ram only database. It does not make sense to use it if you have a very large database. You can use master/slave functionality and use whatever table type you like. But using the newer clustering technology you have no choice but to use the ndb table type which is ram only. On Mon, 2004-12-06 at 16:53 -0500, Joshua Beall wrote: Klaus Berkling [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I have started to import our data. I gather from the manual that tables are stored in RAM. I am trying to import a database with 11 tables with about 7 million rows. If I follow the math in the manual, one row will use 32KB, I would need 224 TB of RAM. What part of the manual leads you to believe that the entire contents of every table needs to be stored in RAM? I was not aware of this requirement. -- John A. McCaskey Software Development Engineer Klir Technologies, Inc. [EMAIL PROTECTED] 206.902.2027 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Clustering and a large database
-Original Message- From: John McCaskey [mailto:[EMAIL PROTECTED] Sent: Monday, December 06, 2004 17:01 To: Joshua Beall Cc: [EMAIL PROTECTED] Subject: Re: Clustering and a large database Yes, mysql clustering is a ram only database. It does not make sense to use it if you have a very large database. You can use master/slave functionality and use whatever table type you like. But using the newer clustering technology you have no choice but to use the ndb table type which is ram only. Once the clustering functionality is available with other table types (which are not RAM only), then clustering will be feasible for larger databases? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database values to variables
[snip]
I need to convert database values into php variables.
Let me explain:
i.e.
select firstbase, secondbase, thirdbase, home from
allstars where firstbase = 122;
Now I want to use all those field names as variables
with the same record information they would have if
you just ran the above statement.
i.e
$fb = $firstbase
$sb = $secondbase
$tb = $thirdbase
[/snip]
Here is the code --
?php
$sql = select firstbase, secondbase, thirdbase, home from allstars
where firstbase = '122' ;
if(!($returnedData = mysql_query($sql, $yourDatabaseConnection))){
echo mysql_error() . \n;
exit();
}
while($row = mysql_fetch_array($returnedData)){
/* here are your variables */
$fb = $row['firstbase'];
$sb = $row['secondbase'];
$tb = $row['thirdbase'];
/* ...and so on ...*/
}
echo $fb . br\n; //print out to the screen
echo $sb . br\n; //print out to the screen
echo $tb . br\n; //print out to the screen
?
My suggestion is that you work some basic PHP/MySQL tutorials and use
the PHP general list ([EMAIL PROTECTED]) for questions like
this. Make sure you have RTFM, STFA, and STFW before posting, those guys
can be merciless.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database values to variables
--- Jay Blanchard [EMAIL PROTECTED] wrote: My suggestion is that you work some basic PHP/MySQL tutorials and use the PHP general list ([EMAIL PROTECTED]) for questions like this. Make sure you have RTFM, STFA, and STFW before posting, those guys can be merciless. Jay, thank you and I more then appreciate the code. I will learn something from this. Now what is STFA and STFW ? I won an acronym contest 2 years ago. I'm going downhill. PHP list, pt...I can handle it. Aren't you on the list ? Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Clustering and a large database
On Dec 6, 2004, at 1:53 PM, Joshua Beall wrote: Klaus Berkling [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I have started to import our data. I gather from the manual that tables are stored in RAM. I am trying to import a database with 11 tables with about 7 million rows. If I follow the math in the manual, one row will use 32KB, I would need 224 TB of RAM. What part of the manual leads you to believe that the entire contents of every table needs to be stored in RAM? I was not aware of this requirement. This is the part in the manual: http://dev.mysql.com/doc/mysql/en/MySQL_Cluster_DB_Definition.html Look for the section on DataMemory. I was hoping that some swapping would occur... kib -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error documentation
- Original Message - From: Titus [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 06, 2004 4:53 PM Subject: Error documentation Probably a real old question, but where can I go to look up a numbered error, as in ERROR 1005 at line 333502: Can't create table './warehouse/tblGTprojConfigs.frm' (errno: 150) If you go to http://dev.mysql.com/doc/ and click on the second link under MySQL Reference Manual - Searchable, with user comments - then enter a search term in the search box on the resulting screen, you should find what you want. If you search on '1005', the first hit takes you to a page http://dev.mysql.com/doc/mysql/en/Error-handling.html which won't tell you much that you don't already know. However, if you look at some of the subsequent hits, or if you search on '150', you'll get one hit that takes you to this page: http://dev.mysql.com/doc/mysql/en/InnoDB_error_codes.html. There, you will find an explanation of error 1005, errno 150. Of course, that is only relevant if your table is using the InnoDB engine. If you *aren't* using InnoDB, one of the other hits may answer your question. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7: cast(1-2, unsigned) != cast('18446744073709551615' as unsigned)
Ah, Excelent. Thanks for looking at this. The use context that exposed
this was using uuids as primary keys. I was breaking the result of
uuid() into two parts, and then storing into a pair of bigint unsigned
fields that formed a composite primary key:
CREATE TABLE `test`.`uuidkeys` (
`uuidlo` bigint(20) unsigned NOT NULL default '0',
`uuidhi` bigint(20) unsigned NOT NULL default '0',
`meta_data` varchar(255) default NULL,
PRIMARY KEY (`uuidlo`,`uuidhi`)
) ENGINE=InnoDB;
set @uuidkey=uuid();
set @uuidkey='----';
insert into test.uuidkeys (uuidlo, uuidhi) values (
conv(left(replace(convert(@uuidkey using
latin1),'-',''),16),16,10),conv(right(replace(convert(@uuidkey using
latin1),'-',''),16),16,10));
select * from `test`.`uuidkeys` where uuidlo = 18446744073709551615;
+--+--+---+
| uuidlo | uuidhi | meta_data |
+--+--+---+
| 18446744073709551615 | 18446744073709551615 | NULL |
+--+--+---+
select * from `test`.`uuidkeys` where uuidlo =
cast('18446744073709551615' as unsigned);
Empty set (0.00 sec)
Prety new to DB/SQL stuff so no idea if paired bigints offer any real
advantage over char(32) in this case; Comments on this most welcome! My
life is easier now I'm using the latter.
Cheers,
Robin
Gleb Paharenko wrote:
Hello.
I've submitted a bug http://bugs.mysql.com/7036.
Robin Bryce [EMAIL PROTECTED] wrote:
Hi,
I'm having trouble converting to and from strings that represent
unsigned BIGINT's.
My server is MySQL 4.1.7-standard and
the following selects were entered at the prompt of my client with
version mysql Ver 14.7 Distrib 4.1.7, for pc-linux (i686).
select cast(1-2, unsigned);
gives the expected 18446744073709551615. This is one of the 'cast'
examples on http://dev.mysql.com/doc/mysql/en/Cast_Functions.html.
select cast(18446744073709551615 as char);
yields the expected '18446744073709551615'.
The converse doesn't hold:
cast('18446744073709551615' as unsigned);
yields '9223372036854775807' better known as '0x7fff'.
Can anyone tell me what is happening here ?
Thanks,
Robin Bryce
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Selecting a random row
Pseudo code:
$min = SELECT MIN(id) from fortunes;
$max = SELECT MAX(id) from fortunes;
While (!$row $count 3) {
$id = rand $max + $min;
if ($id $max) {
next;
}
$row = SELECT * from fortunes where id = $id;
$count++
}
If ($count = 3) {
return 1st row;
}
-Original Message-
From: news [mailto:[EMAIL PROTECTED] On Behalf Of Joshua Beall
Sent: Monday, December 06, 2004 12:40 PM
To: [EMAIL PROTECTED]
Subject: Selecting a random row
Hi All,
I understand that I can get a random row out of a table by doing
something like
SELECT * FROM fortunes ORDER BY RAND() LIMIT 1
But I have also been told that this is a very slow operation.
I am building a script that will display a random saying, user
testimonial, whatever, on a web page. Since this is a public page
(i.e., not an admin backend), I have to be concerned about speed. What
is the best way to get a random row out of a database for this sort of
application? It's the sort of thing you see all the time, so I'm sure
others have thought about this before. Any pointers?
Sincerely,
-Josh
--
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: Newbie, MySQL test fails, spent hours, please help
Aaron,
You aren't trapping errors on mysql_connect().
Try this:
mysql_connect($host,$user,$password)
or die(mysql_error());
-Eric
On Mon, 6 Dec 2004 19:13:49 +0100, Aaron Ford [EMAIL PROTECTED] wrote:
Hey.
The code that I'm trying to get to work is as follows. The problem is
with the 7th, 8th, and 9th lines...
html
head
titleTest MySQL/title
body
!-- mysql_up.php --
?php
$host=;
$user=;
$password= ;
mysql_connect($host,$user,$password);
$sql=show status;
$result = mysql_query($sql);
if ($result == 0)
echo(bError . mysql_errno() . : . mysql_error() . /b);
elseif (mysql_num_rows($result) == 0)
echo(bQuery executed successfully!/b);
else
{
?
!-- Table that displays the results --
table border=1
trtdbVariable_name/b/tdtdbValue/b/td/tr
?php
for ($i = 0; $i mysql_num_rows($result); $i++) {
echo(TR);
$row_array = mysql_fetch_row($result);
for ($j = 0; $j mysql_num_fields($result); $j++) {
echo(TD . $row_array[$j] . /td);
}
echo(/tr);
}
?
/table
?php } ?
/body
/html
The server that I'm uploading to is www.allbutnothing.com. I know my
computer name, is the correct form for the host value
computername.webserver.com?? How do I find my username and password,
the only one I entered was the one when MySQLadmin.exe installed... is
that the username and password that should be used?
Thanks very much.
--
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.bleated.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem using debug switch with mysqlimport
I'm trying to utilize the debug switch with mysqlimport so that I can figure out why I'm getting errors on the data I'm importing, but I have yet to figure out a way to do this. I've tried to read the sparse documentation concerning this feature and no debug file is ever produced. I've tried using it as indicated by the mysqlimport --help documentation such as: /usr/local/mysql/bin/mysqlimport --debug=d:t:o,mysql.dbg -uuser -ppassword --local database import_file As well as many variations of this. I've been unable to locate through Google or anything else anyone who has successfully utilized this feature. Does anyone out there have any input? Thanks. Aaron
RE: Error documentation
If you are running Linux, try. ]$ perror errno errno being the number of the error. David W. Juntgen Medical Informatics Engineering Inc. Phone: 260.459.6270 Fax : 260.459.6271 -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Monday, December 06, 2004 5:17 PM To: Titus; [EMAIL PROTECTED] Subject: Re: Error documentation - Original Message - From: Titus [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 06, 2004 4:53 PM Subject: Error documentation Probably a real old question, but where can I go to look up a numbered error, as in ERROR 1005 at line 333502: Can't create table './warehouse/tblGTprojConfigs.frm' (errno: 150) If you go to http://dev.mysql.com/doc/ and click on the second link under MySQL Reference Manual - Searchable, with user comments - then enter a search term in the search box on the resulting screen, you should find what you want. If you search on '1005', the first hit takes you to a page http://dev.mysql.com/doc/mysql/en/Error-handling.html which won't tell you much that you don't already know. However, if you look at some of the subsequent hits, or if you search on '150', you'll get one hit that takes you to this page: http://dev.mysql.com/doc/mysql/en/InnoDB_error_codes.html. There, you will find an explanation of error 1005, errno 150. Of course, that is only relevant if your table is using the InnoDB engine. If you *aren't* using InnoDB, one of the other hits may answer your question. Rhino -- 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: Foreign Key Error 1005:150
Steve, - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, December 06, 2004 1:00 PM Subject: Re: Foreign Key Error 1005:150 --NextPart_Webmail_9m3u9jl4l_14802_1102330771_0 Content-Type: text/plain Content-Transfer-Encoding: 8bit Michael, Thank you for your reply. Here is a bit more info. I changed the default table type to innodn in the my.ini file before creating the database, so all tables are innodb. I tried the create statements with and without explicit index clauses with all permutations - same result each time. I agree that something is wrong. Did you try running the ddl you suggested below? If so, did it work for you? I downloaded the latest release from thr ANL mirror which says v 4.1.2 in the file name. When I run MySQL, the system says it is 4.0.22. Is the engine version different than the release version? This is a secondaary issue however. if the server says it is 4.0.22, then you are running 4.0.22. Please post the COMPLETE output of the mysql client when you try the problematic command sequence, and after that run SHOW INNODB STATUS. It prints a detailed description of the latest FOREIGN KEY error. Since you are running 4.0.22, MySQL does not automatically create indexes on FOREIGN KEYs, and your command sequence is indeed expected to fail. Steve 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 -- Original message -- Something is wrong, but it's hard to say what. It seems unlikely you entered exactly those commands and got an error only on the last ALTER TABLE. First, you need InnoDB tables to support foreign keys, but you don't specify the table engine in your CREATE statements. The default is MyISAM, unless you've changed it. But that's not it. If they were MyISAM tables, neither ALTER would work, but if they're all InnoDB, then all should work. Is it possible that just table address is MyISAM? In order to create a foreign key, you must have an index on the columns on each side of the relationship. That is, you need person_id and address_id to be indexed in both tables. Prior to 4.1.2, you had to do that by hand, but in 4.1.2 and later it's automatic. Again, all or nothing, so not likely relevant here. Some other things to note (which are unrelated to the error): There is no need to put an index on a column which has already been indexed as the primary key. It's a waste of space that adds overhead to inserts. You are relying on MySQL to create indexes for you in table person_address, but I don't think it will make the best choices in this case. You need an index on each column, but you most likely also need the combination of person_id and address_id to be unique. In other words, if you let mysql create indexes for you to satisfy the foreign key needs, you get separate single-column indexes, but you need a combined column unique constraint which renders one of the single column indexes redundant. How about: CREATE TABLE person ( person_id INT UNSIGNED NOT NULL AUTO_INCREMENT, constraint person_pk PRIMARY KEY (person_id) ) ENGINE=InnoDB; CREATE TABLE address ( address_id INT UNSIGNED NOT NULL AUTO_INCREMENT, constraint address_pk PRIMARY KEY (address_id) ) ENGINE=InnoDB; CREATE TABLE person_address ( person_id INT UNSIGNED NOT NULL, address_id INT UNSIGNED NOT NULL, CONSTRAINT person_address_pk PRIMARY KEY (person_id, address_id), INDEX (address_id), CONSTRAINT person_person_address_FK1 FOREIGN KEY (person_id) REFERENCES person (person_id); CONSTRAINT address_person_address_FK1 FOREIGN KEY (address_id) REFERENCES address (address_id); ) ENGINE=InnoDB; Michael [EMAIL PROTECTED] wrote: I am unable to define a foreign key with the following three tables. I am unable to find the error having searched the documentation and tried several variations. Note that I created the first two tables with and without the index clause in the table ddl with no difference in outcome. The three tables and the first foreign key, person_person_address_FK1, create properly. The second foreign key, address_person_address_FK1, causes the error. Please help. create table person ( person_id int unsigned not null auto_increment, constraint person_pk primary key (person_id), index(person_id)); create table address ( address_id int unsigned not null auto_increment, constraint address_pk primary key (address_id), index(address_id)); create table person_address ( person_id int unsigned not null, address_id int unsigned not null); -- This statement works. alter table person_address add constraint person_person_address_FK1 foreign key (person_id) references person (person_id); -- This statement fails. alter table person_address add constraint address_person_address_FK1 foreign key (address_id) references
Re: Bug? Can't create/write to file '/root/tmp/ibu6vdue' (Errcode: 13)
Alejandro, please search the bugs.mysql.com database about known --tmpdir bugs. If TMPDIR works, it is a fine workaround. 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 - Original Message - From: Alejandro D. Burne [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, December 06, 2004 2:40 PM Subject: Re: Bug? Can't create/write to file '/root/tmp/ibu6vdue' (Errcode: 13) Heikki I do it, in my.cnf on [mysqld] section I add: tmpdir=/tmp then I try: /usr/bin/mysqld_safe --tmpdir=/tmp --datadir=/var/lib/mysql --user=mysql with the same result If I change TMPDIR enviroment variable to /tmp works fine. I forgot to say I install mysql from rpm. Alejandro On Fri, 3 Dec 2004 22:01:37 +0200, Heikki Tuuri [EMAIL PROTECTED] wrote: Alejandro, - Original Message - From: Alejandro D. Burne [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, December 03, 2004 7:20 PM Subject: Bug? Can't create/write to file '/root/tmp/ibu6vdue' (Errcode: 13) After installing MySQL 5.0.2 on MDK10.0 mysqld doesn't start with error: Can't create/write to file '/root/tmp/ibu6vdue' (Errcode: 13) InnoDB, and mysqld in general, must be able to create temporary files. http://dev.mysql.com/doc/mysql/en/Temporary_files.html MySQL uses the value of the TMPDIR environment variable as the pathname of the directory in which to store temporary files. If you don't have TMPDIR set, MySQL uses the system default, which is normally `/tmp', `/var/tmp', or `/usr/tmp'. If the filesystem containing your temporary file directory is too small, you can use the --tmpdir option to mysqld to specify a directory in a filesystem where you have enough space. I think it's a problem with innodb, if I add skip-innodb on my.cnf mysqld starts up: 041203 14:04:01 mysqld started 041203 14:04:01 [Warning] Asked for 196608 thread stack, but got 126976 /usr/sbin/mysqld: ready for connections. Version: '5.0.2-alpha-standard-log' socket: '/var/lib/mysql/mysql.sock' port: But when I enable innodb (#skip-innodb on my.cnf); 041203 14:04:56 mysqld started 041203 14:04:56 [Warning] Asked for 196608 thread stack, but got 126976 ./usr/sbin/mysqld: Can't create/write to file '/root/tmp/ibu6vdue' (Errcode: 13) 041203 14:04:56 InnoDB: Error: unable to create temporary file; errno: 13 041203 14:04:56 [ERROR] Can't init databases 041203 14:04:56 [ERROR] Aborting 041203 14:04:56 [Note] /usr/sbin/mysqld: Shutdown complete 041203 14:04:56 mysqld ended The only way to do work innodb is chmod 777 /root and /root/tmp, =( Alejandro 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 technical support from https://order.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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Password (str) vs. MD5 (str)
does anyone know what type of encryption is used in the PASSWORD(str) function? When would you use the MD5 vs the PASSWORD function? We have a campus standard to use the MD5 encryption so I need to confirm if the PASSWORD function will offer that or not. Thanks, Lauren -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb: TRUNCATE vs. DELETE FROM
Paul, - Original Message - From: Paul Mallach [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, December 06, 2004 4:26 PM Subject: innodb: TRUNCATE vs. DELETE FROM Hi! Is TRUNCATE optimized for innodb tables in MySQL 4.1.7? http://dev.mysql.com/doc/mysql/en/TRUNCATE.html says: For InnoDB, TRUNCATE TABLE is mapped to DELETE, so there is no difference.. But http://dev.mysql.com/doc/mysql/en/InnoDB_tuning.html claims: Beware also of other big disk-bound operations. Use DROP TABLE or TRUNCATE TABLE (from MySQL 4.0 up) to empty a table, not DELETE FROM tbl_name. thank you for pointing out this error. TRUNCATE is still mapped to DELETE FROM ... in 5.0. I have now corrected the online manual. bye, Paul. -- Paul Mallach ARIVA.DE AG Ostseekai 2 D - 24103 Kiel Tel: +49 (0)431/97108-24 E-Mail: [EMAIL PROTECTED] Fax: +49 (0)431/97108-29 Internet: http://www.ariva.de 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Locking Issue?
Terry, - Original Message - From: Terry Riley [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, December 06, 2004 8:15 PM Subject: Locking Issue? Can someone help, please? We set up a server to handle a coldfusion web application (CFMX 6.1) running against MySQL 4.1.3b-beta on WinNT. When it is a little stretched, we are finding many instances of queries listed as either 'Sending...' or 'Copying...' in the processlist, with the time going ever upwards (last check was at 1000 seconds and rising). All the tables in the database concerned are InnoDB, and none of the queries concerned are, as far as I know, involved in any transaction - they are straight selects (albeit complex ones, perhaps). When this happens, the other requests to the server are inevitably slow, and these seem never to be cleared unless I kill the threads - and I'm not 100% sure how much damage I'm doing in that action. Is this a possible locking issue? If so, how do I get around it. The settings for the server are at default, except where noted. This is the my.ini file: [mysqld] max_connections=1000 basedir=e:/mysql datadir=e:/mysql/data wait_timeout=60 # TR added next 6 lines on 27/07/04, after instal of v4.1.3b old-passwords local-infile query_cache_size=25M query_cache_type=1 set-variable=max_allowed_packet=16M set-variable=key_buffer=8M log-bin= log_slow_queries= [mysql] local-infile=1 Any pointers as to what I may be doing wrong? Please? Yes, I know we should upgrade to 4.1.7, and we will - soon. try tuning InnoDB. Your workload may be seriously disk-bound. Cheers Terry Riley 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inodb: large old ibdata1 and multiple tablespaces
Paul, - Original Message - From: Paul Mallach [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, December 06, 2004 1:59 PM Subject: inodb: large old ibdata1 and multiple tablespaces Hi! Running version 4.0 we created a fairly big innodb table (10GB, 72.950.601 rows). After upgrading to 4.1.7 we switched to multiple tablespaces. Then somebody ALTERed the table and innodb created a new idb file for the table. So right now we have 3 large files (old ibdata1|2 and the new *.ibd file). How can I get rid of the old main ibdata files? The documentation in 15.8 talks about deleting the file and recovering from a mysqldump. Is this still necessary, when I'm using multiple tablespaces? All the data would still be in the new .ibd file. Can anyone tell me, what would happen if I deleted the main ibdata1 file but left the .ibd intact? Would innodb recover gracefully? sorry, no. The ibdata files, .ibd files, and ib_logfiles must live together. You cannot separate them. You can move the table back inside that big ibdata file by removing the option innodb_file_per_table from my.cnf, and running ALTER TABLE ... TYPE=InnoDB. Or recreate everything from scratch. bye, Paul. -- Paul Mallach ARIVA.DE AG Ostseekai 2 D - 24103 Kiel Tel: +49 (0)431/97108-24 E-Mail: [EMAIL PROTECTED] Fax: +49 (0)431/97108-29 Internet: http://www.ariva.de 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join data from 2 mysql servers ??
When I am logged on to a particular mysql server, is it possible to - Access table residing on a different mysqlserver? or - To join tables between two databases residing on two different mysql servers (on two different H/W boxes or same H/W box). Any Ideas I looked in the manual. It talks about accessing table in a different database but the same mysql server! You can refer to a table within the current database as tbl name (within the current database), or as db name.tbl name to explicitly specify a database. You can refer to a column as col name, tbl name.col name, or db name.tbl name.col name. You need not specify a tbl name or db name.tbl name prefix for a column reference unless the reference would be ambiguous. Thanks Rakesh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Password (str) vs. MD5 (str)
On Mon, Dec 06, 2004 at 04:21:38PM -0600, [EMAIL PROTECTED] wrote: does anyone know what type of encryption is used in the PASSWORD(str) function? When would you use the MD5 vs the PASSWORD function? We have a campus standard to use the MD5 encryption so I need to confirm if the PASSWORD function will offer that or not. As the manual says, the PASSWORD() function should not be used within your application. It is meant only for use with the built-in MySQL privilege tables. Use MD5() or SHA1() for your own applications. Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Clustering and a large database
Hi, On Monday, December 6, 2004, at 04:15 PM, Klaus Berkling wrote: I beginning to use MySQL clustering abilities for a large records keeping solution. I have installed 4.1.7 with the clustering components. The ndbd and ndb_mgmd processes are running. I can create the database and tables using the ndb engine. I have started to import our data. I gather from the manual that tables are stored in RAM. I am trying to import a database with 11 tables with about 7 million rows. If I follow the math in the manual, one row will use 32KB, I would need 224 TB of RAM. You are misreading how the 32K page-size works. You can have multiple rows on a single page. So here is about how much you would need: 16 bytes overhead per row + 460 bytes per row (taken from 3GB/7,000,000) = 476 bytes per row You should get ~71 rows per page with each having an overhead of 128 bytes. 7,000,000 / 71 = 98591 pages 98591 * 128 = 12619648 or 12.6MB overhead on the page level. 12.6MB + (460 + 16)*7,000,000 = ~3.3G data * NoOfReplicas So you can see it isn't much more than your regular tables. If you had the actual table schema it could be a much closer estimate. Does this make sense or am I way off? In version 4.0, the data length of my largest table is 2,671,788,032 (I assume bytes, using SHOW TABLE STATUS), about 2.5 GB. So my entire version 4.0 database is about 3 GB is size. It won't be that much larger in general. Also keep in mind it will be split across all of the machines in the cluster so even if you need 15 gigs of ram, you could do it with 4 * 4GB machines. Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant 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]
error 1005 (errno150)
Hello, I try to set up replication and woulkd like to export my master with mysqldump. The import fails with ERROR 1005 (HY000) (errno 150)when trying to import the dump on the slave. The create-state which causes the error is: CREATE TABLE fond4client ( id int(11) NOT NULL auto_increment, fond_id int(11) NOT NULL default '0', client_id int(11) NOT NULL default '0', price_buy double NOT NULL default '-1', price_sell double NOT NULL default '-1', import_history_id int(11) default NULL, active char(1) NOT NULL default '', PRIMARY KEY (id), KEY fond4client_import_hist_id_idx (import_history_id), KEY fond4client_client_id_idx (client_id), KEY fond4client_active_idx (active), CONSTRAINT `0_34` FOREIGN KEY (`import_history_id`) REFERENCES `import_history` (`id`), CONSTRAINT `0_35` FOREIGN KEY (`client_id`) REFERENCES `client` (`id`) ) TYPE=InnoDB; master is: 4.0.17 slave: 4.1.7 Trying to insert the dump on another 4.0.17 fails too. Any hints out there? Regards Jochen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld process usage high and long
Background: I have a web site that is running MySQL 3.23.58 (although phpinfo shows Client API version: 4.0.20) and PHP 4.3.9 on a FreeBSD 4.7 system. The only live site on the server is http://www.routerbitworld.com/ which is using osCommerce. Problem: Until today, the site was speedy. Pages pulled up in seconds. Nearly every page accesses the db. Now it can take upto a couple of minutes to pull up even the main page. Troubleshooting: I have a copy of the site and database on the same server under a different name for staging purposes. I accessed that and it was speedy as usual. So it must not be the MySQL DBMS, it must be the actual database. The plain HTML pages on the site work fine, so it must not be the Apache server. I ran a myisamchk -e to see if there were errors and fix any of them. I rebooted the MySQL server as well as the physical machine. I restored a backup copy of the database from a day ago. None of these steps helped. Further info: Using top to monitor the mysqld processes I see something similar to this when I access the site: PID USERNAME PRI NICE SIZERES STATE C TIME WCPUCPU COMMAND 92903 mysql 80 14 30976K 8156K RUN1 1:18 17.19% 17.19% mysqld 93018 mysql 80 14 30976K 8156K CPU1 1 0:44 17.19% 17.19% mysqld 92989 mysql 18 14 30976K 8156K pause 1 0:50 17.14% 17.14% mysqld 92893 mysql 80 14 30976K 8156K RUN1 1:25 17.04% 17.04% mysqld 93016 mysql 18 14 30976K 8156K pause 1 0:44 17.04% 17.04% mysqld 92966 mysql 18 14 30976K 8156K pause 1 0:56 16.75% 16.75% mysqld 92988 mysql 18 14 30976K 8156K pause 1 0:51 16.60% 16.60% mysqld 93304 mysql 79 14 30976K 8156K RUN1 0:14 16.76% 16.46% mysqld 92932 mysql 77 14 30976K 8156K RUN1 1:08 16.06% 16.06% mysqld 93436 mysql 18 14 30976K 8156K pause 1 0:01 18.96% 4.20% mysqld Any ideas what could be causing the slowness? There are only two admins on the system, including myself. I did nothing over the weekend and the other admin said he only edited an e-mail address on someone's account. Help? Thank you very much for your time, Jonathan Duncan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld process usage high and long
Never mind, it was a query optimization issue. Upon the third interogation of the other admin, he remembered one small setting that he changed, which just so happened to increase the number of queries exponentially. Thanks, Jonathan Duncan On Mon, 6 Dec 2004, Jonathan Duncan wrote: Background: I have a web site that is running MySQL 3.23.58 (although phpinfo shows Client API version: 4.0.20) and PHP 4.3.9 on a FreeBSD 4.7 system. The only live site on the server is http://www.routerbitworld.com/ which is using osCommerce. Problem: Until today, the site was speedy. Pages pulled up in seconds. Nearly every page accesses the db. Now it can take upto a couple of minutes to pull up even the main page. Troubleshooting: I have a copy of the site and database on the same server under a different name for staging purposes. I accessed that and it was speedy as usual. So it must not be the MySQL DBMS, it must be the actual database. The plain HTML pages on the site work fine, so it must not be the Apache server. I ran a myisamchk -e to see if there were errors and fix any of them. I rebooted the MySQL server as well as the physical machine. I restored a backup copy of the database from a day ago. None of these steps helped. Further info: Using top to monitor the mysqld processes I see something similar to this when I access the site: PID USERNAME PRI NICE SIZERES STATE C TIME WCPUCPU COMMAND 92903 mysql 80 14 30976K 8156K RUN1 1:18 17.19% 17.19% mysqld 93018 mysql 80 14 30976K 8156K CPU1 1 0:44 17.19% 17.19% mysqld 92989 mysql 18 14 30976K 8156K pause 1 0:50 17.14% 17.14% mysqld 92893 mysql 80 14 30976K 8156K RUN1 1:25 17.04% 17.04% mysqld 93016 mysql 18 14 30976K 8156K pause 1 0:44 17.04% 17.04% mysqld 92966 mysql 18 14 30976K 8156K pause 1 0:56 16.75% 16.75% mysqld 92988 mysql 18 14 30976K 8156K pause 1 0:51 16.60% 16.60% mysqld 93304 mysql 79 14 30976K 8156K RUN1 0:14 16.76% 16.46% mysqld 92932 mysql 77 14 30976K 8156K RUN1 1:08 16.06% 16.06% mysqld 93436 mysql 18 14 30976K 8156K pause 1 0:01 18.96% 4.20% mysqld Any ideas what could be causing the slowness? There are only two admins on the system, including myself. I did nothing over the weekend and the other admin said he only edited an e-mail address on someone's account. Help? Thank you very much for your time, Jonathan Duncan -- 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 cluster installation
hi, I wish to have clusters of MySQL. i installed it from RPM. version, 4.1.7. but, i cant get the ndbd command to start my NDB. do i really need to install from tarball?? i am really new to MySQL clustering. all this while, i am using MySQL standalone database. pls guide me... i am willing to learn..thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help interpreting SHOW INNODB Status Message
Howdy all, We're having concurrency problems with a table in our database and I'm not sure if I'm interpreting the following chunk of output from SHOW INNODB STATUS correctly. From what I gather, the row could not be inserted because the table was locked. I think that this insert was a victim of the next key locking stategy used by INNODB based on statement lock_mode X locks gap before rec. We don't use SELECT FOR UPDATE statements so I'm wondering how there could be a lock. If someone was performing a regular SELECT statement (they would be using the index session_guid, label to retrieve the records) would that cause the insert statement to get locked out? What confuses me is how there could be two or more different guids involved in any one query (the session guid is always in the where clause of any select statement against this table, as is the label, though the labels are almost all the same, a value of SelectedCustomer). Any ideas as to how I can clean this up so that the contention is reduced? BTW, we're using a transaction isolation level of REPEATABLE READ. Thanks in advance, Tripp LATEST DETECTED DEADLOCK 041206 17:13:50 *** (1) TRANSACTION: TRANSACTION 0 10790587, ACTIVE 151 sec, process no 31424, OS thread id 2949241776 inserting mysql tables in use 1, locked 1 LOCK WAIT 46 lock struct(s), heap size 5504, undo log entries 158 MySQL thread id 85684, query id 14714501 host ip user update INSERT INTO s_contact_log (log_id, customer_id, entry, author, log_dt, log_type, office_id, session_guid, label, sort_id) VALUES (228072,38755, 'test entry' - *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 2263 n bits 264 index `idx_session_label` of table `ahf_test/s_contact_log` trx id 0 10790587 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 130 PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 32 0: len 30; hex 35384143373041392d343745312d313144392d393830322d383442304138; asc 58AC70A9-47E1-11D9-9802-84B0A8;...(truncated); 1: len 16; hex 53656c6563746564437573746f6d6572; asc SelectedCustomer;; 2: len 6; hex 004880b1; asc H ;; Here's the structure of the table: CREATE TABLE `s_contact_log` ( `session_guid` varchar(36) NOT NULL default '', `label` varchar(50) NOT NULL default '', `log_id` int(11) NOT NULL default '0', `office_id` int(11) NOT NULL default '0', `customer_id` int(11) NOT NULL default '0', `entry` text NOT NULL, `author` varchar(60) NOT NULL default '', `log_dt` date NOT NULL default '-00-00', `sort_id` int(11) NOT NULL default '0', `log_type` int(11) NOT NULL default '0', KEY `idx_session_guid` (`session_guid`), KEY `idx_session_label` (`session_guid`,`label`), CONSTRAINT `s_contact_log_ibfk_1` FOREIGN KEY (`session_guid`) REFERENCES `s_session` (`session_guid`) ) TYPE=InnoDB __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: characterset problem 4.1.7
I just read the post 'MySQL 4.1 and Unicode produces crap' on Dec. 6 by Yves Goergen. I guess we are having the same issue. I notice that 4.1.0 if I do SHOW VARIABLES LIKE char%', there is only one setting, however, in 4.1.7 there are different characterset. I did tried to change all variables to use utf8, but the problem still exist. Is there anybody could help? Or did I asked the question in a wrong group? Thank you. Francis Mak -Original Message- From: Francis Mak [mailto:[EMAIL PROTECTED] Sent: Friday, December 03, 2004 7:42 PM To: [EMAIL PROTECTED] Subject: characterset problem 4.1.7 Dear all, I was using mysql 4.1.0, all table use utf8. I can use php to store and display utf8 character without any problem. Yesterday I upgraded 4.1.0 to 4.1.7. I use mysql-control-center and I can see the data in 4.1.7 are utf8 characters. However, when I use my php program to display it, all things become ??? I supspect it is due to the client connection? What config I need to do in order to make php display correctly? Please Please! Thank you very much. Francis Mak -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to reduce the query response time?
Hello, I am a software developer, I am using Mysql-4.1.3b-beta-nt, ODBC3.5.1 driver on windows PC for an application development. the application which i am developing is a client-server architecture based,in which we have to store data of the BSM(Base Station Manager of CDMA network). the nature of data is records containing alarms and faults occuring in the CDMA system,after storing the data we need to generate statistical reports on these data my table structure is as fallows, CREATE TABLE ind_kar_bng_robocop_bsc_0_pm_ipc_0 ( gan_id INTEGER NOT NULL, bsc_id INTEGER NOT NULL, bts_id INTEGER NOT NULL, bd_type VARCHAR(10) NOT NULL, bd_id INTEGER NOT NULL, duplex VARCHAR(10) NOT NULL, data_GenTime DATETIME NOT NULL, item_id INTEGER NOT NULL, M0 INTEGER NOT NULL, M1 INTEGER NOT NULL, M2 INTEGER NOT NULL, M3 INTEGER NOT NULL, M4 INTEGER NOT NULL, M5 INTEGER NOT NULL, M6 INTEGER NOT NULL, M7 INTEGER NOT NULL, M8 INTEGER NOT NULL, M9 INTEGER NOT NULL, M10 INTEGER NOT NULL, M11 INTEGER NOT NULL, M12 INTEGER NOT NULL, M13 INTEGER NOT NULL, M14 INTEGER NOT NULL, M15 INTEGER NOT NULL, M16 INTEGER NOT NULL, M17 INTEGER NOT NULL, M18 INTEGER NOT NULL, M19 INTEGER NOT NULL, M20 INTEGER NOT NULL, M21 INTEGER NOT NULL, M22 INTEGER NOT NULL, M23 INTEGER NOT NULL, M24 INTEGER NOT NULL, M25 INTEGER NOT NULL, M26 INTEGER NOT NULL, M27 INTEGER NOT NULL, M28 INTEGER NOT NULL, M29 INTEGER NOT NULL, M30 INTEGER NOT NULL, M31 INTEGER NOT NULL, KEY DateIndex (data_GenTime), KEY gan_idIndex (gan_id) , KEY bsc_idIndex (bsc_id) , KEY bts_idIndex (bts_id) , KEY bd_typeIndex (bd_type) , KEY bd_idIndex (bd_id) , KEY item_idIndex (item_id)); the type of query that is executed is as below mysql select Sum(m0),Avg(m1),Max(m5),Min(m6) from ind_kar_bng_robocop_bsc_0_pm_ipc_0 where bsc_id = 0 and bts_id = 255 and data_Gentime between 2004-11-22 00:00:00 and 2004-12-10 19:41:44 and item_id = 0; +--+---+-+-+ | Sum(m0) | Avg(m1) | Max(m5) | Min(m6) | +--+---+-+-+ | 23376896 | 1154.9079 | 0 | 0 | +--+---+-+-+ 1 row in set (3 min 30.35 sec) this query executed when the record count in the table ind_kar_bng_robocop_bsc_0_pm_ipc_0 was 79,21,988 records mysql select count(*) from ind_kar_bng_robocop_bsc_0_pm_ipc_0; +--+ | count(*) | +--+ | 7921988 | +--+ as the number of record in the table keep on growing the query response time increases.. the explain select result of the same query is here mysql explain select Sum(m0),Avg(m1),Max(m5),Min(m6) from ind_kar_bng_robocop_bsc_0_pm_ipc_0 where bsc_id = 0 and bts_id = 255 and data_Gentime between 2004-11-22 00:00:00 and 2004-12-10 19:41:44 and item_id = 0 \G *** 1. row *** id: 1 select_type: SIMPLE table: ind_kar_bng_robocop_bsc_0_pm_ipc_0 type: ref possible_keys: DateIndex,bsc_idIndex,bts_idIndex,item_idIndex key: item_idIndex key_len: 4 ref: const rows: 820535 Extra: Using where 1 row in set (0.28 sec) these are my system variables. mysql show variables; +-+---+ | Variable_name | Value | +-+---+ | back_log| 50 | | basedir | C:\mysql\ | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client| latin1 | | character_set_connection| latin1 | | character_set_database | latin1 | | character_set_results | latin1 | | character_set_server| latin1 | | character_set_system| utf8 | | character_sets_dir | C:\mysql\share\charsets/ | | collation_connection| latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server| latin1_swedish_ci | | concurrent_insert | ON | | connect_timeout | 5 | | datadir
could not connect
hello all,
i had installed mysql-4.1.7 and php-4.3.5 and while
testing the php. it shows error as:
mysql_connect(): Access denied for user
'alaguraj'@'localhost' (using password: YES) in
/home/alaguraj/public_html/my.php on line 3
Could not connect: Access denied for user
'alaguraj'@'localhost' (using password: YES)
is there any problem with apache?? how can i solve
this problem.
thanks in advance
my test code is:
#!/usr/local/bin/php
?php
$link = mysql_connect(localhost,alaguraj,iii);
if (!$link) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);
?
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: could not connect
Am Mon, 06 Dec 2004 21:16:02 -0800 schrieb alagu raj: hello all, i had installed mysql-4.1.7 and php-4.3.5 and while testing the php. it shows error as: mysql_connect(): Access denied for user 'alaguraj'@'localhost' (using password: YES) What are the privileges for user alaguraj? If You have a column in mysql.user with the host-field other than 'localhost You have to add one for localhost regardless, if there is one for '%'. Alternatively You may try not to connect to localhost but to the real hostname in mysql_connect(). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
