texts: Why are periodic restarts required to keep my instance performing well?

texts:
We've got a production DB server on SQL 2005.  Everything runs normally for a while, but after a couple weeks we see a notable performance drop.  Only restarting SQL Server brings performance back to normal.

Some background:

Running over 1200 databases (mostly single tenant, some multi-tenant). Before anyone lectures on moving to only multi-tenant, there are valid reasons for keeping this structure......
RAM is 16 GB.  After restarting, it doesn't take too long for SQL Server to go back to 15 GB usage.
Active DB connections is about 80 connections - which we feel is fairly healthy considering there is one connection pool per web server per process - so we don't have a connection leak issue.

We've tried several things in non-peak times:
- Run DBCC DROPCLEANBUFFERS (with a CHECKPOINT) to clear data cache. It has no effect, nor does it clear any of the RAM usage).
- Run FREEPROCCACHE and FREESYSTEMCACHE to clear query plans and stored proc cache. No effect.

Obviously restarting SQL Server is not ideal in an active production environment.  We're missing something.  Anyone else go through this?

UPDATE: April-28-2012
Still battling this problem.  I've lowered the memory for SQL Server to 10 GB, just to rule out any contention with the OS.  I'm getting closer to narrowing it down, but need some help from my next step.

Here's what I found, after restarting SQL Server, the page file hovers between 12.3 GB and 12.5 GB.  It'll stay that way for days.  Total server threads will hang out between 850 and 930 - also stable and consistent for days on end (sqlserver is steadily between 55 and 85 of those depending on traffic).

Then, there's "an event".  I have no idea what the event is, I can't see it in the logs, and I can't see anything consistent on the day of week or time it happens, but all of the suddent he pagefile jumps to either 14.1 or 14.2 GB, and the threads jump to between 1750 and 1785.

Checking perfom when this happens, over 900 of those threads are sqlserver.  So I go to sp_who2 to see where are these threads are coming from...and there's just the used 80 or so db connections.

So....does anyone have any ideas how I can locate where the rest of these 900 threads on SQL server are, and what they are doing?

UPDATE: June-01-2012
Still battling the issue. For anyone reading this still, the issue with the threads jumping up has been resolved. This was caused by autodated ComVault backup software. It was creating a thread trying to backup databases that were no longer there (it was maintaining a list of previous databases) rather than just backing up the current databases.

But - the issue still remains, and we have to restart every week, give or take a few days.  Working with the Rackspace team to see if they can shed any light.


texts: Constraint for multiple foreign key yet it's mutually exclusive (PostgreSQL)

texts:
Loan table associated to due table.

also

Invoice table associated to due table.

In due table either one of them foreign key should present and other one should be null.

What kind of constraint will be perfect ?

Thanks.


texts: Unexpected HY001: Memory allocation error after MySQL upgrade

texts:
Earlier today I upgraded a db.t3.large MySQL instance on AWS from v8.0.17 to 8.0.20.  I've seen a handful of cases where I'm seeing the error SQLSTATE[HY001]: Memory allocation error: 1038 Out of sort memory, consider increasing server sort buffer size.  I don't expect such an error because the config or instance type didn't change.  An example of where I'm seeing this is on the following query:
SELECT * FROM `messages`
WHERE
    EXISTS (
        SELECT * FROM `updates`
        WHERE
            `messages`.`update_id` = `updates`.`id`
            AND `game_id` = 18
    )
    AND `messages`.`deleted_at` IS NULL
ORDER BY `id` DESC
LIMIT 6

In this query, all columns used in the order and where clauses are either foreign or primary keys with messages.deleted_at being the only exception.
In the meantime I'm going to adjust this setting in my prod environment to increase the size of the sort_buffer_size, but this change is not recommend according to a lot of stuff I'm reading so I hope it'll temporarily resolve the issue.


texts:
Is it necessary to REINDEX or REBUILD the indexes of a table after truncating it and then repopulating it?

I have a table with approximately 3.5 million rows in Oracle. Every week new data is recharged, truncating the table and then loading the data.

There are 2 or 3 indexes in the table. Should I delete and re-create the indexes or simply rebuild the ones I already have? , some indexed columns are encrypted with TDE.

Thank you


texts: Monthly summary of active users based on a start/end date roster

texts:
I have a table that dictates who was on a project, from a startdate to an enddate. I'd like some help writing a query that will return the number of "active" users at the end of every month, since the start of this year.
DROP TABLE "public"."roster";
CREATE TABLE "public"."roster" ("id" int,"user_id" int,"project_id" int,"start_date" datetime,"end_date" datetime,"closed_date" datetime, PRIMARY KEY ("id"));

INSERT INTO "public"."roster" ("id", "user_id", "project_id", "start_date", "end_date", "closed_date") VALUES
(1, 1, 1, '2019-05-27 00:00:00', '2021-01-15 00:00:00', NULL);

INSERT INTO "public"."roster" ("id", "user_id", "project_id", "start_date", "end_date", "closed_date") VALUES
(2, 2, 2, '2020-05-27 00:00:00', '2021-02-01 00:00:00', '2021-02-05 00:00:00');

INSERT INTO "public"."roster" ("id", "user_id", "project_id", "start_date", "end_date", "closed_date") VALUES
(3, 3, 3, '2020-05-27 00:00:00', '2024-02-01 00:00:00', '2021-02-05 00:00:00');

INSERT INTO "public"."roster" ("id", "user_id", "project_id", "start_date", "end_date", "closed_date") VALUES
(4, 4, 4, '2020-05-27 00:00:00', '2021-03-05 00:00:00', NULL);

id  user_id project_id  start_date            end_date              closed_date
1   1       1           2019-05-27 00:00:00   2021-01-15 00:00:00   NULL
2   2       2           2020-05-27 00:00:00   2021-02-01 00:00:00   2020-02-05 00:00:00
3   3       3           2020-05-27 00:00:00   2024-02-01 00:00:00   2020-02-05 00:00:00
4   4       4           2020-05-27 00:00:00   2021-03-05 00:00:00   NULL

And the result would be something that shows how many distinct users had an active project within each month (only since the start of this year).
So for the dataset above, we can see that all 4 projects were "active" in the month January 2021, because the end date is in the future.
2021-01-31 | 3 (4 projects were active in some way, during January)
2021-02-28 | 2 (3 projects were active in some way, during February)
2021-03-31 | 1 (1 project was active in some way, during March)

The last bit of complexity is that sometimes a project can be closed before the EndDate and I'd like to exclude any users who's endDate is in the future but the project has actually closed.
For example in the dataset above, the third project has an end date of Feb 2024, however the project closed in Feb 2021. So technically the person was active in 2021 Jan and Feb, but not March 2021 and beyond.
p.s Hoping to get an answer for Redshift


texts: Find jobs and dependencies run between two timestamps

texts:
I would like to know what jobs ran and dependencies (what triggered them) between two timestamps or dates.

I have been looking in all the oracle views. I cannot find anything like this in the dba_scheduler_jobs view.


texts: azure mysql database replication with on-prem mysql master is failing with 2003 error

texts:
I have setup azure mysql master database and I would like to do data-in replication with an on-prem mysql database but the replication is failing. Can someone tell me the reason ?
I am able to connect azure mysql to on-prem mysql master
running the change to master command below on azure mysql:
azure mysql -
+------------+
| VERSION()  |
+------------+
| 5.7.29-log |
+------------+
CALL mysql.az_replication_change_master('xx.xxx.xx.xx', 'abcd', 'abcd', 3306, 'bin.000141', 12176221, 'cert');
CALL mysql.az_replication_start;

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: xx.xxx.xx.xx
                  Master_User: abcd
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin.000141
          Read_Master_Log_Pos: 12175973
               Relay_Log_File: relay_bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: bin.000141
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%,sys.%
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 12175973
              Relay_Log_Space: 154
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: C:\work\primary_ca.pem
           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: 2003
                Last_IO_Error: error connecting to master '15000@xx.xxx.xx.X:3306' - retry-time: 60  retries: 1
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID:
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 210126 00:06:31
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:

on-prem master server configuration -
+---------------+
| version()     |
+---------------+
| 5.7.25-28-log |
+---------------+


texts:
looking for some help.  I have just started looking at the Blitz stats but getting the following error.  This is whilst running sp_BlitzIndex. I am using Version 7.2 (dated 28/01/2019).

  FOR XML could not serialize the data for node 'col_definition' because
  it contains a character (0x001F) which is not allowed in XML. To
  retrieve this data using FOR XML, convert it to binary, varbinary or
  image data type and use the BINARY BASE64 directive.

Is there any easy way to fix this or is it a case of adding a number of replace statements to remove invalid characters?

It looks like the error is triggering at the start of Step 2 (line 1667). The specific section of code is but I think the following lines would also have the same issue.

RAISERROR (N'Updating #IndexSanity.key_column_names',0,1) WITH NOWAIT;
UPDATE    #IndexSanity
SET        key_column_names = D1.key_column_names
FROM    #IndexSanity si
        CROSS APPLY ( SELECT  RTRIM(STUFF( (SELECT  N', ' + c.column_name
                            + N' {' + system_type_name + N' ' + CAST(max_length AS NVARCHAR(50)) +  N'}'
                                AS col_definition
                            FROM    #IndexColumns c
                            WHERE    c.database_id= si.database_id
                                    AND c.schema_name = si.schema_name
                                    AND c.object_id = si.object_id
                                    AND c.index_id = si.index_id
                                    AND c.is_included_column = 0 /*Just Keys*/
                                    AND c.key_ordinal > 0 /*Ignore non-key columns, such as partitioning keys*/
                            ORDER BY c.object_id, c.index_id, c.key_ordinal
                    FOR      XML PATH('') ,TYPE).value('.', 'nvarchar(max)'), 1, 1, ''))
                                ) D1 ( key_column_names );


texts: Is there any reasonable way to determine what executed a query?

texts:
I have an S3 SQL Server database in Azure, and I'm having an issue with a specific Entity Framework generated query and how often it is being run.

As the numbers below show, I'm getting 160K+ executions per hour, and this number is unexpected (for the time periods illustrated I would have expected less than 10K executions per hour).

The number of executions also doesn't correlate with the number of requests to the API methods that issue this query (they were called about 50 times in the corresponding period).

So my question is: using Azure analytics is there any way I can trace the query to determine who/what made the call? Can I somehow get an IP address? This is clearly a code problem, is there any helpful way to determine where the calls are coming from?


texts: Where are Full-Text indexes in SSMS 2008 R2

texts: I created a new database, a few tables, and full-text index & catalog using SQL Server Management Studio without any problem.  I wanted to copy the T-SQL creation scripts for each of those to include them in my documentation.  I can get the creation scripts for the database, tables, foreign keys, and the catalog but I can't seem to find the full-text index.  I've checked the related table's Script Table As->CREATE To and it's not there, nor is it with the Catalog.  Any ideas?  Is it because I'm only running SQL Server Standard edition?


texts: Why are Stored Procedure schema changes not being replicated when using ”initialize with backup”?

texts:
In SQL Server I have set up transactional replication using the "initialize with backup" option. Data is being replicated fine, as are schema changes to tables. However, schema changes to stored procedures are not appearing as a replicated command and therefore are not being made on the subscriber.
I found this post which seems to describe my problem, but I have been unable to implement the workarounds.
In my testing I'm positive I had this working and I think it is because I first created the replication using the GUI, which turns on Snapshot replication even though I wasn't using it. This leads me to believe that I should be able to turn on parts of the Snapshot Replication process and get this to work (I think this is what the first workaround in the blog post is alluding to).
Can anyone provide further information on the issue or suggest how I  can get this working?


texts: foreign key relationships and auto-incrementing in Sql Server

texts:
I have two instances of a database with these two tables:

Foo

foo_id <-- primary key

Bar

bar_id <-- primary key

foo_id <-- foreign key

bar_id and foo_id are automatically incremented, read only primary keys.

What I want to do is this:

On database instance 1, I drop all the rows in foo and bar
Import the rows from database 2 to database 1

How can I keep the relationship between tables Foo and Bar in this setup? That is, whatever the primary key in Foo is, the corresponding row in Bar has the right foreign key value to Foo no matter what value foo_id has?


texts: Keeping an up to date copy of a SQL Server database on another server

texts:
We outsourced some of our business functions to another company. That company makes use of the same software as we do and we would like to keep a update to date copy of their database on our servers for monitoring purposes.

Just to clarify what I mean by having an an update to date copy we don't mind if the database is only updated or synchronized once a day.

Last thing to keep in mind is that the connection between the companies is a 10 Mbps connection.

We are both running Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64).

How would you tackle this?

Edit

I don't expect the database to grow to more than 8GB in the next 2-3 years.


texts: Mariadb connection fails through tunnel when bind address = localhost

texts:
I have a MariaDB 10.1.26 virtual server running on Debian 9. It is connected to via ssh tunnel and the bind address = localhost. It has been working without fail untill recently.
Now when I connect via ssh tunnel I get the error

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet'.  system error: 0 "Internal error/check (Not system error)"

Connecting locally is ok. Changing the bind address to 0.0.0.0 allows the ssh connections to work. The exact moment the ssh connections failed the only thing I was doing was trying to connect to the server with PHP with mysqli but can't see this being the cause. I have firewalled the port used but would like to change the bind address back. What would cause this change?

Thanks

EDIT: added full error message.
EDIT: I ran strace -e trace=network mysql -u <user> -p -h 127.0.0.1 the line where the error occurs is:
recvfrom(3, "", 16384, 0, NULL, NULL)   = 0 this recvfrom on a working connection reads recvfrom(3, "b\0\0\0\n5.5.5-10.1.26-MariaDB-0+deb"..., 16384, 0, NULL, NULL) = 102

EDIT: I changed bind-address = localhost to bind-address =127.0.0.1 and it works fine now? My /etc/hosts file hasnt changed.


texts: Postgres: difference between CTE and temporary table

texts:
In Postgres is there a difference between a CTE and a temporary table other than the fact that the CTE exists just for the context of one statement?

Documentation says that

  Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query.

Does "can be thought" means that they are identical?


texts: What MySQL storage engine should I choose?

texts:
I have a database for my catalog webpage. Some tables are about 3GB with millions of rows.

What engine should I choose for my database, InnoDB or MyISAM?

There will only be about 1,000 new records a week, and about 1,000 deletes, but I will modify some data in one table quite often (price, quantity, etc.).  All other tables will be "sealed".

I need indexes to enable fast queries. Which storage engine is best for this scenario?


texts: How to recover previous values in Mysql Table?

texts:
I have MySQL Innodb table of 14M rows. There was a column called is_disabled which had different values. By mistake I ran the query without the where clause:

update url_queue set is_disabled=1

while wanted to run

update url_queue set is_disabled=1 where is_disabled=3;

I havent told my boss yet. And if somehow I can undo this before the meeting I would be able to save my job.

Please help!


texts: Prefix scale for UTL_MATCH.JARO_WINKLER_SIMILARITY

texts:
This is a repeat of a previous question from several years ago that did not receive an answer.

Oracle has a string matching package, UTL_MATCH, which contains the subprogram JARO_WINKLER_SIMILARITY.

Jaro Winkler Similarity is a variant of the Jaro Distance, but includes a prefix scale factor to account for common characters at the start of two strings being matched. I cannot find any documentation for the default prefix scale in the UTL_MATCH package, or how to set this manually. Does anyone know of any documentation on this?


texts: How to join multiple columns from different tables

texts:
How can you join multiple tables and display values from tables that referenced further in other tables?

select booking.booking_id,
(select customer.first_name from customer
where customer.customer_id=booking.customer_id) as First_Name,
(select customer.last_name from customer
where customer.customer_id=booking.customer_id) as Last_Name,
(select aircraft_model.aircraft_model from aircraft_model
where aircraft_model_id = (select aircraft.aircraft_model_id from
aircraft
where aircraft_modle_id = booking.aircraft_id))

from booking;

As you can see I'm trying to pass on the value of aircraft_model_id from the table booking to the aircraft_model table.

The only table connecting the aircraft_model table to the booking table is the aircraft table..

I'm completely new to sql so please do state if there are any errors.

Added in the codes for the table:

CREATE TABLE Booking (
Booking_ID            NUMBER(10)   NOT NULL PRIMARY KEY,
Company_ID            NUMBER(10)   NOT NULL REFERENCES
Penrhyn_Jet_Charter(Company_ID),
Customer_ID           NUMBER(10)   NOT NULL REFERENCES
Customer(Customer_ID),
Aircraft_ID           NUMBER(10)   NOT NULL REFERENCES
Aircraft(Aircraft_ID),
Assignment_No         NUMBER(10)   NOT NULL,
Booking_Date          DATE         DEFAULT SYSDATE,
Charter_Cost          NUMBER(14,2) CHECK(Charter_Cost > 0),
Departure_Date        DATE         NOT NULL,
Departure_Location    CHAR(3)      NOT NULL,
Arrival_Date          DATE         NOT NULL,
Arrival_Destination   CHAR(3)      NOT NULL
);

CREATE TABLE Aircraft_Model (
Aircraft_Model_ID     NUMBER(10)    NOT NULL PRIMARY KEY,
Aircraft_Model        VARCHAR2(255) NOT NULL,
No_Of_Aircrafts       NUMBER(10)    NOT NULL CHECK (No_Of_Aircrafts >
0),
Aircraft_Description  VARCHAR2(500)
);

CREATE TABLE Aircraft (
Aircraft_ID       NUMBER(10) NOT NULL PRIMARY KEY,
Company_ID        NUMBER(10) NOT NULL REFERENCES
Penrhyn_jet_Charter(Company_ID),
Aircraft_Model_ID NUMBER(10) NOT NULL REFERENCES
Aircraft_Model(Aircraft_Model_ID),
Seats             NUMBER(10) NOT NULL
);


texts: PostgreSQL newbie - how to create objects in a database

texts:
I've just finished installing PostgreSQL and pgadmin3 on Ubuntu.  Using pgadmin, I've been able to create a new user called 'test' and also a new database with 'test' as the owner.
Now I'm trying to create tables using SQL statements... but I can't seem to be able to figure out how to do that in pgAdmin.  I don't' want to create each column individually using the GUI.
Is there any way I can run a SQL statement like:

create table test
(
  id serial primary key,
  name varchar(64),
  handler varchar(16),
  desc varchar(255)
);


texts: Is there any way to identify which SP return data

texts:
I have inherited a DB (SQL Server 2016) with almost 6000 SP and no naming conventions… I have to identify all SP that return data vs which are update or inserts or delete… a lot of the SP are complex, and can do update / insert and then return data…. Is there any way to identify which sp return data and which do not (IE are pure insert / delete / update)…. Besides visually eye balling each is there a quick method…

Thank you… In advance.


texts: How to implement many-to-many relationship with one table

texts:
I have a table:

CREATE DATABASE IF NOT EXISTS test;
USE test;

CREATE TABLE IF NOT EXISTS emp_list(
id INT UNSIGNED NOT NULL AUTO_INCREMENT primary key,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
job_position  VARCHAR(20) NOT NULL,
salary decimal(15,2) UNSIGNED,
);

INSERT INTO emp_list ( id, first_name, last_name, job_position, salary) VALUES ( null, 'Ivan', 'Noscov', 'Director', 69000);
INSERT INTO emp_list ( id, first_name, last_name, job_position, salary) VALUES ( null, 'Igor', 'Malov', 'Accountant', 54000);
INSERT INTO emp_list ( id, first_name, last_name, job_position, salary) VALUES ( null, 'Ira', 'Solnceva', 'Salesperson', 27000);
INSERT INTO emp_list ( id, first_name, last_name, job_position, salary) VALUES ( null, 'Ivan', 'Noscov', 'Security guard', 15000);
UPDATE employees.emp_list SET last_name = 'Varnaev' WHERE id=4;
INSERT INTO emp_list ( id, first_name, last_name, job_position, salary) VALUES ( null, 'Natasha', 'Bolshova', 'Salesperson', 27000);

I need update the database schematic so that it is possible to store the information about
subordinates. No restrictions: each employee can have several subordinates (or not have them)
and may have more than one boss (or no boss at all). Request a sample of all
of a certain employee's subordinates.

Need to do this by adding a new column and need to use a foreign key to create relationships


texts: I currently am working with some data that is being stored as a CLOB between 4000  and 6000 characters. I would like to perform the TRANSLATE function on it but I learned that it's not possible with a CLOB. So my thought is to split the CLOB into multiple VARCHAR2's, and perform the TRANSLATE operation on each part. Say I have a 6000 character CLOB. I started by using SUBSTR(COL,4000,1) and storing the first 4000 in a variable. However, I'm having trouble getting the characters between 4000 - 6000. How can I go about splitting this CLOB?


texts: mysql reverse int value using generate column

texts: How to reverse the value of generate colum in mysql 5.7 ? For exampe, if the source column's value is -1, the generate column value is 1, if the source column's value is 1, the generate column value is -1, what should I do to archive this?


texts: Strange MySQL database that I can't select

texts:
I am investigating a web site server (CentOS release 6.3) that runs the traditional lamp stack;

I am getting the following output my MySQL;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| #mysql50#.config   |
| webtestdb          |
| mysql              |
| test               |
| websitedb          |
+--------------------+
6 rows in set (0.00 sec)

What is "#mysql50#.config"? Also, I can't select it;

mysql> use #mysql50#.config;
    -> ;
ERROR:
USE must be followed by a database name
mysql> use \#mysql50\#.config;
No connection. Trying to reconnect...
Connection id:    1707
Current database: *** NONE ***

ERROR 1049 (42000): Unknown database 'mysql50.config'
mysql> use `#mysql50\#.config`;
ERROR 1102 (42000): Incorrect database name '#mysql50#.config'

Does this resemble some sort of crashed database, or a temporary database from a previous import or export than has been left behind by mistake?

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.1.67-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | redhat-linux-gnu    |
+-------------------------+---------------------+
5 rows in set (0.00 sec)


texts: SPIDs stuck indefinitely in suspended state

texts:
I have a few application servers that all connect to a central database server. The applications execute stored procedures (that get XML) on the database.

Every once in awhile a transaction will get "stuck" and stop processing. The spid is in the suspended state with the wait type - ASYNC_NETWORK_IO. The last reads and writes in sys.dm_exec_connections are not current. Basically, it's just sitting there indefinitely. The stored procedure is using TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

Tops waits on the server are:

wait_type           wait_time_s     pct     running_pct
CXPACKET            16737173.38     56.60       56.60
ASYNC_NETWORK_IO    3628438.90      12.27       68.87

These "stuck" spids can block index rebuilding, specifically on a table that is being used in a select in the stored procedure. I have isolated this section in the store procedure.

Why does this spid just sit there if it's not doing anything? We are thinking that the application times out when processing large amounts of XML. If this is the case then is there anything I can do on the SQL side to close this connection or should this be handled on the application side?

The application developers are basically saying that it's a SQL issue because it's a stuck transaction.

The only way to get rid of these now is to set up a job that would check to see any non current spid and kill it or kill it manually. Neither is really an acceptable solution. How can I handle these SPIDs?

Running SQL Server 2008 R2 (SP2) - 10.50.4000.0


texts: Online index maintenance in SQL Server using SNAPSHOT isolation level

texts:
I have recently altered our SQL Server 2008 R2 database to enable SNAPSHOT isolation level and made appropriate changes to hibernate to run all transactions in SNAPSHOT mode. I have however noticed that when I add/delete/alter indexes, the queries which are accessing the underlying table are rolled back and I get this exception:

  Snapshot isolation transaction failed in database 'foo' because the
  object accessed by the statement has been modified by a DDL statement
  in another concurrent transaction since the start of this transaction.
  It is disallowed because the metadata is not versioned. A concurrent
  update to metadata can lead to inconsistency if mixed with snapshot
  isolation

...which is exactly what has happened.

According to https://msdn.microsoft.com/en-us/library/bb933783(v=sql.105).aspx "These statements are permitted when you are using snapshot isolation within implicit transactions." So I tried:

set implicit_transactions on
GO

DROP INDEX blabla
GO

IF @@TRANCOUNT > 0 COMMIT TRAN set implicit_transactions off

But the problem still persists. There must be a way to do this in SQL Server?


texts:
I'm trying to use Microsoft's SQL Server Migration Assistant for Sybase 5.3 ('SSMA') to migrate multiple Sybase databases (that have the same schema) to a single MSSQL database.

The first database migrated fine. But when I try to migrate data from the next one, I see lots of warnings like:

The table 'MyDatabase.dbo.MyTable' contains data.
If you continue with data migration, all data in the table will be deleted.

I don't want the existing data to be deleted, but can't find a way to do this in SSMA.


texts: More than 64 parameters to WHERE IN sentence makes MSSQL reading far too many rows


texts: Designing a multi-tenant database for a scenario with multiple user types

texts:
I'm developing a SaaS application for recruiting which has multiple user types such as Client, Recruiter, Panellist and Owner  etc. There is also a chance that the user types may increase.

The Owner has an Organization and this is what will be my tenant. Here I'm considering every owner's organization as a tenant. I'm using Django to develop this application and will be utilizing the schemas feature provided in the PostgreSQL, hence there will be a schema for every tenant.

What I'm planning to do is that there will be a global User table that will be kept in the public schema and stores generic user information such a first name, last name, email, password, etc. The User table will be used for logging into the site. And there will be additional tables for each of the respective user types that each have their own unique set of columns. These additional tables will be tenant/organization specific.

Now once the user logs into to site, he can then switch to his desired tenant/organization something similar to how workspaces function in Slack. Within the tenant/organization, the user can assume the role of the above-mentioned user types that were allotted by the Owner to the user. So the user can be a Recruiter in one tenant and he can also be a Panellist in another tenant.

There will be an UserOrganization junction table which will keep track of the fact that a User can belong to multiple Organization.

Here is a schema diagram of what I'm thinking:

NOTE: I did not make the schema connections for the tables in the second tenant because it was starting to get messy but for all intents and purposes assume that they are present over there.

My questions are:

What is exactly the best practice when dealing with multiple users in a multi-tenant scenario?
What are the deficiencies of my design?


texts: MongoDB | Problem inserting embedded documents into document

texts:
I am trying to insert data in a nested format.

A snippet of my models (there are many, many more):

class LifetimeMode(db.EmbeddedDocument):
    meta = {'collection': 'lifetime_mode'}
    career = db.EmbeddedDocumentField(GameModeData)
    war = db.EmbeddedDocumentField(GameModeData)
    sd = db.EmbeddedDocumentField(GameModeData)
    dom = db.EmbeddedDocumentField(GameModeData)

class LifetimeData(db.EmbeddedDocument):
    meta = {'collection': 'lifetime_data'}
    mode = db.EmbeddedDocumentField(LifetimeMode)

class UserData(db.EmbeddedDocument):
    meta = {'collection': 'user_data'}
    timestamp = db.DateTimeField(default=datetime.utcnow)
    type = db.StringField()
    level = db.IntField()
    lifetime = db.EmbeddedDocumentField(LifetimeData)

class User(db.Document):
    meta = {'collection': 'user'}
    public_id = db.UUIDField()
    gamer_id = db.StringField()
    user_data = db.EmbeddedDocumentField(UserData)

When I try to create my database with dummy data I am running into an issue I cannot solve. I grab my data from an external API.

lt_mode_data = data['lifetime']['mode'] # << This is the API returned data
for mode, mode_data in lt_mode_data.items():
    game_mode_data = GameModeData()
    lifetime_mode = LifetimeMode()
    json_data = json.dumps(mode_data['properties'])
    lifetime_mode[mode] = game_mode_data.from_json(json_data)

The problem is that only the final mode is added, as if each modes data gets added but is then overwritten by the next.

Any ideas?


texts: Is it ok for MySQL to always use 18-25% of Memory (I have 2 GB Ram)?

texts: I have MySQL 8 in my Server with Ram of 2GB. And MySQL is always using 18-25% of Ram even when everything is off. Is it normal? If not then plz let me know how to debug it.


texts: How can I count distinct columns inside a group?

texts:
I have the following query:

SELECT DISTINCT name, surname, id_number, gender FROM table1;

How do I filter by gender to get the gender breakdown of the results of the above query? Do I use a subquery? If so, how do I write the subquery?

Note in my table there are duplicate entries for id_number

Sample Data:

-------------------------------------
Name    | Surname   |ID_No   |Gender|
-------------------------------------
John       Doe        1234A6    M
Jane       Doe        2553B7    F
Steve      Jobs       2636A8    M

Expected Output

--------------
  M   |   F  |
---------------
  2       1


texts: Two different MYSQl instances on same machine?

texts:
I have MySQL 5.6 installed (Linux).

/var/lib/mysql
/etc/my.cnf

I have a cold backup that I am recovering:

/var/lib/mysql_backup
/etc/mybackup.cnf

In April I was able to get the backup started and it had been running fine until I had to reboot my machine 2 days ago to release an unmount that wouldn't succeed.

Now, I attempt to restart that second instance and I get an error that doesn't make sense given that it was working for nearly 6 months nonstop before and simultaneous/in parallel with the other instance.

root@VENUS:/var/lib/mysql_backup# mysqld --defaults-file=/etc/mysql/mybackup.cnf --pid-file=/var/run/mysqld/mysqld_backup.pid
2013-10-19 15:48:07 22026 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Can't find file: 'plugin' (errno: 2 - No such file or directory)
2013-10-19 15:48:07 22026 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2013-10-19 15:48:07 22026 [Note] InnoDB: The InnoDB memory heap is disabled
2013-10-19 15:48:07 22026 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-10-19 15:48:07 22026 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-10-19 15:48:07 22026 [Note] InnoDB: CPU does not support crc32 instructions
2013-10-19 15:48:07 22026 [Note] InnoDB: Using Linux native AIO
2013-10-19 15:48:07 22026 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-10-19 15:48:07 22026 [Note] InnoDB: Completed initialization of buffer pool
2013-10-19 15:48:07 22026 [Note] InnoDB: Highest supported file format is Barracuda.
2013-10-19 15:48:07 22026 [Note] InnoDB: 128 rollback segment(s) are active.
2013-10-19 15:48:07 22026 [Note] InnoDB: Waiting for purge to start
2013-10-19 15:48:07 22026 [Note] InnoDB: 1.2.10 started; log sequence number 1642375387
2013-10-19 15:48:07 22026 [Note] Recovering after a crash using /var/log/mysql_backup/mysql-bin
2013-10-19 15:48:07 22026 [Note] Starting crash recovery...
2013-10-19 15:48:07 22026 [Note] Crash recovery finished.
2013-10-19 15:48:07 22026 [Note] Server hostname (bind-address): '192.168.1.70'; port: 3310
2013-10-19 15:48:07 22026 [Note]   - '192.168.1.70' resolves to '192.168.1.70';
2013-10-19 15:48:07 22026 [Note] Server socket created on IP: '192.168.1.70'.
2013-10-19 15:48:07 22026 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: 'user' (errno: 2 - No such file or directory)

What am I missing here? Nothing has change from the first time it was running.


texts: How to select same number of data points over different time ranges

texts:
Grafana is querying our postgres database using this SELECT statement:

SELECT
  tstamp AS "time"
  dvalue AS "weight"
FROM tablename
WHERE
  description = "exp_weight" AND
  tstamp BETWEEN 'time1' AND 'time2'
ORDER BY
  tstamp

How can I code this such that I'm displaying the same number of data points even if my time intervals are different (ex. display 1,000 data points in the last hour or display 1,000 data points in the last month).


texts:
Using - Oracle 11g

I have created a table say X. of this table, a column say C3 is a computed column which will have a value based on incoming values for the other 2 columns say C1 and C2. I am using a BEFORE INSERT trigger for the same.

The table is partitioned by Range on the computed column C3 and this is a Date column.

Now, when I try to insert data into the computed column using a Trigger, the system dumps all data into the default partition rather than creating individual partitions. Instead, when I use the computation formula directly in my Insert query; the partitioning is fine and as expected.

Any idea?

Thanx


texts: Search and compare list of ever-growing IPs

texts:
I'm a newbie when it comes to mysql things and queries in general, just wanted to point that out.

I'll keep this as short as I can.

I have a table where I track the IPs of users that enter the front-page of a site. Any other page is not logged.

I have a unique ID for that table set as the IP of the client.

Every time a new user hits the front-page I have to look-up his IP in the table to make sure his IP is not there, if it's not it gets inserted, if it is, nothing happens.

So far this is working great, but then again, there aren't many IPs in there.

Since I'm using Laravel's Eloquent, I can't give you the precise query, in PHP it looks like this:

Tracker::where('ip', $client_ip)->first();

Although I'm not sure, I suspect that the raw query looks something like this:

SELECT ip FROM table_name WHERE column_client_ip = '127.0.0.1' LIMIT 1

The IP itself is saved as a varchar. I know about INET_ATON but I'm not exactly sure if I can use it with ipv6.

My questions are as follows:

I know that searching and comparing varchar type fields isn't the fastest thing in the world, do I have any other options to save IP? Keep in mind that they can also tunnel their IP which, in case I'm not wrong, it adds to the total length of the string I get as the IP - I could be way off here, my sysadmin skills are basically non-existent.
Currently the search is really fast, mostly because there aren't a lot of records and I also keep an index on the ip column. Realistically speaking, at what point should I start being concerned that the search/compare will slow down significantly?

If you guys have any other suggestions or want to warn be about possible issues I may have in the future with the current setup, I'm all ears.


texts: How to copy database from one server to another with script

texts:
So I have a script that almost does what I want but for one server.I need it to work "Cross-Server" ex.I have a database in one server and I need to pass it to other server and deleting if it exists on second server.

What I have now does almost the same but in one instance ->

USE master;

DECLARE
    @SourceDatabaseName AS SYSNAME = 'ABCBRAGA',
    @TargetDatabaseName AS SYSNAME = 'TEST'

-- ============================================
-- Define path where backup will be saved
-- ============================================
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = @SourceDatabaseName)
    RAISERROR ('Variable @SourceDatabaseName is not set correctly !', 20, 1) WITH LOG

DECLARE @SourceBackupFilePath varchar(2000)
SELECT @SourceBackupFilePath = BMF.physical_device_name
FROM
    msdb.dbo.backupset B
    JOIN msdb.dbo.backupmediafamily BMF ON B.media_set_id = BMF.media_set_id
WHERE B.database_name = @SourceDatabaseName
ORDER BY B.backup_finish_date DESC

SET @SourceBackupFilePath = REPLACE(@SourceBackupFilePath, '.bak', '_clone.bak')

-- ============================================
-- Backup source database
-- ============================================
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = 'BACKUP DATABASE @SourceDatabaseName TO DISK = ''@SourceBackupFilePath'''
SET @Sql = REPLACE(@Sql, '@SourceDatabaseName', @SourceDatabaseName)
SET @Sql = REPLACE(@Sql, '@SourceBackupFilePath', @SourceBackupFilePath)
SELECT 'Performing backup...', @Sql as ExecutedSql
EXEC (@Sql)

-- ============================================
-- Automatically compose database files (.mdf and .ldf) paths
-- ============================================
DECLARE
          @LogicalDataFileName as NVARCHAR(MAX)
        , @LogicalLogFileName as NVARCHAR(MAX)
        , @TargetDataFilePath as NVARCHAR(MAX)
        , @TargetLogFilePath as NVARCHAR(MAX)

SELECT
    @LogicalDataFileName = name,
    @TargetDataFilePath = SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))) + '\' + @TargetDatabaseName + '.mdf'
FROM sys.master_files
WHERE
    database_id = DB_ID(@SourceDatabaseName)
    AND type = 0            -- datafile file

SELECT
    @LogicalLogFileName = name,
    @TargetLogFilePath = SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))) + '\' + @TargetDatabaseName + '.ldf'
FROM sys.master_files
WHERE
    database_id = DB_ID(@SourceDatabaseName)
    AND type = 1            -- log file

-- ============================================
-- Restore target database
-- ============================================
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @TargetDatabaseName)
BEGIN
SET @Sql = 'DROP DATABASE ' + @TargetDatabaseName
EXEC (@Sql)
END

SET @Sql = 'RESTORE DATABASE @TargetDatabaseName
FROM DISK = ''@SourceBackupFilePath''
WITH MOVE ''@LogicalDataFileName'' TO ''@TargetDataFilePath'',
MOVE ''@LogicalLogFileName'' TO ''@TargetLogFilePath'''
SET @Sql = REPLACE(@Sql, '@TargetDatabaseName', @TargetDatabaseName)
SET @Sql = REPLACE(@Sql, '@SourceBackupFilePath', @SourceBackupFilePath)
SET @Sql = REPLACE(@Sql, '@LogicalDataFileName', @LogicalDataFileName)
SET @Sql = REPLACE(@Sql, '@TargetDataFilePath', @TargetDataFilePath)
SET @Sql = REPLACE(@Sql, '@LogicalLogFileName', @LogicalLogFileName)
SET @Sql = REPLACE(@Sql, '@TargetLogFilePath', @TargetLogFilePath)
SELECT 'Restoring...', @Sql as ExecutedSql
EXEC (@Sql)

What I need it to is to pass a database from one server to other.


texts: How to get data comparations from two mysql tables

texts:
What I have: The next structure:

Table ChrM

LID (PRIMARY with auto increment)
LOCUS (varchar (9)) Example value: (At1g30500) (Fixed Number of LOCUS values =173 rows)
StartPoint (INT) (Exclusive value assigned by locus)
EndPoint  (INT) (Exclusive value assigned by locus)

Actual table looks like the following

    | LID |   LOCUS      | StartPoint | EndPoint|
       1    AT1G30500        734         273
       2    AT3G15690        11415       8848
       3    AT3G05690        262900      195300
       .        .              .          .
      173   At5g01410        366700      264418

Table DMRs

idDMRs (Primary with auto increment)
StartPos (INT)(Exclusive value assigned by idDMRs)
EndPos (INT) (Exclusive value assigned by idDMRs)
Other...

Actual table looks like this

        | idDMRs |   StartPos  | EndPos | OTHER 1| OTHER 2 | OTHER n
           1         1003        716         .       .         .
           .           .          .          .       .         .
           156       197126      195371      .       .         .
           172       197299      196119      .       .         .
           201       197233      195621      .       .         .
           .           .           .         .       .         .
           20633     262857      262857      .       .         .

What I want : Select all values from table DMRs that fulfill the next condition,

EndPos >= EndPoint AND StartPos <= StartPoint

This for every LOCUS in table ChrM (Total of 173 times)  to get an (LOCUS, idDMRs, EndPos, StartPos, OTHER1, OTHer2, OTHER n) array.

As a newbie to Mysql I tried doing this one by one printing Table ChrM in paper and inserting LOCUS respective StartPoint and EndPoint by hand and querying individually LOCUS by LOCUS in the following manner:

SELECT * FROM DMRs
  WHERE EndPos >= 195300 AND StartPos <= 262900;

And the results are what I want, but I wonder if I will ever finish to analyze all my data when I have 50 different DMRs tables to analyze doing this 173 times for table.

Example of my query output

         |  idDMRs  | StartPos | EndPos | OTHER 1| OTHER 2 | OTHER n
             156      197126    195371      .       .         .
             172      197299    195419      .       .         .
             201      197233    195621      .       .         .

This is the result I want to get for every LOCUS

        | LOCUS |   idDMRs  | StartPos | EndPos | OTHER 1| OTHER 2 | OTHER n
         AT3G05690    156      197126    195371      .       .         .
         AT3G05690    172      197299    195419      .       .         .
         AT3G05690    201      197233    195621      .       .         .

So, in conclusion I would like help on how to resolve this problem or any advice on what I should read or know in order to resolve this on my own.


texts: How can I prevent SSMS from re-writing my code when saving a view?

texts:
I'm creating a view that uses a statement with a WHERE clause similar to this:

WHERE
    (
        col1 IS NOT NULL
        OR
        col2 IS NOT NULL
    )
    AND
    NOT EXISTS (SELECT ...)

which takes an average of 10 seconds to run.  However, when I try to save this query as a View, SQL Server (or the MS SQL Server Management Studio Client) "optimizes" the query to use this structure, instead:

WHERE
    (col1 IS NOT NULL AND NOT EXISTS (SELECT ...))
    OR
    (col2 IS NOT NULL AND NOT EXISTS (SELECT ...))

Slowing down the query to 6+ minutes.  Is there any way to disable this behavior, so that the view uses exactly the SQL Query I give it?


texts:
What would be the equivalent of the sqlite pragma below if I want to get the best performance out of postgresql.

pragma synchronous = OFF;
pragma journal_mode = OFF;
pragma count_changes = OFF;
pragma temp_store = MEMORY;


texts: MySQL - Truncated incorrect datetime value on update statement

texts:
I have an application which produces log files. These log files include a Timestamp field in the format 2015-08-25T09:35:01 UTC.
(there will be apx 60 logs produced at 0.25 GB per day).

I need to import these log files into MySQL for analysis. But I have a problem converting the Timestamp to DateTime.

example:

CREATE TABLE test1 (
  `TIMESTAMP` varchar(25) DEFAULT NULL,
  `EVENT_TIME` datetime DEFAULT NULL
);

INSERT INTO test1 (`TIMESTAMP`)
VALUES
('2015-08-25T09:35:01 UTC'),
('2015-08-25T09:36:01 UTC'),
('2015-08-25T09:37:01 UTC'),
('2015-08-25T09:38:01 UTC'),
('2015-08-25T09:39:01 UTC');

So far so good. I can now run a SELECT query to get the datetime

SELECT CAST(`TIMESTAMP` AS datetime) FROM test1;

But, if I try to update the table with the datetime format I get an error

UPDATE test1 SET `EVENT_TIME` = CAST(`TIMESTAMP` AS datetime);

Error Code: 1292. Truncated incorrect datetime value: '2015-08-25T09:35:01 UTC'

Is there a way to do this? as I really need the datetime field in the database, so I don't have to do the CAST every time I run a query.

I was also looking to partition the table by date, as there will be a lot of data produced, and so I only want to keep the minimum amount of data, and then drop the oldest partitions once I am done.


texts: Safe values for InnoDB variables when using FULLTEXT searches

texts:
I'm using Maria DB, version 10.2.22, where one database column uses FULLTEXT for a broad document searching. However, I've ran into a "Table handler out of memory" on some searches. The table itself is only 4.4 GB. I've read on stackoverflow, that changing some of the InnoDB variables such as:
•   innodb_buffer_pool_size
•   innodb_ft_result_cache_limit
from their default value to say 4 GB could potential solve to the memory issue. My question is three parts, I suppose.

Are there any other variables I should consider changing.

•   innodb_buffer_pool_instances
•   innodb_ft_cache_size
•   innodb_ft_total_cache_size

Because this DB is fairly critical to run, after running the command lines to change the variables would I need to stop and start the MariaDB service to fetch these changes?

If, restarting MariaDB services is needed, can anyone point me to a guide as far as how to safely change these variables?


texts: Is Database Backup Time = Restore Time in SQL Server?

texts:
I have a SQL Server Database with 10 GB size. When I performed a backup, it took almost 3-4 minutes. When I tried to restore, it took almost the same 3-4 minutes.

Does this mean backup and restore times of a SQL Server database are always equal, or can they differ?

Kindly suggest and point me the logic behind.


texts: Implementing something similar to "pointer arrays" with MySQL

texts:
While building a site I came across the following issue:

I want to be able to have multiple containers and within each container have ids pointing to multiple items that lie on another table. Scheme:

I am using MySQL and would like to stick with it. However I am open to other ideas.

My questions are:

Would this be a reasonable data structure, or is there any better way to organize this data?
If So, How would I go about implementing this idea of "array of pointers" to elements.

Possible Solution: http://tutsbook.com/mysql-foreign-keys/


texts:
I get an LDAP feed in for my employee database - basically a big csv file.  This updates once a night and I revise all of our internal apps based on this spreadsheet.

When a new person comes on the feed I have a trigger in my table that fires and spits out a "created" field so that I can run queries based on when the employee row entered our system.

My question is, is there a way to trigger when nothing is queried?  I would like to date (time) stamp a user row based on that row no longer being on the feed.  But also I don't want that overwritten every night since I would like my "left_date" field to be accurate.


texts: Prevent SQL Server failback after cluster aware updating

texts:
We have SQL Server 2012 in a two-node cluster with Windows Server 2012 (node and disk majority).  Neither of the nodes are marked as preferred owner and the "Prevent Failback" option is selected.  Here is the problem we are having:

Node A current host, Node B inactive node.
Node B is updated and the process fails the cluster over from A to B
Node A is updated
After node A is back on line, the process fails over the SQL Server cluster back to Node A. However, the Windows Cluster is still on Node B.

We have prevented this by setting the inactive Node to preferred owner before the updating process begins, but that entails manually setting it every time we want to update.

Does anyone know how to prevent the failback to the pre-update active node?


texts: PgAdmin III -- how to merge output pane and query pane

texts:
Somehow, I clicked on something the wrong way and popped the output pane out into a separate window from the query pane. I've tried restarting my computer, restarting PgAdmin III, but nothing fixes it. It's really annoying, and very hard to work with if I have more than one query window open.

Hopefully the image shows what I'm talking about.

What can I do to pop the output pane back into the bottom of the query pane?


texts: A table with all fields NULLable with important, sensitive data

texts:
I have a requirement for a database table to hold sensitive patient information about their lifestyle (alcohol/ drug use / injecting etc).

On screen the information is sectioned into tabs - "alcohol", "drugs", "injecting" and so on. Some of the data is co-dependent (ie: data in one tab may depend on what is input in another).

I was considering storing all of the data captured in the tabs (circa 25 fields - maybe more, but not much more) in a single table, because of the aforementioned co-dependency. All of the fields in the table would be NULLable (edit: for two reasons: (1) because people might have alcohol problems but not drug problems and vice versa and (2) the data can be in DRAFT or PRODUCTION state - in draft incomplete data is acceptable, but in production relevant data must be supplied)

To improve data integrity I would employ CHECK constraints to ensure that data was supplied for sets of fields in the table IF certain BIT flags were set (e.g. we are in PRODUCTION mode and a flag named HasUsedAlcohol is set, this mandates all alcohol related fields be populated; HasUsedDrugs would mandate all drug related fields be populated)

Additionally this table may be used to produce reports.

My question is - is this a sensible, maintainable solution? And if not, why not?

Database target is SQL Server 2012.

Thanks in advance.


texts: How to perform count(), count(distinct) in the same output using POSTGRESQL

texts:
I am trying to perform both count(distinct(keyword)) and count(keyword) from same tables. Here are the two queries.

Query-1

SELECT t1.count(distinct(keyword)) from keyword t1, sent t2,tweets t3,users t4
WHERE t4.user_id='18839785'
AND t1.id=t2.id AND t2.id=t3.id AND t3.user_id=t4.user_id AND t2.sent='Pos';

count
-------
251
(1 row)

Query-2

SELECT t1.keyword,t1.count(keyword) FROM keyword t1, sent t2,tweets t3,users t4
WHERE t4.user_id='18839785' AND t3.id=t4.user_id AND t2.id=t3.id
AND t2.id=t1.id AND t2.sent='Pos' GROUP BY t1.keyword order by count(keyword) desc ;

 nlp_keyword_name | count
 ------------------+-------
 happy            |     5
 people           |     4
 handsome         |     4
 glad             |     3
 forward          |     3
 wishes           |     3
 proud            |     2
 (7 rows)

Query-3

SELECT distinct_drivers,positive_drivers,countt
from (

SELECT t1.count(distinct(keyword)) as distinct_drivers ,
t1.nlp_keyword_name as P_drivers,
t1.count(nlp_keyword_name) as countt
FROM keyword t1 LEFT JOIN sent t2 on t1.id=t2.id
LEFT JOIN tweets t3 on t3.id=t2.id
LEFT JOIN users t4 on t4.user_id=t3.user_id
WHERE (t4.user_id)=('18839785') AND t2.sent='Pos' GROUP BY t1.keyword order by countt desc) T;

But I am getting my output as

distinct_drivers | positive_drivers | countt
------------------+------------------+--------
                1 | happy            |      5
                1 | people           |      4
                1 | handsome         |      4
                1 | glad             |      3
                1 | forward          |      3

Here after executing query-3 my output has to be below way which I am not getting like that.

 **desired_output:**

 distinct_drivers | positive_drivers | countt
------------------+------------------+--------
              251 | happy            |      5
              251 | people           |      4
              251 | handsome         |      4
              251 | glad             |      3
              251 | forward          |      3

Can anyone help me out in achieving desired output. I will be thankful.
resources I have gone through Rolling sum / count / average over date interval


texts:
I am using this query to get information on an index on a table. I can see the index in SSMS.
select object_name(object_id), *
from sys.dm_db_index_usage_stats
where database_id =  DB_ID() and  object_name(object_id) = 'MyTableWithoutSchemaName';

But the result set is empty? How come?
Same thing happens if I use some of the queries from here: https://www.sqlshack.com/how-to-identify-and-monitor-unused-indexes-in-sql-server/


texts: Separation of users columns in another table

texts:
I've got a quite a lot of columns on the users table and I have been cleaning it up recently. Now since I'm searching over the users I figured it would improve performance if I separated verification columns from user. I'm talking about these kind of columns :

verified_telephone_number
verified_telephone_by_id
verified_photo_by_id
verified_photo_source
verified_photo
verified_photo_at
video_call_requested
skip_video

So I was thinking to create verifications table with each user having one verification and verification having these above attributes and some more.

Is there a downside to this approach? I mean for one, in terms of count, there will be verifications rows = users rows(count). Instead of having these extra columns.


texts: Using Microsoft technologies/products is it possible to create a column store database with column family sets, where each row being able to have a different number and type of columns (a different column family).


texts: Postgres DDL and DML best practices and coding standards

texts:
Is there a best practice or coding/design conventions for Postgres both DDL and DML/SQL that you could share and recommend.

I am looking for something similar to what Google has for programming.

Thank you in advance.


texts:
What is the best approach to mapping between two tables with many to many relationship? Is it best to create third table with id of each table as foreign key ?
For example student table and course table each student have many courses.


texts: Bidirectional grant between rols for Postgres database

texts:
If I have 2 roles, role_a and role_b.

If I grant all from a to b with:

grant role_a to role_b;

Then any table, schema, etc created by role_a can be queried by role_b.

But if I create a table/schema/... with role_b, then role_a cannot query it...

But I cannot for the life of me figure out how to configure the roles. Basically I have 3 roles:

role_dev
role_app
role_deployment

Dev only exists in CD environment and sometimes I connect and create stuff directly to test re-runable scripts. This role is rather open.

App cannot create tables/schemas but can read/write to everything that exists in that database.

Deploy can create all the tables/schemas.

At the moment I manage this by explicitly granting to the stuff I create, but it's a hassle.

PostgreSQL group roles

This is the closest I can find but it doesn't appear to work for me.


texts:
How can I fine tune MySQL full-text search for my information domain?

For example, I might have a keyword like js that I would like the search to recognize, but I don't think allow a minimum word length of 2 be a good idea.

Another scenario I have: I'd like to ignore 3 if that was searched, but would like it to be recognized if the search was css 3. Not all single-characters are insignificant, particularly when combined with another string.

Is there a way I can give MySQL a list of words I do not want it to exclude either through it's stop words list or minimum word length variable?


texts: How to get the consolidated ServiceID with the sum of File Size associated with over an year

texts:
Can anyone please help me on below to get the consolidated ServiceID with the sum of File Size associated with it over an year.

Complete Code (For reference)

If(OBJECT_ID('tempdb..#temp1') Is Not Null)
Begin
    Drop Table #Temp1
End

GO

If(OBJECT_ID('tempdb..#temp2') Is Not Null)
Begin
    Drop Table #Temp2
End
GO

If(OBJECT_ID('tempdb..#temp3') Is Not Null)
Begin
    Drop Table #Temp3
End

GO

If(OBJECT_ID('tempdb..#temp4') Is Not Null)
Begin
    Drop Table #Temp4
End
GO

SELECT *
INTO #Temp1
FROM
(
SELECT
SUBSTRING(ServiceName,2,3) AS ServiceID
      , FileTotalSize as FileSize
      ,year = '1980' from table1
    ) a

--1981
SELECT *
INTO #Temp2
FROM
(
SELECT
SUBSTRING(ServiceName,2,3) AS ServiceID
      , FileTotalSize as FileSize
      ,year = '1981'
        from table2
    ) b

--1982
SELECT *
INTO #Temp3
FROM
(
SELECT
SUBSTRING(ServiceName,2,3) AS ServiceID
      , FileTotalSize as FileSize
      ,year = '1982' from table3
) c

--1983

SELECT *
INTO #Temp4
FROM
(
SELECT
SUBSTRING(ServiceName,2,3) AS ServiceID
      , FileTotalSize as FileSize
      ,year = '1983' from table4
    ) d

select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize1 from #Temp1
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize2 from #Temp2
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize3 from #Temp3
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize4 from #Temp4

Below are the 4 queries existing in #temp tables I finally worked and their outputs:

select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize1 from #Temp1
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize2 from #Temp2
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize3 from #Temp3
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize4 from #Temp4

Output of the above 4 queries:

My Question : How can I get the below output using the above 4 queries ? Please help me , the way I need to join . I tried my way to join them but sure it is wrong so reached out for help.

Thank you


texts: Can I Improve the design of my Transaction Table?

texts:
I have a transaction table that logs credits and debits. The numbers are always int (-1 to usually 100). My issue is that Debits have a lot more data than credits, and it feels weird having this data in the same table.

Columns
ID |
ClientID |
CardCodeID |
ClientDeviceID |
ClientEmployeeID |
ProjectID |
PlantID |
MaterialID |
Amount |
CreatedAt

A credit only uses ClientID, Amount,MaterialID and CreatedAt, and a Debit uses All the columns. Is there a more effecient way to design this table?


texts:
I would like to know if there is a table where accesses to tables are saved, per user or per system.

I'm using both SQL Server 2005 and 2008.


texts:
Check out this query.  It's pretty simple (see the end of the post for table and index definitions, and a repro script):

SELECT MAX(Revision)
FROM dbo.TheOneders
WHERE Id = 1 AND 1 = (SELECT 1);

  Note: the "AND 1 = (SELECT 1) is just to keep this query from being auto-parameterized, which I felt like was confusing the issue - it actually gets the same plan with or without that clause though

And here's the plan (paste the plan link):

Since there is a "top 1" there, I was surprised to see the stream aggregate operator.  It doesn't seem necessary to me, since there is guaranteed to only be one row.

To test that theory, I tried out this logically equivalent query:

SELECT MAX(Revision)
FROM dbo.TheOneders
WHERE Id = 1
GROUP BY Id;

Here's the plan for that one (paste the plan link):

Sure enough, the group by plan is able to get by without the stream aggregate operator.

Notice that both queries read "backwards" from the end of the index and do a "top 1" to get the max revision.

What am I missing here?  Is the stream aggregate actually doing work in the first query, or should it be able to be eliminated (and it's just a limitation of the optimizer that it's not)?

By the way, I realize this is not an incredibly practical problem (both queries report 0 ms of CPU and elapsed time), I'm just curious about the internals / behavior being exhibited here.

Here's the setup code I ran before running the two queries above:

DROP TABLE IF EXISTS dbo.TheOneders;
GO

CREATE TABLE dbo.TheOneders
(
    Id INT NOT NULL,
    Revision SMALLINT NOT NULL,
    Something NVARCHAR(23),

    CONSTRAINT PK_TheOneders PRIMARY KEY NONCLUSTERED (Id, Revision)
);
GO

INSERT INTO dbo.TheOneders
    (Id, Revision, Something)
SELECT DISTINCT TOP 1000
    1, m.message_id, 'Do...'
FROM sys.messages m
ORDER BY m.message_id
OPTION (MAXDOP 1);

INSERT INTO dbo.TheOneders
    (Id, Revision, Something)
SELECT DISTINCT TOP 100
    2, m.message_id, 'Do that thing you do...'
FROM sys.messages m
ORDER BY m.message_id
OPTION (MAXDOP 1);
GO


texts:
Today we got a term DDBB. I am not sure whether it refer normal database or database driver in certain programming language.

Google search did not help me much in this. And I am sure it is not 'Dirty Dozen Brass Band'.


texts: DB2 error - SQL Statement too long or complex

texts:
Getting this error in a DB2 production system:

ERROR [HY000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0101 - SQL statement too long or complex.

The statement has a large number of OR statements in the WHERE clause, as in

...<sql statements and joins>
WHERE (a=x1 and b=y1)
  OR (a=x2 and b=y2)
  OR (a=x3 and b=y3)
... <4000 times>

I am thinking that I need to scale back the batch size to something less that 4000 OR statements.  Any suggestions?


texts: Query to find GUID's "not in" null containing subquery returns no results

texts:
When I have a query that checks if a column of type uniqueidentifer does not exist in a table that has a null value then I get no results back. If the subquery does not return a null it works fine and it only happens when using not in.

I know I can just do a not null check in my subquery, but I am curious why this does not work.

Query Example:

select a.guid from tableA a where a.guid not in (select b.guid from tableB AS b)

Working Test:

select 1 where newid() not in (select newid())

Broken Test:

select 1 where newid() not in (select null)


texts:
I want to create an "immutable" Postgres database, where users can insert/select (write/read) data only, but can not update/delete (change/delete).

I came to know there is FOR UPDATE lock, but did not get how to use it.

Let's say for example I've the below table, how can I make it immutable (or, if I understood correctly, how can I use FOR UPDATE lock permanently)?

CREATE TABLE account(
 user_id serial PRIMARY KEY,
 username VARCHAR (50) UNIQUE NOT NULL,
 password VARCHAR (50) NOT NULL,
 email VARCHAR (355) UNIQUE NOT NULL,
 created_on TIMESTAMP NOT NULL,
 last_login TIMESTAMP
);


texts: SQL Server 2008 and compatibility level 80

texts:
Is there any technical reason why an application certified on SQL Server 2000 should not work properly on SQL Server 2008 with compatibility level 80?

In other words would there be any possible data or functionality loss in attaching a 2000 database to a 2008 server, keeping the 80 level?


texts: What could lead to excesive PAGEIOLATCH_EX

texts:
I'm having some delays when insert into a table. The funny thing is that it doesn't always happen.
Basically it's part of a load test that I created to compare some new features. I run the store procedure for a bunch of lists and there are some lists that always get delayed taking 3 hours to end and other that always are executed in a acceptable time (10 minutes).
At this point, look at performance monitor and the disk is reading at 1mb/s, so there isn't any of high load on the disk.
Also using extended events, look at "waits", saw the main wait is PAGEIOLATCH_EX, but as I said, the disk is reading at low rate, so what happen here?
CREATE TABLE SubscriberByList (
    SubscriberId INT NOT NULL
    ,ListId INT NOT NULL
    ,AddedAt DATETIME NOT NULL DEFAULT GETUTCDATE()
    ,Active BIT DEFAULT 1
    ,CONSTRAINT [PK_SubscriberByList] PRIMARY KEY CLUSTERED (ListId, SubscriberId)
    );

This table has 2.381.451.668 rows. A non clustered index on SubscriberId. SubscriberId and ListId are FK. Also a indexed view to count the amount of subscribers by each list.
On my test environment I created a procedure as following.
CREATE PROCEDURE [dbo].[LoadTest] @ListId INT
AS
BEGIN
    CREATE TABLE #Subscribers (SubscriberId INT, Active BIT, HashKey as convert(binary(32),HASHBYTES('SHA2_256',convert(varchar,SubscriberId))) PERSISTED,PRIMARY KEY CLUSTERED(HashKey))

    INSERT INTO #Subscribers (SubscriberId, Active)
    SELECT SubscriberId, Active
    FROM dbo.SubscriberByList sxl
    where sxl.ListId = @ListId

    DELETE TOP (1000) dbo.SubscriberByList
    WHERE ListId = @ListId

    WHILE @@ROWCOUNT = 1000
    BEGIN
        DELETE TOP (1000) dbo.SubscriberByList
        WHERE ListId = @ListId
    END

    DECLARE @Offset INT = 0
        ,@Amount INT = 1000
    INSERT INTO dbo.SubscriberByList (SubscriberId, ListId, Active)
    SELECT SubscriberId, @ListId,Active
    FROM (SELECT SubscriberId, Active
        FROM #Subscribers
        ORDER BY HashKey
        OFFSET @Offset ROWS
        FETCH NEXT @Amount ROWS ONLY
        ) x

    WHILE @@ROWCOUNT = @Amount
    BEGIN
        SET @Offset = @Offset + @Amount

        INSERT INTO dbo.SubscriberByList (SubscriberId, ListId, Active)
        SELECT SubscriberId, @ListId,Active
        FROM (SELECT SubscriberId, Active
            FROM #Subscribers
            ORDER BY HashKey
            OFFSET @Offset ROWS
            FETCH NEXT @Amount ROWS ONLY
            ) x
    END

    DROP TABLE #Subscribers
END

The delay is on INSERT INTO dbo.SubscriberByList.
The HashKey is to get the subscribers distributed. As a new test I remove the HashKey and use as PK the SubscriberId. With this change the performance is improved but this isn't as real as distributed insertion and also isn't as expected because it takes 1 hour to complete.
What could happen since one list take 10 minutes to process 2.7 millions of rows and the other with similar amount takes 3 hours (1 hour on the best case)?
Thanks!
EDIT 1:
Thanks for your comments, now I realize that delay also happen on the delete!
here are some stats using the suggested Paul Randal script, taking 3 minutes on each test
Fast Insert:

Slow Insert:

Fast Delete:

Slow Delete:

So, on slow operations there are more number of reads/writes, but a small number of bytes. Right?
Also the delete is on the primary key, why take more time for the same amount of subscribers?
Why is that?


texts: How to create a synonym for a column in SQL Server?

texts:
The following is not working. I want to create a synonym on a column in SQL Server 2017. How would I go about doing this?

CREATE SYNONYM dbo.[ProductTest] for [dbo].[Product].[ProductId]
GO

select ProductTest from dbo.Product


texts:
I have a column with values saved as hexadecimal.

When I run this query:

select column from  table
where column like '%22639935-KCN%';

This is the output I get:

0x24000000008B010000000089FF32323633393933352D4B434E000000

I would like to turn the output into a varchar. This should give me a value resembling what is in the like statement.

My attempts so far have been:

SELECT CONVERT(VARCHAR(MAX),
       0x24000000008B010000000089FF32323633393933352D4B434E000000)

SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX),
       '0x24000000008B010000000089FF32323633393933352D4B434E000000', 2))

However, I get this outcome:

$

How can I convert this hexadecimal value into a varchar?


texts:
I have a SQL Server 2005 database that I backed up and restored onto a new server running SQL Server 2012. So far so good. This morning I had a really bad power outage and the new server went down. When the new server came back online the database had Recovery Pending status.

I put the database into single user mode, did a DBCC CHECKDB and then put the database back into multi user mode and the Recovery Pending status went away. When testing the backup jobs I noticed there was an issue with the full text catalog.

I tried to drop the catalog and recreate it, but it will not do it. It complains that it is not empty and that it has a full text index. I tried removing the index from the table that is using the full text catalog, but then I got a message saying the text catalog is empty, read only or offline.

What am I missing?

Note: The database is online, but the backup jobs keep failing. When I do a DBCC CHECKDB I get the following:

Msg 8996, Level 16, State 1, Line 1
IAM page (3:9) for object ID 983374968, index ID 1, partition ID 72057594124173312, alloc unit ID 72057594131513344 (type In-row data) controls pages in filegroup 0, that should be in filegroup 2.
Msg 2575, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (3:9) is pointed to by the next pointer of IAM page (0:0) in object ID 983374968, index ID 1, partition ID 72057594124173312, alloc unit ID 72057594131513344 (type In-row data), but it was not detected in the scan.
Msg 8968, Level 16, State 11, Line 1
Table error: IAM page (3:9) (object ID 983374968, index ID 1, partition ID 72057594124173312, alloc unit ID 72057594131513344 (type In-row data)) is out of the range of this database.
Msg 7965, Level 16, State 2, Line 1
Table error: Could not check object ID 983374968, index ID 1, partition ID 72057594124173312, alloc unit ID 72057594131513344 (type In-row data) due to invalid allocation (IAM) page(s).
CHECKDB found 2 allocation errors and 2 consistency errors in table 'sys.fulltext_avdl_1062555119' (object ID 983374968).
CHECKDB found 2 allocation errors and 2 consistency errors in database 'MY-DATABASE'.


texts: How to show all system privileges for all users in Oracle database

texts:
I need a query how to list all system privileges only (not the object ones) which are granted to each user in database. I need listed system privileges whatever they granted thru role or direct.
Thank you


texts: SSMS 2012 / 2014 / 2016 Object Explorer hangs when Expanding Databases list on a server with many DBs

texts:
When accessing SQL Server with SSMS versions (2012/2014 and 2016), opening the databases list from the object explorer is really slow.
If I try it using SSMS 2008 R2 on the same instance the result is => instant display.

Note : The account is sysadmin, all databases have autoclose disabled, and I am not connecting to a remote server. It's on my local machine!

I noticed that there is a small difference comparing the queries generated from SSMS 2008 and 2012/2014/2016 when opening the databases list.

All of them are using has_dbaccess function, except SSMS 2008.

For instance:

SELECT
dtb.name AS [Name],
dtb.database_id AS [ID],
CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible]
FROM
master.sys.databases AS dtb
ORDER BY
[Name] ASC

There is a huge performance difference if I remove the function.
I guess they should add a condition in the begging of the query where in case it's a sysadmin account, just don't check permissions in every single database.
Does it make any sense?


texts:
I will need to expand my existing cluster drive (sql cluster with 2 node active and passive) which has sys and user database data files. My san admin told he will need to delete all the existing data and expand the size which is above 2 tb. I can detach all the system databases and user databases, move it to a temp storage and copy back once the space is added. or I can remove everything and restore all the system and user database from a full back up.
Just wanted an opinion about which process is the safest and wont break the cluster


texts: Why does Integer Based SQL Injection still require single quote in the parameter (')?

texts:
This is the source code of Damn Vulnerable Web Application (DVWA).
nl /var/www/dvwa/vulnerabilities/sqli/source/low.php
 7      $id = $_GET['id'];
 8
 9      $getid = "SELECT first_name, last_name FROM users WHERE user_id = '$id'";

mysql
mysql> DESC users;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| user_id    | int(6)      | NO   | PRI | 0       |       |
| first_name | varchar(15) | YES  |     | NULL    |       |
| last_name  | varchar(15) | YES  |     | NULL    |       |
| user       | varchar(15) | YES  |     | NULL    |       |
| password   | varchar(32) | YES  |     | NULL    |       |
| avatar     | varchar(70) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql>

The user_id or id in users table is actually an integer type. So, this is an Integer based SQL Injection.
Based on Joe McCray presentation in Def Con on page 23, ' not required for Integer based injection.
However, when I tested it on DVWA without ', I did not get Unknown column '100' in 'order clause' message.
http://127.0.0.1/dvwa/vulnerabilities/sqli/?id=1 ORDER BY 100-- &Submit=Submit#

Output (No error)
ID: 1 ORDER BY 100--
First name: admin
Surname: admin

Then, I decided to test it with ' and it worked.
http://127.0.0.1/dvwa/vulnerabilities/sqli/?id=1' ORDER BY 100-- &Submit=Submit#

Error Message
Unknown column '100' in 'order clause'

Didn't ' not required in this example (integer based injection)?


texts: Why storing product fees in another table is preferred?

texts:
MySql:
What is the benefit of storing several types of fees in another table like

Product Table -> (productid...),
ProductFee Table -> (productid, feetypeid, feeamount),
ProductFeeType -> (feetypeid, feetitle)

over

Product Table -> (productid, feeXamount, feeYamount, feeZamount) ?

The former would require too many joins and when creating/updating the prouct page, it would require too many inserts/update. What would be the benefit of such structure? The former seems to be preferred approach. Obviously, if the number of types of fee amounts start to go crazy like 40-50, it's best to follow the latter approach. However, if it's only 5-10 such columns, database-size is trivial, and especially when you're not writing a generic CMS like WordPress but a custom-tailored CMS for a specific shop?

Granted: The fees are optional but that's what null's are for, right?


texts: Meaning of SQLIO output "counts per second"

texts:
I realise that my question isn't strictly 'database' but I know that a lot of DBA's will use the SQLIO tool to check I/O performance on their systems so I'm hoping that somebody here will be able to answer this question:

When I run SQLIO the 3rd line of output says something like:

using system counter for latency timings, 14318180 counts per second

What does that mean?  I get different values on different systems, and because I'm comparing different systems I would like to know what that number is telling me (if anything).


texts: Postgresql: does effective_cache_size includes shared_buffers?

texts:
I am tuning a PG installation with 32GB of RAM.
The question is: which one of the following sentences is true?

effective_cache_size + shared_buffers ~= 30GB (PG knows that it has shared buffer plus the effective_cache_size)

effective_cache_size ~= 30GB (effective_cache_size includes shared_buffers)


texts: Execution of a Java program by a trigger.

texts:
I want to make an update trigger execute a Java program.

I have a table T1 with a column named Flag.  Whenever Flag changes, I want to run a trigger that results in an execution of a Java program.

Is this possible?


texts: MariaDB database corruption in MediaWiki install

texts:
I'm having an issue with a MariaDB database on a MediaWiki installation on my personal computer (local, personal use). A few days ago I booted it up and tried to access the wiki, and got the following database error:
[645bcb17b6a5a7b99a921100] /dndwiki/index.php/Main_Page Wikimedia\Rdbms\DBQueryError from line 1603 of D:\wamp64\www\dndwiki\includes\libs\rdbms\database\Database.php: A database query error has occurred. Did you forget to run your application's database schema updater after upgrading?
Query: SELECT user_id,user_name,user_real_name,user_email,user_touched,user_token,user_email_authenticated,user_email_token,user_email_token_expires,user_registration,user_editcount,user_actor.actor_id FROM `user` JOIN `actor` `user_actor` ON ((user_actor.actor_user = user_id)) WHERE user_id = '1' LIMIT 1
Function: User::loadFromDatabase
Error: 1932 Table 'dndwiki.user' doesn't exist in engine (127.0.0.1)

I accessed the wiki's database through phpMyAdmin, and found that 7 of the tables aren't accessible. They appear in the list, but give the error "#1932 Table '(name)' doesn't exist in engine" when selected. They are also listed as "in use" in the list of tables, as shown in this image:
From what I've found online, this type of error can happen with databases using InnoDB when data is moved incorrectly from one place to another. I haven't moved anything around, but I'm thinking there may have been some sort of auto-update that broke something. Regardless, the issue seems to be the same: table id's of the individual tables not matching up with what the database is expecting.
The .frm and .ibd files for all of the tables are there, so I don't think the data has been lost, but I cannot access it from phpMyAdmin. Now, I did find this blog post that describes a way to recover data from an .ibd file, so I was thinking I may just be able to drop the tables and restore them that way. However, that post says that you need to know the CREATE statement for the tables you're replacing. Since these tables were all generated by MediaWiki when it was installed, I don't have easy access to that. I'm sure I can figure out some way to do it, but if there's an easier way to fix this that would definitely be preferred.
I'm running Windows 10, running the server through Apache (2.4.41) via WAMP (3.2.0 64-bit). MariaDB version is 10.4.10.
I'm very much not a database admin, so please let me know if there's any more information I can provide about the problem to help you answer.


texts: Database Backup from 2008 R2, Restore on 2012 Failover Cluster Instance

texts:
I have a production database server having sql server 2008r2. This is an old server and considering our load, we are moving to a better hardware configuration, Now the new environment is a cluster environment on Win Server 2012 with single installation of SQL SERVER 2012.

My understanding of failover cluster environment is that same instance of sql server is there on both nodes and they get data from a shared SAN. Primary node remains active while secondary node remains idle till the time the primary node fails. Once the primary node fails, windows brings the secondary node as active.

Now i want to restore backup of 2008r2 database to this new sql server 2012 environment. Does this failover cluster impose any restrictions on this backup restore or will it be a normal backup restore operation. Please share your thoughts.


texts: How to make DDL changes to large tables and cause minimum server and object contention?

texts:
I work with a lot of large tables (billions to tens of billions of records in each) in a database I recently inherited. I see a few clear DDL changes that would benefit the use-cases of the database but it's hard for me to implement them because the database can afford very minimal contention. (Essentially if a heavy query is running for more than a minute or two it has to be killed.)

Even during a maintenance window, these changes would be just way too long and would exceed my allocated time (at most 1 hour, since there aren't really any off hours).

Types of changes I want to make are create indexes, add persisted computed columns, create indexed views, and general index tuning. If there was a way to do any of these operations iteratively and pause between iterations then I could get away with the total time taking a while because at least I can allow other processes to run in between, instead of a backlog being built up.

The only idea I can think of is if I maintained a copy of the database on a separate server where I can make DDL changes, then re-point my applications to that server. Then update the first server with the DDL changes so it's in sync, and then the next time I need to make an update, I can repeat the process.

Edit: I'm on SQL Server 2016 Enterprise Edition.


texts: Should numeric identifiers like serial numbers be stored as integers?

texts:
Many of our databases store numeric tracking codes (like serial numbers) as integers. There is little chance of these codes every getting alphabetic characters, but it still seems like an incorrect data type since you would never be performing mathematical operations on them.

What is appropriate data type for these types of identifiers?


texts: select query on postgis not working for specific fields

texts:
So I have what seems to be a really weird problem.

I have imported a table from QGIS into a postgresql + postgis database.
I simply want to select a few columns from that, but I can't.

whats funny is that i can use the select query for the lowercase fields and it works, but not for the uppercase ones, such as LABELRANK. the error shows that such a column does not exist.

why is that so?


texts: SQL Query is getting progressively slower

texts:
We are experiencing a very weird problem when making consecutive query calls. Not sure what info is important, so I will give as much info as I can.
We have a webserver and database server through Rackspace.  They are running Windows Server and Sql Server 2008 r2 express version for the database. The problem only began to happen when we moved our database to a remote server.  There is no problem when both are on the same box.
Symptoms
When we make multiple SQL calls from code (PHP, ASP) to database server, the results will get returned at a slower, but consistent rate. The SQL call generally has to be the same call, and after the first two or three calls we see a slow down to 1 second, 3 seconds, 8 seconds, 25 seconds, timeout.  When it slows down, the times are very consistent(1,3,8,25,timeout).  And we can almost always cause the slow down, but it isn't always consistent.
It seems that a single selected result set is the hardest to reproduce the slowdown, but we can reproduce it.  Just not as consistently as a more complex call with multiple result sets.
What we know.

It's not a code problem, because we can reproduce using bare bones calls and from different languages.
It's not the sql, because we can reproduce with extremely simple calls on tables with 10 rows. And the rows are only small integers. But it also happens on big tables.
If we run procedures in SQL Management studio, there is never a problem. Only across the network.
It also doesn't matter if we call raw sql from code (PHP, ASP), or call stored procedures to get result sets.
We don't see any event log errors on the server, or at least we don't know where to look.

We're not sure what would cause this, or even where to continue our search. Any info would be great.
Edit
We added SSMS to another server, but there was no slow down to any of the queries.
The Sql Server version Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (Intel X86)   Mar 19 2015 13:34:46   Copyright (c) Microsoft Corporation  Express Edition with Advanced Services on Windows NT 6.0 <X86> (Build 6002: Service Pack 2) (Hypervisor)
Just to be clear, this is not a single query.  We are seeing this happen on a very wide variety of queries.  From selecting from a very simple table, with like 3 columns, 30 rows, and indexing. To a select with many joins across tables with 300,000 records. But when the slow down starts to happen, it happens consistently no matter how complex the sql.
Edit 2
The ARITHABORT ON did not solve the problem.
I've linked to one of the EXECUTION PLANS. This procedure probably causes the most consistent slow down.  I just added it to a jsfiddle, html section. I'm assuming that would be ok.
Edit 3
Just some more information.  The server is a dedicated server.
Also, we spun up a server on Amazon and made some calls to the database server(on Rackspace), and we had the same problem. Same slow down.
Update
I believe the problem is/was with Rackspace servers.  We upgraded to a General Purpose server from a Standard.  With 4x more CPU's, 2x more RAM, and 4x more bandwidth. But when we point to our old Standard server the slowdown issue goes away. Thanks for everyone's help.


texts: SQL Server Configuration Manager is not showing SQL Services

texts:
I have newly installed following things on my system

     - Microsoft SQL Server Management Studio 2014
     - SQL Server 2017
     - Visual Studio 2015

When I tried to open SSMS for the first time, it asked me for the "Server Name" when I surfed I came to know that I have to check the instance name in Configuration manager-->Sql Services, but I couldn't find any services running in Configuration Manager, it is showing "There are no items to show"

Secondly, I also tried giving the permissions to my SQL Server services as mentioned in article here,  but couldn't solve my issue.

Also I tried to check find the SQL Server instance name using the sqlcmd -L command, but it didn't show any servers, even though SQL Server is already installed.

What am I doing wrong?


texts: MySql Galera Multi site high availability

texts:
We have a datacenter with 3 percona glera nodes

We have a store that wants to be in sync with our datacenter(multi master)

our store's internet connection is not reliable ( may be down for 30 min), it is adsl , then
we decided to get two internet connections from two different ISPs with static ip address

Questions are:

Is it possible for a single galera node to have multiple public ips?

2.When both internet connections go down. Local galera node will be
    unavailable too , How can we can configure it to avoid this?

3.Do you have any recommendation
    or better solution in this regard?


texts: Can We Perform an "Up-To-The-Minute Restore" After Taking Transaction Log Backups?

texts:
The Setup:

Say we have a Database in Full Recovery Mode that does a nightly Database Backup, Transaction Log Backup, and Transaction Log Shrink.

Now the Transaction Log grows so quickly during the day, so we need to perform a Transaction Log Backup + Transaction Log Shrink every 6 hours to keep it small.

The Question:

Given these sequence of events (specifically the every-six-hours Transaction Log Backup/Shrink), will we still be able to perform an "Up-to-the-Minute" Restore? (That is, using the last DB Backup + Transaction Log to get the most recent data).


texts: How do I create a simple SELECT statement as a stored procedure in PL/SQL? - Regardless of the best practises around Stored Procs

texts:
Was wondering how to create a simple select statement to return all records within a table in PL/SQL such as

CREATE OR REPLACE PROCEDURE getStudents
AS
C1 SYS_REFCURSOR;
BEGIN
OPEN C1 FOR
SELECT * FROM STUDENTS
DBMS_SQL.RETURN_RESULT(C1);
END;

I'm a beginner to this, Thanks in advance.


texts:
I'm trying to use an ETL (in my current case, the free version of Talend). I've successfully set up a MySQL on Amazon RDS, logged in through the MySQL Workbench for Mac, and uploaded my company's Salesforce Tables into the database, using a text file to create the fields/tables.

That has all worked out well. I've actually been using CDIR security records every time I log in, using my current IP address. I know its not perfect, but its been working so far.

The trouble arises when I try to use an ETL to transfer data in the form of a 'job'. I get this error, no matter what I try to do:

[statistics] connecting to socket on port 3700
[statistics] connected
Exception in component tMysqlOutput_1
java.sql.SQLException: Access denied for user '(Username)'@'c-(IPAddress).hsd1.pa.comcast.net' (using password: YES)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:919)
    at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:4004)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1284)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2312)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2122)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:774)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
[statistics] disconnected
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:375)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:289)
    at java.sql.DriverManager.getConnection(DriverManager.java:582)
    at java.sql.DriverManager.getConnection(DriverManager.java:185)
    at sqmigration.sfdcmigration_0_1.SFDCMigration.tSalesforceInput_1Process(SFDCMigration.java:2391)
    at sqmigration.sfdcmigration_0_1.SFDCMigration.runJobInTOS(SFDCMigration.java:4799)
    at sqmigration.sfdcmigration_0_1.SFDCMigration.main(SFDCMigration.java:4667)
Job SFDCMigration ended at 16:17 16/09/2012. [exit code=1]

Any Ideas what in the world is happening here? Is it because of my Users? I'm logging in as the master account, that I set through the AWS management portal. I tried creating new users using phpMyAdmin (running on my local machine, with MAMP, connected to the database), but I was getting errors there as well granting privs.

Sorry if this is remedial. Just trying to learn how this stuff works and help my company!

Thanks,

Charlie


texts:
I have a message table in Postgres 9.4 that contains a words field of array type, with random words of a message. Currently I have millions of messages:

\d messages
                        Table "public.messages"
            Column            |            Type             | Modifiers
------------------------------+-----------------------------+-----------
 id_str                       | character varying(255)      | not null
 feed_id                      | integer                     |
 message                      | character varying(255)      |
 posted_at                    | timestamp without time zone |
 words                        | character varying(255)[]    |
 Indexes:
    "messages_pkey" PRIMARY KEY, btree (id_str)
    "index_messages_on_feed_id" btree (feed_id)
    "index_messages_on_feed_id_posted_at" btree (feed_id, posted_at DESC NULLS LAST)
    "index_messages_on_words" gin (words)

Now I have an array of words [A, B, C].
I need to find the number of messages that the words contains each of the combinations of the list, i.e. [[A,B], [A,C], [B,C]].

Also I have this combination array created outside of the Postgres query already.

I can do this by creating 3 queries:

select count(*) from messages where words @> ARRAY['A','B']::varchar[];
select count(*) from messages where words @> ARRAY['A','C']::varchar[];
select count(*) from messages where words @> ARRAY['B','C']::varchar[];

Is there a way to do this in one query? And output the result in the following format?

A, B, count or [A, B], count
A, C, count
B, C, count

Edit:

Here's the query I run currently:

http://sqlfiddle.com/#!15/7c907/2/0

http://explain.depesz.com/s/Dot

And this one is using Erwin Brandstetter 's method:

http://sqlfiddle.com/#!15/7c907/1/0

http://explain.depesz.com/s/pr2

Now assume the messages table has more than 1 Million records, the CTE scan or seq scan in each of the query plan will become very slow.


texts:
SQL newbie here, trying to understand how tempdb work when there is more than one datafile.
To keep this simple, let's assume the server has three tempdb datafiles, each 10GB, totaling 30GB and are all configured not to auto grow. If a user query were large enough to require 25GB of tempdb, would it:
A: fail
B: fill the first datafile then the second and into the third
C: randomly spread the usage across all three datafiles
D: something else entirely? If so, what is that something else?

Any insight into this is greatly appreciated.


texts: Returning empty string when string_agg has no records

texts:
I am trying to return a text field in a PostgreSQL query that is of the form
'stringOne' || string_agg(field, ',') || 'stringTwo'

where for certain elements in the group clause, field is always null. I want, and expect, to end up with stringOnestringTwo in that case, but instead I get NULL.
Why is this, and how do I accomplish what I'm trying to do?
Example
Suppose I have the tables
foo                 bar
+----+--------+     +----+-------+--------------+
| id | name   |     | id | fooid | baz          |
+----+--------+     +----+-------+--------------+
|  1 | FooOne |     |  1 |     1 | FooOneBazOne |
|  2 | FooTwo |     |  2 |     1 | FooTwoBazTwo |
+----+--------+     +----+-------+--------------+

and I run the query
SELECT
  foo.name AS foo,
  'Bazzes: ' || string_agg(bar.baz, ', ') AS bazzes
FROM
  foo LEFT JOIN bar ON bar.fooid = foo.id
GROUP BY
  foo.name

Then I want (and expect) to get the resultset
+--------+------------------------------------+
| foo    | bazzes                             |
+--------+------------------------------------+
| FooOne | Bazzes: FooOneBazOne, FooOneBazTwo |
| FooTwo | Bazzes:                            |  <== NOT NULL
+--------+------------------------------------+

but instead, the second row is ('FooTwo', NULL). How can I modify this query so that the second row returns ('FooTwo', 'Bazzes: ')?