texts: Fast way to split primary key into ranges

texts:
I want to split values of an auto-incrementing primary key into ranges of approximately equal size as fast as possible. The number of ranges is low, less than 20. So far I've came up with this:

Determine approximate row count by querying sys.partitions and calculate bucket size.

Run a query select min(id), max(id) from (select top 1000000 id from my_table order by id desc) t

Run a series of queries

select min(id), max(id)
from (
    select top 1000000 id
    from my_table where id > $prev_max
    order by id desc) t;

until all the ranges are found.

Query times are acceptable (several seconds for the first one and less than second for subsequent queries) but is there an easier (preferably a single query) way? Generated ranges don't need to be exact, it's ok if they intersect or have gaps, I just need a good enough estimate.


texts: Poor SELECT performance on recheck cond with data and large (millions of rows) partial index

texts:
Query:

EXPLAIN (ANALYZE, BUFFERS) SELECT
    COUNT (*) AS "count"
FROM
    "Posts" AS "Post"
WHERE
    "Post"."createdAt" > '2015-08-19 14:55:50.398'
AND "Post"."new" = TRUE;

Index:

CREATE INDEX posts_new_createdat_idx ON "Posts" ("createdAt")
WHERE
    NEW = TRUE

Plan:

Aggregate  (cost=389915.59..389915.60 rows=1 width=0) (actual time=4234.772..4234.773 rows=1 loops=1)
  Buffers: shared hit=254427
  ->  Bitmap Heap Scan on "Posts" "Post"  (cost=14415.81..387990.63 rows=769985 width=0) (actual time=123.805..3859.150 rows=1138854 loops=1)
        Recheck Cond: (("createdAt" > '2015-08-19 14:55:50.398+00'::timestamp with time zone) AND new)
        Rows Removed by Index Recheck: 8238790
        Buffers: shared hit=254427
        ->  Bitmap Index Scan on posts_new_createdat_idx  (cost=0.00..14223.32 rows=769985 width=0) (actual time=122.601..122.601 rows=1138854 loops=1)
              Index Cond: ("createdAt" > '2015-08-19 14:55:50.398+00'::timestamp with time zone)
              Buffers: shared hit=3114
Total runtime: 4234.989 ms

Schema:

CREATE TABLE "public"."Posts" (
    "id" int4 NOT NULL DEFAULT nextval('"Posts_id_seq"'::regclass),
    "actionId" int4,
    "commentCount" int4 DEFAULT 0,
    "facebook" bool,
    "featurePostOnDate" timestamp(6) WITH TIME ZONE,
    "forcedPrivate" bool,
    "instagram" bool,
    "isReported" bool,
    "likeCount" int4 DEFAULT 0,
    "note" text COLLATE "default",
    "photo" varchar(255) COLLATE "default",
    "private" bool,
    "new" bool,
    "popular" bool,
    "twitter" bool,
    "userId" int4,
    "objectId" varchar(255) COLLATE "default",
    "createdAt" timestamp(6) WITH TIME ZONE,
    "updatedAt" timestamp(6) WITH TIME ZONE,
    "activityLogId" int4,
    "weightLogId" int4,
    "workoutId" int4,
    "workoutLogId" int4,
    "thumbnail" varchar(255) COLLATE "default"
)

Data:

new = true for 99% or records
Any posts older than 2 weeks can be ignored (from the count and the index)

Database Details:

Host           = Amazon AWS
Engine         = PostgreSQL 9.3.10
Instance Class = db.r3.8xlarge
Storage Type   = SSD
IOPS           = 3000
StorageAmount  = 500 GB

As you can see, the partial index is very large. Is there a way to better index so that the recheck condition is not so heavy?


texts:
I have morning job schedule that starts at 5:30 AM....starting on 5-27-2014. I've begin implementing table replication on one of our heavily used tables...and use the replicated table for reporting purposes only.

Basic Publication
Type = Snapshot
Only one table article being used
No filters
Publish every 20 mins.
Replication agents checkup job every 10 mins

**The unique problem is that the 5:30 AM job calls a SP that has logic to a linked instance...but the logic doesn't (insert, del, or update) the trigger table...actually the logic doesn't use the table at all. But, I've have identified the table and triggers.

Error Message from the failed job:

  Message
    Executed as user: **********. ...because the server option "disallow_results_from_triggers" is true.
    [SQLSTATE 01000] (Message 531)  Cannot set NOCOUNT to OFF inside the trigger execution because the server option "disallow_results_from_triggers" is true.
    [SQLSTATE 01000] (Message 531)  Cannot set NOCOUNT to OFF inside the trigger execution because the server option "disallow_results_from_triggers" is true.
    [SQLSTATE 01000] (Message 531)  Warning: Null value is eliminated by an aggregate or other SET operation.
    [SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation.
    [SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation.
    [SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation.
    [SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation.
    [SQLSTATE 01003] (Message 8153)  Warning: Null value...  The step failed.

Wait and you shall receive....this is the 1st this happened after my last edit

Message

Executed as user:********. ...because the server option "disallow_results_from_triggers" is true.

[SQLSTATE 01000] (Message 531)  Warning: Null value is eliminated by an aggregate or other SET operation.
[SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation.
[SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation.
[SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation.
[SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation.
[SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation.
[SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation.
[SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation.
[SQLSTATE 01003] (Message 8153)  Warning...  The step failed.

Steps to solve the problem...
1. Found all triggers in instances with SET NOCOUNT OFF inside triggers and comment out the line....using the script provided in this thread
2. Ran the job for weeks with no errors until today (7/16/2014 5:30 AM).


texts:
Here is my table:

+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| id          | int(11)       | NO   | PRI | NULL    | auto_increment |
| tckno       | bigint(20)    | YES  |     | NULL    |                |
| adid        | mediumint(9)  | NO   |     | NULL    |                |
| soyadid     | mediumint(9)  | NO   |     | NULL    |                |
| anaadid     | mediumint(9)  | NO   |     | NULL    |                |
| babaadid    | mediumint(9)  | NO   |     | NULL    |                |
| cinsiyet    | enum('E','K') | NO   |     | NULL    |                |
| dogumyeri   | mediumint(9)  | NO   |     | NULL    |                |
| dogumtarihi | date          | NO   |     | NULL    |                |
| nufusil     | tinyint(4)    | NO   |     | NULL    |                |
| nufusilce   | smallint(9)   | NO   |     | NULL    |                |
+-------------+---------------+------+-----+---------+----------------+

Now, according to https://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html my row size is

id(4) + tckno(8) + next 4 rows (4*3) + cinsiyet(1) + dogumyeri(3) + dogumtarihi(3) + nufusil(1) + nufusilce(2) = 34 bytes

I should have 48166478 (row count) * 34 = 1637660252 bytes database. But when I navigate to mysql/data/<database name> folder, I see that <table name>.idb file is 2826960896 bytes on disk. There is a discrepency of 2826960896 bytes - 1637660252 bytes ≈ 1.1GB. What is this extra data?

Here is what I get from exporting said table

CREATE TABLE `optimumtablo` (
  `id` int(11) NOT NULL,
  `tckno` bigint(20) DEFAULT NULL,
  `adid` mediumint(9) NOT NULL,
  `soyadid` mediumint(9) NOT NULL,
  `anaadid` mediumint(9) NOT NULL,
  `babaadid` mediumint(9) NOT NULL,
  `cinsiyet` enum('E','K') NOT NULL,
  `dogumyeri` mediumint(9) NOT NULL,
  `dogumtarihi` date NOT NULL,
  `nufusil` tinyint(4) NOT NULL,
  `nufusilce` smallint(9) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `optimumtablo` ADD PRIMARY KEY (`id`);

ALTER TABLE `optimumtablo` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

This is show table status output for said table;

+--------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name         | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| optimumtablo | InnoDB |      10 | Compact    | 47621825 |             57 |  2759852032 |               0 |            0 |   4194304 |       48166479 | 2015-09-11 20:53:45 | NULL                | NULL                | utf8_general_ci   |     NULL |                |         |
+--------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+


texts: Oracle Developers guide 10g is not connecting to server

texts:
I have installed Oracle developer's guide 10g on Windows 10; it installed successfully.
While connecting to Oracle EBS host data base it give an error.
Entered detail on connection screen:
User - myuser
Pass - mypass
Connect String - myuser@SID

Error:

ORA-12154: TNS:could not resolve the connect identifier specified
Cause
The service name specified is not defined correctly in the TNSNAMES.ORA file.

I check my TNSNAMES.ORA file at my database host machine. I found entry TNSNAMES.ORA:
SID =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SID)
    )
  )

Where and what changes do I need to make in the settings file to connect it properly?
Host machine OS is Linux. Listener services is started.
Note:
tnsnames.ora and sqlnet.ora files are present at two places
1. C:\DevSuiteHome_1\NETWORK\ADMIN - Path created after installing oracle developer guide 10g
2. E:\app\product\11.2.0\dbhome_1\NETWORK\ADMIN - previous oracle installation path
is there any catch in this file locations ?
tnsping result:
C:\Users\mymachine>tnsping mymachine
Used parameter files:
C:\DevSuiteHome_1\network\admin\sqlnet.ora

Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=my_machine_name))
(ADDRESS=(PROTOCOL=TCP)(HOST=my_ip)(PORT=1521)))
OK (20 msec)

C:\Users\mymachine>tnsping IP.32.88
Used parameter files:
C:\DevSuiteHome_1\network\admin\sqlnet.ora

Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=IP.32.88))(ADDRESS=(PROTOCOL=TCP)(HOST=IP.32.88)(PORT=1521)))
OK (70 msec)


texts: MS SQL 2012 - Need to split records from 4 different fields then query those

texts:
So I have a table called 'WORK_ORDER' and the ID is a unique text example '16-0833' next would be 0834, 0835, etc.

In that table there are 4 user fields.. USER_1, USER_2, USER_3, USER_4..

This top work order needs x amount of other work orders to be completed.. so we will in those fields like this.

USER_1 = '16-0838'
USER_2 = NULL
USER_3 = '16-0834;16-0835;16-0835'
USER_4 = NULL

I found a routing that will split them up and if I use this TSQL I get results.

DECLARE @UDF_1 nvarchar(100), @UDF_2 nvarchar(100), @UDF_3 nvarchar(100), @UDF_4 nvarchar(100), @ALL_UDF nvarchar(400);

SELECT        @UDF_1=USER_1, @UDF_2=USER_2, @UDF_3=USER_3, @UDF_4=USER_4
FROM            WORK_ORDER
WHERE        (BASE_ID = N'16-0833')

IF @UDF_1 IS NOT NULL
BEGIN
SET @ALL_UDF = @UDF_1
END

IF @UDF_2 IS NOT NULL
BEGIN
SET @ALL_UDF = @ALL_UDF + ';' + @UDF_2
END

IF @UDF_3 IS NOT NULL
BEGIN
SET @ALL_UDF = @ALL_UDF + ';' + @UDF_3
END

IF @UDF_4 IS NOT NULL
BEGIN
SET @ALL_UDF = @ALL_UDF + ';' + @UDF_4
END

BEGIN

SELECT
    *
FROM
    dbo.func_split(@ALL_UDF, ';')
END

With these results :

  ElementID Element

  1 16-0838

  2 16-0834

  3 16-0835

  4 16-0836

I now need to take those 16-% numbers and requery the Work Order table to get their status, etc.

Just not quite sure how to accomplish this.

Any help is greatly appreciated.   SQL 2012


texts: Drop & re-add subscribers to transactional replication

texts:
I'm trying to get stored procedures to execute on the subscribers, as seems to be best practice. I already have transactional replication running happily.

I add a new stored procedure, and set properties of this stored procedure article, but 'Replicate' is grayed out.

Ok - so I go to the stored procedure root and set properties for all stored procedure articles. Under 'Replicate' I set it to: "Execution in a serialized transaction of the SP", which is the recommended setting.

Yay! All is good!

But SQL Server lies.

If I go back in and check the properties for all stored procedures, it's changed the 'Replicate' setting back to "stored procedure definition only".   It doesn't actually keep my change.

The only mention of this issue I could find was here: http://www.informit.com/articles/article.aspx?p=343672&seqNum=4  where the solution is:
"To make stored procedure execution options available, you must drop any subscriptions from this publication and drop the article from the publication. After you add the article back to the publication, all options will be available to you again."

So - apologies for the long pre-amble, but here's my question:

If I remove my subscribers, make my necessary changes, and add the subscribers back, is SQL Server going to want to create a full snapshot?

I'm asking because a full snapshot takes a few hours & chews up our server, so it needs to be carefully planned.

Any insight anyone can provide would be awesome - thank you!


texts: How can Update table column with the virtual column?


texts: Is Microsoft SQL Server 2016 fully ANSI SQL-92 compliant?

texts:
I am trying to find a compliance confirmation for MS SQL 2016 - if it is fully compliant with ANSI SQL-92 starndard.

I found this article on Microsoft Docs which states it is not, but it refers to ODBC driver and Microsoft Jet engine - not sure if this is exactly the same thing, shouldn't it also relate to T-SQL?


texts: How can I eliminate false "connection string format is not valid" messages from SSIS log?

texts:
I have a project that uses the SSIS catalog with project-level connection managers. The connection managers' connection strings are overridden at the server using a project parameters file, to enable easily repointing the whole project to different data sources by setting different connection strings.

Here's the issue:

It works. No real connection problems or failures.
It bloats the log
    tables horribly in the catalog because upon each package execution I
    get:

  120    30  Error: The connection string format is not valid. It must
  consist of one or more components of the form X=Y, separated by semicolons.
  This error occurs when a connection string with zero components is set on
  database connection manager.

For every connection manager, in every package. I think this has to be a false error that gets triggered when the connection string is overridden with the (correct) values in the param file.

Is there something wrong here? Some way to suppress those messages?


texts: How are multiple indexes used in a query by MySQL?

texts:
Imagine a table where there are separate indexes on columns a and b.

The query runs somewhat like this:

SELECT col1, col2 FROM <table> WHERE a = 'value_1 AND b = 'value_2';

Now, does this query always use the indexes on both the columns? If so, what is the mechanism? And if not, is there any condition which MySQL checks to decide which index to use when more than one are available?


texts: How to implement an effective ACL/auth in a relational db?

texts:
We are looking at implementing an ACL that fulfills the following requirements:

Most users have access to an item (access to the item by default), but a few is denied access.
Most users do not have access to an item (no access to the item by default), but a few is granted access.
A user only has access to a few items.
Default is that all users have access to an items.

We are talking about hundreds of thousands of items and tens of thousands of users. They are all stored in a relational DB. We think that having a many-to-many relation between items and users and defining the access in this relation would be ineffective, but are open for comments about this. We also think that access groups are irrelevant here, but we might be wrong.

What are your thoughts on how to do this?


texts: Change built-in default privileges in PostgreSQL?

texts:
Introduction.

When I create a database,

postgres=# CREATE DATABASE test2 OWNER test2;

it is created with an empty privileges column:

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 test2     | test2    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

Now, it is important to note that this is not the same situation as it having no privileges granted whatsoever. In the latter case, the column contains just {} (which by the way displays as empty, too, in postgres=# \l, AFAIR).

An empty field means that PostgreSQL uses built-in default privileges.
{} means that there are literally no privileges to the database.

Research.

In our case, these built-in defaults happen to be:

=Tc/test2  +  test2=CTc/test2

How do I know that? I issued:

postgres=# GRANT CONNECT ON DATABASE test2 TO test1;

... and that resulted in:

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 test2     | test2    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/test2            +
           |          |          |             |             | test2=CTc/test2      +
           |          |          |             |             | test1=c/test2

... that is the implicit defaults plus test1=c/test2 granted explicitly.

Problem & question.

While I have no problem with =CTc granted to owner in these defaults, I'd like not to have =Tc granted to PUBLIC.

Sure, I can just REVOKE ALL [...] FROM PUBLIC after creation, but is there a way to automatize it?

Afterthought.

Really, why aren't these defaults as I want them by default? Is it a popular practice to run one PostgreSQL server for one app, not just one database on the server per app, even if it is a small one?

E.g. default installation of phpPgAdmin "crashes" when it stumbles upon a database it has no connect privs, I had to modify the source a bit. And despite numerous bug reports, it's been doing so for at least six months now (I can't remember any date of the reports, but one of them stated that six months had passed).


texts: Process Data cube from job on SQL server

texts:
I have an SQL Server Agent job on server(X) that has a step that is supposed to process a data cube on a remote server(Y).

Whenever I run the job it fails and says that server(X) does not have permission to process the Cube or it does not exist. I believe I have the job set up correctly but how do I give access on server(Y) to server(X) to process the cube?? Below is the script I am using. with the "SQL Server Analysis Services Command"

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
<Object>
  <DatabaseID>Analysis Services Project1</DatabaseID>
  <CubeID>S2E</CubeID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Batch>

When I go to add the server(x) to the admin role on server(Y) via object explorer I cannot find the server through check names button. Please help.

I am using SQL Server 2014


texts: App Engine - Datastore ; Exclude columns on backup

texts: I want to backup a table without some columns. Can I exclude column/s? Is there any way to do this?


texts: Cleanup Error Messages in SQL Server ERRORLOG

texts:
The SQL Server's ERRORLOG file has a lot of messages like the following in AG in publisher (primary) as you know that distributor is out of AG Server. Subscriber is in another AG setup.

 Logon       Login failed for user 'domain\svc-prod'. Reason: Failed to open the explicitly specified database 'DB'. [CLIENT: xx.xxx.xxx.xxx]
 Logon       Error: 18456, Severity: 14, State: 38.

This error is because the replication server thinks there are either Publication on production SQL Server and I need to  clean up these publisher/subscriber items and error messages.

These messages are related to Replicated DB that are hosted in AG, these messages are occurring continuously and I wanted to stop them from occurring. Somewhere I need to map the above mentioned user login to the DB but I am not sure how to do that to stop these messages.


texts: T-SQL to check whether or not a indexed view can be created on tableX?

texts:
After applying my database changes on the test environment, the test team starts testing their processes,
one of the things I get is a truncate table error because the table is used in an indexed view.

    (1 row(s) affected)
    ProductIconTypes ending
    ProductImageCategories starting
    Msg 3729, Level 16, State 2, Line 1
    Cannot TRUNCATE TABLE 'dbo.ProductImageCategories'
because it is being referenced by object 'VWProductImageGetByStyle'.

Questions:

1)  next time I want to put an indexed view on one of my databases
what exactly I should be checking?

2) Can I get this done through my favourite route (T-SQL)?

I would not mind powershell either.

Unfortunately on these servers in question we are still on SQL Server 2005 AND SQL Server 2008 R2.

A Comment:

These versions impose a lot of limitations on things that we can do as alternatives. Filtered indexes for instance are not available on SQL Server 2005.

I am actually looking for a way if possible to find out what would stop working if I created this indexed view. In my example, the truncate tables could no longer work.


texts: Is it possible to copy mounted database without RMAN?

texts:
I need to make a copy of production database for development. For some reason I can't use RMAN. My question is: can I copy standby database while its in mounted state and recovery is canceled? Of course I can shut it down to make a "cold backup", but I do not want to lose time for manually copying new archive logs (and I don't have enough space on primary for keeping them for a long time).

The procedure was fine until today. After a few hours I got this:

/usr/sfw/bin/gtar: owfin_d/owfin_d06.dbf: file changed as we read it.

It is strange because timestamps on all db files shows the time when I canceled standby recovery.

UPD After a long time I found the problem. For some reason a scrip on cron schedule changes file permissions and tar responds with "file changed as we read it".


texts:
I was told today that having a table structure similar to this is bad for performance of indexes because of the mismatch of column types.

Table 1
id - int (7) PK
Others ....

Table 2
id int (11) Index    (has to exist in table 1 logically - though not enforced through FK)
Others ....

I'm using MySQL with InnoDB.

The question I suppose could also be related to Varchar 200 vs 220 for example.

I know that the index size will be affected by the extra space per item needed (and storage space in general), probably in the 5-10% area, but will the time taken for joins to happen increase?


texts: Query to compare two column of table 1 with two column of table 2 and update the third column in table 1 as 'y' if the record matches

texts:
I have two tables, Table 1 with column email , number, enabled and Table 2 with column email,number.
Now i have to compare the records of both the column from Table 1 and Table 2 , If the records matches in both the table then i have to update the column enabled as  "Y" in table 1.
Please help me with the query.


texts:
I am trying to construct a query in PostgreSQL 9.0 that gets the longest sequence of continuous rows for a specific column.

Consider the following table:

lap_id (serial), lap_no (int), car_type (enum), race_id (int FK)

Where lap_no is unique for each (race_id, car_type).

I would like the query to produce the longest sequence for a given race_id and car_type, so it would return an int (or long) that is highest.

With the following data:

1, 1, red, 1
2, 2, red, 1
3, 3, red, 1
4, 4, red, 1
5, 1, blue, 1
6, 5, red, 1
7, 2, blue, 1
8, 1, green, 1

For car_type = red and race_id = 1 the query would return 5 as the longest sequence of the lap_no field.

I found a similar question here however my situation is a bit more straightforward.

(I would also like to know the longest sequence for a given car_type for all races, but was planning to work that out myself.)


texts: Missing .msi files gives error while repairing SQL Server 2008R2

texts:
I've got a SQL Server 2008R2 SP1 database installed and I'm trying to run a repair on it using a 2008R2 install binaries.

In the middle of the repair loading, the window suddenly shuts down and gives me this error message, anyone knows what to do with that ? My searching skills have left me alone as of now.


texts:
I have a report that uses data from several datasets. I chose to do it this way because the alternative would be to join about 12 tables.

From what I can gather, I have to use the dataset name to define the scope of the variable when using it in expressions. I have tried to do this, but I still get an error.

The expression I'm using is:

=(Fields!PropertyDescription.Value, "Application")

And the error I get is:

  System.Web.Services.Protocols.SoapException: The Value expression for the text box ‘Textbox11’ refers directly to the field ‘PropertyDescription’ without specifying a dataset aggregate. When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions which specify a dataset scope.

Even if I use an expression such as

=First(Fields!PropertyDescription.Value, "Application")

I get the same error.

Can someone perhaps help me with this error or suggest another way of combining the data?


texts: MariaDB Galera Cluster on Ubuntu 16.04 does not start after rebooting

texts:
I've installed a MariaDB Galera Cluster on Ubuntu 16.04 in 2 nodes.

Here the info from node1:

root@localhost:~# sudo more /etc/mysql/conf.d/galera.cnf
[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://192.168.143.81,192.168.148.75"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="192.168.143.81"
wsrep_node_name="Node1"

and Here the info from node2:

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://192.168.143.81,192.168.148.75"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="192.168.148.75"
wsrep_node_name="Node2”

Everything was working fine since I restart both servers and I have a problem when starting the service:

Aug 02 09:18:03 localhost mysqld[3220]:          at gcomm/src/pc.cpp:connect():158
Aug 02 09:18:03 localhost mysqld[3220]: 2018-08-02  9:18:03 0 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():208: Failed to open backend connection: -110 (Connection timed out)
Aug 02 09:18:03 localhost mysqld[3220]: 2018-08-02  9:18:03 0 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1458: Failed to open channel 'galera_cluster' at 'gcomm://192.168.153.81,192.168.158.75': -110 (Conn
Aug 02 09:18:03 localhost mysqld[3220]: 2018-08-02  9:18:03 0 [ERROR] WSREP: gcs connect failed: Connection timed out
Aug 02 09:18:03 localhost mysqld[3220]: 2018-08-02  9:18:03 0 [ERROR] WSREP: wsrep::connect(gcomm://192.168.153.81,192.168.158.75) failed: 7
Aug 02 09:18:03 localhost mysqld[3220]: 2018-08-02  9:18:03 0 [ERROR] Aborting
Aug 02 09:18:04 localhost systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Aug 02 09:18:04 localhost systemd[1]: Failed to start MariaDB 10.3.8 database server.
Aug 02 09:18:04 localhost systemd[1]: mariadb.service: Unit entered failed state.
Aug 02 09:18:04 localhost systemd[1]: mariadb.service: Failed with result 'exit-code'.

The problem just happens after the Setting up IP Failover with KeepAlived  with a new floating IP 192.168.143.33

but I also did:

$ sudo ufw allow from 192.168.143.33 to any port 3306,4567,4568,4444 proto tcp
$ sudo ufw allow from 192.168.143.33 to any port 3306,4567,4568,4444 proto udp

doing

ip addr show eth0
7: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether f2:3c:91:49:7f:0a brd ff:ff:ff:ff:ff:ff
    inet 172.104.129.63/24 brd 172.104.129.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet 192.168.143.33/17 scope global eth0:1
       valid_lft forever preferred_lft forever
    inet 192.168.143.81/17 scope global secondary eth0:2
       valid_lft forever preferred_lft forever
    inet6 2a01:7e01::f03c:91ff:fe49:7f0a/64 scope global mngtmpaddr dynamic
       valid_lft 2591984sec preferred_lft 604784sec
    inet6 fe80::f03c:91ff:fe49:7f0a/64 scope link
       valid_lft forever preferred_lft forever

where 172.104.129.63 is the public IP and the others one, the private IPs


texts: Unable to restore TDE enabled database when MAXTRANSFERSIZE and CHECKSUM is used

texts:
Update: @AmitBanerjee - Senior Program Manager for the Microsoft SQL Server Product Group confirmed that MS will look into the issue as it is a defect.

Has anyone encountered issue restoring backups taken on SQL Server 2016 with TDE enabled and using MAXTRANSFERSIZE > 65536 (in my case, I have chosen 65537 so that I can compress TDE database) and CHECKSUM ?

Below is a repro:

--- create database
create database test_restore
go
-- create table
create table test_kin (fname char(10))
go
-- Enable TDE

use master
GO
CREATE CERTIFICATE test_restore WITH SUBJECT = 'test_restore_cert'
GO
SELECT name, pvt_key_encryption_type_desc, * FROM sys.certificates WHERE name = 'test_restore'
GO
use test_restore
go
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE test_restore
GO
alter database test_restore set encryption ON

Take full copy only backup .. do it twice ..

backup database test_restore
to disk = 'D:\temporary-short-term\test_restore_KIN_test_restore_1.bak' -- change as per your location !!
with init, stats =10  -- overwrite ..using INIT !!
, maxtransfersize = 65537
, compression
,CHECKSUM

Now do a verifyonly ...

restore verifyonly from disk = 'D:\temporary-short-term\test_restore_KIN_test_restore_1.bak'

Error Message :

  Msg 3241, Level 16, State 40, Line 11
  The media family on device 'D:\temporary-short-term\test_restore_KIN_test_restore_1.bak' is incorrectly formed. SQL Server cannot process this media family.
  Msg 3013, Level 16, State 1, Line 11
  VERIFY DATABASE is terminating abnormally.

Results (1 = ON, 0 = OFF) with different combinations :

+-------------------------+-------------+----------+--------+
| MAXTRANSFERSIZE (65537) | COMPRESSION | CHECKSUM | RESULT |
+-------------------------+-------------+----------+--------+
|                       1 |           1 |        1 | FAIL   |
|                       1 |           1 |        0 | PASS   |
|                       1 |           0 |        1 | FAIL   |
|                       0 |           0 |        0 | PASS   |
|                       0 |           1 |        1 | PASS   |
|                       0 |           1 |        0 | PASS   |
+-------------------------+-------------+----------+--------+

The issue happens on:

  Microsoft SQL Server 2016 (RTM-CU1) (KB3164674) - 13.0.2149.0 (X64)   Jul 11 2016 22:05:22   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: )


texts: Connecting to an SQL server instance without using square brackets to avoid backslash

texts:
In our company we have an old piece of software which was written by our previous I.T manager but was never finished.

The program connects to SQL server but we want it to point to a newer SQL server we have recently configured.

The old instance name (on the current server) is simply server1.

Our new instance name (on the new server) is server2\someinstance.

Changing the programs app.cfg file to point to server server2\someinstance isn't a problem until a certain stage where it joins to another database on the same server. The program then throws the below error:

  Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '\'.

If we run the same query in SSMS, we get the same error but to get around this we can put square brackets around the name and then the query completes successfully, e.g [server2\someinstance].

The problem I have is I cannot edit his source code and when attempting to change the DB server in the app.cfg to [server2\someinstance] it won't connect to the instance at all.

The only way I see around this is to reinstall SQL server on the new server, without having any slash at all, e.g server2.

I thought I'd ask the question here in case anyone can offer any other suggestions. I've looked into SQL aliases but am not sure this is what I want (plus when trying it would never connect using the alias name anyway, but that's probably a separate issue).

Thanks.


texts: Providing an ad-hoc report facility for business users in SQL Server 2008 R2

texts:
We are using SQL Server 2008 R2. We need to provide the business users an interactive data exploration facility which they can drag and drop the attributes(columns) they want to create their own ad-hoc report.

I know in SQL Server 2012 it can be done using Power View and Sharepoint, but what about SQL Server 2008 R2?


texts: What are each of the separate mongodb-org packages for ubuntu?

texts:
I am installing mongodb on a local ubuntu vm and I'm using the official Mongo supported packages per the official instructions. However, I don't know what each of these packages contain. I know this is a merely informational question, but I've googled and not found the answer.

mongodb-org
mongodb-org-server
mongodb-org-shell
mongodb-org-mongos
mongodb-org-tools

What do each of these packages provide? Thanks!


texts:
We have an AWS-r3.large system. With 1.5TB data volume dedicated to Mongo. We are still using 2.6.3 (yes, upgrade pending for a while now).

The data size is only 550GB. The volume that we have, has 1500 dedicated IOPS. But some how once the IOPS reach 700-800 the Mongo performance, reduces drastically.  No clue why are the other IOPS not getting used. Nothing in MMS too. Even if I have a system where I write script that continuously queries on the largest collection in a loop, then too the IOPS go as high as 1200. My largest collection has 150 million documents. Could that be a reason?

Any help would be really appreciated. Lemme know, if anybody requires any more data.


texts: Querying True Physical Database File Sizes

texts:
On querying database file sizes...

1) Most if not all of these answers do not always match the physical file size shown in file explorer:

https://stackoverflow.com/questions/5945360

The accepted answer reports:

vs.

2) This post does seem to match physical file size shown in file explorer:

Is there a query that will report the physical size without relying on dm_os_performance_counters?  Are the first set of queries ignoring free space?

Actual size reported by File Explorer:

Why does Tempdb show a discrepency in both the data and log file sizes as shown?

Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)


texts: Property PopulationStatus is not available for FullTextCatalog... How to fix?

texts:
I've created a new catalog (case insensitive, make default catalog), when I try to right click > properties to add tables I get this error:

  Property PopulationStatus is not available for FullTextCatalog '[Search]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

Executing this code I found I get NULL

SELECT FULLTEXTCATALOGPROPERTY('Search', 'Populatestatus')

When I right click on tables the Full-text index is grayed out...

What am I missing? How can I fix this?


texts: how to clean huge Dbwarden QueryHistory table

texts:
dbwarden is Comprehensive database monitoring solution for SQL Server 2005+ which i am using.

i found that dba_LongRunningQueries – Runs every 5 minutes. This will run usp_LongRunningQueries to determine if any queries on a database are running too long. It also stores data into the QueryHistory table.

what should we do when QueryHistory table is getting very large?. i just want to have the latest logs and old data to be removed by some job.

also please note that i clean up 2 GB after running following query, but it does not seems the correct way to do it.

DELETE [dbWarden].[dbo].[QueryHistory] WHERE DateStamp < '2014-5-28'


texts: Oracle 12c Database swapping after set up huge pages

texts:
I need to setup a Oracle Database 12c with 64GB RAM on Linux RedHet. I decided to use huge pages. Something must have went wrong. Altertlog:

  WARNING: Heavy swapping observed on system in last 5 mins. pct of
  memory swapped in [0.42%] pct of memory swapped out [1.82%]. Please
  make sure there is no memory pressure and the SGA and PGA are
  configured correctly. Look at DBRM trace file for more details.

Supported system pagesize(s):   PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
                               4K          Configured           10                    8192010          NONE
                            2048K          15946             16001                    0                NONE

Seems like the Database is not using the huge pages? What could be the issue?

Update:

 grep Huge /proc/meminfo
AnonHugePages:     40960 kB
HugePages_Total:   16001
HugePages_Free:    16001
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB


texts: Associate a piece of data with a session

texts:
I think I want to associate a piece of data with each session.

The association must have the following properties:

The data being associated is provided either as a part of connection string (if that is possible at all) or as a parameter for a stored procedure which then must be the first SP called within the session.
The data must be easily available for any stored procedures the session then calls, without having this piece of data passed as a parameter.
This point is essential for the idea: obviously, I could add a parameter into each of my stored procedures and oblige the software to pass the piece of data each time any stored proc is called, but that is exactly what I want to avoid.
The data must automatically expire and be invalid as soon as the session disconnects. No actions from the client must be required to mark the data as expired. If a session disconnects unexpectedly, that still must render its piece of data invalid.

I've considered several ways of doing it, but didn't like any:

Local temporary tables.
Pick a table name (based on a freshly created GUID to avoid any clashes). Have a stored procedure that creates temporary table with this name and inserts a row with the data passed as a parameter. Then all other procs can reference the #table and get the data. The idea is that each session will have it's own #table, while the name of the #table remains the same.
Won't work. The temporary table created in the stored procedure will be dropped upon exiting the procedure. Making the user create the #table directly, without calling a SP, is not an option.
A regular table where data is looked up by @SPID.
Won't work. This requires deleting the data manually upon disconnect.
APP_NAME().
Will probably work, but that's not really app name, and that will not be easily extendable.

Are there better ways of associating data with a session?


texts: Why selecting rowversion using outer join never returns null

texts:
Using SQL Server 2014.

Given table with rowversion column, other table to join with and a select like this:

 CREATE TABLE dbo.FooTable(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [RowVersion] [rowversion] NULL,
 CONSTRAINT [PK_FooTable] PRIMARY KEY CLUSTERED
(
    [Id] ASC
))

 CREATE TABLE dbo.BarTable(
    [Id] [int] IDENTITY(1,1) NOT NULL
 CONSTRAINT [PK_BarTable] PRIMARY KEY CLUSTERED
(
    [Id] ASC
))

Insert into BarTable default values
GO

SELECT *
  FROM FooTable ft
  FULL OUTER JOIN BarTable bt on ft.Id = Bt.Id

Result of select is:

Id  RowVersion  Id
NULL    0x      1

Where there was no match with BarTable the RowVersion column is not null, but 0x.

This confuses Dapper, which we use to deserialize result on app server, into thinking, it should construct an object for FooTable part of row - an invalid one, containing just an empty byte array.

Is there any reason for this rather unexpected behaviour?

Are there any smarter solutions than translating the value back to null using CASE in the sql statement or dealing with it in application code?


texts: Connecting mysql on server A/ to webapp on server B/

texts:
I am connecting mysql on server A/ to a webapp located on server B/. Both servers are Ubuntu servers.

For security purposes, is there any way on server B to avoid showing the full path / IP address of server A/ to connect to as this is where the mysql database is?

Thanks!


texts: Error in table 'sys.sysbinobjs' on master database

texts:
We have a SQL server DatabaseIntegrityCheck job to check the SYSTEM_DATABASES, and it shows an error message below in the logs now:

  Msg 2570, Level 16, State 2, Line 1 Page (1:354), slot 28 in object ID
  58, index ID 1, partition ID 281474980511744, alloc unit ID
  281474980511744 (type "In-row data"). Column "name" value is out of
  range for data type "nvarchar". Update column to a legal value.
  CHECKDB found 0 allocation errors and 1 consistency errors in table
  'sys.sysbinobjs' (object ID 58). CHECKDB found 0 allocation errors and
  1 consistency errors in database 'master'.

Recommendation is to install SP4 - already done and problem still exists.

Any thoughts please other than rebuild master - no backup available unfortunately.


texts:
I tried to turn on MYSQLI_REPORT_ALL | MYSQLI_REPORT_STRICT and now MySQL tells me:

  No index used in query/prepared statement...

in some of my app's queries.

Is it really best practice to index every WHERE column I'll possibly use so that MySQL will not complain? Seems a bit overkill to me as there are probably some columns where I'll only use the WHEREclause occasionally.


texts:
EDIT I updated my question with a SQL Fiddle Sample http://sqlfiddle.com/#!15/8d88b/1

I'm currently making a report from a database records but I don't know how my query should look like, first of all I have 2 tables. Application Forms, and a table for Login Hours of each user

forms
->id
->agent_id
->SomeInfo
->created_at

loginhours
->id
->user_id
->loginhours (decimal)
->created_at

And I have report with the following columns

UserID, TotalLoginHours, TotalApplication, Application Per Hour (aph), Revenue Per Hour (rph)

So right now I have this query

SELECT a.agent_id, SUM(b.loginhours) as TotalLoginHours, COUNT(a.id) as TotalApplication, SUM(b.loginhours) / COUNT(a.id) as ApplicationPerHour,   (SUM(b.loginhours) / COUNT(a.id)) * 1.75 as RPH
FROM forms a
INNER JOIN loginhours b ON a.agent_id = b.user_id WHERE a.created_at = '2015-07-17'
GROUP BY a.agent_id

Note that user_id and agent_id is the same.

I want to get the result based on the date selected, example 2015-07-17 I got results but my problem is the loginhours is being SUM based on the number of application for each user. So for example the user1 has 2 records on forms table and his loginhours from 2015-07-17 is 2 then in my result the loginhours becomes 4 which is wrong, I think it is on my GROUP BY statement. Can you help me how to properly query this?
Thanks


texts: Adding wiredtiger nodes to a non-wiredtiger replica set

texts:
Situation

I have 3 nodes on a Replica Set (P+S+S). Mongodb version 2.6.9. I would like to migrate all the nodes to 3.0.4 wiredtiger.

When I need to do that without downtime, I do the following workflow:

setup a new server with 3.0.4
add the node to the replica set
remove one of the old nodes
go to step 1

Problem is that with my set of data, adding a node with the "rs.addnode" tactic just doesn't work. The process takes forever and starts the initial syncronization again and again. To walkaround this, I normally do a cold backup from one of the old nodes to the new one. That takes the data and the local database, the node joins the replica set without initial syncronization.

Problem is that I cannot do that because the file system is not the same. If I can put one with wiredtiger, I can do the cold backup trick with the other ones.

Attempts I made with no luck:

reduce size of my database
reduce the Oplog size
try with a dump backup / restore
rs.add

I know there is a way to add a node with a setting --fastsync. But to do that, I need to have a copy from the primary, and the files are not the same. Not sure if that will do the trick with dump backup / restore. I would know if when the node takes the new member as a valid secondary, I can restart the node without the fastsync or I should put that setting forever.

This is my mongod.log when it fails with the initial sync:

I INDEX    [rsSync]         building index using bulk method
I INDEX    [rsSync] build index done.  scanned 3 total records. 0 secs
I REPL     [rsSync] initial sync cloning db: config
I REPL     [rsSync] initial sync data copy, starting syncup
I REPL     [rsSync] oplog sync 1 of 3
I NETWORK  [rsSync] Socket recv() timeout  x.x.x.x:x
I NETWORK  [rsSync] SocketException: remote: x.x.x.x:x error: 9001 socket exception [RECV_TIMEOUT] server [x.x.x.x:x]
I NETWORK  [rsSync] DBClientCursor::init call() failed
E REPL     [rsSync] 10276 DBClientBase::findN: transport error: x:x ns: local.oplog.rs query: { query: {}, orderby: { $natural: -1 } }
E REPL     [rsSync] initial sync attempt failed, 9 attempts remaining

SOLUTION (see comments)

configure ulimits:

sudo nano /etc/security/limits.conf
# add at the bottom
mongod       soft    nproc   64000
mongod       hard    nproc   64000
mongod       soft    nofile  64000
mongod       hard    nofile  64000
mongod       soft    fsize   unlimited
mongod       hard    fsize   unlimited
mongod       soft    cpu     unlimited
mongod       hard    cpu     unlimited
mongod       soft    as      unlimited
mongod       hard    as      unlimited

configure keep alive

sudo nano /etc/sysctl.conf
# add at the bottom
net.ipv4.tcp_keepalive_time = 120

Note: I did that on the new member and on the existing ones


texts: How to safely add column to replicated mysql database

texts:
We have 2 databases, one is a replication used for read only, with around a million records, we want to add a new column to the master db, to one of the tables.

Do I just go on MySql workbench and add the column, or do I need to shutdown the database first?

SELECT COUNT(1) FROM mytable;

===>489170

    CREATE TABLE `mytable` (
  `column1` int(11) NOT NULL auto_increment,
  `column2` char(1) NOT NULL default '',
  `column3` varchar(32) NOT NULL default '',
  `column4` varchar(32) NOT NULL default '',
  `column5` varchar(32) NOT NULL default '',
  `column6` datetime NOT NULL default '0000-00-00 00:00:00',
  `column7` varchar(96) NOT NULL default '',
  `column8` int(11) NOT NULL default '0',
  `column9` varchar(32) NOT NULL default '',
  `column10` tinyint(1) NOT NULL default '1',
  `column11` varchar(32) default NULL,
  `column12` tinyint(1) NOT NULL default '1',
  `column13` varchar(32) default NULL,
  `column14` tinyint(1) NOT NULL default '1',
  `column15` tinyint(1) NOT NULL default '1',
  `column16` varchar(32) default NULL,
  `column17` varchar(32) default NULL,
  `column18` tinyint(1) NOT NULL default '1',
  `column19` varchar(40) NOT NULL default '',
  `column20` char(1) default '1',
  `column21` varchar(11) default NULL,
  `column22` tinyint(3) unsigned NOT NULL default '0',
  `column23` int(11) NOT NULL default '1',
  `column24` int(10) default '0',
  `column25` text,
  `column26` varchar(64) default NULL,
  `column27` varchar(64) default NULL,
  `column28` varchar(64) NOT NULL default '',
  `column29` tinyint(2) default NULL,
  `column30` varchar(12) NOT NULL default '',
  `column31` varchar(12) NOT NULL default '',
  `column32` varchar(32) NOT NULL default '',
  `column33` varchar(32) default NULL,
  `column34` int(10) default NULL,
  `column35` int(10) default NULL,
  `column36` tinyint(1) default '0',
  `column37` int(1) NOT NULL default '0',
  `column38` int(11) NOT NULL,
  `column39` varchar(64) default NULL,
  PRIMARY KEY  (`column1`),
  KEY `key1` (`column7`),
  KEY `key2` (`column21`),
  KEY `key3` (`column4`,`column5`),
  KEY `key4` (`column8`),
  KEY `key5` (`column24`),
  KEY `key6` (`column38`)
) ENGINE=MyISAM AUTO_INCREMENT=489814 DEFAULT CHARSET=latin1


texts: How can we create a table from stored procedure?

texts:
CREATE DEFINER=`root`@`localhost`
PROCEDURE `create_tb_one`(In tableName varchar(255))
BEGIN

    CREATE table tableName(
        ID INT      NOT NULL,
        SNAME VARCHAR(100)  NOT NULL,
        SSTATE VARCHAR(100) NOT NULL,
        TIME_STAMP VARCHAR(45) NOT NULL,
        constraint FK_SENSOR
            foreign key (SNAME)
            references sensorInfo(SName)
            on delete set null
    );END

Now, when I call this procedure by passing a String, it is not creating a table and showing this following error code:

  call create_tb_one(first) Error Code: 1054. Unknown column 'first' in 'field list'    0.00024 sec


texts:
At our organisation we are migrating to SQL Server from visual foxpro dbf and hence had no maintenance plans and have no DBA. We have gone through our backup plans and Index maintenance is only index rebuild as and when required.

I wanna propose a new plan for index maintenance but wanna be sure if am wrong about it.its like Daily Index reorganizing and index rebuild as and when required (weekly or as late as monthly).

As I understand reorganize can be done online and will reduce accumulation of fragments.

Does clustered index duplicate the table or it just reorganizes the table?

Also would like to know if there are any other aspects of sql server maintenance that i need to look-up.

Sorry for so many question, just curious about many things.


texts: Referencing a single table from all tables in the database

texts:
At current, I have around 130 tables in my database, anticipating a growth of circa 200 tables in total.

Now I have several tables (well, most of them, actually) that contain properties like CreatedBy / ModifiedBy / DeletedBy, which take an nvarchar(MAX) as their data type and contain the name of the user.

This makes versioning a lot harder, because the name of the user might change during the lifetime of the application.

Now I've thought about referencing each and every *By-Column via a Foreign Key to the users table. However this would result in 600 foreign keys on a single table (expecting the 200 tables with 3 foreign keys per table, if not more).

Are there any drawbacks? What's the best practice?

As a side note (not relevant for the dba aspect of my question, I presume): I'm using Entity Framework in C#, only referencing a single part of it in my code (because I don't want and don't need 600 collections in my User class).


texts:
I need to setup a sql audit to capture all queries running on a server from any unauthorized locations, and one of the columns that is required is Row Count.

In sql trace, this is possible. But I can't seem to find corresponding column in extended events. I looked into these 2 DMVs:

select * from sys.dm_xe_object_columns
where object_name = 'sql_statement_completed'

select pkg.name as PackageName, obj.name as ActionName
from sys.dm_xe_packages pkg
inner join sys.dm_xe_objects obj on pkg.guid = obj.package_guid
where obj.object_type = 'action'
order by 1, 2

But all they have is reads/writes, but not actual number of rows.

Is it possible to get this info in SQL 2008 or SQL 2008 R2 ?


texts: Recovery model from Full to simple and Shrink transact log result on poor performance

texts:
I'm new mantaining databases, I'm more like programmer but you know in some jobs you must be multitools because theres no specific dba.

My boss told me to change the recovery model from Full to simple because there was no specific routine to back up log and store the backups and the result was a 1.5 gb transaction log with no other initial backups (or maybe missing, the guy how do the project left) besides they do not care about the possible dataloss on daily basis. So I followed the command.

We schedule a windows and I do the job on SSMS and do the following steps:

Make a full copy to database with -> Tasks Copy Databases.
Make a full and transact log copy on Database.
Go to database options and change Recovery model from full to simple.
Shrink the transaction log ->Tasks > Shrink > Files

In the Shrink file dialog the currently allocate space field was the 1.5gb but were about 1 gb the Avaliable free space so I left 600MB to the log file

Reboot...

So after this the information was able to query but the performances is bad as hell a stored procedure that take 0.6 sec to execute before the changes now take up to 12 secs to run.

Moreover I repeat this in database copies (from the copy that I do before changes) and realized that instead of shrink the space in disk it's growing exactly the space trying to shrink.

After this I try to see if was fragmentation and indeed there about the 40% of tables about up to 50% in avg_fragmentation_in_percent

SELECT
    OBJECT_NAME(object_id) AS tblName
    ,dbo.index_name(object_id, index_id) AS ixName
    ,avg_fragmentation_in_percent
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)
WHERE
    avg_fragmentation_in_percent > 20
    AND index_type_desc IN('CLUSTERED INDEX', 'NONCLUSTERED INDEX')
ORDER BY
    avg_fragmentation_in_percent DESC

So I try to reorganize the index on the tables. Some go down to 20% but must still on ~ 45%

Finnaly I realised that the CPU was overheating more and more until reach 100% of CPU usage.

Here I used this query to make the what querys are cosuming more resources

SELECT TOP 50
     [Average CPU used] = total_worker_time / qs.execution_count,
     [Total CPU used] = total_worker_time,
     [Execution count] = qs.execution_count,
     [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
     (CASE WHEN qs.statement_end_offset = -1
        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
      ELSE qs.statement_end_offset END -
    qs.statement_start_offset)/2)
    ,[Parent Query] = qt.text
    ,DatabaseName = DB_NAME(qt.dbid)
FROM
    sys.dm_exec_query_stats qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY
    [Average CPU used] DESC;

Can anyone tell me what's happend?


texts: Cross Database Chaining vs. Trustworthy Option

texts:
Recently, we had a stored proc request that required access across multiple databases. Of course this is not uncommon to have but in our case, earlier we used service accounts and this service account had read/write permissions to all the databases.

But now we changed all permissions to windows sign on AD group and the users in the AD group do not have all permissions to all the databases.

Example:

AD group UG1 has 10 users who are users on Database A.
Stored procedure PROCA resides in Database A but has selects on the tables that belongs to Database B and Database C.
Execute permissions have been granted to the stored proc PROCA and the AD group UG1 as users (with no other permissions) has been created on Database B and Database C.
The database owners of the DatabaseA, DatabaseB and DatabaseC are under login XXX.

As we all know, such permissions across databases can be accomplished by

Option 1 : Switching on Database Cross chains ON all the databases, or
Option 2 : Giving the AD group UG1  required individual object permissions on the databases (B and C)  or
Option 3 : Using Trustworthy db options on DatabaseA and changing the stored proc PROCA to include execute as “dbo” user so that the permissions can impersonate.
Option 4: Using certficates

My question is what would be the best solution with respect to security and keeping it simple and safe?

Please note that none of the developers are database owners of any of the databases and elevated permissions are only assigned to DBA’s and hence the chances of someone hacking or SQL injecting or impersonating as sysadmin is “none”.

But again, I would like to know what your take would be in such scenarios? I prefer option 3.


texts: PostgreSQL: Corrupt primary key, inconsistent table

texts:
While recovering from a cloud failure, I found that some tables on a PostgreSQL database are behaving strangely. These tables are indexed using a primary key, but a pg_dump yielded duplicate fields, failing a pg_restore on a backup server.
I have tried to REINDEX:
REINDEX INDEX rank_details_pkey;
ERROR:  could not create unique index "rank_details_pkey"
DETAIL:  Table contains duplicated values.

The index is defined as:
<table info here>
Indexes:
    "rank_details_pkey" PRIMARY KEY, btree (user_id)

And, oddly,
SELECT user_id, COUNT(*) FROM <table name> GROUP BY 1 HAVING COUNT(*) > 1;
 user_id | count
---------+-------
(0 rows)

To conclude - I have duplicate values in my table which can not be found or cleared.
Any ideas how to fix this? This is a production server, so all fixes should be done without affecting service.


texts:
I have a table where I store visits (id, datetime, country, browser). I want to count how many visits are from which country.

I use this query:

SELECT country, COUNT(*) as hits
FROM hits_table
WHERE timestamp >= CURDATE()
GROUP BY country

But the dataset is huge. So if I add LIMIT 10 to only get the top 10 countries will it improve the performance?

SELECT country, COUNT(*) as hits
FROM hits_table
WHERE timestamp >= CURDATE()
GROUP BY country LIMIT 10

Also I don't need exact results I only need some approximate results. So can't I make the query so it skips every 2nd result. Will it improve the performance? Are there any other methods?


texts: triggers or else between 2 postgres databases hosted on a different datacenter

texts:
I'm trying to create a trigger inside of a Postgres RDS database hosted in Frankfurt that we will call "Frankfurt". That trigger will poll tables in a distant Postgres RDS database hosted in Seoul, let's call that database "Seoul".

The Seoul database will never have delete nor update. It will only have insert.

The aim of that trigger is for each insert into the Seoul database to copy the rows from Seoul that are not in Frankfurt, to the Frankfurt database.

CREATE OR REPLACE FUNCTION my_dml_function() RETURNS TRIGGER AS $employees$
    BEGIN
        --
        --
        IF (TG_OP = 'INSERT') THEN
            perform dblink_connect('dbname=database_seoul user=postgres password=ExtraPassword');
            INSERT INTO all_employees
            SELECT *
            FROM dblink('foreign_server',$RDS$
                SELECT *
                FROM employees
                WHERE employee_id NOT IN  (SELECT employee_id FROM all_employees)
            $RDS$) AS t1;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$emp_audit$ LANGUAGE plpgsql;

CREATE TRIGGER my_dml_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
    FOR EACH ROW EXECUTE PROCEDURE my_dml_function();

I'm not sure if it is the best way.


texts: Why does mongodb virtual memory get so large

texts:
I have just compiled MongoDB in my linuxOS and not insert any data into DB.

The virtual memory is getting very large, about 40G.

  ps aux|grep mongod

mongodb  176574  0.0  0.0 423784 39008 pts/308  Sl   09:00   0:00  mongod

Why does the virtual memory so huge, I haven't insert any data in it?


texts: How to get efficient range index scans with multiple columns with different sort order?

texts:
Let's say we have this table with a million rows:

CREATE TABLE stuff(
    foo integer,
    bar integer
);

INSERT INTO stuff(foo, bar) SELECT
    (random()*1000000) :: integer,
    (random()*1000000) :: integer
FROM generate_series(1, 1000000);

CREATE INDEX index1 ON stuff(foo ASC, bar ASC);
CREATE INDEX index2 ON stuff(foo ASC, bar DESC);

VACUUM ANALYZE stuff;

This table backs an API endpoint where the user can query the data specifying a custom sort order, possibly on multiple columns, and fetch the data in pages of 100. When fetching a page, the user gets a cursor that encodes the necessary data to get the next page.

When fetching rows with ORDER BY foo ASC, bar ASC, the first query would be:

postgres=# EXPLAIN ANALYZE SELECT * FROM stuff ORDER BY foo ASC, bar ASC LIMIT 100;
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..4.79 rows=100 width=8) (actual time=0.104..3.611 rows=100 loops=1)
   ->  Index Only Scan using index1 on stuff  (cost=0.42..43679.92 rows=1000000 width=8) (actual time=0.068..1.297 rows=100 loops=1)
         Heap Fetches: 100
 Planning Time: 0.150 ms
 Execution Time: 4.915 ms
(5 rows)

and a query with a cursor pointing to the middle of the table would be:

postgres=# EXPLAIN ANALYZE SELECT * FROM stuff WHERE (foo, bar) > (543210, 1234) ORDER BY foo ASC, bar ASC LIMIT 100;
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..7.14 rows=100 width=8) (actual time=0.142..3.625 rows=100 loops=1)
   ->  Index Only Scan using index1 on stuff  (cost=0.42..30730.05 rows=457498 width=8) (actual time=0.115..1.279 rows=100 loops=1)
         Index Cond: (ROW(foo, bar) > ROW(543210, 1234))
         Heap Fetches: 100
 Planning Time: 0.325 ms
 Execution Time: 4.857 ms
(6 rows)

This query is very fast because the optimizer knows how to use the WHERE clause to "jump" into the middle of the index, and only fetch 100 rows from there.

Now, if the user wants to sort by foo ASC, bar DESC, I can't think of any equivalent for WHERE (foo, bar) > (543210, 1234). We have to write the query like this:

postgres=# EXPLAIN ANALYZE SELECT * FROM stuff WHERE foo > 543210 OR (foo = 543210 AND bar < 1234) ORDER BY foo ASC, bar DESC LIMIT 100;
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..11.61 rows=100 width=8) (actual time=226.836..230.294 rows=100 loops=1)
   ->  Index Only Scan using index2 on stuff  (cost=0.42..51179.92 rows=457498 width=8) (actual time=226.813..227.959 rows=100 loops=1)
         Filter: ((foo > 543210) OR ((foo = 543210) AND (bar < 1234)))
         Rows Removed by Filter: 543630
         Heap Fetches: 543730
 Planning Time: 0.275 ms
 Execution Time: 231.521 ms
(7 rows)

Now the query is too slow! Apparently, postgres isn't smart enough to figure out that it can "jump" into the middle of the index, and does a full index scan instead.

Is there any way to make the second query fast, like the first one? It seems to me that it should be theoretically possible.


texts:
I've got a table of data like so:

Process_Id  Stage_Id    Step_Id
P1          P1.5        P1.5.1
P1          P1.5        P1.5.10
P1          P1.5        P1.5.11
P1          P1.5        P1.5.12
P1          P1.5        P1.5.13
P1          P1.5        P1.5.14
P1          P1.5        P1.5.15

And as you can see it's not sorting naturally on the last number of the Step (as you or I would imagine it would be sorted in any other situation). This is of course because it's stored as a string.

Is it possible to have these numbers sort naturally? Perhaps ordering by a 'split' pseudo-column consisting of the split of Step_Id by '.'?

Thanks.


texts:
I have 3 instance of oracle database which myservice, myservice1, myservice2.
1st database service name is MYSERVICE; it's working ok

But I can't start myservice1 and myservice2. How can I start these service?
I am using oracle linux.

 tnsping myservice1

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 05-MAY-2020 08:27:48

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mydatabase.orcl.net)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = myservice1)))
    OK (0 msec)
    [orcl@mydatabase ~]$
    [orcl@mydatabase ~]$ tnsping myservice2

    TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 05-MAY-2020 08:28:02

    Copyright (c) 1997, 2014, Oracle.  All rights reserved.

    Used parameter files:

    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mydatabase.orcl.net)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = myservice2)))
    OK (0 msec)

When I trying to connect the db with SERVICE NAME: myservice1 using SQL developer it says:

An error was encountered performing the requested operation:

Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

Vendor code 12514

select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
myservice          OPEN


texts: How to repair, or drop/create a corrupted table in mysql?

texts:
Situation: I have a MySQL database with 104 tables, running locally on XAMPP on windows 7. All the tables have InnoDB engine. XAMPP MySQL is running as windows service. As front end I have PHP (CodeIgniter).

Problem: Last night as usual I stopped working on the project and shutdown the windows normally. Today in the morning I got error table MY_TALBE does not exists in the browser. show tables on console shows the table name, but desc MY_TABLE and select * from MY_TABLE says unknown table. I tried create table MY_TABLE(...) but I had error as table MY_TABLE already exists, so I tried drop table MY_TABLE but it said Unknown table MY_TABLE! I tried mysqlcheck.exe for repair and check, they failed both, but after that, show tables does not show the table name any longer. I need to mention I did not change/move/delete/rename any of mysql files.

Question: Is it possible to recover/repair MY_TABLE, or at least how to drop and create it again (without dropping the database and re-crteating it again)?


texts: how to check whether Oracle database is accessed or not?

texts:
I am an Oracle DBA. Application owner is asking me when was the last time Oracle database was accessed? How do I find information about the DB usage timeline? I mean, I want to check whether it is being used by the application or not?

Unfortunately the application owner has no idea whether her application is used by the users or not.

From DBA's side which data dictionary should I look into? Can ASH help? if yes, then which specific section should I focus on?


texts:
In one project I save statuses of processing in MySQL table:

`id`  `name`  `email`  `has_apples`  `has_bananas`
 1     Bob     a@b.c    1             0
 2     Mike    c@d.e    0             1
 3     John             1             1

name+email pair should be unique.
For now everything works fine. But the table grows rapidly, and currently it contains about 100m rows.
According to the development needs I have to add, lets say, has_oranges column.

Altering such a big table is painful, requires downtime and it doesn't look like that the MySQL is a right tool for this problem.

Even more, the number of such type of data will grow in the future.
I'm looking for a long-term scalable solution, which will allow:

fast search by id
fast search by name
unique control of the name+email pair
column scalable
storage scalable

I'm not familiar with NoSQL solutions, but it seems that maybe that's the way to go.


texts: How to do Statistics in Partial Indexes of PostgreSQL efficiently?

texts:
This answer motivated think again my following tables but I am still uncertain how to store variables/results from data analysis for quick selection in post-processing.
If stored, I think it is well done with partial indexes (B-tree) because only a subset of data is required in my application described here.
Assume you have three orthonormal tables: measurements, events and headers where all have some static (size of events in bytes, etc) columns but wonder if the storage of some numbers from data analysis is beneficial or not.

Example of my table schema measurements without names:

SERIAL                     | NOT NULL
INTEGER                    | NOT NULL
INTEGER                    | NOT NULL
TIMESTAMP WITH TIME ZONE   | DEFAULT CURRENT_TIMESTAMP

where I am worried about increasing the schema size by INTEGER fields because of my need in statistics.
I do not want to store in the main tables variables which are used rarely.
I do not want cause my indexes not being used as described in the book PostgreSQL Up & Running and this article Why is my index not being indexed?

Possible sources

Gordon Linoff and his blog here

When should the static/dynamic statistics stored in tables?
How can static/dynamic statistics be stored efficiently in partial indexes of PostgreSQL?


texts: Execute SQL query after execute TRUNCATE in a table

texts:
I have a table called fos_user_user where I have a default record "always". Since I'm testing all the time and sometimes I need to clean that table I'm asking if there is any way to run the following query any time I run the TRUNCATE command on the table:

INSERT INTO `fos_user_user` (`id`, `username`, `username_canonical`, `email`, `email_canonical`, `enabled`, `salt`, `password`, `last_login`, `locked`, `expired`, `expires_at`, `confirmation_token`, `password_requested_at`, `roles`, `credentials_expired`, `credentials_expire_at`, `created_at`, `updated_at`, `date_of_birth`, `firstname`, `lastname`, `website`, `biography`, `gender`, `locale`, `timezone`, `phone`, `facebook_uid`, `facebook_name`, `facebook_data`, `twitter_uid`, `twitter_name`, `twitter_data`, `gplus_uid`, `gplus_name`, `gplus_data`, `token`, `two_step_code`) VALUES
(1, 'admin', 'admin', 'admin@local.com', 'admin@local.com', 1, 'qd3bxv0cg34s0ow0owsso848k8g4g8s', 'w0PbwCKqCJmEzr7VewCJIq0M8t7+35wS4Tfbzj2LaRKNg9wNv8ic/1TY+y5BTJXoftMENobutsizDluVKM/R/Q==', '2014-02-25 02:36:56', 0, 0, NULL, NULL, NULL, 'a:1:{i:0;s:10:"ROLE_ADMIN";}', 0, NULL, '2014-02-13 02:36:09', '2014-02-25 02:36:56', NULL, NULL, NULL, NULL, NULL, 'u', NULL, NULL, NULL, NULL, NULL, 'null', NULL, NULL, 'null', NULL, NULL, 'null', NULL, NULL);

Any advice?


texts: Need Help maintaining multiple databases for a multi-tenant application?

texts:
i need some information about multiple databases for a multi-tenant application ?

I am working on a project in which we are maintaining separate database for every company?

The Companies Database contain customer Data.

There is a common Database to store common information across these databases. Example Customer ID,Customer Contact Number and Customer Email etc.

A New Database is created for every new company.

If one customer belongs to company1 and same customer is added in company 2
then we need to give same customer id from company 1 to that customer across all companies.

There are many such scenarios similar to the above example.

Can any one tell me how to manage multiple databases(companies) and same customers across all companies.


texts:
I have two databases db-1 (primary) and db-2 secondary
now I moved all files location on DB-2 (system databases and default location in DB settings) to be :Q instead of :D
the problem is when I create new availability group on primary replica DB-1 I find that files created in old location (:D) on secondary replica !
how can I solve this issue so that when I create new AG files will be created automatically in the new drive :Q not :D on the secondary replica DB-2
Thanks in advanced


texts:
I am following the guide - http://www.sqlchicken.com/2009/07/how-to-create-a-server-side-trace-with-sql-profiler/ which is working until I reach Step 8

  Now to schedule your newly created script. In SSMS connect to the server you want to trace on. Go to your SQL Server Agent and expand it so you can see the Jobs folder. Right-click the Jobs folder and select New Job.

I cannot not find SQL Server Agent. Where can I make this SQL Server Agent visible?  I have given the current user all the access permission.


texts: Reindex or Analyze Tables on PostgreSQL after import

texts:
I used PostgreSQL 9.5 on a server and I ended up migrating the data to a new server using PosgreSQL 12.
Should I perform a REINDEX for all tables or just ANALYZE for all tables?


texts: SQL Server - Is there a way to query the specific Size and Space available settings for each Database, including system databases on an instance?

texts:
I am trying to query the two default database properties as seen in the picture below for all databases on an instance, including system databases.
I've used various scripts such as the following, but I'm not sure exactly where data for those two fields are stored.
SELECT
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID() -- for current db
GROUP BY database_id

Thank you for your help.


texts: computed columns, index, clustered index and covering index?

texts:
We all know the we can only have one clustered view per table, cool. But apparently you don't have values of computed columns on leaf even when using a clustered index and they are computed each time.
So, my first question is, is that correct that what I just said?

Also, if you create a clustered index, okay let me just quote..
"If you create a clustered index on a computed column, the values of the column will
physically exist in the corresponding table rows, because leaf pages of the clustered
index contain data rows."
Yeah, okay, but why create clustered index on computed column only? I thought if you create a clustered index on any column the data exists on leaf?
So, my Ques 2 is would the value of computed column exists (persist) in table if only we create a clustered index on that particular column? (question 2 continues) If that is indeed the case, what if we have more than one computed columns? surely, we can't create more than one clustered index, than how do we make their value persistent?

Now moving on, what about the covering indices? First, we don't really need covering (or we can't really create) covering index on while creating clustered index because all the data would already be on leaf, right? So, there's no point of including any other column?
 So, thirdly I'd like to ask, am I correct about what I just said in my previous statement?

Now if we are creating a covering index, (non-clustered right?) and we include the computed column(s), then would they we persisted? would they be "physically stored" or calculated again? (and that was my 4th ques. by the way.)

Finally, (5th ques) if we can achieve the same (make computed columns persist) by all these ways (or some of these ways, I don't really know which one, that's why I am asking the question in first place!) then, what would be the best method to adopt and why?

ps : I am sorry if for asking 5 question in just once, but they are all terribly related, and I didn't want to ask same thing over and over again in 5 different questions. I hope you understand! :)

EDIT : well in response to comments, right now I am using SQL server 2008, but if you could provide how the situation would differ if using anything else (my sql or whatever) that would be added benefit for not only me but also for community. :)


texts: Backup Oracle DB using TSM server version 6.3.0

texts:
I'm new to Oracle and TSM server. I installed Oracle11gR2 and TSM server version 6.3.0 (linux Centos 6.4 OS).
I did the configuration and everything works fine. so far I arrive to run the following script using rman:

run {
allocate channel Channel1 type 'SBT_TAPE' parms
'ENV=(DSMO_NODE=TDPOTSMUSR,DSMO_AVG_SIZE=1,TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/
tdpo.opt,DSMI_LOG=/opt/tivoli/tsm/client/api/bin64/logs)';
backup
        full
        format 'b_%u_%p_%c'
        database;
release channel Channel1 ;
}

I need to know how to backup theOracle DB to a storagepool name ORADisk? I need to understand a bit what does channel mean in a script and what does it actually do.
really appreciate your hel and reply. thank you


texts:
Suppose for simplicity that I have a table t with columns id and value. I can find the ids that have more than one distinct value using a query such as

SELECT id, COUNT(DISTINCT(value)) AS count
FROM t
GROUP BY id
HAVING count > 1

Is there a more efficient way to do this? Can it be changed so that the count for each id stops as soon as more than one distinct value is found for it?


texts: "Measure Type Dimensions" in "Accumulating Snapshot" fact table

texts:
I have an accumulating snapshot fact table that tracks the entry and exit of containers in a terminal.
The containers can enter and exit in 3 different ways, so I thought to create a specific dimension table that lists these 3 possible ways (train, vessel or truck).
Then I read this article which basically says that this technique is wrong, but I can't understand why.
First article:

Sometimes when a fact table has a long list of facts that is sparsely populated in any individual row, it is tempting to create a measure
type dimension that collapses the fact table row down to a single
generic fact identiﬁed by the measure type dimension. We generally do
not recommend this approach. Although it removes all the empty fact
columns, it multiplies the size of the fact table by the average
number of occupied columns in each row, and it makes intra-column
computations much more difficult. This technique is acceptable when
the number of potential facts is extreme (in the hundreds), but less
than a handful would be applicable to any given fact table row.

I understand that if a "Measure Type Dimension" is implemented for a transaction fact table it can create problems like this other article says, but I can't see any downside if used for an accumulating snapshot fact.
Second article: (some downsides of implementing a "Measure Type Dimension")

[...] If we go with a "Measure Type Dimension" we will lose this analytic ability. If one measure is not compatible with the other
measures, we can’t add them up.
[...] The more number of passes our SQL need to run to produce a report, the slower the report.
[...] On the BI tool, if you don’t put the measure type filter, you are risking the user getting “rubbish information”. From usability
point of view, this design is a rubbish.

Response to Mark Storey-Smith's answer
Very nice approach, i would have never thought about that.
Another thing: every entry and exit of a vehicle that brings container into the terminal has a unique ID which gives me other informations like: expected arrival of the vehicle, actual arrive, if it's a vessel the dock, if it's a truck the tollbooth and many other informations...
These are 3 different fact tables and they must be linked somehow to the container fact table.
I thought that the ID of the voyage is a degenerate dimension, so it would directly go into the container fact table. So, my doubt is: should i add 6 different fields in the container fact table (vessel_voyage_in_key, vessel_voyage_out_key, train_voyage_in_key, train_voyage_out_key, truck_voyage_in_key, truck_voyage_out_key) or just 2 other fields (voyage_in, voyage_out) that dynamically links to the various voyage tables?
I hope my doubt is clear, thank you.


texts:
I am busy making a backup of all Agent Jobs by scripting them all to file.`How would i rewrite the below to use BCP instead

        BEGIN
        SET @EchoCommand = 'ECHO ' + @EchoField + ' >> ' + @Path
        + @JobName
        + REPLACE(REPLACE(CONVERT(VARCHAR(200), GETDATE()), ' ',
        ''), ':', '') + '.sql'

Something similar to the below

        SET @EchoCommand = 'bcp
        "exec['+DB_NAME()+'].dbo.CreateBackUpFromJob' +
        @JobName + '" queryout "\\ServerName\SQL_Backups\' + @JobName +
        '.sql" -c -UTF8 -T -SLOCALSERVER'


texts:
Recently I started to see intermittent errors in log files of my web application.
Raising from different places of the application the error is always about failed connection to DB which is located on another server:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

----

System.ComponentModel.Win32Exception (0x80004005): Access is denied

OR

The underlying provider failed on Open.

----

System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) ---> System.ComponentModel.Win32Exception (0x80004005): Access is denied
   ....
   at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
ClientConnectionId:00000000-0000-0000-0000-000000000000

I ran an infinite ping to DB server and another ping to google on my APP server. More than 24h have passed but logs do not show any network issues.

Any ideas why it happens?

I'm using Microsoft SQL Server Standard Edition (64-bit) 10.50.4000.0 on Win Server 2008 R2

Update:

1 Database Autoclose is set to OFF

2 I have checked event logs of both APP and DB server and didn't find anything related to sqlserver or network errors. Event log however contains Schannel errors, but from I googled it is not related.


texts: MSSQL Mirai Botnet, cleaned but connects again using ANOYNMOUS LOGON

texts:
well this problem is making me go nuts
my server was hacked with the Mirai Botnet, we cleaned the system, Disabled the Server Agent completely and the system is stable and clean BUT...

if i re-open that sql server PC to the internet ( port 1433 ), it gets reinfected..

i experimented with all the logins and found out that if i leave the "BUILTIN\Administrators" login, that's what makes the botnet reinfects it.

the client which infects me is called "Microl Office", it originates from various assumingly hacked PCs, and the strange thing is that it authenticates as "NT AUTHORITY\ANONYMOUS LOGON"...

i deleted the model and msdb and recopied the ones in template in order to clean the server, still the same thing happens if i Leave the Builtin administrators.. soo i guess that botnet added the ANONYMOUS LOGON to the administrators group ?

btw i reinstalled another instance and different version and still the same thing happens so it's not an SQL issue i think.

  Login succeeded for user 'NT AUTHORITY\ANONYMOUS LOGON'. Connection made using Windows authentication. [CLIENT: 138.0.224.232]

Also, if i use the BUILTIN\Users instead, it doesn't login and it fails with an error "Token-based server access validation failed with an infrastructure error"
So i THINK once the server was severely infected, the malware somehow added the anonymous logon to the builtin administrators group

So can anyone tell me how can i SEE all the added users to the administrators group ? ( and yes the group has only my user as the Admin user when accessed from computer management )

can anyone please suggest anything in that matter ?  ( Windows Server 2003 R2 and SQL SERVER Enterpise 2005 + 2008 sp1 )

options I tried:

set LSA blockanonymous to 1
set allowanoynomous to 1
disable SID translation
check the administrators group from computer management


texts: How to make a trigger work after a certain UPDATE

texts:
I would like to know if it's possible to create a trigger on Oracle that is invoked only when a certain UPDATE is done. For example:

UPDATE colors
SET tastes_good = y
WHERE color_name = 'Apple';

should call the trigger, while

UPDATE colors
SET tastes_good = n
WHERE color_name = 'Apple';

should not.


texts: Does a COMMIT work within an anonymous plgpsql function in PostgreSQL 9.5?

texts:
I am importing a large number of large files into a number of tables to be partitioned using loops within an anonymous plpgsql code block $do$.

$do$
BEGIN
    FOR yyyy in 2012..2016 THEN
        EXECUTE $$COPY table$$||yyyy||$$ FROM 'E:\data\file$$||yyyy||$$.csv DELIMITER ',' CSV;$$;
    END LOOP;
END;
$do$ LANGUAGE plpgsql

This entire process should take about 15 hours and I'm hoping that all the imports won't be rolled back if there's an import error at some point.

IIRC COMMIT doesn't work within stored functions bc the entire function is treated as a single transaction.

From the documentation for $do$

  The code block is treated as though it were the body of a function with no parameters, returning void. It is parsed and executed a single time.

I'm assuming this means that the entire $do$ is one transaction, and so commits within the block won't work. Am I correct?


texts: Mysql join query taking too much time even though indexes are implemented

texts:
Hi I have a mysql query

SELECT
    `locationMapping`.`keyId` AS `caseId`,
        `locationMapping`.`id`
FROM
    `locationMapping`
INNER JOIN `locationSubtype` ON (`locationMapping`.`locationSubtypeId` = `locationSubtype`.`id`
    AND `locationMapping`.`locationTypeId` = `locationSubtype`.`locationTypeId`
    AND `locationMapping`.`govtId` = `locationSubtype`.`govtId`)
    AND ((`locationSubtype`.`govtId` = '90')
    AND (`locationMapping`.`keyTable` = 'xercase'))
WHERE
    locationMapping.govtId = 90

GROUP BY `keyId`

Taking 8 seconds to execute.

Create table command for locationMapping is:
    show create table locationMapping;

CREATE TABLE `locationMapping` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`govtId` int(11) NOT NULL DEFAULT '0',
`locationSubtypeId` int(11) NOT NULL DEFAULT '0',
`keyId` int(11) NOT NULL DEFAULT '0',
`keyTable` varchar(100) NOT NULL DEFAULT '',
`locationTypeId` int(11) DEFAULT '0',
`gisCode` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `locationmapping_instance` (`keyId`,`keyTable`),
KEY `locationmapping_subtype` (`locationSubtypeId`),
KEY `locationmapping_govtid` (`govtId`)
) ENGINE=InnoDB AUTO_INCREMENT=3994710 DEFAULT CHARSET=latin1

Create table command of locationSubType is

CREATE TABLE `locationSubtype` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`govtId` int(11) NOT NULL DEFAULT '0',
`locationTypeId` int(11) NOT NULL DEFAULT '0',
`name` varchar(100) DEFAULT NULL,
`description` varchar(100) DEFAULT NULL,
`code` varchar(30) DEFAULT NULL,
`gisCode` varchar(100) DEFAULT NULL,
`sortorder` smallint(6) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `locationsubtype_locationtype` (`locationTypeId`),
KEY `locationsubtype_govtid` (`govtId`),
KEY `locationsubtype_giscode` (`gisCode`)
) ENGINE=InnoDB AUTO_INCREMENT=4319 DEFAULT CHARSET=latin1

Explain statement for query is:

id,   select_type, table,             type,  possible_keys,                                                             key,                      key_len, ref,                              rows, Extra
'1',  'SIMPLE',    'locationSubtype', 'ref', 'PRIMARY,locationsubtype_locationtype,locationsubtype_govtid',             'locationsubtype_govtid', '4',     'const',                          '756', 'Using index condition; Using temporary; Using filesort'
'1', 'SIMPLE',     'locationMapping', 'ref', 'locationmapping_instance,locationmapping_subtype,locationmapping_govtid', 'locationmapping_subtype','4',     'comcate_dev6x.locationSubtype.id','333', 'Using where'


texts: How to optimize and find minimum from the two sql query, by writing it as single query?

texts:
I have two queries. How can I write them into a single query for efficient performance? I need the minimum value of the result of $sql1 and $sql2. Is it possible?

$sql1="SELECT sum(pv_value) as pv FROM `{$wpdb->prefix}wpmlm_pv_meta` as M WHERE pv_meta like 'left_%' and user_id={$user_id}";
$sql2="SELECT sum(pv_value) as pv FROM `{$wpdb->prefix}wpmlm_pv_meta` as M WHERE pv_meta like 'right_%' and user_id={$user_id}";


texts:
We have a pull transaction replication.Publisher runs MSSQL 2016.
Distributer runs on separate server with MSSQL 2016.
Is it possible to define Always On over publisher and another new server, so that publisher will become the primary in AG.
Most important that subscribers will keep going.
We are ok with disabling replication for several hours, but we would like to keep it without recreating.
(I could only find articles on defining replication above Always ON, which is the contrary of my request)


texts:
I would like to calculate the average days between several order dates from a table called Orders. For each CustomerID, what is the average days between orders. The sample table is as below (img):

CREATE TABLE #Orders(CustomerID int, OrderDate datetime);

INSERT #Orders(CustomerID, OrderDate) VALUES
    (100,'20170114'),(100,'20170123'),(100,'20170129'),
    (101,'20170202'),(101,'20170212');

I tried this query:

SELECT  CustomerID, AVG(OrderDate - PriorDate)
FROM (SELECT CustomerID, OrderDate
           , LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) as PriorDate
      FROM #Orders where CustomerID = 100)

However, this yields:

  Msg 102, Level 15, State 1
  Incorrect syntax near ')'.


texts: Postgres partition avoiding redundant column

texts:
Say I have a table, say project_data with lots of rows where the data may be grouped by a project_id integer column, and partition it onto one table for each project_id value.
Do I then need the project_id column in every partition table, when the value is implicit?
It seems that having an extra column in project_data_5 project_id integer not null default 5 would be redundant as it will never have a value other than 5 in that table.
This is for postgresql 10.


texts:
How can I find out if a user's password has expired in MySQL?

The MySQL version I'm using is 8.0.16.


texts: I have a database with multiple (10) schemas. I need a user to have access to two of them. If I grant access to the database and then GRANT SELECT to 2 schemas, is access to the other schemas DENY-ed by implication? Or must I grant permissions to those schemas and then DENY SELECT to the rest of them?


texts: Confused on how a value gets added with the DEFAULT constraint

texts:
A recent definition I've read about Default Constraints:

DEFAULT is specified for a column to assign a column a specific value if no value is specified when a row is added.

I believe I've seen code written without the DEFAULT constraint specified and a NULL value was assigned to the field.

If I add a column to a table, do I have specify the DEFAULT keyword for the default value to be assigned?  Or will NULL be assigned since I didn't delineate DEFAULT?


texts:
I have a list of string values for example ('a', 'b', 'c', 'd', 'e'). Now I have a database lets say (T1) having two columns for example (user and permission). Every user has some permissions. For example.
|user   | permission|
| user1 | a         |
| user2 | a,b,f     |
| user3 | b,c       |
| user4 | e,d       |
| user5 | f,g       |

I want to write a query which can retrieve the users who are using those permissions. What I tried until now is below.
select * from T1 where permissions IN ('a', 'b', 'c', 'd', 'e')
After running this I only get user1 nothing else. But the answer should be user2 and others also because they are using permission a also.
Note: This is just an example. The strings I have are more than 25 and the database has more than 100 values.


texts: Which databases are backed up by "mysqldump --all-databases"?

texts:
Which databases are included in the dump generated by mysqldump --all-databases?

By my experience, that should be all the user-created databases, plus the mysql database. The database information_schema is backed up only if explicitly mentioned, and performance_schema is never backed up. Is this correct?


texts: How to interpret the information spit out by innochecksum?

texts:
MySQL is using up a lot of the disk space. Running du -sh on the data directory shows a humongous ibdata1 file (around 1.7 terabytes).

I found a very helpful article online and ran a modified version of the innochecksum program on the file as it instructed. Here's the (partial) output of the program:

  0      bad checksum

  96334276        FIL_PAGE_INDEX 3173387 FIL_PAGE_UNDO_LOG

  19932  FIL_PAGE_INODE

  45494  FIL_PAGE_IBUF_FREE_LIST

  11692831        FIL_PAGE_TYPE_ALLOCATED

  0      FIL_PAGE_IBUF_BITMAP

  0      FIL_PAGE_TYPE_SYS

  0      FIL_PAGE_TYPE_TRX_SYS

  0      FIL_PAGE_TYPE_FSP_HDR

  0      FIL_PAGE_TYPE_XDES

  0      FIL_PAGE_TYPE_BLOB

  0      FIL_PAGE_TYPE_ZBLOB

  0      other

  49572348        max index_id

  undo type: 331 insert, 1761203 update, 1411853 other

  undo state: 0 active, 297 cached, 0 to_free, 1475925 to_purge, 0
  prepared, 1697165 other

I don't have much knowledge of the INNODB internals, nor I could find any documentation that will help me interpret this information.

Could anyone help me understand what these information signify? Particularly, I want to know what is taking up so much space in the ibdata1 file.


texts: Which Postgresql Replication Solution to Use? (Asynchronous Multimaster / Bucardo?)

texts:
First, I'm not a DBA, so pardon me if any of this question seems "off."

I've written a peer-to-peer multiplayer game (the client) which connects to one of multiple servers for match making.

Currently, there is only one server (a linode, let's call it Server 1) which runs the game's custom matchmaking process and PostgreSQL 8.4 (I will be upgrading this to 9.1, 9.2 or 9.3 if necessary).

The matchmaking process uses libpq asynchronously for all SQL statements. Statements are not too complex, so load balancing is not an issue.

I plan to add more linodes (call them Servers 2, 3, 4, etc.) that run the matchmaking process and PostreSQL as necessary. The challenge is that I want high-availability for all clients. If server 1 is unreachable, then server 2 can be used instead, with access to all the same data.

The original plan was to have all servers connect to Server 1's database and asynchronously send SQL statements via libpq. The problem there is that if Server 1 is ever temporarily offline or unreachable, then every other server will fail.

The "simplest" solution I can imagine is for each of the servers to completely mirror the database. If Server 1 is down, clients can connect to Server 2 which reads and writes to its own database, replicating any changes to Servers 3 and 4 immediately, and Server 1 once it comes back online.

In this fashion, every server would hold an entire "mirrored" copy of the database.

After reading through the introductory sections of PostgreSQL 9.3's documentation on replication, it seems like way to implement this solution would be asynchronous multi-master replication. (Is Bucardo the only choice here?)

The thing I'm worried about with asynchronous replication is SQL inserts. When a new client plays for the first time, a player database entry is created. If Servers 2, 3, and 4 are online and Server 1 is offline, will there be any issues with 1, 3 or 4 if 2 inserts a new player row? (Imagine 1 coming back online and immediately trying to insert another player row.)

Is asynchronous multi-master the right way to go for the above mentioned scenario?

Or, is there a simpler or easier solution that I am overlooking? Perhaps one that doesn't require middleware, but just uses PostgreSQL 9.3's built in replication?