Tag Info

New answers tagged

0

Sounds like you want only the orders with a certain status/type... SELECT tblstockingorders.*, tblstatushistory.status AS actualStatus FROM tblstockingorders INNER JOIN tblstatushistory ON tblstatushistory.id=tblstockingorders.stockingorderid WHERE tblstatushistory.itemtype=2 AND tblstatushistory.status=0 editted version ...


1

My immediate suspicion is that there is another my.cnf present on the system. You should run locate my.cnf and see if the reports multiple my.cnf files If there is only one my.cnf, then my.cnf is most likely in the wrong folder. It should be in one of the following places /etc/my.cnf /etc/mysql/my.cnf /var/lib/mysql/my.cnf ~/my.cnf Please check for ...


1

I'd actually think about adding a status column to the table tblstockingorders and having a trigger on that table that injects the status into the tblstatushistory. Granted you'd have to do a 1-time update to all the rows in tblstockingorders (something similar to your query above) and set their last status, but this would give you best overall performance ...


0

There are several methods you can use such as mysqldump folder copy enterprise backup for mysqldump use following command mysqldump -uuser -ppassword --database your_db_name --routines --triggers --events --lock-all-tables >outputfile.sql and use following command at destination mysql -uroot -ppassword You can also use following method stop server at ...


0

Use grep to exclude databases you don't want: candidates=$(echo "show databases" | mysql | grep -Ev "^(Database|mysql|performance_schema|information_schema)$") mysqldump --databases $candidates From looking at http://stackoverflow.com/questions/19354870/bash-command-line-and-input-limit it seems like you'll be able to handle long lines. Otherwise you can ...


0

MySQL Perspective I once had a client that had a CRM aaplication with this layout 780 databases 162 tables per database See my full description in an old post : More table or more database for better performance? The challenge I faced doing database maintenance was the following 5 procedures: troubleshooting and optimizing slow queries add supportive ...


1

Having been on the receiving end of this multiple times from COTS applications, I would choose multiple databases almost every time. In this case, I would still choose multiple databases over any other, and here is why: !This is for the SQL Server related tag and is not transferrable in logic to other RDBMSs! Standards - each database is setup the same ...


0

I had answered a question back in January 2013 concerning the thread state "System Lock" : What does 'system lock' mean in mysql profiling a LOAD DATA INFILE statement? Here is what could be happening, please follow along In my old post, I brought up the following The thread is going to request or is waiting for an internal or external system ...


0

You can choose the third option, A table with all records, make a column as CustomerId with bigint datatype and auto-incrementing(IDENTITY), and make that columns as PRIMARY KEY. Now you can think indexes later depending on how you want to fetch data from table and will be fine in terms of performance. This first two options will make too many objects ...


1

Use a before insert trigger to set the new values back to the old ones? Or, even better, set it to do nothing at all - as per below. As Eric Raymond pointed out in his (good) book "The Art of Unix Programming", the fastest code is that which doesn't have to run at all!. (Note - don't have a running system to test - but I think the underlying concept is ...


1

I would use a before insert trigger: CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN SIGNAL SQLSTATE VALUE '99999' SET MESSAGE_TEXT = 'You cannot insert rows to this table.'; END;


0

Since you used rand () function it will shuffle the rows for every few seconds and it will produce a repeatable sequence of random numbers each time.So surely the value will be different from the one which you got an hour ago. Hope this helps;


0

The correct solution is not to try concatenating quotes around the argument. That is a very naive approach, and leads to SQL injection vulnerabilities. The QUOTE() function solves this, when it's needed. However, you do not need these things to be quoted at all in the queries you're running. Worse, adding literal quotes to these values is likely to get ...


0

There's not an internal way to do this, but it's easy enough to automate with a script, like the following: #!/usr/bin/env perl use strict; use warnings; use DBI; my $sleep_between = 1; # artificial delay; set to zero to remove my $origin = 'origin_server_hostname'; my $oport = 3306; # origin server port my $ou = 'origin_server_username'; my $op = ...


0

You have probably encountered a bug caused by your default server charset BUG #74192


0

There's a good post here. I suggest that you spend some time studying this page (I'm assuinmg that you are familiar with Informix). As the first post suggests, you'll have a lot more trouble if you are migrating stored procedures - frequently there isn't a 1-to-1 correspondance between logical constructs as there can be between different datatypes. As for ...


0

SELECT users, COUNT(*) FROM ( SELECT `user1` AS users FROM usuarios WHERE (`user1` is not null) UNION ALL SELECT `user2` FROM usuarios WHERE (`user2` is not null) ) sq GROUP BY users; Do the grouping outside. The trick is to use UNION ALL instead of UNION. The latter one uses a distinct on the result. You don't have to ...


0

You first need to convert that output file nearest to MySQL syntax and convert the data type according to MySQL. You could user perl or sed scripts for first time and then use these techniques for future use. Once you have prepared the files of table creation then generate dynamic insert statements from informix and use them in MySQL for loading data. ...


0

This finally worked DELIMITER // CREATE PROCEDURE ja_logan_proc() BEGIN SELECT id, dest_msisdn, text_message INTO @the_id, @the_number, @the_message FROM incoming_sms where job_status = 0 AND text_message LIKE '%logan%' order by rand() limit 1; if((select search_number_in_r4(CONCAT('`',@the_number,'`'))) = 'true')then call ...


2

An example from the manual: First, you must have a table on the remote server that you want to access by using a FEDERATED table. Suppose that the remote table is in the federated database and is defined like this: CREATE TABLE test_table ( id INT(20) NOT NULL AUTO_INCREMENT, name VARCHAR(32) NOT NULL DEFAULT '', other INT(20) ...


0

When you do your update, set the lastUpdate column equal to itself, that way it won't default in the current timestamp: update productsStock set productName="HP PRINTER 20000", lastUpdate = lastUpdate where product_reference="001";


1

The InnoDB buffer pool caches queries; if you have less usage on one of the nodes then you're going to have less cache used. If you're not properly load-balancing queries then the amount of cache in use on each node would be different. If you just turned on a node and added it to the cluster the amount of cache would be different than the others one that ...


0

It is a non goal of pt-table-checksum to show you the actual differences. However, there is a companion tool pt-table-synch which can read the checksum table generated by pt-table-checksum. It will take the bounds of chunks where master/slave checksums differ and then scan with in those to identify rows that don't match. It will even generate replace ...


2

This is likely to be a many to many relationship. create table drivers ( driver_id int primary key, ... ); create table trucks ( truck_id int primary key, ... ); create table drivers_trucks ( driver_id int references drivers(driver_id), truck_id int references trucks(truck_id), primary key (driver_id, truck_id) );


0

Use this CREATE FUNCTION the_nxt (in_number VARCHAR(10)) RETURNS varchar(10) BEGIN if (exists(select tel_number from nxt_records where tel_number='in_number' )) then return 'true'; else return 'false'; end if; END$$ Faced this error ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary ...


1

I found the error to have been my varchar CREATE FUNCTION the_nxt (in_number VARCHAR(255)) RETURNS varchar which i changed to CREATE FUNCTION the_nxt (in_number VARCHAR(255)) RETURNS varchar(255) and worked.


1

I think what you're looking is simply to use INSERT IGNORE. Forget about steps 1 and 2, just insert and ignore :) If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and ...


1

Swap the foreign key so that the Drivers table has id_truck. This way multiple drivers can be associated with the same truck.


1

Install percona-toolkit. Then you can use pt-online-schema-change. I usually use it when I have to alter tables. What it does is copy the data from your current table to another table. Triggers are automatically created, that copy incoming data to the copy while copying. This only works flawlessly if you don't have triggers on the table already. After that, ...


0

Yes, I would add a surrogate 4-byte integer key. Your current two columns are 100 bytes, this could then be reduced to 58 bytes by the addition of the new identity column. You could even make the surrogate key a 2-byte smallint if you are sure you will never exceed 32,767 categories (might be still a good idea to leave as INT just in case). The space ...


0

VARCHAR column as Primary Key is not a good choice as normally we create Cluster Index on same column. Cluster Index on VARCHAR columns is a bad choice because of expected high fragmentation rate. Every new inserted key value will try to find its place somewhere between existing keys and normally cause page split and high index fragmentation. As a result ...


3

You don't need self-joining or subqueries at all. This query does the trick: select id from your_table t group by id having sum(name = 'John') + sum(name = 'Jill') = 2 and count(*) = 2; see it working live in an sqlfiddle name = 'whatever' in the sum() function returns 1 or 0, true or false.


1

EXPLAIN will get the execution plan for a query ANALYZE TABLE recalculates the index statistics. So just run ANALYZE TABLE `pics`; Give it a Try !!!


0

Look for all last_payment_Date values that are less than the first of this month SELECT `Name`, `Acc_No` FROM `user_table_details` where `last_payment_Date` < (CURDATE() - INTERVAL (DAY(CURDATE()) - 1) DAY); Give it a Try !!!


0

Consider generating a table of hours as timestamps: HOUR ---- 2014-10-22 12:00 2014-10-22 13:00 2014-10-22 14:00 2014-10-22 15:00 ... Then you can select the hours that are between the periods of your original table. Something like the following: select name, count(distinct hour) from HOUR join PERIOD where hour between PERIOD.start and PERIOD.end ...


1

Since we don't have the hardware, of course, I can't benchmark it While you can't simulate the exact hardware you might be able to get some estimates by comparing simpler local hardware with similar relative differences. If you are upgrading from spinning rust in a similar RAID config then you could benchmark a single traditional drive against a single ...


0

This works SELECT MONTH(CURDATE()); With the final query like SELECT * FROM tbl_orders WHERE MONTH(date) = @month AND YEAR(date) = @year; Here @month and @year are numeric values from the administrator. Defaults could be SET @month = MONTH(CURDATE()); SET @year = YEAR(CURDATE); This is the quick and dirty solution applicable for a ...


2

I'm a proud owner of a Samsung 850 Pro for my desktop machine. It is a great disk, but it is not server-grade, and it is far from the PCI Flashcards sold by Virident and FusionIO (to put examples of some known brands). On the specialized hardware, a recent version of MySQL is almost a must, and some configuration tuning is needed to get most of them (change ...


0

It's not the only way to do it, but the other solutions won't perform any better. You could create a function and use that for the values of your column, which might make your query easier to read. But it won't perform better and depending on your scale, it may perform quite a bit worse.


0

The entire folder should be owned by mysql cd /usr/local/mysql-5.6.21-osx10.8-x86_64/data/ chown -R mysql:mysql * Then go restart mysql Give it a Try !!!


1

Convert the first value using user variables to load the True/False values. Then, compare it to the value 'True' PROPOSED SOLUTION LOAD DATA LOCAL INFILE 'C:/bin/input.txt' INTO TABLE n1 FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' (@var1,c2) SET c1 = (@var1 = 'True'); SAMPLE DATA C:\bin>dir Volume in drive C is TI10665200H ...


0

You can use hostname in grant statements as well, as you are using shared ip then you can limit it with hostname specific grant such as below grant all on db.* to 'user'@'hostname' identified by 'password'; To allow from all IP's/hosts grant all on db.* to 'user'@'%' identified by 'password'; For localhost only grant all on db.* to 'user'@'localhost' ...


2

Following the famous article "fixing ORDER BY rand()", and in particular the multiple-selection via union, we can write this: ( SELECT `p1`.*, `users`.`username`, `users`.`displayname` FROM pics p1 JOIN (SELECT ceil(rand() * (SELECT max(id) FROM pics)) AS id ) AS p2 ...


3

Instead of doing TRUNCATE TABLE (which locks up any connections accessing the table), try making an empty copy of the table, swapping it in, and dropping the old table. EXAMPLE Suppose the table is called mydb.mytable. Do it like this USE mydb CREATE TABLE mytable_new LIKE mytable; ALTER TABLE mytable RENAME mytable_old; ALTER TABLE mytable_new RENAME ...


0

Try this: GRANT SELECT,UPDATE,INSERT,DELETE ON yourdb.* To your_user@'192.162.1.1' identified by 'password'; To allow entire intranet, or similar: GRANT SELECT,UPDATE,INSERT,DELETE ON yourdb.* To your_user@'192.162.%' identified by 'password'; Reference: http://dev.mysql.com/doc/refman/5.5/en/grant.html Hope this helps.


2

Although TRUNCATE TABLE is definitely faster than DELETE FROM I would stick to deleting the records in small chunks. The TRUNCATE TABLE sometimes can be still slow because a lot of stuff is going on behind scenes: it has to grab exclusive lock on the dictionary, it still has to delete ibd file and re-create one, it has to evict pages from the buffer pool. ...


2

Use TRUNCATE TABLE (that will empty the table in the fastest way possible, by droping it and recreate it in a non-rollable-back way. If that takes too much time for you (can happen in older versions of mysql using innodb_file_per_table), you can run it independently on master and each slave with SET sql_log_bin = 0; The underlying bug is probably this ...


0

Depending on the tool and parameters used to create the .sql dump file of multiple databases, the file will normally have CREATE DATABASE DBn...; and USE DBn; statements that will allow your import to proceed without hiccups. For example, both the mysqldump command mysqldump command and phpMyAdmin's Export function for multiple database insert them. ...


0

If I'm not mistaken the pt-table-checksum uses report_host value to find slaves. May I suggest: stop slave2. Note its current position. Temporarily set up slave2 to replicate slave1 (binglog needs to be enabled on slave1) Start only the io thread to get replication chain (i.e. start slave io_thread;) Run pt-table-checksum from slave1 After check is done, ...


0

You say you have bin logging enabled. This leads me to believe you have replication running. To minimize down time you could promote your slave to master. The finer details on how to do this really depends on your setup. Maybe it will involve updating application configurations, maybe it will involve changing the DNS to your "master server". Prior to ...



Top 50 recent answers are included