texts: Duplicate error when update column in the same table

texts:
I am getting error code Error Code: 1062 Duplicate Entry for KEY Primary.

The Procedure is suppose to increment workshift by 1 day each week. Ex, 6001 is Monday shift, 6002 Tuesday and so on each time it's called.

I know i'm getting the error due to as soon as 6001 == 6002 in first if , then there is 2 6002 with same PK. Is there anyway to fix this?

Here is current table.

roster
 employee  branch  workShift
   0001      01       6001
   0001      01       6002
   0001      01       6003
   0001      01       6004

Table after call to update roster:

roster
 employee  branch  workShift
   0001      01       6002
   0001      01       6003
   0001      01       6004
   0001      01       6005

CREATE PROCEDURE `UpdateRoster`(in employeeid char(8), in BranchID int(11))
BEGIN
 DECLARE hours_max INT(35);
 DECLARE fin INT DEFAULT 0;
 DECLARE empid CHAR(8);
 DECLARE brnid INT(11);
 DECLARE workshfid INT(11);
 DECLARE emp INT;

 DECLARE employee_roster CURSOR FOR
         SELECT EmployeeID, BranchID, WorkingShiftID
         FROM DutyRoster;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin = 1;

 OPEN employee_roster;
 REPEAT
    FETCH employee_roster INTO empid, brnid, workshfid;
        IF workshfid = '6001' THEN
            SET workshfid = '6002';

        ELSEIF workshfid = '6002' THEN
            SET workshfid = '6003';

        ELSEIF workshfid = '6003' THEN
            SET workshfid = '6005';

        ELSEIF workshfid = '6005' THEN
            SET workshfid = '6007';

        ELSEIF workshfid = '6007' THEN
            SET workshfid = '6008';

        ELSE
            SET workshfid = '6001';
        END IF;

    UPDATE DutyRoster
    SET WorkingShiftID = workshfid;

  UNTIL fin
  END REPEAT;
  CLOSE employee_roster;
END


texts: Single node SQL Server 2014 cluster at DR environment

texts:
We are setting up a new Disaster Recovery environment.
On Production we have SQL Server cluster with two nodes.
For DR we decided that one node should be enough. The plan is to add the second node ASAP if disaster happens.

We requested our managed cloud company to create a single node cluster for us. However, they try to discourage us from doing that and suggest installing non-clustered instance instead, so when disaster happens we will build brand a brand new cluster with two brand new nodes and then migrate to it from non-clustered instance.
This option sounds like more work for us if disaster happens.
We will need to migrate all the databases to the clustered instance from non-clustered instance. I also expect a longer downtime if we do that, as the opposite to just adding a second node to the existing cluster.

Tech person from this cloud company said that single node cluster is complicated solution and is prone to all kinds of problems like "split brain", that it can stuck all of a sudden and they have seen so many issues with single cluster nodes. He said that Microsoft does not recommend having single node clusters. He wasn't very specific and I'm not sure I understood what exactly problems we will have.

I tried to google for some specific problems, but failed to find were Microsoft would suggest to avoid having single node clusters, especially when it comes to DR.

Is there a reason to avoid single node cluster solution for DR and go with non-clustered instance instead?


texts: Receiving "The SELECT permission was denied on the object" even though it's been granted

texts:
I'm a programmer, not a dba... I know just enough to be dangerous.

I've inherited a database with a legacy user that is a db_owner for the database. We can't adjust this user's permission for existing tables, schemas, etc., for business reasons, but some new tables are being created, and I only want this user to have SELECT access on them.

Permissions have been set for this user for these tables so that everything is DENIED, except SELECT, which is set to GRANT.

Yet when this user (dbadmin) attempts to perform a SELECT on one of these tables (AccountingAudit), this error happens:

The SELECT permission was denied on the object 'AccountingAudit', database 'billing', schema 'dbo'.

I've run this SQL to try and see what permissions are set for this table/user:

select object_name(major_id) as object,
 user_name(grantee_principal_id) as grantee,
 user_name(grantor_principal_id) as grantor,
 permission_name,
 state_desc
from sys.database_permissions

And this is what I get back:

AccountingAudit dbadmin dbo ALTER   DENY
AccountingAudit dbadmin dbo CONTROL DENY
AccountingAudit dbadmin dbo DELETE  DENY
AccountingAudit dbadmin dbo INSERT  DENY
AccountingAudit dbadmin dbo REFERENCES  DENY
AccountingAudit dbadmin dbo SELECT  GRANT
AccountingAudit dbadmin dbo TAKE OWNERSHIP  DENY
AccountingAudit dbadmin dbo UPDATE  DENY
AccountingAudit dbadmin dbo VIEW DEFINITION DENY
AccountingAudit dbadmin dbo VIEW CHANGE TRACKING    DENY

Seems like it should be working right?

The SELECT call I'm making is a very basic SELECT * FROM AccountingAudit, from within SSMS. I'm not doing any special sp_executesql or anything like that.

I've tried explicitly granting permission:

GRANT SELECT ON [dbo].AccountingAudit TO dbadmin

This has no effect (why would it, the query above already shows it's granted! ;-)

I've searched through stackoverflow.com and elsewhere, and cannot find anything I haven't tried yet. I'm wondering if it has something to do with how the schemas are setup. (At this point I know very little about schemas.)

Any ideas? Thanks!


texts:
I know there must be a simple answer to this question, but I cannot find it anywhere.  Our SQL 2012 FCI have SQLDIAG .XEL files in their MSSQL\LOG folder.  In some instances, these files are often exactly 100MB yet when opened in SSMS appear to be empty (says displaying 0 events).

I need to know how to manage their maximum size and file retention if this is possible.

We have some root drive mount points that were from SQL 2008 FCI (upgraded to SQL 2012) and these mount points were only 1GB.  As you can guess, these mount points are filling up and I need to limit these Failover Cluster Diagnostic logs if possible or I'll have to come up with an alternative solution.


texts:
Couldn't find a newbie forum; hope this is in the right place.

In my quest to learn more about databases, analytics, and creating service APIs to work with them, I decided to scrape an online archive of Jeopardy data and plan to reconstruct the data into a functional db.  Pretty sure this has been done before, but learning is the primary objective here :)

I'd like to get the data structured so I can run some advanced statistics on it. To do so, I'll need to query things like a player's score at any given point in the game, see who picked which clues and in what order, identify (and grade) players' wagering on daily doubles & Final Jeopardy, etc. If all goes to plan, it would be neat to expose this via public API so others can play with it too.

There's a decent amount of data here, though it's not very big compared to enterprise dbs:

6000 distinct games
~60 clues per game, so ~360k total unique clues.
12000 players, which includes duplicates (ex: Ken1, Ken2, Ken3)

Below is the latest draft of my data model (original SVG, updated SVG) based on the data available from j-archive, the general structure of Jeopardy games, and my limited understanding of database design. Appreciate any feedback -- especially on the clue_response table, which is the only table with transactional data.

Data is currently stored in an sqlite3 db.  Will probably migrate it to MySQL once I settle on a structure.


texts: Transactional replication creating a fixup index that's sleeping, but blocking other processes

texts:
I've just migrated my ERP database server from SQL Server 2000 to SQL Server 2008 R2 (both Enterprise Edition) and that migration went fairly smoothly.

However, now that I'm trying to recreate replication from the ERP to the Reporting environment, I have two publications that are trying to create a series of MSFixupNonUniqueIndex_{{datetimestamp}} nonunique indexes. The processes creating these indexes are all sleeping, but they are blocking other SQL processes that end-users are running.

What is causing this fixup index creation and can I avoid it?

Using sp_whoisactive there is no wait_info for the index creation task.
I did not drop the replicated database in the Reporting environment.
The "Reporting environment" is also SQL Server 2008 R2 Enterprise.
This is 2008 R2 to 2008 R2 replication, now.
sysprocesses shows two procs blocked by the spid attempting to create the index. The spid itself has a waittype of 0x0000 and a lastwaittype of MISCELLANEOUS.
sys.locks shows there are 777 locks: 7 Intent, 761 Exclusive, 6 Shared, 2 Schema Mod, 1 Schema Stability. Resources are: 1 ALLOCATION_UNIT, 9 OBJECT, 3 DATABASE, 2 METADATA, 677 PAGE, 11 KEY, 1 HOBT, 73 EXTENT.
select * from sysprocesses where open_tran > 0 does return the spid with one open transaction
DBCC OPENTRAN has no open transactions.
MAXDOP is unconfigured


texts: Deleting MySQL table with pending transactions

texts:
Is there a way to delete an InnoDB table or database with pending transactions in MySQL (preferably on file system level)?

What happened:

I use MySQL 5.5.28 and ran LOAD DATA INFILE… to import a huge data set (300M rows) into an InnoDB table. I did not use set autocommit = 0; before. Unfortunately, mysqld was stopped right in the middle of the import.

When I restart mysql, it tries to roll back the transaction filling the system log with messages like this:

  mysqld_safe[4433]: 121212 16:58:52  InnoDB: Waiting for 1 active transactions to finish

The problem is that the roll back is running for more that 25 hours now during which  mysqld is not accepting any socket connections.

I cannot just delete /var/lib/mysql/* and start from scratch because there are some other InnoDB databases/tables on this machine as well. However, the problematic table is the only table in a separate database. Deleting the entire table or the entire database is not a problem since I can re-import all data afterwards.


texts: I have a beginning balance I need to calculate ending balance running total by month rows

texts:
I have been searching this site and others trying to find an answer to this. I tried various things but cannot get my head around an answer so here goes.
Here my data:

What I need is to start with the begbal of each Acct and add the debits, subtract the credits and produce an ending balance of that month (row). When the Acct changes, pick up the new begbal and start again. With the data it should look like this:

I can get a running total on the debits and credits by doing this:

SELECT
pph.Acct,
pph.Year,
pph.Prd,
pph.begbal,
pph.debit,
pph.credit,
SUM(pph.debit+pph.credit) OVER (PARTITION BY pph.Acct ORDER BY pph.Year, pph.Prd) AS endbal
FROM GL_PeriodPostingHistory pph

What I want to do is

  If Acct <> prevoius Acct
  then Sum( begbal+debit-credit)
  else Sum(previous endbal+debit-credit) as endbal

and I just can't figure out how.


texts:
I'm trying to setup a many to many relationship between an gizmo table and a owner table.

A owner can have many gizmos
And a gizmo can have many owners

When I have a linking table do I set the gizmoID and ownerID as a composite key and then set the foreign keys on the gizmo table to Identity Auto-Increment by 1 and the same on the owner table or do I set composite key to identity auto-increment.

I am working on sql server 2014 Express and fairly new to this and trying to wrap my head around this.

Thanks for the help in advance.


texts:
I am a developer who has worked with SQL Server by way of Entity Framework, I am now using MySQL by way of Node.js

I am stepping into your strange world to ask an operations question I hope you can help me with. I imagine this is pretty basic for many of you DBAs. I am not 100% sure what terms I should be searching for with this.

What tools exists where I can make schema changes to a Development database as I see fit, then at any time I can compare the present state of Development database to the present state of Production database.

For Example

Production vs Development

Now that tool will generate a diff SQL file that simple makes all of the Alters.

Thus

 -  OriginalSchema.sql
 -  001.update.sql
 -  002.update.sql
 -  003...

Finally, does a tool exists that can not only generate said scripts but can also manage the UP (patch) and DOWN (rollback) of running said scripts.

~$ tool compare local remote
~$ tool generate local remote
~$ tool apply remote latest
~$ tool rollback --steps=2

Many Thanks!


texts: How to calculate or track monthly database growth?

texts:
Using oracle how can i track database growth monthly? The following  is a query apart of a query i used to stored information using sql server. Is there a way to replicate the same using oracle?

SELECT
  t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t


texts: "Scan interface error" when connecting to a DB2 database restored on a different server

texts:
I restored a database backup taken on a different server using this script:

FORCE APPLICATIONS ALL;
RESTORE DATABASE MY_DATABASE_NAME
    FROM /backup/db2inst1
    TAKEN AT 20140104051657
    ON /data
    INTO MY_DATABASE_NAME
    WITH 2 BUFFERS
    BUFFER 1024
    PARALLELISM 1
    WITHOUT PROMPTING;
TERMINATE;

From which I receive the message:

 Restore is successful

Then I try to connect:

[db2inst1@host sql]$ db2 CONNECT TO MY_DATABASE_NAME
SQL0901N  The SQL statement or command failed because of a database system
error. (Reason "Scan interface error".)  SQLSTATE=58004

I was able to restore the exact same backup to the server it came from with no problems.  This only occurs when attempting to restore it to a different server.  The second server has a nearly identical setup (both CentOS 6.3, both using db2 10.1 express-C), the only difference being the main server has a user db2inst1 in group db2iadm1 and the second has db2inst1 in group db2inst1.  Any ideas?


texts: How to add new columns to MySQL database retaining existing data?

texts:
In implementing upgrades to an existing Python application, on a development server, one MySQL table has gained an additional column but another has gained 23. What is the best way to add these to to the production servers. There are two servers within the company but a few dozen outside who want the update.

Many thanks...


texts: Can I create a stored procedure in mysql using a txt file?

texts:
My server OS is Red Hat 6 with MySQL. I'm using this line in a .sh file to run some jobs on the server:

mysql -u root -p[password] databasename < MySQL_JOBS_SENTENCES.txt

The content of the .txt file is:

call procedure1(date(now()));
call procedure2(date(now()));
call procedure3(current_Date());

Can I create or load a new stored procedure from a .txt file into my database the same way?


texts: How do I iterate over 1000 records in a particular order

texts:
I am writing a procedure to migrate two tables into different tables. This procedure will have two cursors. One to fetch the parent records and then second cursor to fetch the details for each parent record. The parent record is a table with the following columns --

  ID || YYYYMMDD || submitdate.

The combination of ID and YYYYMMDD gives the unique id. So now I want to migrate this to another table in the order it was created. I can order this table by submitdate, but I need to fetch 1000 records at a time. How can I achieve this?


texts: How to set an Oracle user's default schema?

texts: I created a few new users in Oracle.  However, when running sqlplus, they all need to fully qualified the table names in query.  What's the best way to set a default schema for these new users?


texts:
The entirety of my code is:

BEGIN TRAN --SACTION
SELECT 1
COMMIT --TRAN

with any combination of tran, transaction or neither it always returns error:

Incorrect syntax near the keyword 'BEGIN'.

I'm using Microsoft SQL Server 7.0. This is a corporate system, a legacy. I can't update it from my own accord.

The code does nothing useful but demonstrates the problem. commit or commit transaction yields the same error. No mimic, I wrote the code myself.

Trying BEGIN TRAN SELECT 1 COMMIT TRAN also gives the same error.

Select 1 runs OK. Begin, Begin Tran or Begin Transaction yield error.

I was pondering if someone could have altered the system catalogs so that the reserved word BEGIN was replaced by another word. In this case, how would I query for the new equivalent?


texts: Calling a SQL Server job within another job

texts:
Is it possible to call a SQL Server job to run within another job?

I know we can add all steps of Job 1 to Job 2, but I prefer not to do that. First the Job 2 is already quite big and second I couldn't find a copy-paste option to copy steps between jobs, so it would be time consuming to add the steps manually.

Any suggestion is appreciated.


texts: Online Re-Indexing In SQL Server 2005/2008 Standard

texts:
The Story: Once a week, we run a special Maintenance Plan that involves re-indexing 2 critical tables that have ~6 Million records between the two of them. We use SQL Server 2005 Standard Edition.

The Issue: During this re-indexing (~15 min operation), we fail to insert data into the tables, and our clients are dataless.

I was told that SQL Server 2008 Enterprise has a "Online Re-Indexing" that is not present in lesser versions of SQL Server that will resolve our problems.

However, I just found the following option in our Maintenance Plan options (SQL 2005 Standard):

My Question: Does this perform that same "Online Re-Indexing" that 2008 Enterprise does? What are the repercussions (performance wise) to enabling this option? Was I almost wooed into purchasing a 2008 Enterprise license needlessly?


texts:
I am working as a  remote DBA for some of the production servers.

There was replication from Master to Slave.

There is something changed in the scenario they have made one of the Slaves as master now..

when i saw the error log of the machine which is master now I got an error.I was unable to understand what is this..

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it.

but all are working well now.Please help to understand what was that.

Thanks..!


texts:
I have a table called Transactions

transactions ( TransactionID, CustomerID, DeviceTypeID, TransactionQuantity, TransactionDate, ManufacturerID)

TransactionID → CustomerID

TransactionID → DeviceTypeID

TransactionID → ManufacturerID

TransactionID → TransactionQuantity

TransactionID → TransactionDate

TransactionID → TransactionValue

Where transactionID is a primary key.

CustomerID
DeviceTypeID
ManufacturerID
are all foreign keys.

DeviceTypeID and ManufacturerID are needed to tell what "product" a customer is buying.

However, the is another table "inventory" which basically has

DeviceTypeID

ManufacturerID

These two keys are a composite primary key, and a number or quantity.

inventory ( DeviceTypeID,  ManufacturerID, InventoryQuantity )

(DeviceTypeID, ManufacturerID) → InventoryQuantity

Basically, in the inventory, there isnt a device for every manfucaturer. Say,there arent LG boilers in the inventory. But samsung ones are there.

It seems that Transaction Quantity is dependend on DeviceTypeID, ManufacturerID. And manufactuerID is dependend on DeviceTypeID and vice versa but I could be wrong.

It looks like this is in 3NF but i am not sure. I havent seen cases anywhere discussing what happens when you include composite/compound keys in a transaction table and a primary key.

Contextually speaking none of the keys have any realtion with each other in the table.


texts: I am using PostgreSQL 9.3, but the book I am reading "Beginning Databases with PostgreSQL From Novice to Professional" is using PostgreSQL 8. It mentions there is a postmaster.opts file which passes the default flags to the postmaster executable. For example, it allows you to set the -D flag (t set location to data directory) and an -i flag (to enable network connections) and so forth. Well, I am running Ubuntu 14.04 and I cannot find this file anywhere. Has it disappeared in PostgreSQL 9 and if so, what has replaced it?


texts: Run MySQL client as user without user's password if have root access

texts:
If importing a SQL dump file using the mysql command line client, if it wasn't created by the superuser and guaranteed to not have been modified, for security it's best practice to not execute it as the root user.

I know this can be done using the -u and -p arguments, but is there a way to not have to know the password for the user if you already have the ability to log in as root (e.g. the root password is in /root/.my.cnf)?  Like is there some way to log in as root then switch to the particular user, or some other easy way to accomplish this?


texts:
I have SQL Server 2008 R2, when I try to access any value stored in filestream I get the following error:

  Msg 233, Level 20, State 0, Line 0
  A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

Also the following error thrown in the SQL ERRORLOG file :

  SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

I read about that exception and Microsoft has KB for it (KB972936), but it also didn't work after I installed its hotfix.

If I SELECT the count of the records in that table using :

SELECT COUNT(1) FROM [Table_Name]

I get the correct result.

Here are some details for the database files and filegroups. The database has 2 files the "Row Data" file and the "Log" file where it should also contains the "Filestream Data" item.

The database has the following filegroups :

Rows : PRIMARY with 1 File
Filestream : [MyFileName] with 0 Files !

Here is a snapshots for the DB properties page

And here is the full SQL ERRORLOG file.


texts: SQL SERVER dynamic Query with Like Statement for null values

texts:
I'm trying to execute a SQL dynamic query with nullable parameters using LIKE. If the parameter has a value, the query is successfully executed. But if parameter is NULL, query breaks returning only "Command(s) completed successfully" without any values.

Here is my query:

DECLARE  @articleNo VARCHAR(100), @query varchar(8000)

 SET @articleNo = NULL

SET @query =
 'SELECT AT.Id AS ArticleId, AT.[Description] AS ArticleName, AT.ArticleNo, AG.Id AS ArticleGroupId, AG.Name AS ArticleGroup, St.Quantity AS Stock, AB.[Key] AS Attribute, AB.[Value] AS AttributeVal
    FROM [dbo].[Articles] AS AT
    INNER JOIN [dbo].[ArticleGroups] AS AG ON AT.ArticleGroupId = AG.Id
    LEFT OUTER JOIN [dbo].[ArticleAttributes] AS AB ON AT.Id = AB.ArticleId
    LEFT OUTER JOIN (SELECT ArticleId, SUM(Quantity) AS Quantity FROM [dbo].[StockEvent] GROUP BY ArticleId) AS ST ON AT.Id = ST.ArticleId
    WHERE
    ('''+ CAST((@articleNo) AS varchar) +''' = '''' OR AT.ArticleNo LIKE ''%' + CAST((@articleNo) AS varchar) + '%'')'

    EXEC (@query)


texts: Measuring DB time of application using SQL Server Profiler

texts:
I have an application residing on the same host as a SQL Server database. There is a scenario in which this application is slow and due to co-existence of IIS and SQL Server, I cannot get which one wastes what fragment of time.

My assumption is that I can save trace to a table and sum completed RPCs time to get microseconds of time that is spent on DB. yet I doubt it very much. I don't know compilation time or any other times is included in this trace time or not.

Question: how can I tell which fragment of time is spent when an application request is sent to the SQL Server database?

Thanks in advance


texts:
I have SQL Server 2012 SP2 that has a max memory of 25.4GB but SQL Server is only using 15GB. When I check free memory with the PerfMon SQLServer:Memory Manger Free Memory (KB) counter only 40MB is free. The server itself has 32GB.

I think that the server is only using 15GB because it only needs that much for loading all the used pages. But what I do not understand is why the free memory is only 40MB.

Can someone please explain this?

There are no other services on the system and my PLE is 122000 seconds.


texts:
Consider a scenario where I have 3 standalone running Postgres engines
that are installed on three separate machines.

The data on this machine is inserted, updated or removed each month by employees. Changes made to the database must be sent and integrated into a master engine by the means of a usb drive (all the work is done offline).

One way is to always copy all the data from the 3 machines each month and then copy them to the master machine. Is there a more practical solution or technology to that on Postgres?


texts:
Goal:
Retreive a list of data from a stored procedure that is acting as a database name. When you have retrieved the list from stored procedure, I want to make another new data manipulation.

Problem:
I would like to use [sp_Test] to retrieve the data from the syntax code "FROM" but it does not work. Do you know how to solve it?

create table datatable (id int,
                        name varchar(100),
                        email varchar(10),
                        phone varchar(10),
                        cellphone varchar(10),
                        none varchar(10)
                       );

insert into datatable values
  (1, 'productname', 'A', 'A', 'A', 'A'),
  (2, 'cost', '20', 'A', 'A', 'A'),
  (3, 'active', 'Y', 'A', 'A', 'A');

insert into datatable values
  (4, 'productname', 'A', 'A', 'A', 'A'),
  (5, 'cost', '20', 'A', 'A', 'A'),
  (6, 'active', 'Y', 'A', 'A', 'A');

insert into datatable values
  (7, 'productname', 'A', 'A', 'A', 'A'),
  (8, 'cost', '20', 'A', 'A', 'A'),
  (9, 'active', 'Y', 'A', 'A', 'A');

CREATE PROCEDURE [sp_Test]
as
begin
    set nocount on

    SELECT a.name, a.email, a.phone
    FROM datatable a

end


texts: Scale vs. Precision - Why this definition?

texts:
From the Microsoft documentation concerning T-SQL / SQL Server:

  Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2.

To the naive non-expert (=me) this is always a bit confusing. I would have expected it the other way round.

How does the total number of digits relate to the everyday concept of precision (how 'exact' something is measured)?
How can I think of the number of decimals in terms of scale (how 'big' something is)?

Just to be clear: This is not a rant disguised as a question, and more than mere curiosity. I am convinced that there is a good reason for this terminology, and I think I can learn about databases by understanding the underlying thinking here. (If this question is considered too basic for here, I understand.)


texts:
Can I clarify this settings linked server be made using the login's current security context.
If I connect to serverA using windows account JOESmith and create the linked server using the above option to serverB.

What happen when BOBSmith connect to serverA and query serverB. does it use JOESmith credential or BOBSmith?

sorry if this sound's like a stupid question because "current security context" should imply it will use BOBSmith's account to serverB

But I just want to clarify


texts: How can I use LOAD DATA LOCAL INFILE for specific columns?

texts:
I have a directory of text files, as the filename is the id, and the content text for this table:
CREATE TABLE texts
(
ID int(11) unsigned,
Added date,
Text MEDIUMTEXT,
PRIMARY KEY(ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci

I use PHP glob function to find files to INSERT file by file (row by row) as
INSERT INTO texts (ID,Added,Text) VALUES ($id,CURDATE(),'$text')";

$id is the filename, and $text the entire content of the file.
How can I use LOAD DATA LOCAL INFILE to directly INSERT into the database and to avoid reading the large text into PHP variable?


texts: mysql rds read replica replication error

texts:
I use amazon rds and have a cross region replica. While I was doing some schema changes my read replica gave me this error and replication stopped.

  Message : Read Replica Replication Error -  SQLError:  1146, reason:
  Error Table phppoint_koreannoona.phppos_items doesn't exist on query.
  Default database: DB_NAME;. Query: ALTER TABLE phppos_items DROP
  COLUMN aaatex_qb_item_name

What would cause this? The table is in the master. My migration DID remove that field but entire table was missing in slave.

I had to rebuild slave.


texts:
I am looking for help laying out a database that stores information similar to a class definition and then also instances of those 'classes' (but not referring to an ORD).  The specific example is that there are types of electronic modules (AbstractModule) that have connectors on them (AbstractConnector) and a given type of connector will have some number of contacts in it (AbstractContact).  This is straightforword, AbstractContact has a foreign key to AbstractConnector which has a foreign key to AbstractModule.  With those tables filled out and linked I have a set of possible electronic modules defined.

But how do I now store an instance of an abstract module?  The additional information is primarily just the connections made to each pin of each connector on the module.  Is it possible to enforce reasonable constraints in the database or is this a case where it must be enforced by the application?  If we just look at the modules and connectors I would expect

Connector FK to its Module

Connector FK to its AbstractConnector

Module FK to its AbstractModule

My concern is that there are two paths from Connector to AbstractModule (Connector>AbstractConnector>AbstractModule and Connector>Module>AbstractModule) and I don't see how (other than application code) to enforce that they both end up at the same module.  If the answer is just that, use the application, that's fine.  I just want to be sure I'm not overlooking something or horribly mis-designing the database.

Note that it may well not be necessary to have both Connector and AbstractConnector, but either way I believe the branching and merging foreign key path will still exist.

Thanks for any comments, links, pointers...  I can't even figure out how to tag this.


texts: Confused about the Oracle CONTROL_FILES initialization parameter

texts:
This is the explanation of this parameter from Oracle. This is the description of the description in the explanation. From the documentation we know that this parameter is not modifiable (Modifiable: No). But in fact I can modify it:

sys@ORCL>alter system set control_files = '/u01/app/oracle/oradata/orcl/control01.ctl,/u01/app/oracle/flash_recovery_area/orcl/control02.ctl';
alter system set control_files = '/u01/app/oracle/oradata/orcl/control01.ctl,/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

sys@ORCL>alter system set control_files = '/u01/app/oracle/oradata/orcl/control01.ctl,/u01/app/oracle/flash_recovery_area/orcl/control02.ctl' scope = spfile;

System altered.

sys@ORCL>

So, what does it mean by Modifiable: No in the documentation?

BTW, this is my Oracle version:

sys@ORCL>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0  Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

sys@ORCL>

Thanks,


texts:
It was my understanding that MyISAM uses table locking during inserts and updates.
To me that meant that read and write by other queries on that table is disallowed until the first insertion or update is finished.

However, I am reading the book High Performance MySQL and it clearly states on page 64 that MyISAM allows "Table with concurrent inserts".

Does this mean that MyISAM allows concurrent writes but locks all reads during the writes?  Kindly explain.


texts: "ERROR: could not read from config file" MongoDB

texts:
I'm trying to configure MongoDB in OS X Mountain Lion. I've installed with Homebrew; I have followed lots of tutorials, but I have got an issue which I haven't been able to solved.

The issue is about mongod.conf file. I've got it in /usr/local/Cellar/mongodb/2.4.6/ directory and it contains the following code:

fork = true
bind_ip = 127.0.0.1
port = 27017
quiet = true
dbpath = /usr/local/Cellar/mongodb/2.4.6/data/db
logpath = /usr/local/Cellar/mongodb/2.4.6/data/log/mongod.log
logappend = true
journal = true

Why if I execute mongod in the terminal appears a ERROR: could not read from config file?


texts: SQL Server Change Data Capture order of records

texts:
Does anybody know if change data capture functions return always data in the order of occurence?. In a CDC system is quite important to get the data modifications in the order they have been generated.

Looking to the cdc.fn_cdc_get_all_changes_XXX functions, there is no order by clause in any of the underlying queries.

Thanks


texts: How to get rows based on the where inside a column?

texts:
I am working with Oracle and I have a table like this:
| id | wh     |
|----|--------|
| 1  | 1 = 1  |
| 2  | 1 != 1 |
| 3  | 1 != 2 |

I would like to perform a select * from Where table, where the condition of the where is what is inside the 'wh' column. That it was iterating through rows determining if the 'wh' column is valid or not according to the 'wh' field of that row.
For example in row 1 it would be: select * from table where 1 = 1, in row two: select * from table where 1! = 1 and so on.


texts: SQL Full Text Search - Result Order changing

texts:
I am using Full Search text to implement search functionality. Basically I will have to do search on all the columns with the search string. Search string will have combination of values from different columns. However I will not know what all columns will I have to search. For example, using the following query in full text SQL search to find customer based on Last Name, Address and Zip

DECLARE @SearchString VARCHAR(200)
SET @SearchString = 'Adam OR West OR 60608'

SELECT Id, First_Name, Last_Name, Address,City,State,Zip, Rank
FROM Patient_Ex PAT
    INNER JOIN CONTAINSTABLE(Patient_Ex,*, @SearchString) CT
        ON PAT.Id = CT.[Key]

Results of the above query

Id  FirstName   LastName    Address City    State   Zip RankID
25  Adam Avenue Adam     North Albay Avenue Chicago IL  60619   272
26  Adam Avenue West    South Escaaba Avenue    Chicago IL  60620   272
27  Adam Avenue Adam     eclaire Avenue Chicago IL  60634   272
28  Adam Avenue West    Leamington Avenue   Chicago IL  60657   272
29  Adam Avenue Adam    North England Avenue    Chicago IL  60660   272
30  Adam Avenue West    West 59th Street    Chicago IL  60608   272
31  Adam Avenue Adam    West 21st Place Chicago IL  60608   272
32  Adam Avenue West    South Albay Avenue  Chicago IL  60655   272

However, I am expecting the result to be something like this below. If the same row has all the three search criteria matching I would like that to be displayed at the top. In this scenario, a record having Last name as Adam, Address containing West and Zip 60608 should be displayed as top records

Id  FirstName   LastName    Address City    State   Zip RankID
30  Adam Avenue Adam    West 59th Street    Chicago IL  60608   272
31  Adam Avenue West    West 21st Place Chicago IL  60608   272
27  Adam Avenue Adam    eclaire Avenue  Chicago IL  60634   272
28  Adam Avenue West    Leamington Avenue   Chicago IL  60657   272
29  Adam Avenue Adam    North England Avenue    Chicago IL  60660   272
25  Adam Avenue West    North Albany Avenue Chicago IL  60619   272
26  Adam Avenue Adam    South Escanaba Avenue   Chicago IL  60620   272
32  Adam Avenue West    South California Avenue Chicago IL  60655   272

How do I modify the query to get the result similar to the above one.
I searched the forum and found a similar question, however there is no answer for it. Any help to resolve the issue will be of great help.

Thanks in advance,

Santha


texts: How to limit databases number that a MySQL user can create?

texts: Is there any way to limit the number of databases, that a specific MySQL user can create? (as like as in any web hosting), they give each user the ability of creating specific number of databases.


texts: Is it possible in a view to reference a linked server implicitly rather than explicitly?

texts:
I'm using views and stored procedures that reference a linked server:

SELECT Id, Name
FROM [LinkedServer].Database.dbo.FooView

I would like to hide the [LinkedServer].Database or at least the [LinkedServer] part of my views and stored procedures. This way I would be able to have "server-agnostic" code, that would be easily compared between production and dev servers.

This could be done at the server level (like the linked server) or at the database level (where the views and the stored procedures using it are).


texts: How to create a Linked Server from SQL Server 2008 to DB2?

texts:
( This is an edit...the real post was really big )

I'm having problem with my SQL Server 2008 to DB2 linked server.

I'm getting this error:

  TITLE: Microsoft SQL Server Management Studio

  The linked server has been created but failed a connection test. Do you want to keep the linked server?

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

  Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for linked server "DB2LINKEDSERVER". OLE DB provider "DB2OLEDB" for linked server "DB2LINKEDSERVER" returned message "". (Microsoft SQL Server, Error: 7303)

When click "connect" on DSN:

Error in SQL Server Management Studio:


texts: Why mysql multiple column index match from left?

texts: Why MySQL using union index from left? For example union index(a,b,c),(a) could using index,(a,b) could using index,but (b,c) could not using index!!!Any useful tool to probe MySQL index file structure? How could this happen?


texts: Upgrade and migrate SQL Server 2014 AlwaysOn AGs to SQL Server 2016, using the existing WSFC name

texts:
Here are the components:
WSFC Name: SQLPROD1
Existing Nodes in this "cluster": SQL2014OnSite and SQL2014OffSite
New Nodes to be added (?) to SQLPROD1: SQL2016OnSite and SQL2016OffSite
All 4 servers running Windows Server 2012 R2 Enterprise. All 4 instances of SQL Server are Enterprise Edition.

I successfully added the two new servers to the WSFC. I successfully configured the two new SQL2016 instances to use AlwaysOn.

For testing purposes, I created a new AOAG named TestMigrate with a single database, AdventureWorks2014 and configured it to be identical to the 3 production AOAGs (Asynchronous commit, manual failover and non-readable secondary).

I then proceeded to add the 2 new nodes as replicas to the AG (worked great). I read this article (https://msdn.microsoft.com/en-us/library/dn178483.aspx), in the section titled "Availability Group with One Remote Secondary Replica" that indicated it was necessary to change the Availability Mode to Synchronous Commit, then failover to the new primary node, and finally change the Availability Mode back to Asynchronous Commit. Up to this point, everything worked as expected.

The problem happened when I removed the original 2 nodes from the AG. Within a few seconds, both replica copies of the database switched to "Restoring", and the AG disappeared from both 2016 instances.  Is there some "background" metadata that links the AG to the instance where it was created?

Also, it was my assumption that this was the best way to upgrade/migrate these AGs, but since it completely dropped the AG once I removed the two original nodes/instances, I'm guessing it's not...or I have missed a rather crucial step. I've searched high and low online for a straight-forward approach to accomplish this task, but haven't found anything that addresses this specific requirement. Several "similar" tasks, but not quite the same...and not close enough to give me the "missing piece(s)".

Any suggestions or recommendations would be very much appreciated.


texts: PostgreSQL JSONB: How to get an element from Array of Embedded Documents

texts:
I am comparing MongoDB with PostgreSQL query abilities. I struggle a bit with finding simple equivalent for the following MongoDB queries.  What are the equivalent commands in PostgreSQL JSONB to MongoDB's:

Single Element Satisfies the Criteria
db.inventory.find( { memos: { $elemMatch: { memo: 'on time', by: 'shipping' } } } )

Combination of Elements Satisfies the Criteria
db.inventory.find( { 'memos.memo': 'on time', 'memos.by': 'shipping' } )

From: https://docs.mongodb.org/manual/tutorial/query-documents/#array-of-embedded-documents


texts: Update mysql Database - am I doing it right?

texts:
I have about 15 tables that are linked together in my database (mariadb - parent-child with FK).

If I now update something with VBA (over ADODB, mysql-connector), I update first with my code the necessary "Child" tables with the new IDs/values first, close these tables and then add this new IDs/values to the "Parent" table. Otherwise, there is also an error if I first update the value in the "parent" table but not in the linked tables. I have found out that it is probably because of my "restrict" instead of "update" setting. Since it works that way and it was a lot of work to write the code (much code) I would leave it that way. Is this a problem and not common or can I leave it like this (-> update all child tables and then the parent table at the end)?

Am I going the right way or is it completely inconvenient to write the code like that? But what would the usual way?


texts: How to log data changes in Access at the table level?

texts:
I support an Access database for a non-profit organization.  We have some issues with data mysteriously changing, and there been some, um, friendly debate over whether this is caused by users who are not the most computer-literate or by some of my background processing.

I've added some checks at the UI level for these conditions, but I'd like to put some checks at the database table level to log when certain fields are changed.  This way I'll know about the change whether it was initiated by a user or just from an update query running in the background.  Normally I would use an on update trigger, but to my knowledge Access doesn't support triggers.  Is there any other logging or trigger-like functionality available in Access?


texts: Create Random Word to obfuscate a SQL Column

texts:
Does anyone have any SQL code to automatically generate words (all letter, no number).

Is there any inline SQL to perform this on a column?

We are trying to obfuscate first/last name and other word columns in table.

This answer currently does not work on column, because it generates the same result within a column.

select top (@stringlength) char(abs(checksum(newid())) % 26 + ascii('A'))
from sys.all_objects
for xml path('')

I tried this, but functions are slow, inline queries can be faster, feel free to improve or rewrite below

CREATE VIEW [dbo].[RandomValueVw]
AS
SELECT RAND() as random_value

CREATE function dbo.RandomWordGenerate(@length int)
returns varchar(50)
as begin

    DECLARE
        @text nvarchar(255),
        @i int

    SET @i = 0
    SET @text = ''

    WHILE (@i < @length)
    BEGIN
        SET @text = @text + CHAR((select * from [dbo].[RandomValueVw]) * 26 + 65)
        SET @i = @i + 1
    END

    return @text

end


texts: Error attaching database after deleting NDF file

texts:
I have deleted .ndf file by mistake from a SQL Server 2008 database. While trying to attach a previous copy, SSMS raises this error:

  Attach database failed for Server 'MDSWIN34'.
  (Microsoft.SqlServer.Smo)

  ADDITIONAL INFORMATION:

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

  The operating system returned error 38(Reached the end of the file.)
  to SQL Server during a read at offset 0000000000000000 in file
  'C:\Program Files\Microsoft SQL
  Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\template.ndf'. Additional
  messages in the SQL Server error log and system event log may provide
  more detail. This is a severe system-level error condition that
  threatens database integrity and must be corrected immediately.
  Complete a full database consistency check (DBCC CHECKDB). This error
  can be caused by many factors; for more information, see SQL Server
  Books Online. (Microsoft SQL Server, Error: 823)

What is the reason for this? Have I lost all of my data?


texts: Getting a "Request to run job refused because the job is already running" When no job is running?

texts:
So have an ETL job that runs through integration services that I start with a SQL job name StartStageStore. The last step of this job is to start another job and report success. The job this calls to is referred to as LoopStageStore. All LoopStageStore does is execute a stored procedure that Restarts the StartStageStore job all over again this way we are constantly pulling source data into our system. This job has been running for sever weeks now without failure. Until the last two nights I started getting this error "Executed as user: AgentUser. SQLServerAgent Error: Request to run job Stage/Store AgentUser refused because the job is already running from a request by User AgentUser. [SQLSTATE 42000] (Error 22022).  The step failed."

This should not be possible as the run StartStageStore job finishes and is no longer running when LoopStageStore has started.

Here we can see the last step executed at 12:19:14.

And the Next Job Fails at 12:19:15 Saying that StartStageStore is currently running. I already have a workaround for this where I just throw a Delay on the Second job. I'm just wondering if anyone knows why this would happen.

BEGIN
WAITFOR DELAY '00:01';
    EXECUTE dbo.LoopStageStore
END; GO


texts: Merge Statement + Replication difference for SQL Server 2012

texts:
We have a SSIS package that will pull from a source database and run a merge statement to put the data into a "Master" database.  (Eventually the "Master" database will be the source of truth, but we are a while away from that.)

After the Master Database gets the data, it replicates it down to several other databases.

With Sql Server 2008 R2 this all worked great.

But with SQL Server 2012 we are noticing that there are a bunch of delete statements for these tables being replicated.

The Merge Statement in the SSIS Package does not delete anything.

when NOT MATCHED BY SOURCE then
    update set IsActive = 0

So I am left to wonder why this is happening.  Could SQL Server 2012 be translating my MATCHED Update to a delete and insert? (that seems insane, but I can't seem to figure out why this is happening).

NOTE: We turned off replication of delete statements and the problem stopped, but I want to know what is going on.

Why would I be seeing so many deletes when the source seems unchanged?

Is there a way I can see the transactions posted against a table?

NOTE: I tried doing a SQL Trace, but it did not show any deletes.  Only the merge I described above.


texts: Oracle 11g - PL/SQL block - Unable to use a newly added column in subsequent DML

texts:
begin
   execute immedate 'alter table my_table add new_column varchar2(30)';
   update my_table set new_column=old_column;
end;

Here the update statement fails stating that the new_column is not available in the table. I knew DDLs are implicity committed but just for thoroughness sake, I added an explicit commit statement before the update statement, but the error still exists. Any inputs is much appreciated.


texts: Optimal Hardware for > 30K Requests / min?

texts:
We have some performance issues with one of our Test-Machines a MySQL 5.7.21 Standalone Server.

We have: 8 CPUs 16GB RAM (buffer_pool_size 5.6 G) and need to handle > 30 K Requests / minute.

When we run our tests the CPU workload raised to 100% and we got several issues (long query run time etc...)

What is your hardware recommendation for this workload?
ty in advance br Andreas


texts:
I have a query like this:

select * from questions as q
    left join question_filters as qf on q.id = qf.question_id
    left join users as u on q.user_id = u.id
    left join trainers as t on q.trainer_id = t.id
where q.deleted_at is null
    and qf.filter_id in (3,4)
    and q.status = 'published'
    and q.published_at < now()
    group by q.id
having count(distinct qf.filter_id) = 2

I have an "ALL IN" case, and I found this links works for me: https://stackoverflow.com/questions/7505045/mysql-in-operator-must-match-all-values.

But now I want to make a count for that same query:
My problem:
This query gaves me the error "Duplicate column name "ID" (all the tables in the join have ID)

select count(*) from ((select * from questions as q
    left join question_filters as qf on q.id = qf.question_id
    left join users as u on q.user_id = u.id
    left join trainers as t on q.trainer_id = t.id
where q.deleted_at is null
    and qf.filter_id in (3,4)
    and q.status = 'published'
    and q.published_at < now()
    group by q.id
having count(distinct qf.filter_id) = 2)) as first_query

Another attempt:

select count(q.id) from questions as q
    left join question_filters as qf on q.id = qf.question_id
    left join users as u on q.user_id = u.id
    left join trainers as t on q.trainer_id = t.id
where q.deleted_at is null
    and qf.filter_id in (3)
    and q.status = 'published'
    and q.published_at < now()
    group by q.id
having count(distinct qf.filter_id) = 1

returns many results, one for each result.
Thank you


texts: Altering partition functions dynamically by utilizing triggers

texts:
I am wanting to utilize partitioning based on a [TenantId] (and later in conjunction with date ranges). Instead of needing to manually insert the latest value within the PARTITION FUNCTION, I thought of creating a TRIGGER AFTER INSERT to pull the [TenantId] value and ALTER PARTITION FUNCTION to add it to the SPLIT RANGE. However, I am running into an unexpected error:

  Cannot execute ALTER PARTITION FUNCTION on/using table 'Tenant' since the table is the target table or part of cascading actions of a currently executing trigger.

First, I'm creating the PARTITION FUNCTION [PF_Tenant_Isolation] and PARTITION SCHEME [PS_Tenant_Isolation] for partitioning on the [TenantId].

CREATE PARTITION FUNCTION [PF_Tenant_Isolation] ([int])
    AS RANGE LEFT FOR VALUES (1);
GO

CREATE PARTITION SCHEME [PS_Tenant_Isolation]
    AS PARTITION [PF_Tenant_Isolation]
    ALL TO ([Auth]);
GO

Following this, I am creating the [Tenant] table against the newly created partition scheme.

IF OBJECT_ID('[Auth].[Tenant]', 'U') IS NULL
BEGIN
    CREATE TABLE [Auth].[Tenant] (
        [TenantId] [int] IDENTITY(1,1)
        ,[TenantActive] [bit] NOT NULL CONSTRAINT [DF_Tenant_TenantActive] DEFAULT 1
        ,[TenantName] [varchar](256) NOT NULL
        ,CONSTRAINT [PK_Tenant_TenantId] PRIMARY KEY CLUSTERED ([TenantId] ASC)
    ) ON [PS_Tenant_Isolation]([TenantId]);
END

I seed the first value before creating the trigger.

INSERT INTO [Auth].[Tenant]
VALUES (1,'Partition Trigger Test A');

I create the trigger against the [Tenant] table.

CREATE TRIGGER [TR_Tenant_Isolation] ON [Auth].[Tenant]
AFTER INSERT
AS
BEGIN
    DECLARE @MaxInsertedId int
    SET @MaxInsertedId = (SELECT MAX([TenantId]) FROM inserted)

    ALTER PARTITION SCHEME [PS_Tenant_Isolation]
        NEXT USED [Auth];

    ALTER PARTITION FUNCTION [PF_Tenant_Isolation]()
        SPLIT RANGE (@MaxInsertedId);
END

I follow this up with attempting to insert the second [Tenant] value.

INSERT INTO [Auth].[Tenant]
VALUES (1,'Partition Trigger Test B');

This is when the error described above pops up. Based the error itself as well reading the Technet arguments, I understand the issue to be in utilizing AFTER INSERT. Since the partition action of the transaction relies on utilizing the range value within the partition function, the ALTER PARTITION SCHEME fails, and thus the entire transaction as well.

  AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires.

I have looked into INSTEAD OF INSERT but have not had any success. The trigger fires once and updates the SPLIT RANGE with a value of 0 (implicitly converted from NULL). I believe this is due to the IDENTITY not being properly captured in the scope of the transaction.

CREATE TRIGGER [TR_Tenant_Isolation] ON [Auth].[Tenant]
INSTEAD OF INSERT
AS
BEGIN
    DECLARE @MaxInsertedId int
    SET @MaxInsertedId =  (SELECT [TenantId] FROM inserted)

    ALTER PARTITION SCHEME [PS_Tenant_Isolation]
        NEXT USED [Auth];

    ALTER PARTITION FUNCTION [PF_Tenant_Isolation]()
        SPLIT RANGE (@MaxInsertedId);

    INSERT INTO [Auth].[Tenant] ([TenantActive], [TenantName])
    SELECT [TenantActive], [TenantName]
    FROM inserted;
END

Subsequent row inserts into [Tenant] produce an additional error due to it trying to enter 0 (NULL).

  Duplicate range boundary values are not allowed in partition function boundary values list. The boundary value being added is already present at ordinal 1 of the boundary value list.

How can I work around this? Do I need to explicitly set the IDENTITY value of [TenantId] in conjunction with INSTEAD OF INSERT? New inserts into [Tenant] will be rather sporadic and minimal, but [TenantId] will be a constraining key across other tables. This is why I decided to investigate this implementation method so to dynamically alter the partition function.


texts: 11.2GB text file import MySQL or PostgreSQL

texts:
I am out of my depth a bit perhaps but submit this query hoping someone has some experience importing large (11.2GB) tab delimited text file (2.2GB tar.gz file) into either MySQL 5.5.41 or PostgreSQL 9.3.6. Double precision for the fields will be required as it is spatial data (Latitude, Longitude and Elevation).

I have MySQL and PostgreSQL setup on Ubuntu 14.04 using phpMyAdmin and phpPGAdmin to interact with the servers. However I realise that command line interaction will probably be better.

I have read a bit and there seems to be a way to split data for MySQL (SQLDumpSplitter2 - http://www.rusiczki.net/2007/01/24/sql-dump-file-splitter/).

Any suggestions or alternative options greatly appreciated.

Thanks
Paul


texts: Grant permissions on views, deny select on tables

texts:
I have a MySQL user and I want it to view ONLY the views I want and not any other table in the database. I've granted this user permissions only on certain views as following:

GRANT SHOW VIEW ON `myDatabase`.`awesome_view` TO 'thisUser'@'%'

If I do a show grants; statement I can only see this permissions as expected.
However I'd like this user to query JUST the views and not the tables that are related to these views, but I can't find a way to do this. It seems to be that if I want the user to do a select on the view, the select must also be granted for the table, or am I wrong?

If I deny the selectstatement in the rest of the tables, and in the command line I try to do a select I got the following:

SELECT * FROM myDatabase.fordibenForYouTable;
ERROR 1142 (42000): SELECT command denied to user 'thisUser'@'localhost' for table 'fordibenForYouTable'

That's what I want indeed, but I also got denied if I select the view data.

Is there a way I can make available to the user just the views and not the tables?


texts: MSSQL Page Split Spike on AlwaysOn Readable Secondary

texts: Recently I caught a massive page-split spike on an AlwaysOn Secondary instance that was not mimicked in any way by the active primary. I'm interested in what might cause this on a readable secondary since everything is "read-only".


texts: ip address record in sybase ASE auditing database?

texts:
everone.

I installed the sybsecurity database for auditing in sybase ase 15.5. The installation is OK. I configured the audit commands, for example, for auditing the sa user, and other users of the database.

I see that the column extrainfo for the table sysaudits_01 shows additional information. I need to add the IP address of of users who perform certain commands: for example, GRANT, INSERT, 'UPDATE,DELETE,SELECT, andCREATE`.

I see that rows in login attempts include the IP address in the extrainfo column.

I checked the code of the file C:\Sybase\ASE-15_0\scripts\instsecu but I can't find how to add the IP address to extrainfo to my table.

How can I capture the IP address for certain actions, and record it in extrainfo?


texts: Does SQLBackup2Azure delete the blobs as well?

texts: I am not sure if this questions belongs here or in server fault, apologies if it belongs there.  I am using MS SQL 2012 on an azure VM, and am planning on utilizing the SQLbackup2Azure tool to store the backup files directly in blob storage.  Does anyone know if deleting the stub files locally on the server will also trigger the azure storage service to delete the storage blobs?  My hunch is that it will not and that I should utilize a powershell script (like  this one) to remove them after a time.


texts: How to get time_zone from other sessions

texts: Is there some way to find out the time_zone from other sessions defined in session level (alter session set time_zone = ..)?


texts:
Due to a business need, I may need to disable transactional replication in my environment (Sql 2k -> Sql 2008). Functionally, I understand this to mean that I will need to drop subscriptions and articles. Is getting the create scripts enough to restore replication back to original state when the conflicting need is addressed?

Thanks.


texts:
I am attempting to do a calculation in SQL Server 2008 R2 but I keep getting an error of

  Msg 245, Level 16, State 1, Line 10
  Conversion failed when converting the varchar value '.05' to data type int.

Altering table structure is not an option, is there a way I can alter the query so that this will succesfully execute?

DECLARE @Test1 TABLE
(
  field1 VARCHAR(10),
  field2 INT
);

INSERT INTO @Test1 ( field1, field2 )
    VALUES ( '.05', 12 );

SELECT field1 / field2
FROM @Test1;


texts: Best way to copy a SQL Server database from a remote server to local computer?

texts: More specific my db is hosted at appharbor and I want to copy that db to my local SQL Server Express 2012. Up until now I have used Management Studio to export the data and later used the schema compare feature in Visual Studio 2012, but I guess there must be an easier way


texts: Warning Console code page (437) differs from Windows code page (1252). 8-bit characters might not work correctly in PostgreSQL

texts:
Whenever I access psql, I get the following message:

psql (9.6.2)
WARNING: Console code page (437) differs from Windows code page (1252)
     8-bit characters might not work correctly. See psql reference
     page "Notes for Windows users" for details.
Type "help" for help.

postgres=#

I tried command

cmd.exe /c chcp 1252

before running runpsql.bat file in command line. And there, it runs fine without the error. But accessing psql again after closing command prompt, throws the same warning. How do I solve this warning within SQL Shell?

I am using windows 10 OS.

Edit:

This is what I did:

Microsoft Windows [Version 10.0.10586]
(c) 2015 Microsoft Corporation. All rights reserved.

C:\Users\Sarthak Joshi>E:

E:\>cd PostgreSQL

E:\PostgreSQL>cd scripts

E:\PostgreSQL\scripts>chcp 1252
Active code page: 1252

E:\PostgreSQL\scripts>runpsql.bat
Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:
Password for user postgres:
psql (9.6.2)
Type "help" for help.

postgres=#

But after exiting from cmd, and firing up shell like this:

Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:
Password for user postgres:
psql (9.6.2)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=#

It still throws the same warning...


texts: Get multiple columns from a function depending on outer group by

texts:
I have first a selection of trip_id (for a train or a bus e.g.) in a (sub-)Table A

trip_id

350
351
360

and want to use a self-written function which takes the used trip_id and gives back the a TABLE with trip_id, used roads (in form of a road_id) and the sequence seq of stops for this trip (here for example trip no. 350):

SELECT * FROM getroutepertrip(350)
-----
trip_id   road_id    seq

350       a1        1
350       g4        2
350       a2        3
 .        .         .
 .        .         .
 .        .         .

The desired oucome of the whole problem should be like this above but for all the trips from the CTE subselect. Unfortunately, my function only can take a single trip_id. My first and only attempt is of course wrong since a subquery can't contain multiple columns

WITH A AS (SELECT DISTINCT trip_id FROM stop_times)

SELECT trip_id,  (SELECT * FROM getroutepertrip(trip_id))
FROM A
GROUP BY trip_id
ORDER by trip_id

Hopefully somebody can help me here, it feels like I'm mentally in a deadlock...


texts:
I need to store a list of items and that list belongs to a single user. The lists are going to be huge, so this has to be done as efficiently as possible. The lists also have to be modifiable to a small degree (able to add new items).

This is my (possible) solution:
Have a table items, where we store the data of our items. Table lists contains info about our lists (name, user_id etc). Table lists_lengths contains our lists lengths, e.g:

list_id | item_id_start | item_id_end
------------------------------------
1       | 1             | 200
2       | 201           | 220
1       | 221           | 300

If the lists get a lot of modifications, this might end up being a poor design though. Is this good or is the blatant list_id in the items table just the way to go?


texts:
Following error occured while using installing MariaDB Audit Plugin Library (i.e. server_audit-1.1.8.tar.gz) in 5.7.5-m15 MySQL Community Server (GPL) in Centos release 6.5:

ERROR 1126 (HY000): Can't open shared library '/usr/lib64/mysql/plugin/server_audit.so' (errno: 13 /usr/lib64/mysql/plugin/server_audit.so: undefined symbol: _my_thread_var)

Based on the MariaDB documenentation claims that plugin should workin MariaDB or MySQL Server with version 5.5 or newer (MySQL 5.5.14, MariaDB 5.5.20).

I also attempt to use same plugin in MYSQL Server version: 5.6.22 MySQL Community Server (GPL), it works perfectly.

Anyone experienced and resolved above issue?


texts: Difference between two XMLTYPE data types

texts:
In Oracle Database, there are two XML-related data types, SYS.XMLTYPE and PUBLIC.XMLTYPE.
What is the difference between them?


texts: "No Loop" Constraint for hierarchical data in postgresql

texts:
I've got table like this in postgres:

id primary key
parent_id foreign key on id
some data columns

I need no a constrain which prevents cycles on the parent relations. There might be more tree in the table (root of a tree is null in parent_id).

How to achieve this goal?

Thanks

Daniel


texts: Can I use only OrientDB without any RDBMS?

texts:
I'm struggling with this problem and I really need an advice. My project mainly needs a Social structure, users can be connected with some friendship relations, can create groups and so on. In this scenario a Graph database is a must.

However, each user also has some information that fit relational model. For example, purchases, points, historical data and so on.

At first, I thought that using two types of database could solve all my problems, so I've decided for OrientDB and MySQL. The main problem of this approach is that it's hard to mantain consistency between two databases if we are in a distributed scenario, where billion of users does operation on two databases. Moreover, MySQL as a RDBMS has some limitation for scaling.

So, I've thought: Why not use just OrientDB?

As far as I understand with its multi-model architecture and ACID transactions, I can reach the same RDBMS goal (lot of hooks), and at same time I haven't any problem with consistency. I can improve scaling problem too.

So, is it safe to use just one database, like OrientDB, to create a design like mine?


texts: Should I use a composite or single-column index?

texts:
I have the following columns in my database table (Medicines).

ID bigint,
MedicineName nvarchar(50),
BrandName nvarchar(50),
MedicineCode nvarchar(20),
and price,quantity.

I am making a stored procedure with this query:

create proc searchmedicine
@name nvarchar(50)=null,@brand nvarchar(50)=null, @code nvarchar(20)=null
as
select * from Medicines
where MedicineName= Case WHEN @name IS NOT NULL THEN @name ELSE MedicineName END
AND BrandName=Case WHEN @brand IS NOT NULL THEN @brand ELSE BrandName END
AND MedicineCode=Case WHEN @code IS NOT NULL THEN @code ELSE MedicineCode END

Now I am confused which non-clustered index is more suitable to help optimize the query: composite or single column?


texts:
The following two queries return different values for index size.

SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id

And

sp_msforeachtable 'sp_spaceused ''?''''

Can anyone suggest exact query to find Index size for each tables from system views in sql server?


texts: Why does importing a 12 GB .sql file take more than 36 hours?

texts:
I've been waiting now for 36 hours for a 12 GB .sql file to be imported with a simple type site.sql | mysql command. I can see the ibdata1 is growing still, currently nearly 40 GB.

Considering the triggers and stored procedures are at the end of the .sql, I only think MySQL should be adding data and key indexes.

The site.sql was generated using this command from another server:

mysqldump -R -e --databases site --add-drop-database --add-create-database --add-drop-table -C --single-transaction --triggers

What's taking so long?


texts: T-SQL to Exclude the primary from List of AOAG Replicas

texts:
I am working on a task for which I need to exclude primary from the list .Can someone please help me to fix it.

select primary_replica  from sys.dm_hadr_availability_group_states

The above command gives Primary replica of AOAG.

select replica_server_name from sys.dm_hadr_availability_replica_cluster_states

The above command gives list of replicas of complete AOAG.

Now,I want to exclude primary_replica from the replica_server_name and I Want to see only secondary replicas.

So far I tried this but no luck.

select replica_server_name AS SecondaryReplicas from sys.dm_hadr_availability_replica_cluster_states INNER JOIN
sys.dm_hadr_availability_group_states ON sys.dm_hadr_availability_group_states.group_id= sys.dm_hadr_availability_group_states.group_id

Thanks in advance


texts:
So our MongoS instances use up A LOT of memory. More than even the shards and sometimes crash because of it.

Cluster structure

3x mongos
3x mongod Shards
3x mongod Replicas (one for each shard)
3x mongod Config Servers

Each of these is running on its own dedicated server with 64GB of RAM, except the config servers which are running on smaller servers, but they are not relevant here.

Situation

Our MongoS instances use up even more memory than that, but only after some intensive workload. Usually after some huge import or aggregation. And the problem is that they never drop it afterwards. If we do not do huge imports, the memory will stay between 6 and 10GB, but during an aggregation it will jump up to 95-99% memory usage and will stay there even after the aggregation is finished resulting in us getting alerts for high memory usage on our monitoring software all the time. But that's not the main problem, the problem is it crashes.

Problem

Now, I know mongo uses as much memory as it has available and frees it up as necessary, however, it doesn't really free it up correctly. It often crashes, in fact it's bad enough for me to have the following cronjob:

pgrep mongos || service mongos restart

The above cronjob pretty much checks if mongos is running and if not restarts it, i.e. starts it back up after a crash. And after a crash and restart, the memory naturally drops all the way to 5 to 10GB and stays there permanently, unless we do another heavy aggregation or import. In other words, mongo doesn't really need that memory and according to all the issues on stackoverflow, all the docs and what not, mongo is supposed to let the OS tell it when to free up memory. It's supposed to release memory when something or maybe even mongo itself needs to use it for something else, but it doesn't do that and crashes instead.

Thankfully, this isn't a super major problem as our 3 MongoS are behind a load balancer, so if one goes down the other 2 can keep our services online. But it's still an issue I don't want to have to deal with in production, especially as our database and load grows.

I've found this - https://jira.mongodb.org/browse/SERVER-24303
and have enabled aggressive decommit, but that did not help.

Thanks


texts: Why is cross db ownership chaining on by default for system DB

texts:
I am reading about cross db ownership chaining Server Configuration Option

It talks about how and why to use (or not) this option and says

  Setting cross db ownership chaining to 1 is not recommended unless all of the databases hosted by the instance of SQL Server must participate in cross-database ownership chaining and you are aware of the security implications of this setting.

It also offers this code to check.

SELECT is_db_chaining_on, name FROM sys.databases;

According to Server Configuration Options (SQL Server) the defualt value for 'cross db ownership chaining' is 0 (zero)

When I run it on my servers, old, new or whatever they all seem have it enabled on master, tempdb, and msdb.  I checked a few from SQL 2012 to SQL 2017, Enterprise and Standard editions.

I did find that it where it is set to on by default but not why.

tempdb settings Can not be altered
master settings Can not be altered
msdb settings Can be altered

If it is bad, why is it on by default on system databases?

Why am I not finding anything talking about why it is on by default on system databases?


texts: How expensive is a SELECT that returns an empty result set?

texts:
I'm analyzing the HTTP logs to one of our web services and I've noticed a lot of request to an endpoint such as /entities/0 which will end up effectively doing something like SELECT x, y, z FROM entities WHERE Id = 0, and I know for a fact there's no entity with Id = 0.

There are approximately 600,000 requests to this endpoint in a 24hr period. Each request opens it's own connection to the SQL Server and executes the query which returns an empty result.

I'm wondering how bad this actually is. What overhead is associated on the SQL Server with having an open connection, and what overhead with repeatedly returning the empty result for the same query (WHERE Id = 0), does SQL Server cache and optimize for this?

We're using SQL Server 2012.


texts: Large RAM use after import database - ubuntu server

texts:
My new VPS has 4GB of RAM. Before importing database system use about 1.2 GB of RAM. When I import into the database, using 250 MB SQL file, 25000 tables use of RAM goes to 97%, htop showing 3302 mb in cache.
Questions

Is this normal ?
If not, how to reduce RAM use?


texts:
I am using Oracle 11gR1. As a part of a project, I have to execute multiple update statements by disabling a few constraints (triggers and check constraints) which could result in a corrupted database.

If the database gets corrupted, I have to re-import it entirely, which is an additional task that is time consuming.

I have very little knowledge about the Oracle Flashback feature. Will Flashback help me to avoid these unnecessary imports? Can I set some stable point and switch back to there if anything goes wrong?

Can someone please give me brief overview on this feature?

If it fits my requirements, I will try to learn.


texts:
I only have an .mdf file of my database which is not attaching.

I am using the following command:

EXEC sp_attach_single_file_db @dbname = 'Test_Data',
@physname = N'D:\Test_Data\Test_Data.mdf';

and I get the following error message:

  Msg 1813, Level 16, State 2, Line 1
  Could not open new database 'Test_Data'. CREATE DATABASE is aborted.
  Msg 824, Level 24, State 2, Line 1
  SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:24; actual 0:0). It occurred during a
  read of page (1:24) in database ID 11 at offset 0x00000000030000 in
  file 'D:\Test_Data\Test_Data.mdf'.  Additional messages in the SQL
  Server error log or system event log may provide more detail. This is
  a severe error condition that threatens database integrity and must be
  corrected immediately. Complete a full database consistency check
  (DBCC CHECKDB). This error can be caused by many factors; for more
  information, see SQL Server Books Online.

Please help!


texts: Main Considerations When Moving From MS Access Programming to SQL Server

texts:
I am a 100% self taught MS Access programmer (main part of my job is programming), I am now building larger databases; still using MS Access as the UI but SQL Server to store all data and do more of the 'work'.

In essence my question is; what subject matters do I need to know for SQL Server that I probably didn't learn or need when using Access?

Not looking for you to tell me how to do anything, more what you think are the most important things I should go an research - there's a lot of subjects and a hell of a lot of detail, don't want to find myself a long way down a less valuable path...

Brain dump:

Maintenance: what are the most important (check database, reduce
database, update statistics, rebuild etc)
Indexes - I don't know as much as I should, is there a good book/
blog etc that can teach me the basics upwards?
Anything else I have missed (there's probably lots, as I said I am
new to SQL Server...)

If it helps I work for a mid sized retailer and the databases I predominantly work on cover such things as

Reporting platform (summarises sales/ receipts/ inventory etc from
main system and provides fast reporting)
A reconciling tool between a third part and what our stores put
through registers (imports data from third party and cross references
the transaction logs)
Stores all data to do with our promotions; product, prices,
projections, actual results etc


texts:
I have 3 tables (A, B and C) that connect 1:m:1.  I need to JOIN the 3 tables and apply a limit not on the total number of records, but on the number of records used from table A.  This means that more records in total than the limit will be returned.
Here is a fiddle.  This uses MySQL of:
SELECT A.id as Aid, A.name, C.id AS Cid, C.item
FROM A
INNER JOIN B b ON A.id = B.Aid
INNER JOIN C c ON B.Cid = C.id
WHERE A.owner = 1
ORDER BY A.id DESC
LIMIT 3

I would like to limit the number of records used from table A to 3, even though that means that 4 records will result (the ones connected to Aid's of 4, 3 and 2) because there are multiple records in table C that relate to those 3 records in table A.
I am wondering if perhaps this needs to use a SELECT (that has a LIMIT of 3) inside another SELECT that joins to tables B and C, but I don't know how to do this.


texts: Table shows id (auto-increment) is 0 in all rows

texts:
I am using laravel 5.2 to develop my web application. i recently uploaded the web application from localhost to my hosting account. I also imported MySQL tables from phpmyadmin localhost to my phpmyadmin localhost in my hosting account.
When I opened my phpmyadmin I noticed that in one of my tables, the id (which is set to auto-increment) is not working. It is displaying 0 in all the rows.
When I set the id to auto-increment in phpmyadmin it gives me the following error:

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

Table structure in phpmyadmin

SHOW CREATE TABLE


texts: mysql multi threading for a single query

texts:
I have big db of size 8GB and having 9 million contacts in this db. When we try to retrieve all contacts, it takes nearly 1 or 2 hour (in some cases result in timeout) to retreive all contacts for the excecution for a single query. Also I have 8 CPU and 24GB RAM, Xeon Processor, the mysql process won't distribute accross the 8 CPU's. My question is that, does we can enable multi threading for a single query.

Awaiting for your Reply


texts: Table not analyzed after partition has been split

texts:
On my table I split a subpartition successfully. After that I rebuild the indexes and gather statistics.

ALTER TABLE T_MO_DPI SPLIT SUBPARTITION ACTIVE_RELATIONS VALUES (24) INTO (
   SUBPARTITION ACTIVE_RELATIONS_2G,
   SUBPARTITION ACTIVE_RELATIONS);

exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_MO_DPI');

Now, when I check the statistics then apparently table is not analyzed, i.e. table statistics are not present:

SELECT TABLE_NAME, SUBPARTITION_NAME, NUM_ROWS, LAST_ANALYZED
FROM USER_TAB_SUBPARTITIONS
WHERE TABLE_NAME = 'T_MO_DPI';

+---------------------------------------------------------------+
|TABLE_NAME|SUBPARTITION_NAME      |NUM_ROWS|LAST_ANALYZED      |
+---------------------------------------------------------------+
|T_MO_DPI  |ACTIVE_ELEMENTS        |9146    |18.10.2018 22:18:51|
|T_MO_DPI  |ACTIVE_CELLS           |39812   |13.06.2019 22:32:33|
|T_MO_DPI  |ACTIVE_RELATIONS_2G    |        |                   |
|T_MO_DPI  |ACTIVE_RELATIONS       |        |                   |
|T_MO_DPI  |DELETED_ELEMENTS       |1160    |19.11.2018 22:18:14|
|T_MO_DPI  |DELETED_CELLS          |14102   |10.06.2019 22:17:53|
|T_MO_DPI  |DELETED_RELATIONS      |14865562|11.03.2019 23:10:10|
+---------------------------------------------------------------+

Any idea what I am doing wrong?

Running

exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_MO_DPI', 'ACTIVE_RELATIONS_2G');
exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_MO_DPI', 'ACTIVE_RELATIONS');

does not help.


texts: Trim characters from a field but only if it doesn't result in a duplicate?

texts:
I have a database of servers and some servers were entered with the fully qualified domain name.  I want to rename all of these.  However, in some instances the server has been entered both ways.  For instance, servername.domain.com and servername.  When I run my query below, these cause an error because of course that column can only contain unique records.

UPDATE ServerData
SET    ServerName = replace(ServerName, '.domain.com', '')
WHERE  ServerName LIKE '%.domain.com';

Error "Cannot insert duplicate key row in object 'dbo.ServerData' with unique index 'UK_ServerData'."

How can I modify the query to simply ignore records that would generate an error so I can deal with them individually later?


texts: Postgresql: Streaming Replication - determining wal sender process client port

texts:
My experience with asynchronous streaming replication has been great so far, both as a HA solution and as a backup strategy. One issue is delaying me from using streaming replication in production - The port used by the wal sender process seems to be determined automatically when I start the streaming replication (I assume between 32768 to 61000). I need to be able to specify the exact port in order to allow access. Does anyone have any information regarding this issue or even a workaround?
Thanks :)


texts: Datawarehouse Design: Combined Date Time dimension vs. Separate Day and Time dimensions and timezones

texts:
We are just starting design for a new data warehouse and we're trying to design how our date and time dimensions will work. We need to be able to support multiple timezones (probably at least GMT, IST, PST and EST). We were initially thinking that we would have one wide combined date time dimension down to maybe 15 minute granularity, that way we have one key in our fact tables and all the different date time data for all supported timezones are in one dimension table.
(i.e. Date Key, GMT Date, GMT Time, IST Date, IST Time, etc...)

Kimball suggests to have a separate day dimension from the time of day dimension to prevent the table from growing too large (The data warehouse toolkit p. 240) which sounds fine however that would mean we have two keys in our fact tables for each time zone we need to support (one for the date and one for the time of day).

As I'm very inexperienced in this area I'm hoping someone out there knows the tradeoffs between the two approaches, i.e. performance vs. the management of all the different time zone keys. Maybe there are other approaches too, I've seen some people talking about having a separate row in the fact table per timezone, but that seems like a problem if you fact tables are millions of rows then you need to quadruple it to add time zones.

If we do the 15 minute grain, we'll have 131,400 (24 * 15 * 365) rows per year in our date time dimension table which doesn't sound too horrid for performance but we won't know for sure till we test some prototype queries. The other concern with having separate time zone keys in the fact table is that the query has to join the dimension table to a different column based on the desired timezone, perhaps this is something that SSAS takes care of for you, I'm not sure.

thanks for any thoughts,
-Matt


texts: Specifying MySQL user hosts with IPv6 addresses

texts:
Inspired after making the move to IPv6 on my MySQL server, and intrigued by the lack of documentation regarding 'user'@'[IPv6]' nomenclature I proceeded to discover how to limit my users based on their IPv6 address.

My allocation is a /56 and so I wanted to limit my DB users to this prefix length.  My test host was 2001:DB8:111:6601::a01:120

The following didn't work at all:

2001:DB8:111:6600::
2001:DB8:111:6600::%
2001:DB8:111:6601::
2001:DB8:111:66%::
2001:DB8:0111:6601::%
2001:DB8:111:6600::/56
2001:DB8:111:6601::a01:120/56
2001:DB8:111:6601:0:0:a01:120/56
2001:DB8:111:6601::a01:120/128
2001:DB8:111:6601::/64
2001:DB8:111:6601::%/128
2001:DB8:111:6601::%/1
2001:DB8:111:6601::%/64
2001:DB8:111:6601::a01:0120

The following worked

2001:DB8:111:6601::% (too limiting as it's a /64, not /56)
2001:DB8:111:6601::a01:120 (too limiting as it's a host)
2001:DB8:111:66%::% (but may match 066x in the 7th and 8th bytes)
2001:DB8:111:66__::% (this appears to be the best fit)

Some notes:

don't use leading zeros in fields
don't bother with /prefixlen


texts: Is there a maximum length constraint for a postgres query?

texts: The app we are building might execute quite a big insert queries. Is there limit that my postgres query can have only a certain number of characters?


texts: lock escalation issue when using DDL trigger

texts:
I have a problem, I am trying to create a log table for my database so I can keep track of changes. I have created the following trigger that runs when a table is altered, created and dropped:
CREATE TRIGGER TableTrigger
ON DATABASE
FOR
    CREATE_TABLE,
    ALTER_TABLE,
    DROP_TABLE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO TableLog (
        EventDate,
        EventType,
        Existing_Table_Name,
        New_Table_Name,
        Changed_By
    )
    VALUES (
        GETDATE(),
        EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
        EVENTDATA(),
        EVENTDATA(),
        USER
    );
END;
GO

But for example I change a name of a column in a table the event data commandtext XML shows this
<TSQLCommand>
      <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
      <CommandText>ALTER TABLE dbo.Languages SET (LOCK_ESCALATION = TABLE)</CommandText>
</TSQLCommand>

Instead of the full command. How do I stop this from locking and letting me see the full command?


texts:
I am using my router with OpenWRT as a small server. To experiment a little with more advanced database administration in my programming projects I set up psql on it. The.server uses my HDD, which is attached via USB to the router. This is no high-availability.system,. primarily as a DB connection point inside my private network.

Everything is going alright so far. I just experienced that my hd-idle setup is not working anymore. I highly suspect PostgreSQL to be the reason for that. Since the drive is not really that quiet and it's standing in my living room, I was Keen to spin the HDD down if I didn't use it for an hour. I now register quite some regular I/O on the device and I guess it's logging or similar processes.
Is it possible to reduce the frequency of these helper processes down to maybe once a day?


texts: Trouble Creating AG Listener: The WSFC Cluster could not bring the Network Name resource

texts:
We are trying to create an AG Listener and it errors out on us:

It does however seem to create both the DNS entries and the virtual computer object in AD. However, because of this error message SQL backs out the creation of the Listener.

So far we have tried:

Giving the user that sql runs as permission to create objects in AD
Pre-creating the DNS entries and computer object in AD

The only reference I have found to this is at the following connect article, but I can't figure out what the exact fix is from the broken english: http://connect.microsoft.com/SQLServer/feedback/details/686548/denali-ctp3-incorrect-error-message-when-setting-up-listener-with-no-dns-access .


texts: Force MariaDB Galera to only create InnoDB tables

texts:
I successfully run a cluster of 3 DB servers using MariaDB Galera 10.0.16. This setup handels a couple of web apps we have created, plus a dozen medium traffic Joomla websites.

My problem is that Galera doesn't like MyISAM. Pretty often, when a plugin or component in Joomla gets installed, it sets the MySQL engine in its install.sql like so:

ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1

When that happens, a simple Joomla component installation can (and has) break the replication.

Is there a way that I can force the server to ignore the ENGINE setting?

Thanks.


texts: Rows deleted in the subscriber - what to do? - SQL Server Transactional Replication

texts:
Someone has delete by mistake a row of a table in the subscribed server/database.

I am getting the following error message:

  The row was not found at the Subscriber when applying the replicated
  (null) command for Table '(null)' with Primary Key(s): (null) (Source:
  MSSQLServer, Error number: 20598)

Now I have restored the deleted row in the replicated server, but the distributor to the subscriber agent is taking so long to apply the changes (currently nearly 1.5 hours).

How can I make it do this update immediately?


texts:
Context

Every few weeks the database guy at work backs up the production database and then several weeks later imports it into the development database. I don't know why it takes so long between backup and restore, but anything I do in the database during that time period is lost unless I explicitly tell him to save it. Not having every little change memorized has at least once caused a data loss issue in one of my applications. So my plan is to create a table and populate it with information about the changes made to the database so I have an answer next time he asks me "what changes have you made to the database since September 5th?"

Question

I'm trying to compose this trigger to log structural changes to a table that I will query with a cron job and backup the relevant tables as needed. I don't care about data, just structural changes.

How do I access the variables needed to populate the table in this statement?

CREATE OR REPLACE TRIGGER DB_CHANGES_T
AFTER ALTER OR CREATE OR DROP OR GRANT OR RENAME OR TRUNCATE ON DATABASE
BEGIN

  insert into db_change_log (
    event_type, -- 'create', 'alter', 'drop', 'grant', 'rename', or 'truncate'
    affected_entity, -- the name of the table/view/package/etc that was affected
    entity_type, -- 'table', 'view', 'index', 'package', 'procedure', 'function', etc, etc
    event_time
  ) values (?, ? , ?, sysdate);

END;


texts: can my SQL Server application code using temporary tables be used for Postgres?

texts: I have a large application that connects to SQL Server and makes extensive use of temporary tables using the #table syntax.  I would like to convert this to connect to postgres, which does not accept that notation.  Is there a work-around, other than modifying all my code?


texts:
I have a provisioned, single-master RDS DB (5.6.mysql_aurora.1.23.0) with a writer and a reader. The clients are a bunch of jetty applications with a pool of up to 250 connections each.
I have for some time been struggling with intermittent problems: Connection atttempts start to time out, mostly. It seems to correlate with high loads, and I have started copying the entire information_schema.processlist to a log table at regular intervals (with a timestamp, obviously), and it seems the problem becomes most pronounced when the number of connections reaches 2000. It never gets near max_connections or max_user_connections:
mysql> show variables like "%max%conn%";
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| aurora_max_connections_limit | 16000 |
| max_connect_errors           | 100   |
| max_connections              | 3000  |
| max_user_connections         | 0     |
+------------------------------+-------+
4 rows in set (0.03 sec)

So, which parameters should I concentrate on in order to get past this problem? I haven't found anything likely looking, that explains why 2000 connections seem to cause this; of course, it may simply be a coincidence that such a round number pops up.


texts: SET IDENTITY_INSERT OFF - Caused Table Lock

texts:
I Executed SQL on database that looked like this:

SET IDENTITY_INSERT table1 ON
GO
BEGIN TRANSACTION
GO
INSERT INTO table1 SELECT ... FROM table2
GO
DELETE FROM table2 ...
GO
COMMIT TRANSACTION
GO
SET IDENTITY_INSERT table1 OFF
GO

Problem is my table was locked on last statement SET IDENTITY_INSERT table1 OFF, all previous statements took around 6 hours, but my table is locked on this last statement for 1 day now.

In Activity Monitor it has connection that dont execute any command but is head blocker of locking chain, i killed it but nothing changed only that is in rollback state but it's still eating all I/O.

My question is what this session is doing and how to avoid it? It looks like IDENTITY_INSERT is almost costly like full table recreation for some reason, i don't know how this could cause any downsides on performance side.

Additional info: there was not identity collision during insert, bcs i insert operated on lower identity values than initial set on table1

Table design:

CREATE TABLE [dbo].[table1] (
    [id_table1] BIGINT     IDENTITY NOT NULL,
    [id_table3]              INT        NOT NULL,
    [some_id]    BIGINT     NOT NULL,
    [some_time2]           DATETIME2   NOT NULL,
    [some_time]            DATETIME2   NOT NULL,
    [some_value]                FLOAT (53) NOT NULL,
    [some_value2]               FLOAT (53) NOT NULL,
    [some_value3]            TINYINT    NULL,
    CONSTRAINT [FK_table1_table3] FOREIGN KEY ([id_table3]) REFERENCES [dbo].[table3] ([id_table3]),
    INDEX [IX_table1] CLUSTERED ([id_table3] ASC, [some_time] ASC, [id_table1] ASC) WITH (DATA_COMPRESSION = PAGE)
) WITH (DATA_COMPRESSION = PAGE);
GO
CREATE INDEX [IX_table1_some_time] ON [dbo].[table1]
([some_time] ASC, [id_table1] ASC) INCLUDE ([id_table3], [some_value], [some_value2], [some_value3]) WITH (DATA_COMPRESSION = PAGE)
GO


texts: Tool to generate large datasets of test data

texts:
Many times when trying to come up with an efficient database design the best course of action is to build two sample databases, fill them with data, and run some queries against them to see which one performs better.

Is there a tool that will generate (ideally straight into the database) large (~10,000 records) sets of test data relatively quickly?  I'm looking for something that at least works with MySQL.


texts:
Is it possible to drop all empty tables from my huge database (mysql)?

I'm looking for a sql command to automatically remove all those empty tables.

Currently, I have 305 tables in my dataset, and about 30% of them are old empty tables, that will not be used in my new application.

Just to clarify; All tables are of type=MyISAM


texts: Querying a synonym (for a table using linked server) gives no data in return

texts:
I have a function which run in an application at a server. This server have a ODBC connection (user DSN) to the SQL server which is set to use windows authentication.
The function is supposed to query a synonym on the SQL server and return a string.
The synonym is created using a linked server connection to a table in a database in the linked server.
The full script is:

Function OnClick()
Dim iPath as String
dim tQuery as string
Dim tActNo As String
Dim tCcaseID as string
Dim DBase, RS as Object
tActNo =  58333' eActNo.Title '241420
debug
DBase = CreateObject("DATABASE")
DBase.Connect("COMMON")
 tQuery = "SELECT id FROM vContosoCcase WHERE Refno='" + tActNo + "' and Subcase=0"
IF (DBase.Query(RS, tQuery) = 0)
   IF (RS.MoveNext = 0)
     tCcaseID = RS.ID
   End if
   DeleteObject(RS)
ELSE
MessageBox(ErrorText(DBase.Query(RS, tQuery))
END IF
iPath= "http://www.contoso.com:44207/AppCaseMgmt/CaseList/ccaseId/" + tCcaseID
ShellExecute("C:\Program Files (x86)\Google\Chrome\Application\chrome.exe",iPath)
End Function
Function OnSetFocus(P1 as Integer)
   clearFocus()
End Function

This used to give data presented in the application.
The only change after this setup being tested and found ok, the server which the synonym "points to" have gotten SSL disabled.
After SSL disabling, i track the query in SQL profiler, and I see the query is being made:
SELECT id FROM vContosoCcase WHERE Refno='58333' and Subcase=0

Why is the result not presented in the application anymore?


texts: Trigger: Insert Into For Update IF Update - but with custom insert based on what type of update?

texts:
CREATE TRIGGER [dbo].[Logging]
ON [dbo].[TriggerTest]
For UPDATE
AS
BEGIN
    IF UPDATE (ColumnName)
    Begin
    INSERT INTO [dbo].Logging(
        CorrelationID,
        DateTimeStamp,
        Operation
        )
    SELECT
        i.ID,
        GETDATE(),
        'Column has been altered.' -- Want this to be custom.
    FROM inserted i
    LEFT JOIN deleted d
        ON i.ID = d.ID
    WHERE i.ColumnName != ISNULL(d.ColumnName, -1)
    End
END;
GO

This works but I want that indicated line to be dynamic based on 3 situations.

IF i.ColumnName is not null and d.ColumnName is not null THEN 'Column value has been altered'

IF i.ColumnName is null and d.ColumnName is not null THEN 'Column value has been deleted.'

IF i.ColumnName is not null and d.ColumnName is null THEN 'Column value has been added.'

Is it possible to this?


texts: Log File Size and dbcc sqlperf (logspace)

texts:
Consider the following log file setting for a database named XYZ:
Initial size = 250MB, Autogrowth by 500MB, Limited to 1GB
I have created a SQL Job to monitor the size of XYZ database ldf using the following script and insert it into a table in a different database named ABC.
DECLARE @sql_command VARCHAR(MAX)
DECLARE @action VARCHAR(MAX)
DECLARE @size REAL
DECLARE @sizeUsed REAL

IF exists (SELECT * FROM tempdb.dbo.sysobjects o WHERE o.xtype in ('U') and o.id = OBJECT_ID(N'tempdb..#TempForLogSpace'))
    DROP TABLE #TempForLogSpace;

CREATE TABLE #TempForLogSpace
(
    DBName varchar(MAX),
    LogSize real,
    LogSpaceUsed real,
    Status int
)

SELECT @sql_command = 'dbcc sqlperf (logspace)'
INSERT INTO #TempForLogSpace EXEC (@sql_command)

SELECT @size = LogSize, @sizeUsed = LogSpaceUsed FROM #TempForLogSpace WHERE DBName = 'XYZ'

INSERT INTO dbo.ABC.tbl (DBName, FileSize, SpaceUsedPercentage, LoggedDate)
VALUES('XYZ', @size, @sizeUsed, GETDATE())

Initial result after running it for several hours is as follows

My expectations is that,

as transactions being made in database XYZ, the SpaceUsedPercentage = LogSpaceUsed (DBCC)
should be increasing,
and if the ldf size reaching the initial size, SQL server
would increase the ldf file size by another 500MB and so would the the value of
FileSize = LogSize (DBCC).

But after seeing the result:

Seems like SpaceUsedPercentage is increasing/decreasing from
time to time - what is actually happening?
If the ldf reaching initial size - will the FileSize captured is
the initial size or the increased size?

Thank you for your kind attention.


texts: Trying to fit indexes in memory: partitioning vs compression or neither?

texts:
I have a "transaction" table that grows at about 150k rows-per-day rate. 97% of user aggregate queries refer to the last 3 months of data and must run very efficiently. Trimming older transaction data is not an option as I need to make it available going back 12 years. /Splitting "recent" from "old" transactions into separate tables is an option, but I'm trying to avoid this complication if possible./

Would you recommend table partitioning to help keep indexes in memory? Currently, the table has bigint primary key, so if I go with partitioning by date-range, I'd have to add datetime to the PK, which would double PK's memory footprint -- feels kind of counter productive.

Or, perhaps table compression would help better? Specifically, MySQL 5.7 permits page-level compression for InnoDB tables that reside in file-per-table tablespaces (mine do).

Increasing system memory is not an option for a while as I'm pinned to an AWS reserved instance for time being.