integer field
Dear lists, Simple question for geniuses outthere: my database has an integer field where i created with CREATE table test (first int(10)); so when i use php to grab the first field and read it in $myrow[first] , then try to make the number increase by doing a $increase = $myrow[first] + 1; but the $increase is still holding the same number. is anything wrong with my mysql db configuration or php scripts? thanks
replication
Hello list, What is the current replication implementation in mysql: sync or async or both are supported? (and the respective db versions) What are the plans for the future? Thanks -- Best regards, Angel mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication
On Thu, Oct 23, 2003 at 08:48:04AM +0200, Angel Todorov wrote: Hello list, What is the current replication implementation in mysql: sync or async or both are supported? It's async in 3.23, 4.0, 4.1, and 5.0. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 39 days, processed 1,491,228,844 queries (439/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GRANT problem
Hello I've created an account by using: GRANT insert,update,select,delete on phonewatch.* to datatal identified by 'MYPASSWORD' I cant connect(mysql.exe or myodbc) to the db with the new account, and I did not receive any errormessages when creating the account. Have I forgot something? Med vänlig hälsning/Best Regards Datatal Utveckling AB Jonas Gauffin Tel direct: +46 (0) 498 25 30 16 Tel Support: +46 (0) 498 25 30 30 Fax: +46 (0) 498 25 30 99 http://www.datatal.se I try every day, but sometimes I fail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[PRB] REPAIR TABLE causes Too many connections on big table
Hello, I have a broken table (all the INSERTs cause error 'Duplicate entry for key 1: number'). When I run 'REPAIR TABLE tablename' it repairs table fine, but after the short period of thme since the repair process has been run, other mysql cliens start receiving the error Too many connections. It stops when 'repair table' is done. The type of table if MyISAM, it has ~2 000 000 entries (int, varchar(255), int) When I look onto ps -A | grep mysql output when repair table is run, I can see a lot of mysqld processes started Is this a normal behavior? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql 4.0.15 Warning: thr_alarm queue is full BUG
Hi list, I think i've found a little bug in MySQL 4.0.15 (mysql-standard-4.0.15-pc-linux-i686.tar.gz) Description : Here's my error log : Warning: thr_alarm queue is full Warning: thr_alarm queue is full [...] How To Repeat : Here's my conf file : [client] port= 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-innodb skip-name-resolve skip-host-cache skip-locking set-variable= max_connections=256 set-variable= table_cache=512 set-variable= key_buffer = 64M set-variable= max_allowed_packet = 1M set-variable= sort_buffer_size = 512K set-variable= net_buffer_length = 8K set-variable= myisam_sort_buffer_size = 8M Then use SET to modify some variables : set global max_connections=512; set global table_cache = 1024; And wait :) It seems that thr_alarm queue was allocated when server starts and not reallocated when modify some variables. How To Solve : Do not use SET, modify my.cnf and restart the server instead. Hope this help. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to update from other table?
I try convert this MS SQL Statement but an error occurs: UPDATE a SET a.ACCOUNT_CODE=b.ACCOUNT_CODE FROM a,b WHERE a.ACCODE=b.ACCOUNT_OLD Is there any syntax doing same as above? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is best hardware for server performance
On Tue, Oct 21, 2003 at 08:59:31PM -0600, Travis Reeder wrote: It seems mostly to be mysql pinned, not the app. like 99% mysql until all data is processed and keeps going up when data coming in is more than can be processed. So MySQL is using 99% of the CPU? Any idea why? Are you doing too many table scans? Too much computation in the queries? Lots of LIKE %foo% queries? If the CPU is truly the bottleneck and you can't improve the performance by tweaking queries and settings, then faster CPUs will help. What could I change in my.cnf to get better performance? I just have a default mysql install (4.0). It's hard to say. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 39 days, processed 1,493,160,737 queries (439/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
1044 : Access Denied problem Create Table
Hi list, I've just a crazy problem with Mysql 4.0.15 (mysql-standard-4.0.15-pc-linux-i686.tar.gz). I have 2 user, root and Dstats. With root, i can do all i want (create database, create table ...). With Dstats i can't create table in a database. My Grant : grant all privileges on Dstats_Stats.* to [EMAIL PROTECTED]; Here's the db table for my user Dstats : mysql select * from db where Host=localhost and Db=Dstats_Stats; +---+--++-+-+--- --+-+-+---++-+-- --++---+--+ | Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | +---+--++-+-+--- --+-+-+---++-+-- --++---+--+ | localhost | Dstats_Stats | Dstats | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y| +---+--++-+-+--- --+-+-+---++-+-- --++---+--+ 1 row in set (0.00 sec) Then mysql flush privileges; And now trying to create a db : shell ./mysql -u Dstats -p Dstats_Stats Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2839807 to server version: 4.0.15-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql create table test (testi integer not null,PRIMARY KEY (testi)) type=MyISAM; ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'Dstats_Stats' Can someone please help me ? I don't know what i'm doing wrong, all my MySQL servers work like a charm except this one ... PS : This is the my.cnf : [client] port= 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-innodb skip-name-resolve skip-host-cache skip-locking max_connections=512 table_cache=1024 query_cache_type = 0 key_buffer = 64M max_allowed_packet = 1M sort_buffer_size = 512K net_buffer_length = 8K myisam_sort_buffer_size = 8M Thanks in advance. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multithreaded?
On Tue, Oct 21, 2003 at 10:13:55AM -0700, Greg(Bear) Casad wrote: All installations of mysql I have run ps -fax will show about 5 mysql proccess running, even at idle. mysql or mysqld? I compiled on a Redhat 9 box, it did find pthreads however ps -fax show 1 mysqld process running. Is it using NTPL? Is there a spot to configure spare proccess? -- Or verify that it is indeed compiled muli-threaded?? The MySQL server (mysqld) is *always* multi-threaded. As far as spares, you might want to look at setting your thread_cache to a non-zero value. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 39 days, processed 1,493,368,938 queries (439/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication redirector
On Mon, Oct 20, 2003 at 07:03:37PM +0200, Benjamin KRIEF wrote: hi guys, i've just finished implementing replication on 2 production servers. [snip] the problem is that it's quite impossible to fine tune the load between the servers , and my slave is a bit too busy , so the replication thread is becoming very slow , and sometimes , i can see that the slave is more than 2 hours behind the master in terms of replication , and this really is a pb for me. Two hours?!? Wow. i looked everywhere for a high-priority-updates option, but i can't find it.tell me if i'm wrong but i deduced that i can't ask the slave to wait until it reached the master before answering selects. Is your bottleneck CPU, I/O, or lock contention? Is your slave running on worse hardware than your master? so i've started using more regexps to redirect the queries , but it's quite awful to my eyes. i would really like to input the ratio of select queries i'd like to send on the slave . Ouch. That's clearly not a good solution. what about , instead of round robin dns , writing a small perl script , including a socket listening on the port 3306 , this small script would have to redirect the sql requests on the slave or on the master , but this time , i could fine tune it , and input for instance 0,3 for the slave , so that 3 requests out of 10 would go to the slave. Have you looked at SQL Relay? -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 39 days, processed 1,493,548,297 queries (439/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to update from other table?
William IT [EMAIL PROTECTED] wrote: I try convert this MS SQL Statement but an error occurs: UPDATE a SET a.ACCOUNT_CODE=b.ACCOUNT_CODE FROM a,b WHERE a.ACCODE=b.ACCOUNT_OLD Is there any syntax doing same as above? Yes. From v4.0.4 you can do: UPDATE a, b SET a.ACCOUNT_CODE=b.ACCOUNT_CODE WHERE a.ACCODE=b.ACCOUNT_OLD UPDATE syntax is described at: http://www.mysql.com/doc/en/UPDATE.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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]
Full-text search on PDF, Word etc in TEXT colums?
Hi,
has anybody done full-text indexing of pdf and word documents stored in TEXT
columns?
I could find no reference to which documents formats that are supported by
MySQL.
I know that DB2, Oracle and SQL Server have restrictions on supported
document formats, so I asume that there are restrictions in MySQL as well?
I wan't to do something like the example at
http://www.mysql.com/doc/en/Fulltext_Search.html
http://www.mysql.com/doc/en/Fulltext_Search.html :
mysql CREATE TABLE articles (
- id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
- title VARCHAR(200),
- body TEXT,
- FULLTEXT (title,body)
- );
Query OK, 0 rows affected (0.00 sec)
mysql INSERT INTO articles VALUES
- (NULL,'MySQL Tutorial', 'DBMS stands for DataBase ...'),
- (NULL,'How To Use MySQL Efficiently', 'After you went through a
...'),
- (NULL,'Optimising MySQL','In this tutorial we will show ...'),
- (NULL,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
- (NULL,'MySQL vs. YourSQL', 'In the following database comparison
...'),
- (NULL,'MySQL Security', 'When configured properly, MySQL ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql SELECT * FROM articles
- WHERE MATCH (title,body) AGAINST ('database');
Only that I whish to load documents (PDF, Word, XML, HTML etc) into the TEXT
column using
LOAD_FILE(file_name)
or similiar. And be able to do full-text searches on these documents.
Q1: Is it possible to do full-text searches on PDF, Word, XML, HTML etc
documents stored in TEXT columns?
Q2: Is there a list of supported document formats for MySQL full-text
search?
Thanks for any help,
Jan-Erik Öhman
Re: Detect temporary tables
Jeff McKeon [EMAIL PROTECTED] wrote: I know I can issue show tables to give me a list of tables from the current database, how can I do the same thing with temporary tables? That is, is there a command to list the current temporary tables? You can't. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
SV: GRANT problem
Additional info: the user is added to mysql.user with no privileges. the user is added to mysql.db with insert,update,delete,select privileges on phonewatch db. When I try to connect to the db, C:\mysql\bin\mysql phonewatch -h ts2test -u datatal -pMYPASSWORD, I get ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) please help -Ursprungligt meddelande- Från: Datatal AB - Gauffin, Jonas [mailto:[EMAIL PROTECTED] Skickat: den 23 oktober 2003 09:15 Till: [EMAIL PROTECTED] Ämne: GRANT problem Hello I've created an account by using: GRANT insert,update,select,delete on phonewatch.* to datatal identified by 'MYPASSWORD' I cant connect(mysql.exe or myodbc) to the db with the new account, and I did not receive any errormessages when creating the account. Have I forgot something? Med vänlig hälsning/Best Regards Datatal Utveckling AB Jonas Gauffin Tel direct: +46 (0) 498 25 30 16 Tel Support: +46 (0) 498 25 30 30 Fax: +46 (0) 498 25 30 99 http://www.datatal.se I try every day, but sometimes I fail -- 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: command line operation problems
Maybe I am stating the obvious but instead of typing mysql -username root -p try : mysql -u root -p HTH Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Kelley Lingerfelt [EMAIL PROTECTED] To: Scott Purcell [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, October 22, 2003 6:52 PM Subject: Re: command line operation problems Well, according to your message, you might want to make sure you are typing mysql and not mysqld(which is the server daemon) According to the docs when I go to the cmd window, I should type in mysql -username and then get prompted for a password. But I get this: look at what you typed, you typed in mysqld not mysql C:\mysql\binmysqld -username root Kelley Scott Purcell wrote: Hello, I am new to mysql and have a couple of questions that I can't seem to get through at this time. I have installed the product on my win2000 box. I followed the docs for PC, and removed the generic user in the users table. Here is my question: According to the docs when I go to the cmd window, I should type in mysql -username and then get prompted for a password. But I get this: C:\mysql\binmysqld -username root Can't start server: Bind on TCP/IP port: No error 031022 10:46:57 Do you already have another mysqld server running on port: 3306 ? 031022 10:46:57 Aborting 031022 10:46:57 mysqld: Shutdown Complete But if I type just mysql C:\mysql\binmysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 to server version: 4.0.15-max-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql I also tried this, and this is how I need to connect with JDBC C:\mysql\binmysql -username root -p Enter password: ** ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) But it fails. I know I have a root user with a valid password? Where am I going wrong? I did create the 'menagerie' database, and can't get back to it. Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lock wait timeout exceeded problem
My application is designed to avoid concurency rows locking. It means that I should never access two rows at the same time during a transaction. I also get an error messgage when I insert a row. Here is the error message I get: java.sql.SQLException: General error, message from server: Deadlock found when trying to get lock; Try restarting transaction at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1628) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:886) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:945) at com.mysql.jdbc.Connection.execSQL(Connection.java:1809) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1602) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1488) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(Delegating PreparedStatement.java:207) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(Delegating PreparedStatement.java:207) at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:504) at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:444) at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:717) at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:605) at ... What is important to kwown I have 1,6 GB of data distributed into CLOB (longtext) and BLOB (longblob) that correspond to ~25'000 entries and a create or access this data during my tests. What is exactly the problem ? Regards. Yvan -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: lundi, 20. octobre 2003 15:03 To: [EMAIL PROTECTED] Subject: Re: Lock wait timeout exceeded problem Hess Yvan [EMAIL PROTECTED] wrote: I am doing a lot of inserts, updates and reads with big among of data into longtext and longblob fields using JDBC driver and mysql 4.0.15 for Windows with innodb tables. After 1 hours of intensive working the database sent me a message Lock wait timeout exceeded; Try restarting transaction SQL code: 1205. I doing know how I can solve this problem ? How I have do configure mysql or is it a bug ? Environment: 6 Java Virtual machine using each their own connections pool. O/R mapping is done with Hibernate version 2.0. You can increase value of innodb_lock_wait_timeout variable: http://www.mysql.com/doc/en/InnoDB_start.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
SV: GRANT problem
yep. but that does only apply when using INSERT INTO, right? GRANT should do that by itself? anyway, it didn't help. didn't help to restart mysqld either. mysql select * from db where user='datatal'; +--++-+-+-+-+-+-+---++-+++---+--+ | Host | Db | User| Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | +--++-+-+-+-+-+-+---++-+++---+--+ | %| phonewatch | datatal | Y | Y | Y | Y | N | N | N | N | N | N | N | N| +--++-+-+-+-+-+-+---++-+++---+--+ mysql select * from db where user='datatal'; +--++-+-+-+-+-+-+---++-+++---+--+ | Host | Db | User| Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | +--++-+-+-+-+-+-+---++-+++---+--+ | %| phonewatch | datatal | Y | Y | Y | Y | N | N | N | N | N | N | N | N| +--++-+-+-+-+-+-+---++-+++---+--+ -Ursprungligt meddelande- Från: Franz, Fa. PostDirekt MA [mailto:[EMAIL PROTECTED] Skickat: den 23 oktober 2003 11:03 Till: Datatal AB - Gauffin, Jonas Ämne: AW: GRANT problem Hi Jonas, did you send a 'FLUSH PRIVILEGES' after the GRANT-Statement ? If not, MySQL-Srver doesn't read the privileges-Table again. mfg Klaus -Ursprungligt meddelande- Från: Datatal AB - Gauffin, Jonas [mailto:[EMAIL PROTECTED] Skickat: den 23 oktober 2003 10:44 Till: [EMAIL PROTECTED] Ämne: SV: GRANT problem Additional info: the user is added to mysql.user with no privileges. the user is added to mysql.db with insert,update,delete,select privileges on phonewatch db. When I try to connect to the db, C:\mysql\bin\mysql phonewatch -h ts2test -u datatal -pMYPASSWORD, I get ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) please help -Ursprungligt meddelande- Från: Datatal AB - Gauffin, Jonas [mailto:[EMAIL PROTECTED] Skickat: den 23 oktober 2003 09:15 Till: [EMAIL PROTECTED] Ämne: GRANT problem Hello I've created an account by using: GRANT insert,update,select,delete on phonewatch.* to datatal identified by 'MYPASSWORD' I cant connect(mysql.exe or myodbc) to the db with the new account, and I did not receive any errormessages when creating the account. Have I forgot something? Med vänlig hälsning/Best Regards Datatal Utveckling AB Jonas Gauffin Tel direct: +46 (0) 498 25 30 16 Tel Support: +46 (0) 498 25 30 30 Fax: +46 (0) 498 25 30 99 http://www.datatal.se I try every day, but sometimes I fail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update Syntax
Note that REPLACE deletes before inserting!! Very dangerous. If you cannot use UPDATE ON DULICATE KEY UPDATE ... then do the following: You will need o send two queries to the server: INSERT IGNORE INTO tbl ..; UPDATE tbl SET ..; Explanation: - the first query will try to insert the record. If the record already exists, it will abort without any error because there is IGNORE to ignore if the record already exists. So, if the record exists already, nothing will be done. - the second UPDATE query will update the record. If the first INSERT inserted the record, no problem because MySQL won't do anything as there is no change (the docs say that before updating, the engine checks if there is a change). If the insert failed (due to a duplicate), still there is no problem with the UPDATE because the record exists. For me, this is the ideal approach. Maybe, it is even the one used by MySQL when the query contains ON DULICATE KEY The remaining problem is then to know if the DB2 engine supports the IGNORE option!!! I have never used DB2, just find out Thanks Emery - Original Message - From: Kelley Lingerfelt [EMAIL PROTECTED] To: Randy Chrismon [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, October 22, 2003 04:55 Subject: Re: Update Syntax Can you use REPLACE? Kelley Randy Chrismon wrote: Please tell me it ain't so... I am writing a Lotus Nots agent to feed data directly into a MySQL table. The agent needs to either insert a new record or update an existing record depending on whether a document in Nots is new or updated. Because this app may be ported over to a DB2 server, I am avoiding the on duplicate key update option. So, I intended to test for the existence of a MySQL record and, depending on the result, prepend either Update my_table to a build SQL string, or prepend Insert into table. And then I read the documentation... It looks like Update MUST use the set column_1=new_value1, column_2=new_value2, etc. format. I was hoping to do something like update my_table values(newValue1, newValue2...) making sure to have a value or holder for each column. In other words, I was expecting insert and update to look pretty much the same except for the prefix and a where clause on the end of the update. Am I right that I can't do this?? Thanks. Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GRANT problem
Hi Jonas Not sure if this will help - in your GRANT statement do you not need to specify a host for the user e.g. GRANT.. to datatal @ your_host_name.?. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Datatal AB - Gauffin, Jonas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 9:14 AM Subject: GRANT problem Hello I've created an account by using: GRANT insert,update,select,delete on phonewatch.* to datatal identified by 'MYPASSWORD' I cant connect(mysql.exe or myodbc) to the db with the new account, and I did not receive any errormessages when creating the account. Have I forgot something? Med vänlig hälsning/Best Regards Datatal Utveckling AB Jonas Gauffin Tel direct: +46 (0) 498 25 30 16 Tel Support: +46 (0) 498 25 30 30 Fax: +46 (0) 498 25 30 99 http://www.datatal.se I try every day, but sometimes I fail -- 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]
SV: GRANT problem
Not sure if this will help - in your GRANT statement do you not need to specify a host for the user e.g. GRANT.. to datatal @ your_host_name.?. GRANT uses % as host if none is specified. % = all hosts. any other ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1 subqueries!
in version 4.1 states it supports subqueries like SELECT * FROM t1 WHERE (1,2,3) IN (SELECT a,b,c FROM t2); it doesn't support 'IN' [Dissertation] ERROR 1235: This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery' Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SV: GRANT problem
it works if I specify the host. Why doesn't % work as host? -Ursprungligt meddelande- Från: Rory McKinley [mailto:[EMAIL PROTECTED] Skickat: den 23 oktober 2003 11:56 Till: Datatal AB - Gauffin, Jonas Ämne: Re: GRANT problem thinking. In one of your follow up posts I see that you have the command as this : C:\mysql\bin\mysql phonewatch -h ts2test -u datatal -pMYPASSWORD Try C:\mysql\bin\mysql phonewatch -h ts2test -u datatal -p And enter password at the prompt Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Datatal AB - Gauffin, Jonas [EMAIL PROTECTED] To: Rory McKinley [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 11:45 AM Subject: SV: GRANT problem Not sure if this will help - in your GRANT statement do you not need to specify a host for the user e.g. GRANT.. to datatal @ your_host_name.?. GRANT uses % as host if none is specified. % = all hosts. any other ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GRANT problem
Ok. I'm stumped. If I'm reading the manual correctly, you were right about not hving to specify the host..the only difference between your version and the manual is that they use single quotes around the user name but I can't see how that makes any difference. I normally just specfiy hosts because using wildcards for hosts makes me nervous - hey, even paranoid people have enemies :) If anyone knows the answer to this I would be interested in knowing what it is too Sorry I can't be of more help Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Datatal AB - Gauffin, Jonas [EMAIL PROTECTED] To: Rory McKinley [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 12:05 PM Subject: SV: GRANT problem it works if I specify the host. Why doesn't % work as host? -Ursprungligt meddelande- Från: Rory McKinley [mailto:[EMAIL PROTECTED] Skickat: den 23 oktober 2003 11:56 Till: Datatal AB - Gauffin, Jonas Ämne: Re: GRANT problem thinking. In one of your follow up posts I see that you have the command as this : C:\mysql\bin\mysql phonewatch -h ts2test -u datatal -pMYPASSWORD Try C:\mysql\bin\mysql phonewatch -h ts2test -u datatal -p And enter password at the prompt Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Datatal AB - Gauffin, Jonas [EMAIL PROTECTED] To: Rory McKinley [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 11:45 AM Subject: SV: GRANT problem Not sure if this will help - in your GRANT statement do you not need to specify a host for the user e.g. GRANT.. to datatal @ your_host_name.?. GRANT uses % as host if none is specified. % = all hosts. any other ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
making specific query for big data
Hello all.. so Is it possible to do this: make query on mysql ask to select from blabla where bigdata= but tell to get only all data before --header end-- string into it so it will give in result only strings what are found before --header end-- becouse after this string goes very big data ps. bigdata field is longtext ok.. there is whole picture of this: I have longtext field I need to get only data before --header end-- (or any other pattern) so in result it will give only info before that pattern is it possible to as mysql to get some data from field not alll -- Tavs bezmaksas pasts Inbox.lv ___ FLASH GAMES - http://games.inbox.lv
Re: Full-text search on PDF, Word etc in TEXT colums?
At the moment MySql indexes only plain text.
You have to convert your document to text and then insert the text
into the column.
Santino
At 9:50 +0200 23-10-2003, Jan-Erik Öhman wrote:
Hi,
has anybody done full-text indexing of pdf and word documents stored in TEXT
columns?
I could find no reference to which documents formats that are supported by
MySQL.
I know that DB2, Oracle and SQL Server have restrictions on supported
document formats, so I asume that there are restrictions in MySQL as well?
I wan't to do something like the example at
http://www.mysql.com/doc/en/Fulltext_Search.html
http://www.mysql.com/doc/en/Fulltext_Search.html :
mysql CREATE TABLE articles (
- id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
- title VARCHAR(200),
- body TEXT,
- FULLTEXT (title,body)
- );
Query OK, 0 rows affected (0.00 sec)
mysql INSERT INTO articles VALUES
- (NULL,'MySQL Tutorial', 'DBMS stands for DataBase ...'),
- (NULL,'How To Use MySQL Efficiently', 'After you went through a
...'),
- (NULL,'Optimising MySQL','In this tutorial we will show ...'),
- (NULL,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
- (NULL,'MySQL vs. YourSQL', 'In the following database comparison
...'),
- (NULL,'MySQL Security', 'When configured properly, MySQL ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql SELECT * FROM articles
- WHERE MATCH (title,body) AGAINST ('database');
Only that I whish to load documents (PDF, Word, XML, HTML etc) into the TEXT
column using
LOAD_FILE(file_name)
or similiar. And be able to do full-text searches on these documents.
Q1: Is it possible to do full-text searches on PDF, Word, XML, HTML etc
documents stored in TEXT columns?
Q2: Is there a list of supported document formats for MySQL full-text
search?
Thanks for any help,
Jan-Erik Öhman
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Detect temporary tables
I know I can issue show tables to give me a list of tables from the current database, how can I do the same thing with temporary tables? That is, is there a command to list the current temporary tables? You can't. RATS!!! Jeff -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 4:38 AM To: [EMAIL PROTECTED] Subject: Re: Detect temporary tables Jeff McKeon [EMAIL PROTECTED] wrote: I know I can issue show tables to give me a list of tables from the current database, how can I do the same thing with temporary tables? That is, is there a command to list the current temporary tables? You can't. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: integer field
Have you tried casting the $myrow element to integer e.g $increase = (int)$myrow['first'] + 1; I think PHP will return the field as text by default and hence the addition will fail.. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: ketvin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 8:25 AM Subject: integer field Dear lists, Simple question for geniuses outthere: my database has an integer field where i created with CREATE table test (first int(10)); so when i use php to grab the first field and read it in $myrow[first] , then try to make the number increase by doing a $increase = $myrow[first] + 1; but the $increase is still holding the same number. is anything wrong with my mysql db configuration or php scripts? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IMAP server with MySQL as a storage for messages?
http://www.dbmail.org Curtis On Wednesday 22 October 2003 13:29, the council of elders heard Apollo (Carmel Entertainment) mumble incoherently: I was wondering if there is an opensource mail server (IMAP, not POP) that would keep all the messages in a MySQL database, not in a regular file? Apolinaras Apollo Sinkevicius - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changing multiple records dynamically
Hi, Is there any way to automatically update several rows as a result of a change to information being changed in another row in the same table? |--|---| | NAME | VALUE | |--|---| | ABCD | 10| | DEFG | 12| | HIJK | 11| |--|---| So if I changed DEFG to 11, how would I swap the value with that of HIJK or swap ABCD with DEFG? Thanks, Mumba Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Changing multiple records dynamically
Hi Rob, Definitely trying to do a value swap. Regards, David --- Rob [EMAIL PROTECTED] wrote: Are you trying to do a value swap or are you just updating? If you are updating you could simply do UPDATE table SET DEFG = 11, HIKJ = 12 -Original Message- From: Mumba Chucks [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 3:11 PM To: [EMAIL PROTECTED] Subject: Changing multiple records dynamically Hi, Is there any way to automatically update several rows as a result of a change to information being changed in another row in the same table? |--|---| | NAME | VALUE | |--|---| | ABCD | 10| | DEFG | 12| | HIJK | 11| |--|---| So if I changed DEFG to 11, how would I swap the value with that of HIJK or swap ABCD with DEFG? Thanks, Mumba Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Changing multiple records dynamically
Well, once they are avialable, triggers and/or stored procedures may be your answer, however, most trigger design (don't know about MySQL's forthcoming implementation) won't let you modify the same table on an update or insert, as you could end up in an infinite loop very easily. For now, I think that you're stuck doing it in your application. Just encapsulate how that data can be updated, and have all code that updates it call your function/method/etc... My $0.02... Dan Greene -Original Message- From: Mumba Chucks [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 9:53 AM To: Rob Cc: [EMAIL PROTECTED] Subject: RE: Changing multiple records dynamically Hi Rob, Definitely trying to do a value swap. Regards, David --- Rob [EMAIL PROTECTED] wrote: Are you trying to do a value swap or are you just updating? If you are updating you could simply do UPDATE table SET DEFG = 11, HIKJ = 12 -Original Message- From: Mumba Chucks [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 3:11 PM To: [EMAIL PROTECTED] Subject: Changing multiple records dynamically Hi, Is there any way to automatically update several rows as a result of a change to information being changed in another row in the same table? |--|---| | NAME | VALUE | |--|---| | ABCD | 10| | DEFG | 12| | HIJK | 11| |--|---| So if I changed DEFG to 11, how would I swap the value with that of HIJK or swap ABCD with DEFG? Thanks, Mumba __ __ Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ __ Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1 subqueries!
It does, I executed the following and it worked: SELECT * FROM table1 WHERE (field1, field2) IN (SELECT field1, field2 FROM table2) I think your problem is that you used constants (1,2,3) instead of using fields. Also I don't know if the fields don't need to be of the same type. I just need to check the manual. Thanks Emery - Original Message - From: Simon Gentile [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 11:59 Subject: MySQL 4.1 subqueries! in version 4.1 states it supports subqueries like SELECT * FROM t1 WHERE (1,2,3) IN (SELECT a,b,c FROM t2); it doesn't support 'IN' [Dissertation] ERROR 1235: This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery' Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: making specific query for big data
I don't understand well. Are you looking for the MAX value of a column? Or its data size? Thanks Emery - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 12:21 Subject: making specific query for big data Hello all.. so Is it possible to do this: make query on mysql ask to select from blabla where bigdata= but tell to get only all data before --header end-- string into it so it will give in result only strings what are found before --header end-- becouse after this string goes very big data ps. bigdata field is longtext ok.. there is whole picture of this: I have longtext field I need to get only data before --header end-- (or any other pattern) so in result it will give only info before that pattern is it possible to as mysql to get some data from field not alll -- Tavs bezmaksas pasts Inbox.lv ___ FLASH GAMES - http://games.inbox.lv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
When release of 4.1.1
Can you tell us, when you plan to release 4.1.1 version ? A few weeks ago, Heikki guess was: ...before Nov 15th, 2003. And how it looks now ? Best regards, ML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Online Tutorials for beginners
Hi, Does anyone know of any good mysql tutorials online that would suit someone who has a computer science degree but knows nothing about MySQL. Pointers will be most welcome. Cheers, Andrew Sql, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: making specific query for big data
I think he is looking for a way to just retrieve some data prior to a specific (although dynamic) point of the full data saved in a longtext datatype column. Like retrieving just the header of email messages saved with attachments etc in a longtext column. Perhaps having that header data saved in a separate column would fulfill the purpose... Jan -Original Message- From: Director General: NEFACOMP [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 17:16 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: making specific query for big data I don't understand well. Are you looking for the MAX value of a column? Or its data size? Thanks Emery - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 12:21 Subject: making specific query for big data Hello all.. so Is it possible to do this: make query on mysql ask to select from blabla where bigdata= but tell to get only all data before --header end-- string into it so it will give in result only strings what are found before --header end-- becouse after this string goes very big data ps. bigdata field is longtext ok.. there is whole picture of this: I have longtext field I need to get only data before --header end-- (or any other pattern) so in result it will give only info before that pattern is it possible to as mysql to get some data from field not alll -- Tavs bezmaksas pasts Inbox.lv ___ FLASH GAMES - http://games.inbox.lv -- 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: making specific query for big data
I may be missing something, but wouldn't a substring(bigdata, 0,instring(--header end--)) suffice? Of course, my syntax is all kinds of wrong... but you get the idea... -Original Message- From: Jan Magnusson [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 10:56 AM To: Mysql General mailing list Subject: RE: making specific query for big data I think he is looking for a way to just retrieve some data prior to a specific (although dynamic) point of the full data saved in a longtext datatype column. Like retrieving just the header of email messages saved with attachments etc in a longtext column. Perhaps having that header data saved in a separate column would fulfill the purpose... Jan -Original Message- From: Director General: NEFACOMP [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 17:16 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: making specific query for big data I don't understand well. Are you looking for the MAX value of a column? Or its data size? Thanks Emery - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 12:21 Subject: making specific query for big data Hello all.. so Is it possible to do this: make query on mysql ask to select from blabla where bigdata= but tell to get only all data before --header end-- string into it so it will give in result only strings what are found before --header end-- becouse after this string goes very big data ps. bigdata field is longtext ok.. there is whole picture of this: I have longtext field I need to get only data before --header end-- (or any other pattern) so in result it will give only info before that pattern is it possible to as mysql to get some data from field not alll -- Tavs bezmaksas pasts Inbox.lv ___ FLASH GAMES - http://games.inbox.lv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re mysql 4.1 subqueries
i implemented a very simple subquery using 'IN' and i get the same message Dissertation] ERROR 1235: This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery' other subqueries DO work, but i cant get one to work with 'IN' Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql server setting
Wednesday, October 22, 2003, 11:23:45 AM, Pey Ling wrote: PL Hi, thanks for your prompt reply and articles. PL Emmm... If i have a static IP, i can set this [EMAIL PROTECTED], am I PL right? Yes. PL what is the different between [EMAIL PROTECTED] and [EMAIL PROTECTED] [EMAIL PROTECTED] means that user can connect only from the host 210.230.75.1. PL which one is better and safer? If user will connect only from host 210.230.75.1, you don't need to use wildcard. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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 warnings
Tuncay Baskan (Yaz.Muh.-Proje Gel.ve Uyg.Gr.) [EMAIL PROTECTED] wrote: When I run LOAD DATA it sometimes notices that there were some warnings. For example; mysql LOAD DATA LOCAL INFILE './2003-01/MKR_OCAK.txt' INTO TABLE quantis_mkr; Query OK, 271392 rows affected (4 min 2.95 sec) Records: 271392 Deleted: 0 Skipped: 0 Warnings: 61 How can I see those warnings? I checked the error log but nothing shows up there. From version 4.1.1 you can use SHOW WARNINGS command: http://www.mysql.com/doc/en/SHOW_WARNINGS.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: Online Tutorials for beginners
New to SQL or new just to MySQL? -Original Message- From: Andrew Braithwaite [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 10:54 AM To: [EMAIL PROTECTED] Subject: Online Tutorials for beginners Hi, Does anyone know of any good mysql tutorials online that would suit someone who has a computer science degree but knows nothing about MySQL. Pointers will be most welcome. Cheers, Andrew Sql, query -- 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: unable to connect after log in
Hi
Have you tried using mysql_connect instead of mysql_pconnect? Does it make
any difference?
Rory McKinley
Nebula Solutions
+27 82 857 2391
[EMAIL PROTECTED]
There are 10 kinds of people in this world,
those who understand binary and those who don't (Unknown)
- Original Message -
From: chilie palmer [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 10:12 PM
Subject: unable to connect after log in
I have a connection problem that I have been unable to solve after
searching
the web and the mysql mailing-lists. I'm connecting to a local database
using PHP. It's a pretty simple and straightfoward setup, a user logs in
and the links they have access to are displayed (change user info, update
password, etc). I can log in with the initial links displayed (database
connection is made), but after that (either by clicking a link or hitting
refresh on the same page I just logged in to) I get the following error:
Access denied for user: '@localhost' to database 'my_DB_name'
The only way I have been able to work around this is by giving Select,
Insert, Update, Delete permissions to the host 'localhost' (no user or
password) in the mysql.users table. If I give this host no permissions,
the
script works as described above, only logging in once. Below is the PHP
db
connect function that I am using (included via require_once on all pages).
I have tried to connect both with root and another user I created with all
permissions. I have also tried creating a user in the mysql.db table with
'localhost - my_DB_name - my_DB_user' and all permissions.
?php
function db_connect()
{
$result = mysql_pconnect('localhost', 'my_DB_user', 'DBPASSWORD');
if (!$result)
return false;
if (!mysql_select_db('my_DB_name'))
return false;
return $result;
}
?
Is there something I've overlooked here in my connection setup? I can't
give the localhost host permissions in my production environment for
security reasons. Any help, suggestions, or ideas are appreciated.
(Using -
MySQL 4.0.13, PHP 4.3.2)
Thanks
_
Want to check if your PC is virus-infected? Get a FREE computer virus
scan
online from McAfee.
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
--
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]
Innodb vs myisam
I'm sure this has been asked before, but after seeing some benchmarks, it looks like using innodb is a no brainer. Just want to know why you wouldn't use innodb? Travis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is best hardware for server performance
Well I probably can tweak the queries, but there are a LOT of them. It is for http://www.ecommstats.com so we get a TON of requests that have to processed with probably ~20 queries (selects, inserts, updates), then there is a maintenance program that runs every few minutes to clean things up and calculate some things which does several more queries and goes through to processe data. Most of the time it's ok, and it can handle it, but during some peak times, requests queue up and as long as there are requests in the queue, it's pinned. So I'm just trying to figure out how to best handle it. Any idea why? Are you doing too many table scans? Too much computation in the queries? Lots of LIKE %foo% queries? There is maybe 1 LIKE query and I'm sure that's not causing any issues. Travis Jeremy Zawodny wrote: On Tue, Oct 21, 2003 at 08:59:31PM -0600, Travis Reeder wrote: It seems mostly to be mysql pinned, not the app. like 99% mysql until all data is processed and keeps going up when data coming in is more than can be processed. So MySQL is using 99% of the CPU? Any idea why? Are you doing too many table scans? Too much computation in the queries? Lots of LIKE %foo% queries? If the CPU is truly the bottleneck and you can't improve the performance by tweaking queries and settings, then faster CPUs will help. What could I change in my.cnf to get better performance? I just have a default mysql install (4.0). It's hard to say. Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Linker error
Hello, I developed a small C client on RH Linux 7 with MySql 3.xx. In the past I upgraded to RH 9 and MySql 4.0.12 and the program doesn'l link. It give a linker error: undefined reference to `__gxx_personality_v0' in every mysql_* function i call. How can I solve this problem? Santino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb vs myisam
The answer is actually quite simple! There are a few reasons: 1. Features. Each table type has something over the other. While InnoDB has transactions, foreign keys, hot backup capabilities, consistant read and better write concurrency (for many situations), MyISAM has FULLTEXT indexes, the option of having secondary AUTO_INCREMENT columns, OpenGIS data storage (in 4.1 and above) as well as slighly simplified offline backups. Additionally, MyISAM has lower disk space requirements for any given amount of data. 2. Price When looking at commercial licensing, it costs more to buy a version that includes InnoDB. Hope this helps! Regards, Chris On Fri, 24 Oct 2003 01:50 am, Travis Reeder wrote: I'm sure this has been asked before, but after seeing some benchmarks, it looks like using innodb is a no brainer. Just want to know why you wouldn't use innodb? Travis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb vs myisam
In the last episode (Oct 24), Chris Nolan said: The answer is actually quite simple! There are a few reasons: 1. Features. Each table type has something over the other. While InnoDB has transactions, foreign keys, hot backup capabilities, consistant read and better write concurrency (for many situations), MyISAM has FULLTEXT indexes, the option of having secondary AUTO_INCREMENT columns, OpenGIS data storage (in 4.1 and above) as well as slighly simplified offline backups. Additionally, MyISAM has lower disk space requirements for any given amount of data. MyISAM also lets you put indexes and tables onto separate disks for more performance, and supports a compressed read-only format. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0
Hello list, Can you tell me when approximately is the 5.0 version of MySQL going to be released? Thanks. -- Best regards, Angel mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Searching all db tables for a column name in MySQL - how? can I?
In Oracle it looks like this: SELECT table_name FROM all_tables WHERE column_name = 'Identifier' Does anybody know if this is possible in MySQL and if so, what's the syntax? I can't find anything in the documentation that allows me to search for a specific column in all tables. Thank you, Patricia Patricia's Fancy Visit my eBay store for great deals on terrific items! I specialize in pretty things for the girl inside of women and boy toys for the boy inside of men, but also have a variety of life's little necessities. If you play music, check out Rainbow Music Outlet. Bookmark these stores as we are always adding new merchandise for your shopping pleasure. - Do you Yahoo!? The New Yahoo! Shopping - with improved product search
GROUP BY dilemma
I have a database with about 40 tables that contains 'object data'. These objects are loaded from files, so have an associated file. Different files can contain the same object and be 'loaded' simultaneously, but there can only be one instance of any object at any one time. The active object is determined by the Last-Modified time of the source file (newer objects take precedence over older ones.) Below is a simplified version of my DB along with some example queries and further explanations about how I've been trying to do this. CREATE DATABASE TestDB; USE TestDB; CREATE TABLE Files ( FID SMALLINT UNSIGNED AUTO_INCREMENT, LM DATETIME NOT NULL, PRIMARY KEY (FID) ); INSERT INTO Files VALUES (1,'1969-12-31 16:00:00'), (2,'2002-10-30 14:45:16'),(3,'2002-07-17 16:59:22'), (12,'2003-09-17 16:16:54'),(14,'2002-11-23 13:21:00'); CREATE TABLE Globals ( FID SMALLINT UNSIGNED NOT NULL, GID INT UNSIGNED NOT NULL AUTO_INCREMENT, Value CHAR(32) NOT NULL, PRIMARY KEY(FID,GID) ); INSERT INTO Globals VALUES (2,1,'Wrong'),(3,1,'Wrong'), (1,4,'Correct!'),(2,2,'Wrong'),(2,3,'Wrong'),(12,1,'Correct!'), (2,5,'Correct!'),(1,3,'Wrong'),(1,1,'Wrong'),(14,3,'Correct!'), (1,5,'Wrong'),(1,2,'Wrong'),(12,2,'Correct!'),(3,2,'Wrong'), (14,1,'Wrong'); mysql SELECT Glb.GID, Glb.FID, Glb.Value, Fil.LM FROM Globals Glb - LEFT JOIN Files Fil ON(Glb.FID=Fil.FID) ORDER BY Glb.GID, - Fil.LM DESC; +-+-+--+-+ | GID | FID | Value| LM | +-+-+--+-+ | 1 | 12 | Correct! | 2003-09-17 16:16:54 | | 1 | 14 | Wrong| 2002-11-23 13:21:00 | | 1 | 2 | Wrong| 2002-10-30 14:45:16 | | 1 | 3 | Wrong| 2002-07-17 16:59:22 | | 1 | 1 | Wrong| 1969-12-31 16:00:00 | | 2 | 12 | Correct! | 2003-09-17 16:16:54 | | 2 | 2 | Wrong| 2002-10-30 14:45:16 | | 2 | 3 | Wrong| 2002-07-17 16:59:22 | | 2 | 1 | Wrong| 1969-12-31 16:00:00 | | 3 | 14 | Correct! | 2002-11-23 13:21:00 | | 3 | 2 | Wrong| 2002-10-30 14:45:16 | | 3 | 1 | Wrong| 1969-12-31 16:00:00 | | 4 | 1 | Correct! | 1969-12-31 16:00:00 | | 5 | 2 | Correct! | 2002-10-30 14:45:16 | | 5 | 1 | Wrong| 1969-12-31 16:00:00 | +-+-+--+-+ 15 rows in set (0.00 sec) mysql SELECT Glb.GID, Glb.FID, Glb.Value, Fil.LM FROM Globals Glb - LEFT JOIN Files Fil ON(Glb.FID=Fil.FID) GROUP BY Glb.GID; +-+-+--+-+ | GID | FID | Value| LM | +-+-+--+-+ | 1 | 2 | Wrong| 2002-10-30 14:45:16 | | 2 | 2 | Wrong| 2002-10-30 14:45:16 | | 3 | 2 | Wrong| 2002-10-30 14:45:16 | | 4 | 1 | Correct! | 1969-12-31 16:00:00 | | 5 | 2 | Correct! | 2002-10-30 14:45:16 | +-+-+--+-+ 5 rows in set (0.09 sec) mysql SELECT Glb.GID, Glb.FID, Glb.Value, MAX(Fil.LM) FROM Globals - Glb LEFT JOIN Files Fil ON(Glb.FID=Fil.FID) GROUP BY Glb.GID; +-+-+--+-+ | GID | FID | Value| MAX(Fil.LM) | +-+-+--+-+ | 1 | 2 | Wrong| 2003-09-17 16:16:54 | | 2 | 2 | Wrong| 2003-09-17 16:16:54 | | 3 | 2 | Wrong| 2002-11-23 13:21:00 | | 4 | 1 | Correct! | 1969-12-31 16:00:00 | | 5 | 2 | Correct! | 2002-10-30 14:45:16 | +-+-+--+-+ 5 rows in set (0.00 sec) mysql CREATE TEMPORARY TABLE TmpGlobals SELECT GID, MAX(LM) as LM - FROM Globals LEFT JOIN Files ON(Globals.FID=Files.FID) GROUP - BY GID; Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql SELECT Glb.GID, Glb.FID, Glb.Value, Fil.LM FROM Globals Glb - LEFT JOIN Files Fil ON(Glb.FID=Fil.FID) LEFT JOIN TmpGlobals - TGlb ON(Glb.GID=TGlb.GID) WHERE Fil.LM=TGlb.LM ORDER BY - Glb.GID; +-+-+--+-+ | GID | FID | Value| LM | +-+-+--+-+ | 1 | 12 | Correct! | 2003-09-17 16:16:54 | | 2 | 12 | Correct! | 2003-09-17 16:16:54 | | 3 | 14 | Correct! | 2002-11-23 13:21:00 | | 4 | 1 | Correct! | 1969-12-31 16:00:00 | | 5 | 2 | Correct! | 2002-10-30 14:45:16 | +-+-+--+-+ 5 rows in set (0.00 sec) Goal: mysql +-+-+--+-+ | GID | FID | Value| LM | +-+-+--+-+ | 1 | 12 | Correct! | 2003-09-17 16:16:54 | | 2 |
RE: Searching all db tables for a column name in MySQL - how? can I?
SHOW COLUMNS FROM table_name LIKE 'Identifier'; should work. It's all in here, jsut search the page for SHOW COLUMNS: http://www.mysql.com/doc/en/Show_database_info.html Chris -Original Message- From: Patricia LaRue [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 11:02 AM To: [EMAIL PROTECTED] Subject: Searching all db tables for a column name in MySQL - how? can I? In Oracle it looks like this: SELECT table_name FROM all_tables WHERE column_name = 'Identifier' Does anybody know if this is possible in MySQL and if so, what's the syntax? I can't find anything in the documentation that allows me to search for a specific column in all tables. Thank you, Patricia Patricia's Fancy Visit my eBay store for great deals on terrific items! I specialize in pretty things for the girl inside of women and boy toys for the boy inside of men, but also have a variety of life's little necessities. If you play music, check out Rainbow Music Outlet. Bookmark these stores as we are always adding new merchandise for your shopping pleasure. - Do you Yahoo!? The New Yahoo! Shopping - with improved product search -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
New combined MySql and Html development platform
This site: http://www.everydaysoftware.biz Has a complete MySql and Html development environment running on the browser site itself, with debug, edit, run capability. To gain access to the development environment, click Develop on the left from of the opening page. The environment is written in URL code, called Uscript, which allows general intermixing of sql, html, and javascript syntax in a single script. The scripts are stored in Mysql which combines the functionality of a file directory and database. The entire application at http://www.everydaysoftware.biz includes demos of paging anchors, banners, paypal, database discovery and automatic generation of database function anchors, a help system (still developing), password, login, and a development environment. All written in the same coding that is used for URLs. The Uscript engine is less than 2,000 lines of c code. To gain access to the development environment, click Develop on the left from of the opening page. Matt Young Everyday Software -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Searching all db tables for a column name in MySQL - how? can I?
Chris: Your syntax and all documented syntax is for querying column names in just one table. My question is How do I search for a specific column name in ALL tables? Chris [EMAIL PROTECTED] wrote: SHOW COLUMNS FROM table_name LIKE 'Identifier'; should work. It's all in here, jsut search the page for SHOW COLUMNS: http://www.mysql.com/doc/en/Show_database_info.html Chris -Original Message- From: Patricia LaRue [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 11:02 AM To: [EMAIL PROTECTED] Subject: Searching all db tables for a column name in MySQL - how? can I? In Oracle it looks like this: SELECT table_name FROM all_tables WHERE column_name = 'Identifier' Does anybody know if this is possible in MySQL and if so, what's the syntax? I can't find anything in the documentation that allows me to search for a specific column in all tables. Thank you, Patricia Patricia's Fancy Visit my eBay store for great deals on terrific items! I specialize in pretty things for the girl inside of women and boy toys for the boy inside of men, but also have a variety of life's little necessities. If you play music, check out Rainbow Music Outlet. Bookmark these stores as we are always adding new merchandise for your shopping pleasure. - Do you Yahoo!? The New Yahoo! Shopping - with improved product search -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Patricia's Fancy Visit my eBay store for great deals on terrific items! I specialize in pretty things for the girl inside of women and boy toys for the boy inside of men, but also have a variety of life's little necessities. If you play music, check out Rainbow Music Outlet. Bookmark these stores as we are always adding new merchandise for your shopping pleasure. - Do you Yahoo!? The New Yahoo! Shopping - with improved product search
Re: Query status
Filip Rachunek [EMAIL PROTECTED] writes: Hello, can somebody tell me what the status Writing to net returned by show processlist exactly mean? I've tried to find it in MySQL documentation but either it's not there or I missed it. I am asking because this status is usually shown when my J2EE application [using Connector/J 3.0.9] starts to eat 99% of CPU time and then gets jammed. Are you sure you don't have any errors in your SQL? Maybe your query returns a karthesian product - and this might kill your J2EE application. Check out your slow-log - the query is probably there. -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Searching all db tables for a column name in MySQL - how? can I?
Oh, sorry about that. I think I read it a bit too quickly... You can't that I'm aware of, besides getting the table list and using an external script to loop through them. But, of course, I definitely could be wrong. Chris -Original Message- From: Patricia LaRue [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 11:30 AM To: [EMAIL PROTECTED] Subject: RE: Searching all db tables for a column name in MySQL - how? can I? Chris: Your syntax and all documented syntax is for querying column names in just one table. My question is How do I search for a specific column name in ALL tables? Chris [EMAIL PROTECTED] wrote: SHOW COLUMNS FROM table_name LIKE 'Identifier'; should work. It's all in here, jsut search the page for SHOW COLUMNS: http://www.mysql.com/doc/en/Show_database_info.html Chris -Original Message- From: Patricia LaRue [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 11:02 AM To: [EMAIL PROTECTED] Subject: Searching all db tables for a column name in MySQL - how? can I? In Oracle it looks like this: SELECT table_name FROM all_tables WHERE column_name = 'Identifier' Does anybody know if this is possible in MySQL and if so, what's the syntax? I can't find anything in the documentation that allows me to search for a specific column in all tables. Thank you, Patricia Patricia's Fancy Visit my eBay store for great deals on terrific items! I specialize in pretty things for the girl inside of women and boy toys for the boy inside of men, but also have a variety of life's little necessities. If you play music, check out Rainbow Music Outlet. Bookmark these stores as we are always adding new merchandise for your shopping pleasure. - Do you Yahoo!? The New Yahoo! Shopping - with improved product search -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Patricia's Fancy Visit my eBay store for great deals on terrific items! I specialize in pretty things for the girl inside of women and boy toys for the boy inside of men, but also have a variety of life's little necessities. If you play music, check out Rainbow Music Outlet. Bookmark these stores as we are always adding new merchandise for your shopping pleasure. - Do you Yahoo!? The New Yahoo! Shopping - with improved product search -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Use of uninitialized value in join or string at ./dump_members.pl line 35.
On Wednesday, Oct 22, 2003, at 14:21 Europe/Brussels, Harald Fuchs
wrote:
In article [EMAIL PROTECTED],
Jerry Rocteur [EMAIL PROTECTED] writes:
Hi,
I'm reading MySQL by Paul DuBois
When I run dump_members.pl I get:
Use of uninitialized value in join or string at ./dump_members.pl line
35.
Line 35 is the print line before.
while (my @ary = $sth-fetchrow_array ())
{
print join (\t, @ary), \n;
}
I just can't see which value is not initialized...
As I'd like to use the script for my own use and I would prefer using
perl -w than not using it what do I change in the script in order NOT
to get the error ??
Probably one of the columns of the returned row is NULL which gets
translated to undef. Try this:
while (my @ary = $sth-fetchrow_array ()) {
@ary = map { defined $_ ? $_ : NULL } @ary;
print join (\t, @ary), \n;
}
Perfect ... Thanks very much.. That works like a charm!
Now, Paul, perhaps the script should be like this instead of the one
your provide with sampdb ???
Thanks guys,
Jerry
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Use of uninitialized value in join or string at ./dump_members.pl line 35.
On Wednesday, Oct 22, 2003, at 14:21 Europe/Brussels, Harald Fuchs
wrote:
In article [EMAIL PROTECTED],
Jerry Rocteur [EMAIL PROTECTED] writes:
Hi,
I'm reading MySQL by Paul DuBois
When I run dump_members.pl I get:
Use of uninitialized value in join or string at ./dump_members.pl line
35.
Line 35 is the print line before.
while (my @ary = $sth-fetchrow_array ())
{
print join (\t, @ary), \n;
}
I just can't see which value is not initialized...
As I'd like to use the script for my own use and I would prefer using
perl -w than not using it what do I change in the script in order NOT
to get the error ??
Probably one of the columns of the returned row is NULL which gets
translated to undef. Try this:
while (my @ary = $sth-fetchrow_array ()) {
@ary = map { defined $_ ? $_ : NULL } @ary;
print join (\t, @ary), \n;
}
Perfect ... Thanks very much.. That works like a charm!
Now, Paul, perhaps the script should be like this instead of the one
your provide with sampdb ???
Thanks guys,
Jerry
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlimport 'can't get stat of [file]' error
I've unable to load data from files into existing tables. When I give the command: mysqlimport [database] [filename] I keep getting the following error. mysqlimport: Error: Can't get stat of '[filename]' (Errcode: 13), when using table: [table] I get a similar error when trying to use LOAD DATA INFILE from within mysql. The text files all have the extension .table I'm sure that I'm just missing something simple, but I've spent a good amount of time trying to figure this out and haven't been able to. Any advice on what's happening or how to get around this would be really appreciated. Thanks, Will Tyburczy ___ Join Excite! - http://www.excite.com The most personalized portal on the Web! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tablecrash
Datatal AB - Gauffin, Jonas [EMAIL PROTECTED] wrote:
I got a table that crashes everytime that I use delete from
hvd_layout_settings
This is a infinite loop:
while (1)
{
check table hvd_layout_settings = table ok
delete from hvd_layout_settings
check table hvd_layout_settings = table crashed
repair table hvd_layout_settings
}
Im using mysql 4.0.15a.
Seems your bug is the same as http://bugs.mysql.com/bug.php?id=1321
Which OS do you use?
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [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: Query status
Are you sure you don't have any errors in your SQL? Maybe your query returns a karthesian product - and this might kill your J2EE application. Check out your slow-log - the query is probably there. -- Per Andreas Buer Thanks for your response Per. Yes, the query is at my slow-log, I already know that. I've made some changes and optimizations to my code to make it faster. I was only curious what that status really mean. Btw, what is karthesian product? Filip _ Plan your week with MSN Weather - http://www.msn.cz/weather/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlimport 'can't get stat of [file]' error
Check file permissions. The file must be readable by the user 'mysql'. Will Tyburczy wrote: I've unable to load data from files into existing tables. When I give the command: mysqlimport [database] [filename] I keep getting the following error. mysqlimport: Error: Can't get stat of '[filename]' (Errcode: 13), when using table: [table] I get a similar error when trying to use LOAD DATA INFILE from within mysql. The text files all have the extension .table I'm sure that I'm just missing something simple, but I've spent a good amount of time trying to figure this out and haven't been able to. Any advice on what's happening or how to get around this would be really appreciated. Thanks, Will Tyburczy ___ Join Excite! - http://www.excite.com The most personalized portal on the Web! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlimport 'can't get stat of [file]' error
Will Tyburczy [EMAIL PROTECTED] wrote: I've unable to load data from files into existing tables. When I give the command: mysqlimport [database] [filename] I keep getting the following error. mysqlimport: Error: Can't get stat of '[filename]' (Errcode: 13), when using table: [table] I get a similar error when trying to use LOAD DATA INFILE from within mysql. The text files all have the extension .table I'm sure that I'm just missing something simple, but I've spent a good amount of time trying to figure this out and haven't been able to. Any advice on what's happening or how to get around this would be really appreciated. $ perror 13 Error code 13: Permission denied You doesn't have permission on that file. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: integer field
FYI, PHP will automatically convert the data type to an integer if to use increment it like that. Just like if you had $var = 123; and then did $var .= foo it would be converted into the string 123foo. If you're still unable to change the value of $myrow, then I would consult the PHP mailing list for further assistance. There is no reason for that addition to fail. - Gabriel On Thursday, October 23, 2003, at 08:32 AM, Rory McKinley wrote: Have you tried casting the $myrow element to integer e.g $increase = (int)$myrow['first'] + 1; I think PHP will return the field as text by default and hence the addition will fail.. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: ketvin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 8:25 AM Subject: integer field Dear lists, Simple question for geniuses outthere: my database has an integer field where i created with CREATE table test (first int(10)); so when i use php to grab the first field and read it in $myrow[first] , then try to make the number increase by doing a $increase = $myrow[first] + 1; but the $increase is still holding the same number. is anything wrong with my mysql db configuration or php scripts? thanks -- 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: Query status
It's actually Cartesian Product, the result of a query like select * from table1, table2; where you get all the virtual tuples (rows or relations) that represent the set of each tuple in table1 coupled with each tuple in table2. I good way to get all possible combinations of the records from two tables, but is usually a runaway select error because you forgot to put in a join between the two tables. Try the select above with two small tables out of your database and look at the result. Filip Rachunek [EMAIL PROTECTED] com To [EMAIL PROTECTED] 10/23/03 02:15 PM cc Subject Re: Query status Are you sure you don't have any errors in your SQL? Maybe your query returns a karthesian product - and this might kill your J2EE application. Check out your slow-log - the query is probably there. -- Per Andreas Buer Thanks for your response Per. Yes, the query is at my slow-log, I already know that. I've made some changes and optimizations to my code to make it faster. I was only curious what that status really mean. Btw, what is karthesian product? Filip _ Plan your week with MSN Weather - http://www.msn.cz/weather/ -- 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 vs myisam
I thought I read a message on this list that said you can't use full text indexes with InnoDB yet. Can anyone confirm that? - Gabriel On Thursday, October 23, 2003, at 11:50 AM, Travis Reeder wrote: I'm sure this has been asked before, but after seeing some benchmarks, it looks like using innodb is a no brainer. Just want to know why you wouldn't use innodb? Travis -- 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: ODBC Encryption?
What you've seen is what you get. None. Try tunneling through SSH. Try this link to learn more: http://www.cs.kuleuven.ac.be/system/security/ssh/tunnel.shtml -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Online Tutorials for beginners
At 09:54 AM 10/23/2003, Andrew Braithwaite wrote: Hi, Does anyone know of any good mysql tutorials online that would suit someone who has a computer science degree but knows nothing about MySQL. Pointers will be most welcome. Cheers, Andrew Andrew, Try one of these MySQL tutorials: http://www.mysql.com/doc/en/Tutorial.html http://www.analysisandsolutions.com/code/mybasic.htm http://www.devshed.com/Server_Side/MySQL http://www.sqlcourse.com/ http://www.w3schools.com/sql/default.asp http://www.juicystudio.com/tutorial/mysql/ http://www.justphukit.com/mysql/mysql-tutorials-1.php http://sqlzoo.net/ http://www.troobloo.com/tech/mysql.shtml http://perl.about.com/cs/mysql/index.htm http://www.anu.edu.au/web/authors/mysql/manual_Tutorial.html Reference: http://www.mysql.com/doc/en/ (the MySQL manual is quite good too) http://tiger.la.asu.edu/Quick_Ref/MySQL_QuickRef.pdf (MySQL Quick Reference Card) Related links http://dmoz.org/Computers/Software/Databases/MySQL/Tutorials/ Books: MySQL 2nd Edition by Paul Dubois MySQL Cookbook by Paul Dubois Mike P.S. Paul gives me 5 bucks every time I plug his books. ;-) (No, not really, just wishful thinking on my part. His books are very good and if you're serious about MySQL pick them up.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ODBC Encryption?
Thanks. I was thinking about using stunnel. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 3:41 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: ODBC Encryption? What you've seen is what you get. None. Try tunneling through SSH. Try this link to learn more: http://www.cs.kuleuven.ac.be/system/security/ssh/tunnel.shtml -- 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 vs myisam
At 02:37 PM 10/23/2003, you wrote: I thought I read a message on this list that said you can't use full text indexes with InnoDB yet. Can anyone confirm that? - Gabriel On Thursday, October 23, 2003, at 11:50 AM, Travis Reeder wrote: I'm sure this has been asked before, but after seeing some benchmarks, it looks like using innodb is a no brainer. Just want to know why you wouldn't use innodb? Travis Travis, Correct. Innodb DOES NOT support full text indexes. Someday perhaps, but not now. :-( Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MATCH ... AGAINST('...' WITH QUERY EXPANSION) syntax?
Hi Sergei, More full-text questions from me since I just noticed your code and doc changes. :-) What does this new WITH QUERY EXPANSION syntax do? More relevant results? More flexible? Faster? Is it for NLQ, boolean, or both (since both ft_[nlq | boolean]_search.c are changed)? Does it have something to do with 2 level indexes, or aren't they being used yet? Sorry for all the questions! Also noticed that ft_max_word_len_for_sort has become a constant, instead of run-time definable, and ft_query_expansion_limit replaces it, though they don't sound related. I'm wondering about max_..._for_sort because, at least in 4.0, I need to lower it to 10-12 to keep the temp files smaller when building the index. :-( Are the temp files going to get too big in 4.1 when I can't adjust ft_max_word_len_for_sort or is the algorithm different when indexing? If the temp files are the same size as 4.0, I wish ft_max_word_len_for_sort would be restored or I'm going to have problems. :-( Thanks for your time! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
Hello Heikki, I didn't understand the concept of tablespaces utilized, - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 22, 2003 12:00 PM Subject: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 Hi! InnoDB is a MySQL table type which provides transactions, row-level locking, non-locking consistent SELECT (multiversioned concurrency control), foreign key constraints, and a non-free hot backup tool for backing up InnoDB tables. InnoDB is included in all MySQL-4.0 and 4.1 downloads, and also in the MySQL Pro commercial, non-GPL MySQL license. Release 4.0.16 is a bugfix release of the stable 4.0 branch. There are a few known outstanding bugs in InnoDB-4.0.16, but their fixing has been delayed, because we are allocating all free resources to preparing the upcoming MySQL-4.1.1 release. --- A sneak peek of MySQL-4.1.1: NOTE: if you upgrade to InnoDB-4.1.1, you cannot downgrade any more! That is because earlier versions of InnoDB are not aware of multiple tablespaces. The public BitKeeper source tree of 4.1 now supports multiple tablespaces for InnoDB. You can enable them with the line innodb_file_per_table in the [mysqld] section of my.cnf. Then InnoDB stores each table into its own file tablename.ibd in the database directory where the table belongs. This is like MyISAM does, but MyISAM divides the table to a data file tablename.MYD and the index file tablename.MYI. For InnoDB, both the data and the indexes are in the .ibd file. If you remove the line, then InnoDB creates tables in the ibdata files again. InnoDB always needs the 'system tablespace', .ibd files are not enough. The system tablespace consists of the familiar ibdata files. InnoDB puts there its internal data dictionary and undo logs. You CANNOT FREELY MOVE .ibd files around, like you can MyISAM tables. This is because the table definition is stored in the InnoDB system tablespace, and also because InnoDB must preserve the consistency of transaction id's and log sequence numbers. You can move an .ibd file and the associated table from a database to another (within the same MySQL/InnoDB installation) with the familiar RENAME trick: RENAME TABLE olddatabasename.tablename TO newdatabasename.tablename; If you have a 'clean' backup of an .ibd file taken from the SAME MySQL/InnoDB installation, you can restore it to an InnoDB database with the commands: ALTER TABLE tablename DISCARD TABLESPACE; /* CAUTION: deletes the current .ibd file! */ put the backup .ibd file to the proper place ALTER TABLE tablename IMPORT TABLESPACE; 'Clean' in this context means: 1) There are no uncommitted modifications by transactions in the .ibd file. 2) There are no unmerged insert buffer entries to the .ibd file. 3) Purge has removed all delete-marked index records from the .ibd file. 4) mysqld has flushed all modified pages of the .ibd file from the buffer pool to the file. You can make such a clean backup .ibd file with the following method. 1) Stop all activity from the mysqld server and commit all transactions. 2) Wait that SHOW INNODB STATUS\G shows that there are no active transactions in the database, and the 'main thread' of InnoDB is 'Waiting for server activity'. Then you can take a copy of the .ibd file. Another (non-free) method to make such a clean .ibd file is to 1) Use InnoDB Hot Backup to backup the InnoDB installation. 2) Start a second mysqld server on the backup and let it clean up the .ibd files. It is in the TODO to allow moving clean .ibd files also to another MySQL/InnoDB installation. That requires resetting of trx id's and log sequence numbers in the .ibd file. --- InnoDB changelog for 4.0.16: * Fixed a bug: contrary to what was said in the manual, in a locking read InnoDB set two record locks if a unique exact match search condition was used on a multi-column unique key. For a single column unique key it worked right. * Fixed a bug: if one used the rename trick #sql... - rsql... described in section 15.1 of http://www.innodb.com/ibman.html to recover a temporary table, InnoDB asserted in row_mysql_lock_data_dictionary(). * There are several outstanding non-critical bugs reported in the MySQL bugs database. Their fixing has been delayed, because resources are allocated to the upcoming 4.1.1 release. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.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]
Data modelling software
Hi Group, I am looking for the data modelling tool which should incorporate the generalization/specialization feature of database design. Does anyone know better tool for it? So far i have tried datanamic and case studio but none of them has above mentioned feature. Thanks Hardik Doshi - Do you Yahoo!? The New Yahoo! Shopping - with improved product search
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
(Sorry for the last email, it's not complete). Hello Heikki, Sorry, but I didn't understand the concept of tablespace applied. It's different from Oracle, right? AFAIK, tablespace is utilized to logically group tables into one (or more) files. And to group indexes into another files... But you said that the each table (with its indexes) will be in one file... is there an reason? Is it better than split tables and indexes? Thank you. Eduardo - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 22, 2003 12:00 PM Subject: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 Hi! InnoDB is a MySQL table type which provides transactions, row-level locking, non-locking consistent SELECT (multiversioned concurrency control), foreign key constraints, and a non-free hot backup tool for backing up InnoDB tables. InnoDB is included in all MySQL-4.0 and 4.1 downloads, and also in the MySQL Pro commercial, non-GPL MySQL license. Release 4.0.16 is a bugfix release of the stable 4.0 branch. There are a few known outstanding bugs in InnoDB-4.0.16, but their fixing has been delayed, because we are allocating all free resources to preparing the upcoming MySQL-4.1.1 release. --- A sneak peek of MySQL-4.1.1: NOTE: if you upgrade to InnoDB-4.1.1, you cannot downgrade any more! That is because earlier versions of InnoDB are not aware of multiple tablespaces. The public BitKeeper source tree of 4.1 now supports multiple tablespaces for InnoDB. You can enable them with the line innodb_file_per_table in the [mysqld] section of my.cnf. Then InnoDB stores each table into its own file tablename.ibd in the database directory where the table belongs. This is like MyISAM does, but MyISAM divides the table to a data file tablename.MYD and the index file tablename.MYI. For InnoDB, both the data and the indexes are in the .ibd file. If you remove the line, then InnoDB creates tables in the ibdata files again. InnoDB always needs the 'system tablespace', .ibd files are not enough. The system tablespace consists of the familiar ibdata files. InnoDB puts there its internal data dictionary and undo logs. You CANNOT FREELY MOVE .ibd files around, like you can MyISAM tables. This is because the table definition is stored in the InnoDB system tablespace, and also because InnoDB must preserve the consistency of transaction id's and log sequence numbers. You can move an .ibd file and the associated table from a database to another (within the same MySQL/InnoDB installation) with the familiar RENAME trick: RENAME TABLE olddatabasename.tablename TO newdatabasename.tablename; If you have a 'clean' backup of an .ibd file taken from the SAME MySQL/InnoDB installation, you can restore it to an InnoDB database with the commands: ALTER TABLE tablename DISCARD TABLESPACE; /* CAUTION: deletes the current .ibd file! */ put the backup .ibd file to the proper place ALTER TABLE tablename IMPORT TABLESPACE; 'Clean' in this context means: 1) There are no uncommitted modifications by transactions in the .ibd file. 2) There are no unmerged insert buffer entries to the .ibd file. 3) Purge has removed all delete-marked index records from the .ibd file. 4) mysqld has flushed all modified pages of the .ibd file from the buffer pool to the file. You can make such a clean backup .ibd file with the following method. 1) Stop all activity from the mysqld server and commit all transactions. 2) Wait that SHOW INNODB STATUS\G shows that there are no active transactions in the database, and the 'main thread' of InnoDB is 'Waiting for server activity'. Then you can take a copy of the .ibd file. Another (non-free) method to make such a clean .ibd file is to 1) Use InnoDB Hot Backup to backup the InnoDB installation. 2) Start a second mysqld server on the backup and let it clean up the .ibd files. It is in the TODO to allow moving clean .ibd files also to another MySQL/InnoDB installation. That requires resetting of trx id's and log sequence numbers in the .ibd file. --- InnoDB changelog for 4.0.16: * Fixed a bug: contrary to what was said in the manual, in a locking read InnoDB set two record locks if a unique exact match search condition was used on a multi-column unique key. For a single column unique key it worked right. * Fixed a bug: if one used the rename trick #sql... - rsql... described in section 15.1 of http://www.innodb.com/ibman.html to recover a temporary table, InnoDB asserted in row_mysql_lock_data_dictionary(). * There are several outstanding non-critical bugs reported in the MySQL bugs database. Their fixing has been delayed, because resources are allocated to the upcoming 4.1.1 release. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
Improving Query speed - any suggestions?
Hey All-
I am trying to improve the speed of a website and was wondering if
anyone had any ways I can rewrite these queries so that they actually
run with some descent speed.
Its a really nasty query and I'm not sure where to start, I'd like to
now have to redo the tables and I already put some indexes on it which
improved speed a lot but these queries are still very slow. You can most
certainly laugh to yourselves on this one... jsut trying to get some
opinions on what I should do with this.
Thanks-
Matt
SELECT content.row_id AS row_id, content.app_id AS app_id, s1.data AS
niche, s2.data AS type, s3.data AS title, s4.data AS description,
s5.data AS image, s6.data AS last_update, s7.data AS content_link,
s8.data AS unique_id, s9.data AS date_added, s10.data AS
content_provider, s11.data AS user_hits, s12.data AS vote_total,
s13.data AS vote_user_total, s14.data AS channel FROM content LEFT JOIN
content s1 ON s1.field_id=69 AND s1.row_id = content.row_id LEFT JOIN
content s2 ON s2.field_id=70 AND s2.row_id = content.row_id LEFT JOIN
content s3 ON s3.field_id=71 AND s3.row_id = content.row_id LEFT JOIN
content s4 ON s4.field_id=72 AND s4.row_id = content.row_id LEFT JOIN
content s5 ON s5.field_id=73 AND s5.row_id = content.row_id LEFT JOIN
content s6 ON s6.field_id=74 AND s6.row_id = content.row_id LEFT JOIN
content s7 ON s7.field_id=76 AND s7.row_id = content.row_id LEFT JOIN
content s8 ON s8.field_id=84 AND s8.row_id = content.row_id LEFT JOIN
content s9 ON s9.field_id=129 AND s9.row_id = content.row_id LEFT JOIN
content s10 ON s10.field_id=116 AND s10.row_id = content.row_id LEFT
JOIN content s11 ON s11.field_id=118 AND s11.row_id = content.row_id
LEFT JOIN content s12 ON s12.field_id=120 AND s12.row_id =
content.row_id LEFT JOIN content s13 ON s13.field_id=121 AND s13.row_id
= content.row_id LEFT JOIN content s14 ON s14.field_id=125 AND
s14.row_id = content.row_id WHERE content.app_id = 11 AND
unix_timestamp(s6.data)-unix_timestamp('2003-10-23 23:59:59') 0 GROUP
BY row_id ORDER BY last_update desc LIMIT -1
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Improving Query speed - any suggestions?
Glancing at the query, I'm already under the impression that it should
be broken up into multiple queries and code should be used to simulate
the behavior. I find that this method is faster for mysql and a bit more
portable for developers in my opinion.
Other then what is stated above I would have to run it by hand to
determine what the purpose of this query is. Then see if it can be
rewritten.
- Dathan Vance Pattishall
- Sr. Programmer and mySQL DBA for FriendFinder Inc.
- http://friendfinder.com/go/p40688
---Original Message-
--From: Matt Babineau [mailto:[EMAIL PROTECTED]
--Sent: Thursday, October 23, 2003 12:05 PM
--To: [EMAIL PROTECTED]
--Subject: Improving Query speed - any suggestions?
--
--Hey All-
--
--I am trying to improve the speed of a website and was wondering if
--anyone had any ways I can rewrite these queries so that they actually
--run with some descent speed.
--
--Its a really nasty query and I'm not sure where to start, I'd like to
--now have to redo the tables and I already put some indexes on it
which
--improved speed a lot but these queries are still very slow. You can
most
--certainly laugh to yourselves on this one... jsut trying to get some
--opinions on what I should do with this.
--
--Thanks-
--Matt
--
--SELECT content.row_id AS row_id, content.app_id AS app_id, s1.data AS
--niche, s2.data AS type, s3.data AS title, s4.data AS description,
--s5.data AS image, s6.data AS last_update, s7.data AS content_link,
--s8.data AS unique_id, s9.data AS date_added, s10.data AS
--content_provider, s11.data AS user_hits, s12.data AS vote_total,
--s13.data AS vote_user_total, s14.data AS channel FROM content LEFT
JOIN
--content s1 ON s1.field_id=69 AND s1.row_id = content.row_id LEFT JOIN
--content s2 ON s2.field_id=70 AND s2.row_id = content.row_id LEFT JOIN
--content s3 ON s3.field_id=71 AND s3.row_id = content.row_id LEFT JOIN
--content s4 ON s4.field_id=72 AND s4.row_id = content.row_id LEFT JOIN
--content s5 ON s5.field_id=73 AND s5.row_id = content.row_id LEFT JOIN
--content s6 ON s6.field_id=74 AND s6.row_id = content.row_id LEFT JOIN
--content s7 ON s7.field_id=76 AND s7.row_id = content.row_id LEFT JOIN
--content s8 ON s8.field_id=84 AND s8.row_id = content.row_id LEFT JOIN
--content s9 ON s9.field_id=129 AND s9.row_id = content.row_id LEFT
JOIN
--content s10 ON s10.field_id=116 AND s10.row_id = content.row_id LEFT
--JOIN content s11 ON s11.field_id=118 AND s11.row_id = content.row_id
--LEFT JOIN content s12 ON s12.field_id=120 AND s12.row_id =
--content.row_id LEFT JOIN content s13 ON s13.field_id=121 AND
s13.row_id
--= content.row_id LEFT JOIN content s14 ON s14.field_id=125 AND
--s14.row_id = content.row_id WHERE content.app_id = 11 AND
--unix_timestamp(s6.data)-unix_timestamp('2003-10-23 23:59:59') 0
GROUP
--BY row_id ORDER BY last_update desc LIMIT -1
--
--
--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]
Syntax for SQL Query - used to work with Access
Hi This query used to work with an Access database: SELECT *, (SELECT COUNT (*) FROM Links WHERE Links.CAT_ID = Categories.CAT_ID AND LINK_APPROVED = 'Yes') AS LINK_COUNT FROM Categories ORDER BY CAT_NAME ASC Now that we've upgraded to MySQL, the same ASP script returns this error: ADODB.Recordset.1 error '80004005' SQLState: 42000 Native Error Code: 1064 [TCX][MyODBC]syntax error near 'SELECT COUNT (*) FROM Links WHERE Links.CAT_ID = Categories.CAT_ID AND LINK_AP' at line 1 Could anyone tell me what I need to do to fix it? Unsubscribing now so very grateful if you could e-mail me with any advice -- thanks! --- Square Eye's e-mail is certified to be virus-free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.525 / Virus Database: 322 - Release Date: 09/10/2003
Re: Syntax for SQL Query - used to work with Access
Hi Trevor, I think that query might work as-is in MySQL 4.1. However, the current production version doesn't support subqueries. Your query can be rewritten with a join like this I think: SELECT C.*, COUNT(*) AS LINK_COUNT FROM Categories C INNER JOIN Links L ON (L.CAT_ID=C.CAT_ID AND L.LINK_APPROVED='Yes') GROUP BY C.CAT_ID ORDER BY CAT_NAME Hope that helps. Matt - Original Message - From: Trevor Sather Sent: Thursday, October 23, 2003 6:49 PM Subject: Syntax for SQL Query - used to work with Access Hi This query used to work with an Access database: SELECT *, (SELECT COUNT (*) FROM Links WHERE Links.CAT_ID = Categories.CAT_ID AND LINK_APPROVED = 'Yes') AS LINK_COUNT FROM Categories ORDER BY CAT_NAME ASC Now that we've upgraded to MySQL, the same ASP script returns this error: ADODB.Recordset.1 error '80004005' SQLState: 42000 Native Error Code: 1064 [TCX][MyODBC]syntax error near 'SELECT COUNT (*) FROM Links WHERE Links.CAT_ID = Categories.CAT_ID AND LINK_AP' at line 1 Could anyone tell me what I need to do to fix it? Unsubscribing now so very grateful if you could e-mail me with any advice -- thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Improving Query speed - any suggestions?
At 02:05 PM 10/23/2003, you wrote:
Hey All-
I am trying to improve the speed of a website and was wondering if
anyone had any ways I can rewrite these queries so that they actually
run with some descent speed.
Its a really nasty query and I'm not sure where to start, I'd like to
now have to redo the tables and I already put some indexes on it which
improved speed a lot but these queries are still very slow. You can most
certainly laugh to yourselves on this one... jsut trying to get some
opinions on what I should do with this.
Thanks-
Matt
Matt,
Instead of doing table joins on a large number of tables, in my
application I've created a balance line procedure that would sync the
linked tables with the primary table. (The tables are NOT linked using
SQL) This works if there is a 1:1 correspondence between the tables. So
each table has a simple Select statement for just that table and an
optional Where clause and the table is sorted on a field that is common to
all tables. If the key field of any of the secondary tables are less than
the key field in the primary table, then the secondary records are skipped
until it matches or exceeds the primary key field. It it exceeds the
primary field then the secondary record is missing and you would use all
null's for the secondary table.
Example:
select cust_id, col1a, col1b from table1 order by cust_id
select cust_id, col2a, col2b from table2 order by cust_id
select cust_id, col3a, col3b from table3 order by cust_id
A GetNextRcd procedure would sync the records so it returns 3 records all
pointing to the same cust_id. A loop would then traverse through the table.
The other solution would be to create a RAM disk for your MySQL
work directory. This will speed up the table joins. MySQL allows for
multiple work volumes so the first volume could be the ram disk. If it
overflows that, I'm thinking it will go to your second work directory. I
haven't tried it, but it may be something to look into.
Mike
SELECT content.row_id AS row_id, content.app_id AS app_id, s1.data AS
niche, s2.data AS type, s3.data AS title, s4.data AS description,
s5.data AS image, s6.data AS last_update, s7.data AS content_link,
s8.data AS unique_id, s9.data AS date_added, s10.data AS
content_provider, s11.data AS user_hits, s12.data AS vote_total,
s13.data AS vote_user_total, s14.data AS channel FROM content LEFT JOIN
content s1 ON s1.field_id=69 AND s1.row_id = content.row_id LEFT JOIN
content s2 ON s2.field_id=70 AND s2.row_id = content.row_id LEFT JOIN
content s3 ON s3.field_id=71 AND s3.row_id = content.row_id LEFT JOIN
content s4 ON s4.field_id=72 AND s4.row_id = content.row_id LEFT JOIN
content s5 ON s5.field_id=73 AND s5.row_id = content.row_id LEFT JOIN
content s6 ON s6.field_id=74 AND s6.row_id = content.row_id LEFT JOIN
content s7 ON s7.field_id=76 AND s7.row_id = content.row_id LEFT JOIN
content s8 ON s8.field_id=84 AND s8.row_id = content.row_id LEFT JOIN
content s9 ON s9.field_id=129 AND s9.row_id = content.row_id LEFT JOIN
content s10 ON s10.field_id=116 AND s10.row_id = content.row_id LEFT
JOIN content s11 ON s11.field_id=118 AND s11.row_id = content.row_id
LEFT JOIN content s12 ON s12.field_id=120 AND s12.row_id =
content.row_id LEFT JOIN content s13 ON s13.field_id=121 AND s13.row_id
= content.row_id LEFT JOIN content s14 ON s14.field_id=125 AND
s14.row_id = content.row_id WHERE content.app_id = 11 AND
unix_timestamp(s6.data)-unix_timestamp('2003-10-23 23:59:59') 0 GROUP
BY row_id ORDER BY last_update desc LIMIT -1
--
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: Improving Query speed - any suggestions?
Hello,
as i see you use one table to store all the data, but the cohesive data are
split into 15! different rows.
I think to get the best performance you shoud redesign your tabel.
Use at last first normal form [1NF], if the app_id is uniq this can be the
primary key [which will speed up the query] .
Data which have the same row_id should be in one row.
Your table definition shoud look like this:
create table content (
app_id MEDIUMINT NOT NULL AUTO_INCREMENT,
niche??? , -- maybe int
type int,
titlevarchar(200),
description text,
image???, -- maybe varchar
last_update datetime,
content_link varchar(200),
unique_idint,
date_added datetime,
content_provider int,
user_hitsint,
vote_total int,
vote_user_total int,
channel int,
--...
primary key ( app_id )
);
As i see you get at last 129 different filed type [s9.field_id=129], you can
split the data into different tables. [Use heigher normal form.]
But if you don't want redesign the tables and all the different fields exists
then use join instead of left join and then the group by is needless.
burci
Thursday, October 23, 2003, 9:05:26 PM, you wrote:
MB Hey All-
MB I am trying to improve the speed of a website and was wondering if
MB anyone had any ways I can rewrite these queries so that they actually
MB run with some descent speed.
MB Its a really nasty query and I'm not sure where to start, I'd like to
MB now have to redo the tables and I already put some indexes on it which
MB improved speed a lot but these queries are still very slow. You can most
MB certainly laugh to yourselves on this one... jsut trying to get some
MB opinions on what I should do with this.
MB Thanks-
MB Matt
MB SELECT content.row_id AS row_id, content.app_id AS app_id, s1.data AS
MB niche, s2.data AS type, s3.data AS title, s4.data AS description,
MB s5.data AS image, s6.data AS last_update, s7.data AS content_link,
MB s8.data AS unique_id, s9.data AS date_added, s10.data AS
MB content_provider, s11.data AS user_hits, s12.data AS vote_total,
MB s13.data AS vote_user_total, s14.data AS channel FROM content LEFT JOIN
MB content s1 ON s1.field_id=69 AND s1.row_id = content.row_id LEFT JOIN
MB content s2 ON s2.field_id=70 AND s2.row_id = content.row_id LEFT JOIN
MB content s3 ON s3.field_id=71 AND s3.row_id = content.row_id LEFT JOIN
MB content s4 ON s4.field_id=72 AND s4.row_id = content.row_id LEFT JOIN
MB content s5 ON s5.field_id=73 AND s5.row_id = content.row_id LEFT JOIN
MB content s6 ON s6.field_id=74 AND s6.row_id = content.row_id LEFT JOIN
MB content s7 ON s7.field_id=76 AND s7.row_id = content.row_id LEFT JOIN
MB content s8 ON s8.field_id=84 AND s8.row_id = content.row_id LEFT JOIN
MB content s9 ON s9.field_id=129 AND s9.row_id = content.row_id LEFT JOIN
MB content s10 ON s10.field_id=116 AND s10.row_id = content.row_id LEFT
MB JOIN content s11 ON s11.field_id=118 AND s11.row_id = content.row_id
MB LEFT JOIN content s12 ON s12.field_id=120 AND s12.row_id =
MB content.row_id LEFT JOIN content s13 ON s13.field_id=121 AND s13.row_id
MB = content.row_id LEFT JOIN content s14 ON s14.field_id=125 AND
MB s14.row_id = content.row_id WHERE content.app_id = 11 AND
MB unix_timestamp(s6.data)-unix_timestamp('2003-10-23 23:59:59') 0 GROUP
MB BY row_id ORDER BY last_update desc LIMIT -1
--
[nick]:burci [hp]:http://peter.buri.hu [mailto]:[EMAIL PROTECTED] [motto]:Music makes
life easier to survive!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
does mysql supports russion language or any other language?
Hi, i have this form that goes directly.. to a database. I have some clients who dont use english. If they enter on a form a special russion or chinese character will mysql translate it properly? -- - Louie Miranda http://www.axishift.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Improving Query speed - any suggestions?
Thanks everyone for your input, I'll try the ramdisk idea, I read about
someone else who tried that and had some success. Beyond, that I'm gonna
take the long route and redesign the database to be a bit more
conventional.
Thanks!
Matt
On Thu, 2003-10-23 at 20:28, Peter Buri wrote:
Hello,
as i see you use one table to store all the data, but the cohesive data are
split into 15! different rows.
I think to get the best performance you shoud redesign your tabel.
Use at last first normal form [1NF], if the app_id is uniq this can be the
primary key [which will speed up the query] .
Data which have the same row_id should be in one row.
Your table definition shoud look like this:
create table content (
app_id MEDIUMINT NOT NULL AUTO_INCREMENT,
niche??? , -- maybe int
type int,
titlevarchar(200),
description text,
image???, -- maybe varchar
last_update datetime,
content_link varchar(200),
unique_idint,
date_added datetime,
content_provider int,
user_hitsint,
vote_total int,
vote_user_total int,
channel int,
--...
primary key ( app_id )
);
As i see you get at last 129 different filed type [s9.field_id=129], you can
split the data into different tables. [Use heigher normal form.]
But if you don't want redesign the tables and all the different fields exists
then use join instead of left join and then the group by is needless.
burci
Thursday, October 23, 2003, 9:05:26 PM, you wrote:
MB Hey All-
MB I am trying to improve the speed of a website and was wondering if
MB anyone had any ways I can rewrite these queries so that they actually
MB run with some descent speed.
MB Its a really nasty query and I'm not sure where to start, I'd like to
MB now have to redo the tables and I already put some indexes on it which
MB improved speed a lot but these queries are still very slow. You can most
MB certainly laugh to yourselves on this one... jsut trying to get some
MB opinions on what I should do with this.
MB Thanks-
MB Matt
MB SELECT content.row_id AS row_id, content.app_id AS app_id, s1.data AS
MB niche, s2.data AS type, s3.data AS title, s4.data AS description,
MB s5.data AS image, s6.data AS last_update, s7.data AS content_link,
MB s8.data AS unique_id, s9.data AS date_added, s10.data AS
MB content_provider, s11.data AS user_hits, s12.data AS vote_total,
MB s13.data AS vote_user_total, s14.data AS channel FROM content LEFT JOIN
MB content s1 ON s1.field_id=69 AND s1.row_id = content.row_id LEFT JOIN
MB content s2 ON s2.field_id=70 AND s2.row_id = content.row_id LEFT JOIN
MB content s3 ON s3.field_id=71 AND s3.row_id = content.row_id LEFT JOIN
MB content s4 ON s4.field_id=72 AND s4.row_id = content.row_id LEFT JOIN
MB content s5 ON s5.field_id=73 AND s5.row_id = content.row_id LEFT JOIN
MB content s6 ON s6.field_id=74 AND s6.row_id = content.row_id LEFT JOIN
MB content s7 ON s7.field_id=76 AND s7.row_id = content.row_id LEFT JOIN
MB content s8 ON s8.field_id=84 AND s8.row_id = content.row_id LEFT JOIN
MB content s9 ON s9.field_id=129 AND s9.row_id = content.row_id LEFT JOIN
MB content s10 ON s10.field_id=116 AND s10.row_id = content.row_id LEFT
MB JOIN content s11 ON s11.field_id=118 AND s11.row_id = content.row_id
MB LEFT JOIN content s12 ON s12.field_id=120 AND s12.row_id =
MB content.row_id LEFT JOIN content s13 ON s13.field_id=121 AND s13.row_id
MB = content.row_id LEFT JOIN content s14 ON s14.field_id=125 AND
MB s14.row_id = content.row_id WHERE content.app_id = 11 AND
MB unix_timestamp(s6.data)-unix_timestamp('2003-10-23 23:59:59') 0 GROUP
MB BY row_id ORDER BY last_update desc LIMIT -1
--
[nick]:burci [hp]:http://peter.buri.hu [mailto]:[EMAIL PROTECTED] [motto]:Music
makes life easier to survive!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: GRANT problem
I was having the same problems trying to get a user added to a new DB, so here's are the commands I used: GRANT INSERT, DELETE, UPDATE, SELECT ON *.* TO user@localhost IDENTIFIED BY 'password'; FLUSH PRIVILEGES; where user and password are whatever you need it to be. I've never had much luck with % as host, so specify localhost for you user, and you should be all set. Nat -Original Message- From: Rory McKinley [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 6:21 AM To: Datatal AB - Gauffin, Jonas Cc: [EMAIL PROTECTED] Subject: Re: GRANT problem Ok. I'm stumped. If I'm reading the manual correctly, you were right about not hving to specify the host..the only difference between your version and the manual is that they use single quotes around the user name but I can't see how that makes any difference. I normally just specfiy hosts because using wildcards for hosts makes me nervous - hey, even paranoid people have enemies :) If anyone knows the answer to this I would be interested in knowing what it is too Sorry I can't be of more help Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Datatal AB - Gauffin, Jonas [EMAIL PROTECTED] To: Rory McKinley [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 12:05 PM Subject: SV: GRANT problem it works if I specify the host. Why doesn't % work as host? -Ursprungligt meddelande- Från: Rory McKinley [mailto:[EMAIL PROTECTED] Skickat: den 23 oktober 2003 11:56 Till: Datatal AB - Gauffin, Jonas Ämne: Re: GRANT problem thinking. In one of your follow up posts I see that you have the command as this : C:\mysql\bin\mysql phonewatch -h ts2test -u datatal -pMYPASSWORD Try C:\mysql\bin\mysql phonewatch -h ts2test -u datatal -p And enter password at the prompt Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Datatal AB - Gauffin, Jonas [EMAIL PROTECTED] To: Rory McKinley [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 11:45 AM Subject: SV: GRANT problem Not sure if this will help - in your GRANT statement do you not need to specify a host for the user e.g. GRANT.. to datatal @ your_host_name.?. GRANT uses % as host if none is specified. % = all hosts. any other ideas? -- 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: GRANT problem
Have you tried: GRANT INSERT, DELETE, UPDATE, SELECT ON *.* TO user@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; On Thu, 2003-10-23 at 21:03, Nathaniel Mallet wrote: I was having the same problems trying to get a user added to a new DB, so here's are the commands I used: GRANT INSERT, DELETE, UPDATE, SELECT ON *.* TO user@localhost IDENTIFIED BY 'password'; FLUSH PRIVILEGES; where user and password are whatever you need it to be. I've never had much luck with % as host, so specify localhost for you user, and you should be all set. Nat -Original Message- From: Rory McKinley [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 6:21 AM To: Datatal AB - Gauffin, Jonas Cc: [EMAIL PROTECTED] Subject: Re: GRANT problem Ok. I'm stumped. If I'm reading the manual correctly, you were right about not hving to specify the host..the only difference between your version and the manual is that they use single quotes around the user name but I can't see how that makes any difference. I normally just specfiy hosts because using wildcards for hosts makes me nervous - hey, even paranoid people have enemies :) If anyone knows the answer to this I would be interested in knowing what it is too Sorry I can't be of more help Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Datatal AB - Gauffin, Jonas [EMAIL PROTECTED] To: Rory McKinley [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 12:05 PM Subject: SV: GRANT problem it works if I specify the host. Why doesn't % work as host? -Ursprungligt meddelande- Frn: Rory McKinley [mailto:[EMAIL PROTECTED] Skickat: den 23 oktober 2003 11:56 Till: Datatal AB - Gauffin, Jonas mne: Re: GRANT problem thinking. In one of your follow up posts I see that you have the command as this : C:\mysql\bin\mysql phonewatch -h ts2test -u datatal -pMYPASSWORD Try C:\mysql\bin\mysql phonewatch -h ts2test -u datatal -p And enter password at the prompt Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Datatal AB - Gauffin, Jonas [EMAIL PROTECTED] To: Rory McKinley [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 11:45 AM Subject: SV: GRANT problem Not sure if this will help - in your GRANT statement do you not need to specify a host for the user e.g. GRANT.. to datatal @ your_host_name.?. GRANT uses % as host if none is specified. % = all hosts. any other ideas? -- 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]
Potential Bug with inserting nulls with MySQL 4.1.1 nightly snaps hot
Hello, I think I have found a bug in the MySQL 4.1.1 C Binding API with inserting NULL values using the bind API. Can someone please send me an address that I can send a test case to the MySQL development staff so they can take a look at it? Alternatively can someone tell me if this is the appropriate forum to post such items? Thank you very much for your help, Dave Ritter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Improving Query speed - any suggestions?
Hello,
one more idea, use something like this [ i hope it's correct :) ]
SELECT
content.row_id AS row_id,
content.app_id AS app_id,
CASE s1.field_id
when 69 then niche,
when 70 then type,
when 71 then title,
when 72 then description,
when 73 then image,
when 74 then last_update,
when 76 then content_link,
when 84 then unique_id,
when 129 then date_added,
when 116 then content_provider,
when 118 then user_hits,
when 120 then vote_total,
when 121 then vote_user_total,
when 125 then channel
END as fieldname,
s1.data
FROM
content JOIN
content s1 ON s1.row_id = content.row_id and JOIN
content s2 ON s2.field_id=74 AND s2.row_id = content.row_id
WHERE
content.app_id = 11 AND
unix_timestamp(s2.data)-unix_timestamp('2003-10-23 23:59:59') 0 AND
s1.field_id IN (69,70,71,72,73,74,76,84,129,116,118,120,121,125)
ORDER BY
last_update desc LIMIT -1
I think it's can be fast enough, but your aplication have to collect the different
fields...
burci
Thursday, October 23, 2003, 11:50:08 PM, you wrote:
MB Thanks everyone for your input, I'll try the ramdisk idea, I read about
MB someone else who tried that and had some success. Beyond, that I'm gonna
MB take the long route and redesign the database to be a bit more
MB conventional.
MB Thanks!
MB Matt
MB On Thu, 2003-10-23 at 20:28, Peter Buri wrote:
Hello,
as i see you use one table to store all the data, but the cohesive data are
split into 15! different rows.
I think to get the best performance you shoud redesign your tabel.
Use at last first normal form [1NF], if the app_id is uniq this can be the
primary key [which will speed up the query] .
Data which have the same row_id should be in one row.
Your table definition shoud look like this:
create table content (
app_id MEDIUMINT NOT NULL AUTO_INCREMENT,
niche??? , -- maybe int
type int,
titlevarchar(200),
description text,
image???, -- maybe varchar
last_update datetime,
content_link varchar(200),
unique_idint,
date_added datetime,
content_provider int,
user_hitsint,
vote_total int,
vote_user_total int,
channel int,
--...
primary key ( app_id )
);
As i see you get at last 129 different filed type [s9.field_id=129], you can
split the data into different tables. [Use heigher normal form.]
But if you don't want redesign the tables and all the different fields exists
then use join instead of left join and then the group by is needless.
burci
Thursday, October 23, 2003, 9:05:26 PM, you wrote:
MB Hey All-
MB I am trying to improve the speed of a website and was wondering if
MB anyone had any ways I can rewrite these queries so that they actually
MB run with some descent speed.
MB Its a really nasty query and I'm not sure where to start, I'd like to
MB now have to redo the tables and I already put some indexes on it which
MB improved speed a lot but these queries are still very slow. You can most
MB certainly laugh to yourselves on this one... jsut trying to get some
MB opinions on what I should do with this.
MB Thanks-
MB Matt
MB SELECT content.row_id AS row_id, content.app_id AS app_id, s1.data AS
MB niche, s2.data AS type, s3.data AS title, s4.data AS description,
MB s5.data AS image, s6.data AS last_update, s7.data AS content_link,
MB s8.data AS unique_id, s9.data AS date_added, s10.data AS
MB content_provider, s11.data AS user_hits, s12.data AS vote_total,
MB s13.data AS vote_user_total, s14.data AS channel FROM content LEFT JOIN
MB content s1 ON s1.field_id=69 AND s1.row_id = content.row_id LEFT JOIN
MB content s2 ON s2.field_id=70 AND s2.row_id = content.row_id LEFT JOIN
MB content s3 ON s3.field_id=71 AND s3.row_id = content.row_id LEFT JOIN
MB content s4 ON s4.field_id=72 AND s4.row_id = content.row_id LEFT JOIN
MB content s5 ON s5.field_id=73 AND s5.row_id = content.row_id LEFT JOIN
MB content s6 ON s6.field_id=74 AND s6.row_id = content.row_id LEFT JOIN
MB content s7 ON s7.field_id=76 AND s7.row_id = content.row_id LEFT JOIN
MB content s8 ON s8.field_id=84 AND s8.row_id = content.row_id LEFT JOIN
MB content s9 ON s9.field_id=129 AND s9.row_id = content.row_id LEFT JOIN
MB content s10 ON s10.field_id=116 AND s10.row_id = content.row_id LEFT
MB JOIN content s11 ON s11.field_id=118 AND s11.row_id = content.row_id
MB LEFT JOIN content s12 ON s12.field_id=120 AND s12.row_id =
MB content.row_id LEFT JOIN content s13 ON s13.field_id=121 AND s13.row_id
MB = content.row_id LEFT JOIN content s14 ON s14.field_id=125 AND
MB s14.row_id =
Re: replication redirector
hi , and thanks for your answer, On Mon, Oct 20, 2003 at 07:03:37PM +0200, Benjamin KRIEF wrote: the problem is that it's quite impossible to fine tune the load between the servers , and my slave is a bit too busy , so the replication thread is becoming very slow , and sometimes , i can see that the slave is more than 2 hours behind the master in terms of replication , and this really is a pb for me. Two hours?!? Wow. as you say =) i looked everywhere for a high-priority-updates option, but i can't find it.tell me if i'm wrong but i deduced that i can't ask the slave to wait until it reached the master before answering selects. Is your bottleneck CPU, I/O, or lock contention? Is your slave running on worse hardware than your master? my bottleneck is CPU , definitely. the load can go up to 35 in peak hours. no swapping occurs , my key efficiency is at 99,89 . and yes , my slave hardware is worse than my master : master= 2xPIII 1Ghz , 1 Go ram slave = 1 PIV 2,4Ghz, 1Go ram i also have to admit that the webserver is on the slave :( so i've started using more regexps to redirect the queries , but it's quite awful to my eyes. i would really like to input the ratio of select queries i'd like to send on the slave . Ouch. That's clearly not a good solution. what about , instead of round robin dns , writing a small perl script , including a socket listening on the port 3306 , this small script would have to redirect the sql requests on the slave or on the master , but this time , i could fine tune it , and input for instance 0,3 for the slave , so that 3 requests out of 10 would go to the slave. Have you looked at SQL Relay? thanks for that one . it appears to me that it's exactly what i was looking for. bye * Benjamin KRIEF * Directeur Technique * IGUANE Studio Tel:01.56.55.54.20 * 5-7-9 passage des Cloys Fax:01.56.55.54.24* 75018 PARIS Gsm:06.12.56.50.41 * mailto:[EMAIL PROTECTED] * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Simple contains query question
I have a field in the table, call it 'stuff', and it contains a list of values that are comma separated. i.e. 'Books,DVDs,Video Tapes,CDs' I want to SELECT out all records that have an item. I know IN works the other way, so is there something that will get what I want like this: SELECT * FROM Inventory WHERE stuff CONTAINS 'Books' ORDER BY name I appreciate any help. Dale Hans -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie: need example of create table syntax
hi.. i'm currently learning mysql and interesting to constraint, relational and check function. could anyone give some example please ? any help is appreciated. thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Easiest way to output to a Latex2e tabular format???
I am relatively new to mysql, latex, and linux in general. I have created a small table of about one hundred entries that I would like to print out as a table in a latex document. The form of the latex file needs to be something like: field1 field2 field3 \\ \hline As I am a novice please provide an example verbatim of the command I need to enter to accomplish this. Thank you. -Abner __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0
On Thu, Oct 23, 2003 at 07:30:55PM +0200, Angel Todorov wrote: Hello list, Can you tell me when approximately is the 5.0 version of MySQL going to be released? Thanks. Probably not. MySQL doesn't have a fixed release schedule. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 40 days, processed 1,526,471,952 queries (439/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Saving Binary Files to mysql
How to save binary files to mysql ? I want to save *.exe whole in a binary files, after that I want to save it to mysql. thanks - Do you Yahoo!? The New Yahoo! Shopping - with improved product search
Re: gemini
On Tue, Oct 21, 2003 at 05:22:07PM -0400, Gabriel Ricard wrote: Someone recently asked about Gemini tables on this list and it got me curious about what ever happened to that technology... NuSphere still seems to exist, and they still sell their PHPEd product along with the NuSphere Technology Platform which appears to contain a build of MySQL. Did they ever GPL Gemini? Or did they just take it away? I think they gave up on it. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 40 days, processed 1,527,399,757 queries (439/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
