Posts by smantscheff

Oh well. Indexes. Who would have guessed that you did not use them in the first place...
Regarding the logging problem: show the relevant lines from my.cnf and a long listing of the /var/log directory.
And activate the option

log-queries-not-using-indexes

in my.cnf, in case you forget to use indexes another time.
And learn to use the EXPLAIN statement.

Try the offending query from the mysql command line. Check how long it runs on the online machine and on the local server. Also the error message suggests that this is not a mysql error or timeout but a timeout of your client component. Try to increase the timeout for that component.

Why are connections kept open so long? Did you try to reduce the connection idle time (the timespan a connection waits for new input until it closes - I forget the names of the relevant parameters) ?

Store images and text in different columns, use CSS classes for image position and store the CSS class name with the option.

Your post is not clear. Which error occurs when? To get a synchronous insert and update behaviour, use transactions to bracket all statements.

Check for slow queries. (Look up how to enable the slow query log in my.cnf). Check if you can optimize the slowest. Also have a look at the process list while mysql is running under high load. (show processlist from the mysql command line). Which processes are open? Just waiting for connections, or what are they doing?

You can group all rows by their due date and concatenate the task names using the group_concat function. Along those lines:

select (if due_date < now(), 'overdue', if( due_date < date_add(now(), interval 1 day), 'today', due_date)) as "Due date", group_concat(taskName) as "Tasks" from MyTaskTable group by "Due date";

This is of course untested code.

To increase safety, you should also
- restrict user access to the database to the IP address of your webserver, so that no intruders from outside can read the database;
- store the access data (username, database name, password) in a separate file with a name beginning with a dot (like ".access.inc.php"), so that this file won't be served by your webserver even if PHP is deactivated.

You cannot INSERT with a WHERE condition. You can UPDATE or DELETE with a WHERE condition.

This is bad table design. veh_status should be a separate table with a 1:n relation between vehicles and veh_status.

What kind of a setup is this where a user may handle all databases except one? Sound like Bluebeard's Lair.
If I ever had this problem I would write a script (on OS level) which processes a list of database names, a username and an exception name as arguments and grants all privileges for any of the database which does not match the exception name.

The problem probably was not a data mismatch but a data type mismatch. InnoDB requires exactly the same data type in relations, so you cannot link an integer(11) to an integer(5) field.

In practice, limits are set by the hardware, not the software. If your budget allows for adding new nodes as necessary, mysql in the partitioned version is capable of bookkeeping all particles in the universe.

If they have all the same structure just concatenate them into one file and import it.
If this is not sufficient, have a look at the LOAD DATA INFILE command and the mysqlimport utility.

You could create a temporary table which links the taxonomy_index.tids to the column labels and use it in a left join construction.
For further help post a test case, including CREATE TABLE and INSERT statements.

Your code is flawed. AND has a higher precedence than OR, so you have to bracket the OR clauses for the desired results:

SELECT * FROM dms_docman
WHERE dmname like '%2012%'
AND (catid like "%mysearchterm%" OR catid like "%mysearchterm%")
ORDER BY dmdate_published DESC
limit $offset,$records_per_page`

Show the complete CREATE TABLE statement.
Make sure that both waiter.id_w and customer_1.id_w have an index. The InnoDB engine needs an index on all fields which are used in a foreign key relation.

You have to expand the WHERE clause:

SELECT * FROM dms_docman 
WHERE dmname like '%2012%' 
**AND catid like "%mysearchterm%"**
ORDER BY dmdate_published DESC
limit $offset,$records_per_page`

So what is your problem? Any error messages? Or what?

It all depends.
If your app does not store any user input which would be overwritten by the uploads, this might work. Keep in mind, though, that the standard mysqldump program inserts code which disables reference integrity checks (for performance reasons, I assume).
And if your database is large, the app which is fed by the database may stall for some time until all data is loaded.

Your attempt is definitively flawed. If you implement it like that, a deletion of a post may lead to the deletion of reports on comments with the same id.

Another solution might be to combine posts and comments in one table, since they only differ in the reference to a parent. With this structure

 create table posts_and_comments
(
    id integer not null primary key auto_increment,
    body text not null,
    post_id integer references posts_and_comments(id)
);

a record without a post_id is a post, and with a post_id it is a comment. Then you need only one reports table which refers to the unique id of posts_and_comments.

/*    
drop table if exists customeramount;
create customeramount (custid varchar(20), date date, amount integer);
insert into customeramount values (
('1335279294867','2012-04-28','5'),
('1335518740839','2012-04-28','124'),
('1335357566946','2012-04-28','124'),
('1335279294867','2012-05-17','10');


drop table if exists customerpurchaseamount;
create table customerpurchaseamount (TransactionID integer, CustomerID varchar(20), Amount integer, Date date);
insert into customerpurchaseamount values
('1','1335518740839','5','2012-05-17'),
('2','1335518740839','10','2012-05-31');
*/
select custid, ifnull(r,amount) as result
from
(

select a.custid, 
a.amount, a.amount - (
  select sum(b.Amount) 
  from customerpurchaseamount b 
  where a.custid=b.CustomerID
) as r
from customeramount a
) x

So where is the problem?

Show some test data and the unexpected as well as the expected results.

Also keep an eye on the new argentine legislation where you are free to choose your gender as you like, and on the european discussion on transgender people. Might be that boolean is not the correct datatype for gender after all. In modern applications I'd recommend at least four choices: Female, Male, Trans, and Don't know.

Thanks for sharing the results. I saw the problem but did not come up with a solution right away - glad that you've found it.

It is not clear what your problem is. Or how you tried to solve it.

You can sort and group your table by race and forecast. If that's what you want, try
select * from races order by race, forecast

If you want an explicit "rank", count the number of horses with higher forecasts in the same race and add 1:
select a.race, a.horse, a.forecast, (select count(*) from races b where a.race=b.race and a.forecast > b.forecast) + 1 as theRank from races a;

The character set is a property of the server, the database, the table and the field - in that order. Each has a default which can be overwritten by the following. You can change it using the alter table syntax.
Then there is the client and the connection character set which define how the character set is translated on its way from the database to the end user. You can alter those by setting defaults in my.ini (or my.cnf) or by starting the mysql server with explicit values for those variables:

character_set_client
character_set_connection
character_set_database
character_set_filesystem
character_set_results
character_set_server
character_set_system
character_sets_dir

This is a network connectivity problem. Might be that the mysql server on stoberwebcom.ipagemysql.com has to high load to response. That can happen on shared servers by other users and other applications than yours.

Unfiltered use of $_GET variables in SQL statements opens a door for SQL injection. It does not matter if the values are checked against a reference table - when it comes to checking, the harm has already been done.

The + operator does not concatenate strings. Use Concat() instead.
And beware of SQL injection. $_GET[agent_name] is likely to get you into trouble

What does EXPLAIN <query> tell you?
Also have a look at http://code.openark.org/blog/mysql/7-ways-to-convince-mysql-to-use-the-right-index to get some optimization ideas.

You need some sort of chaining of the records. Add a field like NextTransactionID or PreviousTransactionID which will allow you to insert and delete records without breaking any chains.

So better have a look at the list of built-in functions in MySQL. And you can even write your own...

From a philosophical point of view you cannot alter anything in a database without altering its information, because structure and data are what database information consists of. Even if you add only a column with no data or an enum value which is never used the semantics of the database will be changed by that action.
So better specify what your problem is and you might get a helpful answer.

Use the IF or IFNULL function:

SELECT mem_id, IFNULL(company, CONCAT(fname, ' ', lname)) as theName, addr1, addr2, city, state, zip, country FROM dc_order WHERE STATUS='W'

or

SELECT mem_id, IF(length(company) > 0, company, CONCAT(fname, ' ', lname)) as theName, addr1, addr2, city, state, zip, country FROM dc_order WHERE STATUS='W'

@urtrivedi: It's a question of coding efficiency versus runtime efficiency. In small projects I prefer the former. So I do not claim that my suggestion is better than yours, but it has an other focus.

You don't even need any IF for your condition, because like '%%' matches just all content, so you don't have to test for empty variables:

$sql = sprintf( "SELECT * FROM property WHERE id > 0 
and city like '%%%s%%'
and state like '%%%s%%'
and county like '%%%s%%'
and status like '%%%s%%'
and bank_officer like '%%%s%%'"
, mysql_real_escape_string($_REQUEST["city"])
, mysql_real_escape_string($_REQUEST["state"])
, mysql_real_escape_string($_REQUEST["county"])
, mysql_real_escape_string($_REQUEST["status"])
, mysql_real_escape_string($_REQUEST["bank_officer"])
)

Probably you forgot to change the delimiter before creating the trigger.

DELIMITER //
create trigger ins_notification after insert on transaction_detail
for each row
Begin
  insert into sell_notifications(user_id, product_id, quantity)
  values(new.transaction_id , new.product_id, new.quantity);
End //
DELIMITER ;

Though this would be much easier to verify if you showed us the error message instead of let us grope in the dark.

Now it starts to make sense. There is still an error in your sample. The tuple

('2012-05-27', 700,6)

does not show in your desired results.

An inefficient, but working solution:

select min(day_date), max(day_date), price, allotment
from
(
select t1.*,
(select max(t2.day_date) from t t2 where (t1.price != t2.price or t1.allotment != t2.allotment) and t1.day_date > t2.day_date) as prior_group,
(select min(t3.day_date) from t t3 where (t1.price != t3.price or t1.allotment != t3.allotment) and t1.day_date < t3.day_date) as next_group
from t t1
) tx
group by price,allotment, prior_group, next_group
order by min(day_date)

+---------------+---------------+-------+-----------+
| min(day_date) | max(day_date) | price | allotment |
+---------------+---------------+-------+-----------+
| 2012-05-20    | 2012-05-22    |   500 |         2 |
| 2012-05-23    | 2012-05-24    |   600 |         2 |
| 2012-05-25    | 2012-05-26    |   500 |         2 |
| 2012-05-27    | 2012-05-27    |   700 |         6 |
| 2012-05-28    | 2012-05-28    |   700 |         2 |
+---------------+---------------+-------+-----------+

Please show the actual input data and your query again. I do not understand your update.

The location of the database files depends on the datadir configuration variable in my.ini (or my.cnf, depending on the installation). This configuration file usually resides in the root directory of your mysql installation.
But, as drjohn suggests, better export the whole database. mysqldump is a command line utility which comes with MySQL and exports all data. Import the dump by feeding it to the command line client mysql.

Your query would be fine if you replace 'day_add' by 'date_date':

mysql> create table d (day_date date, price integer, allotment integer);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into d values
    -> ('2012-05-20','500','2'),
    -> ('2012-05-21','500','2'),
    -> ('2012-05-22','500','2'),
    -> ('2012-05-23','600','4'),
    -> ('2012-05-24','600','4'),
    -> ('2012-05-25','500','7'),
    -> ('2012-05-26','500','7'),
    -> ('2012-05-27','700','2'),
    -> ('2012-05-28','700','2');
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql>
mysql> select min(day_date) as start, max(day_date) as end,  price, allotment
    -> from d
    -> group by price, allotment
    -> ;
+------------+------------+-------+-----------+
| start      | end        | price | allotment |
+------------+------------+-------+-----------+
| 2012-05-20 | 2012-05-22 |   500 |         2 |
| 2012-05-25 | 2012-05-26 |   500 |         7 |
| 2012-05-23 | 2012-05-24 |   600 |         4 |
| 2012-05-27 | 2012-05-28 |   700 |         2 |
+------------+------------+-------+-----------+
4 rows in set (0.00 sec)

Change your code to

if (strlen($img1))
    $img_list .= "<img src='$img1' width='268' height='269' title='$title' alt='$title'/>";

or better

if (file_exists($_SERVER['DOCUMENT_ROOT'] . $img1))
    $img_list .= "<img src='$img1' width='268' height='269' title='$title' alt='$title'/>";

What is a mysql file? A script containing mysql statements? Read more about it here

urtrivedi's solution assumes that the (id,domain) tupels are the same in 1X and 2X, i.e. that the IDs for all domains are the same in both tables. Since I do not believe this to be the case and since ID presumably is an auto_increment field, change the above suggestion to:

insert into 1x (domain) select domain from 2x where domain not in (select domain from 1x);

The exception is thrown already in the query analysis. Try to EXPLAIN your queries and you will see that the first EXPLAIN has a result while the second one is already rejected without the query being actually executed. So I'm not sure where the short-circuiting occurs - not in the execution phase, it seems.

To retrieve all allowed enum values you have to read the table definition. Use the "show create table" statement and parse it for the enum field definition.

Well, there are about 200-300 tables. Im not sure if that reaches the max and/or you would type them ALL out.....

If this is a one-time problem, dump the database and grep the output.
If it is a recurring challenge, use a script like adam_k proposed. In PHP you could write a routine which retrieves all table names, then walks through the tables retrieving all the column names and combining them into a concat expression.

In the long run you will change the table structure. If you're now starting to get in trouble because of performance issues, chances are that you will find a workaround - which will work until the next magnitude of records has been reached.

As a workaround you might try to split the UNION subquery into single conditions. I'm not sure how the query optimizer will handle it, but it might be worth a try:

SELECT count(mem_id)
FROM members WHERE mem_status = 'F' 
AND (mem_id NOT IN (SELECT mem_id FROM position_1))
AND (mem_id NOT IN (SELECT mem_id FROM position_2))
AND (mem_id NOT IN (SELECT mem_id FROM position_3))
AND (mem_id NOT IN (SELECT mem_id FROM position_4))
AND (mem_id NOT IN (SELECT mem_id FROM position_5))
AND (mem_id NOT IN (SELECT mem_id FROM position_6))
AND (mem_id NOT IN (SELECT mem_id FROM position_7))

or

SELECT count(mem_id)
FROM members WHERE mem_status = 'F' 
AND NOT (
mem_id  IN (SELECT mem_id FROM position_1)
OR  mem_id  IN (SELECT mem_id FROM position_2)
OR  mem_id  IN (SELECT mem_id FROM position_3)
OR  mem_id  IN (SELECT mem_id FROM position_4)
OR  mem_id  IN (SELECT mem_id FROM position_5)
OR  mem_id  IN (SELECT mem_id FROM position_6)
OR  mem_id  IN (SELECT mem_id FROM position_7)
)