SQL Group by months, return all months even if zero

Hi, Is there a short way to return the results from a query grouped by months and return zero if no results found: eg: SELECT count(*) as total, monthname(col) FROM table GROUP BY monthname(col) To return: 5 - Jan 0 - Feb 3 - Mar 0 - Apr etc. Just wondering. I know the long way to do it, by q

Re: Executing MySQL Commands From Within C Program

See my original post below prior to reading the next 4 lines.. In addition to what I tried below, I tried the following (with this result): $ ld -lmysqlclient -o myprog myprog.o ld: warning: cannot find entry symbol _start; defaulting to 08048208 /usr/lib/libmysqlclient.so: undefined reference to

Reduce Log Level?

For some reason mysql is logging every sql statement. I don't recall turning on such a thing. Can someone tell me how to turn that down/off. It seems a little excessive for my wimpy vps. Thanks, Mike # dpkg -l | grep mys ii libdbd-mysql-p 1.2216-2 mySQL database interface for Perl ii libm

Executing MySQL Commands From Within C Program

I am attempting to interface between a simple C program and MySQL, but without luck. I searched the MySQLforum archives to see if there was info specific to this problem. While there were other folks who had problems connecting C/C++ to MySQL, none of the issue were similar to this, that is lin

Re: Preventing Duplicate Entries

Axel IS Main wrote: I have a php app that updates an ever growing table with new information on a regular basis. Very often the information is duplicated. I'm currently handling this by checking the table for duplicate values every time I go to add new data. As you can imagine, as the table gro

Strange behavior, Table Level Permission

Hello Everyone, Using Mysql 4.1.1-alpha release on RH9, I have used RPM provided on Mysql site to upgrade from 3.23 to 4.1.1 I have a very strange behavior of mysql server,when I restart my server, it is not reading permission given to user at Table Level from tables_priv. Permission are still t

Re: Preventing Duplicate Entries

On Sun, Mar 21, 2004 at 07:24:48PM -0800, Axel IS Main wrote: I have a php app that updates an ever growing table with new information on a regular basis. Very often the information is duplicated. I'm currently handling this by checking the table for duplicate values every time I go to add new data

Preventing Duplicate Entries

I have a php app that updates an ever growing table with new information on a regular basis. Very often the information is duplicated. I'm currently handling this by checking the table for duplicate values every time I go to add new data. As you can imagine, as the table grows it takes longer a

Re: mysqldump error

At 10:12 +1100 3/22/04, tait sanders wrote: thanks - i ran the command below and it worked. and now I'm trying to import using: 'mysql -u root -p mtweblog < Bckupmtweblog.sql' on CLI on different machine and am getting "Error 1 at line 11: Can't create/write to file'./mtweblog/mt_author.frm' Er

Re: Slow Query Help

At 23:37 -0300 3/21/04, Anderson wrote: Hello, I analize my mysql slow query log and see this: " # Time: 040320 15:35:31 # [EMAIL PROTECTED]: maxpcco_autoresp[maxpcco_autoresp] @ localhost [] # Query_time: 5 Lock_time: 0 Rows_sent: 764220 Rows_examined: 764220 SET last_insert_id=764597; SELECT

RE: Slow Query Help

All you need to do is : SELECT LAST_INSERT_ID(); The way it's written, you are doing the LAST_INSERT_ID() function call a number to times equal to the amount of rows in arp3_cam -Original Message- From: Anderson [mailto:[EMAIL PROTECTED] Sent: Sunday, March 21, 2004 6:37 PM To: [EMAIL

Re: mysqldump error

thanks - i ran the command below and it worked. and now I'm trying to import using: 'mysql -u root -p mtweblog < Bckupmtweblog.sql' on CLI on different machine and am getting "Error 1 at line 11: Can't create/write to file'./mtweblog/mt_author.frm' Errorcode 13". any suggestions here? tait O

Slow Query Help

Hello, I analize my mysql slow query log and see this: " # Time: 040320 15:35:31 # [EMAIL PROTECTED]: maxpcco_autoresp[maxpcco_autoresp] @ localhost [] # Query_time: 5 Lock_time: 0 Rows_sent: 764220 Rows_examined: 764220 SET last_insert_id=764597; SELECT LAST_INSERT_ID() AS id FROM arp3_cam; #

RE: [WDG] Client doesn't support authentication protocol error inMySQL

Success I got it working after changing my password using this command: mysql> SET PASSWORD FOR -> 'some_user'@'some_host' = OLD_PASSWORD('mypass'); Afterwards I was able to log in via a DOS prompt. I then re-ran the PN install and it installed like it should. Thanks for that link and t

RE: [WDG] Client doesn't support authentication protocol error inMySQL

Cool and thanks for the update. In trying to configure my server to use old passwords I'm having a problem with one of the commands listed on the site: This command works fine. mysql> SET PASSWORD FOR -> 'some_user'@'some_host' = OLD_PASSWORD('mypass'); I get an error after att

RE: [WDG] Client doesn't support authentication protocol error inMySQL

At 19:29 -0500 3/21/04, Marvin Cummings wrote: Then again according to the MySQL website: The Windows download contains both the Standard and Max server binaries. It also contains a version of the command-line client which uses the Cygwin library to provide command history and editing. Would someon

Re: Client doesn't support authentication protocol error in MySQL

At 18:40 -0500 3/21/04, Marvin Cummings wrote: Wondering if anyone running MySQL 5 has come across the following error: Error (1250) : Client does not support authentication protocol requested by server; consider upgrading MySQL client I get this error after attempting to install PN on a Wind

RE: [WDG] Client doesn't support authentication protocol error inMySQL

Then again according to the MySQL website: The Windows download contains both the Standard and Max server binaries. It also contains a version of the command-line client which uses the Cygwin library to provide command history and editing. Would someone happen to know if there are any client libra

RE: local installation on XP

Marvin, All files exist in the WINDOWS directory. I did run the commands form the C:\mysql\bin\ directory. Then basically all that happened was that the cursor moved beneath and flashed for a bit then the directory path came up again. Still the same from mySQLCC: error1045. > [Original Message] >

Re: Bad syntax when trying to create mysql username and password...

At 18:11 -0500 3/21/04, Marvin Cummings wrote: Can someone take a look and let me know what's wrong with this command: GRANT ALL PRIVILEGES ON .* TO @ IDENTIFIED BY '' WITH GRANT OPTION; So for me I say: GRANT ALL PRIVILEGES ON testdb.* TO [EMAIL PROTECTED] IDENTIFIED BY mypassword WITH GRANT O

RE: mysqldump error

Errorcode 13 indicates that there is a permissions error of some sort. I'd double check that the mysql user/group own the data directories on the new machine. I've also found that setting up the new database on the new server helps before loading data to it. For example: mysqldump -uuser -ppa

Client doesn't support authentication protocol error in MySQL

Wondering if anyone running MySQL 5 has come across the following error: Error (1250) : Client does not support authentication protocol requested by server; consider upgrading MySQL client I get this error after attempting to install PN on a Windows 2003 web server. I've already created t

RE: Bad syntax when trying to create mysql username and password...

Ahhh. I didn't pay any attention to the quotes. Thanks -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Sunday, March 21, 2004 6:22 PM To: Marvin Cummings; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Bad syntax when trying to create mysql

Bad syntax when trying to create mysql username and password...

Can someone take a look and let me know what's wrong with this command: GRANT ALL PRIVILEGES ON .* TO @ IDENTIFIED BY '' WITH GRANT OPTION; So for me I say: GRANT ALL PRIVILEGES ON testdb.* TO [EMAIL PROTECTED] IDENTIFIED BY mypassword WITH GRANT OPTION; It returns the following error:

RE: mysqldump error

First, mysqldump is a command in and of itself so it does NOT execute from within the mysql command line tool. Next, expand your command entry a bit to include your userid and password, for example: mysqldump -uuserid -ppassword --add-drop-table mtweblog > BckUpmtweblog.sql -Original Mes

mysqldump error

hi all, I'm a newbie to mysql. have os10.3.2 running mysql v4.0.17 am trying to dump a database called 'mtweblog' into a sql file called 'BckUpmtweblog.sql'. when i do 'mysqldump mtweblog > BckUpmtweblog.sql' from CLI it gives "Error: 1045: Access denied for user: '[EMAIL PROTECTED]' (Using pa

RE: local installation on XP

Check you Windows directory for these files: a. myodbc3.dll b. myodbc3.lib c. myodbc3_install.log d. myodbc3d.dll Open a command prompt and attempt to start the service by navigating to the mysql\bin directory and typing mysqld --console - this runs some INNODB co

FW: local installation on XP

Do you see a green light in the WinMySQL Tool? Have you insalled MySQLODBC ver3.51? If so pull up a command prompt and type c:\mysql\bin> mysqld --console Also type c:\mysql\bin> mysqld --install Try this doc: http://www.mysql.com/doc/en/Windows_post-installation.html HTH -Original Message

FULLTEXT query format question

I've read through the boolean mode fulltext docs, and they address all my questions well except how searches containing exact phrases are handled when there is more than one. I believe the following will work as I expect, but was wondering if anyone can confirm it for me: Given the following se

Location search filters

Hello Can anyone provide me with a link to a tutorial or some code that can help me match resources by geographical location? EG I have a table of resources distributed across a large range of postcodes and I want to select ones within a certain distance of a given postcode I have seen it d

local installation on XP

I've just installed mySQL locally on XP and am getting the following error while trying to connect via mySQLCC. I have checked the username and password and things are ok there. XP is running the mysql-nt.exe fine, but still no go when I try to connect, any ideas?? #This File was made using

Possible OT: Unable to make database error...

I'm getting the following error while trying to install PostNuke ver.726 on a w2k3 server that's running IIS6, MySQL 5 and PHP4.3.4. I've used both MySQLCC and MySQLAdmin to create users with the necessary permissions to connect to the databases. I even tried the command line using GRANT ALL PRIVIL

Performance Koan

I've got 2 Redhat 8 machines with 4.0.1 (? latest) database. Machine1 is a P3-750, Machine2 is a P4-1.6 .. The same database is deployed to both machines using myisam tables. Query on Machine 1 takes almost 2 min to produce data. Same query on Machine2 returns in 5 seconds... ? Why? (I do

Re: Full-Text with JOIN

Hi, "Sergei Golubchik" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi! > > On Mar 20, Lorderon wrote: > > I have 3 tables to join when the last one is a Full-Text table (ft_table).. > > I do the next join: > > > > SELECT id,title FROM table1 LEFT JOIN table2 USING (id) INNER JOIN

RE: Newbie Can't Add a User to MySQL

Hi. Bit off-topic (ish) I know, however: You are missing a semi-colon in the 'forum snippet'. The line: $conn = mysql_connect("localhost", "forums", "forumuser", "somepass") or die(mysql_error()) should be: $conn = mysql_connect("localhost", "forums", "forumuser", "somepass")

Re: Full-Text with JOIN

Hi! On Mar 20, Lorderon wrote: > I have 3 tables to join when the last one is a Full-Text table (ft_table).. > I do the next join: > > SELECT id,title FROM table1 LEFT JOIN table2 USING (id) INNER JOIN ft_table > USING (id) WHERE ... > > But MySQL selects the primary key (id) to join the ft_tabl

Re: Newbie Can't Add a User to MySQL

Greetings Michael, Daniel, et al, Good news, I can get in! As evidenced below, however, my php forum script will not authenticate forumuser to insert data. Further, I tried to run a php/mysql authorization test, and still an error. I know this is not a php mailing list, but please find the pr

Re: Rollup Query?

I don't see how rollup will help you here. As I understand it, you want to GROUP BY Schedule_ID, and for each group you want to see the row with the maximal Schedule_lastupdated. The manual suggests three solutions to this problem . Michae

Rollup Query?

Hi All, I have a complex query on master-child tables that returns this data Schedule ID Schedule CommentsSchedule_lastupdated(timestamp datatype) 301 Test TIMESTAMP1 301 Test2 TIMESTAMP2 302

ESSAYEZ CA MARCHE (try it works)

Télécharger ou lisez le fichier joint intitulé "argent.doc" à cette adresse: et vous comprendrez http://membres.lycos.fr/facartier/

Re: Tough Query for this New DBA

Brian, Identical area codes will not get you nearest neighbours in cases of metro areas with multple area codes, or in cases of nearby companies on different sides of area code boundaries. For nearness, you need need to plug in geographical data, I think. On your approach, though, are you looking

RE: multiple databases: design question

The not very useful answer would be: It depends on what you are trying to achieve. More usefully (I hope): I work with a complex web application which is also strongly modularized. This system is generally delivered using several databases, residing on different servers, to allow us to cope with

Full-Text

Hi.. When doing the next query, the rows are not ordered automatically by the coefficient of the full-text.. Why??? SELECT id,update_time FROM table1 INNER JOIN ft_table USING (id) WHERE MATCH (title,content) AGAINST ('class') LIMIT 0,50; thanks, -Lorderon. -- MySQL General Mailing List For

multiple databases: design question

What are the advantages and disadvantages of using multiple databases, versus placing all tables in one uber-database? I understand and appreciate the organizational value of multiple databases, but what other issues are involved? I ask this because I'm considering moving from tables across mu

Re: Tough Query for this New DBA

Hi Brian, Not answering your question, but are your table names really "table1", "table2" etc? If so, you will be running into more problems :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscen

Re: Stored Procs and Commit/Rollback Transactions

> Possibly veering off topic, but I have a strong urge to comment on this, and > shall! > > I am a M$ .NET developer (primarily ASP.NET with SQL Server), and have > recently embarked on a project at home, and wished to apply the same sort of > principles that I use at work - for example, keeping

nested transactions

Does INNODB support nested transactions? If so, what version? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

txns with INNDOB

Hi -- I am trying to understand txns under INNODB. I'm running 4.0.16-nt-log. Here's my test code, all run in one MySQLYog window. -- step 1 use scratch; -- drop table foo; -- create table foo (x int); truncate table scratch.foo; -- step 2 use scratch; set autocommit=1; -- step 3 begin work

Tough Query for this New DBA

There has got to be a better way for me to query/subquery this data. I have 4 tables. Table1 - Company_Name | Company_ID - Blue Shoe| 101 Fast Cow | 102 Table2 ---