MySQL Replication not proceeding I have a weird replication problem I have not seen before.  It's basic mysql replication with single master and two slaves.  One of the slaves is not executing replication events and seems just stuck at some point yesterday.  The other is current.

Running show slave status on the problematic slave shows no increases in any of the counters.
It lists both the IO and SQL threads as running.
Seconds behind reports 0.
None of the log counters are increasing.
No errors are reported.  Running stop/start slave return no errors.  Bouncing the server reports nothing out of the ordinary in the .err log and says it's picking up replication from the relay log position it's stuck on
The master shows the slave as connected
The slave shows two system user replication threads reporting "Waiting for master to send event" and "Slave has read all relay log; waiting for the slave I/O thread to update it".  Their Time counters in the process list are just steadily increasing.
Attempts to connect from the slave w/ the replication credentials to the master via commandline client work fine
There is plenty of disk space in both the datadir and logdir
The Master_log_file it's reporting still exists on the master according to both show binary logs and looking at the actual filesystem (it wasn't pruned or manually deleted from the FS)
The master and both slaves are running the same percona build (5.5.29-30.0-log) and have been as such for many months.

I'm at a loss on what to further troubleshoot. Help?

This turned out to not be a mysql issue at all.  The network team recently installed a new security device that will block packets on certain rules.  A legitimate database write contained a sequence of characters the device deemed nefarious.

The overall connection handshake for replication was able to make it fine but then it just sat there asking the master for the next log entry whose packets were never making it back.

As far as the broken slave was concerned, it was up to date b/c it had executed the most recent event in the relay logs it had received.


Table name with special character in the end I have a table in database(oracle sql) which somehow contains one special character at the end which I dont know.I am trying to get that special character from its entry in table tab.

SQL> SELECT tname from tab where tname='OPERATION';

no rows selected

SQL> SELECT tname from tab where tname like 'OPERATION%';

TNAME
--------------------------------------------------------------------------------
OPERATION

SQL> SELECT length('OPERATION') from tab where tname like 'OPERATION%';

LENGTH('OPERATION')
------------------------
                       9

SQL> SELECT length(tname) from tab where tname like 'OPERATION%';

LENGTH(TNAME)
-------------
           10

So , there is one special character after char N in string OPERATION .
I tried getting the last character using substr but that did not help.

How can I find this special charater ( or even ascii value of this character ) ?

And how can I run select on the data in this table ?

You can try the dump function to get the actual byte values stored. For example, I just created a table "OOUPS ", with a space at the end.

> select table_name, dump(table_name) from user_tables;
...
OOUPS   Typ=1 Len=6: 79,79,85,80,83,32
...

You can see 32 (0x20 hex) as the last char - that's an ASCII space.

Once you've found what the extra character is, you can query the table using double quotes around the name. This is case-sensitive, so make sure you match that too. e.g.:

create table "OOUPS " (a int);
insert into "OOUPS " (a) values (42);
commit;
select * from "OOUPS ";

And of course:

alter table "OOUPS " rename to more_sane_name;

If you can't type the strange character easily, simplest way would be to create an SQL script, or pipe the exact character sequence to SQL*Plus:

OOUPS   Typ=1 Len=6: 79,79,85,80,83,13

$ echo -e 'alter table "OOUPS\r" rename to saner_name;\nexit;' | sqlplus user/pass
...
Table altered.


sysschedules datatype choices I am curious, why does SQL Server save the values in msdb.dbo.sysschedules for date and times as int instead of datetime.  I assume the reason dates back to something in SQL Server 2000.

Was it a storage capacity issue, performance, or other?

Because back then design controls were much looser than they are today, and the person who was responsible for designing this table thought it would be easier to deal with ints than with separate datetime columns (remember this was back before we had separate date and time data types, and also when the prevailing logic was that dealing with integers was much more efficient than strings or dates). We're all still paying for this design choice. I've been tempted to try creating my own table, replacing the existing table with a view, and seeing how much I could break (not sure if it's possible, but it would make any custom coding around Agent a lot less painful).

Not sure you'll ever track down why now, unless you corner that developer and feed him or her a case of scotch. It's likely the same mentality that led to two SPID columns in sp_who2 output (true story, or so I'm told - the developer got sick of scrolling back to the left to see which SPID was responsible for the metrics on the right, so he or she added another entry at the end of the column list).


Spike and time out errors every x th minute I have very complicated situation here.

Application team was reporting that they see spikes and errors every 20 mins. We checked what was running and found log backups.

So we tuned log backups using different compression level available in idera third party software. They used to run for 5 mins and now started competing in 3 mins.

Application team saw little relieved but issues seems to be relevant and they are still concern on spikes.

To play little further, we changed log backup to run every 13 mins to find whats really going on. And here it is, mo more time outs when log backup completes or run other than every 20 mins schedule.

Checked everything on SQL Server and did not see any other stuff running every 20 mins.

The issue starts every 20 mins which last for about on avg 20 seconds. Unable to find any SQL queries or related process. So far no scheduled task at O/S level found as well.

Waits: LCK-XX being the higher no followed by Pageiolatch...

Snaphshot isolation for all db's: RCSI

I will try to add more info, but please suggest what can be checked here to find and resolve this performance issue occurring every x mins.

This is to help others as this was a very tricky solution to find:

We found below to be the problem where all the available core schedulers were not visible online

i.e we had SQL server enterprise edition because of which all 128 cores were not used and instead visible online were 40 only.

After upgrading to enterprise: core license , we were able to use all schedulers and viola!

Hope this helps as sometimes licensing could be tricky and we might skip


MySQL parent / child table not showing parent entries I have a table in which categories are listed, and each product can belong to a parent category or not. There is only one level of possible depth.

CREATE TABLE `categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `parent` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
);

I then insert some records:

INSERT INTO `categories` (`id`, `name`, `parent`) VALUES
(1, 'Cat 1',    NULL),
(2, 'Cat 2',    NULL),
(3, 'Cat 3',    NULL),
(4, 'Subcat 2.1',   2),
(5, 'Subcat 2.2',   2);

Now I'd like to get a list that includes all the categories and subcategories, ordered by main category and with the subcategories underneath, like so:

Cat 1
Cat 2
Subcat 2.1
Subcat 2.2
Cat 3

When I try the following it almost works, but it doesn't return the record for "Cat 2", only it children.

SELECT e.name AS parent_name, e.id AS parent_id, r.id AS child_id, r.name AS child_name
     FROM categories e
LEFT JOIN categories r ON e.id = r.parent
    WHERE e.parent IS NULL
 ORDER BY parent_name, child_name

Results:

Cat 1   1   NULL    NULL
Cat 2   2   4   Subcat 2.1
Cat 2   2   5   Subcat 2.2
Cat 3   3   NULL    NULL

How can I make it return all 5 records including the "Cat 2" parent category?

The join should be on the opposite direction and without the WHERE. Think of this as starting with any category (r). All of them either have a parent (so the parent_name and parent_id will appear) or not (so those columns will have NULL):

SELECT e.name AS parent_name,
       e.id AS parent_id,
       r.id AS child_id,
       r.name AS child_name
FROM categories r
     LEFT JOIN categories e
     ON e.id = r.parent
ORDER BY COALESCE(parent_name, child_name),   -- first the parent or itself
         child_name ;                         -- then itself


Require to Send a formatted HTML Email via Database Mail in Sql Server 2008 R2 I need to send an e-mail from SQL Server in the below format, as requested by the client:

Dear colleague,

See attached file with your current list of high priority ticket notifications.

Following tickets are listed:

* High priority tickets: last article time is at least 7 calendar days in the past.
* Very high priority tickets: last article is at least 3 calendar days in the past.

Please check if the priority of these tickets is still correct.
If so, don’t forget to focus on them.

Thanks in advance for reserving some time to work on them.

As of now I have been using the below format to send short messages:

Declare @BODY VARCHAR(50)
declare @id  varchar
declare @email_id varchar(50)
DECLARE emailCursor CURSOR FOR
SELECT id, email_id FROM email_address
OPEN emailCursor
FETCH NEXT FROM emailCursor INTO @id, @email_id
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @BODY = 'The following ticket'  +  @id  + 'is open'
    EXEC msdb.dbo.sp_send_dbmail
        @recipients = @email_id,
        @profile_name='Currency Rate Notifier',
        @subject = 'Open tickets',
        @body = @BODY
    FETCH NEXT FROM emailCursor INTO @id, @email_id
END
CLOSE emailCursor
DEALLOCATE emailCursor

Any help would be appreciated.

When calling sp_send_dbmail, you can set @body_format='HTML' and place the HTML source code in the @body parameter.

Important: I'm not sure if there will be a plain-text version of your message for recipients that can't or won't read HTML formatted e-mail.

Like Mark says, you should check the documentation for sp_send_dbmail for more information. Also, if you want the challenge, you could use the FOR XML construct in a SELECT statement to generate an XML document where the elements are named and arranged like HTML tags.


Fill factor for later bigserial + index I'm planning to import and update a huge table (3 Bil. rows) in the following step:

create table with fill factor x
import the data from csv files (6 files, ~21GB each)
add a BIGSERIAL column
create index for the BIGSERIAL column
cluster the table by the BIGSERIAL column?
update one column in the table in batches specified by a range of the BIGSERIAL column

It's a huge operation for me, so I really need your advice on the fill factor x I need to set in step 1, so that it'll have the best impact on the subsequent steps. All columns in the table definition are fixed size. Step 6 is supposed to be most CPU-intensive, since it involves spatial queries (postGIS).

Assuming you are using COPY to populate the table I don't see a reason to create the table without the bigserial column.

Just create the table including the bigserial, but don't specify it in the COPY command. It will then automatically be filled with values during the import.

create table foobar (id bigserial, col1 text, col2 text);
copy foobar (col1, col2)
   from 'foobar.txt'
   with (format csv, header false, delimiter ',');

That would remove steps 3,4 and 5.

I'm not sure what you mean with "range of bigserial" in step 6.


How query execution time and concurrent users affect DTU I am using Azure SQL database. In Azure, pricing is calculated on the basics of DTU (Database Throughput Unit). There are lots of articles and QA regarding this but I failed to find the Steps for calculation of DTU or what are the factors affecting DTU. If I know the calculation steps, I can make changes in my codes or queries so that I can utilize the resource in a good manner.

Whether the following factors are affecting DTU?

Volume of data in the DB
Volume of data that we are fetching;
Type of joins?
Concurrent users/Hits

Azure Pricing Table:

As I understand it, the concept of DTUs is deliberately a bit vague. From the same page the graphic in the question came from:
SQL Database service tiers

Understanding DTUs
The Database Throughput Unit (DTU) is the unit of measure in SQL Database that represents the relative power of a database to process transactions given the constraints of the hardware driving the database. DTU is a concept similar to horsepower in a car. It describes the relative performance of one database to another. Just like with horsepower in a car, the more DTU a database has, the more power it has. Horsepower is a function of many different factors: weight, engine capacity, turbo, exhaust system. Similarly, DTU is a function of the amount and type of memory, compute, and disk the database can consume. You can read the details of how we determine DTU in the Benchmark Overview).

(emphasis added)
There are more details about how to monitor your usage and decide on an appropriate tier in the links above, the rest of the official documentation, and the Azure SQL Database blog.


Reviewing all new MySql queries on staging environment The problem I am trying to solve: It's well known that not all developers know what is EXPLAIN in MySql, and not every knows how to use it. Large part of them consider MySql as a black box, no matter what is the query I write, I expect maximal response time and right result. I think you recognize this situation. Of course more experienced developers should educate them, but I am looking for best practices how to prevent the situation when bad, stucking query goes over QA team, and takes down production environment.

Approaches to solution: we might have few approaches, the first one, do review of the code of the next deployment. It's not to convenient, might take a lot of time, but very effective. The second one, try to identify slow queries on staging environment by looking at slow_logs, this approach is very easy, but not to efficient, data on staging is not the same data as data on production, therefore slow  query on production can be completely good query on staging.

What's your opinion, how to prevent bad written, slow query to go to production environment?

These are some tools suggested by MySQL Performance blog post:
http://www.mysqlperformanceblog.com/tools/
pt-query-digest can be useful to review queries as well
It can review a slow log, saving results to the test.query_review table in a MySQL server running on host1. See --review for more on reviewing queries:

pt-query-digest --review h=host1,D=test,t=query_review /path/to/slow.log
Using Show Profiles also you can review query performance

http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html

SET PROFILING = 1;
SET profiling_history_size = 0;
SET profiling_history_size = 15;
<Your own query>;
SHOW PROFILES;
select state, round(sum(duration),5) as `duration (summed) in sec` from information_schema.profiling where query_id = 11 group by state order by `duration (summed) in sec` desc;
SET PROFILING = 0;

EXPLAIN EXTENDED [Your own query];

When EXPLAIN is used with the EXTENDED keyword, the output includes a filtered column not otherwise displayed. This column indicates the estimated percentage of table rows that will be filtered by the table condition. In addition, the statement produces extra information that can be viewed by issuing a SHOW WARNINGS statement following the EXPLAIN statement. The Message value in SHOW WARNINGS output displays how the optimizer qualifies table and column names in the SELECT statement, what the SELECT looks like after the application of rewriting and optimization rules, and possibly other notes about the optimization process.


Algorithmic order of table indexes for table operations I have not been able to find a clear indication of the impact table indexes have on the algorithmic order of table operations.

I am not interested in the nuts & bolts of specific implementations; I assume RDMS designers know what they are doing and they have made these as efficient as possible.

I am going to limit my discussion to a single index, I think that additional indexes simply add an additional dimension (i.e. the fundamental process has to be performed more than once).

The following assumes a single record in each case - the benefits of indexes are greatly enhanced for multiple record operations because (generally) the seek operations need to be done less times than the number of records being sought as they can be retrieved in a range.

For an unindexed table I believe the operations are:

Step                INSERT     SELECT     DELETE     UPDATE
Find the record      N/A        O(n)       O(n)       O(n)
Modify the record    O(1)       N/A        O(1)       O(1)
OVERALL              O(1)       O(n)       O(n)       O(n)

This assumes that finding the record requires a table scan but a new record is simply slammed onto the end.

To set up an index is an O(nlog(n)) operation based on an efficient sorting algorithm.

For an indexed table I believe the operations are:

Step                INSERT     SELECT     DELETE     UPDATE
Find the record      N/A      O(log(n))  O(log(n))  O(log(n))
Modify the record    O(1)       N/A        O(1)       O(1)
Update the index   O(log(n))    N/A        O(1)       O(1)
OVERALL            O(log(n))  O(log(n))  O(log(n))  O(log(n))

This assumes that finding the record is now an ordered lookup operation on the index and updating the index is a single step for DELETE and UPDATE (because you have already found the record) and an ordered lookup for INSERT

That is, inserts get worse but everything else gets better.

Is this correct?

Things are more complicated than that. Here are a few points of consideration.

First, this entire discussion assumes B-Trees or B+ Trees (Hence the o(log(n))). There are other types of indexes, like hash indexes, where access is in O(1). Your question insinuates you're looking up values using "equals" search (e.g. looking for X=17). But in this particular scenario, a Hash index is preferable, when possible.

I do agree though that most indexes you'll find today are B/B+ Trees, so let's continue with this assumption.

You've also implicitly indicated that there's always only one resulting row in a SELECT, which is hardly a representative case; plenty times do we look for 1,000 rows at a time. But let's continue with the assumption of a single matching row.

Your next assumption is that searches are always done by the indexed column. This is fine, but I'm just noting that DELETEing a record by some unindexed column Y turns out to be more expensive: you're both wasting O(n) time in finding the record, and then paying an additional O(log(n)) for updating the index.

But let us continue with the assumption that we're only discussing queries which are looking up at indexed columns.

Some tables use unclustered index format (which fits into your calculations) - the table is one entity and the index is another. Others use clustered index format: table rows (or rather blocks of rows, or rather yet pages of rows) are actually stored as leaves inside the clustering index. In such scenario, you will pay O(log(n)) for finding a record in an INSERT command. An optimization for that is in the case you're inserting to the end of the table, and a decent implementation would hold a pointer to the last record/page in the index. (Oh, yes, you should not the possibility that your record gets INSERTed to the middle of the table).

Actually, records could get inserted to the middle of the table even in unclustered tables; it would make sense to spend more search time so as to avoid fragmentation, and at least one implementation that I know of does that. I'm assuming other may, too.

Deleting/Inserting an index from a BTree is O(1) on average, but may cost up to log(n) operations in case of propagated page merging/splitting.

Also, the fact that always comes as a surprise to many, is that sometimes a table scan is faster than index lookup. This is particularly true for queries resulting in multiple rows. It turns out looking up the index adds overhead; when compared to full table scan the overhead could actually make total cost higher. For single row lookup the vast majority of index lookups should be faster than a full table scan.

But do consider the following general convention: you pay with dollars any action that accesses disk. You pay with nickels actions that act on in memory data. This is actually at the heart of database disk I/O optimization. If index pages are on disk, and table pages happen to be in memory, you will possibly pay less for table scan.

And that's where havoc comes in: it really all depends on your workloads, on your memory size, on your dataset size.

Did you ever take a math class where you had to solve some complex integral? It took you hours to solve it, and got point off for missing some tiny minus sign somewhere?

Did you even take a physics class where you had to solve some complex integral? The professor would just throw away chunks of the equation, saying "this is neglect-able", and you would rip your hair off? WHY is it neglect-able? Why not other things?

Computer science is based on math. Computers are based on physics. They are physical objects. They need to spin disks, access a memory bus, manage billions of transistors... You just can't actually anticipate what will happen and put it all under some equation.

It may just turn out that for some particular dataset your entire equation does not hold water. In other times, it may be just fine.


Transactional Replication Synchronization taking too long to respond I successfully generated the snapshot, but subscription is not working correctly. Either the synchronization takes too long to respond or it simply displays the text shown in below attached screenshot.

Here is the successful snapshot generation:

It is only retrying and not giving the end result.

Check the following things please:

Snapshot Agent shows as completed successfully and it has generated its files
Log reader agent is running and delivering

Right click on each service -> view details - that will help you get more info.

To add to the comments, check the subscription details:

Go to each tab and see if there's any error. If all is good, they should all show green, running, with an action list of delivering transactions.


Unable to connect to an AWS RDS read replica of MySQL I'm not able to connect to an AWS RDS MySQL read replica.
$ mysql -h my-db-read-replica.qprdbd0pavr5.us-east-1.rds.amazonaws.com -umyname
ERROR 2003 (HY000): Can't connect to MySQL server on 'my-db-read-replica.qprdbd0pavr5.us-east-1.rds.amazonaws.com' (60)

Here're the configurations I've checked:

The security group allows me to connect from my local computer to the remote RDS.

The Public accessibility  of the read replica is Yes

I can use nmap to detect that port 3306 on the RDS is open.

$ nmap -Pn -p 3306 my-db-read-replica.qprdbd0pavr5.us-east-1.rds.amazonaws.com
Host discovery disabled (-Pn). All addresses will be marked 'up' and scan times will be slower.
Starting Nmap 7.91 ( https://nmap.org ) at 2021-05-18 17:12 CST
Nmap scan report for my-db-read-replica.qprdbd0pavr5.us-east-1.rds.amazonaws.com (3.208.xxx.xxx)
Host is up.
rDNS record for 3.208.xxx.xxx: ec2-3-208-xxx-xxx.compute-1.amazonaws.com

PORT     STATE    SERVICE
3306/tcp filtered mysql

Nmap done: 1 IP address (1 host up) scanned in 6.46 seconds

I've also rebooted the read replica, but I still can't connect to it.
The connection worked normally yesterday.
What could be the reason for failure of the connection?

The size of my MySQL writer instance is db.t3.medium, and that of my read replica was db.t3.small.
After upgrading my read replica to db.t3.medium, I'm able to connect to the read replica.
I don't know how to explain this phenomenon.
This page says

It's a best practice that all instances in a cluster are the same size to avoid any workload overload on the reader instances.

But my read replica's workload is not high.


Can underscore be forced as a word splitter without a full-text parser plugin? Is there any way in MySQL to force underscore to become a word splitter character (like space or dash) without having to use a fulltext parser plugin?

If not, does such a plugin already exist or do I have to learn to make my own?

I have never heard of such a plugin. Sure, you could make your own plugin.

However, I have a rather wild suggestion if you can afford the diskspace.

Let's take an example table:

CREATE TABLE mydb.mytable
(
    id int not null auto_increment,
    txt text,
    primary key (id),
    fulltext txt (txt)
) ENGINE=MyISAM;

Let's say a table like this is populated already.

Suppose you want to index any words such as

abc_def_ghi
innodb_file_per_table
key_buffer_size

You may want to create a separate column with the underscores replaced with blanks. You will have to perform the following conversion

CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
ALTER TABLE mydb.mytable_new ADD txtsrch TEXT;
ALTER TABLE mydb.mytable_new ADD FULLTEXT txt_txtsrtch (txt,txtsrch);
ALTER TABLE mydb.mytable_new DROP INDEX txt;
INSERT INTO mydb.mytable_new (id,txt,txtsrch)
SELECT id,txt,REPLACE(txt,'_',' ') FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytable_old;
ALTER TABLE mydb.mytable_new RENAME mydb.mytable;

Once done, the table should look like this

CREATE TABLE mydb.mytable
(
    id int not null auto_increment,
    txt text,
    txsrch text,
    primary key (id),
    fulltext txtsrch (txt,txtsrch)
) ENGINE=MyISAM;

This will index the data both ways. Consequently, this blows up the table and index files in size.

You will have to do searching with the following clause

MATCH(txt,txtsrch) AGAINST ...

Please perform this on a test server, look at the increased size of the new table and index.  You can decide whether this table layout provides the data you want with tokens separated.

Give it a Try !!!


Kafka Partitions vs Mongo Sharding which one is better throughput I am using Mongo Sharding to register page views on my website. We have hashed shard key to evenly distribute data in multiple shards. Then our aggregation queries run over time range at interval to aggregate this data and provide trends on site.
We came across Kafka for write distribution for heavy load and this kind of streaming.
I compared both systems and both provide distribution on partitions in a topic with leader follower approach.
Kafka does it using multiple partition on different brokers with partition replication and Mongo does it with multiple shards which have replica sets.
As aggregation query will always be on time range than it will go to multiple shards/ partitions always.
My question is how we can compare which will provide better through put and run time scalability in case of heavy load as i understand both use same mechanism adding new partitions in case of Kafka or adding new shards in case of Mongo.
Please provide suggestions.

My question is how we can compare which will provide better through
  put and run time scalability in case of heavy load as i understand
  both use same mechanism adding new partitions in case of Kafka or
  adding new shards in case of Mongo.

Definitely each and every technology they have their own pros and cons. In case on MongoDB and Kafka, it's depends on your types of data like.

MongoDB does do data distribution and balancing by itself, but it is only for Document data.

Streaming like Kafka or MapR Streams can handle streaming data on HDFS/MapRFS.

I am writing down some of pros and cons of MongoDB as well as Kafka as mention below. May be it will help out to you.

MongoDB

MongoDB stores data in JSON-like documents that can vary in structure, offering a dynamic, flexible schema. MongoDB was also designed for high availability and scalability, with built-in replication and auto-sharding.

Pros

Document-oriented storage
No sql
Ease of use
Fast
High performance
Free
Open source
Flexible
Replication & high availability
Easy to maintain

Cons

Very slowly for connected models that require joins

Kafka

Kafka is a distributed, partitioned, replicated commit log service. It provides the functionality of a messaging system, but with a unique design.

Pros

High-throughput
Distributed
Scalable
High-Performance
Durable
Publish-Subscribe
Simple-to-use
Written in Scala and java. Runs on JVM
Open source
Message broker + Streaming system

Cons

Needs Zookeeper
Non-Java clients are second-class citizens

I would say, it all depends on your use cases.

If you only have one type of data, like Document data, and you do not need to process other data types, then yes, MongoDB alone is the best bet as of now.
However in modern big data environment, different data sources and data types may require different processing engines.

For further your ref here, here and here


How to find n consecutive numbers in MySql I have numbers starting from 1 to 50 and then 90 to 200 in my database and I want to fetch 100 consecutive numbers then the result should be 90 to 189.
Records in my table are more than 10 million.

Substitute $n with the desired number:

SELECT c1.id
FROM consecutive c1
WHERE $n = (SELECT count(*)
           FROM consecutive c2
           WHERE c2.id
           BETWEEN c1.id AND (c1.id + $n - 1)
           );

This does a full table scan for each number, combined with a range scan. It finds all possible combinations (returning the first figure in the chain, so add a LIMIT 1 for better performance if you just want to find the first result. Example:

mysql> CREATE TABLE consecutive (id int PRIMARY KEY);
mysql> INSERT INTO consecutive VALUES (1),(2),(3),(4),(6),(7),(8),(9),(10),(11);
mysql> SELECT c1.id FROM consecutive c1 WHERE 5 =
       (SELECT count(*) FROM consecutive c2
        WHERE c2.id BETWEEN c1.id AND (c1.id + 4)
       ) LIMIT 1;
+----+
| id |
+----+
|  6 |
+----+
1 row in set (0.00 sec)

It could probably be optimized to be done in a simple table scan with the help of variables, but this uses simpler and more standard syntax.


Quickly appending data to text-column I'm using a statement similar to the following to append data to a column of type mediumtext to a bunch of rows:

INSERT INTO myTable (myKey,myVal)
ON DUPLICATE KEY UPDATE myVal=CONCAT(myVal,VALUES(myVal))
VALUES (1,'foo'),(69,'bar'),(1337,'baz')

At first this is really fast. But the more data there already is the slower it gets. It seems that when appending data, the whole field is read, merged with the new bit and then inserted again.

This mysql bugreport brings up exactly this issue:
https://bugs.mysql.com/bug.php?id=47937

Is there any way of making this faster?

My guess is the answer is NO. You won't make this any faster.

A database (at least all the ones I've worked with) doesn't treat text as you would with a Java StringBuilder, but rather like a Java String, which will suffer exactly the same problem (as would strings in most programming languages, except, maybe the V8 implementation of JavaScript). I wouldn't consider that a bug. It's a design decision. A database is not normally used in this fashion.

A StringBuilder has extra room for extra text, which is appended at the end of the already used space. When it actually runs out of space, it allocates a big chunk for further filling. A String is immutable and does not have extra room to add new text "at the end".

Perhaps what you should do is not modify your original row, and have instead a secondary related table where you store every string associated with myKey, together win an string_order column (could be an auto_increment, or a current timestamp) and, when needed, retrieve everything together with a GROUP_CONCAT of them. Your related table would just work as a kind of log, where you would be inserting the new "events" in order, a piece at a time.


Escaping a single quote in SQL Server The recommendations I have come across advise that single quotes in SQL Server should be escaped using two single quotes. Thus Ravi's becomes Ravi''s.

Another alternative is to use parameters.

In most of the examples I have seen the words usually have only one quote. Most times it works but I have been unable to make it work while creating a database login.

CREATE LOGIN Ng''ang''a  WITH PASSWORD  'Ng''0ng''0'

Where I am going wrong?

The user name is an ID/QUOTED ID that should be enclosed in double quotes or square brackets while the password is a regular string where single quotes need to be doubled. The correct syntax would in other words be;

CREATE LOGIN "Ng'ang'a" WITH PASSWORD='Ng''0ng''0'

or

CREATE LOGIN [Ng'ang'a] WITH PASSWORD='Ng''0ng''0'


Effect of --flush-logs on a slave mysqldump? I have this database setup:

Master -> Slave A

I would like to accomplish the following:

Master -> Slave A -> Slave B

I have reviewed the documentation.

Master and Slave A are both in an MSP environment, while Slave B will be an RDS instance. I can connect to Slave A in the MSP just fine. Master binlog files are being copied over to the instance running Slave A. All binlog files are being kept around for a long time.

However, my database size is 1.3 TB. Thus, a mysqldump on Slave A takes several hours to complete, and even longer to load into my new empty Slave B before beginning replication. In the best case scenario, it takes ~36 hours from starting the dump to when replication can start.

I would like to use --flush-logs, as described in the third code snippet here, to reduce the chances of having to use CALL mysql.rds_skip_repl_error; repeatedly after I begin replication. This is especially important because there is currently no way to accomplish the equivalent of slave-skip-errors = 1062 in RDS, and also because I'm unsure what the effect is on data consistency.

Is my assumption correct, that using --flush-logs reduces the likelihood of duplication errors arising after replication has begun?

If it is correct, what would the effect be of using mysqldump --single-transaction --flush-logs on Slave A? Would it behave as a tunnel and actually flush the logs on Master, or would it only affect the logs present on Slave A?

Point to be considered whether your --flush logs ( Closes current binary log & opens new binary log with consecutive number ) will be beneficial or not :

One thing that you are using --single-transaction option (It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications). This means a snapshot of current database is taken for backup where data modification during this timestamp is not considered in backup & can be retrived from binary logs.

To check the write operation is performed during backup activity on database, you can verify by no of binary logs written during that time.

If write operation intensity is more during this time, then you can afford to start replication from last file written during backup completion.

To overcome this thing,

Measure no of binary logs written during backup time. ( Hope backup time is preferred where less write operation are performed on db).

If No of Binary logs are more during backup time :
Take before backup binary logs reading from master & can be used for starting replication ( Probably there may duplicate error, need to skip it) as data consistency is concerned.

mysql -uuser -pXXX -h192.168.XX.XX -e 'show master status' > before_rd.sql

time mysqldump  -uuser -pXXx -h192.168.XX.XX --force --all-databases --max_allowed_packet=360M --lock-tables=false --triggers --routines > backup_$dd.sql 2> bkp_mep_$dd.log

mysql -uuser -pXXX -h192.168.XX.XX -e 'show master status' > after_rd.sql

If No of Binary logs are less or none during backup time :
Take after backup binary logs reading from master & can be used for starting replication ( -- flush-logs can be used ) as data consistency is maintained.


Executing Shell (bash) from PL/SQL Does anyone know how to execute a shell script from a PL/SQL procedure or function? Let's say the shell script is '/usr/bin/bash/convert.sh'.

You need to create an external job for that.

Techniques for External Jobs

First, you need to create a credential, with the OS user and password in whose name the job will run:

begin
  dbms_scheduler.create_credential
  (
    credential_name => 'my_credential',
    username        => 'oracle',
    password        => 'Oracle123'
  );
end;
/

After that you can use this credential when creating an external job. For example calling your convert script with 2 arguments (input and output file)

begin
  dbms_scheduler.create_job
  (
    job_name             => 'convert_job',
    job_type             => 'executable',
    number_of_arguments  => 2,
    job_action           => '/usr/bin/bash/convert.sh',
    auto_drop            => true,
    credential_name      => 'my_credential'
  );

  dbms_scheduler.set_job_argument_value('convert_job', 1, '/home/oracle/file.in');
  dbms_scheduler.set_job_argument_value('convert_job', 2, '/home/oracle/file.out');

  dbms_scheduler.enable('convert_job');
end;
/


Online index maintenance in SQL Server using SNAPSHOT isolation level I have recently altered our SQL Server 2008 R2 database to enable SNAPSHOT isolation level and made appropriate changes to hibernate to run all transactions in SNAPSHOT mode. I have however noticed that when I add/delete/alter indexes, the queries which are accessing the underlying table are rolled back and I get this exception:

  Snapshot isolation transaction failed in database 'foo' because the
  object accessed by the statement has been modified by a DDL statement
  in another concurrent transaction since the start of this transaction.
  It is disallowed because the metadata is not versioned. A concurrent
  update to metadata can lead to inconsistency if mixed with snapshot
  isolation

...which is exactly what has happened.

According to https://msdn.microsoft.com/en-us/library/bb933783(v=sql.105).aspx "These statements are permitted when you are using snapshot isolation within implicit transactions." So I tried:

set implicit_transactions on
GO

DROP INDEX blabla
GO

IF @@TRANCOUNT > 0 COMMIT TRAN set implicit_transactions off

But the problem still persists. There must be a way to do this in SQL Server?

The problem is in your application layer. You are starting explicit snapshot transactions before doing DDL. You have to modify the app logic to be smarter about its intent. If about to execute DDL then you must either a) use read committed isolation transaction or b) don't start a transaction and let the DDL use an implicit transaction.

Looks like you used a carpet bombing approach and modified your app to use snapshot isolation everywhere. There is no pixie dust, you'll have to make sure you choose the correct isolation/transaction mode on a case by case basis, in your code.


Is LC_COLLATE different from COLLATE? Postgres 12 on Windows 7
This works:
CREATE DATABASE "foo"
    ENCODING 'UTF8'
    LC_COLLATE = 'en-US'
    LC_CTYPE = 'en-US'
    TEMPLATE template0

Afterwards, running SHOW LC_COLLATE with database foo selected reports:

But when running
SELECT UPPER('kedi' COLLATE "en-US")

it reports:
ERROR: collation "en-US" for encoding "UTF8" does not exist

Shouldn't both accept the same collation names?

LC_COLLATE refers to a name of locale from the operating system, whereas COLLATE refers to a collation that should exist in pg_catalog.pg_collation.
pg_catalog.pg_collation is originally populated in the template databases when the PostgreSQL instance is created (by initdb).
Specifically it's the SQL function pg_import_system_collations() that should do that. From the doc (emphasis mine)

pg_import_system_collations ( schema regnamespace ) → integer

Adds collations to the system catalog pg_collation based on all the
locales it finds in the operating system. This is what initdb uses;
see Section 23.2.2 for more details. If additional locales are
installed into the operating system later on, this function can be run
again to add collations for the new locales. Locales that match
existing entries in pg_collation will be skipped. (But collation
objects based on locales that are no longer present in the operating
system are not removed by this function.) The schema parameter would
typically be pg_catalog, but that is not a requirement; the collations
could be installed into some other schema as well. The function
returns the number of new collation objects it created.

For some reason you don't have en-US in pg_collation while it's valid from your operating system, so you seem to be in the case where the advice is to run
pg_import_system_collations() manually in the databases that need it.


sp_dboption is not available in SQL Server 2012 The system stored procedure sp_dboption is not available in SQL Server 2012 anymore. How could it be replaced?

sp_dboption was deprecated a while ago - http://msdn.microsoft.com/en-us/library/ms187310(v=sql.105).aspx

The recommended alternative is ALTER DATABASE - http://msdn.microsoft.com/en-us/library/ms174269.aspx


where in clause order issue I'll admit, I'm an average query coder so I'm pretty sure my issue is "operator error"

I have 2 db tables.  The columns are in ()

table1 (form_id, form_name, field_order)
table2 (field_id, field_name)

Table1 contains all my forms.  The field_order column contains the ID's for all the field_names in table2.  So the field_order column in table 1 looks like (682,1,2,341,72,72,73)

These numbers correspond to the id's for the fields in table 2.

Where my problem is, is that in my query using the IN clause, the form fields results are listed in ASC order by field_id in table2.  I need the field_id and field_name to be listed in the field_order in table1.

Any help is appreciated.  I think I'm missing something simple.

My code is below.

<?php
$sqlForms = "select t1.form_id, t1.form_name, t1.field_ids from table1 as t1 where t1.form_id != '10' order by form_name asc";

$resultsSQL = $this->EE->db->query($sqlForms);
    if ($resultsSQL->num_rows() > 0)
    {
        echo "
";

        foreach($resultsSQL->result() as $row)
        {
        echo "
";
        echo "".$row->form_id."	".$row->form_name."
";

        $formID = str_replace("|", ",", "$row->field_ids");

        $sqlFields = "select t2.field_id, t2.field_name
                        from table2 as t2
                        where t2.field_id IN (".$formID.")";

            $resultsFields = $this->EE->db->query($sqlFields);
                if ($resultsFields->num_rows() > 0)
                {
                    echo "
";
                        foreach($resultsFields->result() as $rowFields)
                        {
                            echo "
".$rowFields->field_id."	".$rowFields->field_name."
";
                        }
                    echo "
";
                }

        echo "	";
        echo "
";
        }
        echo "
";
    }
?>

I'm an idiot!   All I needed to do was add ORDER BY FIELD(id,5,2,6,8,12,1...)
to my code.  I like it when the answer is an easy one.  I just wish it wouldn't take me so long to figure out!


How to determine if the SQL Server running is Express version? is there a way to check whether the SQL Server I am connected is Express version?

How can I find how much space is left until the 10GB limit of the SQL Server Express?

Thank you!

You can execute SELECT @@VERSION. Result will describe SQL Server version and edition. Or SELECT SERVERPROPERTY('Edition') will result SQL Server edition.

SQL Server Express Edition supports maximum database size 10GB per database. So you can monitor database size using following query.

SELECT      sys.databases.name,
            CONVERT(VARCHAR,SUM(size)*8/1024)+' MB' AS [Total disk space]
FROM        sys.databases
JOIN        sys.master_files
ON          sys.databases.database_id=sys.master_files.database_id
GROUP BY    sys.databases.name
ORDER BY    (SUM(size)*8/1024) DESC

Thanks!


What's a good (free) client for Informix on Windows? I'm looking for a GUI client for Informix on Windows (mainly to view data and execute queries), free would obviously be nice but I know Informix is used mostly in corporate environments and free may not be available. Perhaps the best option is set up ODBC and use any one of many clients available?

I would suggest SQuirreL (free) but Informix doesn't appear to be supported

A Google search shows some older pages though so YMMV


PostgreSQL logical replication using pglogical I am testing logical replication using Pglogical with Postgresql v9.5.

Simple configuration seems OK.
It means:

create pglogical extension in the two nodes
create a set and add tables from public schema into this set,
create a node and create a subscription in the other server.

But, subscription to a new replication_set containing tables from a specific schema does not work.

This command passed (in the provider node):

select pglogical.replication_set_add_all_tables(
set_name := 'new_replication_set',
schema_names := '{myschema}',
synchronize_data := 'true'
);

This command passed (in the subscriber node):

select pglogical.create_subscription(
subscription_name := 'subscription',
replication_sets := array['new_replication_set'],
provider_dsn := 'host=10.20.30.40 port=6432 dbname=production user=pglogical_prod',
synchronize_data := 'true'
);

But tables in the subscriber node are empty.

I missed something?
Thanks and regards.

Additional information:

select pglogical.alter_subscription_synchronize(...) and select pglogical.alter_subscription_resynchronize_table(...) commands were sent,
Tables in the subscriber node are in a specific schema too,
select * from pglogical.queue; request is not empty.

Hereunder the log messages taken from the subscriber node :

I set log_min_message = debug5 in the subscriber node.

And hereunder the error message :

792 < 2018-03-14 11:38:56.449 CET >LOG:  starting apply for subscription subscription
793 < 2018-03-14 11:38:56.449 CET >DEBUG:  StartTransaction
794 < 2018-03-14 11:38:56.449 CET >DEBUG:  name: unnamed; blockState:       DEFAULT; state: INPROGR, xid/subid/cid: 0/1    /0, nestlvl: 1, children:
795 < 2018-03-14 11:38:56.449 CET >DEBUG:  CommitTransaction
796 < 2018-03-14 11:38:56.449 CET >DEBUG:  name: unnamed; blockState:       STARTED; state: INPROGR, xid/subid/cid: 0/1    /0, nestlvl: 1, children:
797 < 2018-03-14 11:38:56.449 CET >ERROR:  subscriber subscription initialization failed during nonrecoverable step (d)    , please try the setup again
798 < 2018-03-14 11:38:56.449 CET >DEBUG:  shmem_exit(1): 2 before_shmem_exit callbacks to make
799 < 2018-03-14 11:38:56.449 CET >LOG:  apply worker [16870] at slot 1 generation 4 exiting with error
800 < 2018-03-14 11:38:56.449 CET >DEBUG:  shmem_exit(1): 6 on_shmem_exit callbacks to make
801 < 2018-03-14 11:38:56.449 CET >DEBUG:  proc_exit(1): 2 callbacks to make
802 < 2018-03-14 11:38:56.449 CET >DEBUG:  exit(1)
803 < 2018-03-14 11:38:56.449 CET >DEBUG:  shmem_exit(-1): 0 before_shmem_exit callbacks to make
804 < 2018-03-14 11:38:56.449 CET >DEBUG:  shmem_exit(-1): 0 on_shmem_exit callbacks to make
805 < 2018-03-14 11:38:56.449 CET >DEBUG:  proc_exit(-1): 0 callbacks to make
806 < 2018-03-14 11:38:56.450 CET >DEBUG:  reaping dead processes
807 < 2018-03-14 11:38:56.450 CET >LOG:  worker process: pglogical apply 16385:2875150205 (PID 16870) exited with exit     code 1

Thanks and Regards.

Tables and schema names in the subscriber node have to be the same as the provider node.
Hereunder the error messages if the names are differents between the two nodes:

>LOG:  starting apply for subscription subscription
>INFO:  initializing subscriber subscription
>INFO:  synchronizing data
>ERROR:  schema "myschema" does not exist

>LOG:  starting apply for subscription subscription
>INFO:  initializing subscriber subscription
>INFO:  synchronizing data
>ERROR:  relation "myschema.tblprod2" does not exist

Now replication working.


Oracle Enterprise Manager - stats split by user (schema) Is there any way to split all the stats by user (schema) when looking at the Performance tab in the Oracle 11g Enterprise Manager?

or effectively some other tool to retrieve that information - e.g. a running chart of #active sessions for user (that's probably easy by probing the v$session or similar). Or a running chart of I/O Megabytes per Second broken by user/schema.

You should be able to find what you're looking for by clicking the "Top Activity" link in the bottom right.  When you get to the Top Activity page, try changing the drop down menu on the right side from "Top Sessions" to "Top Clients." You can then click on an individual user session to get more details about that session.


Getting TNS Name from connection string I normally connect to my Oracle database at work using a connection string. Other then the obvious answer of asking the DBA what it is, using SQL Developer or some other tool can I find out what the tns listener name is. This is an excerpt from a config file in .net where i use to connect:

  DATA SOURCE=myURL:port/servicename;PASSWORD=password

The instance's TNS name would be defined in the tnsnames.ora file on your system. When you bust that file open, you'll see something like...

MyOracleInstanceTNSName =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.0)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORA12)
 )
)

The instance name (defined in the file) is really arbitrary; meaning, you can name it whatever you want. So, in the example above, I named it MyOracleInstanceTNSName, but I could've named it something else.

In the professional world, we typically use the same name across hosts, but that's a convention. This way as we share scripts or talk about instances, we're using the same name. That's a while lot easier than remembering that Bob's "foobar" instance is the same as your "bigSexy" instance.

You can find this file in ${ORACLE_HOME}/network/admin on most systems. You should be able to find (or define) the tnsname for the instance you're using there.


Slow access to table in postgresql despite vacuum I'm running PG 8.4

I had a table with about 20K records. Any query accessing this table with more than 1 record (e.g. joins) would be very very slow. Even a count would take like 20 seconds.

Stats showed 5 million live_tuples.

I tried vacuum, vacuum analyze, and in the end, vacuum full. Nothing changes in terms of speed. And stats still showed the same thing.

I ended up creating a new table, insert all records in the new table and drop/rename the old one.

The new table now runs lightning fast.

Any idea of:

1) What would have been the more correct handling? I'm looking for a better way to do this as one of the side effect is that the views which were pointing to the table got modified by the "alter table rename to" statement and unbeknownst to me, the views were pointing to the old table for 2 days!

2) Why would that happen in the first place?

1)  I would have tried cluster followed by analyze.  My only hesitation is I am not 100% sure what happened here.  Is it possible there was some index corruption as well?  Reindex might have helped? Given that the stats entries were way off, is it possible something was corrupt elsewhere regarding the relation's OID?

2)  I have no idea.  I have never seen anything like that before.  I would need a lot more diagnostic information even to hazard a guess.  If it happens again it would be worth knowing the physical size of the table, for example.

Edit:  Remembering that 8.4 still had max_fsm_pages, if that was set too low between vacuums that could cause this sort of table bloat.

Edit2:  Vacuum full, however should have been able to handle bloat due to max_fsm_pages, at least it does in my experience, suggesting again to me that something deeper was wrong.


Will improper bin-log purging leads to deletion of mysql database We had  master to master and master to multiple slave environment in one of our client location. In one of the slave Db host we had disk space issue with bin-logs. At first i deleted the bin-log files manually while the mysql server is running using rm -f binlog.000015, binlog.000016.......binlog.000026. But later i came to know this procedure is wrong by issuing the commmand :

mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| 0             |           |
| 0             |           |
| 0             |           |
| binlog.000027 |    724935 |
| binlog.000028 |    733481 |
+---------------+-----------+

What ever the bin logs deleted using rm -rf was not getting cleared in slave. Later i issued the command : PURGE BINARY LOGS TO 'mysql-bin.000026'; and could see the bin logs got cleared properly.

mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000027 |    724935 |
| binlog.000028 |    733481 |
+---------------+-----------+

But this didnt affected the slave replication and replication is fine. But after some time i could noticed that missing of mysql database. What could be root cause. Whether mysql dropped due to mis handling of the bin-logs.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| client_prod        |
+--------------------+
2 rows in set (0.01 sec)

====

As the other slave is fine.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| client_prod        |
+--------------------+
3 rows in set (0.00 sec)

We do not see anything indicative of the folder being removed or the database dropped in logs or history. What would be the mystery behind. How could be recover apart from taking dump and restoring, can we take dump of only mysql and restore it in this slave. If doing so will it leads to any replication problem.

If your slave does not also act as a master to another slave, then you should not have any issues from deleting binary logs. The relay logs are important to the slave.

You need to further investigate on why mysql schema disappeared. Is mysql schema still present on the disk, even though MySQL is not showing it? If you have not stopped the slave instance yet, then you could run under same user as MySQL is running:

lsof | grep '/path_to_mysql'

You might see mysql schema tables marked as deleted in there:

(deleted)

Another possibility is that you are connecting with a user that has limited privileges and just does not see mysql schema. Run SHOW GRANTS; to see what privileges you currently have.

As Rolando pointed out, use PURGE BINARY LOGS as best practice for cleaning up binary logs. If MySQL is down, you could delete the files manually, but then you have to also delete the same file names from the index file. Be careful on master servers, as binary logs might still be needed by slaves.

And if you do need to copy mysql schema, you can do it with just these steps on the slave. Although, I would recommend executing FLUSH TABLES; on master before doing these steps.

/etc/init.d/mysql stop
scp -rp master_server:/var/lib/mysql/mysql /var/lib/mysql/
/etc/init.d/mysql start


What value should I choose as length for timestamp without timezone in PostgreSQL? I'm currently setting up a database to populate it with big data. All the data I get comes from Valve's Steam Web API. Therefore, as all timestamps are returned in Unix timestamps, I cannot directly derive the correct timezone - which is also not of any interest as it's to fine-grained for what it is intended for. However, PostgreSQL enables the "length"-value field when setting the column up for timestamp without timezone but I don't know which value is meaningful to enter here and I also couldn't find any information about this value - neither in the official documentation nor on StackExchange so far.
As I'm someone who doesn't set up databases all the time I'm a bit confused and would love to get some assistance. Thanks in advance for your suggestions and input.

It's not the "length", it's the precision of the timestamp, so it controls the fractional seconds (milliseconds, microseconds) you can store.
e.g. '2020-12-14 23:14:10.123456'::timestamp(0) returns 2020-12-14 23:14:10 and '2020-12-14 23:14:10.123456'::timestamp(3) returns 2020-12-14 23:14:10.123
As you get a unix epoch which has no fractional seconds, timestamp(0) should be OK.
But in the end it doesn't matter, as changing the precision doesn't change the storage requirements. It's always 8 byte regardless of the precision you choose.


Copy database from one mysql sever to another mysql server Following are the details of the database and server:

Server1:
Host Name: abc
Port Num: 01
database: test

Server2:
Host Name: xyz
Port Num 02
database: spareparts
userid: spts
password: abcdefgh@123

I have to copy all tables from database test to database spareparts.

Please guide me how I can copy them. I am new in using mysql. Also please let me know if any other details are required.

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<outputfile.sql.

You can also use following method
  - stop server at source
  - stop server at destination
  - copy all the database folders from source to destination
  - start destination server
  - start source server

Hope it helps


What is the load impact of the Zabbix DB size query? I am in the process of implementing Zabbix monitoring on multiple servers at an organization.  I have recently run into some resistance from an Oracle / MySQL DBA.  He claims that the following query is resource intensive because his database server contains thousands of tables.  It's a one liner, but I've split it across multiple lines to help with readability:

echo "select sum($(case "$3" in both|"") echo
"data_length+index_length";; data|index)
echo "$3_length";; free) echo "data_free";; esac)) from
information_schema.tables$([[ "$1" = "all" || ! "$1" ]] ||
echo " where table_schema=\"$1\"")$([[ "$2" = "all" || ! "$2" ]] ||
echo "and table_name=\"$2\"");" | HOME=/var/lib/zabbix mysql -N

which looks like it reduces to:

select sum(data_length+index_length) from information_schema.tables;

My specific question is, how resource intensive is this query and should the organization be worried about the performance impact it will have on the DB server if it runs once per minute?

As an experienced UNIX/Linux sysadmin and from reading a little bit from the MySQL manual, it seems like it should be simple addition of values retrieved from the already in memory "information_schema", but I'm hoping for an answer that can definitively describe the internal actions MySQL has to take and the associated impact.

This 'random person on the Internet' can shed some light...

The information_schema is not a 'real' database (until MySQL 8.0), rather it is derived from a bunch of data that may or may not be in RAM already.
If not all the data is in memory, then the query will have to get it from disk -- by reading lots of .frm files (etc).  "Thousands" in your case.  So, this could be quite costly.
The Data_length, etc are relatively stable; I hope your monitoring is not doing this query every minute; that may be a noticeable burden.  Once an hour should be sufficient.
What is the value of table_open_cache?  Is it more than the number of tables?  If not, then this random person on the Internet thinks that you could be in deep .
How lame!  Rather than generating a different query when you ask for "all", the package builds a complex boolean expression.

I have been involved in the monitoring of hundreds of machines.  A simple df is sufficient to alert a human to dig into what is causing the problem.  Checking the MySQL directory with a du is one of several secondary steps.

But, I disagree with every monitor I have seen.  I don't care how big the data is.  It does not matter if the disk is 90% full -- as long as it is not growing.  I do care if it is 20% full but growing so fast that it will fill up before the end of the week.

Sure graphing the size is handy, but that means that a human has to look at it (and not fall asleep).  I want an alert.  Furthermore, due to cyclical things, such as binlog purging, the graph may look like saw teeth -- up, down, up down!  This makes "trend analysis" difficult.

Back to your question of whether that query is "intensive".  If Zabbix is worth having it will give you the answer on a silver (or electronic) platter!  Does it monitor the "slowlog"?  If so, does this query show up?  QED.


Restore Verify Only w/ Transaction Log Backups Question
When RESTORE VERIFY ONLY on a transaction log backup fails, what do you do?

Background
After FULL and DIFFERENTIAL backups, I run RESTORE VERIFY ONLY on the backup file(s). This rarely ever fails. But when it does, I simply re-run the backup, and re-run RESTORE VERIFY ONLY.  Currently, I don't verify transaction log backups. It's an oversight on my part that I'm looking to correct.  When I get to that point, I have to be prepared for all outcomes.

Possible Scenario
Let's say I have a full backup with trx log backups performed periodically afterwards.  If there are 10 trx log backups, but #7 failed a RESTORE VERIFY ONLY, the best I could do in a DR situation is to restore the FULL backup and six subsequent trx log backups.  (I write this based on my understanding that when RESTORE VERIFY ONLY fails, RESTORE DATABASE...FROM <7th Trx Log Backup> will fail too.)

So, even though the trx log chain is not technically broken, it's "effectively" broken since I can't restore the 7th trx log backup (or any subsequent trx log backup).

If I had taken a DIFFERENTIAL backup immediately after #7 failed, in a DR situation I could:

Restore the FULL backup
Restore the DIFFERENTIAL backup
Restore transaction log backups 8, 9, and 10

But this brings up a question about how to proceed when RESTORE VERIFY ONLY fails.Let's say a trx log backup completes successfully, but RESTORE VERIFY ONLY fails

Restore verifyonly(as per BOL)Verifies the backup but does not restore it, and checks to see that the backup set is complete and the entire backup is readable. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. So even if backup set comes as verified clean by Verifyonly its not 100% guaranteed that backup set is consistent only a successful restore of a backup can guarantee that backup set is valid.

I have not see an scenario where verifyonly fails but restores succeeds(unless you succeed with using continue_after_error) it can only happen when verifyonly failed saying there is not enough space to check backup consistency because verifyonly also checks that enough space is there to restore the database

Checks performed by RESTORE VERIFYONLY include:

•That the backup set is complete and all volumes are readable.

•Some header fields of database pages, such as the page ID (as if it were about to write the data).

•Checksum (if present on the media).

•Checking for sufficient space on destination devices.

Please show me the message which you got when verifyonly failed

After Verifyonly fails

I would run a checkdb on my database to check for any inconsistency but point here to note is checkdb does not do consistency check for log file only when recovery on snapshot is run during checkdb log file is used so we 'can' say that it checks for log file as well but not complete like it does for data file. If checkdb comes out clean I would say my database is consistent.

  The backup spans LSNs X to Y. I can't simply take another trx log backup to "fix" the situation: the subsequent trx log backup would span a different range of LSNs (Y+1 to Z). At this point, the log chain is effectively broken, right?

Yes you are correct taking multiple backup does not fixes corruption in log backup but taking multiple log backups does not break log chain. Log files are inked internally with LSN number and taking multiple log backup does not breaks chain if log backups are restored in sequential manner

  My first thought is to immediately take a DIFFERENTIAL backup. What would you do?

It wont solve corruption issue in any way. Differential backups are means to lower the RTO and perform speedy recovery of database thats it.

EDIT AFTER USER UPDATED HIS QUESTION:

  the best I could do in a DR situation is to restore the FULL backup and six subsequent trx log backups

Yes you are correct.

  If I had taken a DIFFERENTIAL backup soon after #7 failed, in a DR situation I could:

Restore the FULL backup
Restore the DIFFERENTIAL backup
Restore transaction log backups 8, 9, and 10

Yes you can restore differential backup, after you cleared question with details I have updated the answer. You can restore differential backup and then restore log backups 8,9,10 as they are taken after diff backup


Which PgBackRest policy for differential backups in PostgreSQL I set up pgBackRest and PostgreSQL configuration in two different servers.

I set up with crontab one full backup a week and one differential backup a day.

0  1   *   *   0     pgbackrest --type=full --stanza=stanzaname backup
0  1   *   *   1-6   pgbackrest --type=diff --stanza=stanzaname backup

I set up retention as below to save disk usage:

repo1-retention-full=2
repo1-retention-diff=1
repo1-retention-archive-type=full

Is it possible to ensure PITR to cover up to 2 weeks with this retention configuration?

If yes, is there a benefit to set a differential retention more than 1?
Because keeping more differential backups could consume a lot of disk space.

Depending on your archive (WAL) retention policy, maybe.

Nightly diffs: Relying solely on your nightly differential, your PITR recovery window would only go as far as last Sunday's full backup. From their docs, "Differential Backup: pgBackRest copies only those database cluster files that have changed since the last full backup".

Let's say today it's Thursday Sept 19th, and that you want to recover up to last week's Tuesday (the 10th). You can't use your only differential, the one taken tonight (the 19th) at 1am, which only contains changes since the last full backup take on Sunday the 15th. If you restore your oldest full backup, the one taken on Sunday 8th, you have no differentials taken from Monday 9th until Sat 14th to apply to it.
WAL archives: So, can you use archived WAL files then, if you apply them after restoring the full backup taken on Sunday Sept 8th? Maybe - this is the part where I think the documentation is a bit unclear: "pgBackRest automatically removes archived WAL segments when expiring backups (the default expires WAL for full backups based on the repo1-retention-full option)".

I am not sure if they mean that last week's WALS are kept around or not; sounds like maybe yes, since the oldest full is not expired yet.. but the statement in brackets gives me some pause.
Regardless of #2, my recommendation would be to find a solution that doesn't rely solely on WAL archive retention. You may either configure a higher retention for your nightly diffs so you don't need to rely solely on WAL archives (ideal but heavy on disk usage), or only do a full backup every 2 weeks (I don't like this option myself, since I prefer the peace of mind of a weekly full)

Or you can do what we do, nightly incrementals: "Incremental backups cannot be expired independently — they are always expired with their related full or differential backup."

To achieve the same PITR window of 14 days we are doing:

Sat 1am full backups, retention 2
Nightly incrementals from Sun-Fri

We find the above to be a good balance for us: the nightly incrementals are relatively small and are quicker than a differential, especially as we get farther from the Saturday full.

Source: https://pgbackrest.org/user-guide.html (visited in 2019-09-19)


Open Database mysql.gz I want to open a 300 MB database downloaded to my computer. The database is called database-name.mysql.gz.

When I open the file called database-name.mysql.gz I get a file called database-name.mysql.gz.cpgz.

I have tried decompressing the GZ file using command line:

tar -xzf <your file>

not unzip unlike in the article since you're not using a .zip file which gave me an error: tar:

  Unrecognized archive format.

I have also tried

gunzip < database-name.mysql.gz | mysql -u USERNAME -p

but I don't really understand what USERNAME should be and the command returns

  gunzip: unknown compression format

How can I open the database and see its content?

The answer to another person's question about gz.cpgz file-types suggests that it may have been compressed twice. Other searches show people having problems opening them but this is down to software not expecting files to be zipped up twice in their cases.

You don't say what operating system you are on but, from what you've posted, I'll take a guess that you have access to the file command to help identify what type file you have.

file database-name.mysql.gz will show that you have a file that can be unzipped using gunzip as you've already done.

Now run file database-name.mysql.gz.cpgz to see what this file's type is. If it comes back as gzip file again then you can gunzip it again.

A MySQL backup is just a text file, so if it reports it as so then you're safe to check it out further. Since it's over 300MB I wouldn't recommend opening it in your text editor. Instead use the head command to check out the top few lines:
head <yourfile.sql>

If you're Windows user 7zip will be able to tell you if the gz.cpgz is an archive or not.


Find all dependencies on a primary key I have found out how to find all dependencies on a table.

However if I just wanted to know where this primary key is used, how would I do it?

I believe we use SQL Server 2005.

I assume you're looking for all foreign keys that are based on a given table (and it's primary key). If you were on SQL Server 2008+ you could use sp_fkeys.

EXEC sp_fkeys 'TableName'

As you're on 2005 you'll have to put together this data manually. Check this previous answer by Gishu, it should give you the script you're after.

https://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server


Where is the MySQL variable - innodb_flush_method? I would like to tweak the value of innodb_flush_method to find out its performance impact on a database server. That variable is listed when I run the command SHOW VARIABLES.

But I could not find it in the configuration file for the MySQL Server - my.cnf.
I'm using XAMPP 1.6.3 with PHP (5.2.3) and MySQL (5.0.45). Its MySQL configuration file path is /xampp/mysql/bin/my.cnf
I searched the variable in the file, but no luck.
Where can I find it? Am I just overlooking?

UPDATE
I upgraded to XAMPP 1.8.1. Its configuration file path is /xampp/mysql/bin/my.ini
Still could not find that variable.

The only way for you to have it is to add it to your my.cnf.

Before you can add this option, you must make sure the InnoDB storage engine is available.

Please run these commands

SHOW VARIABLES LIKE 'have_innodb';
SHOW ENGINES;

If you see this:

mysql> SHOW VARIABLES LIKE 'have_innodb';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_innodb   | DISABLED |
+---------------+----------+
1 row in set (0.01 sec)

mysql> SHOW ENGINES;
+------------+----------+----------------------------------------------------------------+
| Engine     | Support  | Comment                                                        |
+------------+----------+----------------------------------------------------------------+
| MyISAM     | DEFAULT  | Default engine as of MySQL 3.23 with great performance         |
| MEMORY     | YES      | Hash based, stored in memory, useful for temporary tables      |
| InnoDB     | NO       | Supports transactions, row-level locking, and foreign keys     |
| BerkeleyDB | NO       | Supports transactions and page-level locking                   |
| BLACKHOLE  | YES      | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE    | NO       | Example storage engine                                         |
| ARCHIVE    | YES      | Archive storage engine                                         |
| CSV        | YES      | CSV storage engine                                             |
| ndbcluster | DISABLED | Clustered, fault-tolerant, memory-based tables                 |
| FEDERATED  | YES      | Federated MySQL storage engine                                 |
| MRG_MYISAM | YES      | Collection of identical MyISAM tables                          |
| ISAM       | NO       | Obsolete storage engine                                        |
+------------+----------+----------------------------------------------------------------+
12 rows in set (0.00 sec)

mysql>

then you have skip-innodb in your my.cnf. You should comment it out and restart mysql

What you want to see is this:

mysql> SHOW VARIABLES LIKE 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | YES   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW ENGINES;
+------------+----------+----------------------------------------------------------------+
| Engine     | Support  | Comment                                                        |
+------------+----------+----------------------------------------------------------------+
| MyISAM     | DEFAULT  | Default engine as of MySQL 3.23 with great performance         |
| MEMORY     | YES      | Hash based, stored in memory, useful for temporary tables      |
| InnoDB     | YES      | Supports transactions, row-level locking, and foreign keys     |
| BerkeleyDB | NO       | Supports transactions and page-level locking                   |
| BLACKHOLE  | YES      | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE    | NO       | Example storage engine                                         |
| ARCHIVE    | YES      | Archive storage engine                                         |
| CSV        | YES      | CSV storage engine                                             |
| ndbcluster | DISABLED | Clustered, fault-tolerant, memory-based tables                 |
| FEDERATED  | YES      | Federated MySQL storage engine                                 |
| MRG_MYISAM | YES      | Collection of identical MyISAM tables                          |
| ISAM       | NO       | Obsolete storage engine                                        |
+------------+----------+----------------------------------------------------------------+
12 rows in set (0.00 sec)

mysql>

Add that option to the my.cnf

[mysqld]
innodb_flush_method=O_DIRECT

then run

service mysql restart

Afterwards, you should be able to see it if you run

mysql> show variables like 'innodb_flush_method';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| innodb_flush_method | O_DIRECT |
+---------------------+----------+
1 row in set (0.00 sec)

mysql>

UPDATE 2013-04-12 14:25 EDT

I just realized you are running the Windows version of MySQL. What was I thinking ?

As point by @yercube's comment, innodb_flush_method is not available for Windows. Here is the MySQL Documentation on it:

  Controls the system calls used to flush data to the InnoDB data files and log files, which can influence I/O throughput. This variable is relevant only for Unix and Linux systems. On Windows systems, the flush method is always async_unbuffered and cannot be changed.

If you need this performance enhancement, migrate your data to the Linux version of MySQL.


Dropped the Distibution database before disabling publishing and distribution, how to proceed? I'm setting up my first Distributor database on SQL Server 2012.  I successfully created one and then wanted to blow it away and perform the task again just for practice.

This was a brand new Distributor and nothing had been done with it yet.

In my naivete I ran the the following commands before disabling the publishing and distribution:

alter database distribution set offline;
drop database distribution;

Now when I try to disable it, I get the following error:

SQL Server could not disable publishing and distribution on '<server>'.
------------------------------
Database 'distribution' does not exist. Make sure that the name is entered correctly.
Changed database context to 'master'. (Microsoft SQL Server, Error: 911)

Is there a way I can fix this?

I Googled it -- http://www.sqlrepl.com/sql-server/accidentally-dropped-distribution-database/ -- try to follow his advice!


Can having a LIMIT on DELETE degrade performance dramatically? I have a database project in php and mysql.  For, hopefully redundant, data security I added a LIMIT 1 to the delete($uniqueID) php function we use.

Nobody cared because records really aren't supposed to be deleted often from this database.  However we recently got a massive jump in the response time for this server.  By massive I mean approximately four times worse than our previous worst heavy load performance.

The culprit seems to be a series of 100 individual DELETE statements from a small table, about 7 columns and 150 records.  This doesn't seem right.  Is it possible that this newly added LIMIT 1 is obliterating performance?

I don't think so, but the data logging I have suggest that it is.

No, at least not in this case.

The evidence pointing towards a DELETE ... LIMIT 1 was real, but not entirely accurate.  The real issue here was a PHP script that called hundreds of unnecessary queries in order to execute the suspected DELETE ... LIMIT 1 query.

Using MySQL's log all queried on a test environment lead to the solution to this problem.


DBCC PAGE equivalent in Postgres In SQL Server you could check specific index information using the DBCC PAGE command. Is there anything similar in postgres?

And if there isn't, how would you get the same info then? This is simply for research purposes.

This command returns this:

Page @0x00000004ED67A000

m_pageId = (1:299)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x4
m_objId (AllocUnitId.idObj) = 121   m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594045857792
Metadata: PartitionId = 72057594040614912                                Metadata: IndexId = 1
Metadata: ObjectId = 99             m_prevPage = (1:297)                m_nextPage = (1:300)
pminlen = 4008                      m_slotCnt = 2                       m_freeCnt = 70
m_freeData = 8118                   m_reservedCnt = 0                   m_lsn = (35:184:44)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                        DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 4011

You can

CREATE EXTENSION pageinspect;

Then you can use the function

SELECT * FROM bt_page_stats('indexname' , 42);

to get some information about block 42 of the index: the number of live and dead entries, the amount of free space and the pointers to the left and right sibling (btpo_prev and btpo_next) and the type (root, intermediate or leaf).


Change Azure Standard to Premium I have one application using SQL Server Standard and would like the change to Premium, but I need to understand about the DTU. The application reaches 100% in DTU every day.

Would talk know if I need to buy Premium with more or equal DTU in Premium or with less DTU for being Premium is enough?

For example, I have the Standard 1600 DTU (S9) and would change to Premium 1000 DTU (P6), would lose the performance this?

Thanks for attention.

Premium tiers all have SSD storage and are intended for IO intensive workloads. DTUs of Standard Tiers do not have the computation power of Premium tiers. Premium tiers have a dedicated hardware on Azure.

If the current workload reaches the DTU limit then throttling may be occurring. Those spikes in DTU may be related to missing indexes (indexes that need to be created) and to queries that need optimization. After creating missing indexes and optimizing some queries you may find DTU consumption is under control and you don't need to scale up the service tier of your database.

Make sure you enable Automatic Tuning to allow Azure create indexes needed on your database. Use Intelligent Insights to understand more if you need to scale up the tier of the database or not.


Unable to connect to localhost with mysql utilities I installed mysql utilities on my ubuntu server today, so I could use mysqldbcompare, but I just cannot connect to the mysql server with it (localhost). I created an entry into the .mylogin.cnf, using mysql_config_editor, as mentionned into the mysql utilities 1.6 documentation, and printed / tested it (with mysql), and it's ok. But if I try to run any mysql utilities command, it always fail with

ERROR: Access denied for user '****'@'localhost' (using password: YES)

(I put the stars myself instead of my user name).

I spent more than 2 hours searching the web, I have absolutely no clue of what's wrong... any idea ?

EDIT :
alright, let's do this step by step
First, my versions. Everything is on the same virtual machine : mysql server and client, and mysql utils.
My versions are :
MySQL server : Ver 14 Distrib 5.7.22
MySQL client : Ver 14 Distrib 5.7.22
MySQL utils : 1.6.1

First, using phpMyAdmin (which connects correctly to mysql), I create a user named userTest, with host % and password "bonjour!".
Then, I go to my host terminal, and type the following :
mysql_config_editor set --login-path=test --host=localhost --user=userTest --port=3306 --password
When asked, I enter the password "bonjour!"
Then, I try to connect to mysql using the login path (no user is named "test" on the database :
mysql --login-path=test
This connects me to mysql correctly :

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 17

Server version: 5.7.22-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
Now I exit mysql and type a simple mysql utils command :
mysqlserverinfo --server=test
And I get
ERROR: Access denied for user 'userTest'@'localhost' using password: YES

OK, so my suspicions were correct.
The python scripts of mysql utilies use the default "my_print_defaults" utility to get the credentials from a login path.
But the my_print_defaults call returns the password as a bunch of stars, unless you call it with -s...
So I modified the python script mysql/utilities/common/my_print_defaults.py to call the my_print_defaults executable with -s to get the password too. And now it works.


How does InnoDB represent composite integer keys? Let's say I have the following table:

Create Table Orders(server : tiny int, ticket : integer, primary key (server, ticket));

How is the composite primary key representation when stored on disk?

server * 2^32 + ticket (a numeric composition of the two keys)
server & " " & ticket (a string concatenation of the two attributes)
Something entirely different

InnoDB uses B+Tree to store indexes and tables (the entire table is stored inside its primary key) and it stores the multiple columns as tuples (ordered lists) so actually there is somewhere an equivalent to a tree node having two properties - server and ticket both separate numbers.

The entire tree is then a way to store an ordered list of such pairs/tuples - they are ordered first by the first column (server) and in case of a tie the second columns (ticket) are compared and decide the ordering. For the primary key there should be no ties on both columns because it is unique, but for non-unique composite indexes there might be. But in InnoDB each secondary index has the primary key columns added to it (because the primary key is the pointer to actual data row) so any ties in there can be solved by ordering on the added primary key columns.

If you want to imagine it, then take a phonebook - each row consists of three values (lastName, firstName, phoneNumber) and is sorted in that order - the ordering might be effectively equivalent to lastName & " " & firstName but it is really done piecewise.


Store info in a column as JSON I'm learning SQL Server 2016, I learned how to extract JSON from tables.

Now I need to store JSON in a column. My JSON was:

{"endereco":"R. Nome da Rua, 000", "cep":"00000-000"}

I`m using nvarchar as the column type, but when I store it using:

UPDATE test2 SET endereco = '{"endereco":"R. Nome da Rua, 000", "cep":"00000-000"}'

When I do a select, it shows like this:

{\"endereço\":\"R. Nome da Rua, 000\", cep:\"00000-000\"}

But I was expecting this:

{"endereco":"R. Nome da Rua, 000", "cep":"00000-000"}

What was I supposed to do?

My test table creation script:

CREATE TABLE [dbo].[teste2]
(
  [id] [int] IDENTITY(1,1) NOT NULL,
  [descricao] [varchar](200)  NULL,
  [endereco] [nvarchar](MAX)  NULL
);

This is how I solved my problem:

SELECT
    descricao [nome],
    JSON_QUERY(endereco) [endereco]
FROM teste2
WHERE id=2
FOR JSON PATH, ROOT('clientes')

The "magic" was performed by JSON_QUERY (expression, path ) as documented here:

JSON_QUERY (Transact-SQL)

See also:

Frequently Asked Questions about JSON in SQL Server


JOIN on "Latest Match" in "Order" Having a hard time explaining my scenario and what I am trying to accomplish. I have two tables like so, I have reduced the values for my scenarios

Table1
+----------------------+
| ID |   Time          |
+----------------------+
| 1 | 2018-05-15 09:00 |
| 1 | 2018-05-15 09:10 |
| 1 | 2018-05-15 09:20 |
| 2 | 2018-05-15 09:30 |
| 2 | 2018-05-15 09:40 |
+----------------------+
`
`
Table2
+----------------------+
| ID |   Time          |
+----------------------+
| 1 | 2018-05-15 09:21 |
| 1 | 2018-05-15 09:31 |
| 2 | 2018-05-15 09:41 |
+----------------------+

I want to join these on the IDs but match on the latest result. By this I want the latest match ID from Table1 to Join with Latest match of ID with Table 2. So if I have a match of ID from both tables, I want the latest date to be displayed in the result and specify its coming from the other table. Once its used, the next match will get the latest unused date. Not familiar with looping through the results to handle this situation

The output I want to produce from the above query (Third column it to represent where that row comes from):

+----------------------+--------+
| ID |   Time          | Table  |
+----------------------+--------+
| 1 | 2018-05-15 09:00 | Table1 |
| 1 | 2018-05-15 09:21 | Table2 |
| 1 | 2018-05-15 09:31 | Table2 |
| 2 | 2018-05-15 09:30 | Table1 |
| 2 | 2018-05-15 09:41 | Table2 |
+----------------------+--------+

If you can upgrade to MariaDB version 10.2, you can use window functions.

The following assumes that table1 has - for every id - the same or more rows than table2:

WITH
  t1 AS
  ( SELECT id, time,
           row_number() OVER (PARTITION BY id ORDER BY time DESC) AS rn
    FROM table1
  ),
  t2 AS
  ( SELECT id, time,
           row_number() OVER (PARTITION BY id ORDER BY time DESC) AS rn
    FROM table2
  )
SELECT
    t1.id,
    CASE WHEN t2.time IS NULL OR t1.time >= t2.time
        THEN t1.time ELSE t2.time
    END AS time,
    CASE WHEN t2.time IS NULL OR t1.time >= t2.time
        THEN 'table1' ELSE 'table2'
    END AS "table"
FROM
    t1 LEFT JOIN t2
    ON  t1.id = t2.id
    AND t1.rn = t2.rn
ORDER BY
    id, t1.rn DESC ;

Tested at dbfiddle.uk


Getting SQL server to recognise a date column I am trying to get a count of IDS on a orders table for yesterday (not hardcode) where the date in the table corresponds to when the order was placed.

My table looks like this

orders (
order_id INT PRIMARY KEY
, user_id INT
, date_created DATE
, order_value FLOAT
, city_id INT
)

I have used this code to get todays date in the table -

Select *,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) as Today
From orders

This works fine but when I try attempt a where clause below this

Select *,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) as Today
From orders
where Today -1 = date_created;

I receive this error "Invalid column name 'Today'"
So my question is simply how can I get this table to recognise the the date I added as a new column and allow me to perform work on it?

Thanks in advance

I receive this error "Invalid column name 'Today'"

You can't reference the Today expression in the WHERE clause because the WHERE clause is evaluated before the SELECT clause according to the logical order of query processing.

Since your desired type is date, it would be better to use CAST or CONVERT rather than the DATEADD...DATEDIFF ugliness (common before SQL Server 2008 introduced the date datatype). Also, you need to use DATEADD to calculate yesterday's date instead of a subtraction operator.

The example below uses these techniques to get the orders from yesterday:

SELECT
      order_id
    , user_id
    , date_created
    , order_value
    , city_id
    , CAST(GETDATE() AS date) as Today
FROM dbo.orders
WHERE DATEADD(day, -1, CAST(GETDATE() AS date)) = date_created;


MySQL won't use an index on an int, if I provide a string Mysql randomly refuses to use indexes for even simple queries. 1 example  from slow-query log:

  # Time: 130904 12:19:59
   # User@Host: cron[cron] @  [10.71.41.38]
   # Query_time: 58.488760  Lock_time: 0.000029 Rows_sent: 0  Rows_examined: 13634899
  SET timestamp=1378311599;
  UPDATE dial_list set status='AB' where lead_id = '3335602076';

This is a MYISAM table with lead_id as primary key

| Field         | Type             | Null | Key | Default     |Extra                   |
| lead_id       | int(9) unsigned  | NO   | PRI | NULL        | auto_increment         |

The same query run on an identical server with similar data size uses the index. Also, not all such queries on this server turn up as table scans. What could be the problem?

From the comments dcaswell,

  Is the fact that you're comparing a string and int the issue

He suggests you check out

http://stackoverflow.com/questions/16786063/mysql-comparison-of-integer-value-and-string-field-with-index


Mysql join non-unique columns I have two tables, want to join them by an non unique column.

CREATE TABLE `order_item` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `soi` char(1) DEFAULT NULL,
  `product` char(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `inventory` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `inventory` char(1) DEFAULT NULL,
  `product` char(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `order_item` (`id`, `product`)
 VALUES
    (1,'aaaa'),
    (2,'aaaa'),
    (3,'aaaa'),
    (4,'bbbb'),
    (5,'cccc');

INSERT INTO `inventory` (`id`, `unitid`, `product`)
VALUES
    (1,'aa-002','aaaa'),
    (2,'aa-00e','bbbb'),
    (3,'aa-00w','cccc'),
    (4,'aa-00d','aaaa'),
    (5,'aa-003','cccc'),
    (6,'aa-003','bbbb');

-------------------------------------------------------------

order_item table
----------------
|id |product|
|1  | aaaa  |
|2  | aaaa  |
|3  | aaaa  |
|4  | bbbb  |
|5  | cccc  |

inventory table
---------------
|id| unitid | product|
|1 | aa-002 | aaaa   |
|2 | aa-00e | bbbb   |
|3 | aa-00w | cccc   |
|4 | aa-00d | aaaa   |
|5 | aa-003 | cccc   |
|6 | aa-004 | bbbb   |

i'm trying to get all the orders that can fulfill by inventory like below

|order_item_id| product | inventory_id | unit_id | product|
| 1           | aaaa    | 1            | aa-002  | aaaa   |
| 2           | aaaa    | 4            | aa-00d  | aaaa   |
| 4           | bbbb    | 2            | aa-00e  | bbbb   |
| 5           | cccc    | 3            | aa-00w  | cccc   |

I tried with inner join + group by like below its only returns one match from each table

select * from order_item
inner join `inventory` on order_item.product = inventory.product
group by inventory.product

Not sure I understood it correctly, but if you really want to match "first order item with first available inventory" etc. until you get out of "available" units, you have to give each row its "rank" - the first/second/third/.. designation. It is possible to do using user variables (and probably by some other tricks).

http://sqlfiddle.com/#!9/cd6ef/4

select
 order_item_id, order_item_product, order_item_rn,
 inventory_id, inventory_unit, inventory_product, inventory_rn
from
(select
 `id` order_item_id,
 `product` order_item_product,
 @r:=CASE WHEN @p <> `product` THEN 1 ELSE @r+1 END AS order_item_rn,
 @p:=product AS pset
from
 (SELECT @r:= 0) s,
  (SELECT @p:= '') c,
  (SELECT *
   FROM order_item
   group by id
   ORDER BY product, id
  ) t1) t3
join
(select
 `id` inventory_id,
 inventory inventory_unit,
 `product` inventory_product,
 @r:=CASE WHEN @p <> `product` THEN 1 ELSE @r+1 END AS inventory_rn,
 @p:=product AS pset
from
 (SELECT @r:= 0) s,
  (SELECT @p:= '') c,
  (SELECT *
   FROM inventory
   group by id
   ORDER BY product, id
  ) t2)t4
ON
 t3.order_item_product = t4.inventory_product
 AND t3.order_item_rn = t4.inventory_rn;

But you should probably do it at the application level and not in SQL at all. For tables containing lot of rows I expect this solution to be quite slow.


Cannot shutdown a shutdown connection MySQL Enterprise Monitor We are facing below errors on MySQL Enterprise Monitor.. While deleting a connection from MySQL Instances it is displaying following error messages.
Also it is not deleted event it displays success message.

  Cannot shutdown a shutdown connection
  Connection refused: connect

Any way of doing this? Any table in DB storing information in MySQL databases.

MySQL 5.6.21
Windows Platform

There is really nothing you can do once a shutdown starts. Why ?

According to the MySQL Documentation on the Shutdown Process

  2 The server creates a shutdown thread if necessary.

  Depending on how shutdown was initiated, the server might create a
  thread to handle the shutdown process. If shutdown was requested by a
  client, a shutdown thread is created. If shutdown is the result of
  receiving a SIGTERM signal, the signal thread might handle shutdown
  itself, or it might create a separate thread to do so. If the server
  tries to create a shutdown thread and cannot (for example, if memory
  is exhausted), it issues a diagnostic message that appears in the
  error log:

Error: Can't create thread to kill server

  3 The server stops accepting new connections.

  To prevent new activity from being initiated during shutdown, the
  server stops accepting new client connections by closing the handlers
  for the network interfaces to which it normally listens for
  connections: the TCP/IP port, the Unix socket file, the Windows named
  pipe, and shared memory on Windows.

It sounds like you are past Stage 3 because you got Connection refused: connect

In addition, note what may have triggered Stage 2:

  If shutdown is the result of receiving a SIGTERM signal, the signal thread might handle shutdown itself, or it might create a separate thread to do so.

What could be that trigger ? Stage 1 says regarding Windows

  A server running as a service on Windows shuts down when the services manager tells it to.

At this point, you have most likely reached Stage 4, which would go on to terminate currently active connections.

CONCLUSION

Maybe a Bug Report or Trouble Ticket would be in order. Just make sure you have done your due diligence in terms of finding out if the Service Manager died or that human error or lack of memory on the Windows box was ruled out.