Client connection character set be changed after a long while?
My programm connect to MYSQL server, adn set it's charactor set by "set NAMES gbk", after a long while, like one night, I found the client charactor set have been changed to latin, it seems related to some "timeout" value. why the characotor set would be changed? My programm use MYSQL C API. Thanks Hanson
Fw: Import oracle
- Original Message - From: "Renish" <[EMAIL PROTECTED]> To: Sent: Thursday, October 19, 2006 1:08 PM Subject: Fw: Import oracle - Original Message - From: "Renish" <[EMAIL PROTECTED]> To: Sent: Thursday, October 19, 2006 10:28 AM Subject: Import oracle Can anyone tell me how can I import the *.gra (oracle db file) files to Navicat or Acess. Pls let me know in steps as I am v new to this field. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Import oracle
- Original Message - From: "Renish" <[EMAIL PROTECTED]> To: Sent: Thursday, October 19, 2006 10:28 AM Subject: Import oracle Can anyone tell me how can I import the *.gra (oracle db file) files to Navicat or Acess. Pls let me know in steps as I am v new to this field. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Import oracle
Can anyone tell me how can I import the *.gra (oracle db file) files to Navicat or Acess. Pls let me know in steps as I am v new to this field. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlclient in Apache
In the last episode (Oct 18), Danny Swarzman said: > I'm developing an Apache module that uses mySQL. It needs to be able > to talk to a remote host. > > I'm doing this in a Mac. > > I have a simple program in C that calls mysql_real_connect(). It > works with a remote host and with the localhost. > > When I put the same code into my Apache module, the call to > mysql_real_connect() fails. > > Anyone have any idea why this is happening? In the last episode (Oct 18), Danny Swarzman said: > I posted a question about running mysql in an Apache module. Maybe I > need a list with a different focus. Please suggest. This list is fine; you just need to include more detail, I think. Like the error code you get from mysql_real_connect(), for starters. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Anyone know a good list for mysql under the hood?
I posted a question about running mysql in an Apache module. Maybe I need a list with a different focus. Please suggest. -Danny Swarzman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inline View
Hi Melissa, What's wrong with phrasing your query as : select distinct i.reportid, ivalue as IPAddress, ivalue as Computer From item where reportid=1; /* LIMIT 3 */ -- or something like this if you do want 3 rows anway. Am I putting LIMIT right? -- Asif On 10/19/06, Melissa Dougherty <[EMAIL PROTECTED]> wrote: I'm trying to take table data and display the data horizontal I need to take the column and show the results in one row. I have tried several different inline view (queries) and get multiple rows. Here is an example It brings back three rows with each column in a different row. select distinct i.reportid, (select ivalue from item where ifield like '%IP Address%' and ifield = i.ifield and ivalue = i.ivalue) AS IPAddress, (select ivalue from item where ifield = 'Computer Name' and ifield = i.ifield and ivalue = i.ivalue) AS Computer from item i where i.reportid = 1 order by 1 Any suggestions? Thanks, Melissa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get into mysql command line?
Hi Cornelia, On 10/16/06, Cornelia Menzel <[EMAIL PROTECTED]> wrote: Anyhow, if somebody has a glue of what was or could have been the problem, I am interested to know what it was. What really annoys me, is that I have not found what it was. Though I have used only 5.x versions but I got similar errors when I mucked with the filesystem permissions. The solution in my case was to make sure that the MySQL user account (that you use to run MySQL server with) has full permissions on MySQL and other related folders such a the data dir, innodb dir, etc. -- Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Camp: Proven Scaling offering Free Ride
Hi All, As you may know, MySQL Camp, a gathering of the best minds in MySQL, is coming up soon, in just over three weeks. It will be held at Google HQ, in Mountain View, California, November 10-12. You can find out more information about the conference here: http://mysqlcamp.org/ Proven Scaling would like to sponsor airfare and hotel for one person to attend who could not otherwise make it. Read more about it here: http://jcole.us/blog/archives/2006/10/18/want-a-free-ride-to-mysql-camp/ If you're interested in attending on our dime, follow the directions in that post, and send an email to [EMAIL PROTECTED] containing: * Your full, real name * Your blog or website, if any * Your company, school, or organization, if any * Your location, and closest airport * Any contributions you’ve made to MySQL or the MySQL community in the form of code, bug fixes, time, or otherwise * Why you feel you are the best or most qualified person for Proven Scaling to sponsor to MySQL Camp * Anything else you’d us like to know about yourself as we consider who to sponsor We will accept proposals until midnight Pacific Daylight Time (GMT-8), Friday, October 20, 2006. That's only a couple of days away! Good luck! Regards, Jeremy -- high performance mysql consulting www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple JOIN on three tables
> > You can join on any row(s) you like. What are you trying to acccomplish? > > I simply want to select the records from my three tables that are > comprised in time interval. > Now, the first table comprises the records until to 2004 year; the > second table unitl 2005 and the third table until 2006. > > If a user select a date (example) between 2005 and 2006 I want to > select the data from two tables In addition to the other answers -- This sounds like a really bad database design, unless you have very specific reasons for doing so. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple JOIN on three tables
>I simply want to select the records from my three tables that are >comprised in time interval. If you want the results in one resultset, apply an appropriate Where clause to each year-table query and union the queries, eg SELECT * FROM a WHERE date BETWEEN '2004-3-1' AND '2004-6-30' UNION SELECT * FROM b WHERE date BETWEEN '2005-3-1' AND '2005-6-30' etc PB - spacemarc wrote: 2006/10/18, Peter Brawley <[EMAIL PROTECTED]>: You can join on any row(s) you like. What are you trying to acccomplish? I simply want to select the records from my three tables that are comprised in time interval. Now, the first table comprises the records until to 2004 year; the second table unitl 2005 and the third table until 2006. If a user select a date (example) between 2005 and 2006 I want to select the data from two tables -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.5/483 - Release Date: 10/18/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlclient in Apache
I'm developing an Apache module that uses mySQL. It needs to be able to talk to a remote host. I'm doing this in a Mac. I have a simple program in C that calls mysql_real_connect(). It works with a remote host and with the localhost. When I put the same code into my Apache module, the call to mysql_real_connect() fails. Anyone have any idea why this is happening? Anyone have a suggestion of an email list that is more appropriate for this question? -Danny Swarzman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple JOIN on three tables
2006/10/18, William R. Mussatto <[EMAIL PROTECTED]>: Then it is really a UNION. I hope you have the date field as an index otherwise you are looking at a table scan which is always slow. Ok, if I have the field ID that have the same value in three tables but I want to select however the data based on the date (-MM-DD) -- http://www.spacemarc.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple JOIN on three tables
On Wed, October 18, 2006 13:37, spacemarc said: > 2006/10/18, Peter Brawley <[EMAIL PROTECTED]>: > >> You can join on any row(s) you like. What are you trying to acccomplish? > > I simply want to select the records from my three tables that are > comprised in time interval. > Now, the first table comprises the records until to 2004 year; the > second table unitl 2005 and the third table until 2006. > > If a user select a date (example) between 2005 and 2006 I want to > select the data from two tables > > -- > http://www.spacemarc.it > Then it is really a UNION. I hope you have the date field as an index otherwise you are looking at a table scan which is always slow. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB messup
On Wednesday 18 October 2006 23:36, Dan Buettner wrote: > George-Cristian - is it possible that the *.frm files also got moved > about?? Nope. What I'm thinking is the logs got moved, server restarted, it created new ones and... -- George-Cristian Bîrzan Network Engineer ___ RCS & RDS Constanta Tel.: +40341.400.401 / +40341.400.402 Fax: +40341.400.450 http://www.rcs-rds.ro ___ Privileged/Confidential Information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such a case, you should destroy this message and kindly notify the sender by reply e-mail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple JOIN on three tables
2006/10/18, Peter Brawley <[EMAIL PROTECTED]>: You can join on any row(s) you like. What are you trying to acccomplish? I simply want to select the records from my three tables that are comprised in time interval. Now, the first table comprises the records until to 2004 year; the second table unitl 2005 and the third table until 2006. If a user select a date (example) between 2005 and 2006 I want to select the data from two tables -- http://www.spacemarc.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE: InnoDB messup
I haven't tried moving things about with the server running, but have tried to clean up a hosed InnoDB installation after moving files about during an upgrade (trial run on a test system thankfully). You're probably right about the inode thing Jerry. George-Cristian - is it possible that the *.frm files also got moved about?? Dan On 10/18/06, Jerry Schwartz <[EMAIL PROTECTED]> wrote: Dan, have you tried this? It's been a really long time since I pored over the file system internals, and it was on HFS, but what happens when you move a file to another file system? I would think the inode for the open, original file would stay in place and the daemon would keep merrily using it until it was shut down. "UNIX don't care..." Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -Original Message- > From: Dan Buettner [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 18, 2006 2:16 PM > To: George-Cristian Bîrzan > Cc: mysql@lists.mysql.com > Subject: Re: InnoDB messup > > Can you just put the files back where they were originally? > Ordinarily that would be in the path set up in mysql - see SHOW > VARIABLES LIKE "datadir" > > You do need to have your ibdata* files and ib_logfile* files all in > there, assuming you weren't using the file-per-table setup (if you > were then I am not sure; haven't tried that one yet myself). > > I'd shut down the server process, move everything into place, > double-check permissions on the files, and then start the mysqld > server process back up. > > Dan > > > On 10/18/06, George-Cristian Bîrzan > <[EMAIL PROTECTED]> wrote: > > Hello! I'm having some troubles fixing an InnoDB messup, > maybe somebody will > > be able to help me with at least knowing if it's a lost cause... > > > > A colleague of mine moved the ib* files around, with MySQL > (5.0.15) still > > running, which didn't really do it justice... The problem > might've been made > > worse by the fact that the server was out of space at the > time, which was what > > he was trying to fix. > > > > Now, when starting MySQL with innodb_force_recovery set to > 1, I get the errors > > in http://hephaestus.rdsct.ro/~gcbirzan/mysql.log. > > > > So, bottom line, lacking any relevant backups, is there a > way to fix this? > > (Well, I have the .MYD and .MYI from back when the table > was MyISAM, but I > > lack the .frm, and using an older one makes MySQL segfault...) > > > > -- > > George-Cristian Bîrzan > > > > -- > > 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: Simple JOIN on three tables
On Wed, October 18, 2006 13:21, spacemarc said: > ok, instead I use > (SELECT * FROM tab1 WHERE mydate between 'the-date1' and 'the-date2' ) > UNION > (SELECT * FROM tab2 WHERE mydate between 'the-date1' and 'the-date2' ) > etc > > But if I wanted to use a join I can make it however or not? > > > > -- > http://www.spacemarc.it > A JOIN implies that the tables are related, that is, a column (or columns) in table a has the same value as a column (or columns) in table b. Thats what they mean when they call it a RELATIONAL database. If the tables don't have such a relationship you are asking for the UNION of the separate information from table a, where it meets its WHERE conditions, and the information from table b where it meets its WHERE conditions. The fact that both where conditions are the same doesn't matter. Hope this helps. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple JOIN on three tables
>But if I wanted to use a join I can make it however or not? You can join on any row(s) you like. What are you trying to acccomplish? PB spacemarc wrote: ok, instead I use (SELECT * FROM tab1 WHERE mydate between 'the-date1' and 'the-date2' ) UNION (SELECT * FROM tab2 WHERE mydate between 'the-date1' and 'the-date2' ) etc But if I wanted to use a join I can make it however or not? -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.5/483 - Release Date: 10/18/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple JOIN on three tables
ok, instead I use (SELECT * FROM tab1 WHERE mydate between 'the-date1' and 'the-date2' ) UNION (SELECT * FROM tab2 WHERE mydate between 'the-date1' and 'the-date2' ) etc But if I wanted to use a join I can make it however or not? -- http://www.spacemarc.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple JOIN on three tables
Cartesian Join Anyone ??? Rearrange Query as a UNION instead - Original Message - From: spacemarc <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Wednesday, October 18, 2006 3:46:04 PM GMT-0500 US/Eastern Subject: Simple JOIN on three tables Hi I have 3 tables with the same fields. I would want to find the data that they are comprised in the time interval: SELECT a.*, b.*, c.* FROM tab1 a, tab2 b, tab3 c WHERE a.date between '-MM-DD' and '-MM-DD' OR b.date between '-MM-DD' and '-MM-DD' OR c.date between '-MM-DD' and '-MM-DD' ORDER BY a.date DESC But this query returns all the fields duplicated. Where it is mistaken? -- http://www.spacemarc.it -- 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: Simple JOIN on three tables
On Wed, October 18, 2006 12:46, spacemarc said: > Hi > I have 3 tables with the same fields. > > I would want to find the data that they are comprised in the time > interval: > > SELECT a.*, b.*, c.* > FROM tab1 a, tab2 b, tab3 c > WHERE a.date between '-MM-DD' and '-MM-DD' > OR b.date between '-MM-DD' and '-MM-DD' > OR c.date between '-MM-DD' and '-MM-DD' > ORDER BY a.date DESC > > But this query returns all the fields duplicated. > Where it is mistaken? > > -- > http://www.spacemarc.it Well that's what you asked it to do, if you look closely at your query. How are table a, b, and c related to each other? Do you want all the information from each of the tables (which are NOT related to each other) between the specified dates? If that is the case you are looking at a UNION rather than a strait JOIN. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Simple JOIN on three tables
Hi I have 3 tables with the same fields. I would want to find the data that they are comprised in the time interval: SELECT a.*, b.*, c.* FROM tab1 a, tab2 b, tab3 c WHERE a.date between '-MM-DD' and '-MM-DD' OR b.date between '-MM-DD' and '-MM-DD' OR c.date between '-MM-DD' and '-MM-DD' ORDER BY a.date DESC But this query returns all the fields duplicated. Where it is mistaken? -- http://www.spacemarc.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some trouble with a Join after upgrade from 3.23 to 5.0
This is due to a change in MySQL 5.0.12 that was done to align with SQL:2003. Here, we started to be more conservative regarding what could go into the ON clause. The whole thing is documented here: http://dev.mysql.com/doc/refman/5.0/en/join.html In your case, the JOIN would look something like this (Not tested, just straight from under the hairy stuf on the top of my head): FROM article_country ac, article a LEFT JOIN article_menu am ON a.id = am.article_id Or, to be more SQL'ish: FROM article_country ac JOIN article a LEFT JOIN article_menu am ON a.id = am.article_id Or, to be even more more SQL'ish (this one I tested): FROM article_country ac CROSS JOIN article a LEFT JOIN article_menu am ON a.id = am.article_id /Karlsson nocturnal wrote: Hi I moved a lot of databases from a 3.23 system to a new 5.0 system that was taking over because of hardware upgrades. I had no major problems until the last database. This query: SELECT a.id, a.nr, a.parent_id, a.designation, a.designation2, a.short_description, a.road_desc, ac.activestatus_id, ac.active_date, am.menu_id, am.active_status, a.grouparticle_type, ac.bestseller FROM article a, article_country ac LEFT JOIN article_menu am ON a.id = am.article_id AND am.active_status =1 WHERE a.parent_id = '' AND ac.country_code = 'SE' AND a.id = ac.article_id AND a.grouparticle_type <>2 ORDER BY a.designation LIMIT 0 , 30; Gives me this error: Unknown column 'a.id' in 'on clause' I'm no MySQL expert but i assumed that a.id was an alias for article.id so i checked if the column existed manually and sure enough it does exist and it is full of data identical to the database on the old 3.23 server. So now i'd like to know what needs to be updated in the application sending this query because there is obviously something incompatible between 3.23 and 5.0. I read the documentation on this link: http://dev.mysql.com/doc/refman/5.0/en/join.html and found the section describing changes made to MySQL 5.0.12. The problems is that i couldn't find any errors in the query when i read about the new JOIN syntax described. I would like some help with this if anyone has the time to just point out what is wrong with the above query so that i can make the changes in the rest of the application. I'm sure i'll see the light if someone just pushes me in the right direction on this problem. -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm <___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some trouble with a Join after upgrade from 3.23 to 5.0
>I read the documentation on this link: >http://dev.mysql.com/doc/refman/5.0/en/join.html >and found the section describing changes made to MySQL 5.0.12. >The problems is that i couldn't find any errors in the query when i >read about the new JOIN syntax described. The info you need is in that section: since 5.0.12 you must change comma joins to explciit joins (JOIN ON ...) because since that version MySQL complies ISO SQL standards on that point. PB - nocturnal wrote: Hi I moved a lot of databases from a 3.23 system to a new 5.0 system that was taking over because of hardware upgrades. I had no major problems until the last database. This query: SELECT a.id, a.nr, a.parent_id, a.designation, a.designation2, a.short_description, a.road_desc, ac.activestatus_id, ac.active_date, am.menu_id, am.active_status, a.grouparticle_type, ac.bestseller FROM article a, article_country ac LEFT JOIN article_menu am ON a.id = am.article_id AND am.active_status =1 WHERE a.parent_id = '' AND ac.country_code = 'SE' AND a.id = ac.article_id AND a.grouparticle_type <>2 ORDER BY a.designation LIMIT 0 , 30; Gives me this error: Unknown column 'a.id' in 'on clause' I'm no MySQL expert but i assumed that a.id was an alias for article.id so i checked if the column existed manually and sure enough it does exist and it is full of data identical to the database on the old 3.23 server. So now i'd like to know what needs to be updated in the application sending this query because there is obviously something incompatible between 3.23 and 5.0. I read the documentation on this link: http://dev.mysql.com/doc/refman/5.0/en/join.html and found the section describing changes made to MySQL 5.0.12. The problems is that i couldn't find any errors in the query when i read about the new JOIN syntax described. I would like some help with this if anyone has the time to just point out what is wrong with the above query so that i can make the changes in the rest of the application. I'm sure i'll see the light if someone just pushes me in the right direction on this problem. -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.5/483 - Release Date: 10/18/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bin-log with expire_logs_days
I haven't used the server variable you refer to, but instead have always used an external command piped in via cron - PURGE BINARY LOGS BEFORE and I just use a DATE_SUB function to subtract X days from today's date. http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html It's a pretty quick command to run, generally a fraction of a second. Since you have 132 files it might be a few seconds but I would not expect longer than that. I don't know whether MySQL willl go back and delete the old logs if you set that variable and restart - presumably it would, but not certain. Dan On 10/18/06, George Law <[EMAIL PROTECTED]> wrote: Hi All, I have a **high traffic** mysql 4.0.18-standard-log server running with bin-logging enabled. Right now, this must be using a default setting for expire_log_days. I do not see this anyway in "show variables" or "show status" $ echo "show variables" | sql |grep bin binlog_cache_size 32768 log_bin ON max_binlog_cache_size 4294967295 max_binlog_size 1073741824 # echo "show status" | sql |grep bin Com_show_binlog_events 0 Com_show_binlogs9 Right now, I have 132 bin-logs, each at 1 GB. the logs go back to 2/11/2006 If I were to add 'expire_logs_days 45' to my.cnf and restart mysql, is mysql going to attempt to purge the logs > 45 days old and if so... how long does it typically take. We cannot afford to restart if its going to take any significant amount of time for it to purge the logs and restart. thanks! George Law [EMAIL PROTECTED] MSN: [EMAIL PROTECTED] Phone: 864-678-3161 -- 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: Some trouble with a Join after upgrade from 3.23 to 5.0
I think MySQL is being a little more strict as to expressing LEFT JOINs Notice that 'a' and 'am' are NOT TOGETHER with respect to the LEFT JOIN ... ON syntax. Maybe MySQL 3.23 is lenient with this. I know MySQL 4 is not, so MySQL 5 should not either. Try declaring article_country first then article like this: SELECT a.id, a.nr, a.parent_id, a.designation, a.designation2, a.short_description, a.road_desc, ac.activestatus_id, ac.active_date, am.menu_id, am.active_status, a.grouparticle_type, ac.bestseller FROM article_country ac, article a LEFT JOIN article_menu am ON a.id = am.article_id AND am.active_status =1 WHERE a.parent_id = '' AND ac.country_code = 'SE' AND a.id = ac.article_id AND a.grouparticle_type <>2 ORDER BY a.designation LIMIT 0 , 30; Notice that the LEFT JOIN has 'a' on the left and 'am' on the right and the ON clause has 'a' and 'am' components only. Try it out !!! - Original Message - From: nocturnal <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Wednesday, October 18, 2006 10:31:13 AM GMT-0500 US/Eastern Subject: Some trouble with a Join after upgrade from 3.23 to 5.0 Hi I moved a lot of databases from a 3.23 system to a new 5.0 system that was taking over because of hardware upgrades. I had no major problems until the last database. This query: SELECT a.id, a.nr, a.parent_id, a.designation, a.designation2, a.short_description, a.road_desc, ac.activestatus_id, ac.active_date, am.menu_id, am.active_status, a.grouparticle_type, ac.bestseller FROM article a, article_country ac LEFT JOIN article_menu am ON a.id = am.article_id AND am.active_status =1 WHERE a.parent_id = '' AND ac.country_code = 'SE' AND a.id = ac.article_id AND a.grouparticle_type <>2 ORDER BY a.designation LIMIT 0 , 30; Gives me this error: Unknown column 'a.id' in 'on clause' I'm no MySQL expert but i assumed that a.id was an alias for article.id so i checked if the column existed manually and sure enough it does exist and it is full of data identical to the database on the old 3.23 server. So now i'd like to know what needs to be updated in the application sending this query because there is obviously something incompatible between 3.23 and 5.0. I read the documentation on this link: http://dev.mysql.com/doc/refman/5.0/en/join.html and found the section describing changes made to MySQL 5.0.12. The problems is that i couldn't find any errors in the query when i read about the new JOIN syntax described. I would like some help with this if anyone has the time to just point out what is wrong with the above query so that i can make the changes in the rest of the application. I'm sure i'll see the light if someone just pushes me in the right direction on this problem. -- Med vänliga hälsningar Stefan Midjich aka nocturnal [Swehack] http://swehack.se -- 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]
Some trouble with a Join after upgrade from 3.23 to 5.0
Hi I moved a lot of databases from a 3.23 system to a new 5.0 system that was taking over because of hardware upgrades. I had no major problems until the last database. This query: SELECT a.id, a.nr, a.parent_id, a.designation, a.designation2, a.short_description, a.road_desc, ac.activestatus_id, ac.active_date, am.menu_id, am.active_status, a.grouparticle_type, ac.bestseller FROM article a, article_country ac LEFT JOIN article_menu am ON a.id = am.article_id AND am.active_status =1 WHERE a.parent_id = '' AND ac.country_code = 'SE' AND a.id = ac.article_id AND a.grouparticle_type <>2 ORDER BY a.designation LIMIT 0 , 30; Gives me this error: Unknown column 'a.id' in 'on clause' I'm no MySQL expert but i assumed that a.id was an alias for article.id so i checked if the column existed manually and sure enough it does exist and it is full of data identical to the database on the old 3.23 server. So now i'd like to know what needs to be updated in the application sending this query because there is obviously something incompatible between 3.23 and 5.0. I read the documentation on this link: http://dev.mysql.com/doc/refman/5.0/en/join.html and found the section describing changes made to MySQL 5.0.12. The problems is that i couldn't find any errors in the query when i read about the new JOIN syntax described. I would like some help with this if anyone has the time to just point out what is wrong with the above query so that i can make the changes in the rest of the application. I'm sure i'll see the light if someone just pushes me in the right direction on this problem. -- Med vänliga hälsningar Stefan Midjich aka nocturnal [Swehack] http://swehack.se -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bin-log with expire_logs_days
Hi All, I have a **high traffic** mysql 4.0.18-standard-log server running with bin-logging enabled. Right now, this must be using a default setting for expire_log_days. I do not see this anyway in "show variables" or "show status" $ echo "show variables" | sql |grep bin binlog_cache_size 32768 log_bin ON max_binlog_cache_size 4294967295 max_binlog_size 1073741824 # echo "show status" | sql |grep bin Com_show_binlog_events 0 Com_show_binlogs9 Right now, I have 132 bin-logs, each at 1 GB. the logs go back to 2/11/2006 If I were to add 'expire_logs_days 45' to my.cnf and restart mysql, is mysql going to attempt to purge the logs > 45 days old and if so... how long does it typically take. We cannot afford to restart if its going to take any significant amount of time for it to purge the logs and restart. thanks! George Law [EMAIL PROTECTED] MSN: [EMAIL PROTECTED] Phone: 864-678-3161 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB messup
On Wednesday 18 October 2006 21:28, Jerry Schwartz wrote: > Dan, have you tried this? It's been a really long time since I pored over > the file system internals, and it was on HFS, but what happens when you > move a file to another file system? I would think the inode for the open, > original file would stay in place and the daemon would keep merrily using > it until it was shut down. I'm not exactly sure what he did. He may've restarted the server after moving stuff... > > > -Original Message- > > From: Dan Buettner [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, October 18, 2006 2:16 PM > > To: George-Cristian B�rzan > > Cc: mysql@lists.mysql.com > > Subject: Re: InnoDB messup > > > > Can you just put the files back where they were originally? > > Ordinarily that would be in the path set up in mysql - see SHOW > > VARIABLES LIKE "datadir" > > > > You do need to have your ibdata* files and ib_logfile* files all in > > there, assuming you weren't using the file-per-table setup (if you > > were then I am not sure; haven't tried that one yet myself). > > > > I'd shut down the server process, move everything into place, > > double-check permissions on the files, and then start the mysqld > > server process back up. Hm, I did that, that's what I get after doing it. -- George-Cristian Bîrzan Network Engineer ___ RCS & RDS Constanta Tel.: +40341.400.401 / +40341.400.402 Fax: +40341.400.450 http://www.rcs-rds.ro ___ Privileged/Confidential Information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such a case, you should destroy this message and kindly notify the sender by reply e-mail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Operation has been cancelled by the user
hi! In the application's log i have this message (when a query is execute): "Operation has been cancelled by the user" It´s a web application developed with ASP.NET. I try to solve it in many ways... but no success... any idea? thanks Pablo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB messup
Dan, have you tried this? It's been a really long time since I pored over the file system internals, and it was on HFS, but what happens when you move a file to another file system? I would think the inode for the open, original file would stay in place and the daemon would keep merrily using it until it was shut down. "UNIX don't care..." Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -Original Message- > From: Dan Buettner [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 18, 2006 2:16 PM > To: George-Cristian Bîrzan > Cc: mysql@lists.mysql.com > Subject: Re: InnoDB messup > > Can you just put the files back where they were originally? > Ordinarily that would be in the path set up in mysql - see SHOW > VARIABLES LIKE "datadir" > > You do need to have your ibdata* files and ib_logfile* files all in > there, assuming you weren't using the file-per-table setup (if you > were then I am not sure; haven't tried that one yet myself). > > I'd shut down the server process, move everything into place, > double-check permissions on the files, and then start the mysqld > server process back up. > > Dan > > > On 10/18/06, George-Cristian Bîrzan > <[EMAIL PROTECTED]> wrote: > > Hello! I'm having some troubles fixing an InnoDB messup, > maybe somebody will > > be able to help me with at least knowing if it's a lost cause... > > > > A colleague of mine moved the ib* files around, with MySQL > (5.0.15) still > > running, which didn't really do it justice... The problem > might've been made > > worse by the fact that the server was out of space at the > time, which was what > > he was trying to fix. > > > > Now, when starting MySQL with innodb_force_recovery set to > 1, I get the errors > > in http://hephaestus.rdsct.ro/~gcbirzan/mysql.log. > > > > So, bottom line, lacking any relevant backups, is there a > way to fix this? > > (Well, I have the .MYD and .MYI from back when the table > was MyISAM, but I > > lack the .frm, and using an older one makes MySQL segfault...) > > > > -- > > George-Cristian Bîrzan > > > > -- > > 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: InnoDB messup
Can you just put the files back where they were originally? Ordinarily that would be in the path set up in mysql - see SHOW VARIABLES LIKE "datadir" You do need to have your ibdata* files and ib_logfile* files all in there, assuming you weren't using the file-per-table setup (if you were then I am not sure; haven't tried that one yet myself). I'd shut down the server process, move everything into place, double-check permissions on the files, and then start the mysqld server process back up. Dan On 10/18/06, George-Cristian Bîrzan <[EMAIL PROTECTED]> wrote: Hello! I'm having some troubles fixing an InnoDB messup, maybe somebody will be able to help me with at least knowing if it's a lost cause... A colleague of mine moved the ib* files around, with MySQL (5.0.15) still running, which didn't really do it justice... The problem might've been made worse by the fact that the server was out of space at the time, which was what he was trying to fix. Now, when starting MySQL with innodb_force_recovery set to 1, I get the errors in http://hephaestus.rdsct.ro/~gcbirzan/mysql.log. So, bottom line, lacking any relevant backups, is there a way to fix this? (Well, I have the .MYD and .MYI from back when the table was MyISAM, but I lack the .frm, and using an older one makes MySQL segfault...) -- George-Cristian Bîrzan -- 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 messup
Hello! I'm having some troubles fixing an InnoDB messup, maybe somebody will be able to help me with at least knowing if it's a lost cause... A colleague of mine moved the ib* files around, with MySQL (5.0.15) still running, which didn't really do it justice... The problem might've been made worse by the fact that the server was out of space at the time, which was what he was trying to fix. Now, when starting MySQL with innodb_force_recovery set to 1, I get the errors in http://hephaestus.rdsct.ro/~gcbirzan/mysql.log. So, bottom line, lacking any relevant backups, is there a way to fix this? (Well, I have the .MYD and .MYI from back when the table was MyISAM, but I lack the .frm, and using an older one makes MySQL segfault...) -- George-Cristian Bîrzan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding the 'page' number of a record, or its position in resultset
Jerry Schwartz the-infoshop.com> writes: > I'm still confused, are you saying that you want to know what page a team > would be on if you did a listing? > > Regards, Yes, given a query (e.g. "SELECT ... FROM teams ORDER BY score DESC, errors") which we usually add a LIMIT clause to for pagination, and a team (either given by a primary ID, or maybe in other cases by a subquery returning one result from the result set, depending on the query), we want to find out the position that the team appears in the result. The aim being to do something like listing the 5 higher and 5 lower teams, or to provide a link to the page which contains this team. My question originally was to find the page number, but instinctively I'd do that by finding the overall position and doing some arithmetic - I don't think it would be reasonable to assume MySQL would understand my pagination Thanks for sticking with this, Gareth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding the 'page' number of a record, or its position in resultset
I'm still confused, are you saying that you want to know what page a team would be on if you did a listing? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -Original Message- > From: news [mailto:[EMAIL PROTECTED] On Behalf Of Gareth Adams > Sent: Wednesday, October 18, 2006 11:47 AM > To: mysql@lists.mysql.com > Subject: Re: Finding the 'page' number of a record, or its > position in resultset > > Jerry Schwartz the-infoshop.com> writes: > > > Can't you just keep track of which page you are on, and > multiply by 20 > > (except for the last page)? I must be missing something. > > > > Regards, > > Hi Jerry, > > The problem is that the page isn't necessarily known at the > time we need to find > out its position. > > As a simple example, finding a team's position in a large > league, based on > "ORDER BY score DESC, errors ASC". The team may not have been > accessed from the > paginated league-table list. > > Maybe one solution would be to run the full query but only > select the primary > key, and then do searching in the application, but this seems > a little messy, > and still has to transfer a long dataset out to the > application only for most of > it to be discarded. Since MySQL has to collate a query, it > seems strange that > there's no kind of "SELECT ROW_NUMBER(), ..." which could > then be used in a > subquery or something (maybe not very efficient, but still > more efficient than > passing the entire resultset to the application where the > searching could easily > be slower depending on the language) > > Just my $0.02 > > Gareth > > > -- > 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: Finding the 'page' number of a record, or its position in resultset
Jerry Schwartz the-infoshop.com> writes: > Can't you just keep track of which page you are on, and multiply by 20 > (except for the last page)? I must be missing something. > > Regards, Hi Jerry, The problem is that the page isn't necessarily known at the time we need to find out its position. As a simple example, finding a team's position in a large league, based on "ORDER BY score DESC, errors ASC". The team may not have been accessed from the paginated league-table list. Maybe one solution would be to run the full query but only select the primary key, and then do searching in the application, but this seems a little messy, and still has to transfer a long dataset out to the application only for most of it to be discarded. Since MySQL has to collate a query, it seems strange that there's no kind of "SELECT ROW_NUMBER(), ..." which could then be used in a subquery or something (maybe not very efficient, but still more efficient than passing the entire resultset to the application where the searching could easily be slower depending on the language) Just my $0.02 Gareth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding the 'page' number of a record, or its position in resultset
Can't you just keep track of which page you are on, and multiply by 20 (except for the last page)? I must be missing something. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -Original Message- > From: news [mailto:[EMAIL PROTECTED] On Behalf Of Gareth Adams > Sent: Wednesday, October 18, 2006 11:25 AM > To: mysql@lists.mysql.com > Subject: Finding the 'page' number of a record, or its > position in resultset > > Hi, > > We have an application which queries our database in 'pages', > i.e. uses the same > query repeatedly, with a different "LIMIT x, 20" to display > blocks of 20 results. > > I'm wondering if there's an easy way (or any way) to find out > where in the > resultset a particular record lies. Obviously the query we're > paginating could > involve an ORDER clause, so this isn't any information that > can be stored in the > table. > > I realise that a row in a resultset might not correspond to a > record, so I > figure this would involve some kind of subquery, I just don't > know what it would > involve. > > I realise that this processing could be done in the > application, but that would > involve loading the entire resultset into memory, which would > pretty much ruin > the point of the pagination. > > Hopefully it won't be too painful to work this out > > Gareth > > > -- > 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]
Finding the 'page' number of a record, or its position in resultset
Hi, We have an application which queries our database in 'pages', i.e. uses the same query repeatedly, with a different "LIMIT x, 20" to display blocks of 20 results. I'm wondering if there's an easy way (or any way) to find out where in the resultset a particular record lies. Obviously the query we're paginating could involve an ORDER clause, so this isn't any information that can be stored in the table. I realise that a row in a resultset might not correspond to a record, so I figure this would involve some kind of subquery, I just don't know what it would involve. I realise that this processing could be done in the application, but that would involve loading the entire resultset into memory, which would pretty much ruin the point of the pagination. Hopefully it won't be too painful to work this out Gareth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How many databases does MySQL 5 support?
Dan, Cheers for doing the translating, I'm one of those beardy types they keep locked in a dark room writing search engines so my English isn't spectacular ;^) As Rolando points out your file system may place a limit on the number of files or directories, but to my knowledge XFS has no such limit so your still left with 8 Exabytes (approximately one quintillion bytes) to store your data. By the way if you used Solaris 10 (he says looking under his desk) you could use ZFS which would give you 16 exabytes (and a 2^48 file limit), which is er, many. Back to the question at hand tho', the schema_information table is required to store information about each column (which looks like the most populous table in there), assuming a vaguely realistic 3 tables with 5 columns per database that would give you 160 million databases before you reached the 2.4 billion row limit of that table? Interestingly I can't file a file limit mentioned for XFS, if it's 2^24 that's only 16.7 million. How about we leave it at "lots". What Philip is saying is that you might run into problems with one of the tables used to keep track of tables and databases, before you run into problems with any hard coded limit of MySQL itself. Regards, Philip (I better get back to real work) Mather ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Query question
Dan, your suggestion is *exactly* what I needed! Furthermore, because of the use of the subquery, there is no need to join to table to itself, so the query may be simplified to: mysql> SELECT distinct loc1.imageId -> FROM locBridgeImageLocLevel5 as loc1 -> WHERE loc1.locLevel5Id = 2356 -> ANDloc1.imageid NOT IN ->(SELECT imageid FROM locBridgeImageLocLevel5 WHERE locLevel5Id = 13128); +-+ | imageId | +-+ | 2 | | 280 | | 4 | | 5 | | 6 | | 7 | ... | 255 | | 258 | | 259 | | 260 | | 261 | +-+ Thank you very much for sharing your expertise. Erick At 3:56 PM -0500 10/17/06, Dan Buettner wrote: I see what's happening, Erick. It's matching all the rows in loc1 and loc2 with the same image id. It *is* excluding 13128, but image id 1 is still appearing because of the rows where they match *besides* 13128. For example, 18302 and actually also 2356 since you're joining a table on itself. Sounds like what you want is to exclude all the image ids for locLevel5Id = 13128 ? Rewrite like so, assuming you have subqueries: SELECT distinct loc1.imageId FROM locBridgeImageLocLevel5 as loc1 INNER JOIN locBridgeImageLocLevel5 as loc2 USING (imageId) WHERE loc1.locLevel5Id = 2356 AND loc2.imageid NOT IN (SELECT imageid from locBridgeImageLocLevel5 WHERE locLevel5Id = 13128); I think I'm understanding your goal!! Dan On 10/17/06, Erick Carballo <[EMAIL PROTECTED]> wrote: Dan, thanks for your prompt response. You are correct: I mistyped. However, if I ran the query as you suggest, I obtain the same results: mysql> SELECT distinct loc1.imageId -> FROM locBridgeImageLocLevel5 as loc1 -> INNER JOIN -> locBridgeImageLocLevel5 as loc2 USING (imageId) -> WHERE (loc1.locLevel5Id = 2356 AND loc2.locLevel5Id <> 13128); +-+ | imageId | +-+ | 1 | | 2 | | 280 | | 4 | | 5 | | 6 | | 7 | ... | 257 | | 258 | | 259 | | 260 | | 261 | +-+ 251 rows in set (0.04 sec) It stills returns imageId 1, eventhough there is a record (locBridgeImageLocLevel5Id 541) in which imagedId = 1 and locLevel5Id = 13128: mysql> select * from locBridgeImageLocLevel5 where imageId = 1; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 1 | 1 |2356 | | 2 | 1 | 18302 | | 541 | 1 | 13128 | +---+-+-+ Erick At 2:47 PM -0500 10/17/06, Dan Buettner wrote: Erick, maybe I'm missing something or you mistyped, but you appear to be saying this: you want 2356 and not 13128 but your last SQL query is excluding only 18302. 13128 is not mentioned in the query. Try re-running the query with 13128 instead of 18302 ? Dan On 10/17/06, Erick Carballo <[EMAIL PROTECTED]> wrote: Hello, I would really appreciate your help regarding a query. First, some background: The query is being executed on the following table: mysql> describe locBridgeImageLocLevel5; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | locBridgeImageLocLevel5Id | int(10) unsigned | NO | PRI | NULL | auto_increment | | imageId | int(10) unsigned | NO | | || | locLevel5Id | int(10) unsigned | NO | | || +---+--+--+-+-++ As you may see, this table gathers data from two separate tables >>(image and and locLevel5). The locLevel5 table contains data from a US official list of places which--unfortunately--mixes location names of counties, cities, and certain geographical places (such as lakes, national parks, mountains, etc.). This creates a many-to-many relationship between imageId and locLevel5Id, as the following queries show: mysql> select * from locBridgeImageLocLevel5 where imageId = 1; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 1 | 1 |2356 | | 2 | 1 | 18302 | | 541 | 1 | 13128 | +---+-+-+ mysql> select * from locBridgeImageLocLevel5 where imageId = 2; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 3 | 2 |
re: excel and Mysql?!
Always being the last to input, there are lots of other database tools out there that let you do this. One in particular is DBTools Professional (which is what I use). You can ADO IMPORT Excel, MS-Access, and others like FoxPro and PostgreSQL. Another is Database Workbench, which one of the guys on this list works on. However, if free is the way to go, then MyODBC and the format below (which I will start using now =) ) are the ways to do it. My 0.02... J.R. "cheap is good, free is better" From: "Jerry Schwartz" <[EMAIL PROTECTED]> Sent: Wednesday, October 18, 2006 10:17 AM To: "'Roberto William Aranda-W Roman'" <[EMAIL PROTECTED]>, "'MySQL List'" Subject: RE: excel and Mysql?! As usual, Dan's suggestion is better than mine, especially for large amounts of data (I usually work with perhaps 100 rows, at most). When you save a spreadsheet as a text file, the columns will be delimited by tabs by default. Also, if you go with my suggestion you should probably surround any text values with an extra pair of single quotes: "... SET col1 = '", 'Sheet1'!A1, "', ...") Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -Original Message- > From: Roberto William Aranda-W Roman [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 18, 2006 8:37 AM > To: MySQL List > Subject: excel and Mysql?! > > > hello . > > anybody knows how to export from excel to mysql in order to > create or fill a database table?? > > tanks for your help and patience > > cheers > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: How many databases does MySQL 5 support?
That does make sense, John. What Philip is saying is that you might run into problems with one of the tables used to keep track of tables and databases, before you run into problems with any hard coded limit of MySQL itself. The OS and the hardware will impose some (rather generous) restrictions. Memory, filesystem space, number of open table file limits, number of subdirectory limits, etc. MySQL might start to suffer from some performance problems if you add tens of thousands of entries to the information schema tables - hard to say for sure. Maybe a developer can chime in with more specifics about theoretical limits, or someone with practical exprience using LOTS of databases. My own installations have never had more than about 40 separate databases, which has obviously never approached any limit! Dan On 10/18/06, John M. Brown <[EMAIL PROTECTED]> wrote: Thanks for the info, but my question is how many databases, not so much how many rows per table or how big the database can be... I mean, how many "create database ABC" can I do before MySQL says "sorry, you can't have more than X databases". Say I create 1000 empty MySQL databases (meaning no tables, just the schema)... would that work? what about 5000? ... make sense? On Wed, 18 Oct 2006 14:45:18 +0100, Philip Mather <[EMAIL PROTECTED]> wrote: > John, >> How many databases does a single instance of MySQL Server 5.x support? >> > I suspect you'll get a bit of a , with a 64bit machine there's > a limit of 4.2 billion rows per table and with an XFS file system 8EB > per table, there's a join limit specified somewhere but I don't think > there's an explicit limit coded in anywhere? Assuming you have no other > hardware constraints beyond a 64bit processor (and that's an enormous > assumption) I'd imagine you'd explode one of the tables in the > information_schema database maybe. > > Having said all that I suspect that the effort to build a 128bit > processor with working generic *n!x kernel, ANSI C compiler and file > system (minus any 64bit constraints) would dwarf the work then needed to > compile MySQL onto it. > > So for the time being I'd suggest that it's safe to assume you'll find > your hardware's limits first. > > Regards, > Phil -- 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: excel and Mysql?!
As usual, Dan's suggestion is better than mine, especially for large amounts of data (I usually work with perhaps 100 rows, at most). When you save a spreadsheet as a text file, the columns will be delimited by tabs by default. Also, if you go with my suggestion you should probably surround any text values with an extra pair of single quotes: "... SET col1 = '", 'Sheet1'!A1, "', ...") Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -Original Message- > From: Roberto William Aranda-W Roman [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 18, 2006 8:37 AM > To: MySQL List > Subject: excel and Mysql?! > > > hello . > > anybody knows how to export from excel to mysql in order to > create or fill a database table?? > > tanks for your help and patience > > cheers > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How many databases does MySQL 5 support?
Each Schema is a Subfolder under the datadir of the MySQL Instance However many folders an operating system permits is how many schemas can be created - Original Message - From: Dominik Klein <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Wednesday, October 18, 2006 10:05:17 AM GMT-0500 US/Eastern Subject: Re: How many databases does MySQL 5 support? John M.Brown schrieb: > Thanks for the info, but my question is how many databases, not so much how > many rows per table or how big the database can be... I mean, how many > "create database ABC" can I do before MySQL says "sorry, you > can't have more than X databases". > > Say I create 1000 empty MySQL databases (meaning no tables, just the > schema)... would that work? what about 5000? ... make sense? I just test-created 10.000 dbs on a cheap celeron 2400 ide hdd testserver with 256 megs of ram and it was no problem at all. -- 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: excel and Mysql?!
I do it all of the time, and once you get the hang of it this is quite easy.
The simple-minded way is to generate one SQL statement per row of the
original spreadsheet (assuming that each row of the spreadsheet corresponds
to a row in the data base table).
1. Create a new worksheet, if needed. Position yourself at cell A1.
2. Using Excel functions, build an entry like
=CONCATENATE("INSERT INTO mytable SET col1 = ",'Sheet1'!A1,", col2 =
",'Sheet1'!A2, ...)
You can use Excel's click to build the cell links to the other
spreadsheet, you don't have to type them by hand.
3. Copy the formula down the range you need.
4. Save the worksheet containing the MySQL commands as a text file.
5. From the mysql command line interface, simply USE the right data
base and then source the text file you created. Alternatively, you can
insert the USE statement into your spreadsheet (adjusting the row numbers
accordingly) or directly into your text file, and redirect the input file
for mysql on the command line.
Once you get the hang of it, this is all simpler than it sounds.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
> -Original Message-
> From: Roberto William Aranda-W Roman [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, October 18, 2006 8:37 AM
> To: MySQL List
> Subject: excel and Mysql?!
>
>
> hello .
>
> anybody knows how to export from excel to mysql in order to
> create or fill a database table??
>
> tanks for your help and patience
>
> cheers
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How many databases does MySQL 5 support?
John M.Brown schrieb: Thanks for the info, but my question is how many databases, not so much how many rows per table or how big the database can be... I mean, how many "create database ABC" can I do before MySQL says "sorry, you can't have more than X databases". Say I create 1000 empty MySQL databases (meaning no tables, just the schema)... would that work? what about 5000? ... make sense? I just test-created 10.000 dbs on a cheap celeron 2400 ide hdd testserver with 256 megs of ram and it was no problem at all. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 'Not a valid MySQL result resource' error
> -Original Message- > From: List [mailto:[EMAIL PROTECTED] > Sent: Tuesday, October 17, 2006 9:30 PM > To: mysql@lists.mysql.com > Subject: 'Not a valid MySQL result resource' error > > Hello, > > I'm running f.a.m.p, f =freebsd 4.7 and mysql is 3.23.52. > > Anyway, I inherited a website from someone else's server(I don't know > what they we're running) but the admin section of the website generates > this error iin the apache error log when trying to login( on the screen > just takes you back to login saying invalid: > > > [Tue Oct 17 19:10:08 2006] [error] PHP Warning: mysql_query(): supplied > argument is not a valid MySQL-Link resource in > /usr/local/apache/website/incoming/_includes/_page-specialeventsnav.php > on line 16 This is the first error. So look for the MySQL-Link resource suppiled to mysql_query(). > $r = mysql_query($x,$db); $x is a string, so that's not it. $db should be the resource, so it's the one throwing the error. So you either don't have a connection, or that's the wrong variable for your connection. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How many databases does MySQL 5 support?
Thanks for the info, but my question is how many databases, not so much how many rows per table or how big the database can be... I mean, how many "create database ABC" can I do before MySQL says "sorry, you can't have more than X databases". Say I create 1000 empty MySQL databases (meaning no tables, just the schema)... would that work? what about 5000? ... make sense? On Wed, 18 Oct 2006 14:45:18 +0100, Philip Mather <[EMAIL PROTECTED]> wrote: > John, >> How many databases does a single instance of MySQL Server 5.x support? >> > I suspect you'll get a bit of a , with a 64bit machine there's > a limit of 4.2 billion rows per table and with an XFS file system 8EB > per table, there's a join limit specified somewhere but I don't think > there's an explicit limit coded in anywhere? Assuming you have no other > hardware constraints beyond a 64bit processor (and that's an enormous > assumption) I'd imagine you'd explode one of the tables in the > information_schema database maybe. > > Having said all that I suspect that the effort to build a 128bit > processor with working generic *n!x kernel, ANSI C compiler and file > system (minus any 64bit constraints) would dwarf the work then needed to > compile MySQL onto it. > > So for the time being I'd suggest that it's safe to assume you'll find > your hardware's limits first. > > Regards, > Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How many databases does MySQL 5 support?
John, How many databases does a single instance of MySQL Server 5.x support? I suspect you'll get a bit of a , with a 64bit machine there's a limit of 4.2 billion rows per table and with an XFS file system 8EB per table, there's a join limit specified somewhere but I don't think there's an explicit limit coded in anywhere? Assuming you have no other hardware constraints beyond a 64bit processor (and that's an enormous assumption) I'd imagine you'd explode one of the tables in the information_schema database maybe. Having said all that I suspect that the effort to build a 128bit processor with working generic *n!x kernel, ANSI C compiler and file system (minus any 64bit constraints) would dwarf the work then needed to compile MySQL onto it. So for the time being I'd suggest that it's safe to assume you'll find your hardware's limits first. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 'Not a valid MySQL result resource' error
I don't see any code to attach to the MySQL server and select the data base
you are using. This code might be in a part of the script you didn't show
us, but somewhere there should be something like
$db = mysql_connect(...);
Better yet,
$db = mysql_connect(...) or die("Unable to open data base!\n");
This might be in the including script, if any, since the error message shows
a suggestively-named file name.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
> -Original Message-
> From: List [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, October 17, 2006 9:30 PM
> To: mysql@lists.mysql.com
> Subject: 'Not a valid MySQL result resource' error
>
> Hello,
>
> I'm running f.a.m.p, f =freebsd 4.7 and mysql is 3.23.52.
>
> Anyway, I inherited a website from someone else's server(I don't know
> what they we're running) but the admin section of the website
> generates
> this error iin the apache error log when trying to login( on
> the screen
> just takes you back to login saying invalid:
>
>
> [Tue Oct 17 19:10:08 2006] [error] PHP Warning:
> mysql_query(): supplied
> argument is not a valid MySQL-Link resource in
> /usr/local/apache/website/incoming/_includes/_page-specialeven
> tsnav.php
> on line 16
> [Tue Oct 17 19:10:08 2006] [error] PHP Warning: mysql_fetch_array():
> supplied argument is not a valid MySQL result resource in
> /usr/local/apache/website/incoming/_includes/_page-specialeven
> tsnav.php
> on line 17
> [Tue Oct 17 19:10:08 2006] [error] PHP Warning: mysql_num_rows():
> supplied argument is not a valid MySQL result resource in
> /usr/local/apache/website/incoming/_includes/_page-specialeven
> tsnav.php
> on line 20
>
>
>
> code in question:
>
>
>
>
>
>
>
> width='180' border='0'>
> Special
> Events
>
> page_contents,page_sections WHERE page_contents.pagename =
> page_sections.pagename AND page_contents.display != 'N' AND
> page_sections.publicurl = '/specialevents.php' AND begdate <=
> Now() AND
> enddate > Now() ORDER BY rand() LIMIT 6";
> $r = mysql_query($x,$db);
> while ($re = mysql_fetch_array($r)) {
> print " href=\"javascript:focus('http://www.website.com/viewevents.php
> ?rid=$re[id]')\">$re[title]\n";
>
> }
> $rcount = mysql_num_rows($r);
> if (($rcount == 6)) {
> print " href='http://www.website.com/specialevents.php'>continued... a>\n";
>
> }
> ?>
>
>
>
>
>
> Any and all help is much appreciated, thanks.
>
> --
> Jason
>
>
> --
> 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]
Incemental backup
Hi, I just bough innodb hotbackup, I need to do incremental backup as I have a database which is several GB. Is it ok that I enable the binary log and using innodb hotbackup to create a full backup and use the binary log for incremental ?, if yes how do I force mysqld to write a new log after the backup is complete ? -- Taymour A El Erian System Division Manager RHCE, LPIC, CCNA, MCSE, CNA TE Data E-mail: [EMAIL PROTECTED] Web: www.tedata.net Tel:+(202)-3320700 Fax:+(202)-3320800 Ext:1101 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: RE: How to rewrite query
I'm not certain how MySQL handles the specific case where some columns in a record covered by a multi-column index are updated; it may update the whole index entry, or just part of it, not sure. In any case, yes, there is some overhead associated with having an index on columns that get updated. In my experience, the overhead is not bad (how's that for quantifying it??) even with multiple indices on a table with 100,000 to 150,000 records. Your table is smaller, I think you said around 30,000 records. If you're concerned about it, you could certainly try an index that covers just Is_id and stype, in either order; it would be an improvement over where you are now. That would avoid index update overhead if those fields never change. It's also frankly something that is pretty easy to experiment with, as dropping and creating indices on a 30K record table should be reasonably quick (around a few seconds?). Also, don't forget the datatype mismatch in the one column, that can have an impact too. Dan On 10/18/06, Mindaugas <[EMAIL PROTECTED]> wrote: >I agree that individual fields have relatively few possible values - > hopefully, when those are combined in a multi-column index, he will > have a greater number of unique combinations, gaining more out of the > index. That's why I suggested putting stype and Is_id as the first > two fields in the index (though I guess I did not mention that!). > > stype had 6 values, Is_id had 5, so he may have up to about 30 > combinations as the first two fields, which should be enough to help a > lot. ls_id is evenly distributed but stype is not. But thought is interesting. And one question. I mentioned that update:select ratio is 3:1. There are no deletes and inserts. Also update does not touch ls_id and stype fields. Is there index updating overhead then? Mindaugas -- 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: excel and Mysql?!
If you have the Max Binaries of MySQL, do the following: 1) Export Excel Data to a CSV file (mydata.csv) 2) Goto MySQL and create table, without indexes to accept the data (CREATE TABLE myImportCSV) 3) Change the table layout to accept CSV i.e., ALTER TABLE myImport ENGINE = CSV; Please note that CSV tables in MySQL do not support indexes. At this point myImport.csv in the datadir of MySQL is a zero-length file. 4) Goto datadir of MySQL installation and put the contents of mydata.csv into myImport.csv. 5) Run this SQL statement: ALTER TABLE myImport ENGINE = MyISAM; 6) Create any necessary indexes for the MyISAM table myImport. If you do not have Max Binaries, I hope you have Microsoft Access. Then, you can try the following: 1) Download MyODBC onto your PC and install it. 2) Goto Administrative Tasks and goto 32-bit ODBC in Control Panel 3) Set up ODBC entry to point to your MySQL database of choice 4) Goto Microsoft Access and create a new table. 5) Create a link table entry to the Excel spreadsheet 6) Create a link table entry to MySQL table Make sure the MySQL userid and password has full rights to insert data Make sure the MySQL table has a primary key. If it does not have one, make one. Microsoft Access requires all outside databases (i.e., MySQL, Oracle, SQL Server, etc) to have a primary key. 7) Create an Append Query to the MySQL table from the Excel Spreadsheet. If you do not have Microsoft Access, here is a last resort: 1) Export Spreadsheet Pipe Delimited to myImport.txt 2) Create a table to load on MySQL (with indexes if necessary) 3) Use LOAD DATA INFILE if myImport.txt is to be on the server or LOAD DATA LOCAL INFILE if myImport.txt is loaded from your PC Note: If using LOAD DATA LOCAL INFILE, make sure you use forward slashes i.e., C:/data/myImport.txt I hope this helps !!! - Original Message - From: Roberto William Aranda-W Roman <[EMAIL PROTECTED]> To: MySQL List Sent: Wednesday, October 18, 2006 8:36:41 AM GMT-0500 US/Eastern Subject: excel and Mysql?! hello . anybody knows how to export from excel to mysql in order to create or fill a database table?? tanks for your help and patience cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: excel and Mysql?!
Sure, it's usually fairly straightforward to transfer data. You can export to a CSV or other delimited file, then use MySQL's LOAD DATA INFILE command to read it in. See http://dev.mysql.com/doc/refman/5.0/en/load-data.html I haven't ever tried creating tables from something in Excel automagically; I have always created the tables by hand. Dan On 10/18/06, Roberto William Aranda-W Roman <[EMAIL PROTECTED]> wrote: hello . anybody knows how to export from excel to mysql in order to create or fill a database table?? tanks for your help and patience cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
excel and Mysql?!
hello . anybody knows how to export from excel to mysql in order to create or fill a database table?? tanks for your help and patience cheers
How many databases does MySQL 5 support?
I've looked as many places as I can think of and can't find a direct answer to my question: How many databases does a single instance of MySQL Server 5.x support? Is this because there is no logical limit? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
why length 3 key in utf8 use 11 bytes?
create index ub_city_key on ub (city(3)); id: 1 select_type: SIMPLE table: ub type: ref possible_keys: ub_id_key,ub_city_key key: ub_city_key key_len: 11 ref: const rows: 4340 Extra: Using where 1 row in set (0.00 sec) Why the key_len is 11 not 9? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'Not a valid MySQL result resource' error
Philip Mather wrote:
List,
Without getting into the specific problem the general debug path I'd
follow would be: -
1. Make sure you are actually connected to the database, you say it's
the admin area? Does the admin area login with different details? try
eching the result of a "select NOW()" right at the start of the admin area.
Yes and how do I do what you mention(I'm technically sound but a sql
novice)?
2. Make sure the SQL works, echo the $x just before it gets sent to the
database and then past it into the MySQL command line.
Again, I don't know how to do this? I assume at the sql command line.
3. Make sure all the variables used in the query (not relevant to you)
and database connection ($db) are set and that you're using the right
result set ($r) in this case.
From the error I'd say you've got either 1 or 2, can't be much more
specific with the information I'm afraid. Are "begdate" and "enddate"
ambiguous?
Regards,
Phil
Thanks
Hello,
I'm running f.a.m.p, f =freebsd 4.7 and mysql is 3.23.52.
Anyway, I inherited a website from someone else's server(I don't know
what they we're running) but the admin section of the website
generates this error iin the apache error log when trying to login( on
the screen just takes you back to login saying invalid:
[Tue Oct 17 19:10:08 2006] [error] PHP Warning: mysql_query():
supplied argument is not a valid MySQL-Link resource in
/usr/local/apache/website/incoming/_includes/_page-specialeventsnav.php
on line 16
[Tue Oct 17 19:10:08 2006] [error] PHP Warning: mysql_fetch_array():
supplied argument is not a valid MySQL result resource in
/usr/local/apache/website/incoming/_includes/_page-specialeventsnav.php
on line 17
[Tue Oct 17 19:10:08 2006] [error] PHP Warning: mysql_num_rows():
supplied argument is not a valid MySQL result resource in
/usr/local/apache/website/incoming/_includes/_page-specialeventsnav.php
on line 20
code in question:
width='180' border='0'>
Special
Events
page_contents,page_sections WHERE page_contents.pagename =
page_sections.pagename AND page_contents.display != 'N' AND
page_sections.publicurl = '/specialevents.php' AND begdate <= Now()
AND enddate > Now() ORDER BY rand() LIMIT 6";
$r = mysql_query($x,$db);
while ($re = mysql_fetch_array($r)) {
print "href=\"javascript:focus('http://www.website.com/viewevents.php?rid=$re[id]')\">$re[title]\n";
}
$rcount = mysql_num_rows($r);
if (($rcount == 6)) {
print "href='http://www.website.com/specialevents.php'>continued...\n";
}
?>
Any and all help is much appreciated, thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE: How to rewrite query
I agree that individual fields have relatively few possible values - hopefully, when those are combined in a multi-column index, he will have a greater number of unique combinations, gaining more out of the index. That's why I suggested putting stype and Is_id as the first two fields in the index (though I guess I did not mention that!). stype had 6 values, Is_id had 5, so he may have up to about 30 combinations as the first two fields, which should be enough to help a lot. ls_id is evenly distributed but stype is not. But thought is interesting. And one question. I mentioned that update:select ratio is 3:1. There are no deletes and inserts. Also update does not touch ls_id and stype fields. Is there index updating overhead then? Mindaugas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'Not a valid MySQL result resource' error
List,
Without getting into the specific problem the general debug path I'd
follow would be: -
1. Make sure you are actually connected to the database, you say it's
the admin area? Does the admin area login with different details? try
eching the result of a "select NOW()" right at the start of the admin area.
2. Make sure the SQL works, echo the $x just before it gets sent to the
database and then past it into the MySQL command line.
3. Make sure all the variables used in the query (not relevant to you)
and database connection ($db) are set and that you're using the right
result set ($r) in this case.
From the error I'd say you've got either 1 or 2, can't be much more
specific with the information I'm afraid. Are "begdate" and "enddate"
ambiguous?
Regards,
Phil
Hello,
I'm running f.a.m.p, f =freebsd 4.7 and mysql is 3.23.52.
Anyway, I inherited a website from someone else's server(I don't know
what they we're running) but the admin section of the website
generates this error iin the apache error log when trying to login( on
the screen just takes you back to login saying invalid:
[Tue Oct 17 19:10:08 2006] [error] PHP Warning: mysql_query():
supplied argument is not a valid MySQL-Link resource in
/usr/local/apache/website/incoming/_includes/_page-specialeventsnav.php
on line 16
[Tue Oct 17 19:10:08 2006] [error] PHP Warning: mysql_fetch_array():
supplied argument is not a valid MySQL result resource in
/usr/local/apache/website/incoming/_includes/_page-specialeventsnav.php
on line 17
[Tue Oct 17 19:10:08 2006] [error] PHP Warning: mysql_num_rows():
supplied argument is not a valid MySQL result resource in
/usr/local/apache/website/incoming/_includes/_page-specialeventsnav.php
on line 20
code in question:
width='180' border='0'>
Special
Events
page_contents,page_sections WHERE page_contents.pagename =
page_sections.pagename AND page_contents.display != 'N' AND
page_sections.publicurl = '/specialevents.php' AND begdate <= Now()
AND enddate > Now() ORDER BY rand() LIMIT 6";
$r = mysql_query($x,$db);
while ($re = mysql_fetch_array($r)) {
print "href=\"javascript:focus('http://www.website.com/viewevents.php?rid=$re[id]')\">$re[title]\n";
}
$rcount = mysql_num_rows($r);
if (($rcount == 6)) {
print "href='http://www.website.com/specialevents.php'>continued...\n";
}
?>
Any and all help is much appreciated, thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
