Postgres epoch from current_timestamp I've learned that epoch (UNIX timestamp) is a unique independent amount, so I am baffled by output I get from the following queries:

select  extract( epoch from current_timestamp::timestamp with time zone at time zone 'Asia/Tehran');
1588605420.89165

select  extract( epoch from current_timestamp::timestamp with timezone at time zone 'UTC');
1588589296.18219

which there is a deficit of 16200 (4 and a half hours). Why is that when it explicitly declares that current_timestamp is in a specific time zone. Should both epochs (UNIX timestamp) not map to the same amount of seconds?

what I don't get maybe better displayed on contrast with this small python code:

>>> import time
>>> import pytz
>>> import datetime
>>> teh_tz = pytz.timezone('Asia/Tehran')
>>> utc_tz = pytz.timezone('UTC')

>>> datetime.datetime.now(teh_tz)
datetime.datetime(2020, 5, 4, 15, 31, 11, 14869, tzinfo=<DstTzInfo 'Asia/Tehran' +0430+4:30:00 DST>)
>>> datetime.datetime.now(utc_tz)
datetime.datetime(2020, 5, 4, 11, 1, 16, 198437, tzinfo=<UTC>)

>>> datetime.datetime.now(teh_tz).timestamp()
1588590087.590199
>>> datetime.datetime.now(utc_tz).timestamp()
1588590091.901826

As it's seen, different time zones have differences in their current time but all map to the same amount of seconds (UNIX timestamp).
I guess I'm totally lost in here, please save me.

The documentation of extract clearly states:

  For timestamp with time zone values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative); for date and timestamp values, the number of seconds since 1970-01-01 00:00:00 local time; for interval values, the total number of seconds in the interval

If you want to know how many seconds passed since Jan. 1st 1970 in your time zone, use

extract(epoch FROM localtimestamp)

The result of AT TIME ZONE, when applied to a timestamp with time zone, is always a timestamp without time zone.  extract will interpret such a time stamp in your current time zone (it does not know about the second argument you passed to AT TIME ZONE).

Your comment suggests that you want to get the (absolute) epoch at the beginning of the current day in your time zone. That would be

extract(epoch FROM date_trunc('day', current_timestamp))


Window Functions: What is the purpose of Rows Unbounded Preceeding? What is the purpose of using the Rows Unbounded Preceeding clause in a window function?
I think I understand that it's basically saying not to limit how far back to look when rolling up for the aggregate function, but how is that different than not using the clause at all?

Could you please provide an example to illustrate the difference between:

SUM(ColumnA) OVER (PARTITION BY ColumnB ORDER BY ColumnC DESC ROWS UNBOUNDED PRECEEDING)

And

SUM(ColumnA) OVER (PARTITION BY ColumnB ORDER BY ColumnC DESC)

Note: My question is in the context with no cap on the Rows Following.

Semantic Differences Between Rows and Range

If we look at the results of this query, it's fairly easy to see when we might want to examine use range or rows.

SELECT   OwnerUserId,
         CAST(CreationDate AS DATE) AS DumbedDownDate,
         Score,
         SUM(Score) OVER
                   ( ORDER BY CAST(CreationDate AS DATE)) AS Not_Specified,
         SUM(Score) OVER
                   ( ORDER BY CAST(CreationDate AS DATE) RANGE UNBOUNDED PRECEDING ) AS Range_Specified,
         SUM(Score) OVER
                   ( ORDER BY CAST(CreationDate AS DATE) ROWS UNBOUNDED PRECEDING ) AS Rows_Specified
FROM     dbo.Posts
WHERE    OwnerUserId = 1
AND      CAST(CreationDate AS DATE) BETWEEN '2008-08-01' AND '2008-08-31'
ORDER BY DumbedDownDate;

When no boundary is set, the results are the same as if we specifically ask for a RANGE. Asking for ROWS gives us a different result all together.

The ROWS result is more like a running total, and the RANGE result is the total value for the... range of rows.

Performance

If you use rows, the window spool operator can use an in memory work table. Range uses an on disk work table, which is sometimes slower.

Compare these two queries:

SELECT   OwnerUserId,
         CreationDate,
         Score,
         SUM(Score)
             OVER ( ORDER BY CreationDate RANGE UNBOUNDED PRECEDING ) AS Range_Specified
FROM     dbo.Posts
WHERE    OwnerUserId <= 22656
AND      CreationDate >= '20080101'
AND      CreationDate < '20150101'
ORDER BY CreationDate;

SELECT   OwnerUserId,
         CreationDate,

         Score,
         SUM(Score)
             OVER ( ORDER BY CreationDate ROWS UNBOUNDED PRECEDING ) AS Rows_Specified
FROM     dbo.Posts
WHERE    OwnerUserId <= 22656
AND      CreationDate >= '20080101'
AND      CreationDate < '20150101'
ORDER BY CreationDate;

The query plans for them show quite different timing for the Window Spool:

If we look at the output of STATISTICS IO for both queries, they're also quite different:

Table 'Worktable'. Scan count 1363952, logical reads 11944334, physical reads 0,
read-ahead reads 3884, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Posts'. Scan count 5, logical reads 4722714, physical reads 0, read-ahead
reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

vs.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 3884, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Posts'. Scan count 5, logical reads 4723890, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Batch Mode

In SQL Server 2019, where Batch Mode is generally available for Row Store queries, or SQL Server 2016/2017 where there are various methods to introduce Batch Mode processing to Row Store queries, it's possible to get close to even performance thanks to the addition of the Batch Mode Window Aggregate.

See the three part series from Mr. Itzik Ben-Gan here:

Part 1
Part 2
Part 3


Advice on choosing a really fast DBMS I am working on a project that does real-time monitoring of data being transmitted, and logs if there are any of the various type of errors, etc. The CRUD operations are real-time. We initially planned on using PostgreSQL, but the problem we are facing is that PostgreSQL is not fast enough to handle real-time CRUD operations even after tweaking it a little; same goes for MySQL and other biggies. SQLite performs a lot faster than them, but is soon almost dead when the database size reaches a few hundred MBs. Another constraint is that the monitoring is to be done over network.

Is there any database that can handle such fast operations? Or should I opt for a NoSQL database?

EDIT (Regarding design):

The design is normalized to the maximum extent it was possible. The stored data is almost mutually independent, so there are very few joins. Also, I said "a few hundred MBs" just as a reference. The actual databases that we work upon are multiple GBs in size. Lots and lots of data is inserted every second and retrieved also.

Talking about PostgreSQL, it takes 5-7 times the time taken by SQLite in the tests that I ran on my data.

EDIT (Regarding speed):

I'll like to mention a worst-case scenario that can happen.

Suppose that the main application is being used at 10 instances (or PCs). They all interact with a cental DBMS and insert data into it. Now, every single app will have many threads doing some operations on the data that is being transmitted in real-time. The app reports if there is erraneous data in the streaming or not. And since the data is analyzed at packet level, lots of errors can happen in a sec. Based on some very basic calculations, the worst case may require an insertion rate of ~3k rows per second per instance, with each row having some 8-10 associated columns. I tested such a test on my machine(4GB ram, QuadCore), and SQLite is able to do this in ~1 sec over network. I had tweaked PostgreSQL a little, and it did that same in ~5 sec (I'd admit that I did not optimize it a lot, as I am not a pro in the DBMS field). But the bottleneck comes as the db size grows bigger in SQLite; the insertions seemingly go on almost fine, but the reads take a lot of time. I had tested it with a DB size of 3gb myself.

Our major concern is the insertions, which, in worst case will be ~3k per app instance and in average case ~500-1k insertions per instance.

If you can't scale a major RDBMS then your database design (includes indexing, queries and the like) or hardware is wrong. The choice of platform is almost irrelevant.

It is that simple. Especially when you mention "few hundred megabytes" which implies low volumes (I mean a few dozen writes per second)


Get max date for each year from list of dates Need to get max date for each year from list of arbitrary dates:

date         year
=================
1/10/2013    2013
3/26/2013    2013
4/20/2012    2012
9/10/2012    2012
5/6/2011     2011

So with a working query, using the above data, the correct result would be:

maxdate     year
=================
3/26/2013    2013
9/10/2012    2012
5/6/2011     2011

Our database is proprietary, supporting only ANSI-SQL, no fancy extensions.
Tried using group_by, but it's retrieving all dates for each year, not what I want.

It is possible that I am missing something, but you should be able to get the result easily using the max() aggregate and a GROUP BY:

select
  max(date) maxdate,
  year
from yourtable
group by year;

See SQL Fiddle with Demo


Simple way to export and import data in SQL Server I have two SQL Server instances, each with a database.

They are not on the same network and not connected to the Internet and the only way to get data between them is with a thumbdrive.

I want to export a table from one of the computers, put the exported data on a thumbdrive, then import the data into the other computer.

How do I do that?

The data has NULLS so I am afraid a flat file transfer will change the data.  That is, a NULL is converted to an empty string.

The other solutions I have found require using linked servers or otherwise keeping the data in the databases.

In DB2 this is easy because I run a EXPORT command that exports to a ".idx" file and then IMPORT command that imports the data.  This command also defines the table.

This data is only 23,000 rows.

Right Click DatabaseName -> Go to Task -> Generate Script
One dialog is open

1). Select ALL Table & Click on Next

2). Click on Advance Select  button and select value "Data only" in Type data to Script dropdown

3). And Click Finish

4). Copy result .sql file to target machine.

5). run resultant .sql file to target machine.


Problems with insert trigger and primary key I'm dealing with a 3rd party app that I can't change or get support on. I've altered an underlying table in several ways to enhance it's functionality

1- I renamed the table PRE_CASE to PRE_TCASE
2- I created a view named PRE_CASE which is a select on to PRE_TCASE with a UNION ALL on to a linked server DB and table named OTHERSYSTEM_CASE

This works to the extent the app is now fooled into displaying data from PRE_TCASE and OTHERSYSTEM_CASE to the end user when it believes it's looking at the table PRE_CASE. It fails however on inserting data. To try and fix this I created a trigger (I only require to insert to PRE_TCASE, never OTHERSYSTEM_CASE)

create TRIGGER INSREPLACE
   ON  dbo.pre_case
   INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.PRE_TCASE
    (col1, col2, col3, etc.)
    SELECT col1, col2, col3, etc.
    FROM Inserted

END

I am getting failures on insert with the error:

Msg 233, Level 16, State 2, Line 1
The column 'ID' in table 'dbo.pre_case' cannot be null.

The insert statement is of the form

INSERT INTO dbo.pre_case (col1, col2, col3)
values (1,2,3)

No value/reference is made to the ID column.

On the underlying PRE_CASE the column ID is an integer primary key (PK, int, not null) with a valid identity seed. Direct inserts onto PRE_CASE work, but not ones onto the new view and trigger. What have I overlooked?

(My end solution will be deployed onto SQL2000, SQL2005 and SQL2008R2 boxes, but I'm testing/developing on SQL2000)

Edit: I created this model to test in a more isolated way. Turns out the error occurs on SQL2000 and not SQL2008R2, personally I'm giving up at this point.

create table t1(id int  IDENTITY(1,1) PRIMARY KEY ,ltext varchar(100));
create table t2(id int IDENTITY(1,1) PRIMARY KEY,ltext varchar(100));

CREATE VIEW tv AS
SELECT id, ltext from t1
union all
SELECT id, ltext from t2

select * from t2;
select * from tv;

INSERT  t1(ltext) values ('test1a');
INSERT  t1(ltext) values ('test1b');
INSERT  t1(ltext) values ('test1c');
INSERT  t2(ltext) values ('test2a');
INSERT  t2(ltext) values ('test2b');
INSERT  t2(ltext) values ('test2c');

CREATE TRIGGER TRG_TV ON tv
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
INSERT t2(ltext)
select ltext from inserted

END

-- this errors on SQL2000
INSERT  tv(ltext) values ('testv-a');

At first I thought this was due to the INSERT INTO pre_case that you are doing, not the INSERT INTO trigger. But that turned out not to be the case after more info was supplied.

Judging by the google results, this is a common error with INSTEAD OF triggers and presumably an identity column. The value for that column has not been generated when the INTEAD OF trigger fires, so it's NULL from ID. Change the column to be nullable and then see what you get in the ID column in the new table. If you have a value for it, then you are good to go. If you don't, it seems you'll need to generate it.

  (My end solution will be deployed onto SQL2000, SQL2005 and SQL2008R2 boxes, but I'm testing/developing on SQL2000)

CRAZY


mysql replication missed quite a lot SQL statement I have set up a master/slave with mysql-5.1.73
The master's binlog format is "statement".
The master and slave seemed running very well with slave status:

         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes

And when I modified the content on the master manually, whether it was select or update or insert, or alter table, the modification will be synchronized to the slave instantly.

However, after running for several days, I found the slave missed a lot of insert statements, these insertion statements didn't violate any PRIMARY key rule. More ever, when I tried to redo the binlog on another slave with:

mysqlbinlog mysql-binlog.00000X | mysql

Those missed statement were missed again with no warning or error.

Have you ever met such a situation, what should I do to restore all the changes to the slave? (There are quite a lot of  missed changes, so I could not restore them one by one).

I dug into this matter to find that, the relay log on the slave contains all the insertion statement, which means the binlog is transmitted to the slave correctly. However, the binlog on the slave missed some of the insertion statement, so this issue appeared to occurred during the redo process of the slave.

Any suggestions to diagnose into this issue or work around it?

Two possibilities come to mind:

*SQL_LOG_BIN* was temporary turned off.
Your master server crashed without synchronizing the binary log
(and most probably *SYNC_BINLOG* was set to a value different than 1 ...).


How do I change SQL Server 2012 Availability Group DNS subdomain? Questions: how can I get my availability group to be registered as ag-ewgtest.sql.company.com given that my server thinks of itself as server.company.com.

Setup: my SQL Server  2012 availability group is on multiple subnets, similar to what's described at in this MSDN blog article. When I go into Failover Cluster Manager, it shows that name resolution is not yet available:

I then right-click "Name: ag-ewgtest" and choose properties. I check off "Publish PTR records" and it shows me that the full name is ag-ewgtest.company.com.

The problem I have is that I'm only being allowed to update the DNS zone sql.company.com. If I change the DNS name to be ag-ewgtest.sql.company.com then the full name becomes ag-ewgtest.sql.company.com.company.com.

I've tried updating my NIC as:

But hasn't made a difference. How can I change the subdomain that my availability group tries to register itself in?

The Availability Group Listener name is a Computer Name Object (CNO) in Active Directory, not just a DNS entry:

http://technet.microsoft.com/en-us/library/ff367878(v=exchg.150).aspx

Its location in AD determines where it gets registered. If you want to create a different one somewhere else, you can create a DNS CNAME (alias) pointing to the real Availability Group Listener name:

http://technet.microsoft.com/en-us/library/ff625726(v=ws.10).aspx


How do I create a new Oracle schema? I’m pretty familiar with Relational DBMS in general, but not with Oracle. I have Oracle 18c XE running inside Docker (on MacOS). I connect to it using a third party client (SQLPro Studio on MacOS). Now I want to get started.
If this were MySQL I could issue CREATE DATABASE whatever; USE whatever; and proceed with the tables. The same with Microsoft SQL Server, and something similar with PostgreSQL. I could also create an additional schema to keep better organised.
I gather that Oracle XE only gives you a single database, and I would want to create a suitable schema. At this point I’m at sea. I gather that I would need to create a new user associated with the schema.
I also know that different RDBMS have different ideas about what a database or a schema is, so I’m not sure whether this is the correct definition or solution.
I’m having no success creating either a new user or a new schema in my client.
I have tried:
CREATE USER fred IDENTIFIED BY 'h3art0fg0ld`;

but I get the message:

invalid common user or role name

I also read that I should use something like c##fred, which did work, but (a) I don’t really know what that means and (b) the statement GRANT connect, resource, create sessions TO c##printsdb; gives me the message “missing or invalid privilege”, so I’m no further ahead.
Being in a Docker container, I don’t know whether I have access to SQL*Plus, which some online tutorials presume.
How do I create and use a schema, or whatever is required to contain a collection of tables?

I gather that Oracle XE only gives you a single database

Correct - as long as you "speak" Oracle-ese.
The Container Database is the only "real" Oracle Database instance that you have.
The "Pluggable Databases" within it are essentially "Tablespaces on Steroids", but with much better security that now guarantees the isolation of each one from every other.
For most normal operations, you should ignore the Container Database completely.
Just use the/a Pluggable Database inside it.

How do I create and use a schema, or whatever is required to contain a collection of tables?
CREATE USER fred IDENTIFIED BY '********';

Connect to the Pluggable Database and your statement will work exactly as you expect it to.


How to change the database mode to STANDBY? I have a "log shipping" from server A(Database Name = main) to server B(Database Name = LS_main) .
I want to take a Snapshot Database From the secondary Database (Database Name = LS_main), but he id in STANDBY mode (Read_only) and I can't do it.

How can I do it ?

(I Thought that I can change the DatabaseLS_Main from Standby mode to bring it online, take the snapshot and bring it back to STANDBY mode...is it right to do ?)

I Thought that I can change the DatabaseLS_Main from Standby mode to bring it online, take the snapshot and bring it back to STANDBY mode...is it right to do ?

What you are telling is not possible using logshipping.
There are limitations of database snapshots:

In a log shipping configuration, database snapshots can be created only on the primary database, not on a secondary database. If you switch roles between the primary server instance and a secondary server instance, you must drop all the database snapshots before you can set the primary database up as a secondary database.

There are some options:

If your database is not that big, you can do a snapshot replication, if you really want a snapshot of your database as opposed to doing a backup/restore.
If you want a near-to-realtime data on some secondary server, then transactional replication would be a better option.
Alternatively, you can use database mirroring. It allows database snapshot on the mirrored server but the database must be in the synchronized mirroring state.
Now that SQL Server 2012 and 2014 are out with AlwaysON, you can consider that as well if you want a read-only copy of your database.

Note that database snapshots as well as AlwaysON are Enterprise features :-)


Maximizing throughput on bulk data processing I'm using SQL Serverversion 14.0.3035.2

I have a pile of information that I need to process from table A and insert the modified data into table B. I have written an application to query the data, do some binary manipulation, and insert the results into the target DB.

I am convinced the slowdown is during the read.

When I first started running the application, It was pretty speedy. Towards the second half of the execution it's slowed to a crawl. The difference is the number of rows with the bit IsFetched = 1 in the Source and the number of rows in the target table

Both tables share a drive for the Data files, and also share a drive for the Log files (Dedicated disk for log, dedicated disk for data)

In order to maximize my throughput, I've made the application run batches in parallel. My query to get the data and mark it as 'touched' looks like this

UPDATE TOP(100) _s
            SET IsFetched = 1
            OUTPUT
                INSERTED.[Id],
                INSERTED.[BinaryData]
            FROM Source _s
            where _s.IsFetched = 0

The table I'm reading from has the schema

CREATE TABLE [dbo].[Source](
[SourceID] [int] NOT NULL,
[BinaryData] [varbinary](max) NULL,
[IsFetched] [bit] NOT NULL,

CONSTRAINT [PK_Source] PRIMARY KEY CLUSTERED
(
    [SourceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Source] ADD  CONSTRAINT [DF_Source_IsFetched]  DEFAULT ((0)) FOR [IsFetched]
GO

I have a non-clustered index on IsFetched.

CREATE NONCLUSTERED INDEX [Idx_Fetched] ON [dbo].[---]
(
    [IsFetched] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

I'm writing to a table with the schema

CREATE TABLE [dbo].[Target](
[SourceId] [int] NOT NULL,
[BinaryData] [varbinary](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

As expected, there are locks on fetching the data. However, it seems like it is taking much longer than necessary to get the data. I've used this pattern before and achieved a much higher throughput (10's of thousands of rows per second). Right now I'm getting a maximum throughput of about 200-300 rows per second.The binary data isn't that big so I don't think it's a matter of reading too much data at once.

I've found changing the degree of parallelism and batch size doesn't do much to increase the speed but the fastest I can get is about 20 degrees of parallelism with 10 rows per transaction.

My table doesn't seem to be too fragmented but my disk seems to be the culprit

I should think your workload is not using the index on IsFetched at all. Each successive UPDATE will be starting at the beginning of the table and reading forward until it has processed 100 rows (your TOP value). Subsequent iterations have to read over the previously-fetched rows to get to new ones. Conceptually, the first iteration reads 100 new rows and updates them. The second iteration reads the first 100 rows and skips them then reads & processes 100 new rows. The third reads those 200 rows, skips them, read 100 new rows and process them, and so on.

Why does the query not use the index? The query execution plan is generated when the query is first submitted. At that point all rows in the table have IsFetched = 0. So using the index would not help distinguish which rows to process and which to skip. Indeed using that index would be an overhead since execution would have to read other structures for the other columns' values. The optimizer has no way of accounting for the fact that this UPDATE will be run many times in succession.

The solution is to use a different architecture based on SourceID, which is unique (and clustered). On each iteration store the highest value of SourceID that was processed, then start the next iteration from that value.

First some set-up and test data

drop table if exists Source;
drop table if exists Target;
go

create table Source(SourceID int primary key);
create table Target(SourceID int primary key);
go

insert Source(SourceID) values (1), (2), (3), (4), (5), (6), (7), (8), (9);
go

I'll show the code to iterate then describe it after.

declare @batch int = 4;      -- for 9 test rows this gives me 2 full + 1 partial batches
declare @highest int = -1;   -- set to any value lower than what is in your data
declare @var table (SourceID int primary key clustered);
declare @c int = @batch;     -- essentially a flag showing if the previous
                             -- iteration found rows

while @c >= @batch    -- stop once a not-full batch is processed as that
                      -- marks the end of the data
begin
    print 'Highest: ' + cast(@highest as varchar(99));  -- debug

    insert top (@batch)
    into target(SourceID)
    output
        inserted.[SourceID]
        into @var
    select
        SourceID
    from Source
    where SourceID > @highest
    order by SourceID;    -- important to order so each iteration is guaranteed to get
                          -- a contiguous block

    set @c = @@ROWCOUNT;  -- flag whether to iterate

    -- select * from Target;    -- interesting for debug, do NOT use in production!

    set @highest = (select max(SourceID) from @var);
end    -- end iteration

The output messages are

Highest: -1              -- this is the start with the dummy initial value

(4 rows affected)        -- we find rows 1, 2, 3 & 4 and process them
Highest: 4               -- start the second iteration from "> 4"

(4 rows affected)        -- rows 5, 6, 7 & 8 done
Highest: 8               -- third iteration from "> 8"

(1 row affected)         -- only row 9 left.

There's an assumption that there is an index on the key column (SourceID). Each iteration performs and index seek to where the last left off and then scans leaf pages from there, reading @batch rows. Therefore it obviously works best if the index is covering or is the clustered index, which is covering by definition. Without an index this will revert to table scans and you're no better than you were before.

I put an index on @var. Inserts will be in slustered sequence so there will be no page splits. The MAX() query will be a single-row lookup, which is nice. If you can a non-durable in-memory table works nicely in this role, too.

I make @batch a variable for ease of debug. It could be hard-coded.

Variable @c is really a flag. Defining as an integer and setting it as I do avoids unnecessary IF statements.

It is important to have an ORDER BY on the SELECT. Without it we are not guaranteed that the rows returned are adjacent. For example, given the data above and @highest = -1, rows 3, 5, 7 & 9 satisfy the WHERE clause, which is not what we desire.

There is no requirement for SourceID values to be contiguous. If there are holes in the sequence, if data has been deleted, this is OK.

I stop once an iteration processes fewer than the desired number of rows.

Note that IsFetched is not required for this. It can be dropped from the schema if this is its only purpose.

There's a nice write-up here including benchmarking over a large-ish table. Swart's solution reads ahead to find the end of a batch then processes the batch in a second statement. Mine determines the end of a batch retroactively after a batch is processed. His scans the source table twice and mine once plus an index lookup. His solution will always perform a "no-op" iteration at the end of the table, mine when the table cardinality is an exact multiple of the batch size. I think mine is slightly more efficient but you're unlikely to notice in a real workload.

If the table source is continuously populated with new rows and not truncated between runs you can save the value of @highest to a table at the end of a run and populate it from that table during the next run. If future runs can insert SourceID values lower than the current largest, or if there are concurrent writes, the algorithm can be adjusted to accommodate.

My experience is that batch sizes of a few thousand rows give the best balance between throughput, blocking and resource consumption. Test on your environment, of course, to see what works for you.


How to get all the users on a database with SA access? I am new to SQL database and besides running basic queries I don't know much. Trying to figure out how to get users with SA access.

Can anyone guide me through this, will really appreciate the help.

The simplest scenario (a user explicitly granted sysadmin directly) is covered by the following query:

SELECT p.name
  FROM sys.server_principals AS p
  INNER JOIN sys.server_role_members AS m
  ON p.principal_id = m.member_principal_id
  WHERE m.role_principal_id = 3;

But then you may also have users who are members of a Windows group (which SQL Server won't know about) and that group has been granted sysadmin. To get all of those users you may need to run some queries against Active Directory (or ask someone who can).


How to calculate mysql server reads per second,writes per second? Can anyone let me know how to calculate mysql server writes,reads,deletes,updates per second.

There are two ways of determining this.

RECENT VERSIONS OF MYSQL

One is to use the performance_schema (assuming 5.6 or higher).

There are many tables that you can query to get at this information, particularly if you have innodb_file_per_table = 1 in your my.cnf.

If you do USE performance_schema there are several tables that can help you to find this information, notably (from DESC TABLES;)

| file_instances                                     |
| file_summary_by_event_name                         |
| file_summary_by_instance                           |

and

| table_io_waits_summary_by_index_usage              |
| table_io_waits_summary_by_table                    |
| table_lock_waits_summary_by_table                  |

Then queries such as

mysql> SELECT SUM(sum_number_of_bytes_read) AS Total_Read
FROM file_summary_by_instance;

+----------+
|Total_Read|
+----------+
| 10470860 |
+----------+
1 row in set (0.00 sec)
mysql>

and

mysql> SELECT SUM(sum_number_of_bytes_read)
+ SUM(SUM_NUMBER_OF_BYTES_WRITE)
AS Total_IO
FROM file_summary_by_instance;

+----------+
| Total_IO |
+----------+
| 15161292 |
+----------+
1 row in set (0.00 sec)
mysql>

can help you get the information you want.

OLDER VERSIONS OF MYSQL

If you don't have a recent version of MySQL, then you can use the Unix utility iostat to help you obtain a (reasonably good) estimate of your overall usage.

[pol@localhost inst]$ iostat
Linux 3.11.10-301.fc20.x86_64 (localhost.localdomain)   11/05/15    _x86_64_    (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           6.33    0.03    2.97    6.12    0.00   84.55

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
scd0              0.02         0.09         0.00        116          0
sda              13.34       544.46        55.79     742770      76109
dm-0              0.20         0.82         0.00       1116          0
dm-1             16.14       497.16        23.26     678245      31736
dm-2              5.69        42.97        32.52      58628      44364
[pol@localhost inst]$

Some of the readings are on a per/second basis, but others represent data from machine bootup. You should obtain a baseline figure (with no MySQL running) and then take your measurements with the RDBMS running and subtract the baseline from the MySQL numbers - that way you will have a reasonable estimate of how much I/O is going on in your server.

iostat is available through the sysstat package which may or may not be installed on your distro - use man iostat (and man sar &c.). The package is available from here. Check out the documentation for your particular system.

For completeness, I should also add that this information is available on an ad-hoc basis through the (excellent) program MySQL Workbench, as shown by the screenshot below:


Generate Database Creation Script Within A Stored Procedure I'm looking to automate a wipe of our test environment to bring it up to be in-line with production at the end of every sprint.

As it stands this is currently being completed by the test enviroment grabbing the full backup and doing a restore with move&replace however this is using up most of the space we have.

The intention is to create the environment from scratch and populate only the needed tables.

I can use the 'Right Click > Tasks > Generate Scripts...' to create the framework of the database.

Is there a way to create that script from within a stored procedure so that can be used to re-create the database

Also I'm working on this bit as it stands but obviously that script will just create the database at its current size so all of the File Sizes need to be modified once the script has been generated

Cheers for any help

There are several methods you could use.  In order of difficulty (easiest to hardest IMHO)

Manually create (using generate scripts) a structure only DB populate it with just the data you need for the copy and then backup/restore it.  (you could also do it structure only and code the data copy)

Up side - This is the easiest method to code and should be fairly quick
Down side - This requires constant maintenance as the code/data of your source database changes.

Manually create the scripts and have the SP run them (probably using dynamic SQL) and copy the data.

Up side - Also fairly easy to implement and doesn't require an additional "model" DB that you will copy.
Down side - Also requires constant maintenance

Powershell - You use a POSH script to create the new db, generate the scripts and run them.

Up side - This is the most powerful method.  It won't require much maintenance and you can probably even find a script someone else has written as a starting point.
Down side - This requires that you know Powershell at least a little bit. (Which might be considered an up side as well)

OLE Automation Procedures - sp_Create etc.  This is going to be the most difficult/complicated method (knowing at least a little bit of this and PoSH) but once written also won't require that much maintenance.

Up Side - Minimal maintenance and it can all be run inside of SQL
Down side - more difficult code, you probably can't find a script already built and you have to enable OLE Automation Procedures (a security risk, if minimal).

I should probably note that my original thought was you wanted this to be re-producible.  If you only have to do it once then @MaxVernon is right.  Truncate the tables you don't need data in (or delete what you don't need) and shrink.  You may have to do this a bit at a time depending on how constrained you are for  space.


How much memory do I need for innodb buffer pool? I read here that

  You need buffer pool a bit (say 10%) larger than your data (total size
  of Innodb TableSpaces)

On the other hand I've read elswher that innodb_buffer_pool_size must be up to %80 of the memory. So I'm really confused how should I choose the best size for the pool. My database size is about 6GB and my total memory 64GB. Also I'm wondering if I increase the buffer pool size, I should shrink the number of maximum connections to make room for extra buffer, or these parameters are independent. Thanks

If you go strictly by that rule of accommodating an addition 10%, here is my suggestion:

SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
    SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
    FROM
    (
        SELECT SUM(data_length+index_length)*1.1*growth RIBPS
        FROM information_schema.tables AAA,
        (SELECT 1 growth) BBB
        WHERE ENGINE='InnoDB'
    ) AA
) A;

This will produce exactly what you need to set innodb_buffer_pool_size in /etc/my.cnf. If you want to account for 25% increase in data and indexes over time, please change (SELECT 1 growth) BBB to (SELECT 1.25 growth) BBB

Recently, I answered another question like this in the DBA StackExchange.


Putting an order in the way how locks are aquired in MySQL From MySQL documentation:

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned.

When I'm performing an UPDATE (although I would be interested about the others also, now I'm concerned with UPDATE statement), is there a way to put lock in the same order, so that to avoid deadlocks as much as possible?

N.B. Deadlocks occur in my table due to concurrent updates and inserts (and even deletes).

Short answer: No.

Thing you can do:

Tailor the indexes to the queries -- this may decrease the frequency of deadlocks.  Would you like to provide some examples of what is giving you trouble?
Sort the items in IN(...) -- this may prevent certain deadlocks.
Check for errors and replay transactions that are rolled back due to a deadlock.  Then live with the deadlocks.


MySQL join two large tables is very slow I have two tables, one of them contains a history of downloaded urls, while the other table contains details about each url.

The following query groups the URLs by the number of repetitions during the past hour.

SELECT COUNT(history.url) as total, history.url
FROM history
WHERE history.time > UNIX_TIMESTAMP()-3600
GROUP BY history.url
ORDER BY COUNT(history.url) DESC
LIMIT 30

The query above takes about 800ms to execute, not fast enough, but acceptable,

however, when joining it with the cache table, the new query takes about 25s to execute, which is very slow.

SELECT th.total, th.url, tc.url, tc.json
FROM (SELECT COUNT(history.url) as total, history.url
      FROM history
      WHERE history.time > UNIX_TIMESTAMP()-3600
      GROUP BY history.url
      ORDER BY COUNT(history.url) DESC
      LIMIT 30
) th
INNER JOIN (SELECT cache.url, cache.json FROM cache) tc
    ON th.url = tc.url
GROUP BY th.url
ORDER BY th.total DESC
LIMIT 30

I think that this might be happening because in 'tc', the whole cache table is being loaded, and it contains 1million+ entries.

When I use the first query, and then programmatically iterate the results and then run a SELECT query from cache for each result, it is much faster. Is there anyway to speed up my second query?

P.S. I'm using InnoDB

UPDATE
The output of the second query with EXPLAIN

The structure of 'history' table

The structure of 'cache' table

It is generally a good idea to index columns that participate in JOIN predicates or WHERE clauses. A common mistake is to create several one column indexes instead of fewer multi-column indexes. Here we can benefit from both url and time in history (look at all queries against these tables and you may find that you can add additional columns to these indexes):

CREATE INDEX x01_history_url ON HISTORY (URL, TIME);
CREATE INDEX x01_cache_url ON CACHE (URL);

Second, try to unnest the queries. MySQL has limitations on what kind of query rewrites it is able to do, so the nesting may cause unnecessary overhead.

SELECT COUNT(th.url) as total, tc.url, tc.json
FROM history th
JOIN cache tc
    ON th.url = tc.url
WHERE th.time > UNIX_TIMESTAMP()-3600
GROUP BY tc.url, tc.json
ORDER BY COUNT(th.url) DESC
LIMIT 30

Note that this query is semantically different from your query so you might get a different result. If that is a problem you may want to keep the LIMIT 30 construction in a subquery as before. You may also consider if you can add a similar limit to CACHE, is there an upper bound on how many CACHE rows you must investigate to get 30 rows in total?

INNER JOIN (SELECT cache.url, cache.json
            FROM cache
            ORDER BY ? LIMIT ?) tc


Which index will be used in this scenario? SQL Server 2014 Standard Edition

I need to find the number of flights that are to and from specific cities for certain months. E.g.

select count(*)
from flights
where flightTo_AirportCode = 'aaaa'
and flightFrom_Airportcode = 'bbbb'
and flightdate < '2016-04-01'
and flightdate > '2016-02-28' ;

The table schema is below.

I am trying to estimate if index modelA or index modelB (below) is preferable (it takes many hours to build the index, and disk space allows only one to exist at a time, so I am trying to look before I leap).

From my experience, either index will do. Am I right?

  create index [modelA] on flights (flightTo_AirportCode, flightFrom_AirportCode, flightDate)

  create index [modelB] on flights (flightDate, flightTo_AirportCode, flightFrom_AirportCode)

(Or, better, is there a binary index or advanced mechanism I can use to approach this?)

CREATE TABLE [dbo].[flights](
    [flightId] [uniqueidentifier] NOT NULL,
    [accountId] [uniqueidentifier] NULL,
    [flightDate] [datetime] NULL,
    [flightTo_AirportCode] [nvarchar](30) NULL,
    [flightFrom_AirportCode] [nvarchar](30) NULL,
    -- ... 45 more fields
    CONSTRAINT [PK_flight] PRIMARY KEY CLUSTERED
(
    [flightId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Index A is better for this query. When all the conditions in the WHERE are equality checks except one that is using a range condition or IN operator on a column, then that last column should be last in the index, after all the columns that have an equality check.

This allows the optimizer to use an index seek to the first row that matches the conditions and then traverse the index until it finds a row that doesn't match it. All the rows in between are a match, too.

So, the best index for this query would be either (to, from, date) (your model A) or (from, to, date).

The model B index has the date first so it is not the best, although it is still a covering index for the query. If this was used, the query plan would be almost the same. An index seek to find the first row that matches the range condition (date > '2016-02-28') and then traverse the index until it finds a row that doesn't match the date < '2016-04-01'. But all the rows in between do not necessarily match the 2 other conditions so they would have to be checked against these conditions and (possibly many of them) rejected.

So while the plans would be similar, the model A plan would have to only go through the part of the index that has all the needed rows and only them, while the model B plan would go through a (possibly much) larger part of the index.

It would also be best to use a 100% safe format for dates (YYYYMMDD).
And if you want the dates in March, you should use an inclusive-exclusive check:

AND flightdate >= '20160301' AND flightdate < '20160401'

Guaranteed to work with date and datetime types. Your current query will include also any row that has '2016-02-28' but a time different to '00:00:00' (can you guarantee that there isn't any?) which I assume you don't want. The inclusive-exclusive method will also work in leap years (reminding that 2016 is a leap year so there was a February 29th date as well which your query will return).

Read also these blog posts by Aaron Bertrand:

What do BETWEEN and the devil have in common?
Bad habits to kick : mis-handling date / range queries


Oracle DB hanging after upgrade to 11.2.0.3 The database is hanging randomly (with some leniency I would say almost each hour). Top session is:

select privilege#
from sysauth$
where (grantee#=:1 or grantee#=1) and privilege#>0
or
select /*+ connect_by_filtering */ privilege#, level
from sysauth$ connect by grantee#=prior privilege# and privilege#>0
start with grantee#=:1 and privilege#>0

In ash I saw these top wait events:high concurrency on event library cache: mutex X and latch: shared pool

This problem occurred after upgrading from 11.2.0.1 to 11.2.0.3.

These queries are almost always in top sessions but the wait event when the database is not hanging is CPU. In 11.2.0.1 they didn't appear. We are using DRCP.

select privilege#
from sysauth$
where (grantee#=:1 or grantee#=1) and privilege#>0

That SQL is executed as a user authenticates to the database upon login. This is mentioned in support doc 730066.1

Is there a storm of logins happening periodically?


PizzaShop - DB - Normalized Schema I am trying to make a simple DB in Access for a theoretical pizza shop(the db is for an assignment). What I am trying to do is link one type of pizza to one or more types of toppings. I just wanted a second opinion. Here's the schema:

First of all: Be concistent within your columnnaming! In Type you use ID, in Customer you use CustID and in Orders CusomerID. Same with ToppingID vs ToppingCode. And you dont have to use the Prefix Pizza in the Pizzas (imho, plural is also not that nice) table.

The relation Type is not clear to me. I guess you can put the OrderType directly into Orders.

You also can think about to remove the PizzaCode Column from Pizzas&Orders&Toppings. You can get this information from Orders join Order&Pizzas.

Edit: I guess Time is for the planned delivery time. Therefore you should name it like that. Otherwise you get confused with Date


Are there opensource hierarchical database management systems I was looking for a hierarchical database management system and the only one i came across was IBM's ims.Are there opensource systems that one can use?.

Since XML databases are hierarchical, you could look at those. I recently used BaseX and liked it a lot.


Ola Hallengren Index maintenance on servers with Availability Groups I'm configuring the Ola Hallengren scripts for Index maintenance on all servers in may Availability Group cluster which has several AG's and databases which are not in AG's.

If I select the "ALL_DATABASES" option, is the script intelligent enough to exclude the read only secondary replicas?

Yes, the script is intelligent enough.

This is explicitly stated in the FAQ:

  How should I configure IndexOptimize for an Availability Group?

  Indexes can only be rebuilt or reorganized and statistics only updated
  on the primary replica in an availability group. IndexOptimize has a
  check for this.

  The jobs should be configured identically and be enabled and scheduled
  on all the replicas.


"Normal" time for query execution I have a table in a MariaDB database with ~700,000,000 rows. I have indexed some columns of it.

My question is whether my DB behaves "normaly" or not - because I have not had previous experience with such an amount of data.

Take a look at these queries and please let me know if you think this is normal response time:

SELECT count( DISTINCT(QualityLevel) ) FROM patient_records;
+---------------------------------+
| count( DISTINCT(QualityLevel) ) |
+---------------------------------+
|                          265595 |
+---------------------------------+
1 row in set (1.248 sec)

This I think is great response time. However, look below:

select count(QualityLevel) from patient_records where QualityLevel>10.14;
+---------------------+
| count(QualityLevel) |
+---------------------+
|           700756562 |
+---------------------+
1 row in set (3 min 10.324 sec)

Not so great (I assume?). And, more:

select count(recordID) from patient_records where QualityLevel>10.14 and snpID='.';
+-----------------+
| count(recordID) |
+-----------------+
|        56627747 |
+-----------------+
1 row in set (23 min 53.028 sec)

Quite slow this one, although both columns (QualityLevel and snpID) are indexed.

I will need to build a web interface for these queries, but it can't happen that it takes 25 minutes to execute. What am I missing here? Maybe DB partiotining would help (don't know anything about it, so any suggestion would be highly appreciated).

My my.cnf file:

[mysqld]
datadir=/home/ssd/mysql_datadir
tmpdir=/home/ssd/mysql_tmdir
socket=/home/ssd/mysql_datadir/mysql.sock
innodb_buffer_pool_size=4GB
query_cache_type=1
query_cache_size = 10M
query_cache_limit=256k

[client]
port=3306
socket=/home/ssd/mysql_datadir/mysql.sock

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

My SHOW CREATE TABLE is:

patient_records | CREATE TABLE `patient_records` (
  `recordID` int(11) NOT NULL AUTO_INCREMENT,
  `MRD_sample_FORMAT_id` int(11) NOT NULL,
  `DataType` char(1) NOT NULL,
  `SequencingOrigin` char(1) NOT NULL,
  `ChrNo` varchar(2) NOT NULL,
  `ChrPos` int(10) unsigned NOT NULL,
  `snpID` varchar(20) NOT NULL,
  `NuclREF` varchar(500) NOT NULL,
  `NuclALT` varchar(3000) NOT NULL,
  `QualityLevel` float NOT NULL,
  `FilterString` char(1) NOT NULL,
  `InfoString` varchar(1000) NOT NULL,
  `GT` varchar(20) DEFAULT NULL,
  `AD` varchar(20) DEFAULT NULL,
  `DP` varchar(20) DEFAULT NULL,
  `GQ` varchar(20) DEFAULT NULL,
  `PL` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`recordID`),
  KEY `fk_patients_idx` (`MRD_sample_FORMAT_id`),
  KEY `ChrNo` (`ChrNo`) USING BTREE,
  KEY `snpID` (`snpID`),
  KEY `idx_patient_records_ChrNo` (`ChrNo`),
  KEY `position_chrom` (`ChrPos`),
  KEY `quality` (`QualityLevel`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=700756618 DEFAULT CHARSET=latin1 |

It would be helpful to have SHOW CREATE TABLE, but I will make some guesses...

You have INDEX(QualityLevel), and that index is smaller than the buffer_pool?
You have been doing a bunch of queries; these were not the first?  (Hence some things are cached.)
You did not use SQL_NO_CACHE to avoid the Query cache?

How much RAM do you have?  innodb_buffer_pool_size=4GB is rather small unless you have only 6GB.

The COUNT(DISTINCT ..) seems too fast -- probably caching helped.

When doing timing tests:

SELECT SQL_NO_CACHE ...   -- to avoid the Query cache.

and run the query twice -- to compensate for I/O caching.

Responding to comments

You should switch to InnoDB.  Then, with 250GB of RAM, set innodb_buffer_pool_size = 200G.  This will help InnoDB performance.  (Note: MyISAM does not use that at all; instead, it uses key_buffer_size, which should be set to about 50G for a MyISAM-only setup.)

In general, adding a useful index is worth doing -- in spite of the negative cost considerations (disk space an insert cost).

When building a composite (multi-column) index, the cardinality of the individual columns is irrelevant.  The index becomes a unit, the components of which are not noticed.

For this query:

select count(recordID) from patient_records
      where QualityLevel>10.14 and snpID='.';

the following is optimal in this order:

INDEX(snpID, ,      -- because of `=`
      QualityLevel, -- a range, so comes after `=`
      recordID)     -- tacked on to make it "covering"

A note about COUNT(recordID) -- that checks which rows have a non-NULL value for recordID.  This test is unnecessary since it is declared NOT NULL.  I suggest getting in the habit of simply saying COUNT(*).

With COUNT(*), INDEX(snpID, QualityLevel) would be as good.

To design the optimal indexes, you need to know the SELECTs, DELETEs, and UPDATEs.  You cannot pre-guess.  See my Cookbook .

Another note:  The order of WHERE clauses does not matter; the Optimizer tries all reorderings of the AND clauses.  It will try all reorderings of multiple tables JOINed together.  (LEFT may or may not limit what the Optimizer will try.)

need to build a web interface for these queries

It is passe to say "Exactly 56627747 widgets" in a web interface.  Even "About 56 million" is rarely used these days.  And that could be efficiently achieved by a nightly job that recomputes that, and other, statistics about your data.

If you do need both precision and speed, then build a Summary Table and query it.  That table could, for example, have daily counts for the number of widgets in various quality brackets.

Summary Tables are the key to billion-row datasets.


How to automatically shrink a SQL Server transaction log only if it exceeds a certain limit (e.g. 20 GB) Our database is set to FULL recovery mode and we take a database backup and a log backup every night. On several occasions we have noticed that the transaction log file is not truncated after the log backup. The log_reuse_wait_desc shows LOG_BACKUP. So we have to run couple of iterations for log backup and sometimes even dbcc shrinkfile command.

Is there any way we can run the dbcc shrinkfile command to shrink the log file every time it reaches 20 GB?

The predefined 'Shrink' maintenance plan is only for the .mdf file which I don't want to do. I believe we'll have to do it through t-sql only and use a trigger but I am unable to make it work. We have a relatively small database and we are running SQL 2012 standard edition. Any pointers you can provide would be immensely helpful.

Our database is set to FULL recovery mode and we take a database
  backup and a log backup every night.

I think you need to take log backups much more often than every night. Try every hour to see if that keeps your file growth in check. If not you may need to run them more frequently.

You could also deploy an SQL Agent Alert that triggers when the log percent used reaches a certain threshold. This alert could send an email or fire an agent job that runs a log backup job.

Also I wouldn't ever recommend automatically shrinking files.


mixing binlog formats between master and slave I have a pretty much straightforward Master --> Slave replication setup in MySQL 5.6 / Innodb.

Both my Master and Slave are setup in MIXED binlog format.

Now I need my slave to act as a master to plug a Java binlog connector that will retrieve the binlog to further feed a hadoop stack.
For this to work I have enabled the log-slave-updates variable

Thing is: that java connector will better work on row based binary log.

So my question is:

Can I safely change the binlog format on my slave for ROW ?
and keeping the MIXED format on the master ?

I Couldn't find any specifics about this in MySQL docs.

It might be in the manual:

  Each MySQL Server can set its own and only its own binary logging
  format (true whether binlog_format is set with global or session
  scope). This means that changing the logging format on a replication
  master does not cause a slave to change its logging format to match.
  (When using STATEMENT mode, the binlog_format system variable is not
  replicated; when using MIXED or ROW logging mode, it is replicated but
  is ignored by the slave.)

-- http://dev.mysql.com/doc/refman/5.6/en/binary-log-setting.html

That is "Yes" to your questions.

Note that ROW is the default for 5.7.  That is, ROW has proven itself to be the 'right' way to do replication.  Galera clustering can't even work without ROW.


bulk copy out huge table on sybase adaptive server anyone has a good approach or even strategy to bulk copy out single table with 500+ millions rows. the table encountered unitialized logical page, and we are looking to atleast salvage good records.

we are running sybase adaptive server version 12.5 on linux platform.

TIA

We have done very large table migrations successfully with Sqoop. Yes, there will be a bunch of setup that is needed but it is tuneable to run in multiple threads for faster performance.

From Comments:

Over the years, I have used many methods to extract data from sybase tables. BCP, IBM Optim (very proprietary), Apache MetaModel and Apache Sqoop. Using Sqoop I found is the most effective for very large tables. The setup for Sqoop would need some basic Hadoop libraries to run. Then you would install the sqoop server and the client software. All of this is not very difficult to do. Sqoop does not offically support Sybase but it does work really well. Here is a good starting point for installing Sqoop on a single node.


SQL Server 2012 Intellisense Support for Extended Events Using SQL Server 2012 with SP1 for some reason when try to use extended events programmatically using T-SQL. I am not able to see intellisense working? Just trying to check is this something on my end or MS has yet to support intellisense for extended events and few other things.

Not able to find anything specific for extended events intellinsense support in SQL Server BOL

For e.g. creating a new event session no events are popping up in intellisense for SQL Server? I know this was the case was SQL Server 2008 where intellisense was first introduced.

I haven't come across Intellisense support for XEs in SSMS yet. It will tell you when you have an error in your syntax (my common mistake was omitting ON SERVER). SQL Prompt 5 (latest version) has support for the control commands (CREATE EVENT SESSION ... ALTER EVENT SESSION, etc.) but not for the packages and events yet. For now I'd recommend just sticking with the metadata queries from Jonathan Kehayias to get a sense for the packages, events, predicates, maps, etc.

I think this is probably the case because Extended Events are still a work in progress and are undergoing such rapid change and development that more effort would have to be put into generating the Intellisense XML than they'd like to put into it until it's stabilized (from a feature perspective, not a reliability perspective).


SQL Server-MySQL master-master replication We are looking into setting up a master-master replicated MSSQL database and I am interested to hear of any potential pitfalls that we may run into along the way and generally any advice that you might have for us.

To expand on our situation a bit we currently have an internal SQL 2008 R2 server and a web based MySQL server. We use SSIS a lot to transfer the data between the two servers. This ends up with the data on the two servers being out of sync as the SSIS packages run hourly or nightly. We have investigated MSSQL->MySQL replication but we've never found a comprehensive solution.

This has brought us to what we are hoping to set up with the web using both the current MySQL and an additional replicated MSSQL database.

Specific areas that I'm interested in finding out more on are:

How does a master-master set up deal with an internet connection
being dropped? I've read that one node becomes read-only but I can't
seem to find much detail about this.
Is there anything to be wary of connecting from PHP to an MSSQL server?
What type of replication would you recommend? Bi-directional transactional seems to fit the bill quite well.

Thanks for your time

(I'm putting this in an answer, as it's way too long for a comment.)

We have a scenario similar to yours for our bug tracking system. We use it internally, of course, but customers can also submit issues through a page we created on our customer SharePoint site.

What we decided to do was host the database and website only at the office and provide external access from there (which we were already doing for some of our SaaS customers). If the internet totally bombs out (rare), it's more important that we can continue to work than for our customers to be able to submit new issues.

In your scenario, I don't know how critical the data is, how much data there is, or how important it is for external users to be able to write data.

Perhaps you could consider using a database at the alternate location as a read-only secondary, but direct all writes to the primary. While this will probably involve some application changes to separate read-only and read-write connections, this type of solution might be enough to satisfy the requirements for the small amount of time the office internet is down.

Regardless, I stick by my recommendation to not mix MySQL/SQL Server if you can avoid it. IMO, you'll be better off long-term by directing resources into proceeding with the existing migration plan, and hold off on developing a more robust replication solution until that stage of the project is complete.

Also, definitely try your best to avoid any master-master replication scenario. These can be highly non-trivial to configure and support at the best of times. The $ and time that will be spent developing and debugging a solution involving master-master heterogeneous replication will be astronomical, and probably won't ever work correctly 100% of the time (actually, probably nowhere close to that). Not that a built-in homogeneous replication solution will be perfect either, but at least in that case, you can call for customer support if something blows up and you don't know how to fix it; if you roll your own solution, you're on your own.


Should I migrate data using detach/copy/attach or through backup-restore-replay? I am about to embark on migrating database files to a new SAN (from an old SAN) abd I have a couple of options to implement this. (1) It was suggested that I look into the level of effort of restoring a full backup to a new database on the server. However, (2) my original plan was to copy the files from the old SAN to the new SAN by detaching and then reattaching the database.

My gut tells me that I'd rather detach, copy, and attach since it seems more fail-safe, but that may just be my naïvety. I don't want to miss a transaction or somehow "break something" in the process of renaming databases.

I guess my question is whether or not I am justified in my skepticism of the BACKUP-RESTORE-Replay option and what are other merits or risks of that option?

Personally, I would avoid the detach/attach mechanisms. Especially in SQL Server 2000, I just don't trust that you will always bring the server back up and be able to attach those files. I've heard plenty of stories where this didn't happen cleanly - just because you have a Plan B doesn't automatically make Plan A sensible.

With backup / restore, you don't risk having to go to Plan B. If the backup fails, your database is still up. If restore fails, your old database is still up. In both cases you can restore the operation of the original database and revisit the plan later. In addition to the extra security here over stopping SQL Server and/or detaching, this also means you can test the hoo-has out of the backup/restore methodology (assuming you currently have the space to perform the backups and another instance to test the restore). You can't really test the detach approach without detaching the databases or stopping SQL Server, and that's tough to do outside of a proper maintenance window. And finally, with the other approaches you can't even start copying the files until you've detached or brought SQL Server down. With backup/restore you can have the .bak file waiting on the new storage long before you take the last log backup and start your maintenance window.

One other benefit over the pull-the-drive-out-from-under-SQL-Server method: with backup/restore you can move various files to different drive letters than they were before. For example when we migrated to a new SAN, we were able to have more volumes, so we could move tempdb to T:\ (which didn't exist before), some of the data and log files to new drive letters, etc. to better utilize all the new I/O capacity we had. If you simply shut down SQL Server and then swap out the disks, you need to have the same drive letters and the same number of volumes.


Mysql Percona 5.6 slave lags gradually I have got into slave lag gradually say every minute it lags around 5secs on slave.

So it started morning 10AM the Seconds_behind_master was 13. Now at 4:30 PM it has reached to 560.

No variable changed. Not much selects are running in there, its all regular ones. In fact we have stopped few applications thinking those could cause slowness due to selects.

This seems to be strange for me. This had never happened before.

I have below variables already set to minimize IO load on disk.

> innodb_flush_log_at_trx_commit=0
> innodb_flush_method = O_DIRECT
> sync_binlog=0

I also have 2 more slave nodes. They are performing real good. And they are used heavily by selects on app end.

Any thoughts or any one have run into similar issue before?

New Update

Something found in network.

Transferring 1 GB file to slave0
mannoj@master:~$ scp mysql-bin.001539 mannoj@12.168.4.7:/home/mannoj/
 100% 1024MB  68.3MB/s   00:15

Transferring 1 GB file to slave1                                                                                mannoj@master:~$ scp mysql-bin.001539 mannoj@12.18.11.91:/home/mannoj/
 100% 1024MB 170.7MB/s   00:06

Transferring 1 GB file to newslave000
mannoj@master:~$ scp mysql-bin.001539 mannoj@12.18.19.1:/home/mannoj/
 100% 1024MB 204.8MB/s   00:05

The slave lag persist on slave0.

Final Update (I guess)>

I disabled Network consuming mysqlbinlog puller runs via cron. Seems to be draining seconds_behind_master from 700 to 490.

I have a few suggestions
SUGGESTION #1
You should use innodb_flush_log_at_trx_commit=2 instead. Why ?

With innodb_flush_log_at_trx_commit set to 0, a mysqld crash can lose up to 1 second of data.
With innodb_flush_log_at_trx_commit set to 2, an OS crash can lose up to 1 second of data. A mysqld crash can still keep that last second lingering in the OS until the tables and other moving parts of InnoDB are flushed to disk PROVIDED THE OS IS STILL UP.

See my post Is it safe to use innodb_flush_log_at_trx_commit = 2
SUGGESTION #2
You should use sync_relay_log=1 instead. Why ?
MySQL Documentation on sync_relay_log says:

Setting sync_relay_log to 0 causes no synchronization to be done to disk; in this case, the server relies on the operating system to flush the relay log's contents from time to time as for any other file.
Prior to MySQL 5.6.6, 0 was the default for this variable. In MySQL 5.6. and later, the default is 10000.
A value of 1 is the safest choice because in the event of a crash you lose at most one event from the relay log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).

Since you did not set sync_relay_log, relay logs would flush to disk every 10000 binary log events (the default setting). In that case, you may as well set sync_relay_log_info to 100.
If setting sync_relay_log and sync_relay_log_info produces too much disk I/O you can adjust them to your liking. It seems inappropriate to me to use 10000 as the setting.
SUGGESTION #3
On the Master, make sure sync_binlog is set to 1 if the application is low-to-medium write. If it is heavy write, use sync_binlog is set to 100. Same thing with setting sync_master_info
SUGGESTION #4
With more than one slave, it would do you some good to setup of Semisynchronous Replication. That will give you added assurance that at least one Slave is sync'd and can be promoted to Master.
Please read my Aug 05, 2011 post Is MySQL Replication Affected by a High-Latency Interconnect?. It includes instructions on how to set it up as well as how it differs from regular MySQL Replication (asynchronous).
SUGGESTION #5
If any Slave has log-bin and log-slave-updates enabled, disable them if the Slave is not a Master. Don't be surprised by this suggestion. I have a client who would do midday bulk loads and Seconds_Behind_Master increase 1 second every 2 seconds for 6 hours. Disabling the binary log made all that replication lag go away quickly.
SUGGESTION #6
For slave0, check the following

battery on the disk controller
network traceroute
switches

GIVE IT A TRY !!!


What is a storage need for MySQL index on Varchar(100) in InnoDB? I have a InnoDB table, which has EMail column (VARCHAR(100)), now I will run script which will hash some of that email addresses. I am wondering, if there is a difference in data storage (for example average of emails length is 20 characters and after hash it will be 64 characters), will be there a difference in index storage as well?

I have discussed having large PRIMARY KEYs for InnoDB before : What storage engine should I use for this MySQL table?. The effect would be bloated keys experiencing linear growth of all Secondary Indexes because the PRIMARY KEY reference from a Secondary Key Index Entry would also start getting bloated as well.

Looking at your question, you yourself are saying you will be expanding an email address from 20 to 64 characters. You will be bloating every non-unique index for the table in question that has an email address column. If the email address is itself the primary key, then all indexes for the table in question whether an index has an email address column or not. There are other viewpoints discussed in Mysql int vs varchar as primary key (InnoDB Storage Engine?

It is bad enough that a CHAR field is fast to read than a VARCHAR field but at the expense of having larger indexes (See my post What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?). Doing this  will certainly introduce configuration challenges including

Having a Large Enough InnoDB Buffer Pool (innodb_buffer_pool_size)
Having Large Enough Transaction Logs (innodb_log_size_size)
Having Sufficient Read and Write IO Threads (innodb_read_io_threads,innodb_write_io_threads)
Having a Large Enough Log buffer (innodb_log_buffer_size)

Even if email address is not indexed, data pages for that InnoDB will still have bloating and possibly fragmentation nonetheless. Since you are hashing, I can presume you must be indexing it.


T-SQL - How to split (char separated) string into rows and columns What is the best way to split a char separated string into rows and columns?

Here is my input string:

!1;100;10;200;0;500;2;1000;30!2;100;3;500;1;2000;5

And here is my desired output table:

Id  Value  Count
1   100    10
1   200    0
1   500    2
1   1000   30
2   100    3
2   500    1
2   2000   5

Best Regards,

It is not neccesary to modify Jeff Moden's function. I have devised a little bit sophisticated way of achieving the result just using the original function.

First Here it is the original Moden's function:
(Source: http://www.sqlservercentral.com/articles/Tally+Table/72993/)

CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;

And the query for obtaining the required result (I have created a CTE just for clarity)

DECLARE @string VARCHAR(8000)
SET @string = '!1;100;10;200;0;500;2;1000;30!2;100;3;500;1;2000;5'

;WITH CTE AS
(
SELECT
SUBSTRING(A.Item,1,CHARINDEX(';',A.Item)-1) As Id,
B.Item
FROM dbo.DelimitedSplit8K(@string, '!') AS A
CROSS APPLY dbo.DelimitedSplit8K(A.Item, ';') AS B
WHERE A.Item <> '' --to avoid blank first row because the first !
)

SELECT Id, [Value], [Count]
FROM
(
SELECT Id, Item As [Value],
ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Id) As RowNumber,
LEAD(Item,1,0) OVER(PARTITION BY Id ORDER BY Id) As [Count]
FROM CTE
) As T
WHERE RowNumber % 2 = 0

EDITED:
I realized that my solution assummed that id value was ordered or that the order in the original string was not neccesary to be preserved in the result. Below the general solution (note that I changed the first number in the string for testing the situation)

DECLARE @string VARCHAR(8000)
SET @string = '!3;100;10;200;0;500;2;1000;30!2;100;3;500;1;2000;5'
;WITH CTE AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT 0)) As RN,
CAST(SUBSTRING(A.Item,1,CHARINDEX(';',A.Item)-1) AS Varchar(400)) As Id,
B.Item
FROM dbo.DelimitedSplit8K(@string, '!') AS A
CROSS APPLY dbo.DelimitedSplit8K(A.Item, ';') AS B
WHERE A.Item <> '' --to avoid blank first row because the first !
)

SELECT Id, [Value], [Count]
FROM
(
SELECT Id, Item As [Value],
ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Id) As RowNumber,
LEAD(Item,1,0) OVER(PARTITION BY Id ORDER BY Id) As [Count]
,FIRST_VALUE(RN) OVER(PARTITION BY Id ORDER BY Id) AS FV
FROM CTE
) As T
WHERE RowNumber % 2 = 0
ORDER BY FV

http://sqlfiddle.com/#!6/31ccd/3


How can I Get Value of Max(col) in a greatest n per grouped by into Declared variable. MS SQL sever I am looking for how to set the value of  @MaxItemSeqNum
to the returned result of the column MAX(dbo.[Table1].[Item_Seq_number]) in my select statement.
I need this in order to insert the next record with the next sequence for that grouped item.
I already have a surrogate primary key that auto-increments and is used to identify all items in the Table. Batch_ID can have duplicates and I group by these and then select the max of itemSeqNum to insert the next sequence itemSeqNum + 1. So if I have 5 records with the same batch_Id I am expecting to get 5 as a result and increment it by 1.

DECLARE @MaxItemSeqNum Int

SELECT Batch_Id,MAX(dbo.[Table1].[Item_Seq_number])
FROM Table1
WHERE Batch_Id = 1
Group By Batch_Id

how to set the value of @MaxItemSeqNum to the returned result

Either

SET @MaxItemSeqNum = ( SELECT MAX([Table1].[Item_Seq_number])
                       FROM Table1
                       WHERE Batch_Id = 1
                       Group By Batch_Id );

or

SELECT @MaxItemSeqNum = MAX([Table1].[Item_Seq_number])
                        FROM Table1
                        WHERE Batch_Id = 1
                        Group By Batch_Id;

fiddle

The last variant is more useful - it allows to assign the values to more than one variable in one statement.


mariaDB not caching queries cached by mysql I'm migrating my database (MySQL 5.1.73) to a new server, and I am trying to replace it with mariadb (5.5.5-10). Globally mariadb seems much faster, but there is a couple of queries that it refuses to cache where mysql does.
This is a big problem because those queries are used LOTS (there are sort of on the home page so will be executed pretty much every time the user interact).

I thought mariadb was a drop in replacement for mysql, so I'm surprised of this behavior ? As anyone any idea why such a different behavior or how can I force the cache to be used (I tried SELECT SQL_CACHE but that doesn't change anything.

Here is the mysql configuration

    +------------------------------+------------+
    | Variable_name                | Value      |
    +------------------------------+------------+
    | binlog_cache_size            | 32768      |
    | have_query_cache             | YES        |
    | key_cache_age_threshold      | 300        |
    | key_cache_block_size         | 1024       |
    | key_cache_division_limit     | 100        |
    | max_binlog_cache_size        | 4294963200 |
    | query_cache_limit            | 1048576    |
    | query_cache_min_res_unit     | 4096       |
    | query_cache_size             | 16777216   |
    | query_cache_type             | ON         |
    | query_cache_wlock_invalidate | OFF        |
    | table_definition_cache       | 256        |
    | table_open_cache             | 64         |
    | thread_cache_size            | 8          |
    +------------------------------+------------+

And the mariadb one

+--------------------------------+----------------------+
| Variable_name                  | Value                |
+--------------------------------+----------------------+
| aria_pagecache_age_threshold   | 300                  |
| aria_pagecache_buffer_size     | 134217728            |
| aria_pagecache_division_limit  | 100                  |
| aria_pagecache_file_hash_size  | 512                  |
| binlog_cache_size              | 32768                |
| binlog_stmt_cache_size         | 32768                |
| have_query_cache               | YES                  |
| host_cache_size                | 228                  |
| innodb_disable_sort_file_cache | OFF                  |
| innodb_ft_cache_size           | 8000000              |
| innodb_ft_result_cache_limit   | 2000000000           |
| innodb_ft_total_cache_size     | 640000000            |
| join_cache_level               | 2                    |
| key_cache_age_threshold        | 300                  |
| key_cache_block_size           | 1024                 |
| key_cache_division_limit       | 100                  |
| key_cache_file_hash_size       | 512                  |
| key_cache_segments             | 0                    |
| max_binlog_cache_size          | 18446744073709547520 |
| max_binlog_stmt_cache_size     | 18446744073709547520 |
| metadata_locks_cache_size      | 1024                 |
| query_cache_limit              | 1048576              |
| query_cache_min_res_unit       | 4096                 |
| query_cache_size               | 16777216             |
| query_cache_strip_comments     | OFF                  |
| query_cache_type               | ON                   |
| query_cache_wlock_invalidate   | OFF                  |
| stored_program_cache           | 256                  |
| table_definition_cache         | 400                  |
| table_open_cache               | 400                  |
| thread_cache_size              | 100                  |
+--------------------------------+----------------------+

There are many different "caches"; not all of them have 'cache' in their name.  The most important is innodb_buffer_pool_size.  An that one is important only if you are using InnoDB (which you should be) instead of MyISAM.  Furthermore, was the Engine of individual tables changed during the upgrade?

Check the table Engines and buffer_pool_size, then report back.  BTW, the buffer_pool should be about 70% of available ram.  And show us the non-cached queries, together with SHOW CREATE TABLE for them.

I see that the Query cache is set the same way.  However, that is usually bad for an active production server.  But that is another topic.

"Drop in replacement" -- mostly it is.  But, as you found out, it is "faster" in some ways, "slower" in others.  Even if you moved from MySQL 5.1 to MySQL 5.5, you might see similar better/worse scenarios.


Postgres permissions - schema not showing using \dt I have no experience in postgres, and am trying to get a staging site correctly configured.

The database has a schema data in the database drupal.
I imported the database from development using (as postgres user):

psql drupal <drupal.dump.sql

When I connect as the drupal user, and execute \dt, it only shows the regular tables and not the tables in the schema "data".

I see those "missing" tables when I execute \dt *.*

I am probably missing some permissions to be added to the drupal user. Any help greatly appreciated!

Postgres version: 9.3.17

You have to put the schema "data" in your search_path if you want the tables from that schema to show up in a \dt run with no arguments.  (If your import worked, then you already have permissions to the schema, unless you changed users)


SELECT 180 records took 2 seconds My table is simple:

CREATE TABLE `od_form_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `formid` int(11) DEFAULT NULL,
  `items` mediumtext,
  `template` mediumtext,
  `draftid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=420 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT

And it only contains 180 rows:

mysql> select count(*) from od_form_items;
+----------+
| count(*) |
+----------+
|      180 |
+----------+
1 row in set (0.00 sec)

The longest string is just 26314 bytes:

mysql> select length(items) as a from qiban.od_form_items order by a desc limit 1;
+-------+
| a     |
+-------+
| 26314 |
+-------+
1 rows in set (0.01 sec)
mysql> select length(template) as a from qiban.od_form_items order by a desc limit 1;
+------+
| a    |
+------+
| 6556 |
+------+
1 rows in set (0.01 sec)

But in mysql slow_log, it took over 2 seconds to select all records:

mysql> select start_time, query_time, sql_text from mysql.slow_log order by start_time desc limit 10;
+---------------------+------------+--------------------------------------------+
| start_time          | query_time | sql_text                                   |
+---------------------+------------+--------------------------------------------+
| 2017-11-24 10:34:40 | 00:00:02   | SELECT * FROM `od_form_items`              +---------------------+------------+--------------------------------------------+
1 rows in set (0.01 sec)

How should I optimize it? What mysql parameter should I check? Thanks!

On an ordinary spinning drive, you can get about 100 IOPs.  If all 180 rows needed to reach off-row to get items and none needed for template, then that is 180 + miscellany to get SELECT * 180 times.  That's about 2 seconds.

SSD drives would be faster.

Compressing, in the client, those two columns, then storing them in BLOBs, would shrink things a bunch.  This would probably lead to fewer IOPs.

It seems like populating memcached would be a one-time action, so the 2 seconds would not be a big deal?


How to add a new listener on a new port and restrict it to one instance only I am running oracle 11.2.3 and we have a server with multiple instances running. All of them are registered to port 1521 with the default listener. Now we are required to make one instance available on a new port. I was wondering if there is a way to add a new listener to a new port and restrict it to register only one instance so that this listener can not make connections for other instances.
Current Listener Configuration:
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
    )
    )
  )

See the documentation about configuring multiple listeners in listener.ora, and about static service registration for each. https://docs.oracle.com/en/database/oracle/oracle-database/19/netag/configuring-and-administering-oracle-net-listener.html#GUID-C3C40DBA-4282-41E1-9562-4B8B10947C4E
LISTENER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
    )
  )

QA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1526))
    )
  )

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=prod.us.example.com)
      (ORACLE_HOME=/oracle12c)
      (SID_NAME=prod))
  )

SID_LIST_QA=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=qa.us.example.com)
      (ORACLE_HOME=/oracle12c)
      (SID_NAME=qa))
  )

If you are working with Oracle Enterprise Edition, then another option would be to use Oracle Connection Manager; it would allow you to restrict client access by IP address and database service name. I wrote a white paper on it several years ago, but the basics are still the same with the latest versions: https://pmdba.wordpress.com/2013/12/01/deploying-an-oracle-11gr2-connection-manager/


Data sync from different SQL Server versions databases So I have a SQL Server 7.0 database that can not be upgraded. it is only allowed to have data read and written in it.
I created a SQL Server 2008 R2 database which is the same structure. I want to synchronize the data between both so whatever happens on the SS7.0 happens in the SS2008 R2, and vice versa, is there a way to do so?
I'm not asking for a ready answer, I just want a trail to follow, tips and such. So far I only managed to export a copy of the data with bcp, but I can't do it each time

You can find a lot of articles about MS-SQL Server 7.0 replication in Technet and MSDN.

Two-Way Replication with SQL Server 7.0

Microsoft SQL Server 7.0 Replication Made Easy

Replication for Microsoft SQL Server Version 7.0


Sorting Table and get Position of row I have a Table with 3 rows. Player, Time, Server.

Now, I want 2 things and I don't know how.

For Server there are 3 worths available. 1, 2 and 3. Server and Player are a Primary Key together. User is a VARCHAR, Server and Time are Strings. I use 1,2 and 3 to make i easier to understand.

1st I want to sort the table by time and get the position of a specific player, but only from 1 Server.

Example:

User Time Server
2    10   1
1    50   1
2    60   3
1    20   2
1    100  3
2    70   2

Now, want to get which position the user 2 has

If I sort by time and server = 1

user 2 would be number 2
user 1 number 1

If server = 2

user 2 would be 1
user 1 would be 2

Then I want to get the User position, if I sort by the sum of the time of a user.
In this case, user 1 would be 1 (Sum of time: 170) and user 2 would be 2 (Sum of time:100).

I hope you can help me!
BTW, I want to use this in Java ;)

~java4ever/Robin

QUERY #1

  position the user 2 has if I sort by time and server = 1

SET @given_user = 2;
SET @given_server = 1;
SET @pos = 0;
SELECT * FROM
(
    SELECT (@pos:=@pos+1) pos,User
    FROM playerstats
    WHERE server = @given_server
    ORDER BY time DESC
) A
WHERE User = @given_user;

QUERY #2

  if I sort by the sum of the time of a user

SET @pos = 0;
SELECT (@pos:=@pos+1) pos,User,TotalUserTime FROM
(
    SELECT User,SUM(time) TotalUserTime
    FROM playerstats
    GROUP BY User
) A ORDER BY TotalUserTime DESC;

YOUR SAMPLE DATA

mysql> DROP DATABASE IF EXISTS mydb;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> CREATE DATABASE mydb;
Query OK, 1 row affected (0.01 sec)

mysql> USE mydb
Database changed
mysql> CREATE TABLE playerstats
    -> (
    ->     User INT,
    ->     Time INT,
    ->     Server INT
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.25 sec)

mysql> INSERT INTO playerstats VALUES
    -> (2, 10,1),
    -> (1, 50,1),
    -> (2, 60,3),
    -> (1, 20,2),
    -> (1,100,3),
    -> (2, 70,2);
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM playerstats;
+------+------+--------+
| User | Time | Server |
+------+------+--------+
|    2 |   10 |      1 |
|    1 |   50 |      1 |
|    2 |   60 |      3 |
|    1 |   20 |      2 |
|    1 |  100 |      3 |
|    2 |   70 |      2 |
+------+------+--------+
6 rows in set (0.01 sec)

mysql>

QUERY #1 EXECUTED

mysql> SET @given_user = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @given_server = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @pos = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM
    -> (
    ->     SELECT (@pos:=@pos+1) pos,User
    ->     FROM playerstats
    ->     WHERE server = @given_server
    ->     ORDER BY time DESC
    -> ) A
    -> WHERE User = @given_user;
+------+------+
| pos  | User |
+------+------+
|    2 |    2 |
+------+------+
1 row in set (0.09 sec)

mysql>

QUERY #2 EXECUTED

mysql> SET @pos = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT (@pos:=@pos+1) pos,User,TotalUserTime FROM
    -> (
    ->     SELECT User,SUM(time) TotalUserTime
    ->     FROM playerstats
    ->     GROUP BY User
    -> ) A ORDER BY TotalUserTime DESC;
+------+------+---------------+
| pos  | User | TotalUserTime |
+------+------+---------------+
|    1 |    1 |           170 |
|    2 |    2 |           140 |
+------+------+---------------+
2 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

UPDATE 2014-12-26 15:32 EST

QUERY #3

SET @given_user = 2;
SET @pos = 0;
SELECT * FROM
(
    SELECT (@pos:=@pos+1) pos,User,TotalUserTime FROM
    (
        SELECT User,SUM(time) TotalUserTime
        FROM playerstats
        GROUP BY User
    ) A ORDER BY TotalUserTime DESC
) AA
WHERE User = @given_user;

QUERY #3 EXECUTED

mysql> SET @given_user = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @pos = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM
    -> (
    ->     SELECT (@pos:=@pos+1) pos,User,TotalUserTime FROM
    ->     (
    ->         SELECT User,SUM(time) TotalUserTime
    ->         FROM playerstats
    ->         GROUP BY User
    ->     ) A ORDER BY TotalUserTime DESC
    -> ) AA
    -> WHERE User = @given_user;
+------+------+---------------+
| pos  | User | TotalUserTime |
+------+------+---------------+
|    2 |    2 |           140 |
+------+------+---------------+
1 row in set (0.06 sec)

mysql>


How to select any couple of digits except '00' using mysql regex subset syntax? I just found out that mysql doesn't support full regular expressions syntax. I have to select any couple of digits except of 00 (so, 11, 54, 02, are ok, but not 00).

Normally I would use a negative lookahead like this:

(?!00)[0-9]{2}

But in mysql it is not supported. So how could I do it?

Direct solution:

(0[1-9])|([1-9][0-9])

It is simple enough for do not look for shortening or another optimization, I think.


Error deleting FileTable - HRESULT = '0x80070490' When trying to delete a FileTable, the following error occurs:

  Message 33405, Level 16, State 7, Line 1
  Error during operation on an object table Discard FileTable. (HRESULT = '0 x80070490 ').

Due to the urgency we do not expect a return of support from Microsoft, but it seems that the error occurred due to attempt to put the database offline.

We solve the error as follows:

Since we had a base equal to the base model which showed the error, made the copy of the data base with an error (except FileTable) for the base model and recreate the FileTable. It was in this way that we solve.

Because we applied the solution before the return of support, they could not diagnose prescision.


Can not drop/change a view I've got a view which accidentally (don't know how) lost it's name.

Now I can not drop it any longer because pgadmin III throws an error
Error: syntax-error at the end of input
Line 1:DROP VIEW public

Trying to rename it causes the error
Error: syntax-error at "to"
Line 1:ALTER TABLE RENAME TO NewName

The view definition is

CREATE OR REPLACE VIEW  AS
 SELECT ...
   FROM table a;

ALTER TABLE OWNER TO postgres;
GRANT ALL ON TABLE  TO postgres;

Made a backup of the database, restrored it.

In the new database the invalid, nameless view has been dropped by pgadmin.


Determining what algorithm MYSQL View is using When creating a MySQL View, the algorithm is set to UNDEFINED.

How do I see which algorithm MySQL is choosing for the query ?

I tried using "EXPLAIN", but it is not obvious from the results what algorithm was used for the query.

If the view is being processed with the TEMPTABLE algorithm, the view will be treated by the optimizer as if it were written as a subquery in the from clause (derived table) and should show up in the EXPLAIN output as a derived table.
Here's an example of a view that was explicitly defined with the TEMPTABLE algorithm.  Two tables show up in the query plan, although this view was just defined as SELECT * from a single table.
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |   33 | NULL  |
|  2 | DERIVED     | task       | ALL  | NULL          | NULL | NULL    | NULL |   33 | NULL  |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+

When the view is defined with the UNDEFINED algorithm, the optimizer...

prefers MERGE over TEMPTABLE if possible
— http://dev.mysql.com/doc/refman/5.6/en/view-algorithms.html (also previous versions ~sqlbot)

Aggregate functions/group by/having, distinct, limit, unions, scalar subqueries, and views that return literals only (not from a table) require TEMPTABLE be used... a view without those should be processed as MERGE consistently when the algorithm on the view definition is the UNDEFINED algorithm.
Because of the nature of the two view algorithms, the only one that can use indexes on the underlying tables to find rows matching the outer WHERE -- not one inside the view definition, but the one SELECT ... FROM view WHERE << here >>.  If the EXPLAIN output shows those indexes being used the way you would typically hope, then you're getting MERGE.
I generally define views as MERGE explictly.  The server will accept and save the definition, but somewhat quietly change it back to UNDEFINED and throw a warning if it can't be actually be processed with MERGE.
mysql> CREATE ALGORITHM=MERGE VIEW priority_counts AS SELECT priority_id, COUNT(1) AS quanity FROM task GROUP BY priority_id;
Query OK, 0 rows affected, 1 warning (0.12 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------------+
| Level   | Code | Message                                                                       |
+---------+------+-------------------------------------------------------------------------------+
| Warning | 1354 | View merge algorithm can't be used here for now (assumed undefined algorithm) |
+---------+------+-------------------------------------------------------------------------------+
1 row in set (0.08 sec)

SHOW CREATE VIEW subsequently shows ALGORITHM=UNDEFINED.
Of course, when MERGE is accepted, it does show in the view definition, as does TEMPTABLE when it's explicitly declared that way.


Not getting a result set in SSRS 2008r2 from an iSeries DB2 SQL stored procedure After reading 'Defining an SQL procedure' section of DB2 UDB for iSeries SQL Programming Concepts V5R2 and 'Returning result sets from stored procedures' section of i5/OS Information Center, Version 5 Release 4, I created the following stored procedure:

CREATE PROCEDURE TESTDAT.REPORT (IN DATESTART DATE, IN DATEEND DATE)
LANGUAGE SQL READS SQL DATA
BEGIN
    DECLARE CSR1 CURSOR WITH RETURN FOR
    SELECT *
    FROM  SYSIBM.SYSDUMMY1;

    OPEN CSR1;
    RETURN;
END

Next I read 'Example 1: Calling a stored procedure that returns a single result set' section of the same i5/OS Information Center, Version 5 Release 4 and tried to call this procedure from SqlDbx client (via ODBC) or SSRS report (via IBMDASQL provider) using below statement:

CALL TESTDAT.REPORT (DATE('11/01/2013'), DATE('11/03/2013'));

It executed successfully, but did not return the resultset.
Executing this thru iSeries navigator successfully returns 2 statements - select and declare cursor.
Calling

SELECT * FROM  SYSIBM.SYSDUMMY1;

returns IBMREQD column with 1 row of 'Y'.
What am I missing from the documents above?

Update:

Generated SQL for this procedure includes other options besides already discussed:

SPECIFIC TESTDAT.REPORT
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
SET OPTION  ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX

Can any of them affect returning of resultset to SSRS?

One thing that you are missing in your stored procedure declaration is the DYNAMIC RESULT SETS option. The default value for that option is 0, may be that's what confuses the client software. Whether it (the client software) can even handle cursors returned by a stored procedure is another story.


Recover encrypted Oracle database A client fired their DBA. He used OWM to add a wallet to their Oracle database and secure a few necessary columns of tables that they need. I can see the walled using:

orapki wallet display -wallet /opt/oracle/home/owm/root

I do not know the password. The ex-employee will not divulge the password. With root access to the system, is it possible to remove the wallet or change the wallet's password?

NOTE: Not part of this problem, but for reference... Oracle Support told the client they are out of luck. I told them they are out of luck. They want a third (and fourth (and fifth)) opinion.

You can't remove the wallet or change the password, but if the wallet is set to auto-open (or it is open) you should be able to export the data out of that database and create a new one that does not have an associated wallet.