texts:
NOTE The audience of programmers.se and dba.se is different, and will have different viewpoints, so in this instance I think it's valid to duplicate What are the arguments against or for putting application logic in the database layer? on programmers.se.

I couldn't find discussion on dba on this already, and the original post says it all, so:

  Most software developers want to keep
  application logic in the application
  layer, and it probably feels natural
  for us to keep it here. Database
  developers seem to want to put
  application logic in the database
  layer, as triggers and stored
  procedures.

  Personally I would prefer to keep as
  much as possible in the application
  layer to make it easier to debug and
  keep the responsibilities of the
  layers separate.

  What are your thoughts on this, and
  what should or should not be ok to
  implement in the database layer?

N.B. I'm not the OP for that question, but left the original wording intact.


texts: Does Oracle randomly switch execution plans to check if there is a better solution?

texts:
I had a talk with an external colleague about oracle "swinging in" its performance. In detail it was about, that oracle optimizes its execution plans for the same query by time.

I don't talk about filling the caches. What I mean is that oracle tries different execution plans and keeps the one with best performance. This means a query send the 100th time is probably less performant than a query sent the 10000th time.

The Query has to be 100% the same, so index is defined and no parameters change or bind variables are used. Oracle only optimizes by sometimes trying a different execution plan and comparing its performance to the previously executed plan.

I would really appreciate if some of the Oracle pros could make this clear for me because I doubt this statement. Does Oracle really randomly switch execution plans to check if there is a better solution?


texts: Is it a good practice to store auth session in the database?

texts:
I created a login system that in addition to being used on a website, will also be used in mobile applications.
As on cell phones I want to keep the user logged in until he chooses to log out, I did not use the authentication for sessions in PHP.
So I thought it would be better to store the login sessions in the database, for each user request, to verify if the authentication token is still valid.
But I don't know if this is a good practice. Since every time the user updates the screen in the browser, or sends any application request to the system, he will make a query to verify that the login is still active and then make another query to search for what the user requested.
My concern is whether this will become too slow, for a system that could have between 900 million and 1,5 billion users, since the database will have many more requests and verification queries in addition to the normal query requested by the user.
Below is the current structure of my database. I would also like tips if my structure is very wrong.


texts: Setting up Availability group for report server databases

texts:
I am reading msdn article on using SSRS with AG and have few doubts on setting up the same:

Server A -  Will host Report Server services with scale out deployment
Server N1 - Node 1 with ReportServer and ReportServerTempdb database serving as primary replica
Server N2 - Node 2 is secondary replica of the ReportServer and ReportServerTempdb

Above nodes will be part of multi-subnet cluster with N1 in DC1 and N2 in DC2.

I am thinking to create the listener first with 2 IPs for those multi-subnet so that we can use same while installing reporting services and point the listener name in instance and it's report server database to it so that it is aware of primary replica even after fail-over.

Is my above understanding correct? Is that how it's suppose to work?

Secondly, document says automatic fail-over is not supported even when I use sync mode between N1 and N2? Is that true? And why would that be the case only with SSRS? Also if it is, can we automate the fail-over or does it have to be manual every-time since I am thinking data source application string can use the option MultiSubnetFailover=true ?

Appreciate your inputs.


texts: Name of attributes that are indirectly related to entity

texts:
If I have a set of data that relate to an entity directly and some that do not, is there a standard name for these two types of attributes?

example, Employee:

employeeID firstname lastname manager dept
123        boris     mctrump  gareth  sales

I would suggest that 'firstname' and 'lastname' directly relate to the employee but 'manager' and 'dept' are somehow indirect


texts: How to debug database server freeze (not accepting connections, timeouts etc)

texts:
Apologies if this question is a little bit vague but we are having issues for a couple of days now and have no clue on what may be wrong.

We have a pair of Windows servers with SQL Server installed in high availability (Always On in a master / replica setup). Exact spec is Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30  Enterprise Edition (64-bit) on Windows Server 2016 Standard 6.3  (Build 14393: )

SQL Server has around 7-8 databases to support different services of a single quite sizeable app. The sizes of each database varies but it ranges from a few GB to over 1TB. Some apps

Suddenly (check note 1), 2 days ago our master db server instance unexpectedly stopped working, requests started timing out etc. When I say it hangs, I mean that nothing goes through on any database. The whole DB server just stops working. The OS and server is fine though. Its just the SQL Server instance.

When this happens we need to manually fail-over to the replica, restart the SQL Server processes on the old master and re-attach it to the cluster.

This has started happening 2 days ago and it occurs every 8-10 hours or so. I thought there was a regular frequency but there isn't unfortunately. Through this time we tried pausing all maintenance jobs on SQL Server (index rebuilds, log cleanup, backups etc). The problem still occurs.

I know that you dont have a magic ball to guess whats going on. So this question is how would you go about finding the problem here? What commands or steps would you run to get more info? Obviously we can't reproduce the issue - it just occurs.

Btw, I am not a DBA but I dont quite get why SQL Server doesnt automatically failover since nothing works on master...The underlying process is still running but nothing works...

Note 1: No app releases have happened to justify that. No releases at all really. No major change in db sizes or anything along these lines really.


texts: ALTER TABLE DROP COLUMN - server status 'Stopped'

texts:
I am in the process of removing 4 columns from my (MySQL/innodb) table,

simple code,

ALTER TABLE database_name.table_name
DROP COLUMN A,
DROP COLUMN B,
DROP COLUMN C,
DROP COLUMN D;

It has been running for close to 20 hours now. It's a large table 30GB / 8560000 rows, large for what i have experience with anyway, local instance on an old desktop computer.

When I check the Server Status on the Administration panel it is telling me that the Server has 'Stopped'. However in the Action/Respose window at the bottom of the Query window that row is showing 'Running..' and the icon is that spinning/thinking animation you expect.

There is around 4 KB/s Traffic but no read or writes, InnoBuffer usage is at 99.9% which should be around 30GB, CPU is between 1.5 and 2.

I can open another instance of the server and that shows it is running but still has the old columns listed in the Schema. (I have have refrained from querying the db through that instance)

I am just wondering what is happening exactly?
Is this 'normal' and I should just be patient?


texts: MySQL subquery slows down drastically, but they work fine independently

texts:
Query 1:

select distinct email from mybigtable where account_id=345

takes 0.1s

Query 2:

Select count(*) as total from mybigtable where account_id=123 and email IN (<include all from above result>)

takes 0.2s

Query 3:

Select count(*) as total from mybigtable where account_id=123 and email IN (select distinct email from mybigtable where account_id=345)

takes 22 minutes and 90% its in the "preparing" state. Why does this take so much time.

Table is innodb with 3.2mil rows on MySQL 5.0


texts:
We have an application that makes of of a SQL Server 2012 (ServerA) database. Some data that we need to display, though, comes from another database, but it's SQL Server 2008 (ServerSOURCE). The database is also on a separate server.

The plan is to create a replication database on the same server as ServerA, called ServerREP, and replicate data from ServerSOURCE into ServerREP. But we only want certain tables from ServerSOURCE. It's a small subset of the tables. ServerSOURCE has hundreds of tables. We only need around 10.

Additionally, the data in the tables could be reduced. For example, say we have a Person table, which has a PersonTypeId. We only want rows where PersonTypeId = x.

Can this be achieved with replication? Can we get subsets of data from a subset of tables from the source, into our new replicated database? Would it be something like creating a VIEW on the source, and replicating the results of that view, as a table in the replicated database?


texts: Run lo_export as client through psycopg2

texts:
Python 2.7
psycopg2

When I run:
cur.execute("SELECT lo_export(123456, '/tmp/test.jpg')")

Produces error - need to be superuser to do this.

This makes sense as it is running a server side lo_export and the permissions are not set to superuser for the user I want to run the request.

I want to run a client side lo_export as if I was running psql.

Can I do this within an open psycopg2 connection?


texts: Error 1236 'Found old binary log without GTIDs' after master restart

texts:
So after quite a few attempts to build a slave and getting the error above, I decided to start with no binlogs from scratch so on the latest slave rebuild I issued PURGE ALL BINLOGS BEFORE NOW() on the master to get rid of all the possible binlogs without GTIDs, then made a full dump with innobackupex and moved it to the slave.
Started the slave up and it worked fine...
Up to the point where I always get this exact error which is when I restart the master....
so after a mysql restart on the master is executed the slave lost connection of course, after reconnecting I got greeted with the usual:

"Got fatal error 1236 from master when reading data from binary log: 'Found old binary log without GTIDs while looking for the oldest binary log that contains any GTID that is not in the given gtid set'"

This is getting ridiculous now, I have rebuilt the slave 4 times now in 3 days.

Here's my my.cnf:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
nice            = 0
open_files_limit        = 65535

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /srv/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp

bind-address    = xxxxxxxxx

key_buffer              = 64M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 16
myisam-recover         = BACKUP
table_open_cache                = 512
open_files_limit        = 65535
interactive_timeout=180
wait_timeout=180

query_cache_limit       = 1M
query_cache_size        = 256M

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 10
max_binlog_size         = 100M
expire_logs_days        = 10

log-error       = /var/log/mysql/error.log
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

symbolic-links=0

innodb_file_per_table
innodb_additional_mem_pool_size = 20M
innodb_buffer_pool_size         = 24G
innodb_file_format              = "Barracuda"

binlog-format=MIXED
log-slave-updates=true
log-bin
gtid-mode=on
server-id=1
enforce-gtid-consistency=true

[mysqldump]
quick
quote-names
max_allowed_packet      = 32M

[mysql]

[isamchk]
key_buffer              = 16M

At the time of master restart, the slave was about 4000 seconds behind already (that's what I was trying to solve somehow, but I got this error - again -)

I case you are wondering last time I rebuilt the whole thing was last night, so it is not a case of missing binlogs due to expiry.

Does anyone have any idea?

Thanks


texts: How to enable SQL Server Log Truncation Counter

texts:
I have a database in Full Recovery mode. Full backup is executed every day, transaction log backup every hour.

However,

SELECT* FROM sys.dm_os_performance_counters where counter_name = 'Log Truncations'

shows 0 log truncations for that DB.

At the same time,

DBCC SQLPERF(LOGSPACE);

shows different "Log Space Used" after each execution (it slowly grows during interval between log backups, and then drops; Log Size doesn't change), so it seems to me that log truncation works fine; otherwise, I'd run out of space long time ago.

I suspect counter is not updated for any reason, but I can't figure out why. Also, I may be well wrong, and the problem is more serious than it looks .

Any help is greatly appreciated.


texts: SQL Server table with 4,000,000 rows is 40GB

texts:
I have been trying to figure out why our production database has grown so much recently and have found the cause to be an audit table. We are going to archive the table to fix the problem but I just wanted to check if the size of the table looked normal for a large SQL server table like this.
It has 4,000,000 rows with the following structure...

Name                    Type        Nullable
AuditLogId              bigint      no
UserName                nvarchar    no
TimeOfChange            datetime    no
ObjectName              nvarchar    no
ChangeName              nvarchar    no
RecordId                int         yes
OriginalValues          nvarchar    yes
ResultingValues         nvarchar    no

With a primary key on the AuditLogId

ALTER TABLE [dbo].[AuditLog] ADD  CONSTRAINT [PK_AuditLog] PRIMARY KEY CLUSTERED
(
    [AuditLogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

There is also a foreign key to the record that was updated on RecordId.
The nvarchar fields seem to have up to 12,0000 characters with an average of around 8000 characters.
The table is currently 40GB in size, does that seem right? I can provide more information on the table if that question can’t be answered with what I have posted above.

Thanks


texts:
Backgroup on the autonomous database
For the last couple of years Oracle have offered a "autonomous" or "self driving" database in their cloud which is only available on the Oracle cloud.  If you cannot guess from the name its a PaaS/SaaS type of operation where a lot of routine tasks - from performance tuning to making database backups are automated.  It generally does a decent job too.  Its Oracle flagship RDMS database and they are actively trying to push all their customers onto it.

My question is what is the best approach to setting up an autonomous database that can be used in two regions.  I would like to have databases in regions local to customers on different continents.  Its not urgent right now but I think will become necessary and I should getting an understand of this.

Is it possible/practical to set up two databases and somehow keep them in sync?  Are there any best practices around this that I should be aware of?  Any options that don't involve paying oracle would be ideal!


texts: One SQL Server. One SSIS. Two versions of DTExec.exe?

texts:
Is it possible to install and use the 2012 version of the DTExec.exe on a Server 2008R2/SSIS2008 box without installing SSIS 2012?  All jobs will be called from the command line, no server agent jobs. I see that the 2012 SSDT BI Tools download contains the DTexec.exe tools and it's required binaries. Would that work?

An example...

The path to the existing 2008 DTExec tool on a 2008R2 SQL Server is..

\\SQLSERVERNAME2008\Program Files (x86)\Microsoft SQL Server\105\DTS\Binn\DTExec.exe

Could we drop the 2012 DTExec and binaries from SSDT BI Tools onto that server into this path?

\\SQLSERVERNAME2008\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DTExec.exe

Will this work?  Do we have to install SSIS 2012? Why?  Why Not?  Any help you can provide will be useful.  Thanks all.


texts: How to Choose Between VALUES and SELECT for INSERT?

texts:
This answer raised the question for me how to choose between VALUES and SELECT in such a function . Using PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit:

CREATE OR REPLACE FUNCTION insaft_function()
   RETURNS TRIGGER AS
$func$
BEGIN
   INSERT INTO file_headers (measurement_id, file_header_index_start
                                           , file_header_index_end)
   VALUES (NEW.measurement_id, TG_ARGV[0]::int, TG_ARGV[1]::int);

   RETURN NULL;  -- result ignored since this is an AFTER trigger
END
$func$ LANGUAGE plpgsql;

VALUES work with many rows but with SELECT you can do much more.
The only requirement here is to do the above INSERT to the table.
You can assume that there are 100k of such INSERTs done per cycle in continuous quality assurance of a system.

I noticed these differences with my data where selected three median values here:

VALUES
real      user      sys
-------------------------------
0m0.353s  0m0.256s  0m0.028s
0m0.327s  0m0.252s  0m0.036s
0m0.358s  0m0.252s  0m0.040s
so average real 0.34s

SELECT
real      user      sys
-------------------------------
0m0.362s  0m0.256s  0m0.024s
0m0.383s  0m0.236s  0m0.056s
0m0.356s  0m0.264s  0m0.032s
so average real 0.36s

So this small subset of data says that VALUES is faster with such a simple INSERT. I am interested in requirements for concurrent processes and real time data analysis.

How can you decide between SELECT and VALUES for INSERT?


texts: Can we restrict the number of connections to a database in SQL Server?

texts: I have a database on SQL Server. My web application contacts this database. Is there a way in which I can restrict the number of connections to this database through some settings on the SQL Server?


texts: How does the Microsoft tablediff utility work?

texts:
Our team is curious about the architecture of tablediff utility. We want to find the difference between table rows.

Microsoft mentions tablediff here, however does not state how it works. Does tablediff take the checksum or hash of every row and compare tables? What is the internal algorithm method?

https://docs.microsoft.com/en-us/sql/tools/tablediff-utility?view=sql-server-2017

create table dbo.CustomerTransaction
(
    CustomerTransactionId int primary key,
    CustomerName varchar(50),
    ProductName varchar(50),
    QuantityBought int
)

Using tablediff example:

So row values Table 1: (1,'Bob','Table',8) is the Same as Table 2: (1,'Bob','Table',8)

These are different (1,'Bob','Table',8), (1,'Bob','Chair',8), different on the primary key.

I know it requires the source table to have primary Key/Identity/rowguid column  to compare, and was originally based on replication technology.


texts: Can a database file be brought back online if the file is recovered from a disk backup?

texts:
TL:DR

Deleted .ndf files
Database wouldn't load
Set deleted files offline
Brought database online
Restored files from disk backup
How can I bring them online/remove the database reference?

During a (manual) disk cleanup process while the specific database was offline a developer in our shop deleted some .ndf files from the data directory because they were believed to be no longer in use. Said database was then unable to start due to the missing files. These files were not on the primary filegroup and were part of a partition scheme based on monthly data. There are no applications (outside SQL Server) which need to access the deleted files and the bulk of them were empty.

The files were set to offline to enable the database to be brought back online. The files have since been recovered from a disk backup - not a SQL Server backup. The files themselves have not been modified in 3+ months and I would like to remove them as a matter of cleanup. The partition scheme they were part of has been removed.

A full restore is not feasible as it is a 1TB database and disk space will not allow it. Is there SQL script I can run in SSMS to either bring the files back online to remove them properly or remove the references outright? I tried to do an alter of the database/file with recovery but SQL let me know they must be offline (sys.master_files tells me they are).


texts: Half of the partitions are empty on a KEY partition

texts:
We are trying to move data from one table that currently has over 1B rows to a partitioned table.
But as we started to do so we notice that half of the partitions are empty, why is that?
Table is partitioned by the column that has UUIDv4 values.
Our table definition:
CREATE TABLE table1 (
  `col1` varchar(36) NOT NULL DEFAULT '',
  `col2` varchar(256) NOT NULL DEFAULT '',
  `col3` datetime NOT NULL DEFAULT NOW(),
  UNIQUE KEY `unq_col1_col2 (`col1`,`col2`),
  KEY `idx_col3` (`col3`)
)
ENGINE=InnoDB
PARTITION BY KEY(col1)
PARTITIONS 100;

INFORMATION_SCHEMA.PARTITIONS output:
PARTITION_NAME  TABLE_ROWS
p0  136532
p1  0
p2  339523
p3  0
p4  156955
p5  0
p6  305700
p7  0
p8  209548
p9  0
p10 329291
p11 0
p12 152995
p13 0
p14 388600
p15 0
p16 251903
p17 0
p18 364532
p19 0
p20 200799
p21 0
......
p89 0
p90 303628
p91 0
p92 215546
p93 0
p94 399165
p95 0
p96 210364
p97 0
p98 318675
p99 0

We are using Google Cloud SQL (MySQL) which is currently on version 5.7.32-google-log.


texts: Optimize a WHERE IN condition using an index

texts:
I have a table that looks like the following one:

CREATE TABLE foo (
    type       TINYINT,    -- low cardinality (2..5)
    id         BINARY(16), -- high cardinality
    other_data OTHERTYPE(n),
    CONSTRAINT foo_PK PRIMARY KEY (type, id)
);

One application accessing the database that contains that table runs point queries by type and id, and queries that filter by multiple (type, id) pairs.

The question is: What index would perform better in these cases? Currently, I group type column and do UNION on SELECT * FROM table WHERE type = ? AND id IN (?)

The rule of thumb to use the highest cardinality column first would still work best, or should I define a (type, id) index?

Note: The database is running on top of MySQL, but answers considering other RDBMSs would be interesting too.


texts: Should I split a single table with an AUTO_INCREMENT PRIMARY KEY into two tables that can be joined on that PRIMARY KEY?

texts:
I have a table that has 46 fields with an AUTO_INCREMENT PRIMARY KEY.  It's hard to look at a table that has this many columns, so I frequently use a SELECT statement to look at 34 of the fields that I mentally categorize as 'group 1' and 12 fields that I categorize as 'group 2'.

Would I face a performance hit on reads/writes if I split this table into two tables, one with 34 fields and another with 12 fields.

Both would have the same AUTO_INCREMENT PRIMARY KEY so that they could be joined on the PRIMARY key if I need to look at at 46 fields at once.


texts: How do I determine the spid of the session that is making a query on a linked server?

texts:
I have a session running on a SQL Server instance that is blocking other sessions (holding on to a lock without releasing it).

Querying sys.sysprocesses yielded the hostname of a different SQL Server instance.

I want to know that spid of the originating session that is ultimately responsible for blocking the sessions on the linked server.

How would I do that?


texts: INSERT INTO with RETURNING clause: return undefined columns?

texts:
Breaking it down to it's most simplest form... Is it possible to use INSERT INTO with a RETURNING clause to capture a non-existing column alongside the output?

test=# \d x
       Table "public.x"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer |
 b      | integer |

--void column does not exist in table...
INSERT INTO x (a, void) values (1,2),(3,4) RETURNING a, void;

Under this scheme would return:

 a | void
---+---
 1 | 2
 3 | 4

To give this problem some context...

I have a client ID that I'd like to append to the results so that it's server (real) ID will be returned alongside as a pair.

I don't actually need the client ID persisted to the DB.

I could use the AS keyword in the output, but there is no way to diversify the client ID with this method:

test=# INSERT INTO x (a, b) VALUES (1,2),(1,4) RETURNING a, b, 6 as clientid;
 a | b | clientid
---+---+----------
 1 | 2 |        6
 1 | 4 |        6
(2 rows)

INSERT 0 2

I tried playing with some CTE's, can't seem to get it. Basically, I want to be able to provide a ROW or, array of IDs that I want JOINed to the result.

In theory, I could do this at the server level instead of DB level. Just curious.


texts: MySQL Access Denied, tried a few things, pulling hair

texts:
I'm trying to get to know Django (my first attempts at a framework, or any backend work for that matter), and I'm seriously stumped by MySQL, and SQL in general.

I'm trying to create a new database and I get:

ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'dbname'

So I've tried the advice here: Access denied for user localhost

Which might work, but using: mysql -uroot -p  I can't seem to remember my password. I don't recall setting one, but leaving it blank doesn't work either.

Also, I have to run mysql using:

/Applications/MAMP/Library/bin/mysql

Because:

mysql

results in:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

I'm guessing that's related to the error above, right?

I'm in a right mess here, SQL and the command line intimidate the heck out of me and it seems so easy to break stuff. If anyone could offer some pointers that would be great.


texts: How do you protect against malicious PREPROCESSOR attacks in Oracle External Tables?

texts:
I'm a new DBA and I recently found out about the option of External Tables in Oracle using the PREPROCESSOR feature ( http://download.oracle.com/otndocs/products/database/enterprise_edition/utilities/pdf/xtables_preproc11g_1009.pdf )
Unfortunately, this feature, which seems quite useful in our lounge seems very dangerous, as someone with access to the OS (or remotely....) could exploit it to cause the database to get compromised, or even worst - the whole OS.

I have restricted the access to this feature to the minimum, and revoked any additional privileges which might allow outside access to the os (extproc, java, etc)

However, there are still times when we must use this feature, and this is where I ask you guys 2 main questions:

How do you protect against malicious attacks using this wicked feature ?
Assuming something has failed in the security mechanisms, what sort of ways are there to detect that someone used this feature in an evil way? What sort of queries (or content of them) could be seen ?

Thanks (:


texts:
In my rails application using the Postgres database, I have a table called tw_schedules which belongs to a scenarios table (one scenario to many tw_schedules). In addition to the scenario_id foreign key (primary key "id" column from scenarios table), it contains two more foreign keys: type_well_id (primary key "id" column from type_wells table) and tw_import_id (primary key "id" column from tw_imports table).

Each tw_schedules belonging to a particular scenario can either have a type_well_id or tw_import_id, but not both.  This is an application enforced constraint with the result that type_well_id or tw_import_id can have a null value.

My question is: in order to optimize the query retrieval time, should I create two compound indexes on:

scenario_id + type_well_id

and

scenario_id + tw_import_id

or three individual indexes on columns:

scenario_id
type_well_id
tw_import_id

The reason I ask is that both type_well_id and tw_import_id columns can contain null values.


texts: How can I detect broken stored procedures after a schema change?

texts:
I've modified a central table in my database, and sp_depends literally returns hundreds of results, and I'm concerned some of those stored-procedures might not compile anymore after my change.

Checking one single stored procedure is easy (I just rerun the alter script and see whether the operation is successful), but doing that on 100+ procedures is a bit cumbersome.

I know I can use a script like this one to recompile all the objects of my database, but the actual operation will take place next time the stored procedure is executed, not immediately, so that doesn't seem appropriate in my case.

I was also thinking that I could drop all the stored procedures altogether, and resycnhronize my database with my source control system, but that option, although viable, isn't very elegant. Is there a better way of doing this?

I'm using SQLServer 2008 R2 and my database scripts are stored in a VS 2008 database project.

To clarify, I'm not advocating one should solely rely on this approach to test code.
Just exactly like in c# you instantaneously detect syntax error in other dependent files as you code (and then use other strategies to test such as unit tests, which is usually several orders of magnitude slower), I think it would make sense to detect SQL dependencies errors in seconds rather than having to run a full functional test which can typically take a few hours to complete.


texts: Insert range of value based on SQL where clause count

texts:
SQL Server 2012, say I have this:

select '12358', resource_code, 'N', '1' from sch_resource
where resource_desc like '%avid%media%comp%dongle'

instead of inserting '1', I want to instead insert 1 to X, where X is:

select count(resource_code) from sch_resource
where resource_desc like '%avid%media%comp%dongle'

Is there an easy way to do this without a loop?

Result, count equal to 7, would look like this:


texts: Slow Performance of Parametrized Query in Excel due to Parameter Datatype

texts:
I'm pulling Data from a SQL Server 2008 into Excel using a Parametrized ODBC Query created by the Excel Wizard. (Data-->getExternalData/FromOtherSources-->FromDataConnectionWizard-->ODBC DSN)

My query faces a huge Performance Problem as soon as I'm using a parameter in it. Assuming that the Parameter1 points at a Cell which hold E20140718 the following Query times out:

SELECT Top 20 *
FROM Viewname
Where RunId=?
Order by RelativeRWAVariation desc

However the following query has a runtime of only 2 seconds:

SELECT Top 20 *
FROM Viewname
Where RunId='E20140718'
Order by RelativeRWAVariation desc

The Parametrization in the first query should work correct as this style of queries works very good for selecting from other, a bit less complicated Views.

I'm pretty sure, that the problem I'm facing is described in this thread. This means that if i could somehow tell the SQL Server that the Parameter he's getting is a varchar(9) the parametrized query would probably only take 2 seconds aswell. Is there any way in Excel to give the Parameter a datatype or another solution to the problem?

Convert(Varchar(9),?) doesn't work, since the Parameter can't be used as part of a forumla according to Excel :(

Edit:
I just tried the same thing in SQL Server Management System and surprisingly (to me) faced the same Problem

The following statement takes ages in SSMS:

Declare @Run varchar(9);
Set @Run='E20140718';

Select Top 20 *
From Viewname
Where RunId= @Run
Order by RelativeRWAVariation Desc

While the second Query from above performs very good.


texts: "Unexpected EOF" during SELECT on huge MyISAM table

texts:
When executing a SELECT query that uses filesort on a huge table (2 billion rows) the execution stops after ~30mins with the following error message:

ERROR 9 (HY000) at line 1: Unexpected EOF found when reading file '/tmp/MYIcoG9f'
(Errcode: 175 - File to short; Expected more data in file)

I use "5.6.21 MySQL Community Server (GPL)" on Debian 7.0 on a 64-Bit CPU. The system has 64 GB of physical RAM and about 340 GB of free disk space. The table is stored as MyISAM table.

The query I run looks like:

SELECT id, UNIX_TIMESTAMP(time) AS unixtime, somecolumn, othercolumn FROM
       bigtable WHERE somecolumn=b'0' ORDER BY time ASC;

First I assumed that the drive on which /tmp resides ran out of free disk space. I ran df every second in parallel to executing the query. In the df output I can see that MySQL's temporary file consumed only about 70 GB; the disk had still 270 GB free space. Also, I ran the same query successfully on the same machine on three other tables with the same structure, but less rows (1 billion rows and below).

Maybe I need to adjust a server variable? Below you can find some vars which may be relevant. Please let me know in the comments if other variables are relevant as well and I'll add them here.

mysql> SHOW VARIABLES WHERE `Variable_name` LIKE '%buf%' OR `Variable_name` LIKE '%tmp%';
+-------------------------------------+-------------------+
| Variable_name                       | Value             |
+-------------------------------------+-------------------+
| bulk_insert_buffer_size             | 8388608           |
| default_tmp_storage_engine          | InnoDB            |
| innodb_buffer_pool_dump_at_shutdown | OFF               |
| innodb_buffer_pool_dump_now         | OFF               |
| innodb_buffer_pool_filename         | ib_buffer_pool    |
| innodb_buffer_pool_instances        | 8                 |
| innodb_buffer_pool_load_abort       | OFF               |
| innodb_buffer_pool_load_at_startup  | OFF               |
| innodb_buffer_pool_load_now         | OFF               |
| innodb_buffer_pool_size             | 134217728         |
| innodb_change_buffer_max_size       | 25                |
| innodb_change_buffering             | all               |
| innodb_log_buffer_size              | 8388608           |
| innodb_sort_buffer_size             | 1048576           |
| join_buffer_size                    | 262144            |
| key_buffer_size                     | 34359738368       |
| max_tmp_tables                      | 32                |
| myisam_sort_buffer_size             | 8388608           |
| net_buffer_length                   | 16384             |
| preload_buffer_size                 | 32768             |
| read_buffer_size                    | 131072            |
| read_rnd_buffer_size                | 262144            |
| slave_load_tmpdir                   | /tmp              |
| sort_buffer_size                    | 34359738368       |
| sql_buffer_result                   | OFF               |
| tmp_table_size                      | 16777216          |
| tmpdir                              | /tmp              |
+-------------------------------------+-------------------+


texts: while restoring mysql database, views changed into tables

texts:
I have a full database dump and i restored it. But in my new restored server all the views are appeared as MyISAM tables. In early times dumps were restored perfectly. can anyone help me to trigger out the issue, please?

Regards,
Praveen


texts:
I am attempting to only select the 1st row returned from the query.  This is my syntax ->

insert into @Temp (id, salesID)
select
    RN = ROW_NUMBER() OVER(order by t.psUserID)
    ,t.psUserID
from (
    select distinct
    psUserID = rstln.Bama
    From rusticlines rstln
    AND tnr.Active = '1') t
WHERE RN = 1;

And I get the error:

  Msg 207, Level 16, State 1, Line 51
  Invalid column name 'RN'.

What do I need to change so that it only selects the 1st row number?


texts: Most efficient way to insert data to MS SQL table from OpenQuery on DB2 linked server?

texts:
after much researching and experimenting, I figured I would make an attempt to get some expert advice.

I am maintaining a stored procedure that is executed as a job. It creates a table with data nicely defined. It then uses an OpenQuery to collect some data and insert it into the table.

Example:

insert into LOCAL_TABLE
select *
from OPENQUERY(linked_server, 'select * from linktable')

The result of the openquery is 2.5 million rows. This takes roughly 2 hours to complete.

The DB server is an iSeries so I ran the query with Visual Explain and the query completes in about 1 second. I am running the query from MS SQL Server 2016. Both servers sit right next to each other connected through a gigabit switch.

I am trying to identify the bottleneck here and I believe it is the INSERT.

I have read about BULK INSERT and it would appear I cannot use BULK INSERT with an OpenQuery.

I have read about OPENROWSET(BULK... ) but I don't think I can use a linked server with this. I have to maintain using the linked server so that if the server changes the code does not need to be updated.

Finally, running this as SELECT * INTO may increase performance, but then the data types would be defined by the result set. I suppose I could work with it from there once it's local but I'd like to confirm this is worth the effort before embarking on it.

Any recommendations as to how I can improve performance here?


texts: How to use a list in a list in report builder?

texts:
I'm currently using Microsoft SQL Report Builder to create a custom report for my data. I have a table of customers, a table of devices and a table of jobs (each customer has a couple of devices, each device has a couple of jobs).

What I want is a list of customers, and when I click on a customer it should expand and show all associated devices, and when I click on the device it should expand and show all jobs.

Should I just use a list in a list in a list? Or should I embed a subreport inside my list? Should I split all 3 tables in different datasets? Or just use 1 dataset?

I imagine there are a lot of different options (I did my research online) but there seems to be little consensus about whether it's a good idea to use subreports or not for example.


texts: Column types analysis of columns used in joins

texts:
Is it possible to do a deep type analysis on the source code base, views and stored procedures in order to identify joins of columns that don't have compatible types?

I inherited a database that hasn't been consistent in using varchar vs nvarchar columns. And sometimes numeric columns are joined with text fields that contain numbers. Now there are some changes that bring more data in some of the tables in the database as in tens of millions of records and any discrepancies in the joined types can have huge performance impacts. These tables have varchar fields and it is not possible to change them to nvarchar (they are populated by a third party product that doesn't support nvarchar).

The options that I have:

Convert all the varchar columns to nvarchar. Unfortunately, it is not possible. That would have been my preferred option.
1.b Convert all the nvarchar columns to varchar. Hmmm... I feel this is a rabbit hole I probably don't want to get into though all the text data is supposed to be in the English language.
Use the MS Transact SQL parser (DacFx) to analyse the queries, however, I feel this is going to take some significant programming effort. I would need a complete list of all sql server functions and the types they return.

Any other ideas?

Thanks


texts: Why does auto increment jumps by more than the number of rows inserted?

texts:
I am very perturbed by this weird behaviour I am seeing in the auto_increment value recorded in the bidID of a Bids table after performing bulk insertion using a stored procedure:

INSERT INTO Bids (itemID, buyerID, bidPrice)
 SELECT itemID, rand_id(sellerID, user_last_id), FLOOR((1 + RAND())*askPrice)
 FROM Items
 WHERE closing BETWEEN NOW() AND NOW() + INTERVAL 1 WEEK ORDER BY RAND() LIMIT total_rows;

For example, if the auto_increment bidID value is 101 at start, and I inserted 100 rows, the ending value becomes 213 instead of 201. However, the bidIDs of those inserted rows runs sequentially to a maximum of 201.

Having check the following,

SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

I have no idea why it is happening. What could be causing the jump in the auto increment value?


texts: Change query to improve operator estimates

texts:
I have a query which runs in an acceptable amount of time but I want to squeeze the most performance possible from it.

The operation I'm trying to improve is the "Index Seek" at the right of the plan, from Node 17.

I've added appropriate indexes but the estimates that I get for that operation are half of what they are supposed to be.

I've looked for changing my indexes and adding a temporary table and re-writing the query, but I couldn't simplify it more than this in order to get the right estimates.

Does anyone have any suggestions on what else I can try?

The full plan and its details can be found here.

The non-anonymized plan can be found here.

Update:

I have a feeling the initial version of the question raised a lot of confusion, so I'm going to add the original code with some explanations.

create procedure [dbo].[someProcedure] @asType int, @customAttrValIds idlist readonly
as
begin
    set nocount on;

    declare @dist_ca_id int;

    select *
    into #temp
    from @customAttrValIds
        where id is not null;

    select @dist_ca_id = count(distinct CustomAttrID)
    from CustomAttributeValues c
        inner join #temp a on c.Id = a.id;

    select a.Id
        , a.AssortmentId
    from Assortments a
        inner join AssortmentCustomAttributeValues acav
            on a.Id = acav.Assortment_Id
        inner join CustomAttributeValues cav
            on cav.Id = acav.CustomAttributeValue_Id
    where a.AssortmentType = @asType
        and acav.CustomAttributeValue_Id in (select id from #temp)
    group by a.AssortmentId
        , a.Id
    having count(distinct cav.CustomAttrID) = @dist_ca_id
    option(recompile);

end

Answers:

Why the odd initial naming in the pasteThePlan link?

Answer: Because I used anonymize plan from SQL Sentry Plan Explorer.
Why OPTION RECOMPILE?

Answer: Because I can afford recompiles in order to avoid parameter sniffing (the data is / could be skewed). I have tested and I am happy with the plan that the Optimizer generates while using OPTION RECOMPILE.
WITH SCHEMABINDING?

Answer: I'd really want to avoid that and would use it only when I have a indexed view. Anyway, this is a system function (COUNT() ) so no use for SCHEMABINDING here.

Answers to more possible questions:

Why do I use INSERT INTO #temp FROM @customAttrributeValues?

Answer: Because I noticed and now know that when using variables plugged into a query, any estimates that come out of working with a variable is always 1. And I tested putting the data into a temp table and the Estimated is then equal with Actual Rows.
Why did I use and acav.CustomAttributeValue_Id in (select id from #temp)?

Answer: I could have replaced it with a JOIN on #temp, but developers were very confused and proffered the IN option. I don't really think there would be a difference even by replacing and either way, there is no problem with this.


texts: Why set up static data in views vs. using tables in mysql?

texts:
I get an LDAP feed nightly.  I get it as a text file and dump/create my LDAPALL table. There are roughly 75K employees times about 50 fields.

I have the following too:

LDAPIMPORTANT - view that stores all 75K but only 15 fields

LDAPSHORT - view that stores all 75k but 5 fields

LDAPAB - view that only stores 9k employees based on two groups (field lookup)

Each of these are used a lot and for different apps and also there are a lot of views written against these views.  But there is no updates to them.  We do not update employee data. It is just LDAPALL update once a night.

In this circumstance should I create tables from the LDAPALL table instead of views?  I could set up jobs to create these tables once a night.  What is best practice behind this?  Speak in layman's terms because I am a PHP developer made to do all DB admin stuff.


texts:
I'm building a Data Warehouse in MySQL based on yelp dataset.

Most of the keys in the dataset are given as strings:

user_id                 review_id               business_id
hzw-qTUVpmLAKjdkoUNh8A  Awq_6cyNjK1-qPZAwnXjjQ  7p6tHUA1Pknh0DVWqz86lA
mldKxVI59o3LhK3ITG6mnA  96YkAuJzlT54qZZWNebFUg  7p6tHUA1Pknh0DVWqz86lA
SaedHW9i7k4lHR8tgwtMgQ  OfZRG7RgKA118zDtj6yo-g  7p6tHUA1Pknh0DVWqz86lA

Should I transfer them to self-generated keys (Auto-increment Integers) or leave them as is (VARCHAR(22)).

What are the considerations in Primary/Foreign Keys data type selection?

Thanks


texts: Greenplum DB changing table column length

texts:
I am new to using Greenplum DB, was working on Oracle DB. I understand conceptually Greenplum in columnar DB and has different workings then Oracle.

The question I have is regarding altering table column length. I created a table with a column with say data type character varying(50). On top of this table I created a view which is summarizing some information. Later I wanted to change the length of column to character varying(100). It is not allowed in GP.

I had to drop view, change the column length and then re-create the view. I do not believe that this was the case on Oracle. Not sure why GP has to to this.

Does someone has understanding as to how to avoid this?


texts: Count number of rows and sum up a field of all tables in all relevant schemas with a readonly connection

texts:
We have a rather complex database setup which involves 500 schemas. Most of these schemas contain a certain table. This table contains a certain number of rows and those rows contain a size field.

I need to iterate the schemas, detect those schemas that contain the relevant table and count the number of rows and sum up the size field.

The correct SQL statement for one schema looks like this:

SELECT
    TRUNC(SUM((ceil(SMART_REPOSITORY_LOGICAL_FILE.SRLF_SIZE / 4096)*4096)) / (1024*1024),0) AS
    "Volume in MB",
    COUNT(*) AS "number of files"
FROM
    SCHEMA_XYZ.SMART_REPOSITORY_LOGICAL_FILE
WHERE
    SMART_REPOSITORY_LOGICAL_FILE.SRLF_IS_DIRECTORY = 'N'

How could I run this over all schemas that contain the table SMART_REPOSITORY_LOGICAL_FILE ?

I already tried answers available here that involve some XML magic but obviously I don't have the permission to use those calls because I am rejected. I also can not use approaches that involve writing to the schemas.


texts: Do MySQL sub-queries essentially use as much overhead as separate queries?

texts:
If I have a query like this:

SELECT name FROM
(
 SELECT * FROM `table`
 WHERE id IN (1,40,300)
 ORDER BY AnotherColumn
)

From a bandwidth and overhead standpoint, is that the same thing as doing three separate queries?  Or is it significantly less overhead?


texts: Startup Oracle Database with an NFS mount on the control file

texts:
I currently have an Oracle Database which works just fine on 1 server.

I want to share the folder which contains its' control file to another server via an NFS mount.

However when mounted I get presented with the error:

ORA-00205: error in identifying control file, check alert log for more info

The mount command I'm running is:

sudo mount -v -t nfs4 -o intr,bg,nolock,port=2222,mountport=3333,tcp localhost:/oracle/oradata/stby/ /u02/app/oracle/oradata/stby/

And my /etc/exports file from the attempted mount machine is:

/oracle/oradata/stby *(no_auth_nlm,insecure_locks,rw,sync,no_root_squash,nohide,insecure)

The mount in itself works! I just need to find a way in which the control file doesn't get 'locked' I guess.


texts:
I am trying to migrate a sql query into PostgreSQL. After running the query I get this error:

ERROR: syntax error at or near "("
LINE 20: ..._date is null or fcm.effect_end_date>=current_date()) limit ...

Below is my query:-

select farechart_master_id,farechart_name,version_number_service_stype,
fcm.route_id,st.service_type_name,fcm.passenger_type_id,
fcm.effect_start_date,fcm.effect_end_date,fcm.nignt_service,
fcm.peak_time,fcm.flexi_fare,r.route_number,r.route_direction,
r.effective_from,r.effective_till from farechart_master fcm
left join rate_master rm on rm.rate_master_id=fcm.rate_master_id
left join route r on r.route_id=fcm.route_id
left join service_type st on st.service_type_id=fcm.service_type_id
where fcm.deleted_status=0
and (fcm.effect_end_date is null or fcm.effect_end_date>=current_date())
limit 0 offset 10

Where am I going wrong?


texts: How do I dump only the data for all tables using mysqldump

texts: I have a new database with schema and primary key, FK, index constraints created for all tables. How do I dump only the data from an existing database using mysqldump, so that I can import only the values alone and exclude everything else.


texts:
Im implementing a trigger to be executed when the end date of a booking is passed

Its supposed to change a cats status within checked in from 1 to 0

when updating the table changing an ex booking cats stay to 1 it does not change it back to 0

This is the following script:

DELIMITER |
CREATE TRIGGER CheckOut
BEFORE UPDATE ON BOOK
FOR EACH ROW
BEGIN

    SET @CheckedInCat = ( SELECT Checked_In FROM CATTERY.BOOKCAT WHERE Ref = NEW.Ref );
    SET @StartDCopy = ( SELECT Start_Date FROM CATTERY.BOOK WHERE Ref = NEW.Ref );
    SET @EndDCopy = ( SELECT End_Date FROM CATTERY.BOOK WHERE Ref = NEW.Ref );
    Set @CurrrentDate = '2019-11-25';

    IF @CurrentDate >= @EndDCopy THEN
        IF CheckedInCat = 1 THEN
            UPDATE `CATTERY`.`CATINFO` SET `Checked_In` = '0' WHERE (`Chip_ID` = BOOKCAT.Chip_ID);
        END IF;
    END IF;
END |
DELIMITER |

Table structure:

create schema test;
use test;
CREATE TABLE BOOK( Ref INT NOT NULL AUTO_INCREMENT, Start_Date DATE NOT NULL, End_Date DATE NOT NULL, PRIMARY KEY(Ref));
CREATE TABLE CATINFO( Chip_ID INT NOT NULL AUTO_INCREMENT, Checked_In TINYINT NOT NULL, PRIMARY KEY(Chip_ID));
CREATE TABLE BOOKCAT( Ref INT NOT NULL, Chip_ID INT NOT NULL, FOREIGN KEY (Ref) REFERENCES BOOK(Ref), FOREIGN KEY (Chip_ID) REFERENCES CATINFO(Chip_ID));

INSERT INTO BOOK(Start_Date, End_Date) VALUES ('2019-11-22', '2019-11-25'),('2019-11-24', '2019-11-28'), ('2019-11-01','2019-11-02');
INSERT INTO CATINFO(Chip_ID, Checked_In) VALUES(1, 1), (2, 1), (3, 0);
INSERT INTO BOOKCAT(Ref, Chip_ID) VALUES(1, 1),(2, 2),(2, 3),(3, 1);

it should be changing cat id 1 to Checked_In to 0, like the following:

# Chip_ID, Checked_In
1, 0
2, 1
3, 0

The current date is only a local variable for testing purposes


texts: How to use a table when the user identifier can be one of two fields

texts:
Recently we've started developing Github like organisation system for our service.

We have both free and payed plans.

There are users. Also there should be the owner for organisation and the owner has to register his credit card for the payed plan.

Owners can invite any users.

We cost each users and owners separately.

For example,

ManA registered with his email ManA@gmail.com
ManA also opened up his organisation named "OrgA"with his email OrgA@gmail.com
I'm struggling with how I can architect the table like Github.

I thought "Pivot table" would be great but it's okay as long as the email is the same. (ManA also opened up his organisation named "OrgA"with the same email as he uses for his personal account. ManA@gmail.com)

Payment info is also same as the email. ( Any users can register another billing info for his own organisations.)

I just wonder how I could implement the good entity relations.

NOTE: This is as the same as I posted on https://stackoverflow.com/questions/38096718/how-to-use-a-table-when-the-user-identifier-can-be-one-of-two-fields.

But maybe here is the right place to post, so as soon as I get some answers, I will delete posts in stackoverflow.

##### Update  ######

Scenario

I am new to databsae design, and I decided to develop to make Github like organisation billing system.
However I am having trouble modeling and normalizing it.

The databse is meant to store data about Credit card info (Actually it will be token though.), Users, Organization, Billing

Business rules

A User can have multiple billing information
A User can join any organizations
A User can be an organizer.
A User has to pay for his own usage.
A User can separate  his creditcard billing information
An Organization always has an organizer
An Organizer has to pay for usages made by users in the group
An Organization has a name and an emai.

The issue

I'm struggling to apply any relations between tables efficiently.
Also the problem is that if the credit_card has changed the billing history will be gone with this implementation.
So I'd love to put Billing relations to Person & Organization, however it will be mess.
Any ideas?

Model

##### Update  2016 Jul 8th######

##### Update  2016 Jul 8th (2)######


texts: Oracle 11g - Database Job About User Lock

texts:
I have an Oracle database (Oracle 11gR2) and I want to create a job. The job will trigger my procedure.
The procedure must comply with the rules I have specified below:

If a user's expiry_date value is EXPIRED (select username, account_status, lock_date, expiry_date from dba_users where account_status='EXPIRED';), it will lock this user.

If a user's expiry_date value is EXPIRED and if it is already lock state, it will not do anything.

Exclude internal schemas for this operation.

I searched it but I didn't find any clear solution for it.
How can I create this procedure?
Best regards,


texts: Replication doesn't resume once it fails to connect

texts:
We have MySQL replication running for our application. Right now the slave fails to resume if it fails to connect to the server for some time.

We get the error below:

[ERROR] Slave I/O: error connecting to master 'replication-user@master-ip:3306' - retry-time: 60  retries: 186, Error_code: 1045

The above is possible because of power failure or network problems. Once the issue is fixed and it is able to connect to the master server it tries to resume but most of the times it fails with the error below:

  [ERROR] Slave SQL: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594

I followed this question and solved this problem by resetting the slave to last read relay-bin file and position. The problem is that the connection failure has become very frequent and we need to do the same thing pretty often.

What could be the possible reason of this occurring over and over again? Is there a way to overcome this problem?

Note: We are using MySQL 5.6.30.


texts:
I'm writing a script which weekly updates the users who have access to a database (all users from a specific group in our AD).

In my script I have to authenticate me as an user with the privileges to create users and to grant them privileges.

I dislike using the root user (and his plaintext password!) in a script, so I hope to create a dummyuser with as little as possible privileges, I want to reduce the security risk.

Is there a possible way and will it be secure? Which privileges will this dummyuser need?


texts:
Seems like a simple question but I've found conflicting information out there. Can anyone enlighten me (ideally with a rock-solid source)? Is it:

Part of the transaction log, per database?
Part of tempdb?
Somewhere else...

My gut says the transaction log file.


texts: Cannot start recv'ing chunk, sharding is not enabled and no config server was provided

texts:
I config a cluster in MongoDB as following:

1 Config Server
2 Shard Servers,

and this is Sharding status:

--- Sharding Status ---
  sharding version: {
        "_id" : 1,
        "minCompatibleVersion" : 5,
        "currentVersion" : 6,
        "clusterId" : ObjectId("59c333c5d92439c17b08a330")
}
  shards:
        {  "_id" : "rs1",  "host" : "rs1/ShardServer1:27018",  "state" : 1,  "tags" : [ "siteGroup1" ] }
        {  "_id" : "rs2",  "host" : "rs2/ShardServer2:27018",  "state" : 1,  "tags" : [ "siteGroup2" ] }
  active mongoses:
        "3.4.9" : 2
 autosplit:
        Currently enabled: yes
  balancer:
        Currently enabled:  yes
        Currently running:  no
                Balancer lock taken at Thu Sep 21 2017 05:39:18 GMT+0200 (W. Europe Standard Time) by ConfigServer:Balancer
        Failed balancer rounds in last 5 attempts:  0
        Migration Results for the last 24 hours:
                195 : Failed with error 'aborted', from rs2 to rs1
  databases:
        {  "_id" : "DataCollector",  "primary" : "rs2",  "partitioned" : true }
                DataCollector.EMessageData
                        shard key: { "SiteId" : 1 }
                        unique: false
                        balancing: true
                        chunks:
                                rs2     5
                        { "SiteId" : { "$minKey" : 1 } } -->> { "SiteId" : 1 } on : rs2 Timestamp(1, 1)
                        { "SiteId" : 1 } -->> { "SiteId" : 10 } on : rs2 Timestamp(1, 2)
                        { "SiteId" : 10 } -->> { "SiteId" : 11 } on : rs2 Timestamp(1, 4)
                        { "SiteId" : 11 } -->> { "SiteId" : 20 } on : rs2 Timestamp(1, 5)
                        { "SiteId" : 20 } -->> { "SiteId" : { "$maxKey" : 1 } } on : rs2 Timestamp(1, 6)
                         tag: siteGroup1  { "SiteId" : 1 } -->> { "SiteId" : 10 }
                         tag: siteGroup2  { "SiteId" : 11 } -->> { "SiteId" : 20 }

When I move a chunk from a Shard Server to another and got the error message:
"cannot start recv'ing chunk, sharding is not enabled and no config server was provided"

Could you please help me to resolve this problem?

Thank you in advanced!

Updated:
Config Server log says:

2017-09-22T03:48:32.803+0200 I SHARDING [Balancer] distributed lock 'DataCollector.EMessageData' acquired for 'Migrating chunk(s) in collection DataCollector.EMessageData', ts : 59c46aefbd78955b52702afc
2017-09-22T03:48:32.809+0200 W SHARDING [conn5] cannot start recv'ing chunk, sharding is not enabled and no config server was provided
2017-09-22T03:48:32.817+0200 I SHARDING [NetworkInterfaceASIO-ShardRegistry-0] distributed lock with ts: '59c46aefbd78955b52702afc' and _id: 'DataCollector.EMessageData' unlocked.
2017-09-22T03:48:32.820+0200 I SHARDING [Balancer] Balancer move DataCollector.EMessageData: [{ SiteId: 1.0 }, { SiteId: 10.0 }), from rs2, to rs1 failed :: caused by :: UnknownError: cannot start recv'ing chunk, sharding is not enabled and no config server was provided
2017-09-22T03:48:32.820+0200 I SHARDING [Balancer] about to log metadata event into actionlog: { _id: "ELVN-LAP02-2017-09-22T03:48:32.820+0200-59c46bf0bd78955b52702dcf", server: "ELVN-LAP02", clientAddr: "", time: new Date(1506044912820), what: "balancer.round", ns: "", details: { executionTimeMillis: 31, errorOccured: false, candidateChunks: 1, chunksMoved: 0 } }

Log file of nodes:
https://github.com/jvanhien/mongodbcluster


texts: Stored Procedure return unwanted cursor result

texts:
Context
I have created a stored procedure that simulates a merge command for an OLTP in-memory table. It used the simulated merge for upsert moving massive data form a normal table to an OLTP one.
Procedure
This is the code:
CREATE PROCEDURE [cache].[MoveInverterData] (@sourecInverterID bigint, @from datetime2(7))
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    DECLARE @InverterID bigint, @Timestamp datetime2(7), @Status nvarchar(50);

    DECLARE Employee_Cursor CURSOR READ_ONLY FOR
        SELECT [InverterID],[Timestamp],[Status]
        FROM [data].[InverterData]
        WHERE [InverterID] = @sourecInverterID AND [Timestamp] >= @from;

    OPEN Employee_Cursor;

    FETCH NEXT FROM Employee_Cursor;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        FETCH NEXT FROM Employee_Cursor INTO @InverterID, @Timestamp, @Status;

        UPDATE [cache].[InverterData]
        SET [Status] = @Status
        WHERE [InverterID] = @InverterID AND [Timestamp] = @Timestamp;

        -- if there was no row to update, insert
        IF @@ROWCOUNT=0
            INSERT INTO [cache].[InverterData]
                ([InverterID],[Timestamp],[Status])
            VALUES
                (@InverterID, @Timestamp, @Status);
    END

    CLOSE Employee_Cursor;
    DEALLOCATE Employee_Cursor;

    RETURN(0)
END

The cursor upserts a range of rows like this because OLTP tables (target) do not support MERGE commands.
Problem
If I execute my SP I will get a result the first cursor selected FETCH and a list of single row counts.

Question
I understand that this is normal behavior, but I try to suppress this to get a simpler result.
What I want:

No table result from CURSOR select
A sum of affected row count as single number of all update/insert operations

The empty FETCH was indeed an error and solves the ROW result problem (point 1).
Looks like this now:
CREATE PROCEDURE [cache].[MoveInverterData] (@sourecInverterID bigint, @from datetime2(7))
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    DECLARE @InverterID bigint, @Timestamp datetime2(7), @Status nvarchar(50);

    DECLARE Employee_Cursor CURSOR READ_ONLY FOR
        SELECT [InverterID],[Timestamp],[Status]
        FROM [data].[InverterData]
        WHERE [InverterID] = @sourecInverterID AND [Timestamp] >= @from;

    OPEN Employee_Cursor;

    FETCH NEXT FROM Employee_Cursor INTO @InverterID, @Timestamp, @Status;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE [cache].[InverterData]
        SET [Status] = @Status
        WHERE [InverterID] = @InverterID AND [Timestamp] = @Timestamp;

        -- if there was no row to update, insert
        IF @@ROWCOUNT=0
            INSERT INTO [cache].[InverterData]
                ([InverterID],[Timestamp],[Status])
            VALUES
                (@InverterID, @Timestamp, @Status);

        FETCH NEXT FROM Employee_Cursor INTO @InverterID, @Timestamp, @Status;
    END

    CLOSE Employee_Cursor;
    DEALLOCATE Employee_Cursor;

    RETURN(0)
END


texts: Apply INNER JOIN only if there is a row available otherwise use NULL


texts:
I have a table with 8 columns, no primrary key, and many, many indexes. Now I wonder whether they're as optimal as can be.

The table looks as follows:

CREATE TABLE `search` (
  `a` bigint(20) unsigned NOT NULL,
  `b` int(10) unsigned NOT NULL,
  `c` int(10) unsigned DEFAULT NULL,
  `d` int(10) unsigned DEFAULT NULL,
  `e` varchar(255) DEFAULT NULL,
  `f` varchar(255) DEFAULT NULL,
  `g` varchar(255) DEFAULT NULL,
  `h` varchar(255) DEFAULT NULL,
  `i` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This table is used for searches, and searches contain constant values to look for, and a specific range of two columns (i.e. a, and b). Never together.

An example of a query that occurs very often:

SELECT DISTINCT S.a
  FROM search
 WHERE S.c = 12
   AND S.f = 'foo'
   AND S.h = 'bar'
   AND S.a < 1234567890
 ORDER BY S.a DESC
 LIMIT 200;

An was to have two composite indexes, containing all columns used for constant comparisons, with a single range column appended to it. This resulted in the following two indexes:

KEY composite_a (`c`, `d`, `e`, `f`, `g`, `h`, `i`, `a`)
KEY composite_b (`c`, `d`, `e`, `f`, `g`, `h`, `i`, `b`)

However, this feels excessive, and it still resorts to temporary tables, and file sorting if only a subset of the columns is used for searching.

A different option was to add every single combination with the range columns appended to it (which works quite well), but fills up storage rather quickly. To give a sense of size, it's a table containing a few billion rows. This caused the need for a significantly larger machine with more memory, because of indexes growing into the several terabytes range.

My question is: am I missing an index 'trick'? Is there an efficient combination of index to solve this problem?


texts: SQL Browser Service Stops After One Minute

texts:
I am encountering an issue with the SQL Browser service where the service will start, run for around 60-90 seconds and then stop. No error is logged, the event logs show three informational entries:

The SQL Server Browser service has started.
The SQL Server Browser is enabling SQL instance and connectivity discovery support.
The SQL Server Browser service has shutdown.

Things I've tried so far:

Removing and reinstalling SQL Browser.
Repairing the SQL installation.
Changing the logon account for SQL Browser.
If I run the browser in console mode from the command line (sqlbrowser.exe -c) then it runs fine and does not shut down.
Verified no antivirus is terminating the service.
Turned off Windows Firewall just to be sure.
Restarted machine.
Applied latest updates.

I've run out of ideas. Is there a log file for the Browser service somewhere that might give some clues? Has anyone encountered this issue before?
SQL: 2017 Standard Edition
Windows: 2019 Core
Installed Features: SQL Engine, SSRS, Integration Services, Browser


texts: mysql inner join producing more results than it should

texts:
I am trying to get the roles name, verbs name, and privileges enabled value where privileges is a join table joining verbs and roles through a verb_id and roles_id foreign key.
 But I am having trouble with the join:

SELECT roles.name, verbs.name, privileges.enabled FROM verbs, roles
INNER JOIN privileges on privileges.role_id = privileges.verb_id

It gives me way more records than there are. There are duplicate records for example many 'update student' records

verbs table:
id
name

roles table:
id
name

privileges table:
id
role_id
verb_id
enabled

a verb has many roles through privileges
a role has many verbs through privileges

I would like to show the role names, verb names, and privilege enabled value where the privileges role_id field is associated with the verb_id field. So for example, if role id 1 is associated with verb id 18, then the privileges table would have role_id 1 and verb_id 18 to show the relationship between role and verb. So I would like to select that relationship to display the associating role name, verb name, and privilege enabled value.


texts: Detect data spikes (loops) in MySQL database and ignore these

texts:
i must create some statistics out of a WWW access log database, but i found out that there are some redirect loops after a login if the user has disabled cookies. So there are 10-1000 mostly identical rows like the following:

...
/login?redirect=/app - 200 - 123.123.123.123 - 2014-01-01 10:00:00
/app                 - 403 - 123.123.123.123 - 2014-01-01 10:00:00
/login?redirect=/app - 200 - 123.123.123.123 - 2014-01-01 10:00:00
/app                 - 403 - 123.123.123.123 - 2014-01-01 10:00:01
...

Is there a way to detect unusal count grouped by the ip/url in an interval? I think i need to select all counts for each minute an detect a spike and ignore that... Does anyone had the same difficulties?


texts: Search SQL Server Execution Plan by cost in SSMS

texts:
I was given a large Execution plan and I'm trying to find the nodes connected to high costs. SSMS offers a search util, however, I did not find the option that searches for the cost value in percent.
Given my image below, how do I search for a node causing 21 percent of the cost? The second input allows to select equals and contains. Is it possible to search for operations that cost more than 15 %?


texts:
I have below database tables,
Tables Definition
A - order_dateils :

id
order_bar
description
payment_status
created_at

B - invoices :

id
invoice_id
status
created_at

and created below event to delete data older than 2 months
DELIMITER //
CREATE EVENT
IF NOT EXISTS daily_delete
ON SCHEDULE EVERY 1 DAY STARTS '020-09-13 04:00:00'
DO
BEGIN
    DELETE
    FROM bariq_DB.invoice
    where timestampdiff(MONTH,created_at,NOW()) > 2 and payment_status = 2;
    delete from bariq_DB.order_details
    where timestampdiff(MONTH,created_at,NOW()) > 2 and payment_status = 3;
END;//
DELIMITER ;

and I checked : SHOW PROCESSLIST
+-------+-----------------+-----------+-----------+---------+-------+-----------------------------+------------------+
| Id    | User            | Host      | db        | Command | Time  | State                       | Info             |
+-------+-----------------+-----------+-----------+---------+-------+-----------------------------+------------------+
|     5 | event_scheduler | localhost | NULL      | Daemon  | 61763 | Waiting for next activation | NULL             |
| 34948 | root            | localhost | baridi_DB | Sleep   |   984 |                             | NULL             |
| 34949 | root            | localhost | baridi_DB | Query   |     0 | starting                    | SHOW PROCESSLIST |
+-------+-----------------+-----------+-----------+---------+-------+---------------------------

But the events is not deleting any thing that matches the conditions, what Im missing here?
Any help will be much appreciated


texts: SSMS - Is it possible to pass additional connection parameters via Command Line?

texts:
I'm able to launch SQL Server Management Studio (2014) from command line using something like below.

ssms.exe -E -S myservername -D -mydatabasename

However, I'm not sure how to pass additional connection parameters such as multisubnetfailover=true

Is it possible to do this via commandline or through 'Target' on SSMS windows desktop properties?


texts:
I am working on some data access restriction feature in Oracle 11g and have been struggling since then.

What I want to achieve is, that I want to allow a specific group only to access a particular set of data and the rest should not be able to see them (or have them hashed) ...

Let's image two users

USER_JOHN and USER_TIMMY

The role settings for them is following

USER_JOHN
  - it_analyst
  - sales_analyst
  - read_all

USER_TIMMY
  - risk_analyst

The %_ANALYST roles are just for the sake of demonstration only .. the only important role in this case is the READ_ALL role which allows JOHN to access the "privileged" data ...

Example of privileged data (columns marked with * are considered "privileged"):

CLIENT_TABLE
-------------------------------------------------------------
ID  |  Name   |   Date of Birth * |    Address *  |   Sex   |
-------------------------------------------------------------
 1  | Franco  |     8/1/1990      | Sesame Street |  Male   |
 2  |  Jane   |     1/18/1976     | Corner Road   | Female  |

When a user (with READ_ALL) role queries this table I want him to get the same data set as you can see above

USER_JOHN:$> SELECT * FROM client_table;

-------------------------------------------------------------
ID  |  Name   |   Date of Birth * |    Address *  |   Sex   |
-------------------------------------------------------------
 1  | Franco  |     8/1/1990      | Sesame Street |  Male   |
 2  |  Jane   |     1/18/1976     | Corner Road   | Female  |

But if a user without this READ_ALL role queries this table, they should get only non-privileged data in a read-able form ... see below

USER_TIMMY:$> SELECT * FROM client_table;

-------------------------------------------------------------
ID  |  Name   |   Date of Birth * |    Address *  |   Sex   |
-------------------------------------------------------------
 1  | Franco  |    3978005636     |  4259532014   |  Male   |
 2  |  Jane   |    2282325776     |  1146039631   | Female  |

As you can see, these "privileged" records are hashed ... they can even be empty or not select.able at all .. thus the result will look like this

USER_TIMMY:$> SELECT * FROM client_table;

--------------------------
ID  |  Name   |    Sex   |
--------------------------
 1  | Franco  |   Male   |
 2  |  Jane   |  Female  |

Is there some solution to achieve this? The best result for me would be hashed values .... I have read something about Oracle VPD which should do the same I just do not know, how to apply that on roles globally ...

What I have done so far is that I have created the same object within different schema ...

SCHEMA_RAW.CLIENT_TABLE     -> accessible for READ_ALL; not accessible for others
SCHEMA_HASHED.CLIENT_TABLE  -> accessible for all

I know it's not the best solution but at least I do have some data protection implemented .. the major drawback of this solution is the script-sharing .. if there is a user that has an access to SCHEMA_RAW only .. they need to change the script provided by users that have an access to SCHEMA_HASHED only ...

Note: there is not just this single table ... there are many (hundreds of them) that needs to be secured this way ...

EDIT: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit


texts: Does the order of the column name assignation in the join part have an impact on indexes in MySQL?

texts:
Does the order of the column name assignation in the join part have an impact on indexes ?

For example, if I have an index on table X column Y, and I write:

SELECT * FROM B LEFT JOIN X ON X.Y = B.Y

Then the Y index from table X is used.  However, if I write the same query like this:

SELECT * FROM B LEFT JOIN X ON B.Y = X.Y

will the index still be used?


texts: Use utl_match.jaro_winkler_similarity to select distinct rows using Oracle

texts:
I have a table with log messages that many times contain repeated information with slightly different values.
For example:

Unable to make thread 19043 a realtime process
Unable to make thread 20763 a realtime process
Unable to make thread 22179 a realtime process
FEED_XYZ Secondary Instrument Lines not configured
FEED_ZZZ Secondary Instrument Lines not configured
(...)

I'd like to have just one line from each group above:

Unable to make thread 19043 a realtime process
FEED_XYZ Secondary Instrument Lines not configured

Is there a way to use a select query to retrieve distinct rows with utl_match.jaro_winkler_similarity?

Something like:

select log_message
from logs_table
where "utl_match.jaro_winkler_similarity between rows < 80"

I know that I can make a pipelined function or some other PL/SQL routine, but I just would like to double-check if there's some easier way of doing this.


texts: Are two logically equal indices physically the same index?

texts:
I have a table with this definition:

CREATE TABLE public.delete_operation
(
  id bigint NOT NULL,
  parent_id bigint,
  pdu character varying,
  progress smallint NOT NULL DEFAULT 0,
  description character varying,
  state character varying(31) NOT NULL,
  status character(1) NOT NULL DEFAULT 'A'::bpchar,
  CONSTRAINT delete_operation_pkey PRIMARY KEY (id),
  CONSTRAINT "Ref_delete_operation_to_delete_operation" FOREIGN KEY (parent_id)
      REFERENCES public.delete_operation (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

Index on id created implicitly.

Then I want to create an index on parent_id (which refers to same id column with already created index).

My question is: would it be physically same index with different names or will it double this index ?


texts: Failover an Availability Group during a backup

texts:
This is a scenario I hope won't happen, but if I was to perform a manual failover on an availability group halfway through a database backup will this cause the backup to fail or would it continue?
I've been looking through MS documentation but can't find this specific case.
I'm running SQL 2017 in an active - active environment with the listener in a third location. (synchronous commit)
(Backups are utilizing the Ola Hallengren scripts)


texts: Moving a SQL Server TempDB of AlwaysOn members

texts:
We have two SQL Server instances on Azure Windows virtual machines. Always on availability group is configured between them. We need to move TempDB on both instances, is there a risk that something can get corrupted regrading data and availability group listener or we can simply execute instructions from MSDN instructins found here: http://msdn.microsoft.com/en-us/library/ms345408(v=sql.110).aspx

Thanks.


texts: How to convert rows values into different columns based on the values in a Column in SQL Server 2012

texts:
Scenario:

VendorID | OPTFIELD   |  VALUE  |   VDESC
---------+------------+---------+---------------------
7ELEVEN  | CHANNELAP  |   03    | Independents
7ELEVEN  | CUSTGROUP  | Metcash | Metcash
7ELEVEN  | SUBCHANNEL |   ING   | Independent Grocery

Solution Required:

Vendor_ID | level_2 |  level_2_desc| level_3 |         level_3_dec | level_4 | level_4_desc
----------+---------+--------------+---------+---------------------+---------+--------------
7ELEVEN   |    03   | Independents |   ING   | Independent Grocery | Metcash |      Metcash

So I just want to convert the rows into columns depending on a particular scenario which is based on the value of one of the Columns(OPTFIELD)

Example:
 - When OPTFIELD ='CHANNELAP' then results corresponding to 'CHANNELAP' that is Value and VDESC would appear in different columns as VALUE in level_2 and VDESC in level_2_desc

So, values in OPTFIELD columns is the condition on which VALUE and VDESC should be moved to different Columns.

IF 'OPTFIELD' = 'CHANNELAP'  then level_2(Value) and level_2_desc(VDESC)
IF 'OPTFIELD' = 'CUSTGROUP'  then level_3(Value) and level_3_desc(VDESC)
IF 'OPTFIELD' = 'SUBCHANNEL' then level_4(Value) and level_4_desc(VDESC)

So, the final result will be 1 row for VendorID = '7ELEVEN'

VendorID | level_2 | level_2_desc |level_3 |level_3_desc |level_4 |        level_4_desc
---------+---------+--------------+--------+-------------+--------+--------------------
7ELEVEN  |      03 | Independents |Metcash |     Metcash |    ING | Independent Grocery


texts: SQL Server Trigger When New Value  Old Value

texts:
I need a trigger that update to Null a column everytime another column is changed.

This is what I have now:

ALTER TRIGGER [dbo].[ERROR_CLEAR]
ON [dbo].[contact]
AFTER UPDATE
AS BEGIN
SET NOCOUNT ON;
IF UPDATE (NAME)
BEGIN
    UPDATE contact
    SET ERROR = Null
    FROM contact c2 INNER JOIN Inserted I ON c2.ID = I.ID
    INNER JOIN Deleted D ON d.ID = c2.ID

    WHERE c2.NAME <> I.NAME
    AND I.NAME <> D.NAME

END
END

I'm testing like this:

UPDATE contact SET NAME = 'John' WHERE NAME = 'Paul'

But its not working and I'm running out of ideas. Any help?


texts: Cannot find reason why differential backups size reduced

texts:
Sql Backup job summary:

Hi, I have a backup job that take full backup first day of the month and take differential backup every 2 hours between 9am and 5pm everyday.

Problem:
While testing disaster recovery plan I found that after few differential backups size of differential backup reduced and when I include reduced sized differential backup(red square) I get this error: in restore

Any idea why size of differential backup reduced? and how I can restore this differential backup?

Please help and thanks in advance for reading and answering this question.

Update
As mentioned job is taking full backup start of month there is no full backup between differential backup size of differential backup is increasing till position 91 However, as you see in image no full backup is taken before position 92.


texts:
I have a table with 3 columns, id, name and vote. They're populated with many registers. I need that return the register with the best balance of votes. The votes types are 'yes' and 'no'.

Yes -> Plus 1
No - Minus 1

This column voteis a string column. I am using SQL SERVER.

It must return Ann for me


texts: MySQL Point in Time Recovery For Just a Single Schema?

texts:
Is it possible to simply restore a single schema and bring it to a point in time (Say three hours ago) without bringing the entire database back?

I can think of a lengthy method of backing up each schema individually at present time, bringing the database back three hours and restoring all other schemas (except for the one you want to roll back) back to present time.

Is there any other method? Or am I not understanding binary logs correctly?


texts:
Is it normal in Mysql 8 to have a high percentage of CPU wait?
(see image)


texts: SQL Server database in Recovery mode, how to troubleshoot?

texts:
I have a SAP instance running with a 'SQL Server' database that just recently got into recovery mode without a clear reason (no backup or restore was executed).

We noticed that the storage for logs (max 50gb) was 99% used. After cleaning unused data and turning the db to online, everything was back to normal. But I need to identify the root cause. What can you suggest, please, as a troubleshoot analysis approach?

More details:

MSSQL release 12.00.6329.01
DB Client Library msodbcsql13
Database Releases MSSQL 9.00.2047 or higher

It's connected to SAP system via Database Shared Library:

DBSL Version 753.02 DBSL Patch Level 510
SQL Server 2014 120


texts: mongodump: are 4 shards with total datasize 575 GB consider a Small Sharded Cluster?

texts:
I need to test the restore with Ops Manager. For this I "clone" the production sharded cluster. I create VMs with the same size like production and do mongodump/mongorestore (Ops Manager Deployment). My test (for restore) don't need to be a consistent copy, for me no problem if around 5 GB is missing.

DATA SIZE: 573.6 GB

shard0
142.6 GB

shard1
145.94 GB

shard2
142.55 GB

shard3
142.52 GB

For simplicity reason I wish to take a mongodump and pipe it to mongorestore on the mongos.

I found a an old doc (v3.0) Backup a Small Sharded Cluster with mongodump. This documentation doesn't exists anymore in new MongoDB versions.

  If your sharded cluster holds a small data set, you can connect to a
  mongos using mongodump.

what is a small data set in GB? See above for my deployment.

  If you use mongodump without specifying a database or collection,
  mongodump will capture collection data and the cluster meta-data from
  the config servers.

I don't need to backup explicitly config RS?

  When restoring data to sharded cluster, you must deploy and configure
  sharding before restoring data from the backup. See Deploy a Sharded
  Cluster for more information.

It means in plain English I need to define the shard key (and enable sharding) before restore?

Do I miss any steps/important things?


texts: Need correct mysqlfrm command path to restore a database from .ibd and .frm files

texts:
I'm attempting to restore a database made up of only .ibd and .frm files.
Why I'm doing this: An uninstall of my WAMP installation left only .ibd and .frm files, and was using MySQL 5.6.12. A reinstall of WAMP has MySQL 5.6.17. The WAMP people said if I restore the database in the 5.6.12 then I'll be able to choose the 5.6.12 setup from the Aestan tray menu.

Using this post Recover MySQL database from data folder without ibdata1 from ibd files by Ronaldo, I was able to learn of a routine using mysqlfrm to DISCARD TABLESPACE and then IMPORT TABLESPACE. While that post mentioned creating a database to start, I was informed by someone else here that the ibdata1 file from the 5.6.17 installation could be used in the 5.6.12 as part of the recovery. No database has been set up in the 5.6.17 installation. So I have a "generic" ibdata1 file, I assume.

My database is located at C:\wamp\bin\mysql\mysql5.6.12\data\wordpress.

I've read in this post How can extract the table schema from just the .frm file? where I assume the user put mysqlfrm.exe in the database folder as evident by this command example: C:\MySQL_5.6.10\data\mysql>mysqlfrm --server=root:rootpassword@localhost mysql:user.frm --port=3307 In the example, they're trying to read frm files. My goal is to to establish the right location to start running Ronaldo's instructions.

I put mysqlfrm.exe in the wordpress folder and I tried this command: C:\wamp\bin\mysql\mysql5.6.12\data\wordpress>mysqlfrm --server=root:rootpassword@localhost --port=3307 but I got a Fatal Python error: cannot get zip importer instance, and Windows gave me a message about the program stopping running.

Could someone share where I should put mysqlfrm.exe, and what my command should be to get to the point where I can run mysqlfrm to run Ronaldo's routines as listed in the first link above?


texts: Is an accumulating snapshot fact table appropriate for a process with multiple paths?

texts:
Problem

I am modeling a business process that can take a handful of conditional paths from start to completion, and am struggling with understanding which approach is most appropriate from a dimensional modeling perspective.

Possible Solutions

The two options I see as viable for modeling this are:

Model as a single accumulating snapshot table, expect steps b and c to often be null, and supply a path field that defines which path was taken, e.g.:

a
a->b
a->c
a->b->c

Create four accumulating snapshot tables, one for each path.

Is there an obviously-right Kimball-esque way to solve this problem? Does it depend on the relative frequency of the paths?

I've scoured Kimball and various other places on the internet to try to find an answer to this question, to no avail. Any guidance is appreciated!


texts: Transferring IBD Files from one MySQL database to another in Windows

texts:
Without going into loads of historical detail I'll just get straight to the point.

I am trying to move IBD files from one Windows server to another. MySQL is corrupt on the source server but I have a backup of the entire data directory. However, that's all I have. I do not have a mysqldump or anything of the sort, I can't generate one, so the reality is that all I have are the files.

I installed MySQL fresh on another server and copied the old schema into the new MySQL instance. All files moved over just fine with exception of the InnoDB storage engine (IBD) files, which is not a surprise.

I've tried all the suggestions I can find on the web and none of them are working. There's a nice writeup here but it does not work for me: http://www.chriscalender.com/tag/innodb-error-tablespace-id-in-file/

How can I import the IBD files into MySQL? I have all the files and I have a new MySQL instance. There must be a way to accomplish this. The above writeup seems like its sort of on the right track but it just simply does not work for my particular setup.

Any help would be greatly appreciated. Thank you!


texts:
We are building a new server setup following this guide:

http://en.community.dell.com/techcenter/extras/m/white_papers/20441793/download

And it reccomends 7x 960GB SSD's in Raid 5.

Question: If the databases are on the SAN, what is all this extra space for?


texts:
In a simple scenario of me inserting a key-value pair, when value contains some white space, how can I preserve it?

Example:

> > db.bar.insert({"name":"mike    "})
> > db.bar.find() { "_id" : ObjectId("5080c03b8410d443d9acb7f5"), "name" : "mike " }


texts: Query a database specific configuration parameter

texts:
I have a postgres 9.6 server with > 100 databases on it. When doing a mass-migrate, I use statements like this:

ALTER DATABASE mydatabase SET default_transaction_read_only = false;

Which works great. However, occasionally I get into a state where I'm not sure which databases are read only and which aren't. How do I query this parameter? I know how to query it server-wide:

SHOW default_transaction_read_only;

But not at a database specific level.


texts: How to change a column type from SET to an ENUM

texts:
I have a table with a SET column type that need to be changed to ENUM type.
Can I do it with a simple ALTER or will this mess up my data?
NB: there are no multiple values (a,b,c) in the table.


texts: Assessing statement about third normal form

texts:
Consider the following statements:

A relation R, is in 3NF if every non-prime attribute of R, is fully functionally dependent on some key of R.

State True or False.

The answer given is true. But I think its false.
Example:
Let R(A,B,C) be a relation with functional dependencies {A→B, B→C}.
Here A is the candidate key and both B and C are fully functionally dependent on A since A→B and also A→C right?
But this relation is clearly not in 3NF. Where am I wrong?
As per the definition I found here, the set of attributes X will be fully functionally dependent on the set of attributes Y if the following conditions are satisfied:

X is functionally dependent on Y and
X is not functionally dependent on any subset of Y.

Here C is functionally dependent on A and also C is not functionally dependent on any subset of A. So this would imply that C is fully functionally dependent on A right?


texts: Index: Does 0% fragmentation imply a B-tree?

texts:
I am currently dealing with Microsoft SQL Server and its index structures. The data structures for this are trees.

When I reorganize a table index (e.g. the primary key), the result afterwards is 0% fragmentation. Then, in my opinion, the tree is optimally set up. Does this also mean that the structure is now a B-tree, i.e. is a fully balanced tree?


texts:
We're consolidating data from a bunch of databases into four reporting databases each night.

Because the entire dataset is imported each night we do not need to be able to restore the data to a point in time. Thus the databases are in simple recovery mode.

Each time we run the import however, our database ldf files grow to absurdly large sizes (50+ Gigs).

Is there a way to turn off the logging altogether or get SQL Server to clear those log files sooner?

I'm guessing no for clearing as the log_reuse_wait_desc is ACTIVE_TRANSACTION.


texts: Mirroring multiple source databases to a single destination in SQL Server 2008

texts: How can we maintain multiple source databases data in a single destination server database   in SQL Server 2008? They all are at different networks, if that makes any difference.


texts: How can I define the datatype of a view in MySQL?

texts:
I've written a simple aggregation view over a table, and the view returns different data types that the table. This causes some issues on the application side.

Here's a simplified query for the sake of the example:

SELECT id, FLOOR(date_id / 100) month_id, SUM(value) FROM some_table WHERE....;

The problem is that data_id is an INTEGER(11) column, represented in my app as an int32, and the result of FLOOR(date_id / 100) is a BIGINT, represented in my app as an int64.

The type of id also changes, for reasons I can't explain, from int64 to int32.

Is there any way to cast the column to an int32? CAST seemed not to work.
I haven't found any documentation for a way to see what the expected data type of a view result set is.


texts:
I am currently looking for an alternative to writing a large, complex XML file based on a known specification. Currently the file is written in t-sql as a stored procedure. The output is in a results column which is pretty useless when there are thousands of rows and each needs to be written to individual files. It is also a nightmare (for me) to debug. Break points frequently encompass 1000's of lines of code.

To overcome the XML to file problem I altered the stored procedure to insert the XML to a staging table and then use a query in SSIS to pull the data out of the table, a C# script to create the files and then an SSIS extension from codeplex to zip and send the file to an external SFTP server. With a little more automation this process is workable and meets the project criteria.

I have searched google and these forums to try and find a feasible alternative and I was unsuccessful. The ideal solution would not require additional commercial software and open source would be a bit of an issue as well. I am hoping CLR would be an alternative as writing the code in .NET and its xml libraries seem to be more abstract rather than hand coding text. I see no support for CLR or much at all about SQL to XML. XML to SQL is by far more prevalent.

I hope I have covered what I have tried and the efforts I put in to find an alternate solution on my own.

Let me know if anyone has some suggestions, even if they do not meet the ideal solution criteria.

First question post by the way.


texts: how to prevent an oracle block corruption from duplicating a database with a few nologging tables

texts:
I got a few data corruptions in a test db when I performed a duplicate from a production db where a few tables have the nologging option.

Oracle support suggested to turn on the force logging option when perform a duplicate.  I wish that i do not have to change the production settings in order to make a duplicate db in Test.

Any suggestions to prevent data block corruption in the target db from my next duplicate?

ORA-01110: data file 14: '+DATA/xxxxx/datafile/xxxxx01_02.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
Errors in file /xxxxx/app/oracle/diag/rdbms/xxxxx/xxxxx/trace/xxxxx_j000_67043338.trc (incident=1426036):

ORA-01578: ORACLE data block corrupted (file # 15, block # 27467)
ORA-01110: data file 15: '+DATA/xxxxx/datafile/xxxxx01_03.dbf'

ORA-26040: Data block was loaded using the NOLOGGING option
Errors in file /xxxxx/app/oracle/diag/rdbms/xxxxx/xxxxx/trace/xxxxx_j000_67043338.trc (incident=1426037):

ORA-01578: ORACLE data block corrupted (file # 15, block # 27019)
ORA-01110: data file 15: '+DATA/xxxxx/datafile/xxxxx01_03.dbf'

ORA-26040: Data block was loaded using the NOLOGGING option
Errors in file /xxxxx/app/oracle/diag/rdbms/xxxxx/xxxxx/trace/xxxxx_j000_67043338.trc (incident=1426038):

ORA-01578: ORACLE data block corrupted (file # 15, block # 27883)
ORA-01110: data file 15: '+DATA/xxxxx/datafile/xxxxx01_03.dbf'

Oracle enterprise version 11.2.0.3.
Here is the rman script:

SPOOL LOG to '/home/oracle/scripts/REFRESH/target_db_duplicate.150601_1019.log'
run {
allocate channel D1 type sbt_tape;
allocate auxiliary channel T1 type sbt_tape
    parms='ENV=(NB_ORA_CLIENT=server_name,NB_ORA_COPY_NUMBER=3)';
set until time="to_date('01-JUN-2015 02:00:00','DD-MON-YYYY HH24:MI:SS')";
duplicate target database to target_db
    pfile=$ORACLE_HOME/dbs/inittarget_db.ora
    nofilenamecheck;
}
SPOOL LOG OFF;


texts: SQL Trigger to verify account, if not verified dont process stored procedure/transactions

texts:
Is there a way to create a trigger or function to check a user against the database and if not verfied, the following stored procedure is not ran. I would like this option for verification check before stored procedure is ran

A function or trigger something along these lines

DECLARE @strtoken varchar(30)

IF NOT EXISTS (SELECT userid from users where token = @strtoken)
BEGIN
--stop any further transactions
END

Sorry, yes I meant to trigger the event of checking the token against the user table before any further processing of the stored procedure. The reason I'm doing this is because I'm using cookies and cookies can be changed. So I encrypted the token with MD5, 3DES and salted it. Right now I'm wrapping the stored procedure like this:

DECLARE @strtoken varchar(30)
DECLARE @userid bigint

IF EXISTS (SELECT userid from users where token = @strtoken AND userid=@userid)
BEGIN
--run stored procedure here WHERE userid = @userid
END

Of course, the user has to log in to use the web app, but I don't want anyone fiddling with the token. So I figured there was a way to check against the db with the token and if not verified, stop any further processing of the stored procedure without wrapping the entire stored procedure like I'm doing now


texts: Insert values into new columns using values from old columns without locking table

texts:
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?


texts:
In a PostgreSQL 9.6 database, I'm using this sql to get 'application' (field) -> 'tour_id.id' (key and subkey in jsonb field)

SELECT *
FROM "payment" WHERE ("payment"."application"#>>'{}')::text
LIKE '%a43d38d4-9bfb-4ffa-8704-e536f04b0c60%'
ORDER BY "payment"."createdAt" DESC;

My id is unique, but really I'd like to do it the right way in Postgres. (I know how to do it with a key, but not with a subkey.)

Example jsonb value:

[{"tour_id":
   {"id":"a43d38d4-9bfb-4ffa-8704-e536f04b0c60","name":"Terre Sainte 2018"},
   "amount":"500.00",
   "customer_id":{"id":"f492b26d-d260-4bcb-8e96-6532fdf38665","name":"OBAMA John"},
 "id":"5cd8338c-15c2-48c5-b305-9e172bb33a9a"}]


texts: SQL Server 2008 R2 - Service shutdown - what with running stored procedures?

texts:
I would like understand below behavior of SQL Server:

SQL Version:

Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Let's assume that we have stored procedure as the following (some code omitted for brevity):

CREATE PROCEDURE Test
AS
BEGIN TRY
    INSERT INTO test_log ('Stage 1. PROC started')

    ...

    EXEC (@ScheduledProcedure) -- Execution of this takes 8-10 mins
    INSERT INTO test_log ('Stage 2. Scheduled Procedure completed')
END TRY
BEGIN CATCH

    ...

END CATCH;

INSERT INTO test_log ('Stage 3. PROC Completed')

I have done some testing to check what happens with my procedure when I shut SQL Server down when my procedure is still running and here are the results:

When I go to the services.msc and stop the SQL Server, the service will be in 'Stopping' state until @ScheduledProcedure is completed (8-10 mins).
When I shut the OS down it seems that SQL Service cancels stored procedure. I think that it is the case, because Stage 2 and 3 messages are not available in test_log table.

Can someone try to explain this SQL Server behavior ?


texts:
I got app server with MongoDB installed.

Every 3 hours I run some nodejs script that updates data, and my server consumes 100% CPU and becomes slow.

Is there any way to improve that?

Can I create another db instance (cluster), and run the script only on one of them and sync them ?


texts: Is Locked Pages In Memory Still Recommend in SQL Server 2017?

texts: I have done some research about lock pages in memory, the question that still bothers me is (I know it really depends on your specifications but) is it still recommended to lock pages in SQL Server 2017 like it was in SQL Server 2005, 2008 etc.?