texts:
-- Trying to get SQL Server verion with CMS information. Run this in SQLCMD mode

-- Create temp table for Instance
CREATE TABLE #Instance_list(Server_Group_Name nvarchar(12), Server_Name nvarchar(50), Instance_Description nvarchar(512))
GO
-- Get Instance list from CMS
INSERT INTO #Instance_list
SELECT CONVERT(nvarchar(12), groups.name) AS Server_Group_Name
     ,CONVERT(nvarchar(50), svr.server_name) AS Server_Name
     , CONVERT(nvarchar(512),svr.description) as Instance_Description

FROM msdb.dbo.sysmanagement_shared_server_groups_internal groups
INNER JOIN msdb.dbo.sysmanagement_shared_registered_servers_internal svr
ON groups.server_group_id = svr.server_group_id
GO
-- Add SQL version column
ALTER TABLE  #Instance_list ADD Instance_Version nvarchar(512)
-- Loop each instance
DECLARE @Instance nvarchar(50)
DECLARE Instance_List_cursor CURSOR FOR
SELECT [Server_Name] from #Instance_list
OPEN Instance_List_cursor

 FETCH NEXT FROM Instance_List_cursor
 INTO @Instance

 WHILE @@FETCH_STATUS = 0
 BEGIN
 -- How to connect each SQL instance to get SQL server version updated in to the temp table?
PRINT @Instance
DECLARE @SQLVERSION nvarchar(512)
--:Connect @Instance

SET @SQLVERSION = (SELECT @@VERSION FROM sys.dm_os_sys_info)
PRINT @SQLVERSION
--:CONNECT CMS_INSTANCE
-- UPDATE #Instance_list SET [Instance_Version] = @SQLVERSION WHERE Server_Name = @Instance

 FETCH NEXT FROM Instance_List_cursor INTO @Instance
 END

CLOSE Instance_List_cursor
DEALLOCATE Instance_List_cursor

SELECT * FROM #Instance_list
GO

DROP TABLE #Instance_list

How to connect each SQL instance to get SQL server version updated into the temp table?

Thanks in advanced.


texts:
I am looking for something like this, where I could store the output of sp_who2 in a table, without having to first create the table:

create table test as exec sp_who2


texts: Accidental DBA: Unsure why I have a broken log chain?

texts:
First of all, thanks for having a look at this - We've got a large database in our production environment (1.26 TB), it's got a few hundred corrupt pages in it and has done for months, so the same corruption is in all of the backups available.

I got dragged into this late last week as it seems the scheduled jobs for reorganizing indexes have been failing for some time due to the corruption and we are now at the stage where the indexes on the largest and most commonly used tables range between 50% and 80% fragmentation which is seriously degrading application performance.

I've entertained a number of ideas on how I could remedy this situation (believe me, I'm more than open to alternatives) and from what I've read I think the following sounds like a good idea:

Take a compressed backup of the corrupt database from production (SQL Server 2008R2), copy down to our (local) development server (SQL Server 2016) and run the repair (there isn't enough space on the production system to have two copies of the corrupt db attached concurrently).
Restore two copies of this corrupt backup onto the development server where we do have enough space.
Run the repair down on the development server with the allow_data_loss option for one of these dbs, rename to "DbNameHereRepaired" (most of the corrupt pages that I looked at with DBCC PAGE appeared to have had their data all set to 0x00 anyway).
Rename other copy to DbNameHereCorrupt, attempt to run the page level restore using the following code:

    alter database DbNameHereCorrupt set single_user with rollback immediate

    --set db to FULL recovery mode
    alter database DbNameHereCorrupt set recovery full

    --Declare paths for backups
    declare @fullBackupPath nvarchar(max) = N'D:\Restore\DbNameHereCorrupt-FullBackup.bck'
    declare @tranLogBackupPath nvarchar(max) = N'D:\Restore\DbNameHereCorrupt-LogBackup.bck'

    --Take full backup to begin new TLogChain
    backup database DbNameHereCorrupt to disk = @fullBackupPath with init, differential;

    --Immediately after whilst in single user mode, begin the t-log chain, this will also put the db in a restoring state
    backup log DbNameHereCorrupt to disk=@tranLogBackupPath with init, norecovery;

    --get corrupted pages
    declare @corruptedPages nvarchar(max) =
    (
        select stuff
        (
            (
                select ',' + cast(s.file_id as nvarchar(20)) + ':' + cast(s.page_id as nvarchar(20))
                from msdb.dbo.suspect_pages s

                where s.database_id = 20

                for xml path('')
            ), 1 ,1, ''
        )
    )

    --push page-level restore
    restore database DbNameHereCorrupt
    page=@corruptedPages
    from disk=N'D:\Restore\DbNameHereRepaired.bak'
    with norecovery;

    -- restore log over db
    restore log DbNameHereCorrupt from disk=@tranLogBackupPath
    with norecovery;

    -- put db back into usable state
    restore database DbNameHereCorrupt
    with recovery

    --set db to SIMPLE recovery mode
    alter database DbNameHereCorrupt set recovery simple

    alter database DbNameHereCorrupt set multi_user

This appears to work how I think it's supposed to up until the page-level restore statement, where it errors out saying:

  Msg 4346, Level 16, State 1, Line 35
  RESTORE PAGE is not allowed with databases that use the simple recovery model or have broken the log backup chain.

This is my first time using the full recovery model, so I've probably done something wrong...I can see what looks like a small gap between the last lsn of the differential and the transaction backup, which I'm guessing that there shouldnt be.

Can anyone see what I've done wrong here? I've tried to follow examples on the web that all seem to follow a similar pattern, but I guess I'm missing something?


texts: Creating a relationship between a document and a collection in MongoDB

texts:
I have no idea if this is even possible but I'd like to find out if there's some way to create a relationship between a document and a whole collection. I have a user schema that looks something like this:

var userSchema = new Schema({
    fname: {type: String, required: true},
    lname: {type: String, required: true},
    email: {type: String, required: true, unique: true},
    password: {type:String, required: true}
});

To achieve my desired goal, I'm thinking my schema would need to look a bit like this:

var userSchema = new Schema({
    fname: {type: String, required: true},
    lname: {type: String, required: true},
    email: {type: String, required: true, unique: true},
    password: {type:String, required: true},
    collections: [{type: Schema.Types.ObjectId}] // where ObjectId would be the ID to a collection

});

The problem with this approach is, I have no way to get the ID of a collection. I'm unsure if that's even a thing. Any suggestions? Is this even possible?

Any help is appreciated.


texts: type recognition with MonetDB, *create* tables from CSVs

texts:
I am confused whether you can copy records into a new table in MonetDB.

The official documentation seems to talk only about copying into existing tables. I have quite a large universe of billions of records in dozens of tables, and I would much prefer a solution that inferred the type from the CSV itself, and the column names from the first rows.

A StackOverflow answer seems to work for SQL Server 2000 with OpenRowset, though even they are not concerned about data-type inferencing.

Another answer here on DBA recommends BCP before calling BULK INSERT in Server 2000, neither of which is discussed in the MonetDB documentation.

Other answers also suggest that this is impossible in MySQL without external scripts.

Disclaimer: I am an SQL and MonetDB newbie, but thanks for not letting me miss the obvious.


texts: Indexed view referencing objects on two different schemas

texts:
When I try to create/alter a view to create an index like this

CREATE UNIQUE CLUSTERED INDEX IDX_vSalPopulation
   ON sfdc.vSalPopulation (ID);

I get the following error message

  Msg 1938, Level 16, State 1, Line 40 Index cannot be created on view
  'vSalPopulation' because the underlying object 'YR_TRM_SBTRM_TABLE'
  has a different owner.

When I check the tables I see that the tables are owned by different schemas

exec sp_tables 'dbo.YR_TRM_SBTRM_TABLE'
exec sp_tables 'vSalPopulation'

TABLE_QUALIFIER     TABLE_OWNER       TABLE_NAME              TABLE_TYPE    REMARKS
MyDB                dbo               YR_TRM_SBTRM_TABLE      TABLE         NULL
MyDB                sfdc              vSalPopulation          VIEW          NULL

The documentation on indexed views states that you cannot have an indexed view that references two different databases.

The view must be created by using the WITH SCHEMABINDING option.
The view must reference only base tables that are in the same database as
the view.
The view cannot reference other views. … etc

However, I have the same database but two different schemas. Maybe the problem is actually the third required. While I am not referencing other views, there are functions.  Maybe I misunderstand the error message. Permissions? So, generally, is it possible to have an indexed view that references objects from two different schemas?

A simplified definition of the view that gives me the same error looks like this

ALTER VIEW sfdc.vSalPopulation
   WITH SCHEMABINDING
AS
SELECT DISTINCT
    ID
FROM dbo.CAN
INNER JOIN dbo.YR_TRM_SBTRM_TABLE YTS ON CAN.YR_CDE = YTS.YR_CDE
WHERE YTS.SBTRM_END_DTE > GETDATE()


texts: Postgresql: Permission denied to schema to user created based on master with rights

texts:
I'm trying to write a function that eventually will rotate users. Currently I have the code below which works and creates a new user using the prior user which ultimately has the same rights as the master user, ie, can do everything.

select mysch.dblink('dbname=mydb user=themasteruser password=abc123 connect_timeout=200000 host=localhost', 'CREATE USER newuname WITH PASSWORD ''pass1'' CREATEDB CREATEROLE;GRANT rds_superuser TO newuname;');

DROP SERVER IF EXISTS fs_link_b CASCADE;
CREATE SERVER fs_link_b FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', port '5432', dbname 'mydb ', connect_timeout '200000');
CREATE USER MAPPING FOR newuname SERVER fs_link_b OPTIONS ("user" 'newuname',password 'pass1');
ALTER SERVER fs_link_b OWNER TO newuname;

However, when I make a connection with the above created user and I try to run the query below:

select * from mysch.dblink('link_b', 'select usename from PG_USER limit 1;') as t(uu text);

I get this error:

  SQL Error [42501]: ERROR: permission denied for schema mysch

If I created the user based on a master user, then should it not have all rights as the master user as created above? Or Do I need to do a bunch of individual GRANTS still?


texts: Do I lose query performance by using same filegroup for all partitions?

texts:
Let's say the table is partitioned by Month_Key and I execute a simple

SELECT Columns FROM PartitionedTable WHERE Month_Key=201701.

I am wondering whether there are any disadvantages regarding SQL query performance when storing partitions for a partitioned table in one filegroup vs. multiple file groups?

Then, let's say that I execute multiple queries at the same time, querying different Month_Key values. Would the total execution time be lower if the table partitions are stored in multiple file groups vs. one file group?


texts: Oracle 11g R2 installation on Ubuntu 14.04 LTS

texts:
I have installed Oracle 11g R2 on ubuntu 14.04 LTS.
On terminal I wrote following to start oracle:

sudo service oracle-xe start

on pressing enter, got following output :

" Starting Oracle Net Listener.
  touch: cannot touch '/var/lock/subsys/listener' : No such file or directory
  Starting Oracle Database 11g Express Edition instance.
  Failed to start Oracle Net Listener using /u01/app/oracle/product/11.2.0/xe/bin/tnslsnr
  and Oracle Express Database using /u01/app/oracle/product/11.2.0/xe/bin/sqlplus. "

What should I do now.


texts: Alter schema of Access 2013 database with linked table

texts:
I'm trying to change the schema of an Access 2013 table. I want to lengthen some of the text fields. When I change the schema and try to save I get dialog with Operation is not supported for this type of object, and a note that This property cannot be modified in linked tables. That makes sense. So I go to the relationships and try to delete one of the links. This gives me the error: You can't delete a relationship inherited from a linked database.

I don't know of any "linked databases" (I didn't create the database). Is there a way I can figure out what/where these might be?


texts:
See below example. ID_1 and ID_A are primary keys for table_1 and table_3 respectively. Table_2, the primary key is ID_1 and ID_A. ID_A is joined to one and only one ID_1 via table_2 due to business rules.

I need a query that will return all rows from table_1 where the value_1 = 11 and joined to that, I need only the rows from table_3 that have a value_a = 'a' if it joins to a row in table_1 that has a value_1 = 11.

Please see results required. Note there is no row

|1 | a |

in the result set because value_a for id_a = 'b';

I've tried a left outer join but all ID_1 are returned. I've also tried CTE, to no avail. Here's one SQL, I tried.

Select ID_1, Value_1, ID_A, VALUE_A
   From TABLE_1 t1
   Join TABLE_2 t2 on t1.ID_1 = t2.ID_1
   Left Outer Join TABLE_3 t3 on t2.ID_A = t3.ID_A;

I've also tried using a temporary table which include only the results from TABLE_1 that I need, but the left outer join using the temp table still returns the above mentioned row.

The DB is Oracle, but I prefer a standard solution. Also, performance is a consideration, given that TABLE_1 has over 200 million rows and TABLE_3 has about 4 times that many.

TABLE_1

| ID_1 | Value_1  |
-------------------
| 1    | 11       |
| 2    | 12       |
| 3    | 11       |
| 4    | 13       |

TABLE_2

| ID_1 | ID_A  |
----------------
| 1    | a     |
| 1    | b     |
| 3    | c     |
| 4    | d     |

TABLE_3

| ID_A | Value_A |
------------------
| a    | b       |
| b    | a       |
| c    | e       |
| d    | a       |

Results Required

| ID_1 | ID_A    |  Value_1  | Value_A |
----------------------------------------
| 1    | b       |    11     |    a    |
| 3    | <NULL>  |    11     | <NULL>  |


texts: HierarchyID: Get all descendants for a list of parents

texts:
I have a list of parent ids like this 100, 110, 120, 130 which is dynamic and can change. I want to get all descendants for specified parents in a single set. To get children for a single parent I used such query:

WITH parent AS (
    SELECT PersonHierarchyID FROM PersonHierarchy
    WHERE PersonID = 100
)
SELECT * FROM PersonHierarchy
WHERE PersonHierarchyID.IsDescendantOf((SELECT * FROM parent)) = 1

Have no idea how to do that for multiple parents. My first try was to write something like several unions, however I'm sure that there should be smarter way of doing this.

SELECT * FROM PersonHierarchy
WHERE PersonHierarchyID.IsDescendantOf(
    (SELECT PersonHierarchyID FROM PersonHierarchy WHERE PersonID = 100)
) = 1
UNION ALL
SELECT * FROM PersonHierarchy
WHERE PersonHierarchyID.IsDescendantOf(
    (SELECT PersonHierarchyID FROM PersonHierarchy WHERE PersonID = 110)
) = 1
UNION ALL ...

P.S. Also I found such query to select list of ids which might be helpful:

SELECT * FROM (VALUES (100), (110), (120), (130)) AS Parent(ParentID)

To summarize, my goal is to write query which accepts array of parent IDs as a parameter and returns all their descendants in a single set.


texts:
I have installed postgres (different versions actually) as well as pgadmin3 on windows using the package manager:

https://www.openscg.com/bigsql/package-manager/
https://www.openscg.com/bigsql/docs/pgcli/pgcli/

I also added the bin directories into the path environment on windows. Is there any way I can install program shortcuts to the start menu in windows?


texts: how to add option(recompile) within this stored procedure?

texts:
In my current environment, I have many instances of stored procedures, just like the one shown below, where a bunch of parameters are passed to the procedure, and then within the procedure a select exists is run and based on the result, different logic paths are run within the stored procedure.

I have a couple of questions regarding the procedure below:

1) is it a good candidate for parameter sniffing?

2) how could I add option(recompile) within the code?

There are differences between adding option(recompile) within the code and creating a stored procedure with recompile.

I would be more keen on the option(recompile) if that is possible.

ALTER PROCEDURE [dbo].[usp_upd_activity]
                    @activityId             INT,
                    @title                  VARCHAR(100),
                    @description            VARCHAR(MAX),
                    @inclusions             VARCHAR(MAX),
                    @locationId             INT,
                    @imageUriMain           VARCHAR(255),
                    @uploadToBucket         VARCHAR(200),
                    @path                   VARCHAR(200)
AS
BEGIN

    SET NOCOUNT ON;

    BEGIN TRAN
    BEGIN TRY

        DECLARE @documentId INT

        IF NOT EXISTS (SELECT 1
                         FROM document
                        WHERE activityId    = @activityId)
        BEGIN
            INSERT INTO  document
                        (   [uploadToBucket], [path], [activityId])
            VALUES      (@uploadToBucket, @path, @activityId)

            SET @documentId = SCOPE_IDENTITY();

        END
        ELSE
        BEGIN
            UPDATE  document
            SET     uploadToBucket  = @uploadToBucket,
                    [path]          = @path,
                    activityId      = @activityId
            WHERE   activityId      = @activityId

            SET @documentId =
                (SELECT documentId
                   FROM document
                   WHERE activityId = @activityId)
        END

        UPDATE          activity
        SET             title           =       @title,
                        description     =       @description,
                        inclusions      =       @inclusions,
                        locationId      =       @locationId,
                        imageUriMain    =       IsNull(@imageUriMain, imageUriMain),
                        documentId      =       @documentId
        WHERE           activityId      =       @activityId

        COMMIT

    END TRY
    BEGIN CATCH
        DECLARE     @ErrorMessage   nvarchar(max),
                    @ErrorSeverity  int,
                    @ErrorState     int;
        SELECT      @ErrorMessage = ERROR_MESSAGE() + ' Line ' +
                                    cast(ERROR_LINE() as nvarchar(5)),
                                    @ErrorSeverity = ERROR_SEVERITY(),
                                    @ErrorState = ERROR_STATE();

        IF @@TRANCOUNT > 0
            ROLLBACK;

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH

END


texts: How do you call an external C function from a SQL stored procedure?

texts:
In Teradata 17, I am trying to call their own hash_md5 external function from my own SQL stored procedure:
replace procedure p()
sql security creator
begin
  declare l_hash varchar(32);
  set l_hash = hash_md5('abc');
end;

call p();

I get:

Failed [3706 : 42000] P:Syntax error: expected something between '(' and the string 'abc'.

I can call this function successfully in plain SQL:
select hash_md5('abc');

900150983CD24FB0D6963F7D28E17F72

I tried to pass a parameter in, instead of a hardcoded literal, but it made no difference.


texts:
I've been working on pgpool2 setup with 2 DB node and I need to attach on the node, which os currently down. when I run

postgres@pg-pool:~$ pcp_attach_node -p 9898 -n 1 -U postgres

I get following error.

  pcp_attach_node -p 9898 -n 1 -U postgres pcp_attach_node: error while
  loading shared libraries: libpcp.so.1: cannot open shared object file:
  No such file or directory

I am using:

- pgpool-II-4.0.1
- ubuntu 18.04 LTS
- postgresql 10

I have installed:

postgresql-contrib
gcc
make
libpq-dev

pgpool installation:

./configure
make
make install

Contents of /usr/local/lib

-rwxr-xr-x  1 root root     959 Mar 18 07:55 libpcp.la*
lrwxrwxrwx  1 root root      15 Mar 18 07:55 libpcp.so -> libpcp.so.1.0.0*
lrwxrwxrwx  1 root root      15 Mar 18 07:55 libpcp.so.1 -> libpcp.so.1.0.0*
-rwxr-xr-x  1 root root  244296 Mar 18 07:55 libpcp.so.1.0.0*

Your help would be great


texts: How to store a multi-lingual "dictionnary" in a NoSQL database?

texts:
I hope this is the good Stack to ask this question, pardon me if it's not.

I need to categorize images and need manipulate a lot of words. I'll explain what's bugging me first and then there is a example document below.

For this, I need to store words in multiple languages in a database with their definition, type of word, emotion, etc.

For regular noun, like the month 'september', it is quite easy. But some words are more complicated, like 'shelter'.

Since shelter is a noun and can be a verb to shelter but are related and in french it becomes the noun abri and the verb abriter.

Some french words doesn't make any sense in english, like the verb vouvoyer, sometimes there will be a noun or a verb only in french or only in english.

My current object looks like this:

  {
    word: 'shelter', // act like a key
    type: ['noun', 'verb'],
    tags: ['structure', 'protection'],
    polarity: 'neutral',

    noun: {
      word: 'a shelter',
      definitions: []
    },
    verb: {
      word: 'to shelter',
      definitions: []
    },

    i18n: {
      // Languages
      fr: {
        type: ['noun', 'verb'],
        verb: {
          word: 'abriter',
          definitions: []
        },
        noun: {
          word: 'abri',
          mf: 'm',
          definitions: []
        }
      }
    }
  }

The first issue here is in case of a word that only "exists" in french, I don't know where to put it.
I also think there is a lot of redundant information in this document.

I need to be able to query words by it's type, by it's tags and by language.

Before suggesting what would be the obvious easy (but complicated) solution: RDBMS, I want to use a high-availability database, probably MongoDB (since Cassandra would require me to have multiple dataset of the same data with different keys)

So, how can I store such information properly?

(Thanks!)


texts: What should I index when joining on a table with conditions?

texts:
I have a query like this:

SELECT "main_table".*
FROM "main_table"
INNER JOIN "other_table" ON "other_table"."deleted_at" IS NULL
AND "other_table"."id" = "main_table"."appointment_id"
WHERE "main_table"."user_id" = 1
  AND (other_table.date BETWEEN '2020-02-26 23:29:42.678693' AND '2020-02-27 01:29:42.678739')

You probably know everything you need to about the schema from the query, but let me know any questions.

What are the optimal indexes to make for this?

main_table is easy (right?)

CREATE INDEX ON main_table (user_id);

For other_table, I'm tempted to think "first postgres will 'do the join', then it will 'filter by date'". Which suggests this index:

CREATE INDEX ON other_table (id, date) where deleted_at IS NULL;

But, without creating the indexes and running EXPLAIN, is it unknowable how postgres will go about doing the query, and it might think that it's better to filter by date first, then 'do the join'?

this select will typically match 1–10 rows
for a given date range, there are thousands of rows in other_table
for a given user_id there are thousands of rows in main_table
the results of this query will never have two main_table rows correlating with one other_table row, or vice versa. It's always 1-1.
other_table has about 2/3 of its rows with deleted_at not null


texts: Where should I put nvarchar(max) dimensions in my data warehouse?

texts:
I am designing the ERD for a new data warehouse.

SQL Server 2016
Clustered Columnstore Indexes for the "fact" tables
Loose star schema

I put "fact" in quotes and say "loose star schema" because, with clustered columnstore, I can put many dimensions right into the "fact" table without the typical concerns over row width. Many of my dimensions are going into the "fact" tables.  I am creating some dimension tables and surrogate keys, but only if the dim has attributes in addition to the dim description itself.

This brings me to some very wide, high cardinality, fields that are nvarchar(max). Without going too in-depth, think of these fields as denormalized lists. I need the list denormalized for the grain of one of my data sources.  I do have it normalized in another fact table, but not one I am surfacing in this data source.

Users need these fields to search for keywords in the data mart I am surfacing.  In my current design they are in the clustered columnstore "fact" table. Users will frequently query the fact tables without touching the nvarchar(max) fields.

Is there a place to put wide dimensions into my data warehouse that is more correct than the clustered columnstore table?

Joe Obbish informed me that we cannot currently put nvarchar(max) into CCIs. Would it be the best practice for me to create a table of LOBs as an extension of my "fact" table?

We may add other languages in the future. Currently and for the foreseeable term, the nvarchar column contains just English.


texts:
I have a mature 50+ tables web application based on mySQL. In order to do some advanced data mining I want to use Neo4j and the goodnes of cypher. However I'm having a hard time migrating my data from RDBMS to Neo4j. I don't wan't to do it by hand, because this is generally unmaintainable.

I use pentaho which supports the Neo4j JDBC, however I'm having a hard time wrapping my head around loading the extracted data to Neo4j since the GUI is clearly designed for RDBMS and there are no sources about how to do it. Had anyone run into a similar problem and found a solution?


texts: Restarting SQL Server Agent on a cluster installation

texts:
I've just enabled the mail profile in SQL Server Agent and I need to restart the service for it to take affect but the Agent is running as part of a SQL Server cluster and I don't want the cluster manager to think there is a problem and fail-over all of the services to the other node.

What is the correct way to restart the SQL Server Agent on a SQL Server instance that is clustered?


texts: How to inspect global temp table from outside the transaction it is created in

texts:
To help debugging a batch of SQL (that is run inside a transaction), inside the transaction I dump some data into a global temporary table. The global temporary table is created inside the transaction (I have no choice about that for reasons I am not going into here).

I thought I might be able to select from the temp table from outside the transaction (i.e. under a different connection) by using "with(nolock)". However, the select is blocked from completing.

Is there any way to select from the temp table from outside the transaction?


texts: MySQL won't use an index on an int, if I provide a string

texts:
Mysql randomly refuses to use indexes for even simple queries. 1 example  from slow-query log:

  # Time: 130904 12:19:59
   # User@Host: cron[cron] @  [10.71.41.38]
   # Query_time: 58.488760  Lock_time: 0.000029 Rows_sent: 0  Rows_examined: 13634899
  SET timestamp=1378311599;
  UPDATE dial_list set status='AB' where lead_id = '3335602076';

This is a MYISAM table with lead_id as primary key

| Field         | Type             | Null | Key | Default     |Extra                   |
| lead_id       | int(9) unsigned  | NO   | PRI | NULL        | auto_increment         |

The same query run on an identical server with similar data size uses the index. Also, not all such queries on this server turn up as table scans. What could be the problem?


texts: What would cause Mysql database entries to be truncated to 253 characters?

texts: Randomly seeing database entries truncated to 253 characters on a php site and can't figure out why or if it could be malicious or a corruption issue.  Any thoughts would be appreciated.


texts: Understanding comparison operators on columns

texts:
Consider this dummy_data:

number

1

2

3

4

5

I want to return all the rows from dummy_data where column number is below a certain value.
Came up with this:
WITH
  dummy_data AS
  (
    (SELECT 1 AS number) UNION (SELECT 2) UNION (SELECT 3) UNION (SELECT 4) UNION (SELECT 5)
  )
  , some_selection AS
  (
    (SELECT 3 AS number) -- in my use case, this query might return nothing...
    UNION
    (SELECT 0 AS number) -- ...so I'm using the union to 0 to sort of coalesce.
  )
SELECT
  *
FROM
  dummy_data
  , some_selection
WHERE
  dummy_data.number < some_selection.number
;

Which returns:

number
number

1
3

2
3

So this seems to work like I intended, but I don't understand enough what's going on to know if I can rely on this code.
Can I be guaranteed that the comparison is always based on the max value of some_selection.number? Or should I add an intermediate step to explicitly extract max(some_selection.number)?
i.e. is the comparison based on the whole column or just on a single semi-random cell?
i.e. I'm worried the comparison might at times be performed on < 0 which would return very different results.
It's probably in the Postgres manual somewhere, but I could not find the relevant section.


texts: MDX - Sum values greater than then a median calculated measure

texts:
I'm relatively new to SSAS / MDX. I have a cube with a calculated field which calculates the Median value for a range of prices.

MEDIAN( [DimItems].[Dim Items Id].CurrentMember.Children, [Measures].[Price] )

Included as a measure is a quantity field. I need to calculate the sum of that quantity field where the raw price is greater than, equal to, and less than the calculated Median price.

Take the following data:

Qty Price
183 $100
51  $114
420 $123
80  $137
12  $144
200 $147
51  $152

The Median price is $137. Therefore, to get the sum of the quantity where the Price is less than the Median, I need to sum up the following Qty values (183,51,420) since $100, $114, and $123 are all less than $137. The value of this summation is 654.

The problem is I have no idea how to do this. I cannot figure out how to compare a raw value (eg $114) with a Median value over a range (eg $137). Any help would be appreciated.


texts: Error restoring backup in sql server. Old server version

texts:
When trying to restore a backup file (.bak) to SQL Server installed on my machine, I get the following error (steps that I follow to restore are: Right Click Database ---> Tasks ---> Restore)

Error message:

  The database was backed up on a server running version 10.50.2500.
  That version is incompatible with this server, which is running
  version 10.00.2573. Either restore the database on a server that
  supports the backup, or use a backup that is compatible with this
  server.

I have SQL Server 2008 R2 on my machine and under Help --> About, I see the following:

Microsoft SQL Server Management Studio    10.50.1600.1
Microsoft Data Access Components (MDAC)    3.85.1132

Question: I already have Service pack 2, what should I down and from where to be able to restore my back up.


texts: Can I control which algorithm SQL Server uses to hash symmetric key passphrases

texts:
I will start by describing the issue / cause before asking my question:

If you are using a symmetric key in SQL to encrypt data.
This can be set up as follows:

CREATE MASTER KEY ENCRYPTION BY PASSWORD='password';
CREATE CERTIFICATE My_Certificate AUTHORIZATION [dbo] WITH SUBJECT = 'My Certificate'
CREATE SYMMETRIC KEY My_Key AUTHORIZATION [dbo] ENCRYPTION BY PASSWORD='CertificatePassword' WITH IDENTITY_VALUE = 'My Key', ALGORITHM = AES_256, KEY_SOURCE = '12345-AABB-1234-BBAA' ENCRYPTION BY CERTIFICATE My_Certificate;My_Certificate

This can then be used with the de/encryptbykey function(s) to encrypt data or vise-versa.

The issue is that data encrypted on SQL Server 2016 (or a previous version) is not accessible if moved to SQL Server 2017 (after re-applying the certificate that is).

This looks to be an issue with SQL Server 2017, See: https://support.microsoft.com/en-us/help/4053407/sql-server-2017-cannot-decrypt-data-encrypted-by-earlier-versions

And the suggested fix is to ensure you have CU2 installed and to globally enable trace-flag #4631

My question is:  Is it possible (and if so how) to specify which hashing algorithm should be used.

For instance, can I tell SQL to use sha2_256 for example when encrypting my certificate?


texts: High load on server because of one query

texts:
I have one query running from last stable version of wordpress on causing high load on my centos 6.7 server

I enabled slow query log thought /etc/my.cnf file to record which sql cause this problem and i found this one

# User@Host: database_Swhf[database_Swhf] @ localhost []
# Query_time: 5.129712  Lock_time: 0.000107 Rows_sent: 0  Rows_examined: 331084
use database_Swhf;
SET timestamp=1450396105;
SELECT SQL_CALC_FOUND_ROWS  wp_frposts.ID
FROM wp_frposts
  INNER JOIN wp_frterm_relationships
    ON (wp_frposts.ID = wp_frterm_relationships.object_id)
WHERE 1=1
  AND wp_frposts.ID NOT IN (37615)
  AND (
          (
            SELECT COUNT(1)
            FROM wp_frterm_relationships
            WHERE term_taxonomy_id IN
                (192471,192472,192473,192474,192475,
                 192476,192477,192478,192479,192480,
                 192481,192482,192483,192484,192485,
                 192486,192487,192488,192489,192490,192491)
            AND object_id = wp_frposts.ID
          ) = 21
      AND
          (
            SELECT COUNT(1)
            FROM wp_frterm_relationships
            WHERE term_taxonomy_id IN (34)
            AND object_id = wp_frposts.ID
          ) = 1
      AND
          wp_frterm_relationships.term_taxonomy_id IN (13)
      )
  AND wp_frposts.post_type = 'post'
  AND (wp_frposts.post_status = 'publish')
GROUP BY wp_frposts.ID
ORDER BY wp_frposts.post_date DESC
LIMIT 0, 4;

I don't know how to fix this query and make it stop causing these problems to my server.


texts: best way to create multiple similar tables - CTAS vs INSERT

texts:
I am creating a few dozen tables as CREATE TABLE AS SELECT (CTAS).  The only difference in the SELECT statements is specifying a different value in the WHERE clause for TEAM_ID.  I'm an Oracle newbie and thought I could use bind variables to speed up the process, but those are not allowed in DDL statements.

My data is quite large - 500+ million rows.

What is my best option for speed?  Currently we are using nologging on the CREATE TABLE statement.  Would it be better to explicitly create the table and INSERT with the /*+ append */ hint and bind variables?  Or do the gains of nologging outweigh the gains of bind variables?

(I am sure this is an "it depends" scenario, but I don't even know what it depends on...)


texts: Oracle EXP Error ORA-12704: character set mismatch

texts:
I need to duplicate a Oracle 11g Express Edition database from one computer to
another computer.

I tried exp sys/password FULL=Y FILE=backup.dump (the idea is to do a imp on the other pc)

But it gaves me the following error:

EXP-00008: ORACLE error 12704 encountered
ORA-12704: character set mismatch
ORA-06512: at "SYS.DBMS_SERVER_ALERT_EXPORT", line 10
ORA-06512: at "SYS.DBMS_SERVER_ALERT_EXPORT", line 118
ORA-06512: at line 1

How can I make a full, complete backup with usernames, passwords, everything to be loaded into another host?


texts: Postgres: Missing data after Binary Copy - Is VACUUM FULL required?

texts:
I'm running Postgres 9.0 on a relatively big (10TB) and old Database. I must move that database to new server hardware, so I copied the whole $PGDATA directory via rsync of a LVM Snapshot and used archive/restore Commands to get all the changes after the copy process. I switched to the new server using the trigger_file.

Everything looked good so far, but after a few hours I'm having some tables where data is not showing up in SELECTs (I think that was instant after copying, but have no evidence).

I did ANALYZE and VACUUM on the table - no data there. But when I do a VACUUM FULL on the table, the data appears.

I couldn't find anything comparable in the postgres documentation except the Transaction Id wraparound. The autovacuum was disabled for 1/2 year at the old server.

A week ago I activated it and autovacuum did its job as I saw.

On the old database server, the data from my test table is is returned by SELECTs.

Did anyone encounter such a problem? I'm looking for a query that can show me which of the tables needs such a vacuum full.

Why cannot I copy the data directory at the file system level is also hard to understand for me at the moment. Did I miss anything from the documentation that needs to be done after using the Trigger File?

I don't think it's relevant, but the version at the old server was 9.0.18 and at the new one 9.0.23.


texts:
I am trying to find the superkeys of this relation, but I am having troubles finding out how many superkeys there are and exactly what they are. I figured out that the candidate keys were {A},{B},{C},{D}.

Here is the relation:

R(A,B,C,D)

Functional Dependencies:

A->B
B->C
C->D
D->A

Candidate keys: {A},{B},{C},{D} (from what I figured out)

Can someone please help me find the superkeys, and how exactly to find them?


texts:
I have a vague understanding of transactions that it means you can help the server understand that a series of queries are related.

Scenario : Suppose I have a series of queries which creates a user - inserts a user info record, creates an empty profile record and initialises a few other records etc..

If for whatever reason the script were to fail half way through this, what do I need to do to ensure that everything to do with the "creating a user queries" gets undone?

From what I understand reading the MySQL documentation, it as simple as using START TRANSACTION and COMMIT statements.

If that is so, why do so many CMS's neglect to do so? I suppose a common argument against it might be that it is unnecessary for short scripts dealing with only a few record updates, but am I not right in thinking that it is also highly beneficial when you are dealing with replication environments too?


texts: Best practise how to sync SQL Server 2014 database with other database

texts:
What is best practise if we have application using a SQL Server database and we would like to have backup if SQL Server does not work?

Our scenario:

We would like to use our main SQL Server database
If main SQL Server does not work, we would like to use a different SQL Server database with the same data.
If the main SQL Server works again, we need to sync data back to the main database
There is not possible generate same data, because every record use UID.
We can use SQL Server 2014 or SQL Server 2012.

Failover Cluster is option, but are they any other options?

May there will be help from some web pages where are those situations described and recommend what is best in each situation.


texts:
I am running into an issue while trying to join two MySQL tables and getting output.

My first table named res having 14 columns related to trains bookings. It has all the information of all trains with their date of Journey, available tickets, and their fares.

Table definition for rest .

CREATE TABLE  `abcd`.`rest` (
`PNR` INT NOT NULL AUTO_INCREMENT ,
`Tr_Num` INT( 6 ) NULL ,
`Train_Name` VARCHAR( 60 ) NULL ,
`na` VARCHAR( 60 ) NULL ,
`age` INT( 3 ) NULL ,
`DOJ` DATE NULL ,
`seat` VARCHAR( 20 ) NULL ,
`src` VARCHAR( 60 ) NULL ,
`des` VARCHAR( 60 ) NULL ,
`class` VARCHAR( 60 ) NULL ,
PRIMARY KEY (  `PNR` )
) ENGINE = INNODB;

Table rest

This table has 10 columns

PNR=> Auto Increment. Primary. Int value
Train Number => Integer. Unique
Train Name => Varchar. Unique
Name of the passenger=> Varchar
Age => Int Value
date => date of Journey
Seating => Varchar
src => varchar
des => Varchar
class => varchar (ACI,ACII,ACIII,SL)

Table res and their attributes:

id=> Auto Increment.Primary Key
Train Number => Integer
Train Name => Varchar
src => Varchar
des => Varchar
date => date of Journey
AC_I => No of Tickets.Integer value
First_AC => Fare
AC_II => No of Tickets.Integer value
SEC_AC => Fare
AC_III => No of Tickets.Integer value
THIRD_Ac => Fare
SL=> No of Tickets for Sleeper Class.Integer value
SL_CLASS => Fare

The SQL command that I have used and was able to get something out of it but was not completely successful.

select rest.PNR, res.TRAIN_NUMBER,res.Tr_name,rest.na,rest.age, rest.doj,
rest.seat, rest.src, rest.des,rest.class, case when rest.class = 'AC I'then res.AC_I
  when rest.class = 'AC II' then res.AC_II
when rest.class = 'AC III' then res.AC_III
when rest.class = 'Sleeper Class' then res.SL_CLASS
end as Tickets
from rest, res where rest.DOJ = res.date order by PNR desc limit 2;

After running this command I get the output as below.

+-------+----------+----------+------+------+----------+------+------+------+------+--------+
| PNR   | TRAIN_NR | Tr_name  | Name | Age  | doj      | seat | src  | des  |class | Tickets|
+-------+----------+----------+------+------+----------+------+------+------+------+--------+
| 40    |  10008   |  Mum     |  S   |  42  |2018-04-27|  LB  |  M   |  H   | AC I |  480   |
+-------+----------+----------+------+------+----------+------+------+------+------+--------+

Now, What I am trying to do is along with this output I want to know fetch the Fare of the ticket.

I am expecting an output something like this.

+-------+----------+----------+------+------+----------+------+------+------+------+--------+--------+
| PNR   | TRAIN_NR | Tr_name  | Name | Age  | doj      | seat | src  | des  |class | Tickets| fare   |
+-------+----------+----------+------+------+----------+------+------+------+------+--------+--------+
| 40    |  10008   |  Mum     |  S   |  42  |2018-04-27|  LB  |  M   |  H   | AC I |  480   | ???    |
+-------+----------+----------+------+------+----------+------+------+------+------+--------+--------+

How can I achieve this?


texts: How to move from 32bit SQL Server install to 64bit?

texts:
A production server has accidentally been installed with SQL Server 2012 32bit version, while it needed a 64bit version.

How best to fix this issue?

Is it possible to simply UPGRADE from 32 to 64 bit version?

Should I just install a new instance of 64 bit version of SQL Server 2012 and then simply restore the backups?


texts:
A piece of, um, software seems to be issuing ~20k queries of this form every few seconds:

DELETE FROM macVendor WHERE companyId=N'  08-00-'; INSERT INTO macVendor (companyId,organization) VALUES (N'  08-00-',N'TEKTRONIX INC.');

I don't know why, and no the values don't change...

Anyway, it really messes up the output of dm_exec_cached_plans so I figured I'd create a plan guide to force parameterization:

EXEC sp_create_plan_guide @name = N'[PlanGuide-Stop-The-macVendorMadness]',
    @stmt = N'DELETE FROM macVendor WHERE companyId=@0; INSERT INTO macVendor (companyId,organization) VALUES (@0,@1);',
    @type = N'TEMPLATE',
    @params = N'@0 nvarchar(4000),@1 nvarchar(4000)' ,
    @hints = N'OPTION(PARAMETERIZATION FORCED)'

It seems however that plan guides can't be created for multiple statements?

Msg 10506, Level 16, State 1, Line 1
Cannot create plan guide 'PlanGuide-Stop-The-macVendorMadness' because parameter @stmt has more than one statement.

Any hints/help would be much appreciated!

Update: I've created 2 plan guides (DELETE/INSERT) and they were used but only ~17k times (each) out of the 110k executions.


texts: Debit Credit Balance calculating from previous records with Pagination

texts:
I have a table which contains the following data:

ID      In       Out
1      100.00    0.00
2       10.00    0.00
3        0.00   70.00
4        5.00    0.00
5        0.00   60.00
6       20.00    0.00

Now I need a query which gives me the following result:

ID      In       Out    Balance
1      100.00    0.00   100.00
2       10.00    0.00   110.00
3        0.00   70.00    40.00
4        5.00    0.00    45.00
5        0.00   60.00   -15.00
6       20.00    0.00     5.00

Is it possible to do this with one query, without using a trigger or temp variables or stored procedures? Also will work with pagination on table/grid.

I made this question from this i still don't have the answer for this.


texts: Why Turning ANSI_WARNINGS Off Causes Error Updating Col Used as a Source of a Computed Col?

texts:
I have a stored procedure that updates the source column used in a computed column.

That stored procedure has SET ANSI_WARNINGS OFF;.

When the procedure executes, and tries to update the source column, the following error message is received:

  Msg 1934, Level 16, State 1, Procedure my_PROC, Line XX
   UPDATE failed because the following SET options have incorrect settings:
   'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views
   and/or indexes on computed columns and/or filtered indexes and/or query
   notifications and/or XML data type methods and/or spatial index operations.

The error stops when either:

We stop turning ANSI_WARNINGS off, or;
The source column (the one used in deriving the computed column) is removed from the update statement.

There is one clustered index and one non-clustered index, both on columns which are not involved in any way in the computed column definition.

What is it about the column being used in a computed column that causes this error?


texts: Postgresql Cost-Based Vacuum Delay parameters

texts:
How to set the these parameters on postgresql.conf ?
What I should consider while setting them?
What should I monitor?

#vacuum_cost_delay = 0ms
#vacuum_cost_page_hit = 1
#vacuum_cost_page_miss = 10
#vacuum_cost_page_dirty = 20
#vacuum_cost_limit = 200

is there any good tutorial or example?


texts: SQL Server 2008 Triggers and Merge Replication

texts:
I am trying to have a trigger fire when the Merge agent inserts or deletes data into / from a table. It is supposed to insert data into a report table for use with SSRS.

I DO NOT have the "NOT FOR REPLICATION" set in the trigger. Here is the trigger code:

ALTER TRIGGER [dbo].[MyTable_INSERT]
ON  [dbo].[MyTable]
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO...
END

I have tried FOR INSERT and AFTER INSERT, neither seem to work.

The table structure is usual parent / child, I have the trigger set on the child to make sure the parent is already created. The relationships are secured with FK's.

I'm sure am overlooking something simple. Any ideas ?


texts: What is the meaning of Microsoft Compatibility Level?

texts: Setting cmptlevel to 80 on a SQL-Server 2005 inhibits the use of PIVOT and APPLY, but doesn't affect the use of Analytical Functions. Is there any rational behind this?


texts: index maintenance strategy if few insertion points relative to # of rows

texts:
In SQL Server 2008 R2, I have a nonclustered covering index on multiple tables with 100M+ rows. The table has a few thousand "insertion points" where all new inserts happen. This means that regardless of fill factor, I'll quickly end up with page splits and fragmentation at every insertion point, and no fragmentation or splits anywhere else in the table.  Unfortunately, queries always include new rows and hence fragmented areas of the index.

what happens when there's a page split but inserts continue sequentially after the split? Is there a way to tell SQL Server to do the split with lots of extra room for subsequent inserts, without wasting space on existing pages with a large fill factor that for most pages will never be filled?
what are good index maintenance strategies to use for indexes like this?
is there a good automated way to identify tables like this where fragmentation is severe but not uniform? These tables don't show up as more than 5% fragmented overall.
are there index schema changes I should be considering?

Here's more info about the problem. The indexes all look like this pattern (simplifying for clarity below) :

CREATE TABLE Foo (
    id int identity(1,1) PRIMARY KEY CLUSTERED,
    foreign_key int,
    log_time datetime,
    ...)
CREATE NONCLUSTERED INDEX on Foo (foreign_key, log_time) INCLUDE (...)

Queries on this table are always in this form:

WHERE log_time > getdate()-70 AND foreign_key IN (select ...)

Other facts:

there are about 5,000 foreign_key values, each with 10,000's of rows for each.
average row size is 55 bytes, meaning around 150 rows per page
the IN filter usually includes 10%-50% of foreign_key values rows and the date filter includes 20%-40% of the rows. The average is about 15% of total rows selected.
the index is a covering index for the queries, so no clustered index access is needed.


texts: How to prevent inserting same value in two columns in one Table?

texts:
I'm trying to solve problem which will compare BEFORE INSERTING columns values partnerCode and partnerCodeOwner1 with username in the table below, If partnerCode and partnerCodeOwner1 values same with username should error message And insert shouldn't be completed!
My attempt below but incorrect!

Is it possible to implement such condition?

$db = mysqli_connect('localhost', '*******', '********', '***********');

      $user_check_query = "SELECT partnerCodeOwner1 FROM refer where  randomfield='$partnerCode'";
  $result = mysqli_query($db, $user_check_query);
  $row = mysqli_fetch_array($result, MYSQLI_ASSOC);

// --------------------HERE I'm trying to check for the same value in columns--------
//But incorrect! Syntax error?

    if ($row) { // compare columns But incorrect
    if ($row['username'] === ['partnerCodeOwner1']) {
      array_push($errors, "U can't invest to yourself");
    }

    if(empty($row['partnerCodeOwner1'])) {
        $query = "update refer
                    set partnerCodeOwner1= '" . $_SESSION['username'] . "'
                where randomfield='$partnerCode' ;";
    }else {
        $query = "update refer
                    set partnerCode= '" . $_SESSION['username'] . "'
                where randomfield='$partnerCode'  ";
    }

    mysqli_query($db, $query);
}
?>

Example

table: refer

  ID      randomfield    username      partnerCode    partnerCodeOwner1
+-------+ ------------+-------------+-----------------+-----------+
|   1   | A45scdv     |  John       |  Not same       |    John   |
+-------+-------------+-------------+-----------------+-----------+
|   2   | das654d     | Alex        |  Alex           | Not same  |
+-------+-------------+-------------+-----------------+-----------+


texts:
USE tempdb ;
GO

DROP TABLE tbl ;
GO

CREATE TABLE tbl
(
    i SQL_VARIANT NOT NULL
) ;
GO

INSERT INTO tbl (i)
    VALUES (1) ;
GO

SELECT i FROM tbl ;
GO

DBCC IND ('tempdb','tbl',-1) ;
GO

DBCC TRACEON (3604) ; -- Page dump will go the console
GO

DBCC PAGE ('tempdb',1,157,3) ;
GO

Record Size = 17B
30000400 01000001 00110038 01010000 00
TagA = 0x30       = 1B
TagB = 0x00       = 1B
Null Bitmap Offset    = 0x0004    = 2B
Column Count      = 0x0001    = 2B
Null Bitmap       = 0x00      = 1B
Variable-Length Columns Count = 0x0001    = 2B
Variable-Length Column Offset Array  = 0x0011     = 2B
This is the sql_variant encoding for integers...I guess = 0x0138 = 2B
Our integer column    = 0x00000001 = 4B

    SELECT
      SQL_VARIANT_PROPERTY(i , 'BaseType') AS BaseType      -- SYSNAME NVARCHAR(128)
    , SQL_VARIANT_PROPERTY(i , 'Precision') AS [Precision]  -- INT
    , SQL_VARIANT_PROPERTY(i , 'Scale') AS Scale            -- INT
    , SQL_VARIANT_PROPERTY(i , 'TotalBytes') AS TotalBytes  -- INT
    , SQL_VARIANT_PROPERTY(i , 'Collation') AS [Collation]  -- SYSNAME NVARCHAR(128)
    , SQL_VARIANT_PROPERTY(i , 'MaxLength') AS MaxLength    -- INT
FROM
      tbl ;
GO

My questions:

0x3801...what is that
i = 328792402 Huh?  Where did this come from?
SQL_VARIANT_PROPERTY() says I have an integer. Why does it not choose tinyint?
Does anyone know where SQL_VARIANT_PROPERTY() can be found. Must I use the DAC to get at it?


texts: what is the best approach to send report by Email?

texts:
I've been requested to create some sql server reports ( create a report and send them by email ) for some poeple. I thought it would be like 2 or 3 but now that they know I can do it, every week I need to create 1 or 2 more reports.
I use SQL Agent and some steps to create a table, populate with the desired results, then I send it inside a xls file. Thats really not a problem, But I would like to know what can be better used for this task, since now I have more then 20 jobs on my SQL Agent. its all well organized and etc but still, I dont like to be looking at it. I already have a lot of DBA jobs and I dont want to mix things.
Is reporting services a viable way to do this? can I normally create a query, them send it to an excel file and send it by email without using c# or visual studio?


texts: SQL Server 2005 database log size increasing drastically?

texts:
My friend works in a company (pretty small in size) that is running SagePro ERP which has a SQL server 2005 database. Now the problem is, the database size (over the period of 7 years) has increased to 9 GB which is nothing really but the log file of the database is increasing drastically day by day and has now reached 124 GB.

He is worried that eventually their server might run out of storage if the log size keeps increasing. Upon googling, I found the "shrinkfile" method.

The question is why is the log file keeps increasing? How do I stop it? is "ShrinkFile" method the best method (I believe its not a permanent solution)?

P.S. They daily take full back-up of database.


texts: Speed up count queries on a couple million rows

texts:
Assuming a db full of products. A product can belong to exactly 1 collection and is created by a user. Rough scale of the db:

Products: 52.000.000
Collections: 9.000.000
Users: roughly 9.000.000 as well

I am trying to retrieve the amount of products+collections a user has, and the amount of products inside each collection (this information is supposed to get generated all x days and indexed in ElasticSearch) .

For the user query, I am currently doing something like this:

      SELECT
        users.*,
        (SELECT
          count(*)
        FROM
          products product
        WHERE
          product.user_id = user.id
        ) AS product_count,
        (SELECT
          count(*)
        FROM
          collections collection
        WHERE
          collection.user_id = user.id
        ) AS collection_count
      FROM
        users user

all *_id fields are indexed. Using explain(analyze, verbose) (sensitive information removed):

 Limit  (cost=0.00..156500.97 rows=100 width=41) (actual time=0.064..28345.363 rows=100 loops=1)
   Output: (...), ((SubPlan 1)), ((SubPlan 2))
   ->  Seq Scan on public.users user  (cost=0.00..14549429167.11 rows=9296702 width=41) (actual time=0.064..28345.241 rows=100 loops=1)
         Output: (...), (SubPlan 1), (SubPlan 2)
         SubPlan 1
           ->  Aggregate  (cost=1415.84..1415.85 rows=1 width=0) (actual time=261.101..261.102 rows=1 loops=100)
                 Output: count(*)
                 ->  Bitmap Heap Scan on public.products product  (cost=7.32..1414.95 rows=355 width=0) (actual time=0.282..260.767 rows=382 loops=100)
                       Output: (...)
                       Recheck Cond: (product.user_id = user.id)
                       Heap Blocks: exact=32882
                       ->  Bitmap Index Scan on products_user_id_index  (cost=0.00..7.23 rows=355 width=0) (actual time=0.165..0.165 rows=382 loops=100)
                             Index Cond: (product.user_id = user.id)
         SubPlan 2
           ->  Aggregate  (cost=149.13..149.14 rows=1 width=0) (actual time=22.333..22.333 rows=1 loops=100)
                 Output: count(*)
                 ->  Index Only Scan using collections_user_id_index on public.collections collection  (cost=0.43..149.02 rows=44 width=0) (actual time=0.610..22.300 rows=28 loops=100)
                       Output: collection.user_id
                       Index Cond: (collection.user_id = user.id)
                       Heap Fetches: 2850
 Planning time: 0.214 ms
 Execution time: 28345.508 ms

When timing the read queries:

LIMIT 1: 0.695ms
LIMIT 10: 10434ms
LIMIT 100: 150471ms

As the query times become unusably slow when retrieving more than a couple of rows, I am wondering if it is possible to speed this up a bit.

If I were to beef up the DB machine, would adding more CPUs help? AFAIK postgres doesn't execute queries on multiple cores so I am not sure how much that would help.

(Also slightly related, but how come the count() for collections uses a index only scan, while products uses a bitmap heap scan instead?)


texts: SQLite - Should I add some indexes even if select queries are fast?

texts:
I have a SQLite DB with only one table of size ~1.5 MB (in fact, there are ~30 tables in total, but each table is stored in a separate .db file).

When I use EXPLAIN QUERY PLAN for any table, it shows a full table scan, which as far as I know is not good. However, non of the tables have index and also, the speed of select queries are fast.

So, I was wondering whether I should add some indexes on our tables or just keep them as they are? (Now, tables have max 5k rows and in future they may go maximum up to 100k rows).

PS. number of selects and inserts are almost the same..

All ideas are highly appreciated.


texts: Replication error after server name change

texts:
I have changed the computer name of the server where SQL Server 2008 R2 is installed.

Old name was WIN-OUJEKRMGXXX
New server name is KASURDYYY

When I try to configure replication, the following error occurs:

  SQL Server Replication requires actual server name to make a connection to the server ... Specify the actual server name, WIN-OUJEKRMGXXX

I updated the server name in SQL Server in order to try to resolve the issue. I ran the following queries:

execute sp_dropserver 'WIN-OUJEKRMGXXX';
execute sp_addserver 'KASURDYYY', 'local';

...but the same error still occurs. I checked the server name :

select @@SERVERNAME -- returns WIN-OUJEKRMGXXX
select SERVERPROPERTY('SERVERNAME') -- returns KASURDYYY


texts: SQL Server full text search query takes way too long to execute

texts:
I've got query snippet as follows:

SELECT *
FROM CONTAINSTABLE(dbo.Analysis, *, 'FORMSOF(FREETEXT, "historic year-on-year exchange rates forecast year-on-year exchange rates")');

I'm running it on SQL Server 2014.

However it executes forever, so I just give up and cancel it.

If I remove words year-on-year it works just fine.
FTCatalog was rebuilt - didn't help.

This has been driving me crazy for a while now.
I dont't have a lot of experience with FTS searches, but this just doesn't make any sense.

Please advice how to make it work or where I should start looking at.


texts: How to sync MySQL database between two servers

texts:
I need to sync a MySQL database between two servers.

If a new record will be added in server A, then server B fetch new record.

If a new record will be added in server B, then server A fetch new record.


texts: In a DacPac deployment, if a stored procedure that is called by another stored procedure is updated, is the calling stored proc also redeployed?

texts:
I noticed that, in my DacPac, after a deployment, two stored procs were given a modified date on sys.objects, almost to the same millisecond. However, I would only expect one of them to have this, since I only updated one proc. The other proc that was updated calls the one that was updated, but I didn't modify it directly.

Has anyone seen this behavior? Is it expected with DacPac, that the calling stored proc gets "modified" even though there is no actual code change, or could it be something else?

My concern is for a number of reasons, mainly related to QA people questioning this, and ultimately it isn't a good idea to see a proc has been modified when you can't explain why.

Any help appreciated.


texts:
So I am just started doing stuff resembling full-blown Oracle DBA work recently, so am still learning much of the basics of the administration part of my job. I have been tasked with bringing multiple application databases up to meet a certain set of professional standards (I'm being intentionally vague). One of these standards is that owners of objects supporting the application by and large should not also be regularly utilizing them. The major exception being DBA, but then only for certain classes of objects that we would regularly be maintaining such as indexes. Other than when they are doing installation and maintenance tasks, the application object owners should be disabled.

This finally brings me to my question: who should be owning these application objects. Should we just have a user who creates the objects and then is largely disabled for the rest of its life and the requisite usage privileges be given to this or that role? Is it fine for the DBA to own all of the application objects, or should they just own those objects that are being maintained on a daily/weekly basis? Obviously the exact answer will depend the organization's needs and what specifically this standard is asking, but what is the best practice here for application object ownership?

This question may be too broad, so let me know if I need to edit it to make it more specific. I just don't really know where to start with this or even what the right questions to ask are. Thanks for any help you can provide.


texts: Understanding how relationship sides works

texts:
So, I have this relations on my tables (see image) and I need to understand some things. I'll enumerate what I understand and what I need from yours is to say if I'm right or not and in case I'm not give me the good answer.

If general_orders_id is FK on natural_person and legal_person tables if I set onUpdate and onDelete to CASCADE what this mean?

1- When I delete a general_orders the cascade will delete all the records on natural_person and legal_person tables?

2- When I delete a natural_person what happen with the general_orders that points to that person?

In order to maintain a good table structure, data consistency and integrity, what are you're suggestions regarding this topic?


texts:
I have worked with PostgreSQL, primarily using the Django ORM (started with MySQL and migrated to PostgreSQL) and using pgAdmin for maintenance tasks.

I am now working on an open source project developing an alternative Q&A engine, ( Codidact.org if anyone is interested). We will be using PostgreSQL but are still working out a lot of details and no ORM has been selected yet.

Can anyone recommend a way to iteratively develop a database structure - tables, fields, FK relationships, etc. - in a way that would make it easy to produce both:

A development (detailed) and/or management (higher level) diagram of the functional components of the database.
CREATE TABLE commands compatible with PostgreSQL.

The goal is to have a schema definition that effectively generates both human-useful (diagram - i.e., not just "read the SQL code") and machine-readable code.


texts: XML dump of MySQL database don't include foreign key constraints. What can be the reason? sqldump(.sql) file list it

texts:
I am accessing schema information of databases in XML format. I am taking dump of the database in XML format using the command below.

mysqldump --no-data --xml -u root -p bakerydb > bakerydb.xml

these dump don't list the foreign key constraints. Below part of the output is listed.

   <table_structure name="class_details">
        <field Field="id" Type="int(10)" Null="NO" Key="PRI" Extra="auto_increment" Comment="" />
        <field Field="course_id" Type="int(10)" Null="NO" Key="MUL" Extra="" Comment="" />
        <field Field="start_date" Type="date" Null="NO" Key="" Extra="" Comment="" />
        <field Field="end_date" Type="date" Null="NO" Key="" Extra="" Comment="" />
        <field Field="tutor_id" Type="int(10)" Null="NO" Key="MUL" Extra="" Comment="" />
        <key Table="class_details" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="id" Collation="A" Cardinality="0" Null="" Index_type="BTREE" Comment="" Index_comment="" />
        <key Table="class_details" Non_unique="1" Key_name="fk_cources_offered_class_details" Seq_in_index="1" Column_name="course_id" Collation="A" Cardinality="0" Null="" Index_type="BTREE" Comment="" Index_comment="" />
        <key Table="class_details" Non_unique="1" Key_name="fk_employee_details_class_details" Seq_in_index="1" Column_name="tutor_id" Collation="A" Cardinality="0" Null="" Index_type="BTREE" Comment="" Index_comment="" />
        <options Name="class_details" Engine="InnoDB" Version="10" Row_format="Compact" Rows="0" Avg_row_length="0" Data_length="16384" Max_data_length="0" Index_length="32768" Data_free="7340032" Auto_increment="1" Create_time="2014-02-25 07:08:56" Collation="utf8_general_ci" Create_options="" Comment="" />
    </table_structure>

Same database lists the detailed foreign key constraint in sqldump. (.sql file).
below that's mentioned.

DROP TABLE IF EXISTS `class_details`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `class_details` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `course_id` int(10) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `tutor_id` int(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_cources_offered_class_details` (`course_id`),
  KEY `fk_employee_details_class_details` (`tutor_id`),
  CONSTRAINT `fk_employee_details_class_details` FOREIGN KEY (`tutor_id`) REFERENCES `employee_details` (`emp_id`),
  CONSTRAINT `fk_courses_offered_class_details` FOREIGN KEY (`course_id`) REFERENCES `courses_offered` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Anyone know why they don't list parent table information? and the way, How we can get the details of foreign key constraints?

P.S. : storage engine is InnoDB.


texts: How do I pass multiple statements into sqlcmd?

texts:
Wanting to automate some tasks instead of firing up SSMS for everything, I'm trying to write a batch file to run these statements, which work perfectly when I paste them into the sqlcmd prompt.  They simply check the mirroring status, suspend mirroring and then check the status again.  (To minimize dependencies and ease migration from one server to another, I want to keep all commands in a single file.)

select cast(DB_NAME(database_id) as char(30)),
        cast(mirroring_state_desc as char(15))
from sys.database_mirroring
where mirroring_state_desc is not null;
go;

ALTER DATABASE db1 SET PARTNER SUSPEND;
ALTER DATABASE db2 SET PARTNER SUSPEND;
go;

select cast(DB_NAME(database_id) as char(30)),
        cast(mirroring_state_desc as char(15))
from sys.database_mirroring
where mirroring_state_desc is not null;
go;

So, I wrote this batch file:

SETLOCAL ENABLEDELAYEDEXPANSION
SET @SQLSTRING=select cast(DB_NAME(database_id) as char(30)), ^
        cast(mirroring_state_desc as char(15)) ^
from sys.database_mirroring ^
where mirroring_state_desc is not null; ^
go ^
^
ALTER DATABASE MSCRM_CONFIG SET PARTNER SUSPEND; ^
ALTER DATABASE XeroxGHSCRMTest_MSCRM SET PARTNER SUSPEND; ^
go ^
^
select cast(DB_NAME(database_id) as char(30)), ^
        cast(mirroring_state_desc as char(15)) ^
from sys.database_mirroring ^
where mirroring_state_desc is not null; ^
go
::
echo.
sqlcmd -S AGABQ0VMCRMDB01\PROD_CRM -E -Q "%@SQLSTRING%"

However, it fails with syntax errors at the "go" commands.  I've tried "go" and "go;" with no luck.

Any tips or guidance to make this work would be appreciated.


texts: What is the difference between TCP port and TCP dynamic port?

texts:
Can anyone tell me the difference?

I tried to set either 1 port and left another blank also can work. I wonder what is the difference, or anything I missed out?


texts: Referential integrity after disable/enable trigger all

texts:
I wonder how to check referential integrity on a table after disabling and reenabling foreign key constraints.

For example :

postgres=# CREATE TABLE main(id serial, id_delta integer, PRIMARY KEY (id));
CREATE TABLE
postgres=# CREATE TABLE delta (id serial, PRIMARY KEY (id));
CREATE TABLE
postgres=# ALTER TABLE main ADD CONSTRAINT fk_main_delta FOREIGN KEY (id_delta) REFERENCES delta (id);
ALTER TABLE

Let's try to insert a value that do not respect fk_main_delta:

postgres=# INSERT INTO main (id_delta) VALUES (1);
ERROR:  insert or update on table "main" violates foreign key constraint "fk_main_delta"
DÉTAIL : Key (id_delta)=(1) is not present in table "delta".

=> it logically fails, so I disable FK constraints to force insert:

postgres=# ALTER TABLE main DISABLE TRIGGER ALL;
postgres=# INSERT INTO main (id_delta) VALUES (1);
INSERT 0 1
postgres=# ALTER TABLE main ENABLE TRIGGER ALL;

But now, referential integrity is no longer respected.

postgres=# SELECT * FROM main;
 id | id_delta
----+----------
  2 |        1
(1 ligne)

So is there a way to check integrity afterwards?


texts: Managing deleted entries in large database table

texts:
Currently we are using Mysql 5.6 with Amazon RDS.

We have table tracking uploads and we have a deleted flag in this table, we don't delete rows when a user subsequently deletes an upload. We do this to maintain historical upload information.

Now we are finding searches in the table very slow. We are thinking of creating a new table with just the deleted entries, but not sure what the best practice would be here.

Any ideas?

EDIT - Added fields to show what is indexed

Field             Type                 Null Key Default Extra          Index
upload_id         int(10) unsigned     NO   PRI         auto_increment Primary Key
user_id           int(11)              NO   MUL                        Foreign Key
resource_id       int(10) unsigned     NO   MUL                        Foreign Key
upload_size       int(10) unsigned     NO
upload_datetime   datetime             NO
deleted_datetime  datetime             YES
upload_name       varchar(150)         NO
upload_url        varchar(2000)        YES
upload_url_expiry datetime             YES
download_count    smallint(5) unsigned NO       0

It is the deleted_datetime field that gets updated.


texts:
Maybe it sound's like i'm an idiot... But i'm developing app with huge db on RoR. Also i see that if i do some query and after that do it one more it is doing much faster, becouse it has been cached... But could i send all my table data to cache via some query, to improve speed, so that simple my table will be in ram memory?
I read a lot about encreasing cahce size etc... But how can i send this all data in cache not when user do some query (if so user see data from cache)...

*************************** 1. row ***************************
       Table: ART_LOOKUP
Create Table: CREATE TABLE `ART_LOOKUP` (
  `ARL_ART_ID` int(11) NOT NULL,
  `ARL_SEARCH_NUMBER` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ARL_KIND` int(3) DEFAULT NULL,
  `ARL_BRA_ID` int(11) DEFAULT NULL,
  `ARL_DISPLAY_NR` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ARL_DISPLAY` int(11) DEFAULT NULL,
  `ARL_BLOCK` int(11) DEFAULT NULL,
  `ARL_SORT` int(11) DEFAULT NULL,
  KEY `ARL_ART_ID` (`ARL_ART_ID`),
  FULLTEXT KEY `ARL_SEARCH_NUMBER` (`ARL_SEARCH_NUMBER`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

*************************** 1. row ***************************
       Table: ARTICLES
Create Table: CREATE TABLE `ARTICLES` (
  `ART_ID` int(11) NOT NULL,
  `ART_ARTICLE_NR` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `ART_SUP_ID` int(11) DEFAULT NULL,
  `ART_DES_ID` int(11) DEFAULT NULL,
  `ART_COMPLETE_DES_ID` int(11) DEFAULT NULL,
  `ART_CTM` blob,
  `ART_PACK_SELFSERVICE` int(11) DEFAULT NULL,
  `ART_MATERIAL_MARK` int(11) DEFAULT NULL,
  `ART_REPLACEMENT` int(11) DEFAULT NULL,
  `ART_ACCESSORY` int(11) DEFAULT NULL,
  `ART_BATCH_SIZE1` int(11) DEFAULT NULL,
  `ART_BATCH_SIZE2` int(11) DEFAULT NULL,
  `Quantity` int(5) DEFAULT NULL,
  `Price` float DEFAULT NULL,
  `Waittime` int(3) NOT NULL,
  `datetime_of_update` datetime DEFAULT NULL,
  KEY `ART_ID` (`ART_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

via privacy can't show all


texts: SQL Server RECEIVE statement ordering issues

texts:
I'm seeing some unexpected message ordering behavior running a RECEIVE statement against a Service Broker queue.
According to the documentation:

For a given conversation, a RECEIVE statement returns messages in ascending message_sequence_number order.

So I expect the messages in the queue to be returned in the order -1, 0, 1, 2, but they are actually returned in the order 0, 1, 2, -1. The returned order happen to match the order they were transmitted by the other party but not the message_sequence_number value. All the messages in the queue belong to the same conversation.
The negative message sequence number is an SB error message (END CONVERSATION WITH ERROR by the initiator). The same non-expected order also happens with a timer message (sequence -2). My understanding was that those error and timer message types would "skip ahead" in the queue and be dequeued first, but it's not what I'm seeing in practice. So my understanding and expectations must clearly be wrong, but I can't see what I'm missing.
I tested this on SQL Server 2019, 2017 and 2012 and the behavior is consistent between the versions. Is this just me not being able to grasp the docs?


texts: How to compute a membership expiration date on SQL

texts:
This is a follow up to this previously posted question. I'm using LibreOffice Base to work with some membership data. Further expansion might be possible in the future, but for now, the embedded database is fine, and it provides portability (I could send the file to someone and they can see the data, without haing to connect to an SQL server). The downside: LibreOffice Base uses HSQLDB version 1.8 (from circa 2008). Hence, the available functions are limited.

I have several tables and forms and queries to add and retrieve data from my database, including payments. Among the payments, my culprit are the membership renewal. There are several types of membership, each with different prices, and in some cases, different expiration periods. Members can purchase membership for 1 year or 3 years, and they could do it at any given point in time. Each membership expire on December 31st of the calendar year.

I have a table (actually a View produced by a filtering Query) called "membershipPayment" where I have

memberID paymentID paymentDate expirePeriod
0        0         2012-02-02  1
0        10        2013-02-15  3
0        30        2014-01-10  1
0        60        2015-08-15  3
1        5         2012-03-12  1
1        15        2013-04-01  1
1        20        2013-10-01  3
1        35        2014-02-01  1

The expirePeriod is the number of years of membership associated with that payment. Notice that in the example, in some cases members are renewing membership when their membership is still active due to a previous payment. I need to query this table/view to summarize these payments and have an expiration date and membership status, with one line per member

memberID expirationDate memberStatus
0        2019-12-31     active
1        2017-12-31     inactive

The memberStatus column should be computed from the expirationDate and CURRENT_DATE. No problems here. My issue is how to compute the expirationDate. I would like to use only a Query, if possible. I don't know how to loop (or if it's even possible to do such thing) along the rows of the "membershipPayment" table to increment the expirationDate based on the value of expirePeriod, or to keep track of a value from a previous row while checking another. I managed to compute the total number of membership purchased on a sigle year using SELECT, SUM() and GROUP BY, according to the answer of the previously cited post. But keeping track of the expirationDate, still no clue.

If macros are required, any help or advice is appreciated.

Thanks for any advice or comment, even if it's to point that my question is trivial,


texts:
Something strange in this neighbourhood. Calling DBA's and ! GB
db.getCollection('contacts').update({}, {$set: {"source": "domain.txt"}},false,true);

It failed with:
admin> db.getCollection('contacts').update({}, {$set: {"source": "domain.txt"}},false,true);
[2020-09-29 23:24:32] java.lang.IllegalArgumentException: options should be an object: false
[2020-09-29 23:24:32] options should be an object: false

Also, table feels a little corrupted. No longer displays the amount of records.
Based on answers from https://stackoverflow.com/questions/7714216/add-new-field-to-every-document-in-a-mongodb-collection


texts: how to make a table unlocked within transaction block

texts:
I am using SQL Server 2008.  I have several stored procedures that process different reports.  Each stored procedure makes use of Begin Transaction' and 'Commit Transaction, using the default isolation level.

Everything works fine since the target tables use by these stored procedures are different for each stored procedure, except that all stored procedures call another common stored procedure to write log messages into the SPLog table.   I need the SPLog table to be available to all stored procedures, and not to be locked by any stored procedure. The table is designed so that each message has a column indicating which stored procedure it belongs to.

The problem is the SPLog table is locked once a stored procedure is ran, causing other stored procedures to wait until the first stored procedure commits.  How can I allow INSERTs into this SPLog table by concurrent stored procedures from within a TRANSACTION block?

Below is the pseudo code of these stored procedures:

Stored Procedure SP

Begin Transaction

 business logic....perform operation A on dedicated tables
    call common SP to insert a message row to SPLOG table (for common use)
 business logic....perform operation B on dedicated tables
    call common SP to insert a message row to SPLOG table (for common use)
 ....
Commit Trasaction

All stored procedures have similar pseudo code. Appending rows to SPLOG table should be allowed at any time.

How do I achieve that?


texts: Create t2 instance and connect via Wokrbench

texts:
I've created an t2 RDS instance inside a VPC. The instance is set to Publicly avaiable: No. I wasn't able to connect to the instance using MySQL Workbench. Is there an option to connect to the instance in that state?

I also tried to create with Publicly available: Yes, but I get the following error upon creation:

  Cannot create a publicly accessible DBInstance because customer VPC does not support DNS resolution and/or hostnames.

How can I solve this? -- I want to be able to create a database in t2 and connect to a t2 RDS instance from Workbench.


texts: Question about MySQL triggers performance

texts:
I currently have a database that stores some transactions for several users, for example purchasing or selling products is stored as 1 entry in the transactions table. It can also store profits made between transactions (stored in another table called profits).

I also need to store a daily history of purchases, revenues and profits for each user. I have a history table that looks something like this:

user_iduser   date        total_buy  total_sell total_profit
    2500    2016-04-02     24555       45121        2566

Right now, the way I keep the history table updated is outside the database side. The application automatically calculates and updates this table everytime the user logs in (after fetching whatever new transactions come from that day) and updates the corresponding totals for that day. The application also runs a script everyday at midnight that fetches every user's transactions and updates their totals for the previous day. However, I would prefer if this behavior was done by the database instead if possible (the less code the better).

So my question is: would creating triggers be a good way to do this, performance wise? It would be simple to create a trigger that, for example updates a user's history.total_buy column everytime a new 'buy' transaction is inserted, but considering I have several million transactions already stored and thousands of new ones arriving a day, wouldn't the constant trigger calls put a stress on the database? Or am I just getting worried for no reason?


texts:
I have a clustered columnstore table with approximately 1.4 billion rows.
I figured it being a clustered columnstore would best suit my need for various aggregations on many different splits/columns.
There is currently just a single partition, however I would like to split the data into multiple partitions.  I haven't been using partitions before, so I am not quite sure what the best practice is.
The table contains monthly financial information for our customers and will increase with approximately 35 million new rows each month. Each row will have a YearMonth_Id and a Customer_Id as well as a lot of columns containing financials.
I am thinking to do the partition by YearMonth_Id (maybe grouped by quarters?).
I'm no DBA, just a finance analyst.  Since we don't have qualified DBA to maintain this table regularly, it comes down to me to do it; hence I want to have as little maintenance on the table as possible.
My question therefore is as follows. When working with partitions and incremental values (YearMonth_Id), how should I structure the partitions on the table?
I read that usually you would leave an empty partition to the far right (if working with left range) that you can split each month (or quarter).   However, I am thinking of creating, say 12 empty partitions for future values, so the next time I need to do a split will be in a years time; so that the table is not dependent on me adding a new partition every month.
Is this a big no-no or would it be an okay way to go?


texts: Modelling a second level table relationship

texts:
I have three tables in my Database:

Customers
----------
Id
Name

Addresses
----------
Id
Description
CustomerId

Orders
----------
Id
Description
CustomerId
DeliveryAddressId

I'd like to make sure that every Address referenced by DeliveryAddressId in the Orders table belongs to the Customer referenced by CustomerId.
How can I model this?


texts: "Can't write; duplicate key on table" on insert / update?

texts:
I have a strange issue with InnoDB on MariaDB.

Sometimes (and I can't reproduce it, but it happens regularly and multiple times a day on a busy server) I get this SQL error:

Can't write; duplicate key in table 'tablename'

Searching on the web, this error should be happening when creating tables, if you have constraints with the same name. But this isn't the case, since it's happening on a table during some UPDATE or INSERT. I've also checked if there are any CONSTRAINTS with duplicate names, but they are all different.

99% of the INSERTs works, 1% fails. Don't know where to start debugging.


texts: How do I merge these two queries so that the result set is "combined" and consistent?

texts:
Please consider my first query:

SELECT cf.cnum as cnum, rc.name as name, SUM(rs.dh_simple + rs.ha_simple) as sums
FROM retirement_survey as rs
INNER JOIN currentfunds as cf
ON rs.fundid = cf.fundid
INNER JOIN research..complex rc
ON cf.cnum = rc.cnum
WHERE rs.date  = '9/30/2016'
GROUP BY rc.name, cf.cnum
HAVING SUM(rs.dh_simple + rs.ha_simple) > 0
ORDER BY cf.cnum

This gives me the following resultset:

Here is my second query:

SELECT r.cnum as cnum, c.name as name, r.assets as assets
FROM research..complex c, research..retirement_simple_ira r
where r.date = '9/30/2016'
and c.cnum = r.cnum
ORDER BY cnum

Which gives me the following resultset:

I've been trying so many different variations of this and joining the relevant tables together, but I am not able to get a consistent result. What I eventually want is a resultset with four columns:

cnum | name | sums | assets

Please help me with this.


texts: Optimize query with join and null predicate [SQL Server]

texts:
I have the following query which I am having problems optimizing:

SELECT TOP (100) this_.Id AS Id80_1_,
   this_.UserAgent AS UserAgent80_1_,
   this_.IP AS IP80_1_,
   this_.Guid AS Guid80_1_,
   this_.CreatedOn AS CreatedOn80_1_,
   this_.UpdatedOn AS UpdatedOn80_1_,
   this_.IsDeleted AS IsDeleted80_1_,
   this_.AnalyticsUserId AS Analytic8_80_1_,
   this_.SiteId AS SiteId80_1_,
   analyticsu1_.Id AS Id15_0_,
   analyticsu1_.Email AS Email15_0_,
   analyticsu1_.DateLastChecked AS DateLast3_15_0_,
   analyticsu1_.Guid AS Guid15_0_,
   analyticsu1_.CreatedOn AS CreatedOn15_0_,
   analyticsu1_.UpdatedOn AS UpdatedOn15_0_,
   analyticsu1_.IsDeleted AS IsDeleted15_0_,
   analyticsu1_.UserId AS UserId15_0_
FROM [AnalyticsSession] this_
INNER JOIN [AnalyticsUser] analyticsu1_ ON this_.AnalyticsUserId=analyticsu1_.Id
WHERE (analyticsu1_.Email IS NULL
       AND analyticsu1_.UserId IS NULL)
  AND this_.CreatedOn < '2017-04-01'

I have tried adding an index in multiple ways including:

CREATE NONCLUSTERED INDEX [IX_AnalyticsUser_UserEmailIsNull1] ON [dbo].
[AnalyticsUser]
(   [Email],    [UserId])
INCLUDE (Id, DateLastChecked, Guid, CreatedOn, UpdatedOn, IsDeleted)
WHERE (Email IS NULL and UserId IS NULL)
WITH (online=on)
GO

However my query plan shows sql server scanning and joining 113,000 rows to get the 100 rows returned: (both ActualRows="112806" ActualRowsRead="112806" on the scan operator)

This database is on azure in compatibility mode 130.

What is the best index to add to optimize this query? There are 2355366 rows in the AnalyticsUser table.


texts: Compare running / cumulative total with static target

texts:
I have some daily data and monthly data.

I want to compare these two datasets, with Hive and perform a payout when the sum of actual for a month passes that respective months target. In the above dataset, every id has a monthly target of 6000. The payout should be 0 unless an id accumulates more than 6000 for that respective month from the daily data.

Example: Comp1 has a target of 6000 for month 1. When we look at the daily data, we can see Comp1 hits this target on 2020-01-06 by having an accumulated actual of 6600. At this point, the payout should be: (6600-6000)/100*1. Additionally, Comp1 should see a payout for every day following until the end of the month as they've passed their monthly target.

This is what I have tried:

select
    d.yyyy_mm_dd,
    d.name,
    d.id,
    d.actual,
    case when d.actual > t.target
        then (d.actual - t.target) / 100.0
        else 0
    end payout
from monthly_targets t
inner join (
    select yyyy_mm_dd, name, id, sum(cast(actual as int)) actual
    from daily_data
    group by yyyy_mm_dd, name, id
) d
    on month(d.yyyy_mm_dd) = t.month
    and d.name = t.name
where
    d.yyyy_mm_dd >= '2020-01-01'
    and d.name in ('Comp1', 'Comp2')

However, this is only comparing the daily actual value to the monthly target. There will never be a payout this way as a monthly target will never be met on a single day. How can I amend the above so it calculates a cumulative actual for the month and compares on that instead?

I'm expecting output like this:

+------------+----+-------+--------+----------------+--------+
| yyyy_mm_dd | id | name  | actual | actual_to_date | payout |
+------------+----+-------+--------+----------------+--------+
| 2020-01-01 |  1 | Comp1 |   1100 |           1100 |      0 |
| 2020-01-02 |  1 | Comp1 |   1100 |           2200 |      0 |
| 2020-01-03 |  1 | Comp1 |   1100 |           3300 |      0 |
| 2020-01-04 |  1 | Comp1 |   1100 |           4400 |      0 |
| 2020-01-05 |  1 | Comp1 |   1100 |           5500 |      0 |
| 2020-01-06 |  1 | Comp1 |   1100 |           6600 |      6 |
| 2020-01-07 |  1 | Comp1 |   1100 |           7700 |     17 |
| 2020-01-08 |  1 | Comp1 |   1100 |           8800 |     28 |
| ...        |    |       |        |                |        |
| ...        |    |       |        |                |        |
| ...        |    |       |        |                |        |
| 2020-01-31 |  1 | Comp1 |   1100 |          34100 |    281 |
| 2020-02-01 |  1 | Comp1 |   1100 |           1100 |      0 |
| 2020-02-02 |  1 | Comp1 |   1100 |           2200 |      0 |
| 2020-02-03 |  1 | Comp1 |   1100 |           3300 |      0 |
+------------+----+-------+--------+----------------+--------+


texts: How do I group items separated by a 0 in a column using mysql?

texts:
I have a table with the following structure:

id,center,shelf_id,occupied,remaining
1,East,1_1_1,1,3
2,East,1_1_2,1,1
3,East,1_1_3,1,0
4,East,1_1_4,1,0
5,East,1_1_5,1,2
6,East,1_2_1,1,1
7,East,1_2_2,1,0
8,East,1_2_3,0,4
9,East,1_2_4,0,4
10,East,1_2_5,0,4
11,East,1_3_1,0,4
12,East,1_3_2,0,4
13,East,1_3_3,0,4
14,East,1_3_4,0,0

I would like to aggregate by remaining where remaining is not 0.

The above example should return a table with a commas delimited shelf_id and remaining with a total of remaining that looks like:

shelf_id
1_1_1, 1_1_2
remaining
3,1
total
4

shelf_id
1_1_5, 1_2_1
remaining
2,1
total
3

shelf_id
1_2_3, 1_2_4, 1_2_5, 1_3_1, 1_3_2, 1_3_3
remaining
4,4,4,4,4,4
total
24

I am using MariaDB 10.2.31.


texts: Do SQL Server disabled databases cause any usage concerns?

texts:
I have a SaaS e-commerce app. When clients stop using the service I disable their SQL Server Database and User Account rather than delete them.

Do these disabled databases cause any concerns related to stress on the server or SQL Server limitations on databases? For example is the any type of overhead just leaving them there? I have our backup tasks setup to not make backups for disabled databases.

I use SQL Server 2008 Web Edition.


texts: Same query, different result (depending on user's permissions)

texts:
A question about SQL in general:

If two users execute the same sql statement, is it possible that user1 gets different results than user2?

Imagine user1 has more permissions than user2.

With other words: Is it possible that user1 receives 100 rows, and user2 only 80?

My question is about the SQL standard.

But feel free to speak about implementations of specific DB systems in comments.


texts: Unable to enable publishing on restored database

texts:
We have a production database (2008 R2) that is enabled for replication as a publisher and subscriber with a remote distributor (distributorA).

We restored a copy of this database onto a new 2016 server. Now we want to configure replication on the restored copy as a publisher to our new distributor (distributorB).

We configured distributorB with a new distribution database and configured the new 2016 server as a publisher to distributorB.

Back on the new 2016 server, we ran sp_adddistributor to point it to distributorB. Everything works as expected so far.

Now we run the following command on the restored db to enable replication sp_replicationdboption @dbname = 'restoreddb', @optname = 'publish', @value = 'true'. That completed successfully.

This is where the problem starts to manifest itself. When I tried to create a new publication now, at the end of the publishing wizard, it errored out saying it can't find dbo.syspublications.

I check the system tables on the restoredb and find that some of the tables required for replication have not been created (syspublications, sysarticles, syssubscriptions and etc), but some have (sysreplservers and MSPeer*). I can, however, see that this database has 'transactional' checked in publication databases under publisher properties. When I try to uncheck it and hit OK, it gives me the following error

Invalid object name 'dbo.syssubscriptions'.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

I tried using sp_removedbreplication to cleanup any remnants of replication settings from the database to start afresh, but that didn't seem to help at all.

I think the problem here is somehow the remnant replication settings from the database's 'former self' is interfering with setting up replication again with a different distributor.

So my question is why some of the replication system tables were not created? How do we remedy this?


texts: mysql database under symlinked directory: ERROR 1018 (HY000): Can't read dir of './/' (errno: 13)

texts:
I'm using mysql 5.5 on an ubuntu linux server.
I need to move a database from its current location (under /var/lib/mysql) to a disk with more space, so I attempted to move the directory and create a symlink to the new location.  When I do this, I get

ERROR 1018 (HY000): Can't read dir of './<dbname>/' (errno: 13)

when I attempt to show tables after starting mysql again.  The error goes away if I move the database directory back to its original location. (But, I need to move to a larger disk, so I've got to figure this out somehow)

I've looked around on this site and google; The documentation and the accepted answer to this question indicates what I'm doing should work (I'm attempting to use option 3 from the accepted answer).

So, is this still supported?  If so, what am I doing wrong?

Here's some additional information to jumpstart things:

when I run ls -lL /var/lib/mysql all the files are owned by mysql (user & group)
I've also changed ownership of the actual symlink to mysql:mysql (due to comments on this question)
Both filesystems are on local disks (ext4)


texts:
I have the below query.

SELECT DISTINCT P.PLAYERID, P.FIRSTNAME, P.LASTNAME, P.POSITION,PT.POINTS, PT.MINUTES, CALC_TENDEX(P.PLAYERID, PT.TEAMID, 2009) TENDEX, T.DIVISION, PT.TEAMID, T.TEAMNAME
FROM PLAYERS P
JOIN PLAYERS_TEAMS PT ON P.PLAYERID = PT.PLAYERID
JOIN TEAMS T ON PT.TEAMID=T.TEAMID
WHERE PT.YEAR=2009 AND T.DIVISION='East'
ORDER BY TENDEX DESC
FETCH FIRST 12 ROWS ONLY;

I want to output this query as xml, with custom format like this:

<nba dataset="topplayers">
    <division ID="East">
        <player></player>
                .
                .
                .
        <player></player>
</nba>

I have tried using SQL/XML but whatever I try does not return answers I want. Is there some easy way to directly convert it to xml?

EDIT: SQL/XML I have tried (along with various alterations of this)

SELECT DISTINCT XMLELEMENT("nba",
         XMLAGG (
           XMLELEMENT("division",
             XMLATTRIBUTES(T.DIVISION AS "ID"),
               (SELECT DISTINCT XMLAGG(
               XMLELEMENT("player",
                 XMLFOREST(
                   P.PLAYERID AS "playerid",
                   P.FIRSTNAME AS "firstname",
                   P.LASTNAME AS "lastname",
                   P.POSITION AS "position",
                   PT.POINTS AS "points",
                   PT.MINUTES AS "minutes",
                   CALC_TENDEX(PT.PLAYERID, PT.TEAMID, 2009) AS "tindex",
                   T.DIVISION AS "division",
                   PT.TEAMID AS "teamid",
                   T.TEAMNAME AS "teamname"
                  )
                )
              )
              FROM PLAYERS P
              JOIN PLAYERS_TEAMS PT ON P.PLAYERID = PT.PLAYERID
              JOIN TEAMS T ON PT.TEAMID=T.TEAMID
              WHERE PT.YEAR=2009 AND T.DIVISION='East' AND ROWNUM < 12
            )
          )
        )
       ) AS "nba"
FROM TEAMS T
WHERE T.DIVISION = 'East';


texts: What is the correct way of creating a one-to-one relationship?

texts:
Imagine the following situation:

> A person has a passport.
> A person *owns only* one passport.
> One passport can only *be owned by a single* person.

This is a clear case of a one-to-one relationship. For the sake of simplicity let us imagine that a person only has a name and the passport only has a nationality. What is bothering me the most is that it seems that everyone is doing this differently. From what I can tell there are four strategies that can be taken to map this relationship:

Everything on the same table:

A foreign key on the owner side that references the owned side.

Same value for primary keys on both tables.

A foreign key on the owned side with a unique key on top:

Number 1 is pretty self explanatory and I do not see any problem when
the entities are small, but if they have a lot of fields we will have
a gigantic table.
Number 2 seems fine and this is the way I have seen most people and frameworks do it  (e.g. Entity Framework, Hibernate) and it is also the way I was taught in school. The big problem here is that, because of referential integrity, our deletion logic will be upside down. I should be able to delete a passport without any problem, but in this case I will not be able to delete it without deleting the person as well, which does not make any sense.
Number 3 and 4 seem pretty much identical. With the foreign key on the passport side, I am able to delete passports without deleting people, which makes sense. If I remove a person, I should have to remove their passport. The main advantage that I see for using option number 4 instead of number 3 or all the others, is that if for ever reason I decide that I now want a user to have multiple passports I only have to remove the unique key constraint, which is incredibly easy and a lot less hassle than changing the keys from both tables.

So my question basically is reduced to the following: With so many advantages with number 4 why do people keep using the other strategies? My biggest grip is with ORMs such as Hibernate which, in my opinion, do things the opposite way. The owning side of the relation tracked by Hibernate is the side of the relation that owns the foreign key in the database. So if I tried to do this in Java with Hibernate my relationship would be swapped. If I included the foreign key to the passport on the person table like it wants me too, it would ruin the deletion logic like I have explained before. I have the impression that EF also works this way. So, with all of this, why do people keep preferring the "disadvantageous" approaches?