Date: 01/18/06     Keywords: php, mysql, sql

good evening! has anybody ever seen mysql clients (mysql, php, check_mysql(nagios), etc) choke with an error similar to 'can't assign requested address' ? I've got over 30,000 connections listed in netstat with a status of TIME_WAIT. fairly basic php/mysql site (high traffic), no apparent cause.

possible causes I've thought of:

- maybe somebody initiated a connection but didn't close it.. thrwarted due to the mysql reporting otherwise.
- mysql being retarded? not likely, when the error occurs, no incoming connection is reached by the mysql server (via tcpdump/ngrep), resource usage on the server is fine. same with the process list.
- max files being reached?

any thoughts? :D

Source: http://community.livejournal.com/mysql/82879.html

Date: 01/17/06     Keywords: mysql, sql

I have a table that is all messed up so I ran 'repair table my_table'. The problem is that it never completed and didn't give me any error messages. The table is a little bit (a bit over 1 gig), but mysql is using 0.0% of the cpu so it doesn't look like it's doing anything. Does anyone have any ideas to help save my data?

Source: http://www.livejournal.com/community/mysql/82528.html

Date: 01/17/06     Keywords: mysql, sql

I have a table that is all messed up so I ran 'repair table my_table'. The problem is that it never completed and didn't give me any error messages. The table is a little bit (a bit over 1 gig), but mysql is using 0.0% of the cpu so it doesn't look like it's doing anything. Does anyone have any ideas to help save my data?

Source: http://community.livejournal.com/mysql/82528.html

Date: 01/16/06     Keywords: no keywords

I'm working on a query that grabs a list of all the users plus the latest note for that user. The tables look something like this:

user: id | email
note: id | user | body | posted

So I try something like this, but the body and the posted date don't match

SELECT user.id, user.email, note.body, MAX(note.posted) FROM user JOIN note ON note.user = user.id GROUP BY user.id ORDER BY posted DESC LIMIT 20

Source: http://www.livejournal.com/community/mysql/82411.html

Date: 01/16/06     Keywords: no keywords

I'm working on a query that grabs a list of all the users plus the latest note for that user. The tables look something like this:

user: id | email
note: id | user | body | posted

So I try something like this, but the body and the posted date don't match

SELECT user.id, user.email, note.body, MAX(note.posted) FROM user JOIN note ON note.user = user.id GROUP BY user.id ORDER BY posted DESC LIMIT 20

Source: http://community.livejournal.com/mysql/82411.html

Date: 01/15/06     Keywords: php, mysql, sql

i'm trying to select some data from a table. this bit i'm doing quite well with, however it's returning two rows per 'primary key' based on a left join. i've tried using DISTINCT but that doesn't seem to effect it. i then i tried using GROUP BY and that did, so now i'm getting the one row per primary key i was originally after, but i seem to be getting this data without the data of the left join i so desperately need. upon further investigation, back when i was getting two rows per primary key, only the second row per primary key had the data i was after. is their anyway to make sure the query returns the second row as opposed to the first?

my initial idea was to add a clause, but not all rows in the table will have data in that column, i just want to make sure rows with data in that column will definately be returned.

any suggestions?




x-posted to mysql and php_mysql

Source: http://www.livejournal.com/community/mysql/81895.html

Date: 01/15/06     Keywords: php, mysql, sql

i'm trying to select some data from a table. this bit i'm doing quite well with, however it's returning two rows per 'primary key' based on a left join. i've tried using DISTINCT but that doesn't seem to effect it. i then i tried using GROUP BY and that did, so now i'm getting the one row per primary key i was originally after, but i seem to be getting this data without the data of the left join i so desperately need. upon further investigation, back when i was getting two rows per primary key, only the second row per primary key had the data i was after. is their anyway to make sure the query returns the second row as opposed to the first?

my initial idea was to add a clause, but not all rows in the table will have data in that column, i just want to make sure rows with data in that column will definately be returned.

any suggestions?




x-posted to mysql and php_mysql

Source: http://community.livejournal.com/mysql/81895.html

Date: 01/11/06     Keywords: no keywords

Hi I was wondering if it's possable to nest a query like this

SELECT * FROM t1 ORDER BY(SELCT name FROM t2 WHERE t2.id = t1.uid)

Essentially I have 2 linked tables one holing user names and the other holing recipies and I'd like to order them by the posters name not the order in which they joined.

Any help would be much appreciated.

TIA

Paul

Source: http://www.livejournal.com/community/mysql/81232.html

Date: 01/11/06     Keywords: no keywords

Hi I was wondering if it's possable to nest a query like this

SELECT * FROM t1 ORDER BY(SELCT name FROM t2 WHERE t2.id = t1.uid)

Essentially I have 2 linked tables one holing user names and the other holing recipies and I'd like to order them by the posters name not the order in which they joined.

Any help would be much appreciated.

TIA

Paul

Source: http://community.livejournal.com/mysql/81232.html

Date: 01/06/06     Keywords: mysql, sql, google

The MySQL Meetup group meeting on Monday, January 9th will focus on backups -- I will give a 30-45 minute presentation on backups and we will then delve into a discussion -- how folks within the group are backing up, how they're not backing up, how they'd like to back up. If there's time, we'll do a "lessons learned from backup horror stories".

Afterwards, we'll head a few doors down to Boston Beer Works for more chatting.

You can see a video of last month's presentation by Philip Antionades of MySQL AB on the new features in MySQL 5.0 at Google Video:

http://video.google.com/videosearch?q=Mysql+5.0+by+Philip+Antoniades

(we will video this month's presentation but not the discussion)

More details, including the event location and directions, are at: http://mysql.meetup.com/137/events/

I encourage you to sign up to receive notification of events, and participate in discussions outside of the meetups. I won't always be posting here.

Any questions, comments, etc should be sent to me. We're currently looking for a space better than North Station -- something around MIT might be nice, and we're trying to get an MIT grad student/faculty/staff member/organization to sponsor it. (We want a location that's good for people driving AND taking the T, and when the Garden has an event, it's not feasible to park there). If you can help out, please let me know.

(You may forward this announcement to other groups, lists, blogs, whatever.)

I'm quite excited about the presentation, as I'm writing down a lot of information from different sources that really wants to be together, but as far as I can tell is not yet, at least not in a public place.

Source: http://www.livejournal.com/community/mysql/81107.html

Date: 01/06/06     Keywords: mysql, sql, google

The MySQL Meetup group meeting on Monday, January 9th will focus on backups -- I will give a 30-45 minute presentation on backups and we will then delve into a discussion -- how folks within the group are backing up, how they're not backing up, how they'd like to back up. If there's time, we'll do a "lessons learned from backup horror stories".

Afterwards, we'll head a few doors down to Boston Beer Works for more chatting.

You can see a video of last month's presentation by Philip Antionades of MySQL AB on the new features in MySQL 5.0 at Google Video:

http://video.google.com/videosearch?q=Mysql+5.0+by+Philip+Antoniades

(we will video this month's presentation but not the discussion)

More details, including the event location and directions, are at: http://mysql.meetup.com/137/events/

I encourage you to sign up to receive notification of events, and participate in discussions outside of the meetups. I won't always be posting here.

Any questions, comments, etc should be sent to me. We're currently looking for a space better than North Station -- something around MIT might be nice, and we're trying to get an MIT grad student/faculty/staff member/organization to sponsor it. (We want a location that's good for people driving AND taking the T, and when the Garden has an event, it's not feasible to park there). If you can help out, please let me know.

(You may forward this announcement to other groups, lists, blogs, whatever.)

I'm quite excited about the presentation, as I'm writing down a lot of information from different sources that really wants to be together, but as far as I can tell is not yet, at least not in a public place.

Source: http://community.livejournal.com/mysql/81107.html

Date: 01/03/06     Keywords: mysql, database, sql

While trying to connect to the mysql database, I get this error:
Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
So I look up the solutions for this error. I discover that I need to edit a my.cnf file and change the socket setting. I wish it was that easy. I check what socket is set too.

[client]
#password = my_password
port = 3306
socket = /opt/local/var/run/mysqld/mysqld.sock

Not the socket the server is trying to connect to.

The file is located in /opt/local/etc/mysql4 and I also copied it to /opt/local/etc incase.
The password and username is correct, I can manually connect to the database.
Using a Mac OSX.4 server.
DB Server version is 4.1.15
Connection: Localhost via UNIX socket

I am out of ideas, can anyone help me?

Source: http://www.livejournal.com/community/mysql/80709.html

Date: 01/03/06     Keywords: mysql, database, sql

While trying to connect to the mysql database, I get this error:
Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
So I look up the solutions for this error. I discover that I need to edit a my.cnf file and change the socket setting. I wish it was that easy. I check what socket is set too.

[client]
#password = my_password
port = 3306
socket = /opt/local/var/run/mysqld/mysqld.sock

Not the socket the server is trying to connect to.

The file is located in /opt/local/etc/mysql4 and I also copied it to /opt/local/etc incase.
The password and username is correct, I can manually connect to the database.
Using a Mac OSX.4 server.
DB Server version is 4.1.15
Connection: Localhost via UNIX socket

I am out of ideas, can anyone help me?

Source: http://community.livejournal.com/mysql/80709.html

Date: 01/01/06     Keywords: php, mysql, sql

Ok, new problem.

I have got my fulltext searches to work, but am very much against the idea of stopwords. many of them I want people to be able to search.

Now I have tried setting the path of the file containing them to nothing, but my shell access is having an off day (plus I don’t really want to change thing in there).

Is there a way in the query of letting some of the stopwords pass. I’m using version 4.0.25

Thanks for any help

x posted to php (because they use mysql too)

Source: http://www.livejournal.com/community/mysql/80433.html

Date: 01/01/06     Keywords: php, mysql, sql

Ok, new problem.

I have got my fulltext searches to work, but am very much against the idea of stopwords. many of them I want people to be able to search.

Now I have tried setting the path of the file containing them to nothing, but my shell access is having an off day (plus I don’t really want to change thing in there).

Is there a way in the query of letting some of the stopwords pass. I’m using version 4.0.25

Thanks for any help

x posted to php (because they use mysql too)

Source: http://community.livejournal.com/mysql/80433.html

Date: 01/01/06     Keywords: no keywords

I am building that joy of modern life, a search page and i seem to have run into a few problems (well, one so far, but im sure there are more on the way)

An example of the query is:


SELECT id, title, description MATCH(title, description, data_text) AGAINST('about' IN BOOLEAN MODE) AS score FROM `content` WHERE `privlevel` <= 0 AND MATCH(title, description, data_text) AGAINST('about' IN BOOLEAN MODE) ORDER BY score DESC, title ASC

and the error i get is:


text search query failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'MATCH(title, description, data_text) AGAINST('about' IN BOOLEAN

It is meant to take the search string (in this case, "about") and see if it can find it in the title, description and data_text fields of the content table. It is also meant to do a very basic score too, so with any luck, the best matches pass through to the top...

Can you see the error, or even better, do you know of a neater, fast way of doing this?

Thanks for any help.

Source: http://www.livejournal.com/community/mysql/80181.html

Date: 01/01/06     Keywords: no keywords

I am building that joy of modern life, a search page and i seem to have run into a few problems (well, one so far, but im sure there are more on the way)

An example of the query is:


SELECT id, title, description MATCH(title, description, data_text) AGAINST('about' IN BOOLEAN MODE) AS score FROM `content` WHERE `privlevel` <= 0 AND MATCH(title, description, data_text) AGAINST('about' IN BOOLEAN MODE) ORDER BY score DESC, title ASC

and the error i get is:


text search query failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'MATCH(title, description, data_text) AGAINST('about' IN BOOLEAN

It is meant to take the search string (in this case, "about") and see if it can find it in the title, description and data_text fields of the content table. It is also meant to do a very basic score too, so with any luck, the best matches pass through to the top...

Can you see the error, or even better, do you know of a neater, fast way of doing this?

Thanks for any help.

Source: http://community.livejournal.com/mysql/80181.html

Date: 12/28/05     Keywords: mysql, database, sql

I am trying to make it so this result can be ordered by the last name of the client. However, the name is in another table thus the JOIN LEFT. That part seems to be working OK (I think), but where I am running into a problem is that the results are showing duplicates (example if invoice 10, there are 3 result rows for invoice 10, but only 1 row in the actual MySQL table "invoice_main").

(Yes, there are some name/case mismatches, but I didnt create the database.)

Thanks!

SELECT DISTINCT
invoice_main.invoice_number,
invoice_main.invoice_date,
invoice_main.due_date,
invoice_main.clientid,
invoice_main.subtotal,
invoice_main.paid_amounts,
invoice_main.payment_status,
clients.ClientLast
FROM $TableName
LEFT JOIN clients ON invoice_main.clientid=clients.ClientID
WHERE invoice_main.office_id='$office_id'
ORDER BY $SortParam
LIMIT $limit,$Limit_increment

Adding GROUP BY invoice_main.invoice_number gave me an SQL error


[UPDATE] Figured it out
LEFT JOIN clients ON invoice_main.office_id=clients.office_id AND invoice_main.clientid=clients.ClientID

Gotta thank jedifreeman and his help with JOIN a couple weeks ago. I was re-reading our AIM conversation and thought of this.

Source: http://www.livejournal.com/community/mysql/80119.html

Date: 12/28/05     Keywords: mysql, database, sql

I am trying to make it so this result can be ordered by the last name of the client. However, the name is in another table thus the JOIN LEFT. That part seems to be working OK (I think), but where I am running into a problem is that the results are showing duplicates (example if invoice 10, there are 3 result rows for invoice 10, but only 1 row in the actual MySQL table "invoice_main").

(Yes, there are some name/case mismatches, but I didnt create the database.)

Thanks!

SELECT DISTINCT
invoice_main.invoice_number,
invoice_main.invoice_date,
invoice_main.due_date,
invoice_main.clientid,
invoice_main.subtotal,
invoice_main.paid_amounts,
invoice_main.payment_status,
clients.ClientLast
FROM $TableName
LEFT JOIN clients ON invoice_main.clientid=clients.ClientID
WHERE invoice_main.office_id='$office_id'
ORDER BY $SortParam
LIMIT $limit,$Limit_increment

Adding GROUP BY invoice_main.invoice_number gave me an SQL error


[UPDATE] Figured it out
LEFT JOIN clients ON invoice_main.office_id=clients.office_id AND invoice_main.clientid=clients.ClientID

Gotta thank jedifreeman and his help with JOIN a couple weeks ago. I was re-reading our AIM conversation and thought of this.

Source: http://community.livejournal.com/mysql/80119.html

Date: 12/28/05     Keywords: no keywords

I want to merge these three queries into one query (and then add more) but I cant seem to wrap my head arround combining the first two.

ultimately I want to combine the 3 subtotals into one value.

Query1
SELECT invoice_number, sum(((TIME_TO_SEC(end_time) - TIME_TO_SEC(start_time))/3600) * rate) AS subtotal
FROM invoice_items
WHERE office_id='$office_id' AND service_type!='225'
GROUP BY invoice_number
ORDER BY invoice_number

Query2
SELECT invoice_number, sum((1) * rate) AS subtotal
FROM invoice_items
WHERE office_id='$office_id' AND service_type='225'
GROUP BY invoice_number
ORDER BY invoice_number

Query3
SELECT invoice_number, sum((mileage*mileage_rate)+groceries_value-discount_value) AS subtotal
FROM invoice_main
WHERE office_id='$office_id'
GROUP BY invoice_number
ORDER BY invoice_number

Source: http://www.livejournal.com/community/mysql/79721.html