synchronizing local and server database I am developing a billing system software. For this I have created a database it contains many tables database and this is in local system
all transactions data will be stored in local systems database.

I would like to provide data backup tables in server.
Whenever the person(billing s/w user) wants he/she can upload the data to server(only newly added data should be uploded if existing data is there).

If local system data is currepted or get deleted by some reason it can be downloaded  from the database  server.
This all features should be done by using billing software.
How to do this.

Replication, and daily / hourly backups (depending on your need), probably with something like xtrabackup, and storing the binlogs in between, give your pretty good point-in-time recovery. You might even configure pt-slave-delay to let your actual backup lag somewhat, useful for those moments where you are aware of an accidental deletion within the time-frame of your delay, making the restoration a bit less cumbersome.

I do not understand the requirement 'all features should be done by using billing software'. Billing software is for billing, not for database maintenance.


ORA-00959: Tablespace '$name' does not exist when using impdp I'm new at Oracle databases. I'm executing an impdp command and encountered this error:

  ORA-00959: tablespace '$name' does not exist.

Is the only thing I can do is to create the $name tablespace?

Create the tablespace

or

remap the tablespace to use an existing one, with the remap_tablespace parameter:

impdp ... remape_tablespace=$name:$name2

Where $name2 is the name of an existing tablespace.


How to use a postgres variable in the select clause With MSSQL it's easy, the @ marking the start of all variable names allows the parser to know that it is a variable not a column.
This is useful for things like injecting constant values where a select is providing the input to an insert table when copying from a staging table.
declare @foo varchar(50) = 'bar';
select @foo;

How do you express this for postgres?

SQL has no support for variables, this is only possible in procedural languages (in Postgres that would e.g. be PL/pgSQL).
The way to to this in plain SQL is to use a CTE (which is also a cross platform solution and not tied to any SQL dialect):
with vars (foo) as (
  values ('bar')
)
select foo
from vars;

like injecting constant values where a select is providing the input to an insert table when copying from a staging table.

Well you don't need a variable for that:
insert into target_table (c1, c2, c3)
select col_1, col_2, 'some value'
from staging_table;


Mysql Master-Master with additional slaves We have two datacenters with a copy of our web app (online training platform) at each. The primary datacenter receives all of the traffic at any given time and the second datacenter serves as a failover(using DNS failover). The databases are set up in master-master with different auto-increment-increment values. The server at datacenter 1 also has its own local slave server on which we do backups, that way we can pause replication, do the backup with mysqldump, then start replication again.

 SERVER 1 <=========> SERVER 2
    |
  Slave 1

This weekend we did a failover to server 2 because datacenter 1 was doing planned network maintenance. Everything went smoothly and after a few hours we brought everything back to DC1. Last night I start getting replication errors on the slave saying it failed to DELETE an entry that didn't exist. I found it odd that the Slave 1 was getting the error while Server 2 was not (since they're both acting as slaves to Server 1). After digging, I found out that we still have a user that's going to Server 2.. for whatever reason their DNS hasn't updated for over 2 days. So the new data that they are producing is being replicated to Server 1, but Slave 1 is not getting these updates because they're not in Server 1's binlogs.

I now see that this was poor planning on my part for not foreseeing this issue. My question is, how can I make this work? We want to keep the master-master and we want to allow Slave 1 to exist, not only for backups but for additional read only reporting servers and such. Those of you that run Master-Master, what have you done to remedy similar issues? Or is this just a bad setup and I'm SOL? The app was not built with redundancy in mind, so everything is having to be done system side.

What you want is server1 to binlog not only its own updates, but also those updates he received from server2, so that slave1 will get all updates.

You just need to tell server1 to do this, which can be accomplished by setting log-slave-updates to yes.

You might want to set this option on server2 too, if the server topology should change someday (as Rolando kindly remarked in the comments below).

This does not loop endlessly as the default setting for replicate-same-server-id is 0 (and cannot be changed as long as log-slave-updates is on). This setting set to 0 means the slave will skip statements he receives with his own server-id.

Your infrastructure decision in general seems fine to me.


Indexes with High Lock waits - how to fix I've been running dbo.sp_BlitzIndex and have 4 somewhat similar indexes on the main table in my database.  Each one has a high number of waits and escalation attempts.  I'm not sure where these are coming from. I don't have missing indexes. I don't see searches on this table taking a long time so not sure if these are coming from selects or inserts.  Need some pointers on where to look next.

CORE.tblCase.idx_tblCase_bClosed_nUserID_PrimaryAgent (2):
Row lock waits: 1; total duration: 0 seconds; avg duration: 0 seconds;
Page lock waits: 85; total duration: 92 minutes; avg duration: 1 minutes;
Lock escalation attempts: 100,965;
Actual escalations: 1. NC indexes on table: 1
CORE.tblCase.idx_tblCase_bClosed_nActionID_Last_nWorkflowID (27):
Row lock waits: 3; total duration: 26 seconds; avg duration: 8 seconds;
Page lock waits: 15; total duration: 10 minutes; avg duration: 42 seconds;
Lock escalation attempts: 31,908;
Actual Escalations: 0. NC indexes on table: 1
CORE.tblCase.idx_tblCase_bClosed_nWorkflowID_nActionID_Last (28):
Page lock waits: 112; total duration: 85 minutes; avg duration: 46 seconds;
Lock escalation attempts: 31,748;
Actual Escalations: 0. NC indexes on table: 1
CORE.tblCase.idx_tblCase_bClosed_nCaseID_INC (35):
Row lock waits: 2; total duration: 13 minutes; avg duration: 6 minutes;
Page lock waits: 307; total duration: 399 minutes; avg duration: 1 minutes;
Lock escalation attempts: 43,090;
Actual Escalations: 0. NC indexes on table: 1

Side note first - hmm, there's something a little suspicious in those results. They all say the same table, but at the same time, they say "NC indexes on table: 1". That means we think there's only 1 index on the table - but you're showing 4 here. These could be in different databases perhaps, or maybe there's a bug in the sp_BlitzIndex code.

That aside, you have a couple of different questions in here.

How do I know if I have a blocking problem? Look at wait stats, which tracks what your SQL Server has been waiting on. My personal favorite tool for that is sp_Blitz @CheckServerInfo = 1, @OutputType = 'markdown' because you can post the results here on Stack in your question. (Disclaimer: I work for the company that started those scripts.) One of the sections will show your most significant waits - focus on those first. If your primary wait types include LCK*, then you probably have a blocking issue.

How do I find queries that are taking locks? This one can be kinda tough without monitoring software. I'd start with sp_BlitzCache @SortOrder = 'duration'  That'll show you the longest-running queries, which can sometimes point out queries involved in blocking. You can't really filter by table, though, at least not quickly.

How do I design the right indexes to support those queries? That's kind of a big question. Generally, you want as many indexes as you need to support your workloads, but not so many that delete/update/insert (DUI) operations slow down due to the wide number of locks they have to get. If your table really does only have one index, then DUI queries are probably doing table scans in order to find the rows they want to update. It's time to look at how you're accessing those tables when you do updates.

For example, say you have the classic white pages of the phone book - organized by last name, first name. If you try to update all users whose first name is 'Brent', you're going to have to scan the entire phone book, and you can end up with some nasty locking situations. You'd want to create a separate index on first name so that you can quickly find the rows you need to update, and then get out of the table.


Does a non-clustered index make any guarantees about row order? I have a developer that would like, when doing a select statement with no order by, the rows in a table to be in the order they were inserted. The developer suggested changing from a clustered to non-clustered index.

By changing the index from clustered to non-clustered, does this make any guarantees about the order in which rows would appear in the table?

This question is mostly for my curiosity; I am going to suggest using an identity column instead, but this request got me thinking. Timestamp could be used but there is a chance rows can be inserted simultaneously.

Thanks in advance for your help.

If you need order in your query results, put in an ORDER BY. It’s that simple.

Check out this article from SQL Server architect Conor Cunningham which pretty much sums this topic up:

No Seatbelt – Expecting Order without ORDER BY


Why VIEWS are showing in Data Transfer Service SQL Im trying to run DTS from one Db to another in Same server instance. But at the select objects to copy stage, its showing the VIEWs also. But VIEWS are only some customised queries. But why its been shows in DTS

Either use the SSMS Copy Database Wizard

Or use the SSMS Backup Database UI to backup and restore to the instance's default backup location.


Setting up my first SQL Server cluster I've been working with SQL Server for about 6 years and have completed a couple of certification exams so I consider myself reasonably knowledgeable.

I am however, about to spec and build my first SQL Server failover cluster. I know how it works, I've read about it over and over...

My question to you lot is: What advice/warnings would you give to someone in my position?

Did you find certain aspects of setup more tricky than others? Would I be best advised to upgrade from SQL Server 2008 r2 to 2012?

My loose plan is to use my company's existing HP blade enclosure to house 2 x HP servers with plenty of RAM/Cores - just a 2-node cluster with either a mirror or AOAG for added redundancy.

Thoughts?

If it's a new deployment then why not upgrade to the latest version of the software? 2014 has many benefits over 2008 and 2012.

The install is simple if you have configured windows clusters before. I would recommend windows 2012R2 as clustering is a little easier with features like network teaming and the automatic quorum level settings.

One of the hardest things to get right can be the LUNs that you configure on the SAN especially if you're using SAN level snapshotting.


Altering table schema takes too much time I am using PostgreSQL database.

I have a table with millions of rows. I have a varchar column with 2000 size and i want to make it to 4000. I ran the alter table command, but it is taking too much time.

Is there any quicker way to do it?

Yes, there is a quicker way to do it if you are using version 9.1.  The PostgreSQL version 9.1 release notes include this:

  Allow ALTER TABLE ... SET DATA TYPE to avoid table rewrites in
  appropriate cases (Noah Misch, Robert Haas)

  For example, converting a varchar column to text no longer requires a
  rewrite of the table. However, increasing the length constraint on a
  varchar column still requires a table rewrite.

Further improvements in this area are coming in version 9.2, which hits beta release next week.  But for now it appears the faster way is to alter the type to text, in which case it is a quick update to system tables with no change to the heap.


Sum currencies excluding duplicatied records I have a CRMOffer table containing customerID and other CRMOffer data. And a Sell table containing customerID and sellValue and sellPK.

Sell table has a explicit PK, CRMOffer sadly doesn't have one available.

I must join these tables, so that I can relate CRM offers to actual sells.

If Sell table doesn't have a specific customerID, sell value will be null. But if it does, and there are multiple CRM offers to the same customer ID, the join will result in duplicated SellPK and sellValue.

I must then group by CRMOffer's customerID and sum sell values, but sum only once for each SellPK.

A given sell event may be duplicated, in case CRM made multiple offers to a customer that generated a sell. But there may also be multiple distinct sells from the same customer.

Sorry I think the explanation is kinda confusing. Basically I must sum values of distinct sells being careful to exclude duplications resulted to multiple CRM offers. I can see if a sell is duplicated by its SellPK. I can use count(DISTINCT customerID) to remove duplications from customerID, but I can't find a way to remove duplications from sell value.

I agree with oNare that adding a sqlFiddle that builds a simplified version of your data model, shows what you have tried so far, and shows the desired results would be most helpful.

That said, you might want something like the following in order to avoid double-counting the sales for any customers that were given multiple offers.  I've put inline comments quoting the relevant parts of your question in this example query:

SELECT s.customerId, s.totalSellValue, o.* -- "other CRMOffer data"
FROM CRMOffer o
JOIN (
    -- "I must then group by CRMOffers customerID and sum sell values, but sum only once for each SellPK"
    -- Compute total sell value for each customer before joining to CRMOffer in order to avoid double-counting
    -- NOTE: Grouping by Sell.customerId is the same as grouping by CRMOffers.customerId since the two must match
    SELECT customerId, SUM(sellValue) AS totalSellValue
    FROM Sell
    WHERE customerId IS NOT NULL -- "If Sell table doesnt have a specific customerID, sell value will be null"
    GROUP BY customerId
) s
    ON s.customerId = o.customerId


MySQL 5.6 - Query Performance Issue. Searching for Slug I've a case where I don't know how to better the performance of this query.

This is a simple query to the Geonames.org database.

The query:

SELECT name, latitude, longitude FROM geoname
WHERE
slugify(geoname.name) = 'lisboa'
and geoname.country = 'PT'
and geoname.fclass in ('A', 'T')

The Explain:

id, select_type,   table,   type,     possible_keys,     key, key_len,    ref,    rows, Extra
 1,      SIMPLE, geoname,    ref,    fclass,country, country,      11,  const,   73462, Using index condition; Using where

I know that the problem is this where clause:

slugify(geoname.name) = 'lisboa'

I cannot index this. The slugify() is a MySQL function that slugify the geoname, for example New York becomes new-york.

What options shoul I consider to have a better performance in this situation. What can I do?

Upgrade to MySQL-5.7 and then MySQL-8.0 (for safety, 1 major version at a time).

Then use Secondary indexes on virtual colums as such:

ALTER TABLE geoname
ADD slug VARCHAR(20) GENERATED ALWAYS AS (slugify(name)),
ADD INDEX slug_country_fclass (slug, country, fclass);

If you wish to stay on 5.6, and you don't change geoname value often, you can denormalise as:

ALTER TABLE geoname
ADD slug VARCHAR(20) NOT NULL DEFAULT '',
ADD INDEX slug_country_fclass (slug, country, fclass);

Populate the column with:

UPDATE geoname SET slug=slugify(name);

After doing one of the above steps, change the query to use the slug generated column as:

SELECT name, latitude, longitude FROM geoname
WHERE
slug = 'lisboa'
and geoname.country = 'PT'
and geoname.fclass in ('A', 'T')

The new index can then be used for all the criteria of your query.


Confused about behaviour of variable in WHERE statement when value is null I have a table like (SQLServer 2008):

CREATE TABLE [dbo].[my_test_table](
    [productId] [int] NOT NULL,
    [purchaseId] [bigint] NOT NULL,
    (some other columns....),
 CONSTRAINT [PK_my_test_table] PRIMARY KEY CLUSTERED
(
    [productId] ASC,
    [purchaseId] ASC
))

having around 10 milions rows.

I want a query that returns the total number of rows for a product or, if the product is not secified, the total number of rows for all the products.
Something like:

declare @productId int

set @productId = 320

select count(*)
from my_test_table t with(nolock)
where productId = @productId
or @productId is null

The problem is that query takes much more time than the equivalent query:

select count(*)
from my_test_table t with(nolock)
where productId = 320
or 320 is null

How can we explain this behaviour ?

Here are the execution plans:

Consider this query:

select count(*)
from my_test_table t
where null is null;

Does it make sense for the optimizer to do a seek there? There's nothing to seek against. Trying to force it with a hint throws an error:

select count(*)
from my_test_table t with (forceseek)
where null is null;

  Msg 8622, Level 16, State 1, Line 15

  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.

For the query that you have right now you may want an index seek depending on the parameter value. However, an index seek is not valid for all possible parameter values so your cached plan cannot contain a seek. In addition, when the query optimizer builds the plan it does not check the value of the local variable first.

You indicated that the query is complex so likely the best solution will be to upgrade to at least SQL Server 2008 R2 SP2 so you can take advantage of the Parameter Embedding Optimization (PEO). That combined with an OPTION (RECOMPILE) hint allows the query optimizer to sniff the value of the local variable before executing the plan. The query plan cannot be reused by another query.

Let's see it in action. The actual query plan for the following query shows an index seek:

declare @productId int = 320;

select count(*)
from my_test_table t
where productId = @productId
or @productId is null
OPTION (RECOMPILE);

The actual query plan for the following shows a scan:

declare @productId int = NULL;

select count(*)
from my_test_table t
where productId = @productId
or @productId is null
OPTION (RECOMPILE);

If upgrading isn't an option I suppose you could try this:

select *
from my_test_table t
where productId = @productId
and @productId is not null

UNION ALL

select *
from my_test_table t
where @productId is null;

That can will do a seek or a scan depending on the value of the local variable but could have unintended side effects.

If that also isn't acceptable I think your only option is to use dynamic SQL which was already covered in another answer.


Pervasive linked server provider missing I am having the same problem as this question.

I installed Pervasive v11 (which does have 64bit ODBC drivers) and I still do not see Pervasive listed as a provider when trying to create a linked server in SQL Server Express 2008 R2. Does anyone have any ideas what to try?

You will need to set up a System DSN in order to connect to it using the ODBC drivers from SSMS/SQL Server - simple installing the drivers is not enough. I had  to do the same in order to connect to an older instance that our company uses. Here is an excellent link that will help guide you through the process:

http://discoverysoftware.com/support/kb/acorn-docs/bridges-acorn-docs/odbc-bridges/odbc-set-up-pervasive-sql/

Once you have set up the DSN within the System Data Sources you will be able to create a linked server to it.


SSIS Catalogue Inaccessible After Server Collation Change I changed the collation of a Microsoft SQL 2016 Standard server, by starting SQL server in single user admin mode and using the -q parameter to specify "Latin1_General_CI_AS" collation (previous setting was "SQL_Latin1_General_CP1_CI_AS"). Now when expanding the SSISDB Integration Services Catalogue, I get the below error.

Exception has been thrown by the target of an invocation. (mscorlib)

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

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

Cannot resolve collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in add operator occurring in SELECT statement column 1. (Microsoft SQL Server, Error: 451)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.4001&EvtSrc=MSSQLServer&EvtID=451&LinkId=20476

Everything else on the server seems to be OK, although the Agent XPs advanced setting got disabled, which I have corrected.

Is there a way to correct whatever is causing the collation conflict in the SSIS DB, or is the only solution to delete and recreate it?

Two things to consider:

Changing the Collation of an Instance via the -q method changes the Collation for all Databases, not just the system DBs. ( This option is undocumented and hence, unsupported. Please see Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong? for a detailed description of what that option does, as well as the potential scope of impact )
SSISDB, distributed by Microsoft, will have the same Collation for every installation since SSISDB is created from a backup file instead of a T-SQL script. There is an implication there that the Collation should probably not be changed. In addition, I found the following thread on the MSDN Forums -- SSISDB collation default: how to change? -- in which a Microsoft employee states:

  We do not support the changing of collation for SSISDB.

To be fair, it is also possible that a T-SQL install script could specify the Collation for the SSISDB Database, which would then have the same implication of it probably being best to keep that Collation. But, while a T-SQL install script can go either way, a backup being restored will be the Collation that the Database was when it was backed-up (hence the problem that sometimes occurs when restoring something like MSDB from an Instance that had a different default Collation: Example #1 and Example #2).

With those two points in mind:

You can try to change the Database and columns back to SQL_Latin1_General_CP1_CI_AS via ALTER DATABASE [SSISDB] COLLATE SQL_Latin1_General_CP1_CI_AS and then several ALTER TABLE ... ALTER COLUMN statements.

PRO: Existing data is not lost, with no need to export / import.

CON: You could miss one or more columns and the effect(s) might be hard to detect. While there are very few differences between those two Collations, they do not behave exactly the same, and I am not sure what expectations are in the code based on expecting the SQL_Latin1_General_CP1_CI_AS Collation. I'm not sure that I would be comfortable with this, mainly due to lack of certainty.
You can drop and recreate SSISDB (i.e. delete and re-deploy the catalog). If you have existing packages defined in there, you can export the data to import after the DB is recreated.

PRO: Know for sure that the issue is fixed.

CON: Extra work in exporting / importing data (and getting all tables, and in the correct order due to FKs).


Migrate complete postgres database cluster to another machine using pg_dump I am referring to

How to get a working and complete PostgreSQL DB backup and test

In this example/solution, which part of the restore process does actually the "CREATE DATABASE sandbox"? I have been searching for this answer a while now - it seems that pg_dump does not backup the names of the database it creates a backup for --> when I just backup (pg_dump) and restore (psql) it, i get "database sandbox does not exist".

So, if I want to backup and restore the whole cluster with pg_dump, do I have to create all databases on the new server manually? I would like to be able to restore single databases later on, so pg_dumpall doesn't seem to be the right way, or is it?

The question you are referencing is a bit of a mess, I think you would be better off not referring to it at all.  It just introduces misconceptions to then partially remove some of them.

If you want to backup and restore the whole cluster, you should use "pg_dumpall", not "pg_dump".  Using "pg_dumpall" without any of the restrictive options will emit the necessary "CREATE DATABASE" commands.

If you run "pg_dump" with -C option, it too will emit "CREATE DATABASE" commands.

  I would like to be able to restore single databases later on, so pg_dumpall doesn't seem to be the right way, or is it?

If you want to do something different in the future, then do something different in the future.  "pg_dumpall" preserves your options.  You can restore the whole thing, and then drop databases you don't want.  Or you can restore the whole, and re-dump what you do want out of it.  Or just dump what you want out of the original server, if that is still around.  Or use vi or sed or awk or perl to munge the "pg_dumpall" .sql file so that it does only what you want.

If you don't like any of those options, you could do a pg_dumpall -g accompanied with a pg_dump -C of each individual database.  If your primary use case to restore a single database but without knowing ahead of time which one it will be, this would be faster than the previous options, and could be run on a smaller machine than the original.  But it is also a bit harder to document, and more likely for files to get out of sync with each other.


Aggregating high volumes of data from many MySQL servers I have 4 servers of MySQL, each receives around 4 million records a day.

I need to aggregate all the inserted records into another main server after doing some mapping processing to the aggregated records. so this new server should receive around 16 million records per day.

I also need to make this operation as real time as possible, so I was thinking of a script running every minute or so that do the sync. or perhapse some other sync tool or mechanism you can suggest.

So, may question is:

Is MySQL the right choice for a 16-20 million records/day ? or I should try something else (MongoDB, Elasticsearch, .. etc)
What tool/language to give the best performance in such task?

Thank you.

Yes, MySQL will be able to do 20 million inserts per day, no problem. My calculator says 20 million per day translates to roughly 231 inserts per second. See e.g. this blog post from Percona's Database Performance Blog from 2010 which talks about more than 36 thousand writes per second. With the optimisations that have gone into MySQL since then as well as the hardware improvements, this number will obviously be even higher today.

And from a skills perspective, since you're already using MySQL, it might make sense to use that for this aggregation database as well.

Note that you can increase write throughput by combining multiple inserts together in a single transaction.

However, you may be able to use multi-source replication:

  MySQL Multi-Source Replication enables a replication slave to receive
  transactions from multiple sources simultaneously. Multi-source
  replication can be used to back up multiple servers to a single
  server, to merge table shards, and consolidate data from multiple
  servers to a single server. Multi-source replication does not
  implement any conflict detection or resolution when applying the
  transactions, and those tasks are left to the application if required.

So if you're able to use this, then you won't have to write any code to do the actual replication.


mongodb version difference between config and mongos In my mongodb config server has 3.4 version and mongos has 3.6 version but i am unable to start mongos service should both be same version as 3.6 then i have rollback both to 3.4 version now the mongos service started.my doubt is the config server and mongos server should have same version as 3.6 to start the mongos service

As Per MongoDB BOL Starting in 3.4, the use of the deprecated mirrored mongod instances as config servers (SCCC) is no longer supported. Before you can upgrade your sharded clusters to 3.4, you must convert your config servers from SCCC to CSRS.

To convert your config servers from SCCC to CSRS, see Upgrade Config Servers to Replica Set.

Where Config servers store the metadata for a sharded cluster. The metadata reflects state and organization for all data and components within the sharded cluster. The metadata includes the list of chunks on every shard and the ranges that define the chunks.

The mongos instances cache this data and use it to route read and write operations to the correct shards. mongosupdates the cache when there are metadata changes for the cluster, such as Chunk Splits or adding a shard. Shards also read chunk metadata from the config servers.

MongoDB also uses the config servers to manage distributed locks.

Each sharded cluster must have its own config servers. Do not use the same config servers for different sharded clusters.

WARNING:
Administrative operations conducted on config servers may have significant impact on sharded cluster performance and availability. Depending on the number of config servers impacted, the cluster may be read-only or offline for a period of time.

For your further Ref Here and Here


PostgreSQL - Cannot UPDATE pg_database I am not able to UPDATE table pg_database with user postgres. As you can see below, i am able to run the SQL query, however nothing changes.

postgres=# select datname, datdba, datistemplate, datallowconn from pg_database;
    datname     | datdba | datistemplate | datallowconn
----------------+--------+---------------+--------------
 postgres       |     10 | f             | t
 template1      |     10 | t             | t
 template0      |     10 | t             | f
 my_template_1  |     10 | f             | t
(4 rows)

postgres=# UPDATE pg_database set datistemplate=true, datallowconn=false where datname='my_template_1' ;
UPDATE 1
postgres=# select datname, datdba, datistemplate, datallowconn from pg_database;
    datname     | datdba | datistemplate | datallowconn
----------------+--------+---------------+--------------
 postgres       |     10 | f             | t
 template1      |     10 | t             | t
 template0      |     10 | t             | f
 my_template_1  |     10 | f             | t
(4 rows)

postgres=#

You can't update pg_database to change a database (you shouldn't be updating system tables at all, although you can do it for some of them).

Use ALTER DATABASE instead:

alter database my_template_1
  with is_template true
       allow_connections false;


How to select records that meets specific criteria I want to get the result for a particular ID if only all the "Prospect" have after it "Qualify", if not all the calculation will be dropped

Below is example sample and output. How it can be done using sql query?

+----+----------+-----------+-------+
| id |  stage   | nextstage | value1 |
+----+----------+-----------+-------+
|  1 | prospect | qualify   |   200 |
|  1 | solution | develop   |   500 |
|  1 | prospect | null      |   400 |
|  2 | prospect | qualify   |   100 |
|  2 | develop  | solution  |   300 |
|  2 | prospect | qualify   |   100 |
+----+----------+-----------+-------+

Output:

+----+-------------+
| id | time in P2Q |
+----+-------------+
|  1 | NULL        |
|  2 | 200         |
+----+-------------+

I have tried the following, but didn't know how to drop the entire calculation if there is one prospect record that don't have qualify after it

SELECT

 SUM(CASE WHEN stage = 'Prospect' and nextstage='Qualify'  THEN value1 END) AS timeP2Q
 from table1
 group by ID

To solve this problem, I did the following:

Created table and data:

CREATE TABLE table1
(
  id INTEGER NOT NULL,
  stage VARCHAR (10),
  nextstage VARCHAR (10),
  value1 INTEGER
);

INSERT INTO table1
VALUES
(1, 'prospect', 'qualify' , 200),
(1, 'solution', 'develop' , 200),
(1, 'prospect',  NULL     , 200),
(2, 'prospect', 'qualify' , 100),
(2, 'develop' , 'solution', 200),
(2, 'prospect', 'qualify' , 100);

Then ran the following query:

SELECT id, timeP2Q FROM
(
  SELECT
    id,
    SUM
    (
      CASE
        WHEN stage = 'prospect' and nextstage = 'qualify'  THEN value1 END
    ) AS timeP2Q
  FROM table1
  WHERE id NOT IN
  (
    SELECT id
    FROM table1
    WHERE stage = 'prospect' AND nextstage IS NULL
  )
  GROUP BY id
) AS stuff  -- subqueries require an alias, not used.
ORDER BY id;

Which gives the desired result:

 id | timep2q
----+---------
  2 |     200
(1 row)

The main query simply does the sum by id, and then the WHERE id NOT IN removes the unwanted ones. I think that there should be a date or a timestamp in there somewhere to be able to better extract information from this data! I see this as a process with stages?


View history of preferred replica in Availability Group - when Is it possible to view a history of when the preferred replica in Availability Group changed?

E.g.

<datetime> Preferred replica changed from InstanceA to InstanceB

Not expecting it to be in any specific format, just trying to be clear as to what I am referring to.

... the preferred replica in Availability Group changed?

Availability Groups don't have preferred replica. They have primary and secondary replicas, but not preferred. Windows Server Failover Clustering has preferred owners. Are you looking for who was the primary replica or who was a preferred owner?

If it's the WSFC preferred owner you are looking for, then you'll have to scrape the cluster log.

If it's the primary and secondary replicas, then nothing exists out of the box - you'll need to write your own. This data can be gathered from a few different sources:

AlwaysOn Health extended event session
SP_Server_Diagnostics output
Polling the DMVs and recording changes
Scraping the cluster log

You don't need all of those, it's a mix and match sort of thing. Basically you'll want to find out who was primary at the moment the logs started and then look for replica state changes. You'll need all of the logs from all of the replicas to get the most complete picture - but having a single log from a single replica can give you all the data needed as well, just takes a bit more data manipulation to get there.


How to design functionality to comment about a user joining a Facebook group? User A joined the group DBA

The table responsible for users x groups is "Following" table.

    user_id      group_id
       1             1
       1             2
       2             1

And in my app I have a table Comments. the foreign key of it is polimorphic, because comments can happen inside posts, photos, etc..

      text       parent_type      parent_id
     "cool"         Post             1
     "nice pic"     Photo            2
     "welcome!"     Following        3

It is a good schema? The following table has_many comments?

And a more complex one would be: "How to comment on a set of people joining?"

I think if you want to be able to comment on someone joining a group, then "join a group" must be an entity that can be referenced by other records. You might want to have another "parent_type" called "event" and one of the event types would be "joined a group". That would let you reference it from your comments table.


PostgreSQL schemas, select same table from all schemas My app is used by about 80k users every month.  I want to create separate PostgreSQL schemas for each user (a-la SaaS, for protection).

My app allows users to search other user's profiles, via:

SELECT user_id FROM user_table WHERE city = 'NYC';

What is the correct way to implement the same search functionality with unique schemas in place for each user?

Will performance suffer?

This might be a good case for using table inheritance.  Create the initial table in the master schema and then make all user tables inherit it.

The key to making this work well is to see the master table as as abstract mixin rather than a typical public class inheritance.  The parent table then gives you, essentially, a catalog of all child tables' contents.   This becomes problematic if you also insert into the parent table so you may want to use a trigger to avoid that.


MySQL select takes 0.2 sec; equivalent UPDATE takes 1.5 minutes! This query runs in 0.2 seconds and finds 9 rows:

SELECT S.id, S.frame, S.active,
  GF.groupNum, GF.frameDesc
FROM skus S
  INNER JOIN group_frames GF ON (GF.frame = S.frame)
WHERE GF.groupNum = 204 AND GF.frameDesc LIKE '%lumbar%';

This query runs in 1.5 minutes and updates the same 9 rows:

UPDATE skus S
  INNER JOIN group_frames GF ON (GF.frame = S.frame)
SET S.active = 0, S.updated_by=101355, updated_at = NOW()
WHERE GF.groupNum = 204 AND GF.frameDesc LIKE '%lumbar%';

... while this incredibly-hackish version runs in 0.5 seconds and updates the same rows:

UPDATE skus S SET S.active = 0, S.updated_by=101355, updated_at =
NOW() WHERE S.id IN (   SELECT id FROM (    SELECT S1.id    FROM skus S1
        INNER JOIN group_frames GF ON (GF.frame = S1.frame)
WHERE GF.groupNum = 204 AND GF.frameDesc LIKE '%lumbar%' ) S3)

(using a dummy inner-query to defeat the query-optimizer complaining about the same table being used in a FROM clause)

Why on earth did the second query take so long?!

I don't know the answer to your question, but you might try a regular update like:

UPDATE skus S
    SET S.active = 0
      , S.updated_by=101355
      , S.updated_at = NOW()
WHERE EXISTS (
    SELECT 1
    FROM group_frames GF
    WHERE GF.frame = S.frame
      AND GF.groupNum = 204
      AND GF.frameDesc LIKE '%lumbar%'
);

Indexes like:

CREATE INDEX ... ON skus (frame);
CREATE INDEX ... ON group_frames (groupNum, frame, frameDesc);

will probably help


postgresql : find privileges in meta informations With PostgreSQL 9.4 on linux centos 6.6.

I give a privilege to a user with something like :

ALTER DEFAULT PRIVILEGES FOR ROLE DBNAME_adm IN SCHEMA DBNAME_adm GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO DBNAME_usr;

I need to have a SQL statement to find if this privilege is already ok or not.
I did not find any table in pg_catalog nor information_schema which contains such an information.

Where can I find default privileges metadata ?

I find it by myself !

Tables to be parsed to have all metadata on ACL and privileges given by ALTER DEFAULT PRIVILEGES can be found in pg_catalog.pg_default_acl and pg_catalog.pg_namespace with the help of pg_catalog.pg_authid.

Some references :

http://www.postgresql.org/docs/9.4/static/catalog-pg-default-acl.html
http://www.postgresql.org/docs/9.4/static/catalog-pg-namespace.html


DataGrip commands with non-US keyboard DataGrip editor has command ⌘-/ to "Comment with line comment", in SQL that means adding -- to beginning of line.

On my MacBook Pro with Finnish keyboard, / is located in shift-7. When I press ⌘-shift-7, DataGrip opens search box in Help menu or flashes Code menu title, instead of commenting current line. Selecting "Comment with line comment" from Code menu works as expected.

Is there a keyboard shortcut that works on my keyboard?

Go to Settings -> Keyboard and change the shortcut for the action


Insert values into new columns using values from old columns without locking table The premise is simple - a table has a jsonb column; this json value always has three fields, so I decided to split the three fields into three separate columns on the same table.
Since the table is really large (in the ~100m range), I need a way to insert these json values into their own respective columns without locking the table.
Is there a way to achieve this?

You cannot avoid locking. Keep your transactions short and the number of rows locked reasonable by performing the update in batches, e.g. by primary key range. That reduces the danger of deadlocks.


SSIS 2016 Access Denied I followed below steps after SQL Server and Integration Services, but I am still getting an error.
I added SQL service account in Microsoft SQL Server Integration Services 13.0 properties which is under DCOM Config, and granted remote permissions to both service account and dba group account under Launch & Activation Permission & Access Permissions but still getting an error:

connecting to the Integration Services service on the computer SERVER failed with the following error:
Access is denied.
By default only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service.

Windows 2016 Standard and SQL Server 2016 Standard Edition.

Two thoughts come to mind:

You may have missed a step in the configuration setup - Check Eliminating the "Access Is Denied" Error
Is this part of a SQL Server upgrade?  If so, you may need to install SSMS for that release.  The SSMS 17+ do not support connections to Integration Services on earlier versions of SQL Server.  Previous SSMS releases


SQL Server Editions: Backup restore of Enterprise Edition 2014 on Standard Edition 2017 I know, this kind of question has already been answered some times but I don't find some current answers and wonder if SQL Server Standard Edition 2016 SP1 has changed something in the matter as it supports all "programmability features" earlier supported by enterprise edition only.

My specific scenario is as follows:
I have got a central backup server which will be running SQL Server 2017 Standard Edition and regulary restores new backups taken from other SQL Server instances. Most instances will be on SQL Server 2017 Standard as well...however there is a Data Warehouse running SQL Server 2014 Enterprise Edition.

Is it possible to restore backups taken from  a SQL Server 2014 Enterprise Edition instance on a SQL Server 2017 Standard Edition instance or could this result in an error regarding to Enterprise Edition features?
Is there any way to try this out for free? SQL Server evaluation edition is equivalent to Enterprise Edition isn't it or has Microsoft now introduced a way to downgrade developer and evaluation licenses to SQL Server Standard Edition features?

Thank you very much in advance for your assistance

I believe it is possible to restore with same old answer that subject to condition you are not using any "enterprise specific" features. This you can get from

select * from sys.dm_db_persisted_sku_features.

If you are using any such feature you would have to stop using it, take fresh backup and then restore. I know its hard to tell because starting from SQL Server 2016 Sp1 standard provides far more features support. So at the last the answer would be you need to restore and test.

Good news is SQL Server 2017 RTM Evaluation Edition is released so you have the Eval edition for your testing.


Loop through Self JOIN on table until the operand column is NULL completely For the problem I have a single table which store a Member's ID and His Referee's ID. This is something like a Multi-Level-Marketing plan where you join a company for some tasks and then someone join from your reference and appear under your downline and so on and so forth. The scheme I am using is not necessarily a binary tree, there can be many users under a single user.

I want to retrieve the entire tree under a specific user, I am using below query to retrieve the records but this is not the solution I want.

SELECT t1.user AS lvl1, t2.user AS lvl2, t3.user AS lvl3,
       t4.user AS lvl4, t5.user AS lvl5
FROM reference t1
LEFT JOIN reference t2 ON t2.referee = t1.user
LEFT JOIN reference t3 ON t3.referee = t2.user
LEFT JOIN reference t4 ON t4.referee = t3.user
LEFT JOIN reference t5 ON t5.referee = t4.user
WHERE t1.referee = 1

This query will get me the tree structure under user 1 but only 5 level deep.

Now I want to retrieve the entire tree upto last level but cant figure out a way for same. Tree depth can be anything depending on the users referrals so I cannot even guess the depth (That wont make sense anyway).

All I want right now is to perform the JOIN and keep watching the selected column (in our case user) from last JOIN to check if all the values in it are NULL. If the column from last JOIN is NULL then it means the tree is completed and query should return the result.

Can anyone suggest a workaround?
I can make changes in the table structure if necessary but that should not effect the behavior of other operations.

Although MySQL has no CTE functionality, there are two major ways to create CTE expressions:
TECHNIQUE #1 : Write Stored Procedures to Traverse Recursively
Rather than Reinventing the Wheel, please see my past posts on how to make stored procedures

Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs
Oct 26, 2012 : Hierarchical queries without CTEs

TECHNIQUE #2 : Iterate Based on the Tree's Maximum Depth
First, here is some sample data
DROP DATABASE IF EXISTS dufran;
CREATE DATABASE dufran;
USE dufran;
CREATE TABLE reference
(
    user INT NOT NULL AUTO_INCREMENT,
    referee INT NOT NULL DEFAULT 0,
    name VARCHAR(20) NOT NULL,
    PRIMARY KEY (user)
);
INSERT INTO reference (name,referee) VALUES ('Rolando',0);
INSERT INTO reference (name,referee) VALUES ('Pamela',1);
INSERT INTO reference (name,referee) VALUES ('Carlik',2);
INSERT INTO reference (name,referee) VALUES ('Javonne',2);
INSERT INTO reference (name,referee) VALUES ('Dominique',2);
INSERT INTO reference (name,referee) VALUES ('Diamond',2);
INSERT INTO reference (name,referee) VALUES ('Azalia',3);

When loaded, this is the data
mysql> SELECT * FROM reference;
+------+---------+-----------+
| user | referee | name      |
+------+---------+-----------+
|    1 |       0 | Rolando   |
|    2 |       1 | Pamela    |
|    3 |       2 | Carlik    |
|    4 |       2 | Javonne   |
|    5 |       2 | Dominique |
|    6 |       2 | Diamond   |
|    7 |       3 | Azalia    |
+------+---------+-----------+
7 rows in set (0.00 sec)

mysql>

Here is how you compute the maximum depth
SET @depth = 0;
SELECT MAX(@depth:=@depth+1) max_depth FROM
(SELECT DISTINCT referee FROM reference) A;

Here is the maximum depth for the sample data
mysql> SET @depth = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT MAX(@depth:=@depth+1) max_depth FROM
    -> (SELECT DISTINCT referee FROM reference) A;
+-----------+
| max_depth |
+-----------+
|         4 |
+-----------+
1 row in set (0.00 sec)

mysql>

Using this principle, iterate for the SELECT columns and the LEFT JOIN clauses
SET @depth = 0;
SELECT GROUP_CONCAT(CONCAT('t',dep,'.user AS lvl',dep))
INTO @sqllist FROM
(SELECT @depth:=@depth+1 dep FROM
(SELECT DISTINCT referee FROM reference) AA) A;
SET @depth1 = 0;
SET @depth2 = 1;
SELECT GROUP_CONCAT(sqljoin SEPARATOR ' ') INTO @sqljoins
FROM (SELECT CONCAT('LEFT JOIN reference t',@depth2:=@depth2+1,
' ON t',@depth2,'.referee = t',@depth1:=@depth1+1,'.user') sqljoin
FROM (SELECT DISTINCT referee FROM reference) AA) A;
SELECT @sqljoins;
SET @sqlstmt = CONCAT('SELECT ',@sqllist,
' FROM reference t1 ',@sqljoins);
SELECT @sqllist\G
SELECT @sqljoins\G
SELECT @sqlstmt\G

Here is the SQL generated
mysql> SET @depth = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(CONCAT('t',dep,'.user AS lvl',dep))
    -> INTO @sqllist FROM
    -> (SELECT @depth:=@depth+1 dep FROM
    -> (SELECT DISTINCT referee FROM reference) AA) A;
Query OK, 1 row affected (0.00 sec)

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

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

mysql> SELECT GROUP_CONCAT(sqljoin SEPARATOR ' ') INTO @sqljoins
    -> FROM (SELECT CONCAT('LEFT JOIN reference t',@depth2:=@depth2+1,
    -> ' ON t',@depth2,'.referee = t',@depth1:=@depth1+1,'.user') sqljoin
    -> FROM (SELECT DISTINCT referee FROM reference) AA) A;
Query OK, 1 row affected (0.00 sec)

mysql>     SELECT @sqllist\G
*************************** 1. row ***************************
@sqllist: t1.user AS lvl1,t2.user AS lvl2,t3.user AS lvl3,t4.user AS lvl4
1 row in set (0.00 sec)

mysql>     SELECT @sqljoins\G
*************************** 1. row ***************************
@sqljoins: LEFT JOIN reference t2 ON t2.referee = t1.user LEFT JOIN reference t3 ON t3.referee = t2.user LEFT JOIN reference t4 ON t4.referee = t3.user LEFT JOIN reference t5 ON t5.referee = t4.user
1 row in set (0.00 sec)

mysql>     SELECT @sqlstmt\G
*************************** 1. row ***************************
@sqlstmt: SELECT t1.user AS lvl1,t2.user AS lvl2,t3.user AS lvl3,t4.user AS lvl4 FROM reference t1 LEFT JOIN reference t2 ON t2.referee = t1.user LEFT JOIN reference t3 ON t3.referee = t2.user LEFT JOIN reference t4 ON t4.referee = t3.user LEFT JOIN reference t5 ON t5.referee = t4.user
1 row in set (0.00 sec)

mysql>

Here is the big question, does the SQL work ??? Take the SQL and run it dynamically:
PREPARE s FROM @sqlstmt;
EXECUTE s;
DEALLOCATE PREPARE s;

Here is the result:
mysql> PREPARE s FROM @sqlstmt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE s;
+------+------+------+------+
| lvl1 | lvl2 | lvl3 | lvl4 |
+------+------+------+------+
|    1 |    2 |    3 |    7 |
|    1 |    2 |    4 | NULL |
|    1 |    2 |    5 | NULL |
|    1 |    2 |    6 | NULL |
|    2 |    3 |    7 | NULL |
|    2 |    4 | NULL | NULL |
|    2 |    5 | NULL | NULL |
|    2 |    6 | NULL | NULL |
|    3 |    7 | NULL | NULL |
|    4 | NULL | NULL | NULL |
|    5 | NULL | NULL | NULL |
|    6 | NULL | NULL | NULL |
|    7 | NULL | NULL | NULL |
+------+------+------+------+
13 rows in set (0.03 sec)

mysql> DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)

mysql>

I scripted this to give you a starting point and some homework. Here is what I mean: You probably  don't want to see NULL columns. You could do one of two things:

Convert each NULL to IFNULL(lvl1,''), GROUP_CONCAT all columns.
Compute the depth of a particular id and gather columns for it only.

Give it a Try !!!
UPDATE 2016-06-14 16:36 EDT
Someone just commented

Your max depth calculation is wrong. Proof: Add to your sample data: INSERT INTO reference (name,referee) VALUES ('Maria',4);

Let's try it out
mysql> INSERT INTO reference (name,referee) VALUES ('Maria',4);
Query OK, 1 row affected (0.00 sec)

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

mysql> SELECT MAX(@depth:=@depth+1) max_depth FROM
    -> (SELECT DISTINCT referee FROM reference) A;
+-----------+
| max_depth |
+-----------+
|         5 |
+-----------+
1 row in set (0.00 sec)

OK, it says the depth is 5. Is there visible proof of this ?
mysql> SET @depth = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(CONCAT('t',dep,'.user AS lvl',dep))
    -> INTO @sqllist FROM
    -> (SELECT @depth:=@depth+1 dep FROM
    -> (SELECT DISTINCT referee FROM reference) AA) A;
Query OK, 1 row affected (0.00 sec)

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

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

mysql> SELECT GROUP_CONCAT(sqljoin SEPARATOR ' ') INTO @sqljoins
    -> FROM (SELECT CONCAT('LEFT JOIN reference t',@depth2:=@depth2+1,
    -> ' ON t',@depth2,'.referee = t',@depth1:=@depth1+1,'.user') sqljoin
    -> FROM (SELECT DISTINCT referee FROM reference) AA) A;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @sqljoins;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @sqljoins                                                                                |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LEFT JOIN reference t2 ON t2.referee = t1.user LEFT JOIN reference t3 ON t3.referee = t2.user LEFT JOIN reference t4 ON t4.referee = t3.user LEFT JOIN reference t5 ON t5.referee = t4.user LEFT JOIN reference t6 ON t6.referee = t5.user |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET @sqlstmt = CONCAT('SELECT ',@sqllist,
    -> ' FROM reference t1 ',@sqljoins);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @sqllist\G
*************************** 1. row ***************************
@sqllist: t1.user AS lvl1,t2.user AS lvl2,t3.user AS lvl3,t4.user AS lvl4,t5.user AS lvl5
1 row in set (0.00 sec)

mysql> SELECT @sqljoins\G
*************************** 1. row ***************************
@sqljoins: LEFT JOIN reference t2 ON t2.referee = t1.user LEFT JOIN reference t3 ON t3.referee = t2.user LEFT JOIN reference t4 ON t4.referee = t3.user LEFT JOIN reference t5 ON t5.referee = t4.user LEFT JOIN reference t6 ON t6.referee = t5.user
1 row in set (0.00 sec)

mysql> SELECT @sqlstmt\G
*************************** 1. row ***************************
@sqlstmt: SELECT t1.user AS lvl1,t2.user AS lvl2,t3.user AS lvl3,t4.user AS lvl4,t5.user AS lvl5 FROM reference t1 LEFT JOIN reference t2 ON t2.referee = t1.user LEFT JOIN reference t3 ON t3.referee = t2.user LEFT JOIN reference t4 ON t4.referee = t3.user LEFT JOIN reference t5 ON t5.referee = t4.user LEFT JOIN reference t6 ON t6.referee = t5.user
1 row in set (0.00 sec)

mysql> PREPARE s FROM @sqlstmt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

Guess what the output says ?
mysql> EXECUTE s;
+------+------+------+------+------+
| lvl1 | lvl2 | lvl3 | lvl4 | lvl5 |
+------+------+------+------+------+
|    1 |    2 |    3 |    7 | NULL |
|    1 |    2 |    4 |    8 | NULL |
|    1 |    2 |    5 | NULL | NULL |
|    1 |    2 |    6 | NULL | NULL |
|    2 |    3 |    7 | NULL | NULL |
|    2 |    4 |    8 | NULL | NULL |
|    2 |    5 | NULL | NULL | NULL |
|    2 |    6 | NULL | NULL | NULL |
|    3 |    7 | NULL | NULL | NULL |
|    4 |    8 | NULL | NULL | NULL |
|    5 | NULL | NULL | NULL | NULL |
|    6 | NULL | NULL | NULL | NULL |
|    7 | NULL | NULL | NULL | NULL |
|    8 | NULL | NULL | NULL | NULL |
+------+------+------+------+------+
14 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)

mysql>

Just as my code predicted. The depth is 5 and the Dynamic SQL just proved it.


How to include a where clause in a query from another column I have two tables:
pointer_table(col_name varchar, col_val varchar, wh_clause varchar)
value_table(col_1 varchar, col_2 varchar, value varchar)

col_name in pointer_table denotes the column in value_table (either col_1 or col_2), while wh_clause contains a complex where clause to be used in the value_table.

When working with col_name and col_val, it's easy to join the two tables eg:

select vt.value
from (select col_val from pointer_table where col_name = 'col_1') pt
inner join (select col_1, value from value_table) vt
  on pt.col_val = vt.col_1

However I sometimes need to apply the contents of pointer_table.wh_clause on value_table (wh_clause contains conditions like col_1 = 'pine' and col_2 = 'Argentina'.

Is it possible in DB2 to somehow apply the content of that field as where clause for another table in a query?

If you implement the process as a stored procedure, it can generate the dynamic SQL based on the rules in your base tables and then execute that dynamic statement.

From the DB2 CLP, it's possible to write a query (probably containing one or more CTEs) that generates the SQL. The EXPORT utility can write the results of that query to a text file whose name is already in your master script's list of files to execute.

Another option is CLPPlus, which IBM based on SQLPlus from Oracle. It has some capabilities to iteratively construct dynamic SQL in a scratchpad-like area and then execute it.


Updating values based on 3 table join I have three tables orders, customer_old and customer_new.

The orders table contains the old_user_login and associated order number.
The customer_old table contains the information on
customer, namely old_user_login and user_fullname.
The customer_new table contains new_user_login and user_fullname.

The idea is to replace the old_user_login with new_user_login for all the rows in the orders table, and the only thing that matches is the user_fullname in the two customer tables. The old customer table will effectively be made redundant and deleted after this operation is done.

How do I go about doing this?

Here is an example of how that could be done:

--demo setup
Declare @Orders table (OrderNumber int, UserLogin varchar(20))
Declare @CustomerOld table (UserLogin varchar(20), UserFullName varchar(30))
Declare @CustomerNew table (UserLogin varchar(20), UserFullName varchar(30))

insert into @Orders (OrderNumber,UserLogin) values(1,'SillySam')
insert into @CustomerOld(UserLogin,UserFullName) values('SillySam','Sam Snyder')
insert into @CustomerNew(UserLogin,UserFullName) values('SillySamUpdated','Sam Snyder')

--solution
UPDATE o
SET o.UserLogin = cn.UserLogin  --update orders.UserLogin with value retrieved from CustomerNew
FROM @Orders o
JOIN @CustomerOld co        --join CustomerOld to Orders using UserLogin
    ON co.UserLogin = o.UserLogin
JOIN @CustomerNew cn        --join CustomerNew to CustomerOld using UserFullName from both tables
    ON cn.UserFullName = co.UserFullName

--verify
select * from @Orders

OrderNumber UserLogin
1           SillySamUpdated


Basic RDBMS table relations If you have a design of a "master table" and several "subtables", is it better* that the master table "link" to each of the sub tables, the subtables link to the master, or for bi-directional links be established? The data present in the subtables COULD be not present (i.e. it might not be relevant to the particular entry, so a linked entry in the subtable won't be created). Basically data will be related in a 1-to-(zero or one) fashion. There will never be a reason to link between the subtables WITHOUT involving the master table.

Specifically I'm concerned about better* with respect to MS SQL Server (2008R2+)

Example Design 1: Master Table links to Sub-tables

CREATE TABLE tMaster (
  PK_TM int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,IntrinsicField1 datetime NOT NULL
  ,IntrinsicField2 int NOT NULL
  ,IntrinsicField3 nvarchar(255) NULL
  ,index_subA int NULL --index created on this value. links to PK_SA of appropriate entry if it exists
  ,index_subB int NULL --index created on this value
  ....
  ,index_subM int NULL --index created on this value
)
CREATE TABLE tSubA
  PK_SA int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,ExtraField1 int NOT NULL
  ,ExtraField2 varchar(24) NOT NULL
)
CREATE TABLE tSubB
  PK_SB int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,ExtF1 NUMERIC(5,3) NOT NULL
  ,ExtF2 NUMERIC(5,3) NOT NULL
)
CREATE TABLE tSubM
  PK_SM int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,EField1 int NOT NULL
  ,EField2 nvarchar(100) NOT NULL
)

Example Design 2: Sub-Tables link to Master Table

CREATE TABLE tMaster (
  PK_TM int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,IntrinsicField1 datetime NOT NULL
  ,IntrinsicField2 int NOT NULL
  ,IntrinsicField3 nvarchar(255) NULL
)
CREATE TABLE tSubA
  lMaster_ID int NOT NULL PRIMARY KEY CLUSTERED -- value of PK_TM of cross-linked entry
  ,ExtraField1 int NOT NULL
  ,ExtraField2 varchar(24) NOT NULL
)
CREATE TABLE tSubB
  lMaster_ID int NOT NULL PRIMARY KEY CLUSTERED
  ,ExtF1 NUMERIC(5,3) NOT NULL
  ,ExtF2 NUMERIC(5,3) NOT NULL
  ,Master_ID int NOT NULL --index created on this value. links to PK_TM of appropriate entry
)
CREATE TABLE tSubM
  lMaster_ID int NOT NULL PRIMARY KEY CLUSTERED
  ,EField1 int NOT NULL
  ,EField2 nvarchar(100) NOT NULL
)

Example Design 3: Bidirectional links

CREATE TABLE tMaster (
  PK_TM int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,IntrinsicField1 datetime NOT NULL
  ,IntrinsicField2 int NOT NULL
  ,IntrinsicField3 nvarchar(255) NULL
  ,index_subA int NULL --index created on this value. links to PK_SA of appropriate entry if it exists
  ,index_subB int NULL --index created on this value
  ....
  ,index_subM int NULL --index created on this value
)
CREATE TABLE tSubA
  PK_SA int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,ExtraField1 int NOT NULL
  ,ExtraField2 varchar(24) NOT NULL
  ,lMaster_ID int NOT NULL
)
CREATE TABLE tSubB
  PK_SB int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,ExtF1 NUMERIC(5,3) NOT NULL
  ,ExtF2 NUMERIC(5,3) NOT NULL
  ,lMaster_ID int NOT NULL
)
CREATE TABLE tSubM
  PK_SM int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,EField1 int NOT NULL
  ,EField2 nvarchar(100) NOT NULL
  ,lMaster_ID int NOT NULL
)

*better could be

faster
less likely to bork up over time as DB entries are added and/or occasionally deleted
easier for other DBAs to quickly comprehend
more intuitive for the average end-user
more easily extensible by adding additional sub-table types
more data-compact
more easily modified to be a one master to many copies of a subtable
more easily modified to be a many masters to a single copy of a subtable
insert your own definition here

(less emphasis on options 7&8 here)

There is not really much to talk about here.  At least, the conventional wisdom is very clear.  Only option 2 should be considered unless you have a very, very good reason, borne out of practical experience with your database, to do otherwise.

Neither option 1 nor option 3 have a parent table in first normal form (1NF) because they include repeating columns, i.e. the foreign keys to the sub-tables.

In a transactional system, you should start from the assumption that your tables should be in 3NF at least.  Back away from that only for a good, well considered reason.

One such reason might be that there are lots of sub-tables and you don't know which one(s) might need to be joined to the parent for a particular parent record.  It could happen that a performance issue results, depending on many factors.  One compromise that you could use if this problem did occur, which you might consider Option 4 is to have bit flags on the parent table indicating the presence of each type of child.  This is still begging for an eventual data inconsistency problem, but it at least will be less inconsistent than option 3, because it risks only pointing you to an empty join rather than to an incorrect record.


How to set lower case for identifiers in Toad? I'm using Toad to write queries agains an Oracle database. When I write table_name. I get an auto-suggestion menu with all the column names. But if I choose a column name, both the table name and column name is written by uppercases. Is there any way to change this to be written in lowercases instead?

In View > Formatting Option > Case "Identifiers" are set to Lowercase

I found the following on Quest.com.  It works, but may have undesirable side effects.

  Go to View | Toad Options in the main menu.
  On the left-hand side of the options window go to Editor | Behavior.
  Under the Language section, click on the Syntax highlighting button.
  In the new window, under styles on the left hand side, select "Indentifer" and select Lowercase for Capitalization effect on the
  right hand side.
  Under styles on the left hand side, select "Toad_user_table" and select Lowercase for Capitalization effect on the right hand side..
  Click on Apply and OK.


Analysis Service Connection String I use sql server 2012, and have multi cube.

I can connect to my created cube from Excel (Data Tab\Connections) with windows authentication. How can set permission to SQL Server user on my cube. and how can connect to cube with SQL Server user.

thanks in advance.

It's possible and not ;) Basicly SSAS allow only to have a Windows Authentication. If you want to restrict some data/cubes and so on you can prepare a role and everything should work fine.

Another solution that you can use is the dynamic security. You can add to ssas a table with a user mapping and then modify the cube to return only atributes for current user (windows user)

Last solution that match the best for you, but is not good at all is to share the ssas via http (IIS Server and msmdpump.dll). Than you can connect to ssas using for example one windows account and than in the CUstomData connection stirng parameter send the user sql id and check this custom data in the ssas via custom assemblies. It's a horros in management so i am not recommanding this solution. Addictionally if you will want to connect to this cube from Excel you will write some addin to excel or teach the user to prepare a connection string... Don't do that..

You can also write a script using AMO to crate dynamicly the ssas Role. You can read the sql user make for him the ssas role and set what he can see in your cube.

Maybe the best solution for you will be to use the SSRS reports where you can easily set some custom authentication, what do u think?


Users cannot view tables in non-default schema in SSMS I'm having an issue setting the VIEW DEFINITION permission appropriately at the schema level for one of my users. I've created the schema TestSchema and added some tables. The user currently has permissions set to access & modify the table (SELECT, UPDATE, DELETE, etc) through the dbo_datareader and dbo_datawriter roles. However, they cannot see any of the tables in the SSMS object explorer.

I've tried granting permissions to view definitions:

grant view definition on SCHEMA :: [TestSchema] to [User]

That didn't work. I tried setting the table-level permission:

grant view definition on [TestSchema].[NewTable] to [User]

That also didn't work. Then I tried just a blanket grant:

grant view definition to [User]

And that did work; they can now see TestSchema, as well as other schemas that they shouldn't have access to.

My goal here is to allow the user to view all tables within a given schema. How do I accomplish that? If I should be able to do this by default, what permissions should I be looking at to find why I can't?

Short answer: Don't use db_datareader or db_datawriter or their deny equivalents. They are for backwards compatibility only. Using them will cause issues like the one you are facing.

If you want to give principal Alice the SELECT, INSERT, UPDATE and DELETE permissions to all table-valued objects in schema Sales then use the following.

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Sales TO Alice ;

If you want to give principal Alice the SELECT, INSERT, UPDATE and DELETE permissions to all table-valued objects in all schemas then use the following.

GRANT SELECT, INSERT, UPDATE, DELETE TO Alice ;

Metadata visibility will then work correctly.


Troubleshooting Powershell errors from SQL Agent job I have a powershell script that I am trying to add in to a sql agent job, but the job step is failing with the following error

    A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module SQLPS  -DisableNameChecking'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Could not load file or assembly 'file:///C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSSnapins.dll' or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.

I assumed that the error was being caused by differences in the module path, so I explicitly set the path via $env:PSModulePath to match the path of my powershell session where the code runs fine.  I am pretty new to powershell, so any help you could provide would be appreciated

I am just doing some testing right now, so everything is running locally on my desktop.  I am running SQL Server 2012.

When you run a SQL Agent job step with the "PowerShell" type, you aren't actually running in (full) PowerShell. This job step type actually runs under the sqlps.exe minishell, which is based on PowerShell 1.0 and an extremely limited number of cmdlets.

Practically speaking, the PowerShell job step type is not very useful. Instead, I recommend using an "Operating System/CMDExec" job step, and calling PowerShell.exe from the command line.

Using the cmdexec job step type, your job step would look something like this:

PowerShell.exe "D:\Scripts\MyScript.ps1" -NonInteractive

By calling PowerShell.exe, you get the full/current PowerShell shell (instead of the mini shell), and you get to use the full/current SQL Server PowerShell module. This is particularly important if you are using SQL Server 2016+, where the SQLSERVER module has been enhanced with a much longer list of cmdlets.


How to store logs correctly? (This is somewhat a follow up on my last question)

I am using MySQL 5.5 with innodb.

My Log table consists of two columns. One for user number, and the other for a string consisting of IP, request method, request body, and so on. The two columns use no index.

Usually the content of the logs are below 200 letters. However, there are special cases that the content go up to more than 5000 words.

I've thought of three ways to log long contents. First is to split the content into 200 words and put it in several rows. Second is to set the data type to TEXT or VARCHAR(10000). Third is to make a new table only for storing long contents.

What is the normal approach to storing logs?

I would not use any of those options: I would properly split out each part of the log entry, putting each in its own column of appropriate maximum length. This is more work of course as you need to break the data down for insert if you are getting it as a single string (it'll also consume a bit more disk space, probably) but it will greatly increase the analysis you can do on the logs later.

Also define useful indexes. At very least something on that ordering column and/or if there is a date+time in those logs there should be an index covering that.

If you are just storing logs as blocks of text per line then there is very little point pushing them into the database: just keep them in their original flat files where you can use grep and other such tools. If you want to take advantage of the database's features at all, create a table optimised for making best use of the DB instead of optimising solely for easiest insert convenience.

You could also break down the consistent parts of query strings, form parts, and cookies (I'm assuming these are HTTP(S) logs) into their own sub-tables, but unlike separating basic log variables that is probably going to be considerable overkill.


SQLite WITH + UNION issue my UNION queries cannot work when tables created by WITH are used, is this a SQLite limitation? for example -

This works:

with
T_one as (
    select * from T
),
T_two as (
    select * from T
)
select * from T_one

This does not work:

--union cannot see tables made by with
with
T_one as (
    select * from T
),
T_two as (
    select * from T
)
select * from T_one union select * from T_two

  (sqlite3.OperationalError) no such table: T_one [SQL: u'--union cannot see tables made by with\nwith\nT_one as (\n    select * from T\n),\nT_two as (\n    select * from T\n)\nselect * from T_one union select * from T_two']

I was running the query inside python 2.7.10, which comes with sqlite 3.8.3.1. It turns out that if I run this directly with the sqlite 3.8.10.2 that comes with OSX, it works fine.

I upgraded my python sqlite to 3.9.1 and it works now.

Instructions per trac.edgewall.org/wiki/PySqlite

Download pysqlite-2.8.1.tar.gz from
trac.edgewall.org/wiki/PySqlite
Download sqlite-amalgamation-3090100.zip from
sqlite.org/download.html
Unzip both and put them in the same folder
Run this command from the folder:

python setup.py build_static install

Note: I'm using OSX El Capitain, and a separate python installation (not the built in apple one) downloaded from python.org, and my .bash_profile is set up to point to this python installation


InnoDB Buffer pool not showing as much I configured in MariaDB 10.5 I'm using MariaDB 10.5 on Centos7 with 4G RAM and 2 CPU.
Here's my.cnf configuration
[mysqld]
log-error=/var/lib/mysql/mysql-3.kannel.com.err
max_allowed_packet=1G

event_scheduler = ON
innodb_file_per_table=ON
#innodb_file_format=Barracuda
innodb_buffer_pool_size=3G
innodb_buffer_pool_instances=6
innodb_log_file_size=1G
innodb_log_files_in_group=1
innodb_log_buffer_size=32M
innodb_log_write_ahead_size=32M
innodb_flush_log_at_trx_commit=2
innodb_read_io_threads=32
innodb_write_io_threads=128
innodb_io_capacity=10000
innodb_thread_concurrency=6
innodb_flush_method=o_direct

join_buffer_size=128M
sort_buffer_size=20M
read_rnd_buffer_size=128M
datadir=/var/lib/mysql
open_files_limit=10000
default-storage-engine=InnoDB
max_connections = 500

# CACHES AND LIMITS #
tmp-table-size                 = 20M
max-heap-table-size            = 32M
query-cache-type               = 1
thread-cache-size              = 50M
table-definition-cache        = 128M
table-open-cache               = 128M
query_cache_size = 50M
query_cache_limit=128M

But when I see InnoDB engine status using SHOW ENGINE INNODB STATUS\G; it gives me below information where Buffer pool size   193296 but Total large memory allocated 3288334336
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 3288334336
Dictionary memory allocated 19166328
Buffer pool size   193296
Free buffers       179141
Database pages     49186
Old database pages 18176

So if Buffer pool size   193296, where rest of the memory is being allocated?
Thanks!

It is unsafe to raise values arbitrarily.  It often leads to swapping, which makes performance worse.
max_allowed_packet=1G  -- dangerously high; wastes RAM; set to 1% of RAM
innodb_buffer_pool_size=3G   --Since you have only 4GB, lower to 2G
innodb_buffer_pool_instances=6  -- only 1 per GB, so: 2
innodb_log_files_in_group=1   -- Use the standard of 2
innodb_log_write_ahead_size=32M  -- waste of RAM; leave at default (8K)
innodb_write_io_threads=128  -- too high
innodb_io_capacity=10000   -- do you have a super-duper SSD?  Else too high
join_buffer_size=128M    -- lower to 1% of RAM
read_rnd_buffer_size=128M  -- lower to 1% of RAM
max_connections = 500    -- the default will save RAM

As for "Buffer pool size   193296" -- That is pages.  Multiply by 16K to get about 3G.  So it is not "small".


How to handle uniqueness of many large columns in SQL Server? I have the following table:

CREATE TABLE dbo.Document
(
    DocumentId int IDENTITY(1,1) NOT NULL CONSTRAINT PK_DocumentPRIMARY KEY CLUSTERED,
    [Timestamp] datetime2(7) NOT NULL CONSTRAINT DF_Document_Timestamp  DEFAULT (getdate()),
    CreatedBy nvarchar(128) NOT NULL CONSTRAINT DF_Document_CreatedBy  DEFAULT (dbo.getCurrentUser()),
    MonthId int NOT NULL,
    TimeModeId int NOT NULL CONSTRAINT FK_Document_TimeMode REFERENCES usr.TimeMode,
    Key1 bit NOT NULL,
    Key2 int NULL,
    Key3 varchar(max) NULL,   -- sometimes above 8000chars
    Key4 varchar(max) NULL,   -- sometimes above 8000chars
    Key5 varchar(max) NULL,   -- sometimes above 8000chars
    Key6 varchar(max) NULL,   -- sometimes above 8000chars
    Key7 varchar(max) NULL,   -- sometimes above 8000chars
    Key8 int NOT NULL,
    CONSTRAINT FK_Document_BrandType FOREIGN KEY(Key8) REFERENCES dbo.BrandType (Key8),
)

Although I have insisted to find a better natural identifier, I had to deal with the following natural unique tuple:

MonthId, TimeModeId, Key1, ... , Key8

This is way too large for an UNIQUE index (max 900 bytes in SQL Server 2014 or less), so I had to come up with something. My idea is to compute a hash for these columns, so I had a PERSISTED COMPUTED columns as above:

FiltersHash  AS (hashbytes('SHA2_256',(
        (((((((((((((((
            (CONVERT(varchar(10),MonthId)+'|')
            + CONVERT(varchar(4),TimeModeId))
            +'|')+CONVERT(varchar(4),Key1))
            +'|')+isnull(CONVERT(varchar(max),Key2),''))
            +'|')+isnull(CONVERT(varchar(max),Key3),''))
            +'|')+isnull(CONVERT(varchar(max),Key4),''))
            +'|')+isnull(CONVERT(varchar(max),Key5),''))
            +'|')+isnull(CONVERT(varchar(max),Key6),''))
            +'|')+isnull(CONVERT(varchar(max),Key7),''))
            +'|')+isnull(CONVERT(varchar(4),Key8),''))
        ) PERSISTED,
CONSTRAINT UQ_Document_FiltersHash UNIQUE NONCLUSTERED (FiltersHash),

It proved useful because, through a convoluted scenario, the application tried to duplicate a document.

Question: is this solution a good one or are there simpler or more efficient solutions to the large width uniqueness problem?

Note: In my application, I can safely ignore the collisions (if it ever happens, the consequences are rather small). Thanks to Aaron Bertrand for pointing out.

The chance of a hash collision is pretty astronomical (as discussed elsewhere on  Stack Exchange: https://stackoverflow.com/a/4014407).  However, you can reduce it further by adding a second key:

...
FiltersHash  AS HASHBYTES('SHA2_256', /* Various fields */) PERSISTED,
KeyPrefixes  AS CAST(Key1 AS CHAR(1) + '|' + CAST(Key2 AS VARCHAR(10))
    + '|' + LEFT(Key3, 100) + '|' + LEFT(Key4, 100)
    + '|' + LEFT(Key5, 100) + '|' + LEFT(Key6, 100)
    + '|' + LEFT(Key7, 100) + '|' + CAST(Key8 AS VARCHAR(10)) PERSISTED
...
CREATE UNIQUE INDEX UQ_Docs_BizKey ON Documents (FiltersHash, KeyPrefixes)

Now two records must match on three fields and the first part of five more.  If your data commonly includes vertical pipes, consider an alternative separator.  INSERTs into the table will be a little slower, but at your data volumes it's probably not a concern.

As an aside, you're probably better off clustering on MonthID and leaving DocumentID a non-clustered PK, assuming people occasionally run searches by date range ("all documents from June") but rarely search for a range of document IDs.


Is there a data type in MySQL similar to varchar(n) that not allow numbers or special characters in it? Is there a data type in MySQL similar to varchar(n) that not allow numbers or special characters in it?


What Microsoft SQL Server 2008 R2 features do you need to install to get OLEDB Provider Linked Table Connectivity? Which features do you need to install in your SQL Server 2008 R2 instance to get OLEDB Provider Linked Table Connectivity?

Specifically I'm trying to install the VFPOLEDB provider.  (And something tells me I also need a 32-bit instance).

The link below has the download you need:

Microsoft OLE DB Provider for Visual FoxPro 9.0

  The Visual FoxPro OLE DB Provider (VfpOleDB.dll) exposes OLE DB interfaces that you can use to access Visual FoxPro databases and tables from other programming languages and applications.


mongoDB in micro-services structure I'm building a game that have micro-services structure using docker.

The way i started to build my services is having a mongoDB instance to each service, so having OneToOne service to mongoDB instance,

Resources ---> resourcesDB (mongoDB instance 1)
Chat      ---> chatDB      (mongoDB instance 2)
Buildings ---> buildingsDB (mongoDB instance 3)
Battle    ---> battleDB    (mongoDB instance 4)
...

This really made sense at the beginning as those services for example have nothing in common and don't need have a connection to the same mongo instance, and when they do have something in common (building <-> resources) they just send a call to the needed service and that handles the rest (update his DB).

But i starting to think that this is not the right approach because of couple of things:

1 - resourcesDB for example have only 2 collections,

    Logs      ---> logging every resources change (building built, war lost etc.)
    Resources ---> 1 document per user holding how much he have.

meaning that this DB will not hold any huge data (logging will be   deleted after some time), so setting an instance just for those 2 collections is looking like a huge over kill, even if this instance will not have its own server and will share server with the service.

2 - maintaining and doing backups will require some extra work that I'm not sure that its needed in my case.

Each of the service will update his DB quite often, resource is doing calculation every second to see how much each user have, so it will update the db 'users amount' per second, aside with the rest of the requests like build a building (reduce resources) and other updates,
Chat service will update the DB every private message or chat message, Battle service will update troops place and stats and more stuff every second for each battle that is going on.

I'm thinking to chance that structure to one of the next:

maybe have only 1 instance of database with separated DB, so each service will connect to a separated database, but i read that it will still be a hassle to maintain backups of the DB's and scaling.
have 1 database, and have all the collections in it, every collection will be accessed by a different service and will never be accessed by 2, so i have nothing to worry about 2 service changing same collection.
leave it as it is, and go with many instances of mongoDB

few questions about the methods above:

what should i choose? having 1 database sounds good as it will save me a lot of resources and will make my life easier, but separating the services to many database sound logic to me as one service don't need to see collections or be able to access a collection that have nothing to do with his job.
I'm not sure how mongoDB handle many connections to the same instance, or to same database, as you can see it might get a lot of calls in a second even if i have pretty low amount of users.
I read about mongo Sharding, from what i understood it happens per database and not on the instance level, that's why i'm thinking about the 1 database option, as it quite useful in my case, am i right?

It's sensible to think about how to scale your application in future, but you can start with a simpler deployment that can grow with your requirements.

  what should i choose? having 1 database sounds good as it will save me a lot of resources and will make my life easier, but separating the services to many database sound logic to me as one service don't need to see collections or be able to access a collection that have nothing to do with his job.

My recommendation would be to set up a single MongoDB deployment (ideally starting with a replica set or hosted service with high availability & data redundancy):

A single deployment of MongoDB can have multiple databases; a MongoDB deployment per service adds unnecessary admin complexity.
For your use case it sounds reasonable to have a database per service and setup a user per service so actions are limited to direct interaction with the expected database.
A database per service also limits potential impact of administrative actions (eg. a foreground index build) that may create database-level contention.

Later you can scale/tune the deployment starting with options like directoryPerDB (eg. faster storage for some databases) or partitioning your data using sharding.

  I'm not sure how mongoDB handle many connections to the same instance, or to same database, as you can see it might get a lot of calls in a second even if i have pretty low amount of users.

This is a more nuanced question as it depends on server resources as well as what those connections are doing and how/if your driver implements connection pooling. Ideally I would load test your actual application and determine how resource usage scales with increased user activity. If you are administering your own MongoDB deployment you should review the production notes and in particular make sure you have your ulimit settings increased appropriately to ensure you don't run out of file descriptors.

If you're concerned about ease of scaling you could also consider a hosted database-as-a-service solution (for example, MongoDB Atlas).

  I read about mongo Sharding, from what i understood it happens per database and not on the instance level, that's why i'm thinking about the 1 database option, as it quite useful in my case, am i right?

Sharding is initially enabled at the database level but the partitioning happens at the collection level based on a shard key. You can have a mix of sharded/unsharded databases and sharded/unsharded collections in the same MongoDB deployment.


Mysql Innodb: InnoDB: ERROR: the age of the last checkpoint is InnoDB: which exceeds the log group capacity I really need some mysql expertize. I am a newbi to mysql and I am seeing some server crash of my db in the past 1 week.

I am using mysql 5.1.36 on Ubuntu. This is a dedicated mysql server with Dual core and 4GB memory and 40GB SSD.

The log errors are:

120413 23:57:15 [Note] Plugin 'FEDERATED' is disabled.
120413 23:57:15 [Warning] option 'innodb-autoextend-increment': unsigned value 2000 adjusted to 1000
120413 23:57:15  InnoDB: Initializing buffer pool, size = 2.9G
120413 23:57:15  InnoDB: Completed initialization of buffer pool
120413 23:57:16  InnoDB: Started; log sequence number 0 44234
120413 23:57:16 [Note] Event Scheduler: Loaded 0 events
120413 23:57:16 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.58-1ubuntu1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
120414  0:00:25 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=e2-relay-bin' to avoid this problem.
120414  0:00:25 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='', master_log_pos='4'. New state master_host='', master_port='3306', master_log_file='mysql-bin.000043', master_log_pos='87039427'.
120414  0:58:37 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='mysql-bin.000043', master_log_pos='87039427'. New state master_host='', master_port='3306', master_log_file='mysql-bin.000043', master_log_pos='87846901'.
120414  2:20:34  InnoDB: ERROR: the age of the last checkpoint is 241588252,
InnoDB: which exceeds the log group capacity 241588224.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.

My.cnf is as below.

default-storage-engine=innodb
default-table-type=innodb
key_buffer              = 384M
max_allowed_packet      = 64M
thread_stack            = 256K
thread_cache_size       = 16
max_heap_table_size     = 64M
myisam_sort_buffer_size = 64M
join_buffer_size        = 8M
read_buffer_size        = 2M
read_rnd_buffer_size    = 8M
sort_buffer_size        = 3M
tmp_table_size          = 64M
# Innodb changes
innodb_additional_mem_pool_size = 16M
innodb_autoextend_increment     = 2000
innodb_buffer_pool_size         = 3000M #As current Db is around 1.2G.
innodb_file_per_table
innodb_data_file_path           = ibdata1:512M;ibdata2:512M:autoextend
innodb_flush_log_at_trx_commit  = 2 #For more reliablity use 1
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 8M
innodb_log_file_size            = 128M #Transaction Log up to 1/4 Buffer Pool
innodb_thread_concurrency       = 16
#innodb_force_recovery          = 2
#innodb_read_io_threads         = 8
#innodb_write_io_threads                = 8
innodb_lock_wait_timeout        = 50
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 400
table_cache            = 1024M
thread_concurrency     = 16

Any suggestions what I can change.

At first glance, I would say that your innodb_log_file_size is way too small. It should be bigger to do two things:

Accommodate any big BLOB or TEXT fields
Holding bigger transactions

Here is what you should do for now to see if it helps:
STEP 01) Change the following in /etc/my.cnf
[mysqld]
innodb_log_buffer_size          = 32M
innodb_buffer_pool_size         = 3G
innodb_log_file_size            = 768M

STEP 02) service mysql stop
STEP 03) rm -f /var/lib/mysql/ib_logfile*
STEP 04) service mysql start
This will rebuild the following files

/var/lib/mysql/ib_logfile0
/var/lib/mysql/ib_logfile1

Give it a Try !!!
UPDATE 2013-07-03 12:37 EDT
I have updated my other posts on this and missed this one
ButtleButkus just commented at 2013-07-03 07:18:56 EDT

Wouldn't it be advisable to copy the ib_logfile* to another location for backup before deleting them?

Since there could be unfinished transactional data inside, here is what should be done
STEP 01) Change the following in /etc/my.cnf
[mysqld]
innodb_log_buffer_size          = 32M
innodb_buffer_pool_size         = 3G
innodb_log_file_size            = 768M

STEP 02) mysql -uroot -p -e"SET GLOBAL innodb_fast_shutdown = 0;"
STEP 03) service mysql stop
STEP 04) rm -f /var/lib/mysql/ib_logfile*
STEP 05) service mysql start
I added SET GLOBAL innodb_fast_shutdown = 0;. What does that do? It forces InnoDB to completely purge transactional changes from all of InnoDB moving parts, including the transactional logs (ib_logfile0, ib_logfile1). Thus, there is no need to backup the old ib_logfile0, ib_logfile1. If deleting them makes you nervous, then make Step 04
mv /var/lib/mysql/ib_logfile* ..

to put the old logs in /var/lib. If the recreation of the logs is successful and mysqld starts up, then you can delete the old logs.
I have been using this feature for a year now. I have updated my other posts to reflect this...

May 20, 2013 : Innodb one file per tablespace
May 05, 2013 : Issue after moving the ib_logfile1 and ib_logfile0 files
Jan 10, 2013 : Finding and fixing InnoDB index corruption
Dec 17, 2012 : MySQL Start/Stop
Feb 16, 2011 : How to safely change MySQL innodb variable 'innodb_log_file_size'? (Last Update)
Feb 04, 2011 : MySQL InnoDB - innodb_file_per_table cons? (Last Update)
Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine? (Added as Step 03 on Jun 04, 2013)

If there are other older posts of mine where I do not mention innodb_fast_shutdown, let me know so I can update it. Thanks again, ButtleButkus.


Workaround to importing data I am trying to import data into a SQL Server. I can import through the Import and Export Data wizard. I cannot import from my machine using BULK IMPORT or OPENROWSET since the file is not on the server machine. How does the IED wizard insert data into its target db? Does it go row by row?

Are there any other possible solutions to importing data from my machine?

SQL Server cannot import what it cannot see. The reason that you can import it via SSMS is because it opens a Windows File Dialog box, which uses your permissions and not SQL Servers to find the file. If you must import the file then the only way you can do this is to make the file accessible to SQL Server.

How you decide to do this is up to you, whether it be through File Shares, the NET USE command or simply by copying the file over to the server that SQL sits on. You may not be able to do any of these, but somebody in your organization can - if this is a regular thing you should also talk to them about arranging either a SQL Job or a scheduled task whichever is easier to do.


How to move a fairly large tsql table from one server to another server? I have been working on thinking of ways to move a 40gb table from one SQL Server box to another SQL Server box.  They are at separate companies without an intranet linkage and obviously without linked servers.

Initially, I thought the quickest way to do this was to pull the nightly backup file and get this over to the other server.  Create a database from the backup and then transfer my table from that database to the existing.

After thinking about this though, I can't have all the data in that particular database at the other company due to security reasons.  Whereas the table I am after is computational and unrelated to any type of company specific competitive edge.

Either way, what I am looking for is how I can export a table of that size quickly and efficiently and get it into a database that is in no way connected to the existing database.

This is my first time researching something along this line, and I am pretty sure I don't want to script the table.  Initially, I thought the bak file was my best bet, but can't do the entirety of the db.

A couple of feasible options, in order of my preference:
Option 1

Create a new, empty database locally - maybe make the log file large enough, at least temporarily, to accommodate the entire set of data you are moving without growth
Use SELECT INTO, the Import/Export "Wizard", or Red Gate SQL Data Compare to copy this table to the new database (note that if you use SELECT INTO you'll have to create any indexes, constraints etc. manually)
Back up the new database, and send that .bak over (note that the log may be bigger than you expect due to the initial data movement)

Option 2

Back up your database locally
Restore as a copy
Drop everything but this one table
Back up the copy, and send that .bak over


How to Represent Equation with Variables in Relational Database I am building an application to store the different formulas used for different subjects in school, and I have drawn the following database diagram to depict that scenario:

I am having trouble figuring out how to represent an equation in a relational database.

Each equation is made up of variables of different types. For example,

E=mc^2

Has the following components:

E, which is energy in joules(J), which is actually kg*m^2*s^2.
m, which is mass in kilograms(kg), which cannot be broken down further
c, which is the speed of light, with the value of 3*10^8, and units of m/s

The problem is not the representation of the string of the equation within the database. Rather, I want to represent the relationship between the individual variables within an equation.

How should I go about representing these different types of variables within the same database?

Here's one suggestion. Since you used the term component, I'll stick with that, but maybe theres a better term?

CREATE TABLE COMPONENTS
( COMPONENT_ID INT NOT NULL PRIMARY KEY
, ABBREVIATION CHAR(1) NOT NULL -- Example 'E'
, DESCRIPTION VARCHAR(50) NOT NULL -- Example 'Energy'
);

CREATE TABLE EQUATION_COMPONENT
( EQUATION_ID ...
, COMPONENT_ID ...
,    PRIMARY KEY ( EQUATION_ID, COMPONENT_ID )
,    FOREIGN KEY ( EQUATION_ID ) REFERENCES EQUATIONS ( EQUATION_ID )
,    FOREIGN KEY ( COMPONENT_ID ) REFERENCESCOMPONENTS ( COMPONENT_ID )
);

If you, for example, want to find all equations concerning both Mass and Energy:

SELECT EQUATION_ID
FROM EQUATION_COMPONENT
WHERE COMPONENT_ID IN ( id:s for Mass and Energy )
GROUP BY EQUATION_ID
HAVING COUNT(COMPONENT_ID) = 2


What do these RMAN listings mean? I ran a LIST BACKUP SUMMARY on my system, the output is pasted below.

Did my backups fail?

I know TY is Type (B for Backup,right?), but what do LV and S mean?

RMAN> LIST BACKUP SUMMARY;

Lista di backup
===============
Key     TY LV S Device Type Ora di completamento #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
1       B  A  A DISK        04-DIC-16            1       1       NO         BACKUP-LIBRI
2       B  F  A DISK        04-DIC-16            1       1       NO         TAG20161204T
3       B  F  A DISK        04-DIC-16            1       1       NO         TAG20161204T
4       B  A  A DISK        04-DIC-16            1       1       NO         BACKUP-LIBRI

Re
Column  Indicates
Key
A unique key identifying this backup set.
If you are connected to a recovery catalog, then BS Key is the primary key of the backup set in the catalog. It corresponds to BS_KEY in the RC_BACKUP_SET view. If you are connected in NOCATALOG mode, then BS Key displays the RECID from V$BACKUP_SET.
TY
The type of backup: backup set (B) or proxy copy (P).
LV
The level of the backup: NULL for nonincrementals, level 0 or 1 for incrementals.
S
The status of the backup: A (available), U (unavailable), or X (all backup pieces in set expired). Refer to the CHANGE, CROSSCHECK, and DELETE commands for an explanation of each status.
Device Type
The type of device on which the backup was made, for example, DISK or sbt.
Completion Time
The date and time that the backup set completed. Note that the format of this field depends on the NLS_LANG and NLS_DATE_FORMAT environment settings.
Pieces
The number of backup pieces in the backup set.
Copies
The number of copies made of each backup piece in the set. The number is 1 if no duplexing was performed. Otherwise, the value ranges from 2 to 4.
Tag
The tag applied to the backup set; NULL if none. An asterisk (*) indicates multiple copies with different tags. Note that tag names are not case sensitive and display in all uppercase.
Reference --> https://docs.oracle.com/cd/B13789_01/server.101/b10770/rcmsynta36.htm#83097
Thanks


Creating a table space sql developer I asked this question over at https://stackoverflow.com/questions/52068320/creating-a-table-space-sql-developer, but there was no traction on the question, i thought i might try my luck over here.

I'm creating a tablespace to allocate some tables I've made. I'm new to SQL and i'm not exactly sure what my next step is to create the tablespace.

I understood and found that in order for me create a non-clustered table space i need to:

Calculate the total block header size.
Calculate the available data space per data block.
Calculate the space used per row.
Calculate the total number of rows that will fit in a data block.

I've calculated these figures for each table i have, but i'm clueless on the next step after this.

How do I use my calculated values to create a tablespace?

Since you quoted SQL Developer in your question -

View > DBA

Add your connection.

Go to Storage, Tablespaces.

Right-click, 'Create New'

Fill in the details - and I agree with Gerard Pile's comment to your question - you don't need to worry about what you're worrying about. Just pick a directory, file name, and intial file size, and how you want it to grow, with an optional max file size.

You could theoretically override the database's DB_BLOCK_SIZE on the 'Properties' page, but that's generally not necessary.

If you want to see the SQL that's generated by the dialog, click on the 'DDL' page of the popup, otherwise, just click 'Ok.'