texts: Security Risks with having Test Stored Procedures that have SQL Injection Vulnerability

texts:
I noticed that some of the stored procedures used in our integration tests for data generation have SQL injection vulnerabilities. These procedures are taking into string parameters to build a SQL string and executing it. However, these procedures are only ever used for testing.

At first, I thought it was a big issue. However, if these procedures aren't accessible to the public, maybe it's not a problem. But I could be missing something. Are there any potential issues with this setup and my assumptions?

Some possible issues I could think of:

1) Test database somehow has access to a non-test database

2) Accidental procedure copying to a non-test database

3) People might see the procedure and think it's good practice

Though 1 & 2 still wouldn't be accessible.


texts:
I have a Netezza database which gets a daily feed of account information.

I’m trying to write a query which will only return accounts when they experience a price change and advise what the price change was. I.E. in the example below it would return account 100 having a price change of +10 on 2020-02-02.

Date                  Account             Price
2020-02-01       100                    200
2020-02-01       101                    200
2020-02-01       102                    200
2020-02-02       100                    210
2020-02-02       101                    200
2020-02-02       102                    200


texts:
How can I filter results in a SQL query?

Let’s say I have Products A-Z, and each product has a monthly observation for X Months, but for most products the value in the observation stays the same for that particular product, but I need to find the products that have observations with a new value, the moment the value in the observation changes. Also, you can have a double to quadruple entry per month if the value doesn’t only change, but coexists, and I need to find every instance that happens for a product.

Example in pic:


texts: Audit select queries against specified tables by specified users

texts:
I have a requirement to audit all SELECT queries (by access) against specified tables by specified users.

As far as I can tell, this can't be done with normal auditing; it only supports auditing all queries by specified users or all queries agains specified tables (by any user). This is a problem because there are system accounts that would generate too much audit and needs to be excluded from audit.

This leaves fine grained auditing, where it is possible to do this by specifying appropriate predicate for audit_condition parameter in dbms_fga.add_policy. However, FGA has unfortunate effect of writing duplicate rows in audit trail when parallel query is used; not desirable when the goal is to minimize audit information.

Currently, I'm planning to go with FGA and frequently remove the duplicate rows. Is there any better way to implement this auditing strategy?


texts: 1 minus percentage returns negative number

texts:
I have a sidewalk_inspection table:

create table user.sidewalk_insp
(
    insp_id int,
    deficiency_length number(10,2),
    sidewalk_length number(10,2)
);

insert into user.sidewalk_insp (insp_id, deficiency_length, sidewalk_length) values ( 1, 0.0, 10.0);
insert into user.sidewalk_insp (insp_id, deficiency_length, sidewalk_length) values ( 2, 2.5, 10.0);
insert into user.sidewalk_insp (insp_id, deficiency_length, sidewalk_length) values ( 3, 5.0, 10.0);
insert into user.sidewalk_insp (insp_id, deficiency_length, sidewalk_length) values ( 3, 7.5, 10.0);
insert into user.sidewalk_insp (insp_id, deficiency_length, sidewalk_length) values ( 4, 11.0, 10.0);

select * from user.sidewalk_insp;

   INSP_ID DEFICIENCY_LENGTH SIDEWALK_LENGTH
---------- ----------------- ---------------
         1                 0              10
         2               2.5              10
         3                 5              10
         4               7.5              10
         5                11              10

I can calculate the deficiency_percent by dividing deficiency_length by sidewalk_length:

select
    insp_id,
    deficiency_length,
    sidewalk_length,
    deficiency_length / sidewalk_length as deficiency_percent
from
    user.sidewalk_insp;

   INSP_ID DEFICIENCY_LENGTH SIDEWALK_LENGTH DEFICIENCY_PERCENT
---------- ----------------- --------------- ------------------
         1                 0              10                  0
         2               2.5              10                .25
         3                 5              10                 .5
         4               7.5              10                .75
         5                11              10                1.1

Rather than calculate the deficiency_percent (a.k.a. 'percent bad'), I want to calculate the condition_percent (a.k.a. 'percent good'). I can do this by subtracting the percentage from 1:

select
    insp_id,
    deficiency_length,
    sidewalk_length,
    1 - (deficiency_length / sidewalk_length) as condition_percent
from
    user.sidewalk_insp;

   INSP_ID DEFICIENCY_LENGTH SIDEWALK_LENGTH  CONDITION_PERCENT
---------- ----------------- --------------- ------------------
         1                 0              10                  1
         2               2.5              10                .75
         3                 5              10                 .5
         4               7.5              10                .25
         5                11              10               -0.1

Unfortunately, due to a quirk of the data (user input errors), the deficiency_length is occasionally longer than the sidewalk_length. While, in theory this is impossible (a sidewalk's deficiency should never be longer than it's length), in reality, it is something that happens that is beyond my control (the application is not customizable).

The result is a negative number in the condition_percent column (see insp_id #5) which is not a valid percentage. Instead of outputting a negative number, I think the best way to handle negative percentages would be to output a value of 0 (0%).

What is the simplest way to do this?


texts: SSIS Design consideration with object variables

texts:
I have an SSIS package for SQL Server 2008 R2. In the package I am required to work with an Access database (one to many) and iterate through it running validation checks with the data. The validation checks are simply running particular queries against the Access database and if records are returned, the validation failed for that check.

My requirements:

I am pulling the list of Access DBs to validate from a SQL Server
database.
I iterate through the list doing the validation checks (about 36 tot
The records returned will vary on column count and data type.
I need to take only those records returned and generate a single
email.
A title or heading needs to accompany each recordset in the email in
order to associate the records with the validation check

EDIT: One additional requirement that might affect design: this package could will be called at minimum every 15 minutes.

Example:

Validation 1 errors
Column1 Column2
cv1     cv1
cv2     cv2

What I have setup so far:

Data Flow pulls the list of Access files to validate into an object
variable
Foreach loop iterates through each record (using ADO Enumerator), mapping each column to a variable Script task to validate the file is accessible (failures > Execute SQL Task updates database to skip the file)
Data Flow is setup for each validation in order to query the Access file(s)
Each Data flow is a OLE DB Source to a Records Destination I have each validation populating a object variable

I am populating an object variable for each validation. Since the column count can vary I did not see an option to send it to anything else; at least with my knowledge of SSIS.

What I have setup seemed good when I started but... I am now sitting here thinking the best way to check all these object variables for records and put in an email. I came across Andy Leonard's blog post on access the DataSet in an object variable. Then another blog post on retrieving data from the Recordset Destination.

Anyone have suggestion(s) on doing this differently or how to build that email with all these object variables?

My first thought would be to create a second foreach loop to iteration through each object variable. Grabbing each recordset and appending a string variable to contain each table, formatted as HTML or something to make it look pretty in the email.


texts: How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

texts:
I want to migrate a fairly simple, internal, database driven application from SQLite3 to PostgreSQL 9.3 and tighten the permissions in the DB as I go.

The application currently consists of a command to update the data; and one to query it. Naturally, I'll also need to maintain the database in other ways (create new tables, views, triggers, etc).

While this application will be the only one hosted on the server at first, I'd prefer to bake in the assumption that it might be hosted on a server with other databases in the future, rather than having to scramble later if that becomes necessary in the future.

I would think that these would be a fairly common set of requirements, but I'm having trouble finding a simple tutorial explaining how to set up a new database in PostgreSQL, with this sort of user/privilege separation. The references go on at length about groups, users, roles, databases,
schemas and domain; but I find them confusing.

Here's what I've tried so far (from within psql as 'postgres'):

CREATE DATABASE hostdb;
REVOKE ALL ON DATABASE hostdb FROM public;
\connect hostdb
CREATE SCHEMA hostdb;
CREATE USER hostdb_admin WITH PASSWORD 'youwish';
CREATE USER hostdb_mgr   WITH PASSWORD 'youwish2';
CREATE USER hostdb_usr WITH PASSWORD 'youwish3';

GRANT ALL PRIVILEGES ON DATABASE hostdb TO hostdb_admin;
GRANT CONNECT ON DATABASE hostdb TO hostdb_mgr, hostdb_usr;
ALTER DEFAULT PRIVILEGES IN SCHEMA hostdb GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO hostdb_mgr;
ALTER DEFAULT PRIVILEGES IN SCHEMA hostdb GRANT SELECT ON TABLES TO hostdb_usr;

But I'm not getting the intended semantics. I want to have it configured so only the hostdb_admin can create (and drop and alter) tables; the hostdb_mgr can read, insert, update and delete on all tables by default; and the hostdb_usr can only read all tables (and views).

When I tried this I found that I was able to create tables in hostdb as any of these users; but, for each user, I could only read or modify tables created by that user - unless I use an explicit GRANT.

I'm guessing that there's something missing between CREATE DATABASE and CREATE SCHEMA, something to apply the SCHEMA to the DATABASE?

(As things get more advanced I'll also have questions to apply similar restrictions on TRIGGERS, stored procedures, VIEWS and perhaps other objects).

Where can I find a decent guide, tutorial or video series on this?


texts: Setup an aerospike high-availbilty cluster?

texts:
Are there any guide to set up an aerospike HA? I had search google but there was no possible result in return.

Look like Aerospike is not really popular in common.

P/s: Lab purpose so there only need 2-3 node/host


texts: SQL Server 2008 R2: Problems after computer name change

texts:
I'm having a confusing problem after changing the computer name of a remote server hosting a local SQL Server instance.

Basically, a remote server was moved from one site to another. In order to facilitate this, I backed up and restored the old database to a new database name, clearing out the data so it could be used as a fresh database for the client software. I also changed the computer name, as we always do so to identify each server by its site number.

The database can be connected to by the client software just fine, and I can log in directly to SQL Server fine. However, one of my SQL Server Agent jobs fails, with an error in the event log:

SQL Server Scheduled Job 'Nightly Reset' (0x4F76FDFFF6DFFE4EA0DE4A70252AD3BD) - Status: Failed - Invoked on: 2012-02-07 08:10:05 - Message: The job failed. Unable to determine if the owner (Site-19\Admin) of job Nightly Reset has server access (reason: Could not obtain information about Windows NT group/user 'Site-19\Admin', error code 0x534. [SQLSTATE 42000] (Error 15404)).

Now, 'Site-19' is the old computer name, which has been changed, and the server has been reset. I connect manually using 'Site-28', the new site number, and it shows me as being connected to the SQL Server with Site-28\Admin. However, when I look at the properties of the Agent job, it shows the owner as being Site-19\Admin, and when I attempt to browse for users to change it, Site-28\Admin doesn't show up as an option, only Site-19\Admin. If I script out a new job from this one and manually change the owner to 'Site-28\Admin', the new job is created with the owner 'Site-19\Admin'.

Looking in sys.servers (or via sp_helpserver), I only have one entry: the current computer name. However, SELECT @@SERVERNAME returns the original development machine name (two name changes ago).

In short, I can't run this important SQL Server Agent job because it belongs to a user that no longer exists, and I can't figure out how to change it or create it as the correct user.


texts: How important are transaction log VLFs for performance?

texts: How important are VLFs when considering database performance? What would describe an optimal situation for VLFs?


texts: What are database statistics, and how can I benefit from them?

texts: I've heard mention of statistics that SQL Server keeps by default. What are they tracking, and how can I use this information to improve my database?


texts: Find dates when exactly records changed for user

texts:
I need to find the dates when exactly the status really changed.

Order should be analysed from Start to end period:

Start Period : 11-10-2014
End Period : 21-10-2014

Data:

ID, Name, Effective_date, Status
1   A       21-10-2014      OFF
2   A       20-10-2014      OFF
3   A       19-10-2014      On
4   A       18-10-2014      On
5   A       17-10-2014      On
6   A       16-10-2014      OFF
7   A       15-10-2014      On
8   A       14-10-2014      On
9   A       13-10-2014      OFF
10  A       12-10-2014      OFF
11  A       11-10-2014      OFF

I am using SQL Server 2000.

Expected output:

ID, Name, Effective_date, Status

2   A       20-10-2014      OFF
5   A       17-10-2014      On
6   A       16-10-2014      OFF
8   A       14-10-2014      On
11  A       11-10-2014      OFF


texts: How can I make an index covering a ts_vector expression and also an integer?

texts:
I am using the ruby pg_search gem, where code like this: Food.search("orange") produces a query like this.
SELECT "foods".*
FROM "foods"
INNER JOIN
  (SELECT "foods"."id" AS pg_search_id,
          (ts_rank((to_tsvector('simple', coalesce("foods"."name"::text, ''))), (to_tsquery('simple', ''' ' || 'orange' || ' ''' || ':*')), 0)) AS rank
   FROM "foods"
   WHERE ((to_tsvector('simple', coalesce("foods"."name"::text, ''))) @@ (to_tsquery('simple', ''' ' || 'orange' || ' ''' || ':*')))) AS pg_search_d4392ced9dff0647fed4ed ON "foods"."id" = pg_search_d4392ced9dff0647fed4ed.pg_search_id
ORDER BY pg_search_d4392ced9dff0647fed4ed.rank DESC,
         "foods"."id" ASC;

Ive created this index, which seems to work quite well
CREATE INDEX concurrently foods_gin ON foods USING gin
(to_tsvector('simple', coalesce("foods"."name"::text, '')));

I have another column, category. there are 9 categories. For a given search as shown above for name, I want to show results from category 9 first, and then all the other categories (order doesn't matter).
ideally the results could be paginated, so a perfect query/index combo would be great.
is this possible to do, and/or will it be a truly gigantic index?
p.s. suggestions for a better name for the question are welcome


texts: Access control on Azure SQL Data warehouse

texts:
Access Control setup on Tables inside dbo schema.

We are Denying select permission on some tables inside dbo schema as these tables have some critical data.

Issue: Even when Select query fails on the object. A user having create view grants can create a view on the same object.

Sample queries:

Select * from dbo.TABLE1 -- Fails as user does not have access.

Create view USER.VIEW1 as (Select * from dbo.TABLE1) -- this goes through.

How can I restrict this from happening?


texts: How to replace the string which has passed in procedure while using EXECUTE IMMEDIATE in Oracle?

texts:
Can some one help me how to use the string inside a string? I need to use something without bound variables because I am passing all the input parameters from a procedure which I am using in my real requirement, so the values are fixed.

create or replace procedure Sample( rowsize number )
is
    v_result varchar2(100);
    v_str varchar2(200);
begin
    v_str := 'Select rtrim(legl_ent_name) || '' | '' ||rtrim(legl_ent_prd_grp) as heirarchy from legl_ent_regn where rownum < rowsize';
    EXECUTE IMMEDIATE v_str ;
    DBMS_OUTPUT.PUT_LINE(rowsize);

end;
/

I'm getting the error below when I run the procedure.

BEGIN
dbms_output.enable();
Sample(2);
END;

ERROR at line 1:
ORA-00904: "ROWSIZE": invalid identifier
ORA-06512: at "RATABASE.SAMPLE", line 7
ORA-06512: at line 3


texts: How do I find an invalid utf8 character "somewhere" in my MySQL/trac database

texts:
I have an installation of trac, which uses MySQL. It has been upgraded so many times, moved servers etc. and chances are that the MySQL character sets were not always set correctly or consistently over the years. Currently all of them are utf8.

When attempting to upgrade the data using "trac-admin wiki update", I'm getting an error message that a byte ("UnicodeDecodeError: 'utf8' codec can't decode byte 0xa0 in position 4274: invalid start byte") is not valid unicode. Unfortunately trac-admin gives me no hint where (table/row/column) to look for that byte sequence, or what I could do to fix it.

My question is not about trac/trac-admin, however, it's about the database. How would you go about finding, "somewhere" in the database, the offending bytes, and replacing them with something that is at least valid utf8. I have attempted to mysqldump the database and to re-import it, but MySQL gives no indication that anything might be wrong. The invalid bytes get re-imported.

Ideas?


texts: Is there any advantage to putting some columns in another table, or should I just put them in the same table?

texts:
I have a SQLite database with about 50 tables in it. The largest table contains location data and it has 6,000,000 rows.
An example of some of the columns in this table are:
location_contrivedkey --(unique identifier for each location; 1, 2, 3, 4, 5, etc.)
location_coordinates
location_status --(is there a building in this location)
location_rating --(1-10 based on certain factors)

I now realize I need about 30 more specific rating type values for each location. For example:
location_rating_crime
location_rating_weather
location_rating_tourism
location_rating_income
location_rating_jobs

Each location will have a value in each of these columns. None will be blank.
Questions
Is there any reason to put them into another table and join on the location_contrivedkey, or should I just add them all to the current table?
I read somewhere about queries being slower the more columns you have in a table? Is that true?
Also, is there a 2,000 column limit?
Should I be concerned about this approach?


texts: Using GIN index for array and single value in the same key

texts:
We have table:
create table last_games(id int not null primary key, name text not null, json_value jsonb);

And filled with next values:
insert into last_games values
(1, 'Tournament1', '{"places": ["2","1"]}'::jsonb),
(2, 'Tournament2', '{"places": ["4","2"]}'::jsonb),
(3, 'Tournament3', '{"places": "2"}'::jsonb);

As you can see places is array in two rows and string in another one.
Then add GIN index.
create index idx_gin_places on "last_games" using GIN (("json_value" -> 'places'));

If you make query then
select * from last_games lg where lg.json_value -> 'places' ? '2';

you will get all 3 rows. But what about index? It's ok that part value of key is array and other is string?


texts:
Why is my query non-deterministic?

I have query that do the following:

select sum(float1*float2*coalesce(float3,1)) from table

When I run this query I get a deterministic result, but it seems not be the case when the query is run many times.

The last integer in the result differ between different runs.

All columns are (float,null)

Should a float truncation be the same for all runs?


texts: Performance Order By large table with Join

texts:
Hello I have a table with 206,902 records and I am struggling to improve my query:

SELECT TOP (50) [ID]
      ,[Title]
      ,[Modified]
      ,[Created]
  FROM [Data]
 Inner Join [Permission]
    On ([Data].[ID] = [Permission].[FichaID]
   AND ([Permission].[PermID] = 1 AND [Permission].[IsGroup] = 0
        OR [Permission].[PermID] IN (46, 50, 53) AND [Permission].[IsGroup] = 1
        ))
Group By [ID]
      ,[Title]
      ,[Modified]
      ,[Created]
   Order By [Created] desc

The query is running fast but using the tool SQLQueryStress with 50 iterations complement all time is 3 minutes.
If I remove the order by the query takes to run less than a second.
I have created an index on the table Data:

CREATE INDEX facervo_created ON [Data] ([Created] desc);
CREATE INDEX facervo_status ON [Data] ([_ModerationStatus]);
CREATE INDEX facervo_expiration ON [Data] ([Expiration]);
CREATE INDEX facervo_reserva ON [Data] ([Reserva]);

Index on the table Permission:

ADD CONSTRAINT perm_key PRIMARY KEY (FichaID, PermID, IsGroup)

Still not had great success in performance.
It is possible to execute this query faster?

EDIT

I removed all the index of the two tables, and created this new index:

CREATE NONCLUSTERED INDEX [ix_include1]
ON [Data]([Created] Desc)
INCLUDE ([ID]
        ,[Title]
        ,[Modified]);

Now this query taking 16 seconds, an evolution.
Is it possible to improve the most?


texts:
Why does this give me the same value, four 1?

USE [TSQL2012]
GO
IF OBJECT_ID('dbo.sqlsequence', 'SO') IS NOT NULL
    DROP SEQUENCE dbo.sqlsequence;
GO
CREATE SEQUENCE [dbo].[SQLSequence] AS INT
START WITH 1
MAXVALUE 8 CYCLE

SELECT NEXT VALUE FOR
        [dbo].[SQLSequence] AS [Seq1] ,
    NEXT VALUE FOR
        [dbo].[SQLSequence] AS [Seq2] ,
    NEXT VALUE FOR
        [dbo].[SQLSequence] AS [Seq3] ,
    NEXT VALUE FOR
        [dbo].[SQLSequence] AS [Seq4]

But this works fine as expected.  Gives me 1, 2, 3, 4

USE [TSQL2012]
GO
IF OBJECT_ID('dbo.sqlsequence', 'SO') IS NOT NULL
    DROP SEQUENCE dbo.sqlsequence;
GO
CREATE SEQUENCE [dbo].[SQLSequence] AS INT
START WITH 1
MAXVALUE 8 CYCLE

SELECT NEXT VALUE FOR
        [dbo].[SQLSequence] AS [Seq1]
SELECT          NEXT VALUE FOR
        [dbo].[SQLSequence] AS [Seq2]
SELECT          NEXT VALUE FOR
        [dbo].[SQLSequence] AS [Seq3]
SELECT           NEXT VALUE FOR
        [dbo].[SQLSequence] AS [Seq4]


texts: Best architecture solution for heavy data mining system, that can run on cloud

texts: We are currently hosting our MySQL database on a dedicated server with lots of memory and processors. We would like to push it to a different architecture that would allow us to scale it easily, specially on the part of computing. The service that we are currently testing analyses mobile data. We would like to be able to use cloud system to perform all the computations depending on the current need. Can you point us in a right direction in terms of system architecture. What kind of server scenario would be best for such tasks.


texts: Can't connect mongo shell to Atlas cluster

texts:
There is some issue coming while connect mongo shell to the atlas cluster. It is saying that No primary detected for set Cluster0-shard-0.

ashish@ashish-Not-Specified:~$ mongo "mongodb://cluster0-shard-00-00-pth8i.mongodb.net:27017,cluster0-shard-00-01-pth8i.mongodb.net:27017,cluster0-shard-00-02-pth8i.mongodb.net:27017/test?replicaSet=Cluster0-shard-0" --authenticationDatabase admin --ssl --username m001-student --password m001-mongodb-basics
    MongoDB shell version: 3.2.17
    connecting to: mongodb://cluster0-shard-00-00-pth8i.mongodb.net:27017,cluster0-shard-00-01-pth8i.mongodb.net:27017,cluster0-shard-00-02-pth8i.mongodb.net:27017/test?replicaSet=Cluster0-shard-0
    2017-10-25T22:12:00.759+0530 I NETWORK  [thread1] Starting new replica set monitor for Cluster0-shard-0/cluster0-shard-00-00-pth8i.mongodb.net:27017,cluster0-shard-00-01-pth8i.mongodb.net:27017,cluster0-shard-00-02-pth8i.mongodb.net:27017
    2017-10-25T22:12:00.759+0530 I NETWORK  [ReplicaSetMonitorWatcher] starting
    2017-10-25T22:12:09.080+0530 W NETWORK  [thread1] No primary detected for set Cluster0-shard-0
    2017-10-25T22:12:18.389+0530 I NETWORK  [ReplicaSetMonitorWatcher] Socket recv() timeout  34.204.152.215:27017
    2017-10-25T22:12:18.610+0530 W NETWORK  [thread1] No primary detected for set Cluster0-shard-0
    2017-10-25T22:12:18.610+0530 E QUERY    [thread1] Error: connect failed to replica set Cluster0-shard-0/cluster0-shard-00-00-pth8i.mongodb.net:27017,cluster0-shard-00-01-pth8i.mongodb.net:27017,cluster0-shard-00-02-pth8i.mongodb.net:27017 :
    connect@src/mongo/shell/mongo.js:223:14
    @(connect):1:6

    exception: connect failed


texts: SQL server suddenly backups are slow and async_io_completion shows suspended for long time

texts:
50GB database - full backup normally takes 14 (10 minutes backup, 4 minutes to compress).  Suddenly the backup execution time has been climbing upward to 3-4 hours.   Backup dest is a Network path.

To eliminate the network as an issue, used backup to 'NUL:' - same results 4 hrs.

During the full backup async_io_completion is showing "suspended" examples below:
(1161299ms)ASYNC_IO_COMPLETION  1pm / 22% complete
(2838894ms)ASYNC_IO_COMPLETION  2pm / 60% complete
(3969124ms)ASYNC_IO_COMPLETION  230pm / 77% complete
(4818588ms)ASYNC_IO_COMPLETION  245pm / 80% complete
(5816122ms)ASYNC_IO_COMPLETION  3pm / 86% complete
(6695511ms)ASYNC_IO_COMPLETION  315pm / 89% complete

checked VLF count is low (less than 100) /  Tried SQL native backup - same results / DBCC checkdb - no errors.

HERE IS THE KICKER....Took a full backup of this 50gb and created a new db on the same SQL instance (alongside orig db).  Executed a full backup on the newly created db and the backup time is back to normal 14 minutes.

Anyone has suggestions on why the newly copy of the same db would perform correctly?  Is there something SQL related that can be checked/verified to determine root cause?  Leaning towards SAN or VM issue, need proof.

Env info:
VM environment
Windows 2016 32GB
SQL Standard 2017 CU16


texts: I have a database that was created many SQL Server versions ago that has Full Text files. It is currently on a SQL 2012 server. How can I confirm that there is no data in use in the Full Text index files so I can delete them?


texts:
I have a Java program and PHP website I plan to run on my Amazon EC2 instance with an EBS volume. The program writes to and reads from a database. The website only reads from the same database.

On AWS you pay for the amount of IOPS (I/O requests Per Second) to the volume. Which database has the least IOPS? Also, can SQLite handle queries from both the program and website simultaneously?


texts: Configure MariaDB client with host-specific credentials?

texts:
How can I configure my MariaDB client (mysql) so that I don't have to enter a password to connect, regardless of which host I am connecting to?

I can't just put a password in the [client] section of ~/.my.cnf because that would apply all hosts.

For example, I'd like to be able to connection with mysql -h myhost mydatabase, ie. without specifying username or password. Even better would be the ability to specify a "host alias" instead of the actual (awkwardly long) hostname.

It sounds like this is possible in MySQL with the mysql-config-editor but MariaDB does not have this feature.


texts: Why does the query plan start with a not-filtered-for table when the filtered-for table's entries exceed a certain threshold?

texts:
We process data for our customers. Each customerrequest and is associated with one or more items (less than two on average) and falls into one of <100 categories, foreign keys to which are also present on the items. Customers often want tabular views/exports of their items that include columns of all three tables. Our ORM generates a reasonable-looking query along the lines of
SELECT *
FROM customeritem itm
  INNER JOIN customerrequest req ON itm.request_id=req.id
  INNER JOIN category cat ON cat.id=itm.category_id
WHERE customerrequest.customer_id=123

This works reasonably well for many customers, producing a query whose ANALYZE-is looks like this:

The query plan starts with the requests table on which the WHERE condition operates, then joins the items, then the categories, which intuitively seems the only way to go. However, for customers with many requests, a different plan is chosen and the query takes ages to execute (ANALYZE alone usually takes minutes):

Now the query plan starts with the (unfiltered) category table, then joins the items and only as a last step the requests table on which the WEHRE condition (which shrinks to results from an 8-digit to a 5 or 6-digit number) operates. If I remove the category join from the select, the query plan resembles the good one above (minus the final row for category join, of course).
Is there an intuitive explanation why this happens?


texts: Why mongod said "note: noprealloc may hurt performance in many applications"?

texts:
I just start mongod with config file:

mongod -f /etc/mongod.conf

In /etc/mongod.conf, I set storage.preallocDataFiles=true

This is a part of config:

storage:
   dbPath: "/data/db"
   directoryPerDB: true
   indexBuildRetry: true
   preallocDataFiles: true
   nsSize: 16

Why mongod tell me "note: noprealloc may hurt performance in many applications" ?


texts:
What is the recommended (type) of database design for storing large amounts of time-series data?

The dataset:

2 million time-series;
Each of the time-series contains around 500 timestamps;
At each of the time-stamp there are multiple variables (meta-data);
It would be optimal if meta-data could later easily be ingested into the database (preferably with a Python API) .

I have looked into PostgreSQL, but so far I have been struggling to set it up for many time-series.


texts: Why TRUNCATE TABLE cannot use four-part name?

texts:
I was trying to truncate a table as the following:

TRUNCATE TABLE [servername].[dbname].[schema].[table]

I  got error:

  object doesn't exist or you don't have permissions.

However, if I remove the servername part, it works.

Can anyone explain why I cannot use four part name in truncating? Thanks.


texts:
I am creating a program that will monitor several servers and computers.

A user can create an account and have multiple computers in his account that will be monitored when the user installs the client on it.

When the client is running for the first time, the user can input their account info and it will be saved encrypted for automatic login. After that the program will send important data to the database that only the user can read within his account.

As I am using Mysql.net connector in my vb.net project I would still want to use MySQL as my database.

At the moment I have one DB with the current tables: accounts, computers.

The problem that I am running into is the following:

At this moment the design is very flat. User IDaccount is connected with IDcomputer. But this works only for 1 computer per account. I need a way to link multiple computers to one user account. How should I go about this?

I was thinking of using a separate table that holds the computer IDs with the correct user account. Am I going in the right way?

The accounts table will hold the following info:

IDAccounts
Name
Email
Username
Passwordhash
Salt

computers table will hold the following:

IDComputers
MachineID
Hostname
WANIP
LocalIP
OS
CPUPercentage
RAMFree
RamUsed
Appversion
FreeSpaceC

I'm learning on the job. I just need to know how I can link the user account with multiple computers. I will figure out the rest, I just need a pointing in the right direction.


texts: What is a good schema for a database where users can search for inventory availability on certain dates (such as Airbnb)?

texts:
I apologize if this is not the right venue to ask such a question. This is my first time on this site and I'm open to suggestions.

I am trying to find a good schema for a database that needs to record discrete timeslots of availability of a certain item (like an Airbnb room for example) and allows users to search for items that are available on a certain timeslot.

My current solution, on a NoSQL database, is to have a collection of items with certain parameters, of which one is an array of available timeslots.

Example Item:

Item 1
Name: Foo
Location: Miami
Available_Dates:
[Tues 10am, Tues 11am, Wed 12pm, Wed 1pm....]

From this schema, I would search for items where available_dates contain X or Y slot(s).

Since the potential available times can be quite a few per item, I'm not sure if my current solution is the optimal one. Is there a best practice for something like this, or a suggestion as to how I could structure my database?


texts: How can I add a numeric version_id column to an existing table?


texts: Does SQL Server full restore set the differential base?

texts:
Differential backups are taken as the difference between the current state, and the last full backup called the "differential base".

Does restoring a full backup reset the differential base? If so, is there a way to avoid that?

The Microsoft docs only say that a backup resets the differential base. My experiments are a little inconclusive, although it seems that restores also reset this value.

I have a list of full backups taken sequentially over time. I'm switching this to a periodic full backup, with interspersed differential backups, eg. one full backup a month, with hourly differentials. However, the existing backup sets are all full backups.

I'm writing a script I want to run on a spare server which will go through the existing sets and sequentially restore them and generate differentials from the backup at the beginning of the month. This won't work if a restore resets the differential base though.

Any pointers or suggestions would be much appreciated. I actually already had something like this working with a binary diff program, but that mysteriously broke this month, so I'm trying to switch to using the built-in differential backups.

Edit: gbn as adequately answered the question of whether the restore resets the differential base, but I'd still like some suggestions on whether it's possible to extract a differential backup if given two full backups.


texts:
I have a mysql instance that is logging slow queries. I have it set to 1 sec, but I am getting back lots of these:(example)
   Query_time: 0.000237  Lock_time: 0.000064 Rows_sent: 0  Rows_examined: 109

On another server I get these:
   Query_time: 0  Lock_time: 0  Rows_sent: 0  Rows_examined: 0

Either way they both seem to be logging other queries...or am I reading something wrong?

set-variable = log-slow-queries="/tmp/mysql-slow-query.log"
   set-variable = long_query_time=1


texts:
I wanna restore my database and i use sql server 2012 express edition but when i restore my backup file in sql server management studio, i take this error:

  System.Data.SqlClient.SqlError: There is insufficient free space on disk volume 'c:\' to create the database. The database requires 84260749312 additional free bytes, while only 47428677632 bytes are available. (Microsoft.SqlServer.SmoExtended)

My .bak file is 8.27 gb and my C disk has 44.1 gb free space. Why do i take this error ? how can i fix this error ? Thanks in advance.


texts:
I'm tracking all the visitors of my site using this table:

Visitors table

In my admin panel, I have an overview of which countries visited my site, where they came from, ..., but because over 20+ different countries have visited my site already, the chart looks like this:

Messed up chart

To solve this, I want to select the top 5 countries, but I have no clue on how to do this.

I've tried using LIMIT & COUNT but no luck.


texts: Architecture of table for lot of inserts per second in SQL Server 2019

texts:
We are in process of development of project which will process around 51 000 000 records daily (~around 500-600 inserts per second). We choose Always On solution which will contain:

One Primary Server
Three replicas which has to work in synchronous mode

Main table of this project will contain 45 columns with several data types. Like I wrote above to this table we will have to insert around 500-600 rows per second (idenepndly, I mean that will be procedure insertMainTable which will insert one row to this table will be executed 500-600 times per second).

According to this efficiency we decide to create this table as partition table. Our idea is to create 32 partitions which base on smallint column in this table. Due to this solution we have idea to create 16 file groups with 32 files (so 1 file group will contain 2 files). So every partition will be in different file. What is more every 2 files will be on different filegroup. I was testing this solution and have results below:

https://stackoverflow.com/questions/60339635/sql-server-2019-always-on-performance

My question is:

Do you think that this kind of solution is enough/to complicated comparing to goals? What would you recommend?


texts: Is `GRANT CREATE SESSION to "MYROLE"` working in Oracle 12 database?

texts:
In a Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production I'm trying to assign the CREATE SESSION privilege to a custom role MYROLE and then assign MYROLE role to MYUSER user.

GRANT CREATE SESSION to "MYROLE"
GRANT "MYROLE" to "MYUSER"

When I try to connect I received:

  ORA-01045: user MYUSER lacks CREATE SESSION privilege; logon denied

What am I missing?

If I give CREATE SESSION grant directly to MYUSER it works.

EDIT:

I think that my issue was related to the fact that MYUSER hadn't a DEFAULT ROLE.


texts:
I have tried everything to be able to connect to my MS SQL Server 2012 remotely.
I have read and setup everything like Kyralessa wrote in his answer HERE.

Enable TCP/IP done
Add 1433 TCP Port done
enable SQL Server Browser done
Add 1433 TCP, 1434 UDP ports to Windows Firewall done
Add sqlserver.exe and sqlbrowser.exe to Windows Firewall done
Use ip address and port to connect done

I am still receiving an error message: Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attampt failed because the connected party did not properly respond after a period of time.

Nothing works
Do you maybe have any other solution I could try to solve this problem?


texts: Execute query cache on startup - Reasonable?

texts:
I have a rather database intensive application for a server which is intended to be turned off and on throughout the day. The issue comes at first use of the application after startup; it is slower than intended due to the large volumes of queries.

Once the query cache has taken ahold it is much more efficient and user experience pleasing. Obviously for the user experience I would like this throughout and not after the queries have been cached.

Is it reasonable, in terms of performance and error handling, to run a cron script (php) on startup to execute some queries so they can be cached or is there a method to save the mysql cache and load on the service startup?


texts: Is it dangerous to have dev environment with a Developer Edition and Live with Standard Edition, are the binaries different ?


texts: Tempdb log growth 3x as slow on new server, SP does not perform

texts: We are running into the issue that our stored procedure on our new SQL Server performs a lot slower than on the old server. We looked into the problem and noticed the following on our temp db. The incremental growth takes 3 times as long on the new server. The same happens for the log file of our database. What is causing this?


texts: How to add a new column if this doesn't exist

texts:
I'd like to create a new column in my table if that column doesn't exist. But if the column exists, I would like to update it.

I haven't found a suitable answer yet. Can anyone help me?


texts: Where is the cache for the MySQL caching_sha2_password auth plugin stored?

texts: Where is the cache for the new default authentication plugin caching_sha2_password in MySQL 8 stored? I want to know for future reference how to clear it in case I ever run into any problems using it.


texts: how to get list of months between two dates in db2

texts: I have two dates start and end date i need find list of months between two dates.for example, 01-12-2015 as start date and 01-02-2015 as end date i need result to be as , January. I am using IBM db2. another thing i need to find these using select query i have no access to create function or procedures.


texts:
I have a data structure as follows:

Two main components: Projects and Results

So I have a table called Project which has multiple fields as Id, Name, Title, StarDate, EndDate and many more which correspond to a project.

And each project can have many Results. So first I thought to make a results table but there is a problem. I have many types of results (around 15) each of them not having too many common fields. So then I thought to make a separate table for each type of result but then it will be very hard to get all the results for a project because they can be in any of the result tables.

So to be more clear I have the following data:

For Project: ProjectID, UserID, Type, Title, Summary, StartDate, EndDate, Value, Website

For results I have the following types:

Book: Title, Authors, Year, publisher, city, number_pages
Book Chapter: [All the fields from book] + chapter title, number of pages for chapter, pagination
Article: type, title, authors, magazine, ISSN, year, pagination
Thesis: title, author, coordinator, year
Citation: category, number of citations
Events: type, name, period, members
Brevets: author, name, holder, institution, number, type
And five who have the exact same structure (Tehnology, methods, products...): name, authors
And a distinct category: Others

EDIT:

The result is something that a user attaches to the application as a deliverable for a projects...what was resulted in that X project. And in the end the application must be able to select all results (regardless of their type) for a Project X or for a User X and display them, edit them...manage them.

So far I am thinking of:

making one large table with all the fields (can come up to 30 columns)
making a table for each type of result and another one for a master control which depending on the type says in which table to look
make one small table for all the results and keep only project_id and a description column in which i save all the fields as XML or JSON

But none of these seems good to me and was wondering if there is something else acceptable an ok.

How can I structure the database for it to work in the best way.


texts: Duplicate data in a table given table name

texts:
For a performance testing application we need to have lot more data in our tables than what we have now. How do I duplicate the data in a table.

In the process of duplicating I also need to set new values for primary key column. I'm thinking getting the max value of primary key column and adding a incrementing number to it will solve the primary key issue.

Is it possible to do this in PL/SQL.

-- more information

This table have over 200 columns and 3 unique keys (there are no table level constants), all the operations are happening through views and oracle packages, these packages are responsible for keeping the primary key combination unique. In the duplication process If I could add a incrementing value (starting with max+1 of the column) to a single column I can keep the uniqueness of the key combination.


texts: MongoDB delayed member replication mechanism

texts:
We are considering implementing a delayed member in our MongoDB replicaset to protect us from human error based data corruption, e.g. someone accidentally deletes some data.

How does a delayed member actually delay replication, as I see it there are two possibilities:

the delayed member observes the primary's oplog with a filter of ts < now - delay
the delayed member observes the primary's oplog in real time, stores the oplog locally and replays each action as ts >= now - delay

the reason this is vital for us is we're looking at a relatively large replication delay (24 hours). In normal operations our oplog maintains about 5 days of data, so whether (1) or (2) above is used in replication, we're fine. However, at certain times, our write workload is extremely high, in that situation our oplog will occasionally only contain < 1hr of data. In this situation (1) above will not work for us.


texts: PostgreSQL + PG_Monz (Zabbix) DB size too large > 1073741824

texts:
I an new to Postgres (9.3 on Ubuntu 14.04) monitoring and I have been using Zabbix/PG_monz.

I am seeing errors that my database has "too many temp bytes" and that my database is "too large". My actual backup files are 400 MB daily.

I am wondering if anyone is familiar with PG_Monz or just what this error might be about. Thank you in advance!


texts:
I'm having a lot of issues and don't really know where to look.  I'm pretty sure it's a referential integrity issue and has something to do with the addition of the Professor table and the Foreign Key Constraints between it and the Section table.

I've narrowed the error down to the Enrolled table.  It's the one that gives me "ERROR 1215 (HY000): Cannot add foreign key constraint" when I try an add it.  Remove the Professor, QualToTeach table and the Foreign Key constraints to professor from Section and it works beautifully.

Any help?

Here's my code:

CREATE TABLE Student (
    StudentID INT,
    Fname VARCHAR(30),
    Lname VARCHAR(30),
    DOB DATE,
    Major VARCHAR(10),
    PRIMARY KEY (StudentID)) ENGINE=InnoDB;

CREATE TABLE Professor (
    Emp_no INT,
    FName VARCHAR(10),
    LName VARCHAR(10),
    DeptCode VARCHAR(2),
    PRIMARY KEY (Emp_no)) ENGINE=InnoDB;

CREATE TABLE Phone (
    StudentID INT,
    PNumber VARCHAR(20),
    Type VARCHAR(5),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID) ON DELETE CASCADE
    ) ENGINE=InnoDB;

CREATE TABLE Class (
    ClassID VARCHAR(5),
    NoCredits INT,
    Description VARCHAR(30),
    Requisite1 VARCHAR(6),
    Requisite2 VARCHAR(6),
    Requisite3 VARCHAR(6),
    PRIMARY KEY (ClassID)) ENGINE=InnoDB;

CREATE TABLE Section (
    ClassID VARCHAR(5),
    SecNo VARCHAR(10),
    Semester VARCHAR(4),
    ClassRoom VARCHAR(6),
    TimeOffered VARCHAR(20),
    Emp_no INT,
    PRIMARY KEY (ClassID, Emp_no, SecNo, Semester),
    FOREIGN KEY (ClassID) REFERENCES Class(ClassID),
    FOREIGN KEY (Emp_no) REFERENCES Professor(Emp_no)
    ) ENGINE=InnoDB;

CREATE TABLE QualToTeach (
    ClassID VARCHAR(5),
    Emp_no INT,
    PRIMARY KEY (ClassID, Emp_no),
    FOREIGN KEY (ClassID) REFERENCES Class(ClassID),
    FOREIGN KEY (Emp_no) REFERENCES Professor(Emp_no)) ENGINE=InnoDB;

CREATE TABLE Enrolled (
    StudentID INT,
    ClassID VARCHAR(5),
    SecNo VARCHAR(10),
    Semester VARCHAR(4),
    Grade CHAR(2),
    DropDate DATE,
    PRIMARY KEY (StudentID, ClassID, SecNo, Semester),
    FOREIGN KEY (ClassID, SecNo, Semester) REFERENCES Section(ClassID, SecNo, Semester),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID))
    ENGINE=InnoDB;


texts: Can't connect to Oracle 12c Release 1 in docker container from host

texts:
I pulled the Oracle 12.1.0.2 docker image from the Oracle Container Registry (container-registry.oracle.com/database/standard:12.1.0.2) and created a container as per the instructions, leaving the environment file blank so as to keep all the parameters as their defaults (I am aware that I should change the password etc but right now I just want to get it working).

$ docker run -d --env-file ./db_env.dat -p 1527:1521 -p 5507:5500 -it --name dockerDB --shm-size="8g" container-registry.oracle.com/database/standard

I was able to successfully start the container, and can access it via docker exec. I was also able to verify that the Oracle instance was running correctly by executing some sqlplus against it from the shell inside the container.

However, I am not able to connect to the Oracle instance from my host machine, either through SQL Developer:

or through sqlplus:

I get no errors, but the connection process just hangs forever. Do I need to change some setup somewhere or have I got the details wrong?


texts:
I update JOINed tables as

UPDATE table1 a
JOIN table2 b
ON a.col1 LIKE CONCAT('%', '.', b.col2)
SET a.foreign_id=b.foreign_id

but LIKE is very slow for JOIN. Is there another way based on faster substring functions to make the JOIN?


texts: SQL Server connection from iis website: "error: 26 - Error Locating Server/Instance Specified"

texts:
I've just moved all my databases from one server to another clustered server.  The new SQL Server 2008 R2 (enterprise edition) install is using named instances on a non-standard port.  SQL is running on a Windows Server 2008 R2 Enterprise server.  My web server is running on Windows Server 2008 Enterprise.  The databases are used by three different websites, all hosted on the one server.  After moving the databases I changed the database connection strings in my website, and then checked that they were working ok.

Only one of the sites was working. I changed the connection string on the non-working sites to specify the port number that SQL is listening on.  This got one more site working.  The third site still didn't work.

I tried an iisreset (out of desperation really), and now the first site that was originally working did not work.  Only the second site, that worked with the port number specified, was working.

I disabled the firewall rules between the web server and the database server, and now all three sites work.  But the firewall is disabled, so I can't leave it like that.

I'm at a loss to figure out why the firewall would be preventing one site on a server from connecting to a database, but not another, and why a third site would only connect if the port number was explicitly added to the database connection string.  I'm also confused as to how an iisreset had any effect (to stop the first site from working).

Windows Firewall is turned off
The SQL Browser service is running
SQL is definitely running on the port I think it is running on
TCP and UDP port 1434 and the port number SQL is running on are both opened on the firewall
The old databases were all taken offline, so there's no possibility that the sites were still connecting to the original location somehow
nslookup of the sql cluster from the website server works ok
The sites all work ok when the firewall is disabled, so it has (I assume) got to be firewall related

Any ideas?


texts:
Where/When is the use of RBAR buffering related to ASYNC_NETWORK_IO waits negotiated between the Client/SQL Server, or Is it always utilized as in that is just how SQL Server spools data to the client?

Is "Row By Agonizing Row (RBAR)" a feature of ODBC or .NET Data Components, also, does anyone know if it can be turned off or overridden?

This stems from a SP that was tuned to run in 5 minutes, but when put in a report it falls in and out of ASYNC_NETWORK_IO for another 5 minutes for a total time of 10 minutes. In the worst case scenario, 800K records are returned, however, I would think the data would be "pushed" more quickly than what I am seeing. This is based on the fact that a similar report with no Column grouping seems to pull data quickly and returns the same amount of data. I know that grouping brings reports to a crawl, but please help me work with this issue if possible. Why would one query on a report with no grouping,with the same amount of records,"dumped" to a excel file, finish in 2 minutes, whereas, column grouping causes the client to buffer. Is there a way to force SSRS to buffer all the data locally, then render?

I found that using a "shared memory connection" will share the buffer, however, that is only valid if the client and SQL Server share the same server.

Also, the query plan for the SP is the size of a roadmap but it is a good plan. It uses no cursors and is entirely a set based query.


texts: Will “Oracle Express 11gR2” installation mess with the "Opera PMS" software

texts:
As in the title, I want to install "Oracle Express 11gR2" on a Win7 machine that runs Opera PMS Software (pms for Hotel Management) via Internet Explorer.

I want to write a Python script that will use cx_oracle module. I tried first with "instant client": I put its PATH in windows environment and followed the tutorial, but I still have an import error with cx_oracle module or an error that tells me there is no Oracle application on the computer.

It seems I have to install the Oracle express instead of using the "instant client". But first, I must be sure there will be no dependencies mixing up and messing the system. Can I do that blindly? I can give further info if needed


texts: Recommendations on how to organize Queues in Service Broker

texts:
I have a service broker application that currently has 5 or 6 queues on two servers.

The general workflow is:

Server A

User populates some tables
User fires stored proc which puts a message into a header queue indicating there is work to be done
Broker reads from this header queue and creates a potentially large number of messages to send to Server B

Server B

Messages all go to 1 queue
In this queue, message is examined and unbundled, payload is run and a response is collected from another set of procs/tables that we interact with
Response message is created and sent back to Server A

Server A

Response messages go to a special queue and are used to update tables the user populated at the top
End dialog messages are read from the initiator queue and used to update another table that tracks the progress of a header record (i.e. 50 out of 100 messages processed)
When we detect that the header record has been entirely processed, a new message is sent to another queue that sends a notification email

I've had a recommendation to consolidate some of those queues on Server A and I'm not sure what the best practice is for this.  Two of the queues will have a relatively high volume of message processing, and the email queue will be be pretty slow.

We did (I think) a good job of managing message types so theoretically we could put it all into one queue and the application would still function.

Is there a best practice on when to maintain separate queues and when to consolidate in a scenario like this?


texts: MySQL query to get sum of count distinct column value

texts:
I have the following mysql query.

SELECT
COUNT(DISTINCT l.userid) AS 'Logins',
DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M') AS 'Month'
FROM databasename.tablename l
WHERE l.action = 'loggedin' AND YEAR(FROM_UNIXTIME(l.timecreated)) = '2018'
GROUP BY MONTH(FROM_UNIXTIME(l.timecreated))

It will produce the output of:

Logins       Month
---------      -------
33             January
16             February

How does the mysql query looks like to get the sum of column Logins like 49 on this example?

Thanks!


texts: Saas MySQL Multiple Databases or One Database

texts:
I am using MYSQL so Database is the Schema, sorry if not using right terminology.

I currently have a Saas product which has multiple databases one for each client with a main databases which we reference for account names and connections to the individual client databases.

I am starting a new Saas product and wondered if this is the right approach for scalability.

I have read many posts on this but a lot are old posts and was wondering if anyone could offer opinion on which approach currently works best for scalability,

What is easier to scale one db or multiple db with sharding/partitioning?

Which approach is better/easier for connection pooling?

Which approach is better for query read/write speeds, is one large database better than 100s of small dbs?

Which approach is better on server CPU/Memory usage one large database better or 100s of small dbs?

I use AWS RDS, Thanks in advance.


texts: How can I create and initialize a schema for a embedded MySQL DB?

texts:
I want to create an embedded MySQL database for running tests, but I'm not sure how to do it.

How do I initialize the database?
How do I run my create table statements?

I'm using Perl, so it needs to be done with a language and not a couple of commands.


texts:
I have a table (cumulative_energy_consumptions) defined as:

id int(11) pk
sensor_id int(11) fk
value decimal
recorded_at datetime

with two indexes, one on sensor_id, the other on (sensor_id, recorded_at) and it uses InnoDB engine.

Given a sensor and a datetime, I have to find the row with the maximum 'recorded_at' before that datetime.
It can be solved with the following query:

    SELECT MAX(recorded_at)
    FROM cumulative_energy_consumptions
    WHERE sensor_id = 88 AND recorded_at <= '2016-06-29 00:00:00'

and it is incredibly fast (0.00030 s on my machine), even with a very chatty sensor (id 88) that has more than 300k records.

Instead of a single sensor, I wrote the following query to get the result for many sensors at once:

    SELECT sensor_id, MAX(recorded_at) AS first_before
            FROM cumulative_energy_consumptions
            WHERE
                sensor_id IN (80, 85, 88, 89)
                AND
                recorded_at <= '2016-07-10 00:00:00'
            GROUP BY sensor_id

and it is also very very fast (0.00055 s)

Now I was trying to write the query for one sensor and many datetimes, and I tried that:

SELECT input_times.*, (
        SELECT MAX(recorded_at)
        FROM cumulative_energy_consumptions
        WHERE sensor_id = 88 AND recorded_at <= input_times.instant
    ) as res
FROM
    (
        SELECT '2016-06-29 00:00:00' as instant
--      UNION SELECT '2016-06-30 00:00:00' as instant
--    UNION SELECT '2016-07-01 00:00:00' as instant
--    UNION SELECT '2016-07-02 00:00:00' as instant
   ) as input_times

when the table created using unions has only one row, it takes 0.0004s and I was expecting that with two rows it would have taken roughly the double, but it completes in 0.2 s; with 4 rows it takes 0.4s and so on.

Why does MySql uses a different strategy with multiple rows instead of applying the strategy for one row many times? Is there a way to rewrite this query to make it as fast as expected?

Thank you


texts:
I was trying to use copy Database wizard from one server to another to make a certain database identical and i got the error

  (The @flags parameter is not valid for a job step of type 'SSIS'. (Microsoft  SQL Server, Error: 14545)).

Some info:

The Source Server is SQL Server 2008 R2 and the destination is SQL Server 2016.
On Transfer Method I selected the second option (use the sql management object method) to keep the source database online.

Any advice on how to solve this or where to check will be appreciated.


texts: Oracle Database monitoring: Replication working or not?

texts:
How to define database replication is working or not by using Oracle data guard?
select max(sequence#) a from v$log_history;
we got primary and standby database last log sequence# from this query.
If the log sequence# differ from primary to standby database then can we say that replication is working or not? If any other way to know the replication status then let me know.


texts: We have an application user which has been given read write to the appropriate databases.  This user has the ability to query the sys tables by default.  I've also heard that there are times when this user needs to be able to obtain a listing of tables and columns that exist in the database.  Are there any security risks to having the user have read access to the sys tables?


texts: PostgreSQL - Removing Unused Index on Check Constraint column

texts:
I used the script from this blog to identify the unused index on my database. It returns an index which is on a column called result where I have a check constraint on that column.
So removing this index will impact the check constraint performance?


texts: Best practices for backing up a MySQL DB

texts:
I've recently discovered that our production web servers that run off MySQL are not being backed up regularly (or at all). I'm used to backing up SQL Server DB's but don't have a ton of experience with MySQL DB's. Any best practices for using 'mysqldump' or any other DB backup tools?

I'll probably cron job the schedule so that it's done nightly and then backup the files with my backup system.

Thanks.


texts: is there a way to find out who was spid228 at a given time in the past?

texts:
I have a script that reads the error log so that I can see what has been happening within the last day:

--create the table
CREATE TABLE #Radhe (logdate datetime not null default (getdate()),
                     processinfo varchar(108) not null default ('Radhe'),
                     the_text varchar(4000))

-- create a non-unique clustered index
CREATE CLUSTERED INDEX IXC_RADHE_RADHE ON #RADHE(logdate desc, processinfo  asc)

-- load the table
INSERT #Radhe EXEC xp_readerrorlog

-- read the data
SELECT * FROM #Radhe with (index(IXC_RADHE_RADHE))
WHERE LOGDATE > (GETDATE() - 1)
  AND processinfo LIKE 'SPID%'
order by logdate desc

when I run this script this morning I see the below:

  2016-04-13 11:08:23.640   spid228 Configuration option 'xp_cmdshell'
  changed from 0 to 1. Run the RECONFIGURE statement to install.

Is there a way I can find out who/IP of spid228 who did the above?


texts: Using Row_Number to find consecutive row count

texts:
I have this column of ints that represent an occurrence of a signal and I'm trying to add a column that shows the count of consecutive row

If my data looks like this

724
727
728
733
735
737
743
747
749

the resulting data with a consecutive row count column would look like this

724 1
727 1
728 2
729 3
735 1
737 1
743 1
744 2
748 1

I've done it using a looping function but I'm trying to figure out using a cte. Here is a sample of my latest attempt

DECLARE @d TABLE ( signal INT )
INSERT  INTO @d
        SELECT  724
        UNION
        SELECT  727
        UNION
        SELECT  728
        UNION
        SELECT  729
        UNION
        SELECT  735
        UNION
        SELECT  737
        UNION
        SELECT  743
        UNION
        SELECT  744
        UNION
        SELECT  748 ;
WITH    a AS ( SELECT   signal,
                        ROW_NUMBER() OVER ( ORDER BY signal ) AS marker
               FROM     @d
             ) ,
        b AS ( SELECT   a1.signal,
                        CASE ( a1.signal - a2.signal )
                          WHEN 1 THEN 1
                          ELSE 0
                        END consecutiveMarker
               FROM     a a1
                        INNER JOIN a a2 ON a2.marker = a1.marker - 1
             )
    SELECT  *
    FROM    b

Produces these  results

signal  consecutiveMarker
727 0
728 1
729 1
735 0
737 0
743 0
744 1
748 0

The first obvious issue is missing the first signal in a series. Barring that, I thought I could then pass this to another cte with a row_number partitioning on the consecutiveMarker. That didn't work because it partitioned it as one  partition. I couldn't find a way to indicate to the partitioning method that one series is separate from the next

Any help is appreciated.


texts: Oracle 12c Rman Duplicate Archive Log Not Verified

texts:
I am trying to duplicate a DB from live to dev but it is failing. Can anybody advise what is going wrong? Both databases are running Oracle 12c. All database files seem to be copied over but it fails when trying to apply the archive logs.

I am running a full database and archive log backup before executing the DUPLICATE command.

Command

RUN{
DUPLICATE TARGET DATABASE TO UAT
FROM ACTIVE DATABASE
USING BACKUPSET
SPFILE
set control_files 'E:\oracle\product\12.1.0\oradata\UAT\CONTROLFILE\cf1.ctl','E:\oracle\product\12.1.0\oradata\UAT\CONTROLFILE\cf2.ctl','E:\oracle\product\12.1.0\oradata\UAT\CONTROLFILE\cf3.ctl'
set db_recovery_file_dest 'L:\ORACLE\ORADATA\UAT'
set db_create_file_dest 'E:\oracle\product\12.1.0\oradata'
set log_archive_dest_1 'LOCATION=L:\BACKUP\UAT\RMAN_BACKUPS'
set log_archive_dest_2 'LOCATION=L:\ORACLE\ORADATA\UAT\ARCHIVELOG'
set audit_file_dest 'E:\oracle\admin\UAT\adump'
set core_dump_dest 'E:\oracle\admin\UAT\cdump'
nofilenamecheck;
}

Error

Finished restore at 24-MAY-16

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
restore clone force from service  'ENGSPH'
           archivelog from scn  2661373093;
   switch clone datafile all;
}
executing Memory Script

Starting restore at 24-MAY-16
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service ENGSPH
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=5659
Oracle instance started

Total System Global Area    2147483648 bytes

Fixed Size                     3047720 bytes
Variable Size                822087384 bytes
Database Buffers            1308622848 bytes
Redo Buffers                  13725696 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''UAT'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
}
executing Memory Script

sql statement: alter system set  db_name =  ''UAT'' comment= ''Reset to original
 value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/24/2016 16:26:55
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script

ORA-19845: error in backupArchivedLog while communicating with remote database s
erver
ORA-17628: Oracle error 19571 returned by remote Oracle server
ORA-19571:  RECID  STAMP  not found in control file
ORA-19660: some files in the backup set could not be verified
ORA-19662: archived log thread 1 sequence 5659 could not be verified
ORA-19845: error in backupArchivedLog while communicating with remote database s
erver
ORA-17628: Oracle error 19571 returned by remote Oracle server
ORA-19571:  RECID  STAMP  not found in control file

Thanks for any help.


texts: Directory Verification with For Each Loop in SSIS

texts:
How can we check if a directory is valid or not inside for each loop . I am having multiple files at some location and i want to load that file but before this i
 want to check if the directory is correct or not .
If the directory is not correct it will throw error for wrong directory.

I am passing Directory path in config file.

to avoid this situation do i need to write script task for this

Unexpected behavior of Foreach Loop in SSIS 2012


texts:
I got a little confused by all these keys. As I understand

A key of a relational schema =
The minimal subset of a superkey that is still a key =
A candidate key

Is this correct or not?


texts: Why doesn’t this recursive CTE with a parameter use an index when it does with a literal?

texts:
I am using a recursive CTE on a tree structure to list all the descendants of a particular node in the tree. If I write a literal node value in my WHERE clause, SQL Server seems to actually apply the CTE just to that value, giving a query plan with low actual rows counts, et cetera:

However, if I pass the value as a parameter, it seems to realize (spool) the CTE and then filter it after the fact:

I could be reading the plans wrong. I haven’t noticed a performance issue, but I am worried that realization of the CTE could cause issues with larger data sets, especially in a busier system. Also, I normally compound this traversal on itself: I traverse up to ancestors and back down to descendants (to ensure that I gather all related nodes). Due to how my data is, each set of “related” nodes is rather small, so realization of the CTE doesn’t make sense. And when SQL Server seems to realize the CTE, it is giving me some quite large numbers in its “actual” counts.

Is there a way to get the parameterized version of the query to act like the literal version? I want to put the CTE in a reusable view.

Query with literal:

CREATE PROCEDURE #c AS BEGIN;
    WITH descendants AS (SELECT
         t.ParentId Id
        ,t.Id DescendantId
    FROM #tree t
    WHERE t.ParentId IS NOT NULL
    UNION ALL SELECT
         d.Id
        ,t.Id DescendantId
    FROM descendants d
    JOIN #tree t ON d.DescendantId = t.ParentId)
    SELECT d.*
    FROM descendants d
    WHERE d.Id = 24
    ORDER BY d.Id, d.DescendantId;
END;
GO
EXEC #c;

Query with parameter:

CREATE PROCEDURE #c (@Id BIGINT) AS BEGIN;
    WITH descendants AS (SELECT
         t.ParentId Id
        ,t.Id DescendantId
    FROM #tree t
    WHERE t.ParentId IS NOT NULL
    UNION ALL SELECT
         d.Id
        ,t.Id DescendantId
    FROM descendants d
    JOIN #tree t ON d.DescendantId = t.ParentId)
    SELECT d.*
    FROM descendants d
    WHERE d.Id = @Id
    ORDER BY d.Id, d.DescendantId;
END;
GO
EXEC #c 24;

Setup code:

DECLARE @count BIGINT = 100000;
CREATE TABLE #tree (
     Id BIGINT NOT NULL PRIMARY KEY
    ,ParentId BIGINT
);
CREATE INDEX tree_23lk4j23lk4j ON #tree (ParentId);
WITH number AS (SELECT
         CAST(1 AS BIGINT) Value
    UNION ALL SELECT
         n.Value * 2 + 1
    FROM number n
    WHERE n.Value * 2 + 1 <= @count
    UNION ALL SELECT
         n.Value * 2
    FROM number n
    WHERE n.Value * 2 <= @count)
INSERT #tree (Id, ParentId)
SELECT n.Value, CASE WHEN n.Value % 3 = 0 THEN n.Value / 4 END
FROM number n;


texts: CPU and Hard Drive Spike for 20-30 Minutes

texts: I'm collecting data on the performance of our Microsoft SQL 2008 server in preparation for moving it to Azure.  It's a very low load server (currently max concurrent transactions max out at 3), however, about once a day there's a CPU and hard drive activity spike for about 20-30 minutes.  Transaction activity during this time is the same as other times, however, the buffer cache hit ratio drops to almost 0 at the beginning of this spike (returns to 99+% quickly).  Free pages fluctuate during the spike and after the spike is done the number of free pages is higher than before.  Any way to find out what might be causing this spike?  Could this be some sort of garbage collection?  Anything specific I could monitor via Performance Monitor to give me a better idea of what's going on?


texts: Mongodb - Cluster one host keeps syncing

texts:
I've setup a mongodb cluster, MongoDB shell version: 2.6.11.
About five days ago I performed a mongorestore on the primary database.
The size of the restore is about 2Gigabytes.

Right now, one database is still syncing. I'm not sure if this normal, or there is an issue somewhere. I restarted mongod once on the mongo-one server.

Output of rs.status()

rsOrder:SECONDARY> rs.status()
{
    "set" : "ReplName",
    "date" : ISODate("2016-11-16T07:52:28Z"),
    "myState" : 2,
    "syncingTo" : "mongo-two:27017",
    "members" : [
        {
            "_id" : 0,
            "name" : "mongo-two:27017",
            "health" : 1,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "uptime" : 167670,
            "optime" : Timestamp(1478864074, 8643),
            "optimeDate" : ISODate("2016-11-11T11:34:34Z"),
            "lastHeartbeat" : ISODate("2016-11-16T07:52:26Z"),
            "lastHeartbeatRecv" : ISODate("2016-11-16T07:52:27Z"),
            "pingMs" : 0,
            "syncingTo" : "mongo-three:27017"
        },
        {
            "_id" : 1,
            "name" : "mongo-one:27017",
            "health" : 1,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "uptime" : 167673,
            "optime" : Timestamp(1478864074, 8643),
            "optimeDate" : ISODate("2016-11-11T11:34:34Z"),
            "self" : true
        },
        {
            "_id" : 2,
            "name" : "mongo-three:27017",
            "health" : 1,
            "state" : 1,
            "stateStr" : "PRIMARY",
            "uptime" : 72642,
            "optime" : Timestamp(1478864074, 8643),
            "optimeDate" : ISODate("2016-11-11T11:34:34Z"),
            "lastHeartbeat" : ISODate("2016-11-16T07:52:28Z"),
            "lastHeartbeatRecv" : ISODate("2016-11-16T07:52:27Z"),
            "pingMs" : 0,
            "electionTime" : Timestamp(1479210113, 1),
            "electionDate" : ISODate("2016-11-15T11:41:53Z")
        }
    ],
    "ok" : 1
}

Output of my log:

2016-11-16T08:52:42.342+0100 [ConnectBG] BackgroundJob starting: ConnectBG
2016-11-16T08:52:42.342+0100 [rsHealthPoll] connected to server mongo-two:27017 (10.144.4.41)
2016-11-16T08:52:43.152+0100 [conn11181] command admin.$cmd command: replSetHeartbeat { replSetHeartbeat: "ReplName", v: 6, pv: 1, checkEmpty: false, from: "clp-dbmg-p03:27017", fromId: 2 } ntoreturn:1 keyUpdates:0 numYields:0  reslen:157 0ms
2016-11-16T08:52:43.772+0100 [conn11179] command admin.$cmd command: replSetHeartbeat { replSetHeartbeat: "ReplName", v: 6, pv: 1, checkEmpty: false, from: "clp-dbmg-p02:27017", fromId: 0 } ntoreturn:1 keyUpdates:0 numYields:0  reslen:157 0ms
2016-11-16T08:52:45.153+0100 [conn11181] command admin.$cmd command: replSetHeartbeat { replSetHeartbeat: "ReplName", v: 6, pv: 1, checkEmpty: false, from: "clp-dbmg-p03:27017", fromId: 2 } ntoreturn:1 keyUpdates:0 numYields:0  reslen:157 0ms
...

Is this normal, or should I take action?


texts: Lock out of Oracle based on Windows username

texts:
I have this logon trigger to only allow certain users to log in to an Oracle database (even if they have the correct password to enter the database):

CREATE OR REPLACE TRIGGER SYS.LOGON_TRIGGER
  AFTER LOGON ON DATABASE
DECLARE
  THIS_USER VARCHAR2(50);
BEGIN
  SELECT OSUSER INTO THIS_USER FROM V$SESSION WHERE SID = SYS_CONTEXT('USERENV','SID');
  IF THIS_USER NOT IN (<List of Users>)
    THEN RAISE LOGIN_DENIED;
  ENDIF;
END;
/

It works for preventing users from entering most schemas but not all (e.g. the SYS or SYSTEM schemas can still be entered regardless of the user - this logon trigger is seemingly completely bypassed).

Is there a way to lock out these users even for these SYS type schemas?

A bit of context:

Due to decisions made way before I got involved with this, all of the logins for this database have the same password. Additionally, most users use the same login as many of our processes that read/write to this database automatically.

We don't want to simply change the passwords because it would be a very large effort to see what impact changing these passwords actually does to the system. (We would have to modify the code that the processes use to access the database, and there are many of these.) An easier solution for us is to just lock out based on usernames, if possible.


texts: SqlServer output and return value in procedure

texts:
I'm new to sqlserver, and i have this error that i was not able to fix:

  Msg 245, Level 16, State 1, Procedure info, Line 11 Conversion failed when converting the varchar value 'Steven' to data type int
  Can you please show me where is the error, and how can i fix it? The code is as follow thank you.

CREATE PROCEDURE info (@id int , @howMuch int OUTPUT) AS

select * from emp where empno=@id;

select @howMuch =count (*) from emp;

declare @name varchar(100)

select @name=ename from emp where empno=@id

return @name --with return doesn't work!!
--select @name with select it works !!
--@name as an output parameter it works!!
GO

-- execute the procedure. It gives me error.
DECLARE @num_rows int, @who varchar(100)
EXECUTE @who=info 100, @num_rows OUTPUT

select @num_rows,@who

I have noticed that: If instead of using "return" I use "print" to visualize the "@name", the procedure works, and i see "@name". It also works well if instead of "return" I put "@name" as an output parameter. It seems that the error is only when using "return" to return @name. Why is this? What i'm missing to understand?


texts:
I wrote below script and run it in two tab of SQL Server management studio:

DECLARE @RC INT
Begin TRAN
    Exec @RC =sp_getapplock @Resource='MyLock', @LockMode='Exclusive', @LockOwner='Transaction', @LockTimeout = 15000

    SELECT @@SPID [session_id], @RC [return code], GETDATE()
    WAITFOR DELAY '00:00:08'
COMMIT

One of the execution wait 8 seconds and another wait 16 seconds. I was expecting it and every things is fine.
But when I open two SQL Sever management studio which connected same instance, and run this script, It is not working and both executing wait 8 seconds!

I test my scenario in two Management Studios in two PCs that connected to same SQL Server instance.

Do sp_getapplock not working in multiple Connection?


texts: mysql copy and insert rows in same table with new primary key and sequence number

texts:
I have multiple table with primary key, data columns and batch no.

I require procedure which dynamically creates statement to copy rows from table and insert into same table by changing auto increment value batch no.

Original Table

mysql query should copy records of batch 1 and paste them as batch 10 as below

I have tried to write query but it is coping all records, also it gets failed timeout for large no of records.

CREATE PROCEDURE `duplicateRows`(_schemaName text, _tableName text, _omitColumns text, var_run_seq_no int,var_old_run_seq_no int)
    BEGIN
SELECT IF(TRIM(_omitColumns) <> '', CONCAT('id', ',', TRIM(_omitColumns),'batch_no'), CONCAT('id',',batch_no')) INTO @omitColumns;

SELECT GROUP_CONCAT(COLUMN_NAME) FROM information_schema.columns
WHERE
table_schema = _schemaName
AND
table_name = _tableName
AND
FIND_IN_SET(COLUMN_NAME,@omitColumns) = 0
ORDER BY ORDINAL_POSITION INTO @columns;

SET @sql = CONCAT('INSERT INTO ', _tableName, '(', @columns, ',batch_no)',
  'SELECT ', @columns, ',', var_batch_no,
  ' FROM ', _schemaName, '.', _tableName);

-- select @sql;
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
END


texts: Are multiple simultaneous direct loads a better strategy than individual inserts in PostgreSQL?

texts:
In our application we run a background processor called Sidekiq which allows us to run hundreds of idempotent jobs against the database in parallel.

We have one job which can add up to 3,000 inserts into a very large table (60 attributes, 7 indexes, 1.5 billion records). Currently we have this set up to do direct loads.

CSV data is put into an array (not written to file) and fed to STDIN like so:

  ActiveRecord::Base.connection_pool.with_connection do |conn|
    rc = conn.raw_connection
    rc.copy_data "COPY apps FROM STDIN WITH CSV HEADER" do
      rows.each do |line|
        rc.put_copy_data line
      end
    end
  end

To generate the data for this direct load, we have to hit the table sequence once for each row. If 200 of these jobs are running simultaneously, and they can each have 3,000 records, that seems like a lot.

Is this strategy a bad idea, and all other things being ideal would we experience better throughput by doing individual inserts instead?


texts: Which user does Windows Authentication of a maintenance plan authenticate? before whom, why and for what?

texts:
I am having a maintenance plan configured with Windows Authentication

being run by an SQL Server Agent job with owner sa

Whom does Windows Authentication of a maintenance plan authenticate, for whom and for what -  why is it needed and/or how can it be used?
To which user I should give permissions for accessing backup (being stipulated in maintenance plan) dir location?

And how does the owner of an SQL Server Agent job enter into all this considering that an owner sa has SQL Server authentication?


texts: PostgreSQL problems after vacuum - multiple rows with same pkey, different counts of those rows from seq scan vs index

texts:
We tried to run a full vacuum on our Postgres database the other day because of disk problems. A bunch of things went wrong along the way and eventually we stopped the vacuum.

Now we have some new issues. For example, this constraint on our transactions table:

CONSTRAINT transactions_pkey PRIMARY KEY (transaction_id)

If I try to count the rows for transaction_id 12345 with seqscan=on:

set enable_seqscan=on;
with cte as (
    select count(*), transaction_id from transactions group by transaction_id
)
select count from cte where transaction_id='12345';

The result is a count of 12.

And if I run the same count with secscan=off (to use transactions_pkey):

set enable_seqscan=off;
with cte as (
    select count(*), transaction_id from transactions group by transaction_id
)
select count from cte where transaction_id='12345';

That returns a count of 8.

I only use an awkward count query so that the first example doesn't use the index.

The sequential scan shows 12 rows and the index scan shows 8 rows, but the pkey constraint shouldn't allow more than a single row.

Maybe there was a short period of time during the failed vacuum where the pkey was ignored and duplicate records were inserted? Then somehow the pkey was re-added?

We've tried to script our own cleanup, but deciding which records to keep/update/remove for hundreds of tables with unique constraints has been troublesome. We might need to try another full vacuum, but our database seems to be in an uncommon error state and we're not sure if a vacuum will fix it or make the problem worse.


texts: Sql servers are using Kerberros and NTLM Authentication

texts: Lately I have been facing failed connection errors mainly due to servers using NTLM Authentication. In our environment the servers have a combination of ntlm and kerberoos. My question is If i ask my Domain admin to manually register all the service accounts to use the kerberoos instead of ntlm authentication will there be any problem or any performance issues that might arise ?


texts:
In my application I am using the below stored procedure, but it is taking a long time to execute. Both tables contain millions of records. Please guide me to optimize this procedure. I have searched a lot but not found any better way.

 CREATE PROCEDURE [dbo].[sp_SearchReportAdminPanel]
 @UserName nvarchar(max),
 @FromDate datetime,
 @ToDate datetime,
 @RecepientNumber nvarchar(max),
 @Status nvarchar(max),
 @SenderId nvarchar(max),
 @PanelMasterId BIGINT = 1,
 @PageIndex INT = 1,
 @PageSize INT = 10,
 @RecordCount INT OUTPUT
 WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SkipPageRank INT = (@PageIndex - 1) * @PageSize ;

SELECT temp.*,um.usr_Mobile_No as UserName INTO  #Results FROM
(
 SELECT XML_Id, Temp_Id, RIGHT(Mobile_number,10) as Recepient, Message_Text as Text,
        Coalesce(Message_Status, 'Sent') as Status, CreatedDate,DeliveryDate,User_Id
 FROM  Table1 xmd WITH (NOLOCK)
 WHERE (xmd.CreatedDate between @FromDate AND @ToDate) and
    (RIGHT(xmd.Mobile_number,10) = @RecepientNumber OR @RecepientNumber = '') and
    (Coalesce(xmd.Message_Status, 'Sent') = @Status OR @Status = '') and
    (xmd.Sender_Id = @SenderId OR @SenderId = '')
 UNION ALL
 SELECT XML_Id, Temp_Id, RIGHT(Mobile_number,10) as Recepient, Message_Text as Text,
        Coalesce(Message_Status, 'Sent') as Status, CreatedDate,DeliveryDate,User_Id
 FROM  Table2 xmd WITH (NOLOCK)
 WHERE (xmd.CreatedDate between @FromDate AND @ToDate) and
    (RIGHT(xmd.Mobile_number,10) = @RecepientNumber OR @RecepientNumber = '')   and
    (Coalesce(xmd.Message_Status, 'Sent') = @Status OR @Status = '') and
    (xmd.Sender_Id = @SenderId OR @SenderId = '')
) temp INNER JOIN Table3 um WITH (NOLOCK) on temp.User_Id = um.usr_Id
WHERE
    (um.usr_Mobile_No = @UserName OR @UserName = '') and
    (um.PanelMasterId=@PanelMasterId)

   SELECT @RecordCount = COUNT(*) FROM #Results

   select * from #Results
   ORDER BY XML_Id DESC
   OFFSET @SkipPageRank ROWS
   FETCH NEXT @PageSize ROWS ONLY

    DROP TABLE #Results
END


texts:
I have the following table:

CREATE TABLE assignment (
  id INT NOT NULL AUTO_INCREMENT,
  course_id INT NOT NULL,
  title VARCHAR(128),
  assigned DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  due DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
)

with the following trigger:

DELIMITER ;;

DROP TRIGGER IF EXISTS assignment_due_date;

CREATE DEFINER=`root`@`localhost` TRIGGER assignment_due_date
    BEFORE INSERT ON `assignment`
    FOR EACH ROW BEGIN
        IF (NEW.due IS NULL) THEN
            SET NEW.due=DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 7 DAY);
        END IF;
    END;;

DELIMITER ;

The intent is to set a default due date of 1 week (7 days), unless the INSERT already specifies a due date. However, whenever I insert a row, the due date ends up the same as the assigned date, without any error messages. Why would the trigger not update the due column?

Note that this behaves as expected when the INSERT statement does specify a value for the due column, and only misbehaves when a value is not provided. INSERT INTO assignment (course_id, title, due) VALUES (12, 'Hemingway Essay', '2019-06-12') populates the assigned and due columns with the current time and 2019-06-12, as expected, but INSERT INTO assignment (course_id, title) VALUES (12, 'Hemingway Essay') populates both columns with the current time.

UPDATE

I've removed the DEFAULT value of the due column, so the table now looks like this:

CREATE TABLE assignment (
  id INT NOT NULL AUTO_INCREMENT,
  course_id INT NOT NULL,
  title VARCHAR(128),
  assigned DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  due DATETIME NOT NULL,
  PRIMARY KEY (id)
)

With no trigger in place, the following INSERT fails (due to the lack of default value):

 INSERT INTO assignment (course_id, title) VALUES (12, 'Hemingway Essay')

which is expected. With the following three variations of the trigger in place, I would expect the due date to be set to one week out, but it always ends up with the current time:

IF (NEW.due IS NULL) THEN
    SET NEW.due=DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 7 DAY);
END IF;

SET NEW.due=COALESCE(NEW.due, CURRENT_TIMESTAMP + INTERVAL 7 DAY);

SET NEW.due=(CURRENT_TIMESTAMP + INTERVAL 7 DAY); -- forget conditions, just always set


texts: Using NAS device as SQL server backup target

texts: I have used NAS devices as backup targets for SQL server databases. I have used them as file shares which are mapped to servers holding databases. Thing is that our current NAS supports iSCSI protocol and was wondering if I can use NAS LUNs (mapped as drives) in the SQL server virtual servers and take advantage of the protocol? Anyone who has tried this before or know about some difficulties/deficiencies using this method. Thanks.


texts: MySQL reads Date columns in Excel as DateTime?

texts:
I'm trying to export Excel sheets to MySQL as new table using the plugin.

The export fails on "MySQL error 1064" when I tried to change the data type to Date.

I have a few columns in Excel that are formatted as Short Date, although some of them are generated by functions like =TODAY(), some are numbers like "42445", etc.

But all of them date type not Date Time.

For example it bothers me that MySQL detects '1/1/2016' in Excel as '1/1/2016 12:00:00 AM' as VarChar(225). And whenever I changed the type to Date the error message mentioned above appears.

I did try to re-format the columns in spreadsheet to YYYY-MM-DD but MySQL always convert them to MM/DD/YYYY HH:MM:SS

Thanks in advance for any help.

MySQL for Excel 1.3.6

Office 2016 32-bit

edit: this looks more like the plugin's bug of this version
https://bugs.mysql.com/bug.php?id=80079


texts: How to set up a transactional replication to an Always On Availability Group (AG) with two replicas through connecting to the AG Listener

texts: I want to replicate some tables on a SQL Server instance (Publisher) to an Always On Availability Group (AG) with two replicas (nodes) using its Availability Group Listener. The Distributor is the same as the Publisher. Some tables of a database on the Publisher is supposed to be replicated (Transactional Replication) to both replicas of the remote Always On Availability Group (AG) through connecting to the AG Listener. I successfully built a publication. I could build a subscription and set the AG listener as the Subscription server and didn't see any error but when I checked the AG listener, there was nothing in the Local Subscriptions folder. There was nothing in Local Subscriptions folders of both replicas, either. I tried to use official documents of Microsoft but it didn't resolve the problem. I searched a lot but couldn't find a proper documentation for such a situation.


texts: Top x distinct values ordered by date for each uid


texts: Why is the subquery apparently correlated? How to avoid it?

texts:
I have a table orders and a table orderpositions. The orderpositions (basically the items bought with the order) are linked via orderid (indexed) to the orders primary key.

For a given set of orders I need the orderpositions. Because the conditions with which the orders are determined are dynamic I need to use a subquery instead of a JOIN, so I wrote:

SELECT *
FROM orderpositions
WHERE orderpositions.orderid IN
(
    SELECT id FROM orders WHERE userid=118
);

(The sub-query can be something entirely different, perhaps with nested JOINS and whatnot, but will always return a list of order IDs, of which I need the linked orderpositions.)

Now this runs significantly slower than the JOIN equivalent (which is trivial in this example, but not in others). I have read about correlated queries and believe that my approach is very slow because the subquery is executed once for each row in the orderpositions table.

As a test I have tried to create a temporary table, insert the order IDs into it and then JOIN on that, which is also very fast. But I would like to avoid the overhead of the temporary table and firing off multiple queries.

Is there any way to force the sub-query to be uncorrelated?


texts:
At work, one of our primary databases is running on MS SQL Server. I think it's 2008.

I'm currently hitting it via UnixODBC and FreeTDS from a Linux host. Unfortunately, the UnixODBC/FreeTDS are not supported by our admins, so it falls on me. For a long-term strategy I want to move off ODBC and switch to HTTP, using either SOAP or REST calls. The installation is a lot easier with HTTP and most languages understand it, at least Perl, Python and Ruby, which I develop in.

We've got a decent amount of SOAP calls set up on the database for some Java processes, so we can probably switch to that pretty easily. We were discussing REST also, which I like because it's lighter-weight. Our DBAs don't have any REST interfaces set up, so I was wondering if there is a plugin or tool for SQL Server that can do simple REST mappings of the tables to get us jump-started. I've considered writing something in Ruby's Sinatra but that'd be one more thing on my plate.

I searched for "SQL Server + rest" but it's pretty obvious that the Googles will do the wrong thing with the word "REST" and, as a consequence, didn't find anything worth while. So, if you have experience with something providing REST and felt it was a good tool then chime in.


texts: How to properly replicate and archive at once

texts:
I have a 300GB database that is growing around 5GB a day. It stores time-based data, and as such, I currently have around 60 days of data. My main database server has a 450 GB NVMe, so I want to get a secondary server that has expandable cheap storage for old data.
The main server only really needs the last 15 days of data. So anything older than 15 days of data I want to move off to the secondary database which has slower but cheap storage. I think standard backup or archiving isn't viable because I want to query the data at any given moment (maybe ~100 queries a day).
I want to kill two birds one stone here and I thought if I setup replication to the secondary server and then somehow indicate the cleanup stored procedure which delete records older than 15 days doesn't propagate deletes to the slave.
Is this possible and recommended?
If not, what is the best way to accomplish archived the data but to another server and allow querying. I suspect partitioning all of my tables but not all of my tables are time-based so I would need some way to move the time-based partitioned data over along with any FKs needed for it
PS: I won't be relying on the secondary server as a backup but rather my main replication.  I would then do offsite backups of the secondary server periodically


texts: Installation of SQL Server 2014 and SQL Server 2012 on the same system

texts:
I have a system with a SQL Server 2012 Developer edition installed.

Can I install SQL Server 2014 (6 months trial) and SQL Server 2012 on the same system?

I do not want to overwrite the pre-existing SQL Server 2012


texts: Under what conditions does SQL Server encrypt tempdb?

texts: When does tempdb get encrypted with Transparent Data Encryption?  What configuration, etc, causes tempdb to be encrypted this way?


texts: Txn Log backups - does it backup empty space?

texts:
If I have a transaction log that is 1.5GB and only 5% used, will the transaction log backup only the 5% of the 1.5Gb (~250MB)?  I always assumed so, but I am seeing constant log backups of 250MB (every 10 minutes) even in the middle of the night when there shouldn't be any activity in the database.  This observation has led some to believe it is backing up the entire 1.5GB and with compression of the empty space making it smaller.

Does a transaction log backup only what it needs to, or does it do the entire .ldf file?


texts:
I'm on MySQL trying to get the user's daily post count grouped by post category.

So for every day in a given date range, with no gap, I need the post count for each existing post category for a given user. When the user has not posted anything in a category for that day, that category must still be listed with a null or, better yet, 0 postCount.

I've solved the daily date part by using a sequence table. But I'm failing to include the empty categories. I believe I need to emulate full outer join with union, but I'm not sure where to start.

My current query:

select
    `list_of_dates`.`date` as `date`,
    `post_categories`.`name` as `categoryName`,
    count(`posts`.`id`) as postCount
from (
    SELECT
        DATE_ADD('2016-12-28', INTERVAL seq.seq DAY) AS date
    FROM
        seq_0_to_999 AS seq
    WHERE
        DATE_ADD('2016-12-28', INTERVAL seq.seq DAY) <= '2017-03-28'
) as list_of_dates
left join
    (
        SELECT `posts`.* FROM posts WHERE `posts`.user_id = 65
    ) as posts on DATE(`posts`.`created_at`) = `date`
left join
    `post_categories` on `post_categories`.`id` = `posts`.`category_id`
group by
    `date`, `categoryName`
order by
    `date` asc


texts:
In Powershell, I have some data that I am importing from a .csv, and some data points aren't always entered so I need to replace them with [DBNull]::Value.

The problem is that when I go to use Write-SqlTableData I get this error:

Write-SqlTableData : Invalid storage type: DBNull.

I'm using this code to detect blank spaces and replace them with DBNull's:

$DataOut | Foreach-Object {
    ForEach ($Property in $_.PSObject.Properties) {
        If([String]::IsNullOrEmpty($Property.Value)) {
            $_.($Property.Name) = [DBNull]::Value
        }
    }
}

What is the issue here? What is an alternative?


texts: Postgresql INSERT triggers "0 row affected" workaround

texts:
In a INSERT trigger based table partition, insert always give "0 row affected". I think this problem is well know, it is even in the todo.

Is there any workarounds for this issue in 9.4?


texts: Oracle How to Generate Multiple Ranges Based on a Result Set

texts:
Given a table like this:

with qtys as (
  select 3 as qty from dual union all
  select 10 as qty from dual union all
  select 9 as qty from dual union all
  select 7 as qty from dual union all
  select 9 as qty from dual union all
  select 3 as qty from dual
)

I want to find the greatest common value of QTY.

I have thought of transforming each of these into a range and then getting the value from the result set that has the same count as the count in the table QTYS.

Only I'm not certain how to do this, and I'd like to do it with plain SQL in Oracle 11g, not a DECLARE...BEGIN...END PL/SQL block.

I've searched around and found many different methods of generating a single range based on a number, but I haven't found anything that would generate multiple ranges from zero to the value in the table and union them together as a single result set.

For example, given the above table I think if I could turn it into this:

QTY
---
 1
 2
 3
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 1
 2
 3
 4
 5
 6
 7
 8
 9
 1
...

Then I could find the count(1) of each of these and match it to the count(1) of the table QTYS, and use that value:

select count(1)
, qty
from (
  -- resultant ranges
)
group by qty
having count(1) = (
  select count(1)
  from qtys
);

In the case of the example table above, QTYS, the result of this query would be 3.

Maybe there is a better way to do this that I'm not thinking of, but I'm not coming to any conclusions yet.

Any thoughts?


texts:
I have this table

And i need have this (In source table are thousands values)

How can i do that ?

Here is example database: http://sqlfiddle.com/#!3/4278d

Thank you.


texts: Postgres 9.3 64 Bit Windows 7 and 64 bit Postgres install - cleared password no longer connects

texts:
Originally I allocated a password to the postgres database, but for some stupid reason the servers are usually setup with a blank password.

I then assigned it a blank password in PGAdmin III, and it accepted this.  However when I restarted the server it complained `fe_sendauth: no password supplied.

I have tried following the other posts, but the service won't start if I supply any of the supplied config_types.

I think this must be connected to it being a 64 bit install.

Before checking this site, my colleague suggested

host    all             all             samehost            trust

I put this in the # IPv4 connections: section but whilst the server starts it still repeats the same error message

It seems pretty stupid that it accepts a blank password before the .conf settings have been made because it seems that now I am at the point of no return