texts: changing the value of a column in a table based on the value of another column

texts:
I have the following table that is the "Class" table:

class_size
class_number
student_id

2
4005

1
4007

2
4001

3
4010

I want the "class_size" column to be generated automatically based on the other two columns.
I have tried the following SQL statement, but it doesn't work:
update Class
set class_size=totalSize
where totalSize,Cnum in (Select class_number as Cnum ,count(student_id) as totalSize
from Class
group by class_number)
and class_number = Cnum;

I really appreciate it if someone can help me with this problem, as I tried many different statements and still I cannot get it right.


texts: How to save SSMS 2012 Object Explorer Scripting Settings?

texts:
I'm generating a bunch of object creation scripts in SSMS 2012, using the Object Explorer context menu.  The default options for these generated scripts are not to my liking (for example, I want the permissions and the triggers scripted).  I went into Tools|Options|SQL Server Object Explorer|Scripting and set the scripting options how I wanted them, and that worked great for that day, but now it's another day and I've come back to continue working on this, and all of my customized scripting settings have been lost.

I went into the Import and Export Settings Wizard, hoping to export my changes and then just import them all at once next time.  But I don't see these settings anywhere in the wizard.  So what's up?  Is there a way to do this?

Thanks,

Rebeccah


texts:
I recently asked a question regarding the restoration of a pretty huge (around 300 to 400 GB) mysql database and got some nice suggestions as seen here. Now, I have backed up the data directory of mysql as well.

So, for example before reinstalling the machine the data directory is present under /mounts/mysql.

I have backed up that particular folder and I was wondering if I just restore /mounts/mysql folder back in the new machine, will it be ok? Or will there be some problem because the database is not restored using the proper mysql command?


texts: How to see, if a foreign key is enforced?

texts:
How can I see in IBM DB2, if a foreign key is in enforced Status?
In the systemviews REF_CONSTRAINTS and REFERENTIAL_CONSTRAINTS I see them, but I cannot see, if they are in enforced Status. Is there another System view, where I can see that?


texts: How do you backup your mysql database so you can be sure that you are 100% protected of data loss whatever problems that may happen? I am thinking to rent one or two servers that i will use only for backup. Then i will setup rsync cron jobs that will do incremental backups daily to these two servers. Will this be enough? Do you use other methods than rsync to backup you database?


texts: ORDER BY clause is allowed over column that is not in SELECT list?

texts:
In theory, every statement in SQL Server create virtual table passed to next statement. In other words, when FROM and Where clause are finished, next step is SELECt columns from virtual group created step before.

But, it is possible to order result set by column that is not listed in select list.

How is it possible, when SELECT clause are executed before ORDER BY and which primary task is to select columns for rendering?

It is a little confusing or I don't get something.


texts:
I have a query I'm trying to optimise. I have read an article here http://web.archive.org/web/20100823233821/http://www.biblogs.com/2008/03/30/optimizing-countfilter-expressions-in-mdx/ about optimising the Filter expression, but having no overall improvements. Wondering if anyone else has any ideas.

Basically, the initial idea was to do this;

WITH MEMBER [test]
 AS COUNT(
    FILTER(
        [Dimension].[Hierarchy].[Level],
        [Measures].[Total Orders] = 1
    )
),
FORMAT_STRING = "#,#0"

select [test] on 0
from [InPatient Episodes]

This takes about 15 seconds to run. Following the steps in the article above, I rewrote it like so, as I read that using Filter stops SSAS from using Block Calculation mode:

WITH
  MEMBER [test] AS
    Sum
    (
      [Dimension].[Hierarchy].[Level]
     ,IIF
      (
        [Measures].[Total Orders] = 1
       ,1
       ,NULL
      )
    )
   ,FORMAT_STRING = "#,#0"
SELECT
  [test] ON 0
FROM [InPatient Episodes];

But this takes 15 seconds also.

The Dimension level has around 4 million members in it, and our Fact (Orders) has approx 16million rows, so it's not particularly huge.


texts: My simple FK constraints appear to have been ignored. How?

texts:
I have two tables, device and platform where device.platform_id enforces the following constraint:

Foreign-key constraints:
    "device_platform_id_fkey" FOREIGN KEY (platform_id) REFERENCES platform(id)

It has been this way since the table was first created.

And yet:

select count(*) from device
where device.platform_id
not in (select distinct p.id from platform p);
 count
-------
   210

I discovered the inconsistency while testing a backup (insert or update failed because it violates foreign key constraint).

We don't have any application logic that does something like ALTER TABLE … DISABLE TRIGGER while deferring constraint validation within a transaction.

How could my database enter this state?

The only thing that comes to mind is a bad dump/restore that may have temporarily disabled triggers and imported constraint-violating data at some point in the past.

What am I missing?


texts: Best practice privileges and roles for postgres schema migration

texts:
I'm having a re-look at roles and privileges in postgres to minimise the damage that a compromised account can do.
Ownership and migrations is a part I'm having trouble getting my head around.
As I understand it, a table is initially owned by the role that creates it, and a table can only be dropped by the owner.
However, the owner of a table is effectively a super-user. They can grant themselves all permissions on that table, making any attempt to limit that user's access to the table fairly pointless.
I've a separate role that our application uses (application_role) to connect and that role is not the owner and does not have permission to alter the table or truncate.
Changes to the schema are automated as part of our build process with fairly high permission levels.
I'm looking at a couple of alternatives to see if they offer any practical additional security. But it seems like not really?

One option would be to set up the migration user as CREATE ROLE build_account NOINHERIT IN ROLE db_owner and then SET ROLE db_owner before creating/dropping a table.

Have the build role do ALTER TABLE new_table TO db_owner after it creates a table. This does seem more robust than the above option, but means we'll have to intervene manually if we ever want to drop a table. That might be ok, since dropping tables is rare, but it eliminates some of the advantage of automated schema changes as it introduces more opportunity for user error.

Is there anything I've missed? Is there a best practice for setting up the role that performs Postgres schema migrations in a build process?


texts: Table Valued UDF vs Stored Proc for passing values to a SELECT statement

texts:
I work with an application that, in many places, uses stored procedures to return data to the application, kind of like a view, but these procedures take in parameters (which isn't possible in a SQL Server view).

I believe this was done due to it being more efficient for the given queries to have values passed deep into the inner queries, than it was to use a view with a WHERE clause.

In these cases, none of the other features you get by using a stored procedure are required e.g. they are all only select statements... no transactions, it's a single result set, no error handling, no calling to other procedures, etc.

For this specific use case - which is basically a "parameterized view" - are there efficiencies to be gained by using an inline user-defined function that returns a table data type instead of stored procedures for this?

The use case I am dealing with involves very small result sets. A simple example would be where a web app calls a proc with, say, a member id parameter, the proc contains a query with a bunch of joins and subqueries etc. (the parameter is used deep within these) and then send back a list of, say, subscriptions, owned by that user.

Is one method generally better than the other?


texts: How to Restore Postgresql Cluster from data directory

texts:
in our office we have a development database server that runs Debian 5, on this server we had a postgresql 8.1 running but because of the lack of internet access from the server we backed up with pg_dump_all the entire cluster, but the dump file is lost then we installed many versions and neither 8.1 version itself recognize the database cluster, so my question is, theres a way to reinstall the same postgres 8.1 and tell him to take the old cluster from data folder? or the backup should be via the dump file only? i only have the data folder but neither the 9.2, 8.4, or the same 8.1 can recognize the cluster. this is the error im getting while trying to initialize the same cluster with pg 8.1

postgres@Desarrollo-SDQ-VE:/opt/postgres/8.1/bin$ ./postgres -D  /databases/postgresql/8.1/ve/ -c config_file=/etc/postgresql/8.1/ve/postgresql.conf
[2012-11-13 10:42:22 AST:18359id]     2012-11-13 10:42:22 AST    2012-11-13 10:42:22.137 AST  FATAL:  database files are incompatible with server
[2012-11-13 10:42:22 AST:18359id]     2012-11-13 10:42:22 AST    2012-11-13 10:42:22.138 AST  DETAIL:  The database cluster was initialized with HAVE_INT64_TIMESTAMP but the server was compiled without HAVE_INT64_TIMESTAMP.
[2012-11-13 10:42:22 AST:18359id]     2012-11-13 10:42:22 AST    2012-11-13 10:42:22.138 AST  HINT:  It looks like you need to recompile or initdb.

any Idea?


texts: What is the difference between sys.dm_hadr_ tables and sys.availability_groups / sys.availability_replicas?

texts:
Browsing around some sys tables, I see:

sys.dm_hadr_availability_replica_cluster_nodes.group_name = sys.availability_groups.name

and

sys.dm_hadr_availability_replica_cluster_nodes.replica_server_name = sys.availability_replicas.replica_server_name

and

sys.dm_hadr_availability_replica_cluster_nodes.node_name = sys.availability_replicas.replica_server_name

and

sys.dm_hadr_database_replica_states.group_id = sys.availability_groups.group_id

Could someone please clarify for me whether the "hadr" tables are duplicating data or not? For example, sys.dm_hadr_availability_replica_nodes and sys.avilability_replicas sound like they're describing the same thing.

I guess the question is on how safe it is to join these tables given I can see data values equate - because after all, the same value (something like 'SQL01') could conceivably be used to describe different things.

Better yet - is there no entity-relationship diagram published by Microsoft for these sys tables? I can find old posts where people ask the same question about ERDs and there are answers regarding older versions of SQL Server, but all links to diagrams seem outdated and defunct.


texts: See content of temporary Table in Oracles' EXPLAIN

texts:
I'm executing a Query in Oracle SQL Developer and eventually call
SELECT * FROM TABLE ( dbms_xplan.display );

to get an impression what Oracle just did. There's a column Name in the output that states the name of the index that is used in an operation or the name of the table that is being accessed.
Every once in a while it says something like SYS_TEMP_0FD9D6B47_384FBF5. I'm not sure what of make of it. I guess this is a temporary table created from a WITH clause. How can I see what's the content or the SQL code behind this table?


texts: Interest calculation after repayment in MS SQL Server query

texts:
I have two tables one Loan table and another RePayment table.

I want the output like the below table:

Below are some queries which I have tried:
Interest = Principle * Interest Rate/360* Tenure;
Tenure = datediff(ExpiryDate-RePaymentDate);
and Out = (Principle+interest)-RePayment;
CREATE TABLE [dbo].[Loan](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [AccountNo] [nvarchar](50) NOT NULL,
    [Principle] [decimal](18, 2) NOT NULL,
    [InterestRate] [decimal](18, 2) NOT NULL,
    [ExpiryDate] [smalldatetime] NOT NULL)

INSERT [dbo].[Loan] ([Id], [AccountNo], [Principle], [InterestRate], [ExpiryDate])
VALUES (1, N'ABC', CAST(1564256.56 AS Decimal(18, 2)), CAST(9.00 AS Decimal(18, 2)), CAST(N'2020-10-08 00:00:00' AS SmallDateTime))

CREATE TABLE [dbo].[RePayment1](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [LoadId] [int] NOT NULL,
    [RePayment] [decimal](18, 2) NOT NULL,
    [SubmitDate] [smalldatetime] NOT NULL)

INSERT [dbo].[RePayment] ([Id], [LoadId], [RePayment], [SubmitDate])
VALUES (1, 1, CAST(300000.00 AS Decimal(18, 2)), CAST(N'2020-12-30 00:00:00' AS SmallDateTime))

SELECT AccountNo, ExpiryDate, Principle, (Principle*InterestRate/100/360*Tenure) as Interest, Tenure, SubmitDate, RePayment, ((Principle + (Principle*InterestRate/100/360*Tenure))-RePayment) AS [Out]
FROM
    (SELECT Loan.AccountNo, Loan.ExpiryDate, Loan.Principle, Loan.InterestRate, RePayment.SubmitDate, RePayment.RePayment, DATEDIFF(D, Loan.ExpiryDate, RePayment.SubmitDate)+1 AS Tenure
    FROM Loan left outer join RePayment on Loan.Id=RePayment.LoadId)t

I'm stuck, please help me


texts: Is there any way to do a SELECT query without any optimization in SQL Server?

texts:
I am trying to test the speed of SQL server queries.
However, once I do the query once, it becomes a lot faster.
The database is live so I can not make any changes to it, only to the SELECT query being performed.

Is there any way to do a SELECT query without SQL server optimizing it?


texts: Move (Restore) SQL Server 2005 database which does not exist before

texts:
I did not ready about this but found out after trying to restore a database from remote server and it wont restore if the database did not exist before. What I would do  is

create a .bak file (backup the database)
Copy the .bak file to the remote server using shared folder
Run restore in SQL Server Management Studio to restore the database

It will give me error. I think I found it almost accidently (or out frustration, just restored it on top of the database, and it worked).

Now as I am typing the question, I realize move is probably not the same thing as restore. For restore, the database must already exist?

I am in the same position again to restore a database which does not exist on my system.

1. What procedure do I follow?

2. Do I need to set permission on the .bak file before restoring successfully on remote Server?

3. Or is there a better way?

Am I missing anything? I would  like to know, what are the rules when restoring (or moving) the database.

Edit : Adding the dialog error. I just repeated the process. I am not sure this was the exact error I was getting before. I renamed the original db that I have. Then I import using SSMS.


texts: How to update a table from a query result of another table related to the first one?

texts:
Here is my attempt. What is the correct syntax to achieve this?
UPDATE table_a a
  SET a.firstCreationTime = c.created_at
FROM (
  SELECT created_at
  FROM table_b b
  WHERE a.id = b.id
  ORDER BY b.created_at ASC
  LIMIT 1
) AS c;


texts: inet_client_addr in PostgreSQL don't return IP

texts:
i trying use the function inet_client_addr() (PostgreSQL 9.2) in a trigger, but the return  is '::1/128'.
This is a problem with my script or with the server?

    CREATE OR REPLACE FUNCTION trg_aud_cms() RETURNS TRIGGER AS $aud_cms$
BEGIN
    --
    -- Cria uma linha na tabela para refletir o estado anterior ao atual
    IF (TG_OP = 'DELETE') THEN
            INSERT INTO aud_cms(operation, user, ip, date, codcms, titlecms, contentcms, hashtagcms) VALUES ('D', USER, inet_client_addr(), now(), OLD.codcms, OLD.titlecms, OLD.contentcms, OLD.hashtagcms);
            RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO aud_cms(operation, user, ip, date, codcms, titlecms, contentcms, hashtagcms) VALUES ('U', USER, inet_client_addr(), now(), OLD.codcms, OLD.titlecms, OLD.contentcms, OLD.hashtagcms);
            RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO aud_cms(operation, user, ip, date, codcms, titlecms, contentcms, hashtagcms) VALUES ('I', USER, inet_client_addr(), now(), NEW.codcms, NEW.titlecms, NEW.contentcms, NEW.hashtagcms);
            RETURN NEW;
    END IF;
    RETURN NULL; -- o resultado é ignorado uma vez que este é um gatilho AFTER
END;
$aud_cms$ LANGUAGE plpgsql;

CREATE TRIGGER aud_cms
AFTER INSERT OR UPDATE OR DELETE ON cms
FOR EACH ROW EXECUTE PROCEDURE trg_aud_cms();


texts: Kerberos Configuration Manager - Does it work with SQL Server 2016?

texts:
I have just installed Kerberos Configuration Manager on a clustered SQL Server 2016 server and trying to connect using the app, it keeps failing.

I can connect using SSMS, but not with Kerberos Configuration Manager. I've tried leaving the fields empty and populating them, always fails.

Does anyone know if Kerberos Configuration Manager works with SQL Server 2016?


texts: Mysql replication broke after mysql upgrade on master

texts:
I've seen this question, but I am not sure if the solution is right for my particular error (replication breaks after upgrading master)

I upgrade my master server's mysql from 5.5.34 to 5.5.35. After doing that, the SQL_THREAD stopped and this is what's in the error log

140225 12:43:40 [ERROR] Slave SQL: Query caused different errors on master and slave.     Error on master: message (format)='Cannot load from mysql.%s. The table is probably corrupted' error code=1548 ; Error on slave: actual message='no error', error code=0. Default database: 'performance_schema'. Query: 'DROP DATABASE IF EXISTS performance_schema', Error_code: 0
140225 12:43:40 [Warning] Slave: Cannot load from mysql.proc. The table is probably corrupted Error_code: 1548
140225 12:43:40 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000127' position 17538

I have since upgraded the slave as well and tried running mysql_upgrade on both and trying to start the SQL_THREAD, but it won't start.

What can I do to fix this while keeping the slave's copy accurate?

Here is the show slave status:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.40.20
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000133
          Read_Master_Log_Pos: 34984217
               Relay_Log_File: mysqld-relay-bin.000061
                Relay_Log_Pos: 17684
        Relay_Master_Log_File: mysql-bin.000127
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error: Query caused different errors on master and slave.     Error on master: message (format)='Cannot load from mysql.%s. The table is probably corrupted' error code=1548 ; Error on slave: actual message='no error', error code=0. Default database: 'performance_schema'. Query: 'DROP DATABASE IF EXISTS performance_schema'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 17538
              Relay_Log_Space: 35137400
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error: Query caused different errors on master and slave.     Error on master: message (format)='Cannot load from mysql.%s. The table is probably corrupted' error code=1548 ; Error on slave: actual message='no error', error code=0. Default database: 'performance_schema'. Query: 'DROP DATABASE IF EXISTS performance_schema'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 20
1 row in set (0.00 sec)


texts: USER_ID field in alert logs (also in V$DIAG_ALERT_EXT view)

texts:
Does anyone know what triggers the USER_ID field in the log.xml to be populated?  The value also exists in the V$DIAG_ALERT_EXT view.

I've found by observing the logs that if a temp tablespace fills up, it will log the USER_ID of the problematic SQL statement causing the issue.  But other than that, it appears that value is always NULL.


texts:
PS H:\> Test-DbaDiskAlignment -ComputerName Test_Server
    WARNING: [Test-DbaDiskAlignment][22:09:54] Can't connect to Test_Server
    WARNING: [Test-DbaDiskAlignment][22:09:54] CIM query to Test_Server failed.

I am very new to using dbatools. Would someone be able to help me with this error please.


texts:
How to redirect pg_dump -Fd output to gzip? It requires that a directory would be specified.
PGPASSWORD=$db_pass pg_dump mydb -U db_user -h localhost --verbose 2>/var/log/postgresql/pgdump.log -Fd | gzip > /mnt/bulk-data/db_backup/mydb_bak.gz


texts: How to find Key Constraint in 1:M cardinality Entity set?

texts:
I have a problem in identifying the key constraints of the Entity set.

As in the relation, the key constraint should be from M-side.
But here, in this case, according to me Father is the key constraint.
So for this, the relation is 1:M. In this case, it doesn't satisfy Father to be the key constraint.


texts:
From the docs:

  If a duplicate-key error occurs, a shared lock on the duplicate index
  record is set. This use of a shared lock can result in deadlock should
  there be multiple sessions trying to insert the same row if another
  session already has an exclusive lock. This can occur if another
  session deletes the row.

Going  with the example in the docs,

Suppose that an InnoDB table t1 has the following structure:

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

Now suppose that three sessions perform the following operations in order:

Session 1:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session 2:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session 3:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session 1:

ROLLBACK;

  The first operation by session 1 acquires an exclusive lock for the
  row. The operations by sessions 2 and 3 both result in a duplicate-key
  error and they both request a shared lock for the row. When session 1
  rolls back, it releases its exclusive lock on the row and the queued
  shared lock requests for sessions 2 and 3 are granted. At this point,
  sessions 2 and 3 deadlock: Neither can acquire an exclusive lock for
  the row because of the shared lock held by the other.

I have some questions :

1) The insert query takes an exclusive lock on the row it is inserting. So, suppose T1 is inserting on row 1, it will lock row 1. Now when T2 comes to write, will INNODB evaluate the query before executing it and find out that it is going to write the same PK (row with i = 1) and make T2 wait? Or will it start execution of T2 and find that it gives duplicate key error or PK violation.

2) Why are T2 and T3 taking shared locks? How do shared locks come into picture during insert?


texts: How to add an article to existing replication

texts:
This is the first time I am going to add tables to replication.

I have outlined the below steps to add a new table to existing replication. Please correct me if I am wrong. The table is just created - no data.

exec sp_addarticle @publication = N'pub1', @article = N'tab1', @source_owner = N'dbo', @source_object = N'tab1'

 EXEC sp_addsubscription
  @publication = 'pub1',
  @subscriber = 'sub1',
  @destination_db = 'MLT-Rep'
GO
 EXEC sp_addsubscription
  @publication = 'pub1',
  @subscriber = 'sub2',
  @destination_db = 'MLT-Rep'

After this, I am supposed to run Snapshot right? How do I go about doing that? Just navigate to the "Local Publication" -> "View Snapshot Agent Status" -> Start

Are the above steps right?


texts: Mongodb create user with only read and write access

texts:
Hi I created a new using using following command in mongodb,

db.createUser({ user: 'csquad', pwd: 'somepass123', roles: [ { role: "readWrite", db: "cloud"}]});

Now this user is able to login to db Cloud and do any activities. Including he is able to create a user with same readWrite access to database cloud.

Now I wanted to update this user to a new role so that he has only Read and Write access to any Document or create any collections in this DB. Should not have access to create new user.

How or what role I should assign in MongoDB

Even if I create a user with role "read" still he is able to create a new user with readWrite. And the "read" user is still able to create new collections / add documents

Basically any user in the "cloud" database is able to perform any task irrespective of Roles assigned to them. Only restriction is they are able to do all task in the specified database (like they can't login to admin DB etc..)


texts: Attach a trigger to 'sys.dm_db_missing_index_details'

texts:
I am trying to attach a trigger to the sys table sys.dm_db_missing_index_details with the following query:

CREATE TRIGGER indexBackup
ON  sys.dm_db_missing_index_details
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here
EXEC PerformanceTest.[dbo].[usp_persist_missing_index_DMV_data]

END
GO

I am getting the following error:

  The object sys.dm_db_missing_index_details does not exist or is invalid for this operation.

Is it allowed to attach a trigger to a sys view or am i missing something?


texts: Is it ok to increase `innodb_stats_sample_pages` up to large number?

texts:
MySQL sometimes generates bad execution plan irregularly.

2 years ago, I have increased innodb_stats_sample_pages(default:8) up to 64 and recently changed into 128 because Data Volume has been increased for 2 years.

I think 128 is still small. (128 pages * 16KB = Only 2MB. Am I right?)

Is it ok to increase it up to 1024 or higher? What are the points to consider?

How large value of pages are you using?


texts: MySQL-How to find that value in a column is subset with all other values in same column multiple time

texts:
I have a table named as Team where table contain 3 column (ID,Team,Task). Its datatypes are (INT,VARCHAR(255),INT). Below you will find a sample table where I would like to perform the query on column team.

   -----------------------------
   |ID|        Team      |Task |
   -----------------------------
   |1 |team1,team2,team5 |23   |
   |2 |team3,team2       |34   |
   |3 |team1,team5,team3 |45   |
   |4 |team2             |2    |
   |5 |team1,team3       |65   |
   |6 |team2,team5       |4    |
   |7 |team1             |34   |
   |8 |team5             |67   |
   -----------------------------

My question is that i like to check whether value in column team is subset of any of the value in same column team (It should not check with it own value in the column team), if that value comes out to be true then i would like to delete that row which has that value.It should run for all values of column team.

OUTPUT table will be like this

   ---------------------------
   |ID|    Team          |Task|
   ---------------------------
   |1 |team1,team2,team5 |23  |
   |2 |team3,team2       |34  |
   |3 |team1,team5,team3 |45  |
   ----------------------------

As you can see in team5 is subset of {team2,team5},{team1,team5,team3} and {team1,team2,team5}, Same with team1 ,{team2,team5} ,{team1,team3}, team2 they will also get removed because they are subset of some value in column team.

If there is any other approach to do this then do tell me. In initial Table i used group_concat function on it to get all values related to particular ID.


texts: How to get row count from all tables of a schema ( without using information schema )

texts: The query should collect data from table name , schema name from information.schema and row count should be taken from actual table.


texts:
I have a cursor in MySQL and what I want to do is rather than having an enormous cursor loop that can be up to 4000+ users in size, do it smaller batches so that the MySQL engine doesn't suffer.  I have been told to add a SLEEP into it, but have read it is also possible to batch it but not quite sure how to do that most efficiently.  In the middle of the loop, it goes off and does a couple of other Stored Procedures for each user, but they are quite small and quick - its the main loop below I am stuck on - any help greatly appreciated:-

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_UsersLoop`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_UsersLoop`(
)
BEGIN
  DECLARE v_user_id INTEGER DEFAULT 0 ;
  DECLARE v_officialjobtitle VARCHAR(100) DEFAULT '';
  DECLARE v_location VARCHAR(100) DEFAULT '';
  DECLARE v_corp_id BIGINT DEFAULT 0;
  DECLARE v_name VARCHAR(100) DEFAULT '';
  DECLARE maxcount INTEGER DEFAULT 5; -- 1000; -- 10 ;  -- max numbers of users (testing) - change to select count(*) from users where role='Level 6';
  DECLARE loopcounter INTEGER DEFAULT 0 ;
  DECLARE user_cursor CURSOR FOR
  SELECT u.`user_id`,TRIM(u.`officialjobtitle`),TRIM(u.`location`),u.`corp_id`,CONCAT(TRIM(u.`fname`),' ',TRIM(u.`lname`)) `name` FROM users u WHERE u.`role`='Level 6' ORDER BY u.`officialjobtitle` ;
  SELECT COUNT(*) INTO maxcount FROM users WHERE role='Level 6' and corp_id=1; -- number of users to loop through
  OPEN user_cursor ;
  get_users :
  LOOP
    FETCH user_cursor INTO v_user_id,v_officialjobtitle,v_location,v_corp_id,v_name ;
    IF v_officialjobtitle<>'' THEN
    CALL sp_Step2 (v_user_id,v_officialjobtitle,v_location,v_corp_id,v_name) ;  -- Do all process for the current user
    SELECT SLEEP(1);
    END IF ;
    SET loopcounter = loopcounter + 1 ;
    IF loopcounter > maxcount THEN
    LEAVE get_users ;
    END IF ;
  END LOOP get_users ;
  CLOSE user_cursor ;

  SELECT loopcounter AS `Rows_Processed`;

END$$

DELIMITER ;


texts: Why does this simple select statement require quotes?

texts:
Why does the following statement

select * from expertrule where rule_id = '1';

Return this error

ORA-00904: "RULE_ID": invalid identifier

While

select * from expertrule where "rule_id" = '1';

works!

The same goes for every column of the table (and only this table).  All the columns in this particular table require quotes surrounding their names.  Why?

  CREATE TABLE "TEST"."expertrule"
   (    "rule_id" NUMBER(*,0),
    "name" VARCHAR2(256 BYTE),
    "description" VARCHAR2(256 BYTE),
    "ruleaccess" VARCHAR2(4000 BYTE),
    "ruledisable" VARCHAR2(4000 BYTE),
    "userssn" VARCHAR2(256 BYTE),
    "departmentid" VARCHAR2(256 BYTE),
    "active" CHAR(1 BYTE)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TESTTABLESPACE" ;


texts: Non-Clustered Index key columns to cover a variable where clause

texts:
To whoever reads this, sorry for the long question and thanks so much for your time.

I'm needing some help on understanding what columns should be key/non key in the following situation, and what potential impacts it might have on performance either way.

We're using SQL Server 2008 but must support 2005 and in our application, we have a query which is hit constantly but the where clause changes depending on how the query is hit.

It ALWAYS includes 3 columns in the where (lets call them a1, a2, a3), and varaibly includes some of the others (lets call them v1,v2,v3), and always selects all of the columns.

so what we're looking at is a query that always looks like;

select id, a1, a2, a3, v1, v2, v3, data1, data2, data3, table_order
from example_table
where a1 = ?
and a2 = ?
and a3 = ?
...
order by table_order

where sometimes (but not too often) the where clause contains one of or combinations of;

and v1 = ?
and v2 = ?
and v3 = ?

Is there a best practice for this sort of situation? From what I understand about indexing, I should be make a non-clustered index that has key columns for stuff in the where clause and everything else selected as a non key column;

CREATE NONCLUSTERED INDEX
ON example_table(a1, a2, a3, table_order)
INCLUDE (id, v1, v2, v3, data1, data2, data3)

My Question here is should I be including v1, v2, v3 in as key columns given that sometimes they are there? IE;

CREATE NONCLUSTERED INDEX
ON example_table(a1, a2, a3, table_order, v1, v2, v3)
INCLUDE (id, data1, data2, data3)

Would that help with performance given that this table is queried, inserted to, updated to, etc. all the time? Or should I have different indexes for each possible iteration (IE an index with a1-3, table_order & v1 as key columns for example)? IE;

CREATE NONCLUSTERED INDEX
ON example_table(a1, a2, a3, table_order, v1)
INCLUDE (id, v2, v3, data1, data2, data3)

CREATE NONCLUSTERED INDEX
ON example_table(a1, a2, a3, table_order, v2)
INCLUDE (id, v1, v3, data1, data2, data3)

etc.

I should mention here that DB size is of concern so I don't really want to do the multiple indexes if I can help it.

I guess what this really boils down to is that I'm wondering if the query optimizer will use the one index that has all of the key columns listed in all of these different situations?

select id, a1, a2, a3, v1, v2, v3, data1, data2, data3, table_order
from example_table
where a1 = ?
and a2 = ?
and a3 = ?
order by table_order

select id, a1, a2, a3, v1, v2, v3, data1, data2, data3, table_order
from example_table
where a1 = ?
and a2 = ?
and a3 = ?
and v1 = ?
order by table_order

select id, a1, a2, a3, v1, v2, v3, data1, data2, data3, table_order
from example_table
where a1 = ?
and a2 = ?
and a3 = ?
and v1 = ?
and v2 = ?
order by table_order

etc.

Or am I forced into having to do multiple indexes to satisfy each condition?

Given that the where clause 90% of the time only have a1, a2, a3, should I just include the v columns in as non key columns because they're selected? Does the inclusion of the extra key columns hurt performance for the 90% use case?

Could specifying so many key columns in the order I'm looking to specify cause performance concerns on simple joins like;

inner join example_table
on a1 = ?

or would the query optimizer just not use this index whatsoever for that kind of simple join? If so, I'm going to need a separate index like the follow, right?

CREATE NONCLUSTERED INDEX
ON example_table(a1)


texts: Fast general method to calculate percentiles

texts:
I want to find n>1 percentiles of an unsorted column in PostgreSQL. For example the 20th, 40th, 60th, 80th and 100th percentile.

An obvious solution is to count and sort the column and then do a look but I'm hoping for a better solution. Any ideas?

P.S. I have found a good solution for MySQL but can't translate it to psql


texts: Postgresql CPU usage out of control, unknown commands in htop

texts:
I see random commands in the htop output where postgresql user is consuming more than 200% of CPU all the time. I am new to postgresql and have inherited this setup. I've tried killing the process, the query pid and it just comes back up. Here's what it looks like.
It is postgresql 12.4

How do I find out what's causing this and get rid of it?
Much appreciated.


texts: MySQL duplicate entry error opon import to new server

texts:
I do have a DB on MySQL Galera 5.6.34 with a table that contains an index on ID,date. The table is fully functional and I have used repair and check to verify the table. However, upon backup/restore the importing DB-Server (MySQL 5.7.17, not Galera) tells me there is a duplicate entry and stops importing:

  gunzip < DB_170206_040001.sql.gz | mysql -u root -p staging

  ERROR 1062 (23000) at line 3800: Duplicate entry '329610-2011-03-27 03:00:00' for key 'user_id'

If I empty that table prior to the backup, everything works fine.

Is there a command to "ignore" upon import, or even better to resolve the problem at the live server?


texts: PLS-00306 Error: How to find the wrong argument?

texts:
PLS-00306: wrong number or types of arguments in call to 'string'

Cause: This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The subprogram name might be misspelled, a parameter might have the wrong datatype, the declaration might be faulty, or the declaration might be placed incorrectly in the block structure. For example, this error occurs if the built-in square root function SQRT is called with a misspelled name or with a parameter of the wrong datatype.
Action: Check the spelling and declaration of the subprogram name. Also confirm that its call is correct, its parameters are of the right datatype, and, if it is not a built-in function, that its declaration is placed correctly in the block structure.

How do I quickly identify the wrong argument?
I have a stored-procedure with dozens of parameters. Is there an easy way to check the differences between the used and defined procedure? I don't want to check it line by line..


texts: Enterprise Manager Database Control on host with an underscore in it's name

texts:
I know it sounds awkward, but Enterprise Manager is refusing to be set up because of an underscore in the server name:

C:\>emca -deconfig dbcontrol db -repos drop

STARTED EMCA um 30.05.2012 13:21:49
EM-Konfigurationsassistent, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved. Alle Rechte vorbehalten.

...

SCHWERWIEGEND:  Ungültiger Host-Name vm_scotty11.xxnet.de. Host-Name darf keinen Unterstrich ('_') enthalten.
Weitere Einzelheiten finden Sie in der Log-Datei in c:\app\Administrator\cfgtoollogs\emca\PRJ\emca_2012_05_30_13_21_49.log.
Die Konfiguration konnte nicht abgeschlossen werden. Weitere Einzelheiten finden Sie in der Log-Datei in c:\app\Administrator\cfgtoollogs\emca\PRJ\emca_2012_05_30_13_21_49.log.

Has someone worked around this issue or can provide a documentation link which explains why this happens?

I know that there must have been warning messages upon installation of OS and DB but I still need to see it written in the docs in order to convince and make the point that the host name has to be changed.


texts:
[root@localhost ~]# rpm -ivh percona-toolkit-2.2.7-1.noarch.rpm
  error: Failed dependencies:   perl(DBD::mysql) >= 1.0 is needed by
  percona-toolkit-2.2.7-1.noarch    perl(Time::HiRes) is needed by
  percona-toolkit-2.2.7-1.noarch    perl(IO::Socket::SSL) is needed by
  percona-toolkit-2.2.7-1.noarch

How to solve above dependencies?


texts:
I was having some difficulties when trying to use Armstrong axiom to solve functional dependencies.

R (A,B,C,D,E,F)
Given FD = { A->CD , C->BE, B->F, F->A }
Prove A-> BF

By using closure, for A, I get {A,C,D,B,E,F} so I know A->BF is valid.

So I came out with these to prove using Axiom:

A->CD
CD->BED (Augmentation)
A->BED (Transitivity)
BED->FED (Augmentation)
A->FED (Transitivity)

Then from there, I am lost. Anybody knows how to prove it?

Thanks in advance.


texts: Best way to sync a table between two sql server databases with an identity key

texts:
I have an SSIS package that moves data from a table into another table in another database.  The source table has an identity key.

I need to perform some operations on the data.  The only way I can see to do this is to disable the identity key in the destination table and then use the alter table switch approach outlined here: https://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column-sql-server.

Is there an easier way to migrate data, preserve a key and then restart the identity column?


texts: MySQLdump leads to exceeding max connections. Skip-Quick as a solution?

texts:
Every night I do a full mysqldump of a 17G DB in MySQL 5.7.32. This worke for years, now I am doing some heavy load on inserts during the night which caused at exactly the backup time the connections to rise to max_connections which led to connections errors.
As the Server has enough RAM 64G (30G free), I increased max_connections from 150 to 300 in a first reaction.
However looking at the dump command I found the option -- quick (also enabled by default) which tells me that it is exporting row by row.
--single-transaction --routines --quick --compact

I am thinking of changing this to --skip-quick. but dare to change this since I would need to check the restore again and this is very time consuming.
Looking at the connections over time I also noticed that there are some interruptions around that time period. So maybe connections stack up since there is a block during MySQLdump?
MySQL error log shows a large amount of the following error, although not at this time points but continuously throughout the day:
Aborted connection 63182018 to db: 'mydb' user: 'test' host: 'myhost' (Got an error reading communication packets)

How would you approach this problem?


texts: Repmgr : It automatically promotes to new master but other standby stopped

texts:
I'm trying to setup postgre with repmgr and here is the rsult

The scenario is,

i tried to spinup 1 primary and 2 standby.
then i stop the primary so the postgres-2 got promoted.
Unfortunately the postgres-3 got disconnected due to some reason but here is the error log

It looks like it was able to connect but the postgres restared and didn't come back.
4. I spin up another standby but the master it was pointing is the old one which is postgres-1
That might be the reason why it is saying !running and still primary even if the actual primary is postgres-2.
My question is how can i make other standby not disconnected every time i promoted (automatically due to failure etc.) a new primary?
here is my repmgr.conf
NET_IF=`netstat -rn | awk '/^0.0.0.0/ {thif=substr($0,74,10); print thif;} /^default.*UG/ {thif=substr($0,65,10); print thif;}'`
NET_IP=`ifconfig ${NET_IF} | grep -Eo 'inet (addr:)?([0-9]*\.){3}[0-9]*' | grep -Eo '([0-9]*\.){3}[0-9]*' | grep -v '127.0.0.1'`

HOSTNAME='postgres-'${my_node}

cat<<EOF > /etc/repmgr.conf
    node_id=${my_node}
    node_name=$HOSTNAME
    conninfo='host=${NET_IP} user=repmgr password=repmgr dbname=repmgr connect_timeout=2'
    data_directory='${PGDATA}'

    log_level=INFO
    log_facility=STDERR
    log_status_interval=300

    pg_bindir='/usr/lib/postgresql/10/bin'
    use_replication_slots=1

    failover=automatic
    promote_command='repmgr standby promote'
    follow_command='repmgr standby follow -W'
EOF

Also, I'm running using docker extending the official postgres docker image
FROM postgres:10

RUN echo "deb http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main 10" \
          >> /etc/apt/sources.list.d/pgdg.list

# RUN ln -s /home/postgres/repmgr.conf /etc/repmgr.conf

RUN apt-get update && apt-get install wget -y
RUN apt-get install net-tools -y
RUN apt-get update; apt-get install -y git make postgresql-server-dev-10 libpq-dev postgresql-10-repmgr repmgr-common

#RUN wget -c https://repmgr.org/download/repmgr-5.1.tar.gz -O - | tar -xz

RUN touch /etc/repmgr.conf; \
    chown postgres:postgres /etc/repmgr.conf

ENV PRIMARY_NAME=localhost
ENV REPMGR_USER=repmgr
ENV REPMGR_DB=repmgr
ENV REPMGR_PASSWORD=repmgr

COPY postgresql.replication.conf /tmp/postgresql.replication.conf

COPY scripts/*.sh /docker-entrypoint-initdb.d/

Lastly, here is how i check if should be register as primary or standby
PGHOST=${PRIMARY_NAME}

installed=$(psql -qAt -h ${PGHOST} repmgr -c "SELECT 1 FROM pg_tables WHERE tablename='nodes'")

if [ "${installed}" != "1" ]; then
    echo "Registering as PRIMARY SERVER"
    repmgr primary register
else
    my_node=$(grep node_id /etc/repmgr.conf | cut -d= -f 2)
    is_reg=$(psql -qAt -h ${PGHOST} repmgr -c "SELECT 1 FROM repmgr.nodes WHERE node_id=${my_node}")

    if [ "${is_reg}" != "1" ] && [ ${my_node} -gt 1 ]; then
        echo "Registering as STANDBY SERVER"

        pg_ctl -D ${PGDATA} stop -m fast
        rm -Rf ${PGDATA}/*
        repmgr -h ${PRIMARY_NAME} -d repmgr standby clone --fast-checkpoint
        pg_ctl -D ${PGDATA} start &
        sleep 1
        repmgr -h ${PRIMARY_NAME} -d repmgr standby register
    fi
fi

here is my update for the postgres.conf

sed -i "s/#*\(shared_preload_libraries\).*/\1='repmgr'/;" ${PGDATA}/postgresql.conf
sed -i "s/#port = 5432/port = 5432/g" ${PGDATA}/postgresql.conf
sed -i "s/#max_wal_senders/max_wal_senders/g"  ${PGDATA}/postgresql.conf
sed -i "s/#wal_level/wal_level/g"  ${PGDATA}/postgresql.conf
sed -i "s/#max_replication_slots/max_replication_slots/g"  ${PGDATA}/postgresql.conf
sed -i "s/#hot_standby/hot_standby/g"  ${PGDATA}/postgresql.conf

sed -i "s/#archive_mode = off/archive_mode = on/g"  ${PGDATA}/postgresql.conf

echo "archive_command = '/bin/true'" >>  ${PGDATA}/postgresql.conf

I'm using postgres:10 and repmgr-5.0
hope someone could help me on this.
Thanks,


texts: Handling large DB tables that have millions of records

texts:
So I have a large table(Table1), with about 5 million records. I have a second table(Table2) with about 108 million records. I am using a key value from Table1 to find any matching records in Table 2( there will be between 10-150 records per match). I am creating a new table for those records and deleteing them from Table2. Like a backup except not all records in Table2 will match something from table1.  I have tried a whole match "select * from" which is way too large and have tried going one record at a time from Table1 but calculated it would take 35 days to run after letting it run for 2 days.  So I guess i am looking for a good solution for handling large tables that isn't going to take forever and that doesn't run out of memory.

Thanks, feel free to point me to another question if it matches but i couldn't find any


texts: Move log file without taking database offline

texts:
I need to move a database log file to a new partition without taking the database offline.

The normal way of doing this would be to detach the DB, move the log file then reattach the db.

Is it possible to do this without taking the database itself offline and if so how?


texts: Similar hardware, Same DBconfiguration and Database but different status variables result between Windows and Linux server

texts:
I have two database servers - one is Windows - 2008 and another is RHEL5 with similar hardware configuration. Both OS are 64 bit.

MySQL 5.1.58 community edition is installed on Windows servers while MySQL 5.1.73 is installed on Oracle Linux. Same database is installed on both servers - i..e same number of tables with same data.
At a time, if I run `show global status like 'Qcache%' on both servers and below is the comparison result:

Status variables       Linux           Windows
Qcache_free_blocks     24              54301
Qcache_free_memory     53025440        151737216
Qcache_hits            162509          44648032
Qcache_inserts         33073           6866308
Qcache_lowmem_prunes   0               657080
Qcache_not_cached      11291            994889
Qcache_queries_in_cache 7446            112378
Qcache_total_blocks     14959           279520

Below is the result of show global variables like 'query_cache%';:

Status variables                Linux        Windows
query_cache_limit               2097152      4194304
query_cache_min_res_unit        1024         4096
query_cache_size                67108864     367001600
query_cache_type                ON           ON
query_cache_wlock_invalidate    OFF          OFF

Why is there huge differences?
Also, In Linux, Qcache_free_blocks is only 3 while Qcache_free_memory is about 53MB?


texts: Group by multiple columns, agregate others and select all in SQL Server

texts:
I am using SQL server and I can't seem to construct the query I want.  I have a table with several columns, among them are

PARAMETER_NAME, GW_LOCATION_ID, Report_Result, DETECT_FLAG

I want a query to return

a row for each unique Parameter-Location combination
the maximum value of the Report_Result column for that unique Parameter-Location combination and
the DETECT_FLAG value associated with the maximum value.

There are other columns in addition to DETECT_FLAG that I want to return but I think that if I can get this part worked out, I should be able to return the other columns similarly.

The query works if I group by PARAMETER_NAME and GW_LOCATION_ID and aggregate the Report_Result column (see below).  However, when I add the DETECT_FLAG column, I get the error, "Column 'SLVs_Flagged.DETECT_FLAG' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."  What I want is the value of DETECT_FLAG for the row returned by MAX(Report_Result).

SELECT
PARAMETER_NAME, GW_LOCATION_ID, MAX(Report_Result)
FROM SLVs_Flagged
GROUP BY PARAMETER_NAME, GW_LOCATION_ID
ORDER BY PARAMETER_NAME, GW_LOCATION_ID;

I have tried to do a subselect to return the DETECT_FLAG value that corresponds to MAX(Report_Result) but I try to use a WHERE condition and I get another error.  Please advise how I can execute this query.

Here is a subset of the data for testing.

PARAMETER_NAME  GW_LOCATION_ID  Report_Result   DETECT_FLAG
Perchlorate CDBO-6  2.38    N
Perchlorate CDBO-6  1.45    N
Perchlorate CDV-16-02655    4   N
Perchlorate CDV-16-02655    0.537   Y
Perchlorate CDV-16-02655    4   N
Perchlorate CDV-16-02656    100 N
Perchlorate CDV-16-02656    0.394   Y
Perchlorate CDV-16-02656    4   N
Perchlorate CDV-16-02656    4   N
Perchlorate CDV-16-02657    4   N
Perchlorate CDV-16-02657    4   N
Perchlorate CDV-16-02657    4   N
Perchlorate CDV-16-02657    0.174   Y
Perchlorate CDV-16-02658    4   N
Perchlorate CDV-16-02658    4   Y
Perchlorate CDV-16-02658    0.126   Y
Perchlorate CDV-16-02658    0.0561  Y
Perchlorate CDV-16-02658    20  N
Perchlorate CDV-16-02658    4   N
Perchlorate CDV-16-02659    4   N
Nitrate as Nitrogen R-16 S4 0.003   N
Nitrate as Nitrogen R-20 S1 0.003   N
Nitrate as Nitrogen R-20 S1 0.003   N
Nitrate as Nitrogen R-20 S1 0.003   N
Nitrate as Nitrogen R-20 S2 0.003   N
Nitrate as Nitrogen R-20 S2 0.003   N
Nitrate as Nitrogen R-20 S3 0.003   N
Nitrate as Nitrogen R-20 S3 0.003   N
Nitrate as Nitrogen R-20 S3 0.003   N
Nitrate as Nitrogen R-27    0.003   N
Nitrate as Nitrogen R-31 S2 0.003   N
Nitrate as Nitrogen R-32 S3 0.003   N
Nitrate as Nitrogen R-32 S3 0.003   N
Nitrate as Nitrogen Test Well 1A    0.01    N
Nitrate as Nitrogen Test Well 1A    -0.01   N
Nitrate as Nitrogen Test Well 2 0.04    N
Nitrate as Nitrogen Test Well 2 0.01    N
Nitrate as Nitrogen Test Well 2A    0   N
Nitrate as Nitrogen Test Well 3 0.04    N
Nitrate as Nitrogen Test Well 3 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 8 0.04    N
Nitrate as Nitrogen Test Well 8 0.04    N


texts:
I have a MySQL database that is storing business data... In the database, when running a select against a business name that contains an accented e (é), it comes up correctly (showing me that it is stored correctly.) On our website, though, it is showing up as the diamond with the question mark.

I checked out database and found the following:

Character_sets_dir          /usr/share/mysql/charsets/
Character_set_client        utf8
Character_set_connection    utf8
Character_set_database      latin1
Character_set_filesystem    binary
Character_set_results       utf8
Character_set_server        latin1
Character_set_system        utf8

Where things get lost is on the website itself... The web page is encoded as UTF8...

Any ideas of where the conversion may be failing?

-Wes


texts: Oracle, Is there a way to request a lock with alter table drop partition?

texts:
We have an Oracle RAC cluster with about 100 tables that continually receive data.  All tables have a timestamp field and the data is partitioned by date.  Stopping the receipt of the data is not an option.

We have a job that runs every few hours, figures out what partitions correspond to old data that can be deleted and runs

alter table SCH.DATA_TABLE                 drop partition SYS_P1026632 ;

The good news is most of the time, we can drop the partitions and delete the required data.
The bad news is occasionally, the alter table happens at the same time as we are receiving new data and we get an error:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

I have been experimenting with checking to see if there are any locks on the table before running the drop partition but looping, waiting until the table is unlocked does not seem like a very good solution. Plus there is no guarantee that I will actually get in before another process gets the lock.

I have considered requesting a lock on the table, something like

lock table SCH.DATA_TABLE in EXCLUSIVE mode;

and when I get the lock, dropping the partition and releasing the lock but that does not seem like a very good solution either.

I was kind of hoping for an

alter table wait for lock

or something but that does not exist (alter table locks but I need it to wait for the lock rather than just giving up right away.)

Is there a good way to go about this? Is my best bet just to lock/alter/release?

Thank you for any input.


texts: My transaction log is larger than I'd like it to be, and I want to know how I can fix that.


texts:
I have a stored procedure that populates the temp table #employee_benefits with a list of Ids. This table ends up being roughly 10,000 rows long. The query below then selects from a table called EmployeeBenefitData that has around 4 million rows.

SELECT  ebd.EmployeeBenefitDataId, ebd.EmployeeBenefitId, ebd.[DataDefinitionId]
FROM    #employee_benefits eb
INNER JOIN EmployeeBenefitData ebd ON eb.EmployeeBenefitId = ebd.EmployeeBenefitId

The bottle neck was the index scan on the EmployeeBenefitData table. It did the index scan first and then joined it to the temp table. The temp table was acting as a filter meaning that scanning all the data before the join was very inefficient. I added the following code to change the scan to a seek and dramatically cut down the amount of reads needed.

DECLARE @MinEmpBenId INT, @MaxEmpBenId INT

SELECT @MinEmpBenId = MIN(EmployeeBenefitId), @MaxEmpBenId = MAX(EmployeeBenefitId)
FROM #employee_benefits

SELECT  ebd.EmployeeBenefitDataId, ebd.EmployeeBenefitId, ebd.[DataDefinitionId],
        dd.TypeId, dd.DataDefinitionId, dd.Name, ebd.[Value], ebd.[Date], ebd.[Text]
FROM    #employee_benefits eb
INNER JOIN EmployeeBenefitData ebd ON eb.EmployeeBenefitId = ebd.EmployeeBenefitId
INNER JOIN DataDefinition dd ON ebd.DataDefinitionId = dd.DataDefinitionId
WHERE   ebd.EmployeeBenefitId >= @MinEmpBenId AND ebd.EmployeeBenefitId <= @MaxEmpBenId

It makes a massive difference in the client stats

Total execution time 74,      1794
  Wait time on server replies   11,     11

My question is: Is this good practice? And why doesn't the optimiser do this?

UPDATE
I should have mentioned that the temp table has a clustered index on EmployeeBenefitID


texts: Fetch rows which belongs to a certain geo location

texts:
I've a table (locations) which contains records as given below:

id |     lat   |     lng   | radius_in_km
-----------------------------------------
1  | -18.17943 | 83.968051 |     5
2  | 44.5796   | 7.069637  |     10

Now, I've got a point (lat, lng) from Google and I need to fetch the records from locations table if the given lat, lng falls into radius of those rows. Please notice, the radius is dynamic/different for each row so I need to match the records according to their radius with the given point.

I've searched the stackoverflow network but couldn't find anything that works for me, any hints would be fine even not the code.


texts: AlwaysOn Read Only Routing Configuration for Different Listeners

texts:
I have 5 sql servers. 3 of them are working sync and 2 are async. Load Balance Read-Only Routing is a great feature on SQL Server 2016. But if I add async servers into the load balancing configuration it becomes unreliable because of data loss.

I want to add a listener for only queries that data loss is unimportant for a short time. Like reporting queries. How can I configure the sql server load balance routing configuration for different listeners?


texts: WSREP: failed to report last committed and stop

texts:
I have galera cluster and I have 4 nodes. I'm importing some data. however one node gives error. Warning. The warning is;

warning WSREP: Failed to report last committed 59102, -q (interrupted system call)
mysqld_safe Number of processes running now: 0
mysqld_safe WSREP: not restarting wsrep node automatically
mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

What is that error? How can I solve this? This node shouldn't stop, not mysql service stopped in that error. Thanks


texts:
I'm logged in as a user in postgres with PgAdmin tool in Linux connecting remotely but I don't think the user has enough rights. I'm a MySQL experienced user but not so much in Postgres. When I select a table when logged in as the user I get

An error has occurred:

  ERROR:  permission denied for relation acs_activities

how do I add my user "gainpm" to have access to all tables in "projop" database?


texts:
I am part-way through an upgrade from Oracle 11g to 18c on RedHat.

The Database Upgrade Assistant gives the message:

Errors encountered during the execution of "Database Components Upgrade".

Oracle_Server.log shows:

select count(*) from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
select count(*) from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
LRM-00101: unknown parameter name '_first_spare_parameter'
ORA-01078: failure in processing system parameters
select status from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0

How can I resolve the issue LRM-00101: unknown parameter name '_first_spare_parameter'?


texts: How do I find out what version of SQL Server for Linux I have?

texts:
How do I find out what version of SQL I am running if I can't connect to it because it's crashing? Is there something like --version

$ ./sqlservr --version
Dump collecting thread [8633] hit exception [6]. Exiting.


texts:
We have to do database replication with SQL Server; the two database are on different servers.

We achieve replication, but our scenario is when live is down so we use replicated server as our production server.

Here is where the problem occurred: when we insert anything on replicated server from application it fails because of violation of a primary key constraint.

And yes, it violates b/c on replicated server primary key starts from 1

How to overcome this problem? Or it is correct way to convert replicated server to production server?


texts: How to limit an inner query by outer query attribute

texts:
I have a requirement to sort a table by multiple criteria, select the first and associate with another table.  The DB is Postgres.

Let's say we have players and teams where player has played from several teams.  This is not the real composition of the model but should suffice for our example.

The requirement is to get the most recent record of the team the player played for, ordered by teams.end_date if it's present, otherwise by teams.start_date.

The algorithm would seem to be group by players.id, and order by the criteria mentioned above.  However all of my attempts to limit the results to 1 gives me the first team record that is ordered by that criteria.

Here's what I think is the closest I got:

SELECT
  latest_teams.*
FROM players
  JOIN (
    SELECT
      player_id,
      team_name,
      start_date,
      end_date
    FROM teams
    ORDER BY .... --this part is fine
    limit 1
  ) AS latest_teams
  ON players.id = teams.player_id

How can I get the scope of the inner query to be that of the player id?


texts: How to Permanently solve MySQL error 2002 (HY000)

texts:
I have a problem on my cloud server with 2GB of RAM and 40GB Hard Disk that's hosting a Wordpress website that receives around 1000-2000 hits a day. My problem is that MySQL is always crashing and my only option is to reboot the server. I noticed that logs were not enabled so I enabled them and here is what i found:

150207 17:31:42 [Note] Plugin 'FEDERATED' is disabled.
150207 17:31:42 InnoDB: The InnoDB memory heap is disabled
150207 17:31:42 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150207 17:31:42 InnoDB: Compressed tables use zlib 1.2.3.4
150207 17:31:42 InnoDB: Initializing buffer pool, size = 1.2G
InnoDB: mmap(1285636096 bytes) failed; errno 12
150207 17:31:42 InnoDB: Completed initialization of buffer pool
150207 17:31:42 InnoDB: Fatal error: cannot allocate memory for the buffer pool
150207 17:31:42 [ERROR] Plugin 'InnoDB' init function returned error.
150207 17:31:42 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
150207 17:31:42 [ERROR] Unknown/unsupported storage engine: InnoDB
150207 17:31:42 [ERROR] Aborting
150207 17:31:42 [Note] /usr/sbin/mysqld: Shutdown complete

I am guessing this has something to do with memory allocation or some settings in my my.cnf file. These are the contents:

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

[mysqld_safe]
socket                         = /var/run/mysqld/mysqld.sock
log_error                      = /var/log/mysql/mysql_error.log

[mysqld]
user                           = mysql
pid-file                       = /var/run/mysqld/mysql.pid
socket                         = /var/run/mysqld/mysqld.sock
port                           = 3306
datadir                        = /var/lib/mysql
thread_cache_size              = 4
table_open_cache               = 800
table_cache                    = 800
key_buffer                     = 32M
query_cache_type               = 1
query_cache_size               = 64M
query_cache_limit              = 8M
innodb_buffer_pool_size        = 1G
innodb_io_capacity             = 2000
innodb_read_io_threads         = 64
innodb_thread_concurrency      = 0
innodb_write_io_threads        = 64
log_error                      = /var/log/mysql/mysql_error.log
slow_query_log                 = 1
slow_query_log_file            = /var/lib/mysql/ib_slow_log.log

Any help will be greatly appreciated.


texts: Question about non-clustered index storage in SQL Server

texts:
I have a table people with two non-clustered indexes.

create table people(
 id_person int,
 first_name varchar(50),
 last_name varchar(50),
 city varchar(100),
 state char(2),
 zip_code int
)

CREATE INDEX id_first_name_last_name ON people(first_name, last_name, id_person)
CREATE INDEX id_last_name_first_name ON people(id_person, last_name, first_name)

Each index refers to the same columns but in a different order.

-- Insert some rows

insert into people values(1,'joe','smith','new york', 'NY', 10701)
insert into people values(2,'john','smith','new york', 'NY', 10701)
insert into people values(3,'joyce','smith','new york', 'NY', 10701)
insert into people values(4,'jocelyn','smith','new york', 'NY', 10701)

The size of each index is as follows

TableName   IndexName            IndexID    Indexsize(KB)
people          NULL                     0           16
people          id_first_name_last_name  2           16
people          id_last_name_first_name  3           16

Each index is using 16KB.

Now if I add a clustered index on id_person

create clustered index ix_id_people on people(id_person)

I end up with the same amount of space being used by each index.

TableName   IndexName            IndexID    Indexsize(KB)
people          NULL                     1           16
people          id_first_name_last_name  2           16
people          id_last_name_first_name  3           16

The only difference is now instead of a HEAP, I have a clustered index.

I was expecting that with a clustered index on id_person, the storage engine wouldn't need to include id_person anymore in each non-clustered index.

Questions:

Are the values for each column referenced by a non-clustered index stored only for the first non-clustered index and then referenced by all additional non-clustered indexes?

If a non-clustered index references the clustered index column, are the values from that column copied physically onto each non-clustered index or just simply referenced?

Update 14:55

Here is the query being used to calculate 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


texts: Is there a way to speed up restoring data from MySQL to PostgreSQL?

texts:
OS: Windows10 x64
HDD: 1TB
RAM: 8GB
CPU: Intel Core i5-4460
PostgreSQL: 9.6

I want to import almost 37GB data from MySQL into PostgreSQL, and the data is sql file, which full of INSERT INTO tbl_1 VALUES(x, x, x); statements.

I'm using \i data.sql to import it, but after 15 hours work, the PostgreSQL's data size is only 1GB. While during this 15 hours, there are no other tasks running, only the importing.

So, is there anyway i can do to speed up the procedure?


texts: limiting sql server backups inside .bak file

texts:
I have a job scheduled to run a few times everyday. The job backups my databases, like:

BACKUP DATABASE [databasename]
TO DISK = N'd:\sqlserver\backup\databasename.bak'

I can track the backups in the file by running:

RESTORE HEADERONLY FROM DISK = N'd:\sqlserver\backup\databasename.bak'

And then I can restore a database file by using something like:

RESTORE DATABASE databasename
FROM DISK = N'd:\sqlserver\backup\databasename.bak'
WITH FILE = 1455,
RECOVERY,
MOVE 'databasename_data' TO 'c:\...',
MOVE 'databasename_log' TO 'c:\...'

But I have thousands of backups, and I'm reaching my storage limit.

Because I don't need so many backups, I'm wondering if there is a easy way to limit the backup file to keep only the last 90 backups.

Any tips?

UPDATE:

I have just one .bak file with n backups inside. I can read a specific backup using the withfile option in the restore statement. The bellow answers are supposing I have multiple .bak files. What I need to delete is a outdated backup inside the .bak file.


texts: Scripting Microsoft Data Warehouse Wizard

texts:
I am trying to automate the MDW setup process in my environment. Currently I have everything complete except for enabling the data collectors (usually uses a wizard as seen ).

Has anyone seen or used a script for this? Or have any suggestions?
Thanks.


texts: How to store a 64-digit key of alpha numerical values into MySQL Database

texts:
I am student studying in Database Administration (first year), one of our task requires a to design a login form for corporate and regular users. Below are the requirements for the username and password fields:

User name should be any combination of letters and numbers at least
five characters in length and no more than 20. (Same requirements as
user login ID for corporate account ID).
The corporate password is a 64-digit key of alpha numerical values
that all employees in the corporation has access to.
The regular password for both corporate client employees/users and
regular users should contain these types of characters: one
alphanumeric, one number, one uppercase english letter, one lowercase english letters      and one special character and should be at least 8 characters in length and no more than 20.

I do not have any problem in designing the regular expression for regular username and password.

The following is the regular expression for the regular user's password field (meets above requirements):

^(?=.*?[A-Z])(?=.*?[a-z])(?=.*?[0-9])(?=.*?[#?!@$%^&*-]).{8,20}$

The problem now is that I have no clues on how to create a 64-digit key of alpha numerical values nor to store in Database for the corporate id and password


texts: Is it correct way having status or Summary columns in parent table or should choose another precedure?

texts:
My Parent Table is "Jobs" table, and there is child table that records Job's actions named "JobActions" as below:

Jobs (JobId, JobType, CurrentStage, AssignedTo)

JobActions (JobActionId, JobId, CreateDate, ActionType, FromUserId, ToUserId, Message, Detail)

in my grid in web page I list grid columns as below:

JobId, CreateDate, CurrentStage, AssignedTo, LastActionType, LastActionDate,...

In grid for calculating below columns I use joins to child table (very slow when there is many users and many data)

CreateDate  ==> First(or min) JobActions' CreateDate,

LastActionType ==> Last JobActions' ActionType,

LastActionDate ==> Last(or Max) JobActions' CreateDate

I have about five columns like this that needs many join to JobActions or Another tables, So I Decided to change Job table like below:

Jobs (JobId, JobType, CurrentStage, AssignedTo, CreateDate, LastActionType, LastActionDate)

the last three columns always updated whenever any jobaction added to JobActions.

Question 1: Is this a correct version, (am I denormalized my tables?)
Question 2: Should I use trigger for this type of jobs or should update from inside my code?
Question 3: Is this best practice? is there any better solution for this type of problems.


texts: Export data from InterBase / Firebird database without credentials

texts:
I have an InterBase / Firebird database file of an old project for which I lost the credentials. Some information about the database, dumped with the gstat command, is shown below [1]. I'm accessing this file as an embedded database, i.e., without a running instance of Firebird. I'm on MacOS and I've Firebird 2.5 installed, since On-Disk Structure (ODS) 11.2 corresponds to this version.

I'm able to export the database structure using the isql command, but I'm unable to access any data using this tool without a valid username and password, resulting in the following error message:

Error while preparing SQL statement:
- SQLCODE: -551
- no permission for read/select access to TABLE FILES', -551, 335544352

Providing the default SYSDBA/masterkey as the username/password results in the error message:

Error while connecting to database:
- SQLCODE: -902
- cannot attach to password database', -902, 335544653

Since encryption was introduced in the newer Firebird 3 and the data is stored unencrypted (I can view the data with a hex editor) I'd assume there is a way to access this data without credentials and make an export. How can I do so?

[1]: Output of the gstat command

Database "./DATABASE.GDB"
Database header page information:
    Flags           0
    Checksum        12345
    Generation      26450714
    Page size       4096
    ODS version     11.2
    Oldest transaction  25058616
    Oldest active       25058617
    Oldest snapshot     25058617
    Next transaction    25058619
    Bumped transaction  1
    Sequence number     0
    Next attachment ID  1401189
    Implementation ID   26
    Shadow count        0
    Page buffers        0
    Next header page    0
    Database dialect    1
    Creation date       Jul 21, 2014 13:42:21
    Attributes      force write

    Variable header data:
    Sweep interval:     20000
    *END*


texts: Amazon AWS RDS 120GB free storage suddenly became 0GB within less than 2 hours

texts:
I've been using AWS RDS db.m4.large with 128GB storage for MySQL(InnoDB) for more than one year now.

The size of my database is now less than 2GB and the free storage had always been more than 125GB as captured below but it suddenly dropped to 0GB in less than 2 hours 2 weeks ago.

So I upgraded the storage to 250GB but it was dropped to 0GB again two days ago.

Free storage suddenly dropped

So I asked AWS customer center about it and they told me that mysqlInnoDbTablespace took 243GB and I can reduce the size of it by executing OPTIMIZE TABLE on all of my tables.

dataVolumeAvailableSize 43.3 gb,
dataVolumeTotalSize 295 gb
dataVolumeUsedSize  251 gb
mysqlErrorLogFileSize   72.4 kb
mysqlGeneralLogBackupSize   0 bytes
mysqlGeneralLogFileSize 0 bytes
mysqlGeneralLogSize 0 bytes
mysqlInnoDbLogSize  256 mb
mysqlInnoDbTablespace   243 gb
mysqlSlowLogBackupSize  124 mb
mysqlSlowLogFileSize    35.9 kb
mysqlSlowLogSize    4.03 gb

I did it but the free storage hasn't been increased. so I asked them again but they haven't replied my inquiry.

Below is the size of my database. it's less than 2GB.

The size of my database

Did anybody experience this kind of case or can somebody help me to free up the free storage?

Thanks.


texts:
We are building out a data warehouse (and also some data marts as well) in our organization. My DBA skills have mostly been on OLTP type applications, but I am moving into supporting OLAP for our organization.

We ran into an issue recently where one of our ETL programs (in this case it happens to be a Message Driven Bean written in Java) is using the Repeatable Read (RR) isolation level. Because of this, they were running into locks. I obviously question why RR was being used. (EDIT: I originally thought RR on the DB2 side, but learned this was RR from the JDBC side, which maps to RS on the DB2 side. Since then we have also tried RC on the JDBC side, which is CS on the DB2 side. That appears to have resolved some of the locking issues.)

So I started to research what should be the suggested isolation levels for data warehousing, both from the feed/source side, as well as from the retrieval side.

I really can't find much. I have found a few articles that mentioned Uncommitted Read (UR) being good for the query/retrieval side, but nothing about the feeding side. I was thinking that Cursor Stability (CS) or at the highest Read Stability (RS) should be used. I would side with CS, but perhaps that is tainted by OLTP thinking?

In our case the main feed for our warehouse is right now done "real-time" using Java message queues. Retrieval from the warehouse will be done with ETL tool (DataStage in our case), probably kicked off as nightly jobs (some for daily reports, others for monthly, or quarterly reports). These reports will be based off of our marts. So while the warehouse might be fed "real-time", it won't be read from, or actively queried over (at least not right now).

Granted, we use DB2 9.7 FP 4 (and we are hoping to migrate to 10.1 sometime next year) and some of the specifics may differ per vendor, but what isolation levels do you warehouse DBA's recommend for feeds and reads and why?

Also, any input on isolation levels for marts would be appreciated to.

  EDIT: Just so it is easier for other vendor comparisons. Here is the table of DB2 isolation level names as mapped to the JDBC isolation
  levels. (This per the book Understanding DB2: Learning Visually By
  Examples and thus copyright IBM.)

+------------------------------+-----------------------+
|            JDBC              |          DB2          |
+------------------------------+-----------------------+
| TRANSACTION_READ_UNCOMMITTED | Uncommitted Read (UR) |
+------------------------------+-----------------------+
| TRANSACTION_READ_COMMITTED   | Cursor Stability (CS) |
+------------------------------+-----------------------+
| TRANSACTION_REPEATABLE_READ  | Read Stability   (RS) |
+------------------------------+-----------------------+
| TRANSACTION_SERIALIZABLE     | Repeatable Read  (RR) |
+------------------------------+-----------------------+


texts: SQL Server is unable to connect to server , requires the actual server name

texts:
I buy a cloud vps on vsp.ag , which is ubuntu os and I install sql server
using this .
I can remotely connect from my local PC using IP address of VPS . But I can't configure the distribution.

It said

SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name,

'v3149'. (Replication.Utilities)

What I want to do is synchronization my local database and the database on vps using merge replication .
How can I fix it ?


texts: How to update multiple columns of multiple rows in one SQL statement

texts:
In my SQL Server database I want to update columns of multiple rows. I can do it for one row only:

UPDATE theTable
SET theColumn = case
WHEN id = 1 then 'a'
WHEN id = 2 then 'b'
WHEN id = 3 then 'c'
WHERE id in (1, 2, 3)

How can I update more columns (theColumn2, theColumn3...) in the same query?


texts: SQL Server Cache population on server / service startup

texts: We have upgraded our SQL Server instance with buckets of RAM, so it can now much more effectively cache . The issue is that it takes time (days) to populate the cache. As far as I understand this is something internally managed by the engine, based on user queries. Is there value in pre-empting this process by running a series of select * statements on big / frequently used tables to cache all this data on service start up? Any particularly clever way to do it?


texts: Do you need Table Lock when you have multiple OLE DB connections inserting to the same table?

texts:
SSIS is not really my forte. I noticed you have a table lock option on OLE DB destinations where the entire table becomes locked in what I assume is during the transaction of inserting data.

What happens if you have a flat file data source with a conditional split that is parsing out data into 5 or more OLE DB destinations that are targeting the same table with table lock on? Would each OLE DB destination get blocked by one another in this scenario if data is being fast loaded (inserted) into the destinations themselves?

I removed the table lock in my instance and everything seemed fine. It was splitting the data and inserting records at about 1 million records per minute.


texts: How to store data in look-up tables when some of it may be semantically different, but logically the same?

texts:
I am working on a system that receives communications from various laboratories. Each laboratory has two separate, independently operated data services which means that Laboratory A and Laboratory B could send the same data to the system, but they may send it in different formats (if they use different systems). I'm trying to figure out the best way to handle this. Here's an example:

The system I am working on receives patient appointment status information, specimen collection information and final laboratory test results.

When Laboratory A sends appointment status information or specimen collection information, it comes from one system. Part of the information they send might be XML such as:

<CollectionStatus>INPRC</CollectionStatus>
<ReasonForTest>PRE</ReasonForTest>
<IdSampleType>UR</IdSampleType>

This indicates to our system that the Urine (UR) specimen collection is in process (INPRC) and the reason for that test is "Pre-Employment" (PRE).

Later on when Laboratory A sends the final laboratory test results for the same test they may send XML such as:

<CollectionStatus>OK</CollectionStatus>
<ReasonForTest>PRE-EMPLOYMENT</ReasonForTest>
<IdSampleType>R</IdSampleType>

This indicates to our system that the Urine (R) specimen was collected successfully (OK) and that the reason for the test is "Pre-Employment" (PRE-EMPLOYMENT).

The same lab may send the same information multiple times, but because they come from different systems at the same laboratory, the data sent may be different.

The same applies to different laboratories. Laboratory B may send:

<CollectionStatus>2</CollectionStatus>
<ReasonForTest>PE</ReasonForTest>
<IdSampleType>U</IdSampleType>

and

<CollectionStatus>1</CollectionStatus>
<ReasonForTest>1</ReasonForTest>
<IdSampleType>UR</IdSampleType>

To handle this, right now I have three tables for each differing variable. Using ReasonForTest as an example I would have these tables:

The table "TestReasons" holds data that is standard for our system. Such as (for example):

| TestReasonID | Reason                         |
-------------------------------------------------
|            1 | Pre-Employment                 |
|            2 | Post-Accident                  |
|            3 | Return to Duty                 |
|            4 | Promotion                      |

The other tables, "Results.TestReasons" and "Orders.TestReasons" are simply there to allow the system to convert the data it receives from laboratories into the data our system understands and needs to operate and if necessary, convert it back. The "Name" field holds text that is "UX friendly" and is typically shown on reports or our system's front facing UX. The "Value" field holds the data that the laboratory identified in the "LaboratoryID" field would send (such as "PRE" or "PRE-EMPLOYMENT"). Since not all laboratories may test for the same reasons (the above examples are some typical drug testing reasons), it's possible that the "Results.TestReasons" and "Orders.TestReasons" may not have a record that matches every entry in the "TestReasons" table.

Since there are (at the moment at least) 13 different data types that may be sent at any given time, that ends up being 39 different tables to simply convert from one data type to another. I don't know why, but this just sits funny with me and I feel like there's probably a better way to do this.

Am I missing something or does this seem like the best way to achieve this?


texts: out of all columns in a table, how can I find the one datatype that is cheapest and I could convert all other columns datatypes into?

texts:
I sometimes have to unpivot tables, that means, converting all columns into rows.

Generally that becomes a table with 2 columns (just to simplify things) - the first is the column name and the second is the column value.
.
In the example below I found out manually by looking at the column types in the original table, that the best column to store the value is nvarchar(256).

Questions:

How could I find this "the best value to use as the values column type is nvarchar(256)" out dynamically?

I know I could use sql_variant for the values column type? In this case can I ignore the collate database_default?

That would be an expensive overkill - and I want to avoid that:

  sql_variant can have a maximum length of 8016 bytes. This includes
  both the base type information and the base type value. The maximum
  length of the actual base type value is 8,000 bytes.

--=============================================================================
-- Unpivot the table - SUCCESSFUL
--=============================================================================

IF OBJECT_ID('TEMPDB.DBO.#UNPIVOT') IS NOT NULL
   DROP TABLE #UNPIVOT

CREATE TABLE #UNPIVOT(
    [ColumnNames] [nvarchar](128) NULL,
    [AllCol] [nvarchar](256) NULL
)

INSERT INTO #UNPIVOT([ColumnNames],[AllCol] )
SELECT
     ColumnNames
    ,AllCol
    --INTO TABLEBACKUPS.DBO.UNPIVOT_
FROM (
        select
                [host_platform]                   = CAST (host_platform collate database_default as [nvarchar](256)),
                [host_distribution]               = CAST (host_distribution collate database_default as [nvarchar](256)),
                [host_release]                    = CAST (host_release collate database_default as [nvarchar](256)),
                [host_service_pack_level]         = CAST ([host_service_pack_level] collate database_default as [nvarchar](256)),
                [host_sku]                        = CAST ([host_sku]  as [nvarchar](256)),
                [os_language_version]             = CAST ([os_language_version]  as [nvarchar](256)),
                [host_architecture]               = CAST ([host_architecture] collate database_default as [nvarchar](256))
       from
       sys.dm_os_host_info
      ) Radhe
UNPIVOT
(
AllCol
FOR ColumnNames IN (
                      host_platform
                     ,host_distribution
                     ,host_release
                     ,host_service_pack_level
                     ,host_sku
                     ,os_language_version
                    ,host_architecture
                  )
) unpvt

SELECT [ColumnNames]
      ,[AllCol]
FROM #UNPIVOT


texts: Query timeouts when duration is under a second

texts:
We are getting the following exception in an application:

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

So, I looked at the query and sure enough it was taking a really long time.  So I tuned it up to take less than a second.
But I am still getting timeouts.
So I set up a Extended Events session for the Attention event to catch the timeouts and see what what going on (using this page as a guide).
The results were confusing.  I see my queries in the list, but the duration logged in the trace is under a second!
Here are some example values I am getting in duration column for the timeout:

timeout

539

539

474

423

505

904

I am confused.  Why am I getting an SQL Server timeout error, when the duration shows an average of a half a second?
NOTE: The output from the extended event shows some rows that are 30+ seconds.  These are legit timeouts to my view and I am working on those.  (I just point this out to show that the Attention extended event seems to be working for those queries.)
I thought it might be blocking, so I setup a Blocked Process Report extended event to see (following this guide). While there are blocks happening on the server, they are not on the databases that my query is running against.
The application is using a API called Dapper (a fairly popular Micro ORM that I hear is used to run Stack Exchange).  I imagine it has decent connection management in it.  But even if they misused its connections, as I understand it, that leads to resource leaks, not SQL timeouts.
The timeout of the query in the application is set to 120 seconds. That should be plenty of time to get most of the queries that I am seeing reported as timeouts.


texts: Powershell and dbo.sysjobsteps.Command field being truncated - not a length issue

texts:
On the accepted answer to Export SQL Agent jobs with steps and scripts, there is a comment:

  Command is unlikely to get pulled into Excel very nicely. – Aaron Bertrand ♦ Feb 2 '15 at 21:27

This seems very true.

I am trying to pull the dbo.sysjobsteps.Command field into a csv file using Powershell Export-Csv.  The data seems to be truncated as of the first space found in the Command field (has nothing to do with a length limitation).

Do you know why?


texts: Msg 7399 - Query table in linked server received Authentication failed

texts:
I created a linked server, then I tried to query the table from the server. I received the following message:

OLE DB provider "SQLNCLI11" for linked server "DBServer" returned message "Invalid authorization specification".

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI11" for linked server "DBServer" reported an error. Authentication failed.

Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "DBServer".

--My local host login credential: WSSERVER2012\Administrator

-- under Server Objects-->Linked Servers -->DBServer (database AP displayed)

The code I used to created linked server:

EXEC sp_addlinkedserver
   @server='DBServer',
   @srvproduct='',
   @provider='SQLNCLI',
   @datasrc='localhost'
go

The code I used to query the table

USE AP;
go

SELECT
VendorName,
CustLastName,
CustFirstName,
VendorState AS State,
VendorCity AS City
FROM DBServer.AP.dbo.Vendors AS Vendors
    JOIN DBServer.ProductOrders.dbo.Customers AS Customers
    ON Vendors.VendorZipCode = Customers.CustZip
ORDER BY State, City

Please review the configuration setting for linked server and advise what configuration I need to change in order to query the able listed under the linked server. Thanks!

linked server general tab

linked server security tab

linked server server option tab

I did some search online and used the following steps to make the query work.
Connection was made from local host to the linked server.

drop linked server using sp_dropserver

exec sp_dropserver
@server = 'DBServer'
go

Re-create the linked server using the same code
From Server Objects-->Linked Servers->right click on the 'DBServer' Choose properties-->under the Security tab select Be made using the login's current security context


texts: Replicated Database Log File Maintenance

texts:
I have a database on the publisher that is involved in replication (publication configured for merge and transaction). Trying to regain control of the log file for this particular database (VLF count, size, etc.).

Is there anything I need to do (or be cautious of) with the replication setup before trying to perform any maintenance on the log file? I am not an expert in the area of replication and cannot find anything solid that provides guidance as to what measures should be taken.

Edit: This would include working on the distribution database as well, data retention was not configured at all for some reason.


texts:
Hi everyone i am getting an error trying to run the below query ERROR 1067 (42000): Invalid default value for 'uploadedDate'

CREATE TABLE `filenew` (
  `id` int(11) NOT NULL AUTO_INCREMENT  /* MYSQL_TYPE_LONG */,
  `originalFilename` varchar(255) CHARACTER SET utf8 NOT NULL  /* MYSQL_TYPE_VARCHAR */,
  `shortUrl` varchar(255) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `fileType` varchar(150) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `extension` varchar(10) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `fileSize` bigint(15) DEFAULT NULL  /* MYSQL_TYPE_LONGLONG */,
  `localFilePath` varchar(255) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `userId` int(11) DEFAULT NULL  /* MYSQL_TYPE_LONG */,
  `uploadedUserId` int(11) DEFAULT NULL  /* MYSQL_TYPE_LONG */,
  `totalDownload` int(11) DEFAULT NULL  /* MYSQL_TYPE_LONG */,
  `uploadedIP` varchar(45) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `uploadedDate` timestamp DEFAULT NULL  /* MYSQL_TYPE_TIMESTAMP2 */,
  `statusId` int(2) DEFAULT NULL  /* MYSQL_TYPE_LONG */,
  `status` enum('active','trash','deleted') DEFAULT 'active'  /* MYSQL_TYPE_ENUM */,
  `visits` int(11) DEFAULT '0'  /* MYSQL_TYPE_LONG */,
  `lastAccessed` timestamp DEFAULT NULL  /* MYSQL_TYPE_TIMESTAMP2 */,
  `deleteHash` varchar(32) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `folderId` int(11) DEFAULT NULL  /* MYSQL_TYPE_LONG */,
  `serverId` int(11) DEFAULT '1'  /* MYSQL_TYPE_LONG */,
  `adminNotes` text CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_BLOB */,
  `accessPassword` varchar(32) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `fileHash` varchar(32) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `minUserLevel` int(3) DEFAULT NULL  /* MYSQL_TYPE_LONG */,
  `linkedFileId` int(11) DEFAULT NULL  /* MYSQL_TYPE_LONG */,
  `keywords` varchar(255) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `description` varchar(255) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `isPublic` int(1) NOT NULL DEFAULT '1'  /* MYSQL_TYPE_LONG */,
  `total_likes` int(11) NOT NULL DEFAULT '0'  /* MYSQL_TYPE_LONG */,
  `uploadSource` enum('direct','remote','ftp','torrent','leech','webdav','api','fileimport','other') CHARACTER SET utf8 NOT NULL DEFAULT 'direct'  /* MYSQL_TYPE_ENUM */,
  `unique_hash` varchar(64) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `date_updated` datetime DEFAULT NULL  /* MYSQL_TYPE_DATETIME2 */,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_hash` (`unique_hash`),
  KEY `shortUrl` (`shortUrl`),
  KEY `originalFilename` (`originalFilename`),
  KEY `fileSize` (`fileSize`),
  KEY `visits` (`visits`),
  KEY `lastAccessed` (`lastAccessed`),
  KEY `extension` (`extension`),
  KEY `userId` (`userId`),
  KEY `statusId` (`statusId`),
  KEY `userId_2` (`userId`),
  KEY `uploadedDate` (`uploadedDate`),
  KEY `folderId` (`folderId`),
  KEY `serverId` (`serverId`),
  KEY `fileHash` (`fileHash`),
  KEY `linkedFileId` (`linkedFileId`),
  KEY `statusId_2` (`statusId`),
  KEY `uploadedUserId` (`uploadedUserId`),
  KEY `keywords` (`keywords`),
  KEY `status` (`status`),
  KEY `uploadedIP` (`uploadedIP`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

I have tried many many fixes on Google and other forums but none of them worked, few things that i have tried are

Setting in my.cnf

sql_mode=NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Trying this

SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

...and many more. If someone could please help me out in this matter, because I am not experienced in databases, then I would really appreciate the help.

I am on centos 7 and MariaDB10.3.23-MariaD


texts: Identify performance bottleneck in SQL Server, checked the usual suspects

texts:
I'm trying to identify what users are describing as slow application performance. I have a hunch it may be related to the application itself and not an issue with the SQL server. I've went over many of the usual suspects to identify common bottlenecks.

Is there anything i interpreted incorrectly?
Is there anything i neglected to review?

The MS SQL server (11.0.5058) is running on Hyper-V, 2012r2 with the following assigned to the guest: 72GB ram, 16 processors, 4 disk controllers with tempDB, OS, DB, Program Files all on separate VHDs.

Ran perfmon over the course of a day

% Processor Time < 40% Max

Avg Disk Q Length 0.003 Avg - 0.41 Max

% Disk Time Avg 0.0038

Avg Avail MBytes 5010

Buffer Cache Hit Ratio 99.996 Avg 98.614 Min

I don't see any issues with disk, mem, or processor. Where should i look next?


texts: SQLite and Write Ahead Logging: why so large files

texts:
I have been doing some operations on SQLite databases recently.

The other day I ran a command to DELETE a bunch of rows from a 1GB database file and the WAL file got over 8GBs at which point it filled my partition.

Why did it do this?  How can I stop it?

Here are some of the PRAGMA's that are setup for this database:

PRAGMA wal_autocheckpoint = 1000
PRAGMA page_size          = 1024
PRAGMA max_page_count     = 1073741823
PRAGMA journal_size_limit = -1
PRAGMA journal_mode       = WAL
PRAGMA auto_vacuum        = 0

What can I change to stop WAL files getting so big when I want to make the database smaller because it is starting to eat my disk space?  And will such a thing affect performance?

I do know the library was custom compiled, but I am not sure of the compilation time options.  Can this be found out somehow?

This is running on and SSD in Debian Linux  if that is relevant.


texts:
I have this relation:

R = ABCDE
F: AB->DE, AC->B, DE->C, AE->C, C->AB.

Is AC->B a partial dependency in F?

I'm guessing it is, because

C->AB can be decomposed to

C->A
C->B

from where we can conclude that there exists a subset of AC (C in our case) which goes into B, that is

C->B.

I couldn't find a formal definition on the internet, so I would like it if someone could confirm my assumption.


texts: How to do split each quantity and grouping by child customer order in one table

texts:
I have some data like in picture below on and need to perform some query.

And then I try to produce result like the picture below, but I have trouble and don't know how to split qty by child customer order.

How to get results like picture 2?

This is the query I have tried in order to obtain the result I want:

SELECT item
    ,no_po
    ,sum(qty) as total
    ,parent_customer
    ,child_customer
    ,qty
FROM `detail_order`
WHERE received_date='2018-06-27'
group by item
ORDER BY item asc


texts: Unable to start listener of standby database

texts:
As part of data guard configuration, I've installed standby database using Oracle Software only and configured .ora files/folders as required. But while trying to start the listener or check the status in standby, it's throwing the following error.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
64-bit Windows Error: 61: Unknown error

Then, I tried to start db using pfile and that too shows error as follows.

SQL> startup pfile=C:\app_home\product\11.2.0\dbhome_1\database\initLOCAL2.ora
ORA-12560: TNS:protocol adapter error

As I'm doing this for the first time, I'm very much confused at this stage. How can I resolve this?


texts: SQL Server - What is the difference between BULKADMIN and ADMINISTER BULK OPERATIONS

texts:
To grant user ability to run Bulk Insert T-SQL command, login/user needs to be granted:

BULKADMIN server role - or - ADMINISTER BULK OPERATIONS server-level permission
connect on target database
insert on target table

But what is the difference between BULKADMIN server role and ADMINISTER BULK OPERATIONS server-level permission ?


texts:
I have a simple database with 2000 rows

create table [TableName](
    [variable1] [varchar](50) not null,
    [state] [varchar](50) not null,
    [date] [date] not null
)

That look like this

[variable1] [state] [date]
 A           UT      2015-01-01
 A           UT      2015-01-02
 B           UT      2015-01-02
 B           UT      2015-01-02
 A           TX      2015-01-01
 C           TX      2015-01-01
...

I want to add and identity column that would be based on variable1 and state so the table would look like this

[MyID] [variable1] [state] [date]
 1      A           UT      2015-01-01
 1      A           UT      2015-01-02
 2      B           UT      2015-01-02
 2      B           UT      2015-01-02
 3      A           TX      2015-01-01
 4      C           TX      2015-01-01
 ...

add [MyID] [int] identity(1,1) not null doesn't work, since it just gives each row a unique id.
I want each distinct value in variable1 column to have a unique id, but I want to also use state column incase several states have same value