Chained MySQL replication from Master->Master replicated pair This is a pictorial view of what I have:

Master 1<----->Master 2
|                     |
|                     |
V                     V
Slave 1         Slave 2

The masters are never written to at the same time, rather Master 2 is a standby server. Both Masters have log-slave-updates turned on in my.cnf.

If a table is created in a replicated DB on Master 1, it is present on Master 1, Master 2 and Slave 1. It is NOT present on Slave 2.

In the same way, if a table is created in a replicated DB on Master 2, it is present on Master 1, Master 2 and Slave 2. It is NOT present on Slave 1.

Why is this the case? Is it a limitation of MySQL in how it stops master/master replicated servers from creating replication loops if both have log-slave-updates turned on?

Is there a way this can work?

To answer my own question, the "fix" is to change binlog_format to STATEMENT. It was previously MIXED.

Not entirely sure of the reason behind this - MySQL should be intelligent when using MIXED mode about what is replicated as statement and what is replicated as row.


Small site / online database I have to develop a small website and I wanted to understand which language to use.
The main purpose of this site is to access various tables, for example customer table, orders, warehouse, etc. by people with login / password.
Each user must be part of a group with specific functionalities (for example, they can access only for reading or make insertions / changes and no deletions, others only administration, etc.).
I have good knowledge of PHP and long ago I did something similar accessing 4 tables using CodeIgniter and MySQL.

I could proceed with CodeIgniter as the problem is basically identical, but I wanted to take one more step and, after analyzing various types of PHP and CMS frameworks, I deduced that the best solution is to switch to OctoberCMS (which is based however on the Laravel framework).
Initially I had opted to switch to the use of Laravel (it is more advanced than CodeIgniter) but with OctoberCMS theoretically I save a good part of work (long and laborious for the various pages and controls) while still leaving me a complete customization of the contents and the possibility of also inserting code native php for particular functions.
I prefer to be the owner of the data and that they are not hosted by third parties also because I already have some tables on a MySQL database and I would like to access them too.

Do any of you know OctoberCMS to suggest me if it could be the right solution?
Or is it better to change to something else?
I have already checked in the forum but it seems to be no trace of this problem.
I also have knowledge of Microsoft Access and it would be nice to develop it with this tool since with a few clicks you can do many things ... but there is a problem, it cannot be put online (I am not only referring to tables but also to forms and reports).
What I am looking for, could be a tool similar to Microsoft Access (access to tables and masks to insert data and display table lists) but that can be used on the Internet to share and simultaneous use from people around the world.
In my opinion it is better not to use a Remote Desktop / Terminal Server solution, it doesn't seem serious or functional.

Thank you for the help.
Davide.

October CMS is perfect for this (disclaimer: I'm one of its maintainers). It has a robust roles / permissions system, is highly extendable, based on Laravel, and extremely easy to get started quickly with. Check out the builder plugin to kickstart your development process (it's a visual scaffolding tool to build your own plugins). It's also extremely easy to use October without the frontend "cms" module entirely and just use the backend functionality for custom Web applications.

Feel free to join the discord to ask more specific questions: https://octobercms.com/support


Dynamic Data Masking performance overhead I'm thinking of using the new dynamic data masking that has been introduced with SQL Server 2016 as part of our GDPR project . Does anyone else have experience of it? I am particularly interested if there is a performance overheard.

No, there is no overhead involved (not heard of any yet). I have used it in my Azure SQL Database and works like a charm.

Data Masking is a quick solution for data security as you can control the users who can view the data, but on a broader aspect this cannot be the sole security solution.

Please read below links for better understanding:

Dynamic Data Masking (Microsoft documentation)
Encrypting SQL Server: Dynamic Data Masking by Robert Sheldon


How do I shrink the innodb file ibdata1 without dumping all databases? InnoDB stores all tables in one big file ibdata1.

After dropping a big table, the file is keeping its size no matter how big the table was.
How can I shrink that file without having to dump and re-import the whole database (which has several Hundred GB in total)?

I think the reason is because you are still able to rollback the dropping. In my case I don't need to.

This is one of the most controversial topics I have ever dealt with over the years as a MySQL DBA and in the DBA StackExchange.
To put it mildly, there is simply no other way to shrink ibdata1. With innodb_file_per_table disabled, every time you run OPTIMIZE TABLE on an InnoDB table, ibdata1 grows rapidly. Data that are dropped using DROP TABLE and DROP DATABASE cannot be rolled back because they are DDL, not DML. I believe Oracle and MSSQL can rollback DDL. MySQL cannot do that.
There are several classes of information that reside in ibdata1

Table Data
Table Indexes
Table MetaData
MVCC Control Data
Double Write Buffer (Background write to prevent reliance on OS caching)
Insert Buffer (Managing changes to non-unique secondary indexes)

Using innodb_file_per_table=1 will allow you to create new tables with table data and table indexes being created outside ibdata1. You could extract any tables still inside ibdata1 using ALTER TABLE ... ENGINE=InnoDB; or OPTIMIZE TABLE but that will leave that big gaping unused space in ibdata1.
Notwithstanding, you must clean up the InnoDB infrastructure. I already wrote StackExchange posts on how and why to do this:

May 21, 2012 : How large will a MySQL database be relative to the dump file?
Apr 01, 2012 : Is innodb_file_per_table advisable?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Feb 04, 2011 : MySQL InnoDB - innodb_file_per_table cons?
Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine?

Good News
You only have to dump the data, reload one more time and never revisit this issue again. Running OPTIMIZE TABLE afterwards will indeed shrink the .ibd tablespace file for any InnoDB table.


Automatic MYSQL failover We have a wordpress environment however this is not a wordpress question. Our scenario is that front Ends connect to a MYSQL backend (MYSQL VM1) which is replicated to a backup vm (MYSQL VM2). The replication is Master - Master.

What we're looking for is a MYSQL HA cluster with automatic failover where if VM1 dies / is unresponsive, connection fails over to VM2. Anything you'd recommend ?

I prefer Galera for HA.  Master-Master, especially if you write to both masters, has a lot of problems.  Read other Questions on this topic.


Can Postgres synchronous replication stall a cluster if secondary/standby crashes? In a setup with Postgres synchronous replication, let's say one of the secondary/standby machines crashes, will that lock/halt the master? That is, since the Postgres master always waits for confirmation from the secondary/standby before it commits the write transaction, then will the master simply stop functioning if the secondary/standby is inaccessible? How does Postgres deal with this case?

In a setup with Postgres synchronous replication, let's say one of the secondary/standby machines crashes, will that lock/halt the master?

Yes, by design. See the manual:

synchronous_standby_names
synchronous replication

  will the master simply stop functioning if the secondary/standby is inaccessible?

It'll still serve read-only queries, but commits will block and not return until the replica comes back up. If you can accept some transactions not being synchronously replicated and have SET LOCAL synchronous_commit = off in those transactions they'll commit without blocking.

  How does Postgres deal with this case?

As documented above you can configure multiple synchronous replicas to mitigate potential production impact. PostgreSQL does what you tell it to, and doesn't confirm commits until they're replicated.

If you don't want that, use asynchronous replication. The idea of "synchronous replication" where the master can keep on working while the replica is offline is nonsensical.


Procedure cache (plan cache) size I'm developing an application with many, dinamycally-assembled, complicated SQL queries. I'd like to keep the query plans in the procedure cache for as long as it is possible. To prevent discarding the query plans from the procedure cache (because of memory pressure), I'd like to allocate enough memory to the procedure cache. According to this only 10% of the "visible target" memory in the 4Gb-64GB range is used for the procedure cache; I'd like this rate to be higher.

Is there any way to have some control on the size of the procedure cache? SQL Server 2008 or 2012; maybe some Enterprise Edition features?

Also, does this "10% of the visible target memory" rule also apply, if I explicitly set the min. memory for the SQL Server?

(I am aware of the KEEP PLAN / KEEPFIXED PLAN hints; I'm also aware of the advatages of preparing the statements over ad-hoc queries, so please don't recommend these possibilities. I'm specifically curious about controlling the procedure cache size.)

You can't control plan cache size manually. The following Connect item describes both the fact that they won't be adding a knob any time soon and also that Resource Governor might be marginally useful in a few narrow use cases:

http://connect.microsoft.com/SQLServer/feedback/details/293188/amount-of-ram-for-procedure-cache-should-be-configurable

However, as I've alluded to in the comments, there are other ways you can make the plan cache more effective (e.g. use the "optimize for ad hoc workloads" setting). Kimberly explains this in greater detail, but essentially, this setting places much smaller stubs in the cache until the same plan is used more than once, and only then does it cache the whole thing. Under the default configuration, and especially in cases where a lot of dynamically assembled SQL is in place, single-use plans often waste a lot of space in the cache. If you reduce the amount of wasted space these occupy, you leave more room for plans that are used more than once, and those are precisely the ones you want to keep around anyway.


Does "alter table … set data type" functionally differ from "alter table … type"? The docs show [SET DATA] TYPE which implies the SET DATA part is optional, but the 9.1 release notes also imply that SET DATA TYPE may be different:

  Allow ALTER TABLE ... SET DATA TYPE to avoid table rewrites in appropriate cases

I'm trying to update a column on a billion-row table from int to bigint and I'm at the four-hour mark. I'm curious if I should cancel the transaction and restart it using the SET DATA TYPE form instead of the plain TYPE form that I used.

Does SET DATA TYPE do something different (and hopefully faster)?

Keywords in square brackets in the synopsis of SQL commands in the manual are optional. And in this case just noise. Add them or leave them, no functional difference. (The verbose form SET DATA TYPE conforms to standard SQL.)

bigint occupies 8 bytes, integer occupies 4 bytes. A table rewrite is inevitable. The manual:

  As an exception, when changing the type of an existing column, if the
  USING clause does not change the column contents and the old type is
  either binary coercible to the new type or an unconstrained domain
  over the new type, a table rewrite is not needed; but any indexes on
  the affected columns must still be rebuilt.

Bold emphasis mine. Those are the "appropriate cases" that don't trigger a table rewrite since Postgres 9.1.


Performance: Left joining same table multiple times Let's say I've the following table:

id | name  | created_by  | updated_by | deleted_by
--------------------------------------------------
1  | alice | NULL        | NULL       | NULL
2  | ben   | NULL        | 3          | 1
3  | kane  | 2           | NULL       | 3
4  | ali   | 1           | 1          | NULL
5  | peter | 1           | 1          | 3
6  | rose  | 1           | 1          | 5
.
.

Approach 1: I'd run a query like this:

SELECT
u.*, a.name as 'creator', b.name as 'updator', c.name as 'deletor'
FROM users u
LEFT JOIN users a on u.created_by = a.id
LEFT JOIN users b on u.updated_by = b.id
LEFT JOIN users c on u.deleted_by = c.id
ORDER BY u.id
LIMIT 10

If the table has 1000-10000 records, primary id, is the query bad or good?

Approach 2: Or is it better to fetch without left joining, then on application level, I do another query to fetch the creator/updator/deletor, like so:

Query 1:

 SELECT
u.*
FROM users u
ORDER BY u.id
LIMIT 10

Query 2:

SELECT
u.name
FROM users u
WHERE `id` IN ($ids) # $ids is an array of all created_by+updated_by+deleted_by from query 1
ORDER BY u.id
LIMIT 10

Approach 1 will be better in your scenario, as it will give the result by one query run and seems to be properly joined as required by the business, though same table is joined multiple times.An index on id column will perform better.

Approach 2 with order by will require some more space in temp db for processing and the IN operator will degrade the performance in query 2 if the #ids has a lot of values returned within it(say there are large number of updated,deleted or created users returned by query 1).


Error 1064 during import When I try to import my database on the production server, I get:

  SQL запрос:

CREATE TABLE `articles` (
  `id_art` int(11) NOT NULL AUTO_INCREMENT,
  `id_section` int(11) NOT NULL,
  `id_ct` bigint(20) NOT NULL DEFAULT '0',
  `fesid` int(11) NOT NULL DEFAULT '0',
  `date_art` datetime(6) DEFAULT NULL,
  `title_art` varchar(250) NOT NULL,
  `meta_art` longtext,
  `short_art` text NOT NULL,
  `full_art` longtext NOT NULL,
  `foto_art` varchar(100) DEFAULT NULL,
  `foto_art_text` text,
  `coordinates_art` longtext,
  `hot` tinyint(1) NOT NULL,
  PRIMARY KEY (`id_art`),
  KEY `articles_fesid_c163d690_uniq` (`fesid`),
  KEY `articles_id_ct_af67450c_uniq` (`id_ct`),
  KEY `articles_id_section_8f87bacd_uniq` (`id_section`)
) ENGINE=MyISAM AUTO_INCREMENT=4060 DEFAULT CHARSET=cp1251;

  Ответ MySQL: Документация

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to
use near '(6) DEFAULT NULL,
  `title_art` varchar(250) NOT NULL,
  `meta_art` longtext,
  ' at line 6

What causing this and do I fix that?

I tried to import with Phpmyadmin and by mysql -u user -p basename < filename.sql

I made export with mysqldump

This line seems to be causing the problem

`date_art` datetime(6) DEFAULT NULL,

This works

`date_art` datetime DEFAULT NULL,

Is the version of the database you exported from the same as you are importing to?

https://stackoverflow.com/questions/2572209/why-doesnt-mysql-support-millisecond-microsecond-precision

http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html


Give every user 3 default 'bookmark' entries I've got a table users and a table bookmarks. The table bookmarks obviously contains pages the user wants easy access too.

But, there's a new requirement now: every user gets 3 unmutable bookmarks. One of the bookmarks (be it an unmutable one or a user-specific one) is marked as homepage. Always.

I'm thinking about 2 solutions, but I'm not sure which one to choose:

1) Add those 3 records for every user and use triggers or application logic to enter those 3 records automatically when a new user is created.

2) Keep those 3 'records' out of the database altogether and fix it in the application (on the 'bookmarks' page). This solution has a great disadvantage: by default, one of the 3 unmutable bookmarks is selected as the user's homepage. The user can change their homepage to one of the other 2 unmutable bookmarks, or a bookmark of their own. I would have no place to store this data, or I would have to insert the 3 unmutables in case the user makes a change. But that sounds hackish.

What would you do? Is inserting 3 records for all users a design flaw?

Would you be OK with having a single table that has these three immutable bookmarks, and then using a view that joins this table with the user's real bookmarks?

immutable_bookmarks
-------------------
  id
  name
  URL (or whatever this is a bookmark to)

user_bookmarks
--------------
  id
  name
  user_id
  URL

And then define the view as:

create view user_bookmarks_v as
  select id, name, URL, '-1' as user_id
  from immutable_bookmarks
  union
  select id, name, URL, user_id
  from user_bookmarks

(syntax may not be 100% correct MySQL)

When your application needs to get a user's bookmarks, you can do something like this:

select *
from user_bookmarks_v
where user_id = -1 OR user_id = $USER_ID

This gives you the advantage of having the immutable bookmarks in the database, so that if you ever need to change them, it's very easy, as opposed to having them in the application and needing to roll out an application patch to all installations. You can also change the set of immutable bookmarks by adding or removing records and instantly having them accessible to all users immediately. There's no special triggers or procedures, you do need to add a new table and a new view.

This also has the advantage of not needing to insert 3 records for every user.

The disadvantage is the somewhat ugly (in my opinion) where user_id = -1 to capture the immutable bookmarks. It can probably be refined, I just don't have the time at the moment.

Whoops, I just realised this might not work if users can modify the 3 immutable bookmarks... Hmmm... @Sherlock: let me know if this makes sense, your "edit" requirements were a little unclear and this suggestion might not work for you.


Is Query Performance different for different versions of SQL Server? I have fired 3 update queries in my stored procedure for 3 different tables. Each table contains almost 2,00,000 records and all records have to be updated. I am using indexing to speed up the performance. It is quite working well with SQL Server 2008. A stored procedure takes only 12 to 15 minutes to execute. (updates almost 1000 rows in 1 second in all three tables)

But when I run the same scenario with SQL Server 2008 R2 then the stored procedure takes more time to complete execution. It's about 55 to 60 minutes. (updates almost 100 rows in 1 second in all three tables). I couldn't find any reason or solution for that.

I have also tested same scenario with SQL Server 2012, but the result is same as above.

Here is my 3 table update query in stored procedure.

if (select COUNT(*) from table where conditions)>0

begin

    update table1
    set Coulmnname= @ColumnName
    where Conditions

    update table2
    set Coulmnname= @ColumnName
    where Conditions

    update table3
    set Coulmnname= @ColumnName
    where Conditions

end

execution plan

Image 1

Image 2

Yes it will be different.  Every version will have tweaks to the optimiser, or code path, or caching algorithms or something.  I would expect the later versions to run faster than the earlier ones.

  I am using indexing to speed up the performance.

No, you're not.  If every row in the table has to be updated then an index is, at best, irrelevant.  If the index contains columns which you are updating then the index's pages also have to be updated incurring additional write overhead.

Have you checked the query plan for the three different instances?  My guess is the 2008R2 has less memory available and the optimiser's choosing to spill to TempDB, or the operating system's forcing paging to the virtual memory pagefile.  Or it may be that the transaction log files for the 8R2 instance started out small and what you're seeing is an artifact of autogrowth to accommodaate six million log records.

I would always recommend batching to my devs when processing this many records.  Update the first N records in cluster key sequence.  Then do the next N, and so forth until all 2M are done.  "N" can be altered to give the best rows/minute on your particular installation, though the tens of thousands tends to be about right.  In this way log files remain smaller, transactional locking is reduced and it is restartable in the case of a failure.


Estimating query time Is it possible in MySQL to know beforehand query execution time? I mean how long it will take to execute the query.

Generally its worthwhile to know how long any query can take to execute.

At the moment, problem at hand is Converting CHAR to BIT (1.2GB Table size, MyISAM, indexes not on the columns I want to change, 3GB RAM)

If you go the route I suggested in your previous post I'm going to say < 10 minutes.  But again, soooo much that isn't given will affect the wall time.  Is this a dedicated MySQL server or is other stuff going on?  Specifically things that will be affecting the disk i/o?  How fast are the disks? Are they SSD? Is it write through or write back cache?

In short there is no "explain" for operations like this that will give you a time value.

Don't forget to disable/enable keys during the modifications.

Report back and let us know the results.


Can PAGELATCH Waits cause increase in SOS_SCHEDULER_YIELD wait in SQL Server? We have a high concurrency web server application on which we have some amount of PAGELATCH_UP & PAGELATCH_SH Waits due to tempdb contention. But recently we have noticed the SOS_SCHEDULER_YIELD Waits also going up.

Could the PAGELATCH Waits cause the SOS_SCHEDULER_YIELD Wait to go up?

Also, the web server application and sql server are hosted on the same AWS cloud server.

Could the PAGELATCH Waits cause the SOS_SCHEDULER_YIELD Wait to go up?

No.

SOS_SCHEDULER_YIELD wait time is spend waiting on an available scheduler (CPU).  After any other resource wait, a session will accumulate Signal Wait Time while waiting for an available scheduler.  The Signal Wait Time is tracked along with the previous wait, and included in the reported wait_time, but broken out in the signal_wait_time column in the Instance Level and Session Level Wait Stats DMVs.

So there may be a relationship between the two waits, but it's not that the PAGELATCH causes the SOS_SCHEDULER_YEILD.  Check the signal_wait_time on the PAGELATCH wait.  If the signal_wait_time is a significant proportion of the wait_time, then the PAGELATCH waits are really CPU waits.

Note that when a session is waiting for a page latch, the session that currently owns the page latch may itself be waiting for CPU, so some of the non-signal wait time may also be related to CPU waits.

Put another way, excessive CPU use (or insuffient CPU resources) can cause both SOS_SCHEDULER_YEILD waits and cause other short waits to drag out as no scheduler is available at the end of the resource wait.


Error on simple Select statement: Subquery returned more than 1 value When I query a table

select *
from tableName

it returns error msg

  Msg 512, Level 16, State 1, Line 2
  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, = ,>, >= or when the subquery is used as an expression. The statement has been terminated.

How is this possible I can get this error on simple selection of a table?

Table Definition:

CREATE TABLE [dbo].[TableName](
    [Column1] [int] NOT NULL,
    [Column2] [int] NOT NULL,
    [Column3] [int] NOT NULL,
    [Column4] [bit] NULL,
    [Column5] [numeric](15, 2) NULL,
    [Column6] [numeric](15, 2) NULL,
    [Column7] [datetime] NULL,
    [Column8] [datetime] NULL,
    [Column9] [varchar](5) NULL,
    [Column10] [varchar](5) NULL,
    [Column11] [varchar](15) NULL,
    [Column12] [datetime] NULL,
    [Column13] [varchar](5) NULL,
    [Column14] [varchar](17) NULL,
    [Column15] [varchar](256) NULL,
    [Column16] [numeric](18, 2) NULL,
    [Column17] [tinyint] NULL,
    [Column18]  AS ([dbo].[udf_ChargePointer]([Column1],[Column17])),
 CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED
(
    [Column1] ASC,
    [Column2] ASC,
    [Column3] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].TableName  WITH CHECK ADD  CONSTRAINT [CK_TableName]
CHECK  ((case when len([Column11])>(2) AND isnumeric(replace([Column11],',','A'))=(1) then (1) else (0) end=(0)))
GO

ALTER TABLE [dbo].TableName ADD  DEFAULT ('01/01/1900') FOR [Column12]
GO

ALTER TABLE [dbo].TableName ADD  DEFAULT ('') FOR [Column13]
GO

That particular error happens when you are using a subquery in such a way that only one row can be used but more than one row is returned.  For example using a subquery to return a column value.

Since this error only occurs when you have subqueries it is very likely that your tableName is actually a view.  You can easily check this by running the following command.

SELECT OBJECTPROPERTYEX(OBJECT_ID('dbname.schema.tableName'),'BaseType')

If it returns a V then it's a view.  You can then get the text of the view by using the following:

EXEC sp_helptext TableName


optimize design for a table with 50 columns I have a table with 50 column and one of my friend has told me to split the table in 4 different table to save 12 column in each table. He proposed a design to have Master table and refer the PK of that master table in other four table, and then create NONCLUSTERED INDEX on the new table in all column as the limit is 900 bytes.

The idea was to improve the performance and convert key lookup to seek. I am not sure that it will really improve performance using join rather I think it hamper the performance, Please suggest me what to do here.

EDIT

Existing Table Test

CREATE TABLE Test
(
  A BIGINT,
  B BIGINT - FK --From some other table in DB
 ,C1 -Varchar(100),C2 -Varchar(50),C3 -Varchar(50),,C4 -Varchar(50),C5 -Varchar(50),C6 -Varchar(50),C7 -Varchar(50),C8 -Varchar(50) ,C9 -Varchar(50),C10 -Varchar(50)
 ,D1 -Varchar(100),D2 -Varchar(50),D3 -Varchar(50),,D4 -Varchar(50),D5 -Varchar(50), ,D6 -Varchar(50), D7 -Varchar(50),D8 -Varchar(50),D9 -Varchar(50),D10 -Varchar(50)
 ,E1 -Varchar(100),E2 -Varchar(50),E3 -Varchar(50),,E4 -Varchar(50),E5 -Varchar(50),,E6 -Varchar(50), E7 -Varchar(50),E8 -Varchar(50) ,E9 -Varchar(50) ,E10 -Varchar(50)
  F1 -Varchar(100),F2 -Varchar(50),F3 -Varchar(50),,F4 -Varchar(50),F5 -Varchar(50), ,F6 -Varchar(50),F7 -Varchar(50),F8 -Varchar(50) ,F9 -Varchar(50),F10 -Varchar(50)
)

Now the proposed design is to have four table with column name as

CREATE TABLE TestC
(
  PkId BIGINT,
  A BIGINT - FK --From the Master table Test
  ,C1 -Varchar(100),C2 -Varchar(50),C3 -Varchar(50),,C4 -Varchar(50),C5 -Varchar(50),
   C6 -Varchar(50),C7 -Varchar(50),C8 -Varchar(50) ,C9 -Varchar(50),C10 -Varchar(50)
)

Similar way other tables are, and the these tables are used in reading data. With big table Test here, I think covering index to use with big table Test.

EDIT2

I have requirement to read the data from these table after there are added and and in some case they will be update with status column set to 0 after some days and these rows will be no longer used and the table will have new fresh entry for the same user.

The target is to optimize the data read operation and also minimize the time in insert and update, which one would be a better choice here? Please help.

I believe it's necessary to refer what kind of data your table is storing. Let's say that from C1-C10 you're keeping one group of data and it may be possible that the data in C1-C10 may be redundant (ie, it's possible that you may have the same row of data from C1-C10 throughout your Master Table). Hence, it's wasteful to have redundant data because you're wasting storage space to store the same repeating row of data.

So, what your friend advising you to do is called Normalization. Normalization is a process in which we systematically examine relations for anomalies and, when detected, remove those anomalies by splitting up the relation into two new, related, relations. Normalization can also be thought of as a trade-off between data redundancy and performance. Normalizing a relation reduces data redundancy but introduces the need for joins when all of the data is required by an application such as a report query.

Recall, the Relational Model consists of the elements: relations, which are made up of attributes.

A relation is a set of attributes with values for each attribute such that:

Each attribute (column) value must be a single value only.
All values    for a given attribute (column ) must be of the same
data type.
Each    attribute (column) name must be unique.
The order of attributes    (columns) is insignificant
No two tuples (rows) in a    relation can be    identical.
The order of the tuples (rows) is insignificant.

So, if there's a possibility that the data may be redundant, you may need to separate it to a different table as mentioned by your friend. It will be a pain to join the table together but consider it as a tradeoff towards performance and storage capacity.


Does Oracle run queries to completion after SQLdeveloper disconnects? The query is a

CREATE TABLE t as SELECT .....;

statement. Can I reliably count on Oracle to complete this after I disconnect?

After I re-connect, how can I check on the status of this query?

Most likely, it will finish a DDL command behind the scenes, but I wouldn't bet my job on it.  What if your CTAS action runs out of tablespace?  You would never see the error.  If you have an unrealiable Internet connection, then the best solution is probably

Simple/basic solution

Use VNC or RDP to "jump" to a desktop or server that is in your datacenter. Run SQL Developer or whatever client on that desktop OS.  If you get disconnected, you just reconnect VNC and you're where you left off.  Your DB client never breaks its connection to the Oracle server.

Way cooler Oracle Scheduler "nohup" solution.

But if you can't do that, or just want to do something cooler with Oracle, you could fire off your DDL inside a Scheduler Job in Oracle.  This is basically like using NOHUP in Unix if you're familiar.  You invoke DBMS_SCHEDULER.CREATE_JOB and then a job slave process will proceed on the Oracle server running as your same user to complete the work.  When you submit the job, it will return immediately with no error or confirmation other than "Anonymous block completed.", which just means that you've queued up an asynchronous job.  If you get disconnected, it won't matter.  You can log back in and check the status of the job via the USER_SCHEDULER_JOB_RUN_DETAILS built-in view.

I just did this in SQL Developer (note, user needs to have CREATE JOB privilege, and obviously CREATE TABLE, too):

-- submit this as a background job
BEGIN
  dbms_scheduler.create_job (
      job_name => 'MY_BACKGROUND_JOB'
    , job_type => 'PLSQL_BLOCK'
    , job_action => 'BEGIN EXECUTE IMMEDIATE ''CREATE TABLE t AS SELECT * FROM all_objects''; END;'
    , enabled => TRUE
    , auto_drop => TRUE
  );
END;

...

-- Waited here a few seconds/minutes here for the job to complete.

...

-- Couple of checks to make sure the table got created
SELECT created FROM user_objects WHERE object_name='T';

CREATED
-------------------
06/09/2014 18:06:42

SELECT count(*) FROM t;

  COUNT(*)
----------
     59907

Here's the status check.  Note the status check says SUCCESS and error 0, so all good.

-- Check on last run of job with this name
SELECT * FROM
( SELECT job_name, status, error#, actual_start_date, log_date
  FROM user_scheduler_job_run_details
  WHERE job_name='MY_BACKGROUND_JOB'
  ORDER BY log_date DESC )
WHERE rownum=1;

I ran the job again just for kicks and I got a FAILED with error 955 which is expected since the table t already exists.


Cannot install SQL Server 2012 I'm trying to install SQL Server 2012 on Windows Server 2012 Standard and got this message:

SQL Server Setup has encountered the following error:

The resource 'Microsoft.SQL.Chainer.PackageData.Package.xml' failed to load
from 'E:\PCUSOURCE\x64\Microsoft.SQL.Chainer.PackageData.Package.dll'.

Error code 0x85650003.

I applied the latest updates, but I still get this error. What should I do?

Summary text below:

--------------------
Overall summary:
  Final result:                  Passed
  Exit code (Decimal):           0
  Start time:                    2015-01-28 18:18:18
  End time:                      2015-01-28 18:18:37
  Requested action:              RunRules

Machine Properties:
  Machine name:                  WINDOWS191
  Machine processor count:       2
  OS version:                    Future Windows Version
  OS service pack:
  OS region:                     United States
  OS language:                   English (United States)
  OS architecture:               x64
  Process architecture:          64 Bit
  OS clustered:                  No

Product features discovered:
  Product              Instance             Instance ID                    Feature                                  Language             Edition              Version         Clustered

Package properties:
  Description:                   Microsoft SQL Server 2012
  ProductName:                   SQL Server 2012
  Type:                          RTM
  Version:                       11
  SPLevel:                       0
  Installation location:         C:\en_sql_server_2012_enterprise_edition_with_sp1_x64_dvd_1227976\x64\setup\
  Installation edition:          Enterprise

User Input Settings:
  ACTION:                        RunRules
  CONFIGURATIONFILE:
  ENU:                           true
  FEATURES:
  HELP:                          false
  IACCEPTSQLSERVERLICENSETERMS:  false
  INDICATEPROGRESS:              false
  INSTANCENAME:                  <empty>
  PID:                           *****
  QUIET:                         false
  QUIETSIMPLE:                   false
  RULES:                         SCCCheckRules
  UIMODE:                        Normal
  X86:                           false

 Configuration file:            C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\20150128_181817\ConfigurationFile.ini

Rules with failures:

Global rules:

There are no scenario-specific rules.

Rules report file:               C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\20150128_181817\SystemConfigurationCheck_Report.htm

I can see lot of Cannot be found messages in errorlog like

(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\1033_ENU_LP\redist\VisualStudioShell\SQLSysClrTypes\x86\SQLSysClrTypes.msi' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\1033_ENU_LP\redist\VisualStudioShell\SQLSysClrTypes\SQLSysClrTypes.msi' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\1033_ENU_LP\redist\VisualStudioShell\VSSetup\x86\vs_setup.msi' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\1033_ENU_LP\redist\VisualStudioShell\VSSetup\vs_setup.msi' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\1033_ENU_LP\redist\VisualStudioShell\VSIntSetup\x86\vs_setup.msi' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\1033_ENU_LP\redist\VisualStudioShell\VSIntSetup\vs_setup.msi' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\x64\setup\x64\unused' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\x64\setup\unused' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\x64\setup\x86\unused' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\x64\setup\unused' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\1033_ENU_LP\x64\setup\sql2008support\x64\SqlSupport.msi' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\1033_ENU_LP\x64\setup\sql2008support\SqlSupport.msi' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\1033_ENU_LP\x64\setup\x64\SqlDom.msi' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\1033_ENU_LP\x64\setup\SqlDom.msi' does not exist

This makes me think that the package is nt complete and while you were converting ISO file to executable file all files related to installation were not extracted.

You are trying to install SQL Server 2012 enterprise and Windows 8 machine not windows server. Its not supported on windows 8 please see hardware and software requirements but it wont stop you from installation.

If you are installing SQL Server 2012 on windows 8 you MUST read this support article before proceeding


Mysqldump multiple tables with where clauses in single transaction Suppose, we have 2 tables: t1,t2 in database db:
Expected the below code to dump tables based on conditions specified in the where clauses:

mysqldump --login-path=login_path \
    --single-transaction db t1 \
    --where="user_id>200" , t2 --where="id>200" > /tmp/users_data.sql

However the following error is popped:

  mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM t1
  WHERE id>200 Unknown column 'id' in 'where clause' (1054)

How to dump multiple tables with where clauses  using mysqldump correctly? If it's not possible, is there a workaround?

MySQL dump has only a single where clause which makes the use hard in two tables like you do.

One way would be in t1, create a generated column called id that is effectively user_id.

ALTER TABLE t1
ADD id UNSIGNED INT GENERATED ALWAYS AS (user_id),
ADD INDEX id_key ( id)

Adjust type to match the type of user_id.

ref: https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html


MySQL / InnoDB: Does daily optimization improve performance? Would performing daily optimizations of tables increase performance for MySQL and InnoDB? I have never did this before, but I have seen this module for Drupal http://drupal.org/project/db_maintenance, and it looks promising.

PS: As I learned, OPTIMIZE TABLE liberates overhead. However, I double checked the overhead column in mysql and it is all empty for my innodb tables ... So, I guess this may not be necessary.

MyISAM
Doing OPTIMIZE TABLE mydb.mytable; performs two basic operations
ALTER TABLE mydb.mytable ENGINE=MyISAM;
ANALYZE TABLE mydb.mytable;

This definitely cleans up table fragmentation and computes fresh statistics for indexes
InnoDB
Doing OPTIMIZE TABLE mydb.mytable; performs two basic operations
ALTER TABLE mydb.mytable ENGINE=InnoDB;
ANALYZE TABLE mydb.mytable;

While this eliminates fragmentation, ANAYLZE TABLE is complete useless for InnoDB. I wrote about this a long time ago:

Jun 21, 2011 : From where does the MySQL Query Optimizer read index statistics?
Oct 16, 2011 : Suddenly have to rebuild indexes to prevent site from going down

If your data growth rate is very small (or in other words, your dataset stays basically the same size for months at a time), then defragmenting tables would just be overkill. You should probably focus on tuning InnoDB's other aspects (See my Aug 04, 2011 post: Optimizing InnoDB default settings)
I also have posts in the Drupal StackExchange on opimizing MySQL

Jun 07, 2011 : How do I convert a database from MyISAM to InnoDB?
Apr 15, 2011 : What would be the optimal MySQL configuration for a Drupal 7 site?


Modifying legacy database tables that use composite primary keys Suppose you have an SQL Server database used by a legacy application that has many tables with composite primary key columns (and thus, composite foreign key columns as well). Instead of composite primary key columns, a single primary key column is desired for use by other, more-modern applications, but since the legacy application cannot be changed, neither can these composite primary key columns.

My initial solution involves the following:

Adding a new identity column to each table with composite primary key columns, as well as adding unique non-clustered indexes to these identity columns.
Adding new nullable integer columns to each table with composite foreign key columns, as well as foreign key constraints to the corresponding new identity columns and non-clustered indexes to these integer columns.
Adding AFTER INSERT/UPDATE triggers to these tables to update these new integer columns when new rows are inserted or existing rows are updated.

Given the above scenario, is my solution an effective way to modify these SQL Server database tables with existing composite primary key columns to allow for a single unique column to be introduced to allow for other applications to use instead? Are there any storage or performance issues to consider?

Adding new columns to tables might break the legacy app, because DML might be expecting a certain number of columns of certain types in a certain order in each table.

There aren't really any elegant solutions for this kind of problem. You can make new tables for the new apps and duplicate the data with triggers, but that creates unneccessary complexity and of course duplication. You can make new keys in new tables and use views to present the data to the new apps as one table, but then you're stuck with only using procedures for inserts and updates from the new apps.

In the long run you will probably be better off throwing out the legacy app.


How to check who has permission to execute a stored procedure? I have two environments/servers. Each has two databases, say DATABASE_A and DATABASE_B. There is a stored procedure in DATABASE_A (in both environments) that queries DATABASE_B and also calls multiple stored procedures on DATABASE_B. Most of those procedures are in the dbo schema, but one procedure is in another schema, say SCHEMA_X. That procedure reads from various tables in dbo and then truncates a table in SCHEMA_X before repopulating that table. That table and that procedure are the only two items in SCHEMA_X.
Now - all the above is applicable to both environments.
If I go to SSMS > relevant server > DATABASE_B > Programmability > Stored Procedures > SCHEMA_X.stored-proc-name > right-click > Properties > Permissions
Then nothing is listed. If I click to view schema permissions, again nothing is listed. This is again in both environments, but in one environment a client application presents an error saying it does not have permission to execute the stored proc. I tried brute force by granting the client's user group execute permissions on that schema. That seemed to take things further as the next error related to being unable to see the table, or not having permissions to access the table - which I could probably resolve by granting further permissions around insert/delete/update on that schema.
But my point is that initially both environments presented in the same way - at least in that spot that I checked (permissions against the stored procedure). So how should I debug this issue to determine why a client program in one environment is able to execute the stored proc (and effect the truncate/insert into the table) while the other reports it does not have permission? Is there no single comprehensive query that lists for you all permissions within a database?

I would start by checking whether cross database ownership chaining is involved
You should be aware that this can introduce serious security problems
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-cross-database-access-in-sql-server
I have had success with the scripts provided by Louis Davidson for working out effective permissions but I don´t know if his ambitions extend to cross database permissions
link for reference
https://www.red-gate.com/simple-talk/blogs/calculating-a-security-principals-complete-effective-rights/
Have you runa trace or extended events session to see the specific place you get the permissions error ?


DB2: What's the fast way to drop a big partitioned table? We got a partitioned table with 100000000+ rows, when I drop it it takes 10+ hours and finally failed.

how to speed drop command up ?

You could issue a TRUNCATE against the table. Or detach the partitions and do the same.

There are also some pointers from DB2 veteran Ember Crooks about deleting data in one of her blog entries. I find her tips and tricks quite helpful.


MySQL Conditional Counter Based Other Columns? I have a query that returns purchases for all customers in a store across a date range. It works fine, but now I've been asked to modify the results so only the first purchase per customer per day is returned. I need the SELECT statement to calculate a column that means "This is the customer's Nth purchase for the day." The data is sorted by customer name and date already, so when the customer name or date changes, I want the counter variable to reset to 1.

 ----------------------------------------------------------------
| Customer Name | Product | PurchaseDate | PurchaseNumberForDate |
 ----------------------------------------------------------------
| Customer A    | ...     | 2019-04-01   | 1                     |
| Customer A    | ...     | 2019-04-02   | 1                     |
| Customer A    | ...     | 2019-04-03   | 1                     |
| Customer A    | ...     | 2019-04-03   | 2                     |
| Customer A    | ...     | 2019-04-03   | 3                     |
| Customer B    | ...     | 2019-04-03   | 1                     |
| Customer B    | ...     | 2019-04-03   | 2                     |
| Customer B    | ...     | 2019-04-04   | 1                     |
 ----------------------------------------------------------------

I have tried using MySQL variables, but I cannot figure out how to reset the counter conditionally when the customer name or purchase date change. If I could get the PurchaseNumberForDate correctly calculated, I will use this as a subquery with another query  that will select WHERE PurchaseNumberForDate = 1.

I have found plenty of examples using COUNT() and @var := @var+1, but I haven't found one based on multiple conditions. Is this possible with MySQL?

SELECT t.*,
CASE WHEN CONCAT(t.CustomerName, CHAR(0), t.PurchaseDate) = @group
     THEN @num:=@num+1
     ELSE @num:=1
     END PurchaseNumberForDate,
@group:=CONCAT(t.CustomerName, CHAR(0), t.PurchaseDate) groupname
FROM datatable t, (SELECT @group:='', @num:=0) vars
ORDER BY t.CustomerName, t.PurchaseDate

Variant without additional field:

SELECT t.*,
CASE WHEN CONCAT(t.CustomerName, CHAR(0), t.PurchaseDate) = @group
     THEN @num:=@num+1
     ELSE @num:=1
     END + CONCAT(CHAR(0), (@group:=CONCAT(t.CustomerName, CHAR(0), t.PurchaseDate))) PurchaseNumberForDate
FROM datatable t, (SELECT @group:='', @num:=0) vars
ORDER BY t.CustomerName, t.PurchaseDate;

fiddle


Why does referencing a variable in a join predicate force nested loops? I came across this issue recently and couldn't find any discussion of it online.

The query below

DECLARE @S VARCHAR(1) = '';

WITH T
     AS (SELECT name + @S AS name2,
                *
         FROM   master..spt_values)
SELECT *
FROM   T T1
       INNER JOIN T T2
         ON T1.name2 = T2.name2;

Always gets a nested loops plan

Attempting to force the issue with INNER HASH JOIN or INNER MERGE JOIN hints produces the following error.

  Query processor could not produce a query plan because of the hints
  defined in this query. Resubmit the query without specifying any hints
  and without using SET FORCEPLAN.

I found a workaround which does allow hash or merge joins to be used - wrapping the variable in an aggregate. The plan generated is significantly lower costed (19.2025 vs 0.261987)

DECLARE @S2 VARCHAR(1) = '';

WITH T
     AS (SELECT name + (SELECT MAX(@S2)) AS name2,
                *
         FROM   spt_values)
SELECT *
FROM   T T1
       INNER JOIN T T2
         ON T1.name2 = T2.name2;

What is the reason for this behaviour? and is there a better workaround than the one that I found? (that perhaps doesn't require the extra execution plan branches)

I've tried your query on a SQL 2012 instance and trace flag 4199 seems to fix the issue. With it enabled I get a merge join for a total cost of 0.24 and none of the extra branches.

The specific KB article for this issue is Performance issues occur when the join predicate in your query has outer reference columns in SQL Server 2005 or in SQL Server 2008

To further qualify, TF 4199 enables all optimizer fixes.  See this link for more information. Enabling everything at once can have weird side-effects, so if you can find a specific fix it might be better to enable the fix on it's own.

You can enable a trace flag on a per query basis using OPTION (QUERYTRACEON 4199);


may i reduce the restore time with pgsql 9.2? I need your help about how to reduce time to restore process for a big dump (400GB)

We have a database that takes weeks to restore due to the single threaded nature of index generation on PostgreSQL. Please i need your help to figure out a way that we can restore this quicker, maybe by disabling index generation at load time and adding the indexes back later or some better trick.

sample pg_dump command:

pg_dump --compress=0 -bo -F c --lock-wait-timeout=1500 -h $HOST -p $PORT $DBNAME | lbzip2 > $DB-$TIMESTAMP.bz2

sample pg_restore command:

pg_restore -Ov -j 2 -h $HOST -p $PORT --dbname=$DBNAME $RESTOREFILE

-j option does not help us since it helps the part that doesn't take long (backup restore) but not the part that does take a long time (index generation) - this ticket is to figure out a way to streamline the process so index generation is done separately on an already working database or to speed up index generation, on PG 9.2

I'd like a clear procedure for removing index generation from the restoration process and doing it afterwards, to not block usage of the DB.

Is possible do that? what is your opinion about that?

pg_restore has an option to run the time-consuming parts of the restore, such as the index rebuild process, with multiple "jobs".
From the pg_restore documentation for PostgreSQL 9.2:

-j number-of-jobs
--jobs=number-of-jobs
Run the most time-consuming parts of pg_restore — those which load data, create indexes, or create constraints — using multiple concurrent jobs. This option can dramatically reduce the time to restore a large database to a server running on a multiprocessor machine.
Each job is one process or one thread, depending on the operating system, and uses a separate connection to the server.
The optimal value for this option depends on the hardware setup of the server, of the client, and of the network. Factors include the number of CPU cores and the disk setup. A good place to start is the number of CPU cores on the server, but values larger than that can also lead to faster restore times in many cases. Of course, values that are too high will lead to decreased performance because of thrashing.
Only the custom archive format is supported with this option. The input file must be a regular file (not, for example, a pipe). This option is ignored when emitting a script rather than connecting directly to a database server. Also, multiple jobs cannot be used together with the option --single-transaction.


Setting up a mirror: can't find remote server I have troubles setting up a mirror, more precisely: i cant connect to the mirror :(

Here's my troubleshooting log :)

2 virtual Windows 2k12 Servers running in Microsoft Hyper-V, on a Windows 10 machine with SQL Server 2k12,
Neither are part of a domain and the firewalls are off
Machine A has a SQL database,
Machine B has a full backup of Machine A's database, restored with NO RECOVERY,
Both machines/SQL servers can be reached over the network; pinging works, tested database connectivity using SMSS on a third machine.
I followed the steps in this post when using the wizard didn't work out SQL Server Mirroring can't connect to mirror database. The accounts have sysadmin rights and enpoints are associated with the certifactes,
I'm sure the ports are opened, as shown by netstat -an  and further evidenced by using telnet to connect to the host/port combination,
Endpoints are in started state as shown in the mirroring endpoints table, also recycling the endpoints (i.e. stopping and restarting them) does not seem to have an effect on the issue
GRANTing CONNECT TO PUBLIC on the Endpoint isn't working either,

Still, running ALTER DATABASE [xxx] SET PARTNER = 'TCP:\\....:7023' results in the well known:

Msg 1418, Level 16, State 1, Line 1
The server network address "tcp://m.....:7023" can not
be reached or does not exist. Check the network address name and
that the ports for the local and remote endpoints are operational.

Any other insights that can apply here?

I'll post my own answer to this as well to make it obvious for other readers.

What I did wrong and this should have been very obvious, but apparently I missed this. The command: ALTER DATABASE [xxx] SET PARTNER = 'TCP:....:7023' Should have been run first on the mirror and second on the principal.

So in the end, my networking was, indeed, fine, but a blatant mishap when initiating mirroring on my part.

I'll post my own answer to this.

What I did wrong and this should have been very obvious, but apparently I missed this.
The command: ALTER DATABASE [xxx] SET PARTNER = 'TCP:\....:7023' Should have been run first on the mirror and second on the principal.

So in the end, my networking was, indeed, fine, but a blatant mishap when initiating mirroring on my part.


Best way to store few rows of data every minute without creating 1440 columns a day? I will be calling a server that returns some JSON data every minute. The values are along the lines of Value, Currency, DateTime, and maybe 1 or 2 more values.

What is the best way to keep save these related values in a table using MySQL?

1440 columns with one column per minute and 1 row per day?
1440 rows with 4 or 5 columns? One row with 1 column as a text/blob with the 1440 minutes of data as a serialized array?

I feel like that last one might be best since it's only one days worth of data and it can be queried by day. I'm just afraid of how large that text column might get and if someone wants to see a years worth of rows, how long would it take to retrieve, unserialize, and return 1440 X 365 worth of arrays. I'm by no means a database admin, so this isn't something I've ever encountered.

I would argue for 1 row per data event (so 1440 rows per day) with one static column per data point. This will be easiest to query against any of the fields.


Copy Data between two databases I have the following situation. An application writes logs into SQL Server 2012 Standard edition database. The incoming data are too big to leave in the operational DB because the application also reads from this DB and it is time critical. So we need to delete the log entries which are older than a week.

But in some cases we need the old data. So my idea is to create a second database from a backup of the original and restore it every week before we delete the data. An already existing external reporting tool is ready to read data from two databases so our problem would be solved.

Is this possible - restoring and attaching data to another DB with the same schema - or do I need to create a new database each week?

There are 2 good options that you can think of implementing based on your comfort level :

Incremental Loads - using SSIS

Pros:

You can schedule SSIS package as and when required.
You can do it for selected tables.
Depending on the frequency of data transfer, the data to be transferred would be less.

Cons:

Would require a bit of learning if you are new to SSIS.

Backup Restore (sample script .. change it as per your needs) :

Pros :

You get a copy of your database as per your schedule.

Cons:

Entire database has to be restored. Requires more disk space.

Your application should use 3 part (dbname.schema.tableName).

Also, make sure that Instant file initialization is enabled to cutdown the restore time.


Trying to work out how to escape a hyphen in an XML Tag in Azure SQL Analytics working with Kusto / KQL to try and parse the deadlock xml in Azure SQL Analytics and getting stuck with escaping the tag name process-list as follows
So the deadlock xml looks like
<deadlock>
  <process-list>
  ...

I want to get one of the attributes under process-list but KQL chokes on the hyphen and I don't know how to escape it to make it work - see the bold section in the last line
AzureDiagnostics
| where (Category == "Deadlocks")
| where ResourceId contains "/SERVERS/"
| where ResourceId contains "/DATABASES/"
| where Resource contains "Analytics"
| project Resource = strcat(extract(".+/SERVERS/([^/]+)/.+", 1, ResourceId), ".", extract(".+/DATABASES/(.+)", 1, ResourceId)).tolower(), TimeGenerated, deadlock_xml_s
| extend DeadlockXML = parse_xml(deadlock_xml_s)
| extend KeyDeets = DeadlockXML.deadlock.process-list.waitresource

...but KQL chokes on the hyphen and I don't know how to escape it to make it work

Please see: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/schema-entities/entity-names#identifier-quoting
for example: DeadlockXML.deadlock['process-list'].waitresource


Single Row Table for Storing an Additional Unique Identifier I just came across a somewhat old database (and frontend) and it has funny way of dealing with the aspect of unique ids.

I've got one table with a single column and row storing an Integer (currently 31448). This number is used on an Invoices table as 'InvoiceNo', the Invoices table also has a unique auto-inc id (currently 2847)

It looks a bit like this:

CREATE TABLE InvoiceNumber(
    LatestId [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE Invoices(
    InvoiceId [int] IDENTITY(1,1) NOT NULL,
    InvoiceNo [int] NOT NULL,
    Amount [decimal](18, 2) NULL
) ON [PRIMARY]
GO

No actual relationship between the two tables other than the InvoiceNo which is not actually an enforced relationship.

The frontend then, when the user adds a new invoice performs the following:

public static void CreateInvoice(Invoice newInvoice)
{
    try
    {
        using (var ctx = new DataContext)
        {
            var lastInvoice = (from lastNumber in ctx.SingleRowTable
                                    select lastNumber).Single;

            var newInvoiceNo = lastInvoice.InvoiceNumber +1; // Int

            lastInvoice.InvoiceNumber = newInvoiceNo;

            newInvoice.InvoiceNo = newInvoiceNo;

            ctx.Invoices.InsertOnSubmit(newInvoice);

            ctx.SubmitChanges;
        }
    }
    catch  (Exception x)
    {
        // Sleep now in the fire
    }
}

So basically when a new invoice is created the frontend requests the number on the InvoiceNo (single row) table and adds one (+1) that number then becomes the new invoice number and also the latest used invoice number.

Other tables in the database (InvoiceItems) reference the InvoiceId not the InvoiceNo.

I supposed the question is: Why would someone use this approach? is it an anti-pattern or design error or are there any real implementation towards it?

My thought was that for some reason the "Real World" invoice number needed to be preserved so they decided that running an additional 'Unique Id' would be the best way to deal with it. I thought maybe the developers couldn't afford a one thousand jump on the invoice number. (?) Maybe.

I used to work with a program that used a similar system to generate unique IDs for invoices. It did originate in a different database system, one without auto-incrementing IDs.

There's one difference with your system, and it's a difference I like a lot. The invoice table has a unique, auto-incrementing ID - and it has a separate, customer-facing (I'll assume) invoice number.

I went through a process of merging two structurally identical databases. The auto-incrementing ID values overlapped, so we had to reset one set of them as we went through the merge process. Our invoice table used the unique, auto-incrementing ID as the customer-facing invoice number. So, we functionally lost those invoice numbers in the system.

While it would have been potentially problematic to have two invoices with the same (customer-facing) invoice number, at least we would have had that option if the customer-facing value had been distinct from the primary key.


DELETE rows which are not referenced in other table I have two tables in a PostgreSQL 9.3 database: Table link_reply has a foreign key named which_group pointing to table link_group.

I want to delete all rows from link_group where no related row in link_reply exists. Sounds basic enough but I've been struggling with it.

Will it be something simple like this (not working)?

DELETE FROM link_group WHERE link_reply = NULL;

Quoting the manual:

  There are two ways to delete rows in a table using information
  contained in other tables in the database: using sub-selects, or
  specifying additional tables in the USING clause. Which technique is
  more appropriate depends on the specific circumstances.

Bold emphasis mine. Using information that is not contained in another table is a tad bit tricky, but there are easy solutions. From the arsenal of standard techniques to ...

Select rows which are not present in other table

... a NOT EXISTS anti-semi-join is probably simplest and most efficient for DELETE:

DELETE FROM link_group lg
WHERE  NOT EXISTS (
   SELECT FROM link_reply lr
   WHERE  lr.which_group = lg.link_group_id
   );

Assuming (since table definitions are not provided) link_group_id as column name for the primary key of link_group.

The technique @Mihai commented works as well (applied correctly):

DELETE FROM link_group lg
USING  link_group      lg1
LEFT   JOIN link_reply lr ON lr.which_group = lg1.link_group_id
WHERE  lg1.link_group_id = lg.link_group_id
AND    lr.which_group IS NULL;

But since the table expression in the USING clause is joined to the target table (lg in the example) with a CROSS JOIN, you need another instance of the same table as stepping stone (lg1 in the example) for the LEFT JOIN, which is less elegant and typically slower.


How to determine Oracle LOB storage footprint? With SECUREFILE storage I can specify whether I want compression (and the level of it) and deduplication, and it's all a trade-off between time and space.

Timing is fairly easy to profile but what's the easiest way to get a reasonably accurate measurement of how much space a specific LOB column takes up?

You can get the size in bytes of the BLOB column via DBMS_LOB.GETLENGTH(...).

To get a total size in bytes of column for the entire table SUM it up like this:

SELECT SUM(DBMS_LOB.GETLENGTH(my_blob_column)) as total_bytes
FROM my_table

If you've marked the BLOB column as deduplicated then you can run something like the following to get stats on the actual size in bytes used. Compare the used_bytes value before/after your enable settings that could change the size of the BLOB column (eg. dedupe, compression, etc).

declare
    p_schema_name           varchar(256) DEFAULT 'THE_SCHEMA_NAME';
    p_table_name            varchar(256) DEFAULT 'THE_TABLE_NAME';
    --
    l_segment_name          varchar2(30);
    l_segment_size_blocks   number;
    l_segment_size_bytes    number;
    l_used_blocks           number;
    l_used_bytes            number;
    l_expired_blocks        number;
    l_expired_bytes         number;
    l_unexpired_blocks      number;
    l_unexpired_bytes       number;

begin
    FOR rec IN (select segment_name
                from dba_lobs
                where owner = p_schema_name
                  and table_name = p_table_name
                order by 1)
    LOOP
        dbms_output.put_line('Segment Name=' || rec.segment_name);

        dbms_space.space_usage(
            segment_owner           => p_schema_name,
            segment_name            => rec.segment_name,
            segment_type            => 'LOB',
            partition_name          => NULL,
            segment_size_blocks     => l_segment_size_blocks,
            segment_size_bytes      => l_segment_size_bytes,
            used_blocks             => l_used_blocks,
            used_bytes              => l_used_bytes,
            expired_blocks          => l_expired_blocks,
            expired_bytes           => l_expired_bytes,
            unexpired_blocks        => l_unexpired_blocks,
            unexpired_bytes         => l_unexpired_bytes
        );

        dbms_output.put_line('  segment_size_blocks       => '||  l_segment_size_blocks);
        dbms_output.put_line('  segment_size_bytes        => '||  l_segment_size_bytes);
        dbms_output.put_line('  used_blocks               => '||  l_used_blocks);
        dbms_output.put_line('  used_bytes                => '||  l_used_bytes);
        dbms_output.put_line('  expired_blocks            => '||  l_expired_blocks);
        dbms_output.put_line('  expired_bytes             => '||  l_expired_bytes);
        dbms_output.put_line('  unexpired_blocks          => '||  l_unexpired_blocks);
        dbms_output.put_line('  unexpired_bytes           => '||  l_unexpired_bytes);
    END LOOP;
end;
/

This code is a slightly modified version of the code in Oracle's post explaining how deduplication works. The original is available here: http://www.oracle.com/technetwork/articles/sql/11g-securefiles-084075.html.


Fetching user info from many databases at one go I have a requirement to fetch all the schema available in 100+ Oracle instances running in RHEL environment.

All the schema information such as username, roles assigned and is to be dumped in one place from where it will be used for other purposes.

Is it feasible? I can provide more info if this question is sounding vague.

Are there any utilities to perform this?


SQL Server - Service User Accounts and Licensing? Maybe this is the wrong place to post, since its not necessarily sql server related, but here goes.

If I want to setup some service user accounts in the domain to use to run sql services like the sql server instance itself, sql agent, and others, do I need to be worried about using up user licenses for them?

I believe we are currently using CALs for active directory. I just don't know if this would apply because they aren't real users, just service accounts, but I also don't know if active directory will actually differentiate between the two.

Thanks

Basically User CALs are for people, not for Service Accounts.  In the User CAL model you need a CAL for each person whether they directly access SQL Server from their account or they access SQL Server through some Service Account.

This is a CAL per each and every person that uses the SQL Server, not a CAL per simultaneous user. However, your CALs will allow you to access as many SQL Servers that use CALs as you can reach within your organization.

Edit: There are also Device CALs for when several people share a device at different times.  Read about CALS at:

https://www.microsoft.com/licensing/about-licensing/client-access-license.aspx

Regarding Service Accounts on the SQL Server I cannot find my old link on the subject,  but perhaps this post will help you:

http://blogs.technet.com/b/uspartner_ts2team/archive/2013/11/05/administrative-amp-service-account-cals.aspx

See the sentence that says

  And for system service accounts – no CAL is required.

Therefore, since Service Accounts are not people, you can use as many service accounts as you can make use of: SQL Server, SQL Agent, Full Text, and so on.


Are GUIDs damaged when inserted into Varchar fields? In my staging database, I did the "smart thing", and used NewID() as a default value for Document_ID.  Historically, that GUID has been generated by a fragile .NET script, so I am redesigning the process using database operations.  It worked well, up to the end.

The data from staging are pasted to production after testing, through SSMS (We have a lot of NOT NULLS in the schema, and it's an easy way to get around them).

On the production system, the Document_ID is a Varchar(100).  After the paste, the GUID no longer works in queries, but only as a string:

 -- this format works
select * from Doc_Main where Document_ID like '%B20DC300-DC3C-40D9-AC83-4756C96F83DC%'
 -- Same format as below, doesn't work
select * from Doc_Main where Document_Id ='{B20DC300-DC3C-40D9-AC83-4756C96F83DC}'

Operationally, this is breaking the relationship between Doc_Main and the detail tables.

I am thinking that I will try to generate proper Insert statements based on the Staging data for Doc_Main, and replace the pasted records before doing anything more serious.

Ideas?

Under normal circumstances, a VARCHAR and a UNIQUEIDENTIFIER should implicitly convert in both directions.

However, in your production system, you're comparing two character strings. That's problem one

Problem two is that the formatting you're using (the braces) doesn't make the string into a GUID. If you're comparing a string formatted that way, it will match a GUID with that value (without the braces), but when you do a text comparison, 'X' <> '{X}'.

See the examples below to confirm:

DECLARE @my_guid uniqueidentifier = 'C75F516F-971A-4752-B25B-B9DB877FAE17';
DECLARE @my_varchar varchar(100) = @my_guid;

SELECT @my_guid as [GUID], @my_varchar as [Varchar];

SELECT CASE WHEN (@my_guid = 'C75F516F-971A-4752-B25B-B9DB877FAE17') THEN 'Matches' ELSE 'Doesn''t Match' END as Test_GUID_Regular
      ,CASE WHEN (@my_guid = '{C75F516F-971A-4752-B25B-B9DB877FAE17}') THEN 'Matches' ELSE 'Doesn''t Match' END as Test_GUID_Braces
      ,CASE WHEN (@my_guid = {guid 'C75F516F-971A-4752-B25B-B9DB877FAE17'}) THEN 'Matches' ELSE 'Doesn''t Match' END as Test_GUID_ODBC
      ,CASE WHEN (@my_varchar = 'C75F516F-971A-4752-B25B-B9DB877FAE17') THEN 'Matches' ELSE 'Doesn''t Match' END as Test_Char_Regular
      ,CASE WHEN (@my_varchar = '{C75F516F-971A-4752-B25B-B9DB877FAE17}') THEN 'Matches' ELSE 'Doesn''t Match' END as Test_Char_Braces
      ,CASE WHEN (@my_varchar = {guid 'C75F516F-971A-4752-B25B-B9DB877FAE17'}) THEN 'Matches' ELSE 'Doesn''t Match' END as Test_Char_ODBC
      ,CASE WHEN (@my_varchar = CAST('C75F516F-971A-4752-B25B-B9DB877FAE17' as uniqueidentifier)) THEN 'Matches' ELSE 'Doesn''t Match' END as Test_Cast_Regular
      ,CASE WHEN (@my_varchar = CAST('{C75F516F-971A-4752-B25B-B9DB877FAE17}' as uniqueidentifier)) THEN 'Matches' ELSE 'Doesn''t Match' END as Test_Cast_Braces
      ,CASE WHEN (@my_varchar = CAST({guid 'C75F516F-971A-4752-B25B-B9DB877FAE17'} as uniqueidentifier)) THEN 'Matches' ELSE 'Doesn''t Match' END as Test_Cast_ODBC
;

The results show that comparing the string in braces to an actual GUID, or converting it into a GUID before comparing, works when it's compared to a string; comparing it to a string directly fails.

GUID                                 Varchar
------------------------------------ -------------------------------------
C75F516F-971A-4752-B25B-B9DB877FAE17 C75F516F-971A-4752-B25B-B9DB877FAE17

Test_GUID_Regular Test_GUID_Braces Test_GUID_ODBC
----------------- ---------------- --------------
Matches           Matches          Matches

Test_Char_Regular Test_Char_Braces Test_Char_ODBC
----------------- ---------------- --------------
Matches           Doesn't Match    Matches

Test_Cast_Regular Test_Cast_Braces Test_Cast_ODBC
----------------- ---------------- --------------
Matches           Matches          Matches


MySQL tar.gz backup keeps getting bigger but MySQL folder size is the same Our production site runs on a LAMP stack and we have a daily mysql backup that produces a tar.gz file. I recently reduced the size of some tables by deleting old rows after 3 months rather than 6, and the daily tar.gz file got bigger. The next day I removed an inefficient TEXT field and replaced it with a VARCHAR(3) field...and the daily tar.gz file got bigger. After unpacking into folders, all the individual files and folders (including the ibdata file) in the daily backups appear to be the same size - but on a day by day basis the tar.gz files get ~14mb bigger each day. According to the table size info I get from MySQL, I have reduced the size of my db.

Nothing is actually broken here - site is running fine and the db is responsive - but I cannot find any explanation as to why my backups keep getting bigger even though the db is getting smaller and it's frankly doing my head in. If anyone can offer an explanation that would be great.

You are backing up the raw files, not a "dump" of the rows (via mysqldump or Xtrabackup), correct?

When you DELETE, the rows are held onto in case of a ROLLBACK (explicit or due to crash).  This may increase the file size.

Data (and index) files never shrink in MySQL without explicit action.  In particular, there is no way to shrink ibdata1 without dumping and reloading.

So...

Plan A:  Use mysqldump or xtrabackup to extract the rows.  Then, if you like, send that through tar and gzip (or whatever).

Plan B:  If the tables are "file_per_table", OPTIMIZE TABLE to shrink the .ibd files before the next dump.  (You are stuck with the bloated ibdata1.)

Plan C:  If not file_per_table, that is, if all the data is in ibdata1, do a one-time dump, rm ibdata1, reload.  This is somewhat tricky and somewhat risky.  Test before risking the production data.

If you will regularly be purging old data, consider using Partitioning as an efficient way to purge old data.  If you intend to keep only 3 months' worth, then partition by week (14 partitions).  Details here.  It involves DROP PARTITION which will not leave behind stuff like DELETE does.

As for why the "steep increase" did not continue to happen...  The deleted rows left "free" space in the tablespace; that was being reused.  For now, your dumps will continue to be about the same size.  (Note:  gzip, alone, will fluctuate in size as the data changes, even if the the input size seems to stay the same.  This is a characteristic of compression algorithms.)


MySQL: Given a list of row values, how do you only return the changed rows? This is my first S/O question! I'm stumped on what seems to be a basic query; given a list of values for various rows, return only the values that have changed. I am building a live-updating feature for a ordering system and I need to update rows on the client browser that have changed since the last (5 second) polling interval. Since I'm dealing with thousands of rows I need to only update what's changed.

Here's what my table looks like:
id, order_number, department

My client sends a list of two values (x1000+ items):
order_number, department

'department' is the value that I'm looking to see if a change has occurred.

I need a way to query the DB once with the entire list from the client and have MySQL return a list of only the changed items. Given a list of 1500 orders, it's likely that only one will ever be changed at a time, so I think for efficiency it makes sense to have the DB engine do this for me rather than looping through the list and doing a query each time. I tried doing it with a temp table and while it kind of worked, I found that the DROP TABLE at the end precluded my query results from being returned:

CREATE TEMPORARY TABLE `orderlist` (
    `order_number` varchar(128) DEFAULT NULL,
    `department` int(11) DEFAULT '1'
);
INSERT INTO orderlist (order_id, department) VALUES ('180425','2');
INSERT INTO orderlist (order_id, department) VALUES ('180426','2');
INSERT INTO orderlist (order_id, department) VALUES ('180427','3');
SELECT orders.order_number, orders.department
FROM orders
JOIN orderlist USING(order_number)
WHERE orderlist.department != orders.department;
DROP TABLE orderlist;

One solution could be to add a last_updated timestamp on update current_timestamp column to your orders table. So when an update happens, this column is automatically updated by MySQL to contain the current time.

When you query the database, use the current time minus 5 seconds to compare against the last_updated column i.e.

where last_updated > now()-5

This will give you only the orders updated in the last 5 seconds.

Make sure you add an index on the new last_updated column.


Update previous value field I inherited a database that has multiple tables that contain some form of an object ID, a date, and a value. The metrics depend on comparing the current value on any given date to the previous value (and the values do not occur every day). I am looking for a way to quickly fill in a "previous value" field such that for any given record, previous value contains the value from that table where the object ID is the same and the date is the maximum date that occurs before the target row.

Note: I am using Oracle.

Example: {object, date, value}

1,2014-04-21,12
2,2014-03-01,38
1,2014-02-28,22
1,2014-01-01,28

In this case, the first row should have prev_value set to 22 (third row) because the third row matches on object ID and has the maximum date that occurs before the first row's date.

POSSIBLE SOLUTION:

I am using lag, but it is a bit of a nasty query. I select object_id and lag date over partition on object_id ordered by date. Then, I merge that back into the table to fill the previous date. It takes a very long time, but it appears to work.

You can use the LAG() function:

SELECT
    "objectID",
    "date",
    "value",
    LAG("value", 1) OVER (PARTITION BY "objectID" ORDER BY "date")
        AS previous_value
FROM
    tableX ;

Tested at SQLFiddle


Database Log 3 times Data File I have a set of database on full recovery model, my backup schedule looks like this:

1 Weekly Full Backup
1 Daily Log Backup

Full backups are about 10GB, log backups are about 500mb per day.

What happens is that right now, I'm having a DataFile of 10GB but a log file of 30GB...

Could be an option to shrink the log file? Is this a good practice? I have read that is not recommended but I don't know the exact reason.

Thanks!

I would recommend backing up the transaction log more frequently than once a day, possibly every 15 minutes.

It may be that a single very large transaction has blown up its size. You can monitor how full the transaction log gets during the day (in between log backups) using the command

dbcc sqlperf(logspace)

And yes, you can shrink the log, using a command like this, for example, if you want to shrink it to 1000MB:-

use YourDatabaseName
go

dbcc shrinkfile(2,1000)

Weekly full database backups exposes you to data loss; I would also recommend backing your database more frequently than weekly.


Accidentally turned down SQL memory too low to log in, how do I fix? I turned down the Maximum Memory of the my SQL Server instance to 15 MB... Now I can't log in to increase it. How can I increase the maximum memory without logging in?

Version is SQL 2008 R2

Connect using the Dedicated Administrator Connection (DAC), which is guaranteed a minimal amount of resources in order to solve issues exactly like this one. For example, if you are connecting via Management Studio, launch a New Query window and just type ADMIN:servername instead of servername when it prompts you to connect. Ignore any errors you get which are usually benign as they are associated with Object Explorer, the database dropdown list, IntelliSense, etc. also trying to connect on your behalf (only one DAC connection is allowed).

More info here, here, and some troubleshooting here.


Stopping SQL Server Agent: Effect on runnings jobs On a server with MS SQL Server 2008 installed, we use the SQL Server Agent to schedule a large amount of SSIS-packages, all with unique schedules throughout the day.

Now and then, the server needs to be restarted (patching etc). Is it safe to right-click on SQL server agent and select 'stop'? The SSIS-packages are not that well-built and if they are stopped right in the middle of something data might become corrupted.

So, does stopping SQL Server Agent mean that jobs are 'killed', or is it just so that no more jobs will be scheduled to run from that moment?

Yes, jobs are aborted. Non-SQL process (eg CmdExec) are aborted too.
No more jobs run until SQL Server Agent is started again. Missing scheduled jobs will not be run.

After comment:

SQL Server Agent is just another connection to the DB engine. It will send an abort to the DB engine and all SQL jobs are stopped.

Abort means something here: it is a specific client action. See SQL Server Transaction Timeout


MSSQL Geography STIntersects returns 1 when 0 is expected I'm experiencing an MSSQL Geography issue I can't explain.  Assume that I'm math-challenged, but it seems like the following point should NOT intersect the following polygon:

DECLARE @t_point geography;
DECLARE @t_polygon geography;
set @t_polygon = geography::STPolyFromText('POLYGON((1 1, 5 5, 5 0, 1 1))', 4326);
set @t_point = geography::Point(0, 0, 4326);

select
  @t_polygon.STAsText(),
  @t_point.STAsText(),
  @t_point.STIntersects(@t_polygon),
  @t_polygon.STIntersects(@t_point);

When I execute this, though, I get the following result:

POLYGON ((1 1, 5 5, 5 0, 1 1))  POINT (0 0) 1   1

The last two columns are both 1 where I would expect 0.  How can a point in space at 0,0 intersect with a polygon that does not touch or encompass 0,0?

-1, -1 has the same behavior, as does 100, 100.

Even if I change the point to a polygon in a completely different space, MSSQL says that they intersect.

set @t_polygon = geography::STPolyFromText('POLYGON((10 10, 50 50, 50 10, 10 10))', 4326);
set @t_point = geography::STPolyFromText('POLYGON((1 1, 5 5, 5 0, 1 1))', 4326);

I also tried STGeomFromText().

I'm sure I must be doing something wrong, but I can't imagine what that might be.

The reason you are having a problem is that the polygon is wound the wrong way which results in a polygon that covers the entire world except for your area of interest.

Try

DECLARE @t_point geography;
DECLARE @t_polygon geography;
set @t_polygon = geography::STPolyFromText('POLYGON((1 1, 5 0, 5 5, 1 1))', 4326);
set @t_point = geography::Point(0, 0, 4326);

It is a bit strange, but given that with geographies we are dealing with a sphere, there needs to be a way to determine which side of the boundary is the interior of the polygon.  With SQL Server, this is done by making the exterior boundary be defined as anti clockwise.

Geometries don't care which way they are wound, but prefer anti clockwise.  There is a function for geographies ReorientObject that change the direction of the object.


SP or query to dump "password policy" checkbox for all logins sp_helplogins doesn't show a column for this. I need a listing of all logins, with the status of their "Enforce password policy" checkbox. This is for an auditor, and the standard practice is for screenshots of every login. I'd prefer to make this simpler on me (to generate) and simpler on them to audit.

Here's the start of a report:

SELECT
    sp.name AS LoginName,
    sl.is_policy_checked AS IsPasswordPolicyEnforced
    FROM sys.server_principals sp
    LEFT OUTER JOIN sys.sql_logins sl ON sl.principal_id = sp.principal_id
    WHERE sp.type IN ('S', 'G', 'U');

Note that this property only applies to SQL logins.


How can the TNS connect identifier passed to SQL*Plus be accessed from the run script for reconnecting? I wonder how the TNS identifier can be used within a SQL script. In this example

$ sqlplus MY_USER/pswd@TNSID @script.sql par1 par2

I'd like to get TNSID as a string in order to use it from script.sql but I have no idea where to find it.

The goal is to reconnect as a different user on the same instance.

Sure, pass it as a parameter too.

sqlplus MY_USER/pswd@TNSID @script.sql TNSID par2 par3

Then you can use the below in the script:

connect MY_USER2/pswd@&1


WITH using INDEX hint in Oracle I have a fairly complicated query. I have written two different query to accomplish my goal.
First one is using WITH, other one is using global temporary tables.

First One:

WITH A
(
 KNO
 ..
 )
 , B
 (
 KNO
 ...
 )
 , C
 (
 KNO
 ...
 )
 SELECT * from
 A INNER JOIN B
 on A.KNO = B.KNO
 INNER JOIN C
 on B.KNO = C.KNO

Second One:

Truncate Table tempA;
Truncate Table tempB;
Truncate Table tempC;

INSERT INTO tempA SELECT -- Same select which constructs WITH A
INSERT INTO tempB SELECT -- Same select which constructs WITH B
INSERT INTO tempC SELECT -- Same select which constructs WITH C

 SELECT * from
 tempA A INNER JOIN tempB B
 on A.KNO = B.KNO
 INNER JOIN tempC C
 on B.KNO = C.KNO

They are on par. What I mean is I observe same query time with both of them. But When I add KNO index to tempA,tempB,tempC temporary tables. Second one skyrockets.
What I mean is : It gets faster, a lot. I query for 1 year of values. using WITH: about 58 minutes. using Temp Table with INDEX: about 30 minutes.

Query times for 4 months of values are given below.

Using WITH

DENEME@DENEME   Finished    15:37:02    15:40:38    03:36 mins  Select  500 WITH A

Total:216000 msecs

Using Temporary Tables + Index

DENEME@DENEME   Finished    15:41:54    15:41:55    1 sec   Select  500 WITH D
DENEME@DENEME   Finished    15:41:38    15:41:38    665 msecs   Insert  34660   INSERT INTO C
DENEME@DENEME   Finished    15:41:21    15:41:35    14 secs Insert  34660   INSERT INTO B
DENEME@DENEME   Finished    15:41:17    15:41:17    109 msecs   Insert  1804    INSERT INTO  A
DENEME@DENEME   Finished    15:41:12    15:41:12    33 msecs    Unknown 0   TRUNCATE TABLE C
DENEME@DENEME   Finished    15:41:12    15:41:12    39 msecs    Unknown 0   TRUNCATE TABLE B
DENEME@DENEME   Finished    15:41:11    15:41:11    203 msecs   Unknown 0   TRUNCATE TABLE A

Total: 16049 msecs

My question is :

Can I give hint to oracle to add index when it is constructing WITH temporary tables?

No, I don't think defining an index for a common table expression is possible.

You can experiment with the materialize hint, but I don't think you'll get anything faster than the solution with (indexed) temporary tables.


Authentication failed on secondary mongodb I have primary mongodb v3.4.10 on ubuntu (bare metal) with these users:

{
    "_id" : "admin.root",
    "user" : "root",
    "db" : "admin",
    "roles" : [
        {
            "role" : "root",
            "db" : "admin"
        }
    ]
}

Secondray is docker image with this config (I start secondary-newnode with initial sync and it become secondary):

    services:
      mongodb:
        image: mongo:3.4.15-jessie
        restart: always
        volumes:
          - /opt/mongodb/data:/data/db
          - /opt/mongodb/replication.key:/data/db/ssl/replication.key
        network_mode: "host"
        command: --storageEngine=wiredTiger --wiredTigerCacheSizeGB=12 --journal --replSet rs0 --bind_ip localhost,192.168.50.3 --auth --keyFile /data/db/ssl/replication.key

Now, when i want connect to secondary, with mongo shell authenticataion failed with

    Error: 18 { ok: 0.0, errmsg: "auth failed", code: 18, codeName: "AuthenticationFailed" }
    0

forced to new node become PRIMARY not work.

I found the problem. This is the mongo client issue. I added mongodb repository and install mongo shell

echo "deb http://repo.mongodb.org/apt/ubuntu xenial/mongodb-org/3.4 multiverse" > /etc/apt/sources.list.d/mongodb.list

# apt-get update; apt-get install mongodb-org-shell


Storing IP addresses - varchar(45) vs varbinary(16) I am going to create a table with two fields - ID as BIGINT and IPAddress as either varchar(45) or varbinary(16). The idea is to store all unique IP addresses and use a reference ID instead the actual IP address in other tables.

Generally, I am going to create a stored procedure that is returning the ID for given IP address or (if the address was not found) insert the address and return the generated ID.

I am expecting to have many records (I cannot tell exactly how many), but I need the stored procedure above to be executed as fast as possible. So, I am wondering how to stored the actual IP address - in text or bytes format. Which is going to be better?

I have already written SQL CLR functions for transforming IP address bytes to string and the reverse, so transformation is not an issue (working with both IPv4 and IPv6).

I guess I need to create an index to optimize the search, but I am not sure should I include the IP address field to the clustered index, or to create a separate index and with which type the search will be faster?

how to stored the actual IP address - in text or bytes format. Which is going to be better?

Since "text" here refers to VARCHAR(45) and "bytes" refers to VARBINARY(16), I would say: neither.

Given the following information (from Wikipedia article on IPv6):

  Address representation
  The 128 bits of an IPv6 address are represented in 8 groups of 16 bits each. Each group is written as 4 hexadecimal digits and the groups are separated by colons (:). The address 2001:0db8:0000:0000:0000:ff00:0042:8329 is an example of this representation.

  For convenience, an IPv6 address may be abbreviated to shorter notations by application of the following rules, where possible.

  One or more leading zeroes from any groups of hexadecimal digits are removed; this is usually done to either all or none of the leading zeroes. For example, the group 0042 is converted to 42.
  Consecutive sections of zeroes are replaced with a double colon (::). The double colon may only be used once in an address, as multiple use would render the address indeterminate. RFC 5952 recommends that a double colon must not be used to denote an omitted single section of zeroes.[41]

  An example of application of these rules:

          Initial address: 2001:0db8:0000:0000:0000:ff00:0042:8329
          After removing all leading zeroes in each group: 2001:db8:0:0:0:ff00:42:8329
          After omitting consecutive sections of zeroes: 2001:db8::ff00:42:8329

I would start by using 8 VARBINARY(2) fields to represent the 8 groups. The fields for Groups 5 - 8 should be NULL as they will only be used for IPv6 addresses. The fields for Groups 1 - 4 should be NOT NULL as they will be used for both IPv4 and IPv6 addresses.

By keeping each group independent (as opposed to combining them into either a VARCHAR(45) or a VARBINARY(16) or even two BIGINT fields) you get two main benefits:

It is much easier to reconstruct the address into any particular representation. Otherwise, in order to replace consecutive groups of zeroes with (::) you would have to parse it out. Keeping them separate allows for simple IF / IIF / CASE statements to facilitate this.
You will save a ton of space on IPv6 addresses by enabling either ROW COMPRESSION or PAGE COMPRESSION. Since both types of COMPRESSION will allow for fields that are 0x00 to take up 0 bytes, all of those groups of zeroes will now not cost you anything. On the other hand, if you stored the example address from above (in the Wikipedia quote), then the 3 sets of all zeroes in the middle would take up their full amount of space (unless you were doing the VARCHAR(45) and went with the reduced notation, but that might not work well for indexing and would require special parsing to reconstruct it to the full format, so let's assume that is not an option ;-).

IF you need to capture the Network, create a TINYINT field for that called, um, [Network] :-)

For more info on the Network value, here is some info from another Wikipedia article on the IPv6 address:

  Networks

  An IPv6 network uses an address block that is a contiguous group of IPv6 addresses of a size that is a power of two. The leading set of bits of the addresses are identical for all hosts in a given network, and are called the network's address or routing prefix.

  Network address ranges are written in CIDR notation. A network is denoted by the first address in the block (ending in all zeroes), a slash (/), and a decimal value equal to the size in bits of the prefix. For example, the network written as 2001:db8:1234::/48 starts at address 2001:db8:1234:0000:0000:0000:0000:0000 and ends at 2001:db8:1234:ffff:ffff:ffff:ffff:ffff.

  The routing prefix of an interface address may be directly indicated with the address by CIDR notation. For example, the configuration of an interface with address 2001:db8:a::123 connected to subnet 2001:db8:a::/64 is written as 2001:db8:a::123/64.

For indexing, I would say create a Non-Clustered index on the 8 Group fields, and possibly the Network field if you decide to include that.

End result should be something like the following:

CREATE TABLE [IPAddress]
(
  IPAddressID INT          NOT NULL IDENTITY(-2147483648, 1),
  Group8      VARBINARY(2) NULL, -- IPv6 only, NULL for IPv4
  Group7      VARBINARY(2) NULL, -- IPv6 only, NULL for IPv4
  Group6      VARBINARY(2) NULL, -- IPv6 only, NULL for IPv4
  Group5      VARBINARY(2) NULL, -- IPv6 only, NULL for IPv4
  Group4      VARBINARY(2) NOT NULL, -- both
  Group3      VARBINARY(2) NOT NULL, -- both
  Group2      VARBINARY(2) NOT NULL, -- both
  Group1      VARBINARY(2) NOT NULL, -- both
  Network     TINYINT      NULL
);

ALTER TABLE [IPAddress]
  ADD CONSTRAINT [PK_IPAddress]
  PRIMARY KEY CLUSTERED
  (IPAddressID ASC)
  WITH (FILLFACTOR = 100, DATA_COMPRESSION = PAGE);

CREATE NONCLUSTERED INDEX [IX_IPAddress_Groups]
  ON [IPAddress] (Group1 ASC, Group2 ASC, Group3 ASC, Group4 ASC,
         Group5 ASC, Group6 ASC, Group7 ASC, Group8 ASC, Network ASC)
  WITH (FILLFACTOR = 100, DATA_COMPRESSION = PAGE);

Notes:

I recognize that you plan on using BIGINT for the ID field, but do you really expect to capture more than 4,294,967,295 unique values? If so then just change the field to be BIGINT and you can then even change the seed value to be 0. But otherwise you are better off using INT and starting with the minimum value so that you can make use of the entire range of that datatype.
If desired, you can add one or more NONpersisted Computed Columns to this table to return text representations of the IPAddress.
The Group* fields are arranged purposefully going down, from 8 to 1, in the table so that doing SELECT * will return the fields in the expected order. But the index has them going up, from 1 to 8, as that is how they are filled out.
An example (unfinished) of a computed column to represent the values in text form is:

ALTER TABLE [IPAddress]
  ADD TextAddress AS (
IIF([Group8] IS NULL,
    -- IPv4
    CONCAT(CONVERT(TINYINT, [Group4]), '.', CONVERT(TINYINT, [Group3]), '.',
      CONVERT(TINYINT, [Group2]), '.', CONVERT(TINYINT, [Group1]),
      IIF([Network] IS NOT NULL, CONCAT('/', [Network]), '')),
    -- IPv6
    LOWER(CONCAT(
      CONVERT(VARCHAR(4), [Group8], 2), ':', CONVERT(VARCHAR(4), [Group7], 2), ':',
      CONVERT(VARCHAR(4), [Group6], 2), ':', CONVERT(VARCHAR(4), [Group5], 2), ':',
      CONVERT(VARCHAR(4), [Group4], 2), ':', CONVERT(VARCHAR(4), [Group3], 2), ':',
      CONVERT(VARCHAR(4), [Group2], 2), ':', CONVERT(VARCHAR(4), [Group1], 2),
      IIF([Network] IS NOT NULL, CONCAT('/', [Network]), '')
     ))
   ) -- end of IIF
);

Test:

INSERT INTO IPAddress VALUES (127, 0, 0, 0, 4, 22, 222, 63, NULL); -- IPv6
INSERT INTO IPAddress VALUES (27, 10, 1234, 0, 45673, 200, 1, 6363, 48); -- IPv6
INSERT INTO IPAddress VALUES (NULL, NULL, NULL, NULL, 192, 168, 2, 63, NULL); -- v4
INSERT INTO IPAddress VALUES (NULL, NULL, NULL, NULL, 192, 168, 137, 29, 16); -- v4

SELECT [IPAddressID], [Group8], [Group1], [Network], [TextAddress]
FROM IPAddress ORDER BY [IPAddressID];

Result:

IPAddressID   Group8   Group1   Network  TextAddress
-----------   ------   ------   -------  ---------------------
-2147483646   0x007F   0x003F   NULL     007f:0000:0000:0000:0004:0016:00de:003f
-2147483645   0x001B   0x18DB   48       001b:000a:04d2:0000:b269:00c8:0001:18db/48
-2147483644   NULL     0x003F   NULL     192.168.2.63
-2147483643   NULL     0x001D   16       192.168.137.29/16


“ORA-03146: invalid buffer length for TTC field” after upgrade from Oracle 12 to 19c We are experiencing problems with updating a table record BLOB column. We get the "ORA-03146: invalid buffer length for TTC field" error. We basically are saving e.g. PDF attachments to the DB. The problem occurred after the upgrade from Oracle 12.0.1.2 to 19c (19.3.0.0), now the error is occurring on a larger scale. We have a .Net application, connecting to Oracle through ODP.NET (Oracle.DataAccess) and using NHibernate. We have done an upgrade from Oracle 12.1.0.2 to Oracle 19.3.0.0 using DBUA and the DB server is on Windows 2012 platform.
The error occurs after executing the command: {OpsSqlPrepare2(): SQL: UPDATE SIRIUS.Attachment SET Request = :p0, LOANREQUEST = :p1, ARCHIVE = :p2, FILENAME = :p3, FILESIZE = :p4, BINARYCONTENT = :p5, ATTACHMENTTABLE = :p6, ATTACHMENTTYPE = :p7, NOTE = :p8, MIMETYPE = :p9, CUSTOMID = :p10, ISEXTERNAL = :p11, ISSEND = :p12, DocumentNameDial = :p13, DOCUMENTENUMTYPE = :p14, AttachmentTypeDial = :p15 WHERE ID = :p16}
BINARYCONTENT is a BLOB column.
The error is different from the two environments we have:
Oracle DB 19c/Oracle client 19c - ORA-03146: invalid buffer length for TTC field
Oracle DB 19c/Oracle client 12 - ORA-03138: Connection terminated due to security policy violation

JDBC application fails with ORA-03146: Invalid Buffer Length For TTC Field After Database Upgrade to 19c (Doc ID 2729562.1)

Updating a 12c database to 19.6 can result in this issue. In this
particular case, the database was upgraded from 12.1.0.2.
The ORA-3137 [3146] is a protocol exception which means that the
database has not consumed all the data from the user buffer for the
long bind.
This issue is addressed in Bug:30955880 - ORA-3137 [3146] [4] /
ORA-3137 [3146] [94] UPDATING LOB WITH LARGE BIND which was closed as
duplicate of unpublished Bug 31625618 - DML OVER A LOB COLUMN USING
LONG BIND FAILS WITH AN ORA-3137 [3146] ERROR WHEN THE BIND SIZE IS >
256K BYTES.

Then:
Bug 31625618 - DML Over a LOB Column Using Long Bind Fails with ORA-3137 [3146] [4] / ORA-3137 [3146] [94] When the Bind Size is > 256k Bytes (Doc ID 31625618.8)

Clients (JDBC/OCI) performing DML's on a LOB column intermittently
fail  with the below errors, when the bind length is > 256k bytes:
ORA-03137: malformed TTC packet from client rejected: [3146] [4]    ORA-03137: malformed TTC packet from client rejected: [3146] [94]
ORA-03146: invalid buffer length for TTC field

I quoted this part to highlight that this issue was not specific to JDBC.
Fix for Bug 31625618 exists as a oneoff patch for 19c versions below 19.10.
The bug is fixed in database version 19.10, which will be released next week.
Currently you are on 19.3. Apply the necessary patches.


EER Diagram MySQL Workbench I created a database in MySQL Workbench using Ubuntu.

Is there a quick way to get an EER diagram of that database?

Using MySQL Workbench you can reverse engineer a database into an EER model. See the documentation for details.

  For a visual representation (EER diagram) of this schema, select
  Model, Create Diagram from Catalog Objects to create the EER Diagram
  for the model.

There are binaries available or a PPA depending on your version of ubuntu. See here for details on the apt repository or install from source.