texts:
I have just switched from MySQL to MariaDB and am running into a very silly problem: I cannot enter any extended characters in the database (for example, ö ä or å).
The system is utf8, and I tried switching the keyboard to swedish — works everywhere except for mariadb prompt, and even tried copying and pasting from gucharmap — nothing is pasted at prompt.
Either method worked under MySQL in the past. I looked in MariaDB conf.d, the character_set_system was not set so I changed it to utf8, but that did not help. default-character-set in 50-client.cnf is set to utf8mb4. Any ideas?
System: debian bulseye/sid
Mariadb: Ver 15.1 Distrib 10.5.8-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper
These are the relevant mariadb variables:
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |

| character_set_results | utf8mb4 |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

| collation_connection | utf8mb4_general_ci |

| collation_database | utf8mb4_general_ci |

| collation_server | utf8mb4_general_ci |

| session_track_system_variables |
autocommit,character_set_client,character_set_connection,character_set_results,time_zone

show create table gives
ENGINE=InnoDB AUTO_INCREMENT=1625 DEFAULT CHARSET=utf8mb4

The rest are field definitions, and no collations are set.
Of course, there is a prompt: it is a standard mysql prompt.
I am not sure how to make it clearer. I am at mysql prompt. In the past, typing something like å worked fine, now it shows nothing. When I try to paste the character copied from another utility like gucharmap, nothing is pasted. When I paste "normal" characters (i.e. standard English text) everything works.
Note that the extended characters in database entries created BEFORE the switch show up just fine. Yes, it is all very mysterious.


texts: Using window function to carry forward first non-null value in a partition

texts:
Consider a table that records visits

create table visits (
  person varchar(10),
  ts timestamp,
  somevalue varchar(10)
)

Consider this example data (timestamp simplified as counter)

ts| person    |  somevalue
-------------------------
1 |  bob      |null
2 |  bob      |null
3 |  jim      |null
4 |  bob      |  A
5 |  bob      | null
6 |  bob      |  B
7 |  jim      |  X
8 |  jim      |  Y
9 |  jim      |  null

I'm trying to carry forward the last non-null somevalue of the person to all his future visits until that value changes (ie becomes the next non-null) value.

Expected result set looks like this:

ts|  person   | somevalue | carry-forward
-----------------------------------------------
1 |  bob      |null       |   null
2 |  bob      |null       |   null
3 |  jim      |null       |   null
4 |  bob      |  A        |    A
5 |  bob      | null      |    A
6 |  bob      |  B        |    B
7 |  jim      |  X        |    X
8 |  jim      |  Y        |    Y
9 |  jim      |  null     |    Y

My attempt looks like this:

 select *,
  first_value(somevalue) over (partition by person order by (somevalue is null), ts rows between UNBOUNDED PRECEDING AND current row  ) as carry_forward

 from visits
 order by ts

Note: the (somevalue is null) evaluates to 1 or 0 for the purposes of sorting so I can get the first non-null value in the partition.

The above doesn't give me the result I'm after.


texts: Database design import massive csv files is there a more efficient way?

texts:
I need to let users import huge csv files varied between 200k - 1.5m lines. For this I use LOAD DATA INFILE which is the fastest way to import those files. Thing is however that I can't check the user input, I have solved this by using a trigger on a table. My table structure is as following: (narrowed down to make it more readable):

tbl_debtor: debtor_id (AI), debtor

tbl_debtor_tmp: debtor

tbl_debtor_err: error

Now the (before insert) trigger is on tbl_debtor_tmp where users can literally import anything. In that trigger I check their user input, if okay I'll do a insert on tbl_debtor. If the user input is not okay I write to tbl_debtor_err. After the import is done I'll do a DELETE FROM tbl_debtor_tmp

My question here is, is there a more efficient way? I have about 20 of those table structures which I think is alot.


texts:
I was working with sql server databases for years and I am used to tools like sql server profiler for tracing all queries on particular database.

My question is - how to do this same thing with oracle database. I am using sql oracle developer version 19.2 and I don't see any option there for tracing DB.

I've also checked this question and tried several tools there (the question is quite old) with no success. Do I really need oracle enterprise manager for doing such a basic thing? (that is suggestion from one of the answers).

My application is written in .NET - why is it so hard in oracle to track queries that this app is triggering?


texts: pgBouncer works great but occasionally becomes unavailable

texts:
I'm running pgBouncer in front of a busy postgres 9 database.
For most of the time it works fine. But every few hours I'll get an error email from my application with an exception from psycopg2:

OperationalError('could not connect to server: Cannot assign requested address
Is the server running on host "neo-hulk" and accepting
TCP/IP connections on port 6432?')

This is a python app with a bunch of celery workers running tasks. When those errors arrive I check the pgbouncer db and the pool size is within limits.
After some experimentation I've set the pool max size to 400 and pool size to 200. pool mode is "session" (requests are mostly auto-commit, almost no transactions).

What makes pgBouncer 'vanish' like that? its only for short periods of time (and in total we're talking about a tiny amount of requests compared to the sheer volume of requests its handing) but those requests that fail are important.

Thanks!


texts: Find which numbers in [1, 161] are not in the result set?

texts:
How to find which two number in [1, 161] is not in the result set?

Can I find this two numbers using sql command?

mysql> select blog_id from wp_blogs;

...

|     149 |
|     150 |
|     151 |
|     152 |
|     153 |
|     154 |
|     155 |
|     156 |
|     157 |
|     158 |
|     159 |
|     160 |
|     161 |
+---------+
159 rows in set (0.25 sec)

The sequence is from 1 to 161, there should be 161 numbers, but only 159 numbers are in the resultset.

mysql> select count(blog_id) from wp_blogs;
+----------------+
| count(blog_id) |
+----------------+
|            159 |
+----------------+
1 row in set (0.24 sec)

How to find the missing numbers?


texts: How to generate DDL of an object which a synonym points to?

texts:
I want to generate the DDL of an object which a synonym points to. Consider the below example.

I have a synonym named USER which points to USER_1 table. I want to generate DDL of the USER_1 table using the synonym as input.

I need to generate for over 400 synonyms, hence searching for a method to retrieve DDL using synonyms.

Thanks in advance.


texts: Unable to set InnoDB buffer pool size >900Mb

texts:
On Windows server 2003 sp2 OS with 4 GB of RAM, running MySQL 5.6 it was previously running with 1000Mb but when I try to restart the service it was failing to start with this error:

InnoDB: Initializing buffer pool, size = 1000.0M InnoDB:
VirtualAlloc(1062912000 bytes) failed; Windows error 8

I have to reduce it to 900Mb. It doesn't even work with 901Mb, I am wondering what was stopping it running with the old configuration of 1000Mb.


texts:
Please help with following strange issue.I defined in memory table but insert of 600.000 rows takes 13 minutes and causes heavy write to disk. The CPU is underutilized and I have enough free memory. What's wrong and why I see the I/O?
1. I defined max_heap_table_size as 1GB and restarted the DB.
2. I created the table as:

CREATE TABLE `data`
     (
       `id`         bigint(20)       DEFAULT NULL,
       `datetime`   timestamp        DEFAULT CURRENT_TIMESTAMP,
       `channel`    int(11)          DEFAULT NULL,
       `value`      bigint(20)       DEFAULT NULL
     ) ENGINE = MEMORY;

I created the procedure to insert data.

DELIMITER $$
CREATE PROCEDURE generate_data()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 600001 DO
    INSERT INTO `data` (`id`, `datetime`,`value`,`channel`) VALUES (
      i,
      FROM_UNIXTIME(UNIX_TIMESTAMP('2014-01-01 01:00:00')+FLOOR(RAND()*31536000)),
      ROUND(RAND()*100,2),
      i
    );
    SET i = i + 1;
  END WHILE;
END$$


texts: Using ASPState database in the AG cluster with multiple teams using the same ASPState

texts:
We are migrating a number of the 2008 Windows clusters to the 2016 servers and consolidating databases in the same AlwaysOn cluster. Our dev teams are currently using ASPState databases specific to their application. As a rule it's only one ASPState database per server but we need it to be able to accommodate multiple Web applications.

Questions

Is it possible to use one ASPState with multiple Web applications? If it is, then how?
Should this ASPState database be a part of the AG group? If it's part of the one AG than other AG's won't be able use it.


texts: Copy data from multiple databases to a central database for reporting and analysis

texts:
I have more than 8 databases and all are in a SQL Server environment. I need to extract data from specific tables (more than 50 tables) of each database and dump into a central database for reporting and analysis purposes (a one way transfer).
Table structures are the same in all databases. Note that all my databases reside on the same server, except the central database, which is on a separate server. I have evaluated a couple of options. My current thinking and related questions are below:

SSIS
With SSIS I can do data transfer from the source to the destination databases; however, there is no change tracking. I have to track the changes myself and then extract and push the data manually. Apart from this I will end up creating a number of control flows and related data flows as the data sources are different which would be hard to manage in future.

Replication
Replication has change tracking, so the data transfer would be quick enough. However, I have to create those many publishers against each database. I am afraid server performance would be impacted due to this. I am also not sure which replication type to use, or if any performance issues could be handled with a better configuration. I am okay with a nightly update and data transfer need not be real time.

How should I proceed from here?


texts:
How to change the way that cols orders it self?

If we have a table like this

a        b        c
1        xx       xy
2        xy       yx
3        yy       xx

Since both of b and c are same type I would like to aggregate them in a one col call E respectively

a        E
1        xx
1        xy
2        xy
2        yx
3        yy
3        xx

Is that possible using MYSQL 5.7 ?


texts:
I am using MySQL database (with Django ORM). I want to maintain the database audits similar to StackOverflow, Quora, Wikipedia, etc. These websites maintain the revisions of the changes in the database so that the changes made by users/ admins can be reverted.

After going through the database design of StackOverflow and the  Quora revisions, I understood two ways of doing this -

StackOverflow

Create a duplicate table to keep the log of the changes made in the    database. For each entry record the changes,timestamp and admin/user    who made this changes. Use these entries to find diffs and revert to any point. SO keeps the history of revisions in a separate PostHistory table.

Quora

Instead of making separate tables for each table in the database, make a table like this for audits.-

  id - revision id
  scope_id - id of database table
  scope_type - Question, Topic, User
  item_id - row id of Question/topic/user in the database table
  event - Edited, Added, Reverted, Removed
  user_id - who triggered the event
  timestamp
  serialized_item_column - serialized data in the json format

Then the serialized data can be used to calculate diffs and revert a particular entry.

In context of crowdsourcing platform like wiki/SO where multiple users/admins can make changes,

Which of the two database design will be better ?
If I use one duplicate table per table for revisions, i.e. one for the current value and one with all the previous revisions,  is that a scalable way for a website with millions of entries and much more revisions ?


texts: Enable SSMS in SQL Server 2005 Developer Edition

texts:
We just had an upgrade of all our computers here, and on one of them we loaded our licensed copy of Visual Studio 2008 and SQL Server 2005 Developer edition.  I then loaded the service packs (4 for SS).  There is still no icon for SSMS in the Start Menu.

How do I enable SSMS in this version of SQL Server?

(OS: Windows 7 64-bit)


texts: SQL Server Agent runs a job on one server, not on the other

texts:
I have two SQL servers, each running on their own Windows server. The versions of both Windows and SQL Server are the same, they are on the same domain, they're virtually identical.

On these servers, I have set up credentials for a domain service account (sa_abc), I've added sa_abc as a CmdExec proxy in the SQL Agent, I've created a job with one step (a CmdExec step with time /t > d:\temp.txt ).

On one server this job runs fine and the result is what I'd expect: a txt file on the root of the D; drive with the time of running printed in it. On the second server this job fails with the error:

  Unable to start execution of step 1 (reason: Error authenticating proxy Domain\sa_abc, system error: The user name or password is incorrect.).  The step failed.

I've validated that the domain account is indeed valid; I have a Powershell script that takes a username/password and validates it against the Active Directory. This seems to rule out any conflict with the DC.


texts:
In short: I can connect to the database but the querys return nothing.
Background: Trying to install a Confluence plugin and make it work. Confluence (installed at server SAS) is supposed to access PostgreSQL (installed at server LDB) and fetch data. It throws the error message:

An error occurred while retrieving the data for this page, therefore the list is empty. Please refer to the logs for more information: com.playsql.dialect.api.JdbcException: Couldn't open the connection Connections could not be acquired from the underlying database! Query:null".

Troubleshooting points:

I can connect to LDB via Telnet, from SAS as well as my own computer.
I can connect to PostgreSQL@LDB from my own computer via psql. It seems to work: no error message in the psql client and there is a line that corresponds to the connection in pg_monitor.
Querys in psql does not work. I've tried INSERT INTO roles (name, description) VALUES ("BC", "Business Consultant") which to my understanding didn't do anything. (I already created the roles table through pgAdmin.) SELECT [asterisk] FROM roles returned nothing, the table statistics says "tuples updated 0" (but "sequential scans 2", does that refer to the insert and select?). Nothing in postgresql-2020-09-02_000000.log.

I feel I should AT THE VERY LEAST be able to connect and send queries from my own computer through a regular psql connection, but apparently not. Help is deeply appreciated!


texts: How to display MS SQL results vertically?

texts:
Here is what I've got currently when executing queries in sqlcmd:

1> SELECT 1,2,3,4
2> GO

----------- ----------- ----------- -----------
          1           2           3           4

(1 rows affected)

Although in case of longer results this output format is not human-readable, as it gets wrapped in the terminal.

How do I display the results vertically (an equivalent of \G in mysql)? Or in another way which can be human-readable?


texts:
I had a problem recently with oraenv and oratab and finding out where oraenv is located was a bit challenging.

Since it's a question I ask myself often, I wanted to have it here. It may help others.


texts: Percona Audit Log plugin not auditing account

texts:
I have setup the Percona-server-5.6.41-89 Audit plugin with the following settings:

audit_log_policy=QUERIES
audit_log_handler=FILE
audit_log_file=/var/log/mysql/perc_audit.txt
audit_log_format=CSV
audit_log_rotate_on_size=1000000000
audit_log_rotations=9
audit_log_include_accounts=someuser@%

Now for some reason, auditing is not being performed against the configured account. I tried SET GLOBAL audit_log_include_accounts="'someuser'@'%'";, but with no success.

How do I need to set up the audit_log_include_accounts parameter so that it contains wildcards in the host part?


texts:
I've got 2 triggers. triggerA and triggerB that acts upon changes on tableA and tableB respectively. Inside both those triggers I want to use the same function that uses the OLD & NEW variables. I don't know how to do so because it throws me the error:

OLD hasn't been assigned yet.
The tuple structure of a not-yet-assigned record is indeterminate.

Then I tried assigning null to a variable to get around that and it's:

old_v hasn't been assigned yet.

trigger 1 & 2 (this part is common in both triggers):

DECLARE
    added added_scores;
    old_v record;
    new_v record;
BEGIN
    CASE TG_OP
        WHEN 'INSERT' THEN
            old_v = null;
            new_v = NEW;
        WHEN 'UPDATE' THEN
            old_v = OLD;
            new_v = NEW;
        WHEN 'DELETE' THEN
            old_v = OLD;
            new_v = null;
    END CASE;

    select _i_compute_added_scores(TG_OP, old_v, new_v) into added;
        -- some computation, return result

function :

CREATE OR REPLACE FUNCTION public._i_compute_added_scores(
    tg_op_p text,
    old_p anyelement,
    new_p anyelement)
  RETURNS added_scores AS
$BODY$

DECLARE
    new_score integer;
    old_score integer;
    res added_scores;
BEGIN
    CASE TG_OP_p
        WHEN 'INSERT' THEN
            new_score := new_p.score;
            old_score := 0;
        WHEN 'DELETE' THEN
            new_score := 0;
            old_score := old_p.score;
        WHEN 'UPDATE' THEN
            new_score := new_p.score;
            old_score := old_p.score;
    END CASE;

-- some more computation and return


texts: Choosing between NoSQL and Relational databases while having both flexibility and consistency requirements

texts:
I've never worked with databases professionally and only toyed with database coding using a small sqlite db for my own music collection. Now I'm asked to build an internal database system at work for multiple users. After basic reading of NoSQL vs. relational databases, I'd like to have some ideas on how to choose the right technology.
Context

Before this database, the users work with spreadsheets entirely.
Those sheets differ in structure (schema) from project to project.
Sometimes the same project has several different sheets that need to be isolated but still exist in the same database.
We want to import all those sheets into a database and with new fields added and old fields names/values updated on the fly without breaking the original spreadsheets
When source control ops happen, we want to be able to connect to the database and update certain fields regarding the source control management status
The database will be used by various users from different locations, all through the in-house network for now. There won't be a lot of users like on Facebook or Twitter.
All the data must be localized into multiple languages and ideally co-exist in the same database.

Questions

Since we'll have different sheet structures, is NoSQL a better option than a relational database?
The users will go on using spreadsheets and may change their sheet structure on the fly. Will this be a lot of trouble for NoSQL or relational databases such as SQLite?
NoSQL does not support the ACID of relational databases, does it mean that data may get corrupted when multiple users work on the same record? We normally would like consistency.
If we maintain an optimized database but still want to be able to export selected portions or entire as CSV or JSON files? Will NoSQL or relational work better?
How to handle access control from scratch if we do it online? Will this affect the choice of database tech?
For this type of system, should we hire a database expert dev and/or maintainer? Can we build on some free/open-source infrastructure that requires the least amount of backend/frontend, security tech R&D?

Thanks for your input in advance!


texts: How do you configure a 4-node AlwaysOn cluster to have two listeners?

texts:
Looking to build a 4-node (servers A, B, C & D) AlwaysOn Windows 2016 failover cluster with 2 nodes in the primary datacenter for HA, and 2 other replica nodes in the DR datacenter for disaster/reporting purposes.

Since this is a multisite cluster, is there a way to configure to have two listeners (one for the Production/primary instance, the other for the DR instance) under the one AlwaysOn Availability Group.

Also, would SQL on all four nodes need to run under the same service account?  Or can servers A, B be on one account while servers C, D run under another account.

Is this even feasible/possible?  Thank you in advance for your help!!  Hope everyone is safe & well during this time.


texts: PostrgeSQL 9.1 Certificate Authentication: how to get certificate common name of client?

texts:
I am using Certificate Authentication for clients on a PostgreSQL 9.1 database server. Several certificate common names are mapped to a single database user in pg_ident.conf.

I do not want to create a database user for each certificate because it is easier to manage accepted certificates with pg_ident.conf. But now I need some query to map a backend process to the common name used for the connection. Is there a way to get the common name (CN field) of the certificate with which a user connected to the database?

EDIT

Thanks araqnid for pointing me at the sslinfo extension (source). The function ssl_client_dn_field('CN') provides exactly the information I need.
But the sslinfo functions work only for the connection/backend of the current session, I would rather need this information for other connections/backends (query it by backend process id).

The sslinfo extension uses the global variable MyProcPort (defined in globals.c) of type struct Port* to get the required SSL information.
Maybe someone can give me a hint how the MyProcPort variable of other backends could be accessed in an extension (aka contrib module).


texts: Creating Stored Procedure in local db that references tables in linked server

texts:
I currently have a local server linked to a remote server.

Is it possible to create a stored procedure within the local server, but querying data from tables within the linked server. I am aware there may be performance issues, but I am reluctant to create the sp on the linked server as it would require obtaining permissions to do so.

I believe my problem is syntax but I cannot identify exactly what,

USE [LOCALDB]  --my local database or should I reference linked server db here
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[mystoredprocedure]
    DECLARE @linkedserv NVARCHAR(150)
    DECLARE @linkeddb NVARCHAR(150)

    SET @linkedserv = 'HOSTNAME\SERVER';
    SET @linkeddb = 'remotedb';

    SELECT (CASE LTRIM(RTRIM([COLUMN1]))
        WHEN '' Then ''
        WHEN 'THIS' THEN 'THAT'
        WHEN 'NOW' THEN 'NEVER'
        ELSE 'OTHER' END) [Options]
    INTO #TempTable
    FROM @linkedserv.@linkeddb.dbo.TableOnLinkedServer --is this an issue?
    FULL OUTER JOIN OtherTableOnLinkedServer ON TableOnLinkedServer.COUMN1 =
    OtherTableOnLinkedServer.COLUMN0

The linked servers db and it's tables are queried multiple times throughout the sp so finding a shorthand way of referencing them would be great,

Cheers


texts: Modifying replication distributor & publisher

texts:
I have a SQL Server 2008 transactional replication setup as follows:

192.168.100.1 <-- Distributor
192.168.100.2 <-- Publisher
192.168.100.3 <-- Subscriber

Our network team want to change the IP address of the distributor, for example to: 192.168.200.1

Also, we want to move the publication database to a new server. so, the replication source (publisher) will be also moved and it will have a new IP address as well.

The subscriber will remain as is.

I cannot afford having long downtime as our systems are running for 24x7 and there are so many huge tables that are already in replication and generating a new snapshot will take long time to be replicated.

What is the best way to seamlessly re-configure the replication and keep my data in sync without rebuilding it from scratch

Thanks


texts:
I've attempted to implement solution three from the following Q & A:

Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it

Table created and stored procedure created in msdb, inserted rows as needed.

When I execute the stored procedure in an SSMS query window as the non-owner of the job, it executes as expected.  However, when I put the execute command in a SQL Server job, the execution dialogue box reports successful execution, but the job did not run - as evidenced by lack of impact on the tables the job affects.

Using SQL Server 2014, a non-owner user has these permissions within msdb: SQLAgentReaderRole, stored procedure execute, is member of a db role that has db_owner permission and is named on the indicated table. This user also has ownership of the job which executes the stored procedure.

What am I missing?


texts: Why we need auxiliary instance in TSPITR

texts:
Need help!

I've read Database Backup and Recovery User's Guide 11g but I can't uderstand why we need auxiliary instance to perform tspitr.
 We can do complete recovery of tablespace within same instance, why it's impossible for tspitr?

Here Hemant K Chitale says: "You cannot recover a tablespace to an SCN that is lower than the rest of the database.". Can anyone help me to uderstand why?

In this message is shown that when we turn tablespace offline, its SCN becomes lower than SCN of other tablespaces after a while, and when we turn it online "it will not apply redo it will just adopt current SCN".

So, why database cannot open recovered until time datafile adopting it's SCN to current? It'so due to technical aspects of OracleDB or there is another reason?


texts: What is the definition of bad circular dependency?

texts:
I have been searching for while for a good resource, where the circular dependency is well explained, unfortunately nothing good found. Therefore I tried to understand exactly which kind of circular dependency I should avoid. The problem is I found some resources which explain in contradictory manners. Could someone explain exactly please which types of circular dependency we should avoid (and why)?

Taking these relationship as an example:

Source

This relationship is mentioned here to be bad (I do not understand why).

However, the same relationship is mentioned to be not a problem (and is described as NOT circular) here:

Models <--------------------------- SuperSets
   ^                                    ^
   |                                    |
   |                                    |
Tasks  <---------------------------- Sets

Source

Another example is this:

Source

I also do not understand why is this a circular relationship?

All of the previous relationships seem to me not curcular (the arrows direction is not going back to the same point). I think there is something wrong in my understanding for circular dependency term. Could some one explain that for me please in general, and particularly also maybe on the previous examples?


texts: AlwaysOn commit on Primary if Secondary goes down

texts:
I have two node SQL Server AlwaysOn Availability Group configured in Synchronous with Automatic Failover mode.

Now since in Synchronous mode, the transactions need to commit on secondary replica first and after its acknowledgment only, it gets committed on the primary node.

Now if my secondary replica node goes down, will the transactions not commit only on my primary node, until unless my secondary comes up? Will my transactions not get hardened to disk?


texts: Mongo - findAndModify has to be executed twice to take effect

texts:
I have a collection campaign like this:
{
        "_id" : ObjectId("5ec12c501bd23866ef315a85"),
        "schemaVersion" : "1",
        "internalVersion" : NumberLong(56),
        "code" : "CK",
        "siteCampaigns" : [
                {
                        "_id" : ObjectId("5f3689274d42720edb5be328"),
                        "siteCode" : "IT",
                        "startDate" : ISODate("2020-02-10T06:00:00Z"),
                        "endDate" : ISODate("2020-02-20T06:00:00Z"),
                        "status" : "SUBSCRIBED"
                },
                {
                        "_id" : ObjectId("5f3689274d42720edb5be327"),
                        "siteCode" : "ES",
                        "startDate" : ISODate("2020-02-10T06:00:00Z"),
                        "endDate" : ISODate("2020-02-20T06:00:00Z"),
                        "status" : "UNSUBSCRIBED"
                },
                ...
            ]
}

Now I want to find and modify the element in the siteCampaigns.
I use this query:
db.campaign.findAndModify({
    query: {
        code: "CK", 'siteCampaigns.siteCode': "IT"
    },
    update: {
        "$set": {
            'siteCampaigns.$.endDate': ISODate("2020-08-30T00:00:00Z")
        }
    },
    upsert:false
})

I execute it for the first time, I find the document, no error, but the field is not modified; when I repeat it, it finds the same document, now updated.
I always have to execute it twice to take effect. Why? I use mongo shell.


texts: SSMS table properties show an exception for Index space on every table

texts:
When getting table properties from a SQL 2014 server (12.0.4449) using SSMS 2014 (12.0.4100) or 2016 (13.0.15600) on any database or table I get the following text for the "Index space" property on the Storage tab:

  An exception occurred while executing a Transact-SQL statement or
  batch.

This is the error:


texts: Efficient way in MariaDB/MySQL to manage user access from multiple IPv4 and IPv6 subnets

texts:
In our MariaDB setup I would like to treat users connecting from two different subnets as "local" users. I.e. grant the same permissions as they would connect from localhost. Since there are many users that will have the same permissions, I wonder if there is a clever/efficient way to achieve that.
As far as I understand it, I would have to create five MySQL users for every "real" user and copy all required permissions to all of them:
- user1@localhost
- user1@[IPv4 subnet1]
- user1@[IPv4 subnet2]
- user1@[IPv6 subnet1]
- user1@[IPv6 subnet2]
- ...
- userN@localhost
- userN@[IPv4 subnet1]
- userN@[IPv4 subnet2]
- userN@[IPv6 subnet1]
- userN@[IPv6 subnet2]

When I need to change the permissions of a "real" user, I need to change them for all the corresponding MySQL users.
This seems like a lot of users and a lot of work. Is there an easier way to do this? Or is there a common technique to automate this setup? (Preferably without writing our own automation script.)
We are using the adminer web interface (https://www.adminer.org/) but it doesn't seem to have a tool to do this.
Im aware of the possibility to limit access with a firewall, but unfortunately this does not work in our case, because a few databases need access from a wider range of remote addresses than the majority of DBs, that should be limited to the "local" subnets.
Any hint how to do this in an elegant way is greatly appreciated!


texts: Connecting to a Remote SQL Server 2008 Instance

texts:
I work with SQL Server 2008 at my current job and the way I connect to the (backup) server where The SQL Server 2008 instance is installed is by doing a remote desktop from my local work machine into a windows server 2003 computer where the SQL Server 2008 database program and database files are stored (also on our local network)...

This works fine but the only problem is that the SQL Server installation does not have SSRS, SSIS or BI tools installed. I know it is fairly easy to install these features BUT I was wondering If I would be able to connect to the database using a different SQL Server 2008 Installation on a totally different computer through an internet connection (not LAN)?

For Example, If I have a fresh Sql Server 2008 installation on my home computer with all the needed features (SSIS,SSRS,BI Tools), would I be able to Open up Management Studio and connect to the SQL Server 2008 database server/engine at my workplace?


texts: MYSQL improve query performance when using OR

texts:
I have a very simple MYSQL table with 2 columns and I run this query:

SELECT * FROM table WHERE (col1 = '123' AND col2 = '456')
                       OR (col1 = '456' AND col2 = '123')

Col1 and col2 are a composite primary key: PRIMARY KEY('col1','col2'). Each of both is also a foreign key for primary key in another table

When I ran the EXPLAIN command for the above query i got the following:

id  select_type  table  type    possible_keys   key key_len ref rows    Extra
1   SIMPLE       table  index   PRIMARY,col2    col2    8   NULL    1   Using where; Using index

The type in the above result is index which is very similar to All and so very likely to be slow on a large database. Is there a way to improve the above select command


texts: Can A Database Transaction Have Different Session IDs?

texts:
I am just trying to troubleshoot an issue and was it seems that one of my spids is associated with an open transaction.  Just wondering if that spid would be used for other queries in the transactions too?  Or how can I find what other queries are running under that transaction?

Thanks!


texts: Enable jemalloc for MySQL 5.7 WHM Server

texts:
i want to enable jemalloc on MySQL 5.7 on cPanel/WHM server on Centos 7.
i install jemalloc:
yum list jemalloc
yum install jemalloc.x86_64

after that edit /etc/my.cnf and add:
[mysqld_safe]
malloc-lib=/usr/lib64/libjemalloc.so.1

then restart mysql server from whm. but when i check if jemalloc if enabled using pt-mysql-summary and also using below:
lsof -p $(pidof mysqld) | grep mem

but the result that it is not enabled yet.
how to enable it?
Thanks,


texts: couchbase - multiple buckets or documents with different types

texts:
Background Information

This is day one for me with couchbase.  I've been reviewing the sample databases using the database console that comes with couchbase.
Specifically, I've been playing with the "beer-sample" application to try to understand how to organize my database.
It seems that they have two different types of documents - breweries or beers - and everything is just in one bucket.

For my personal application, I have the following data:

{
  "server_details": {
    "name": "testserver",
    "dns_name": "testserver.mydomain.net",
    "ipv4": "10.xx.xx.xx",
    "ipv6": "",
    "type": "webserver"
  },
  "server_status": {
    "up_to_date": true
  },
  "packages": {
    "package1": {
      "current_version": "x.x",
      "previous_version": "x.x",
      "last_updated_on": "2016-03-16",
      "play_id": "link to audit table"
    },
    "package2": {
      "current_version": "x.x",
      "previous_version": "x.x",
      "last_updated_on": "2016-03-16",
      "play_id": "link to audit table"
    }
  }
}

I have different server types such as webserver, appserver, dns server etc.  So I'm planning to just use the same json object but change the "type" field.

Question

I also need to create some sort of a audit table that will show the history of when a package on a server was updated.
I need to track things like:

execution date
script name
package1_version_b4_update
package1_version_after_update
server_dns_name

This is the json object I've come up with for this type of a document:

{
  "audit_details": {
    "script_name": "upgrade_all_mrts",
    "rt_ticket_number": "16123",
    "execution_date": "2016-03-16",
    "status": "success"
  },
  "affected_servers": {

    "server1": {
      "package1":{
        "version_after_update": "x.x",
        "version_before_update": "x.x",
        "last_updated_on": "2016-03-16"
      },
      "package2": {
        "version_after_update": "x.x",
        "version_before_update": "x.x",
        "last_updated_on": "2016-03-16"
      }
   },
    "server2": {
      "package1":{
        "version_after_update": "x.x",
        "version_before_update": "x.x",
        "last_updated_on": "2016-03-16"
      },
      "package2": {
        "version_after_update": "x.x",
        "version_before_update": "x.x",
        "last_updated_on": "2016-03-16"
      }
   }
  }
}

Would this be just another document with a completely different structure? or should i be putting it in another bucket?
If it should all be in the same bucket, is the structure of the json object I'm proposing appropriate?  Fundamentally, I guess my question is do you dump everything into one bucket as a collection of varied documents... and just make sure that the related documents have the same structure so that you can query them all at once?

Sorry for the remedial questions but I'm try to build a prototype on the fly ...while reading the docs.  Any tips would be appreciated.


texts: Problem connecting to postgresql from external windows machine

texts:
I have a Windows laptop running a postgresql database. I want to be able to pull data from this DB into an application on another laptop, say excel or access.

So far, I set up a DSN on my machine and it works fine if I want to pull data into excel on my machine. However, I cannot successfully replicate this process on another machine. I have already installed the Postgres ODBC driver on this machine.

Can anybody help me through this process? I'm completely new to database administration and mostly do not know what I am doing - any help would be greatly appreciated!


texts:
Somehow MySQL eats up 50% of my RAM on the server being used. I checked and there are no queries, show full processlist is empty. I restarted the mysqld and within a couple of minutes the RAM usage again went up to 40-50% while again no processes were running.

top - 14:51:55 up 1 day, 19 min,  1 user,  load average: 0.22, 0.32, 0.37
Tasks: 175 total,   1 running, 174 sleeping,   0 stopped,   0 zombie
Cpu(s):  2.3%us,  0.3%sy,  0.0%ni, 97.3%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   2038460k total,  1967892k used,    70568k free,    21840k buffers
Swap:  4095992k total,   458352k used,  3637640k free,   244896k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 2798 mysql     16   0 1254m 1.1g 4716 S  1.7 57.4   1:17.22 mysqld

I am not expert on this so I don't even know where to start looking. Anyone can give some suggestions on where to start looking? (i.e. is there a way to see what the memory is being used for?)

Here are some details on the server I have:

SERVER DATA

Kernel and CPU:     Linux 2.6.18-400.1.1.el5 on i686
Processor information:  Intel Core2 Duo CPU E7500 @ 2.93GHz, 2 cores
CPU load averages:  0.34 (1 min) 0.49 (5 mins) 0.44 (15 mins)
CPU usage:  11% user, 2% kernel, 6% IO, 81% idle
Real memory:    1.63 GB used, 1.94 GB total
Virtual memory:     447.02 MB used, 3.91 GB total
Local disk space:   59.03 GB used, 219.71 GB total

MySQL SETTINGS

bdb_log_buffer_size     262144
bulk_insert_buffer_size     8388608
concurrent_insert   1
connect_timeout     10
innodb_additional_mem_pool_size     1048576
innodb_buffer_pool_size     1073741824
innodb_doublewrite  ON
innodb_file_io_threads  4
join_buffer_size    131072
key_buffer_size     8384512
max_heap_table_size     16777216
max_insert_delayed_threads  20
max_join_size   4294967295
max_sort_length     1024
max_tmp_tables  32
max_write_lock_count    4294967295
multi_range_count   256
myisam_data_pointer_size    6
myisam_max_sort_file_size   2146435072
myisam_mmap_size    4294967295
myisam_sort_buffer_size     8388608
net_buffer_length   16384
preload_buffer_size     32768
query_alloc_block_size  8192
query_cache_limit   1048576
query_cache_min_res_unit    4096
query_cache_size    0
query_cache_type    ON
query_cache_wlock_invalidate    OFF
query_prealloc_size     8192
range_alloc_block_size  4096
read_buffer_size    131072
read_only   OFF
read_rnd_buffer_size    262144
sort_buffer_size    2097144
table_cache     64
tmp_table_size  33554432
wait_timeout    28800


texts: How to set slow query log in MariaDB my.cnf?

texts:
It should be easy, but when I put these lines:

log_slow_queries = 1
long_query_time = 1
log-slow-queries = /var/log/mysql/slow_query.log
log_queries_not_using_indexes

in [mysqld], MariaDB fails to restart.

I looked at the docs, but could not figure out what is wrong in my configs.

Also , I've set the file permission to /var/log/mysql/slow_query.log

# ls -al /var/log/mysql/slow_query.log
-rw-rw-r-- 1 mysql mysql 744682 Dec  4 23:46 /var/log/mysql/slow_query.log

However, I can set the parameters in command line:

SET GLOBAL slow_query_log_file = '/var/log/mysql/slow_query.log';
SET GLOBAL LONG_QUERY_TIME = 1;
SET GLOBAL slow_query_log = 'ON';

and see that they are set:

+---------------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name       | Value                                                                                |
+---------------------+--------------------------------------------------------------------------------------------------------------+
| log_slow_filter     | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |
| log_slow_rate_limit | 1                                                                                |
| log_slow_verbosity  |                                                                                |
| slow_launch_time    | 2                                                                                |
| slow_query_log      | ON                                                                                |
| slow_query_log_file | /var/log/mysql/slow_query.log                                                                                |
+---------------------+--------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

and even the slow queries are being written to the log. But I want to make the config persistent by writing them to my.cnf. So appreciate your help.


texts: Local package won't compile when remote package recompiles. (Oracle)

texts:
We have a package on a 10.2.0.4 database(A) and a package on an 11.2.0.2 database(B) that references A's package.  When something on A changes that requires the package on A to do an implicit re-compile, sometimes the package on B that should also implicitly re-compile goes invalid.  The error it shows is PLS-00907:

 Cause: PL/SQL is unable to find and load a library unit that was
        previously available.  This typically happens when you try to
        load a unit which references another library unit that is
        non-existent or invalid.
Action: Provide the needed library unit.

The strange thing is that the package on A is valid and can be recompiled successfully, yet recompiling the package on B is still unsuccessful.  It is only when we make an inconsequential change to the package on A that the package on B will then compile successfully.  This problem has been occurring every few weeks for the past several months.  It may have started when we upgraded B from 11.1 to 11.2.

The database link on B is a private database link using a specific username/password to connect to A.  The remote_dependencies_mode parameter is set to SIGNATURE on both databases.

This problem is similar, but not the same as MOS Bug 9719541, and Doc ID 166680.1.  Anyone have any idea what could be wrong?


texts: Error reading MySQL 5.5 binary log using mysqlbinlog

texts:
I'm having some trouble with a replication setup I'm using. It has been working fine for months, and then over the weekend, the slave stopped reading updates from the masters binlog with an error Got fatal error 1236 from master when reading data from binary log: 'bogus data in log event'

When trying to read the relevant binary log using mysqlbinlog, I get the error shown below;

[root@slglcd-01] # mysqlbinlog ibm-pr-slglcd-01.000075 > /dev/null
ERROR: Error in Log_event::read_log_event(): 'Event too small', data_len: 0, event_type: 0
ERROR: Could not read entry at offset 1828: Error in log format or read error.
[root@slglcd-01] #

Frustrating! I have tried using various combinations of --start-position and --offset to try and get past the bad data, and nothing seems to be working...

What I'm after is a way of skipping (or removing) this error from within the binary log, creating a new binary log without the offending item, so that I can continue my replication.

I'm not worried about loosing the statement, this is a syslog collection database, and one missing row isn't going to hurt.

What I can't do is re-create the slave from the master, as the master uses the BLACKHOLE engine, and therefore has no real data stored...

If the worst comes to the worst, I will have to start from the next binary log in the sequence and loose the data left in the offending log.

Thanks in advance,
Dave


texts: Gathering sp_BlitzFirst data into a centralized database

texts:
I have setup a Central Management Server and registered all of my SQL servers (prods and devs). I want to save the results of sp_BlitzFirst from running it against all of my servers to a centralized database 'DBAtools' on my test box for later analysis. What is the most efficient method for achieving this task?

Thank you so much in advance.


texts: How long does it take to do RESTORE WITH NORECOVERY

texts:
I am in the unfortunate situation of having to do a database restoration.

My full backup was at 9.30pm (157MB) and there were incrmentatl backups at 11am and 3pm.  The incremental backups are less than 30kb each.  It is now 6pm.

When I restored the full backup WITH RECOVERY, it took less than a minute.  But I could not apply the two transaction logs.

When I restored the full backup WITH NORECOVERY, it also took less than a minute to say it was successful, but then the database remained in the Restoring... state.  It has been like that for the last 40 minutes.  Task Manager shows almost no activity.  Is this normal?  Should I abort?


texts: Does the primary key of a table help decide what block a row is written to in Oracle?

texts:
I'm trying to refactor a slow Oracle database, where the developers naively chose a VARCHAR2 UUD4 as the primary key for what is really an append only time series dataset. I've found that the uniqueness check for the primary key (frustratingly I can't quite convince the senior developer to drop that uniqueness constraint) and foreign key checks for UUID4s are the cause of poor performance.
This is the first time I've touched Oracle in my career, so I'm unfamiliar with how it works.  In MySQL the primary key absolutely effects how the table is allocated physically, but I am getting mixed messages about how it affects things in Oracle.  Obviously there are index-optimised-tables, but those have to explicitly set.
Looking at the index statistics for the primary key, the clustering factor is nearly as high as the row count.  Did the random primary key have anything to do with that?
If I have a random primary key, does it affect how the row is physically allocated? For instance, would it mean a similarly random allocation of the row to a block?

If so, how can I change that so the database will try to cluster the writes to a smaller set of blocks (to reduce random IO)?  Would moving to a 'clustered' identifier like a UUID1 improve
insert performance?


texts:
Oracle SQL Developer has an option to export the contents of a query result to various formats (CSV/fixed width/Excel/XML).  Is there any way to automate it?

If not, what free tools are available that will let me automate exports into the same formats that SQL Developer is capable of exporting to?


texts:
while in the process of creating a publication in one of my servers I continuously get the following error message

  The Distributor has not been installed correctly. Could not enable
  database for publishing.

Although I have dropped and re-created the distributor several times.

--==============================================================
-- replication - create publication - complete
-- marcelo miorelli
-- 06-Oct-2015
--==============================================================

select @@servername
select @@version
select @@spid
select @@servicename

--==============================================================
-- step 00 --  configuring the distributor
-- if there is already a distributor AND it is not healthy,
-- you can have a look at the jobs related to this distributor and
-- MAYBE, if you need to get rid of it, run this step
-- generally you need to run this when adding a publication it says there is a problem with the distributor
--==============================================================

use master
go
sp_dropdistributor
-- Could not drop the Distributor 'QG-V-SQL-TS\AIFS_DEVELOPMENT'. This Distributor has associated distribution databases.

EXEC sp_dropdistributor
     @no_checks = 1
    ,@ignore_distributor = 1
GO

--==============================================================
-- step 01 --  configuring the distributor
-- tell this server who is the distributor and the admin password to connect there

-- create the distributor database
--==============================================================

use master
exec sp_adddistributor
 @distributor = N'the_same_server'
,@heartbeat_interval=10
,@password='#J4g4nn4th4_the_password#'

USE master
EXEC sp_adddistributiondb
    @database = 'dist1',
    @security_mode = 1;
GO

--==============================================================
-- check thing out before going ahead and create the publications
--==============================================================

USE master;
go

--Is the current server a Distributor?
--Is the distribution database installed?
--Are there other Publishers using this Distributor?
EXEC sp_get_distributor

--Is the current server a Distributor?
SELECT is_distributor FROM sys.servers WHERE name='repl_distributor' AND data_source=@@servername;

--Which databases on the Distributor are distribution databases?
SELECT name FROM sys.databases WHERE is_distributor = 1

--What are the Distributor and distribution database properties?
EXEC sp_helpdistributor;
EXEC sp_helpdistributiondb;
EXEC sp_helpdistpublisher;

--==============================================================
-- here you need to have a distributor in place

-- Enabling the replication database
-- the name of the database we want to replicate is COLAFinance
--==============================================================
use master
exec sp_get_distributor

use master
exec sp_replicationdboption @dbname = N'the_database_to_publish',
                            @optname = N'publish',
                            @value = N'true'
GO

is there anything missing?
any ideas?


texts: Stop, Start, or Restart a SSAS Tabular service instance in PowerShell with Invoke-Command and $variables

texts:
I'm working on some PowerShell commands / scripts to stop, or start, or restart a SSAS Tabular service instance using Invoke-Command and some $variables. The end goal is to eventually put this code into an Agent Job for easy service restarting, but that's beyond the scope of this post -- I need to get this working with the $variables first.

I'm using PowerShell 5.0 on my source (local) machine, and trying to stop / start / restart a remote SSAS 2012 Tabular server instance (11.0.6540.0).

First, I start my script with Set-ExecutionPolicy.

Set-ExecutionPolicy -Scope CurrentUser RemoteSigned -Force

Then, using the hard coded method just to get the syntax right and watching the SQL Server Configuration Manager (Config Mgr) while RDP'd into the server to verify the command is working, I use this PowerShell code:

Invoke-Command -ComputerName DevServer { stop-service 'MSOLAP$TABULAR' }

This works great. I verify in Config Mgr that the service stops.

I also verify that the 2 below PowerShell code scripts work:

Invoke-Command -ComputerName DevServer { start-service 'MSOLAP$TABULAR' }

and

Invoke-Command -ComputerName DevServer { restart-service 'MSOLAP$TABULAR' }

Excellent. Works great.

Now I start putting things into variables like so:

$tabularinstance = 'MSOLAP$TABULAR'

$server = "DevServer"
$stop = "stop-service $tabularinstance"
$start = "start-service $tabularinstance"
$restart = "restart-service $tabularinstance"

I then show the values of the variables to verify expected results:

# show values
$tabularinstance
$stop
$start
$restart
$server

And I get the following results returned in the PowerShell console:

PS H:\> # show values
$tabularinstance
$stop
$start
$restart
$server
MSOLAP$TABULAR
stop-service MSOLAP$TABULAR
start-service MSOLAP$TABULAR
restart-service MSOLAP$TABULAR
DevServer

OK, so things seem good.
I then run the below PowerShell command, expecting it to work, but nothing happens on the server; Config Mgr doesn't show the service is stopped, it's still running:

Invoke-Command -ComputerName $server { $stop }

I then realized that the $tabularinstance variable doesn't have the wrapping apostrophes, so I tried this:

$tabularinstance = '''MSOLAP$TABULAR''' # triple apostrophes needed to get the ' escape character and apply the apostrophes to variable

and then re-set the $stop, $start, and $restart variables to use the updated $tabularinstance variables:

$stop = "stop-service $tabularinstance"
$start = "start-service $tabularinstance"
$restart = "restart-service $tabularinstance"

and re-show them again to verify they include the wrapping apostrophes:

PS H:\> # show values
$tabularinstance
$stop
$start
$restart
$server
'MSOLAP$TABULAR'
stop-service 'MSOLAP$TABULAR'
start-service 'MSOLAP$TABULAR'
restart-service 'MSOLAP$TABULAR'
DevServer

Yep, they do.
I then try my InvokeCommand again:

Invoke-Command -ComputerName $server { $stop }

Negative. Still did not work. In ConfigMgr, the service is still running.

I even thought to try to assign the full command to variable to see how it all resolves:

$test = "Invoke-Command -ComputerName $server { $stop }"

And then check the results:

PS H:\> $test
Invoke-Command -ComputerName DevServer { stop-service 'MSOLAP$TABULAR' }

This looks right to me too. It looks like the exact same command I did when hard-coding everything instead of using $variables.

So why isn't my PowerShell working using the $variables method? What's missing?


texts: Efficiently filtering a self join based on a separate table

texts:
I have a table into which I've accidentally introduced mostly duplicate data, and I need to work out what are duplicates so I can have them removed. So I join table x onto itself.

Things get slow though, when I also need to filter out row ids based on content in another table, so I end up with these two sub-queries.

SELECT *
FROM table x
JOIN table x2 ON x.uid = x2.uid
  WHERE x.eid IN (SELECT group FROM grouping)
  AND  x2.eid IN (SELECT group FROM grouping)
LIMIT 10;

Can I make this more efficient somehow? The explain looks like this:

+----+-------------+----------+--------+-----------------+---------+---------+-------------------+---------+-------------------------------------+
| id | select_type | table    | type   | possible_keys   | key     | key_len | ref               | rows    | Extra                               |
+----+-------------+----------+--------+-----------------+---------+---------+-------------------+---------+-------------------------------------+
|  1 | PRIMARY     | grouping | index  | group           | group   | 5       | NULL              | 8804849 | Using where; Using index; LooseScan |
|  1 | PRIMARY     | x        | eq_ref | PRIMARY,eid,uid | PRIMARY | 4       | db.grouping.group |       1 | Using where                         |
|  1 | PRIMARY     | grouping | range  | group           | group   | 5       | NULL              | 8802822 | Using where; Using index; LooseScan |
|  1 | PRIMARY     | x2       | eq_ref | PRIMARY,eid,uid | PRIMARY | 4       | db.grouping.group |       1 | Using where                         |

Definition of tables and sample values below:

DROP DATABASE tmp;
CREATE DATABASE tmp;
USE tmp;

CREATE TABLE `e_data` (
  `eid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned NOT NULL DEFAULT '0',
  `created` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`eid`),
  UNIQUE KEY `eid` (`eid`),
  KEY `uid` (`uid`)
);

CREATE TABLE `user_group` (
  `eid` int(10) NOT NULL DEFAULT '0',
  `u_group` int(10) DEFAULT NULL,
  PRIMARY KEY (`eid`),
  KEY `u_group` (`u_group`)
);

INSERT INTO e_data (uid, created) VALUES (1, 1), (1, 1), (1, 1), (1, 2), (1, 2), (1, 2), (1, 2), (1, 2), (1, 2), (2, 3), (2, 3), (2, 3), (3, 3), (3, 3), (3, 3);

INSERT INTO user_group (eid, u_group) VALUES (1, 1), (2, 1), (3, 1), (4, 4), (5, 4), (6, 4), (7, 7), (8, 7), (9, 7), (10, 10), (11, 10), (12, 10), (13, 13), (14, 13), (15, 13);

SELECT e1.eid e1, e2.eid e2, e1.created, e2.created FROM `e_data` e1 JOIN `e_data` e2 ON e1.uid = e2.uid WHERE e1.eid IN (SELECT u_group FROM user_group) AND e2.eid IN (SELECT u_group FROM user_group) AND e2.eid > e1.eid AND e1.created = e2.created;

+----+----+---------+---------+
| e1 | e2 | created | created |
+----+----+---------+---------+
|  4 |  7 |       2 |       2 |
+----+----+---------+---------+

This shows me that since "created" is the same for group 4 and 7, that's a duplicate I should look closer at removing.

Using MariaDB 10.0.21.


texts: Indexing of a PostgreSQL bitstring (up to 20,000 bits each)

texts:
I am building a table that contains chemical compounds (many millions of rows) and of those compounds certain predetermined features/fragments are flagged in a fixed length bitstring.
This bitstring will have between 2000 and 20000 bits, further research needs to be done to determine more precise figures there.
When searching for compounds that have certain specific features, or lack specific features, a search is done on a select subset of this bitstring. This can be a different subset every time.

Is there an index type that can make these searches efficient in PostgreSQL (9.6 or 10)?

Insertions are infrequent and done in a batch process, whereas searches are the mostly used operation and should preferably be quick and not have false positives or false negatives.

To me this vaguely sounds like work for a GIN index, but my understanding of this index type is not enough to say for sure if that is realy the case.

Actually there could be another solution, and that is to create a separate 'fragment_index' table, with fragment identifiers (as they have a fixed position in the bitstring they also have a numerical identifier) + compound-id pairs.
My worry there is that that table can become enormous (20M compounds with avg 50 hits on fragments = 1G rows) and multiple joins against it (one for each fragment) where that join can also return up to 80% of matches with the compounds table (in some cases this is likely) will not perform at all.

I would be gratefull to get any suggestions towards a direction to get this on the road.

update: i tried a GIN index using the trigram module on a varchar array with codified shortcodes, it gave mixed results, mainly depending on the amount of data left over after the filter operations.

For the sake of giving examples that have any meaning let's assume the table looks like this:

CREATE TABLE testcompounds (
 id serial primary key,
 cd_structure text,
 features_as_text varchar(128),
 features_as_bits bit varying(32)
);

CREATE INDEX flags_testcompounds on testcompounds using gin (features_as_text gin_trgm_ops);

CREATE TABLE fragments (
 id serial primary key,
 smarts text,
 keystring varchar(4),
 frequency int
);

insert into fragments (keystring,smarts) values('AAA', '*=O');
insert into fragments (keystring,smarts) values('AAB', '[#6]=O');
insert into fragments (keystring,smarts) values('AAC', '[#7]=O');
...
insert into fragments (keystring,smarts) values('AAN', '[#6]-F');
insert into fragments (keystring,smarts) values('AAO', '[#6]-Cl');
insert into fragments (keystring,smarts) values('AAP', '[#6]-Br');
...
etc.

and the features_as_text and features_as_bits fields have been fully populated.

An example of a query that can be ran on this would be:

select id, cd_structure from testcompounds
where
    (features_as_bits & (B'00000000000000000000000000000001' << (2-1)) = (B'00000000000000000000000000000001' << (2-1)))

AND (features_as_bits & (B'00000000000000000000000000000001' << (18-1)) = (B'00000000000000000000000000000000'))
AND (features_as_bits & (B'00000000000000000000000000000001' << (19-1)) = (B'00000000000000000000000000000000'))
AND (features_as_bits & (B'00000000000000000000000000000001' << (5-1)) = (B'00000000000000000000000000000000'))
AND (features_as_bits & (B'00000000000000000000000000000001' << (6-1)) = (B'00000000000000000000000000000000'))
AND (features_as_bits & (B'00000000000000000000000000000001' << (7-1)) = (B'00000000000000000000000000000000'))
AND (features_as_bits & (B'00000000000000000000000000000001' << (8-1)) = (B'00000000000000000000000000000000'))
AND (features_as_bits & (B'00000000000000000000000000000001' << (9-1)) = (B'00000000000000000000000000000000'))

In words: get everything that has feature 2, and does not have any of features 18,19,5,6,7,8,9


texts: #1194 - table 'user' is marked as crashed and should be repaired

texts:
I have problems getting into Mysql on phpmyadmin
(mysqli_real_connect()(HY000/1130)Host 'localhost'is not allowed to connect to this MariaDB server )
I have found out its a crashed file in mysql called "user'
All the info I have read says to fix a crashed table use the dropdown menu 'With Selcted' . My 'With Selected' is not a dropdown (XAMPP Version: 7.3.7 , Control Panel Version: 3.2.4 ). It is in italic.
Is there a shell command that I can use.
Thanks


texts:
I have just noticed (unfortunately while trying to restore from backup), that on my production server there is a 35GB ibdata1 file!

As I understand I can reduce its size by moving to innodb_file_per_table.
However following the answers I have seen here (link below), you have to dump and drop all databases before you can start the conversion.

How long would it take to do this convert?

Is it possible to stagger the convert and do it one database at a time?

Thanks.

What is the best way to reduce the size of ibdata in mysql?


texts: Does sys.sql_logins.is_policy_checked mean that the policy has been checked?

texts: When I look in sys.sql_logins, I see a column called is_policy_checked. Can I trust that my password policy has been checked for all of the logins where this column value is 1?


texts: Is there a complete list of Online operations for each SQL Server version?

texts:
As we are about to change some of the large tables in our 5TB database I found myself needing a list of the operations that can be performed online and the which require full locks to be held while they run. Ideally, this list would also contain information about which statements require the SCH-M lock to commit at the end.

While I know most of them from my time in Microsoft, I was surprised that I could not find a publicly available list of online operations as they evolved from SQL Server 2005 and all the way to 2014 CTP.

Does anyone have such a list available? If not, I may decide to create it.


texts: Large files in wamp\bin\mysql\mysql5.5.24\data directory

texts:
I have discovered these files located in the wamp\bin\mysql\mysql5.5.24\data folder on my Windows 2008 server.

They are consuming about 22 GB on a 40 GB drive.

I am using a LMS called moodle, that may be kicking off the creation of them, I am not sure.

I am trying to determine:

what is creating these files?
can I delete them to reclaim drive space?


texts: Graphical representation of SQL Server Index

texts:
Are there any methods or tools to enable someone to visualise/graphically represents the b-tree that underpins an index?

The purpose would be to aid in the teaching of how indexes work to enable students to better picture what is going on under the hood.


texts: Making Production database as Publisher and Distributor for Snasphot Replication

texts:
In our environment we are using Log Shipping as DR. Our primary database is a  clustered instance. My requirement is to set up a snapshot replication for reporting purposes.

My plan is as follows:

Make the production database as the distributor and publisher.
The subscriber will be a read only for reporting.

My question is:

Will there be any problem if the production database acts both as distributor and subscriber?


texts: Adding a running count of distinct values in MySQL 5.7

texts:
I have a table that records which hardware was used for a given run (job / batch) on a tool. Vastly simplified, the table looks like:

CREATE TABLE hardware (
  `run_name` varchar(8) NOT NULL,
  `hardware_used` varchar(45) DEFAULT NULL,
PRIMARY KEY (`run_name`)
);

run_name   hardware_used
pk123          h1
pk124          h1
pk125          h2
pk126          h2
pk127          h2
pk128          h3
pk129          h1

I want to get a running count of how many times each hardware has been used:

run_name   hardware_used   times_used
pk123          h1              1
pk124          h1              2
pk125          h2              1
pk126          h2              2
pk127          h2              3
pk128          h3              1
pk129          h1              3

In lexicographical order of run_name, create a running tally of how many times a given hardware_used has been seen.

In SQL Server, PostgreSQL, and MySQL 8, I think it could be done using Window Functions. Eg: the using SQL Server's OVER clause and Partitions, but I'm stuck with using MySQL 5.7.

Is something like this possible to generate in pure SQL?


texts: Can I shrink the transaction log file on a mirror database?

texts:
This is a followup question to a previous question about why I couldn't shrink the log file on the principal database.

To make a long story short, I setup database mirroring but forgot to make sure the job that backed up the transaction log was running again, and the transaction log grew to almost 60GB.

Since mirroring was set up, this size increase was duplicated on the mirrored server, and eventually took up all the disk space and made the mirror database unusable.

Per this question about transaction log maintenance on a mirror database, you can't back up the log on the mirror, but when asked specifically in comments about how to shrink an overgrown log file on a mirror database, a comment was left that

  One way to do this would be to failover to the mirrored database and
  do the shrink there. Thoroughly test this out in a non-production
  environment to ensure it has the behavior you want/expect.

This seems to suggest that there might be other ways to shrink the log file on a mirror, and this method might not necessarily be safe to do on a production server.

Is there a way to safely shrink the transaction log file on a database mirror?


texts: How to perform schema migration from array to many-to-many to lookup table?

texts:
I currently am storing values in a column of type array like follows (this is just an example):

CREATE TABLE  blog_posts(
    id INTEGER PRIMARY KEY,
    blog_title TEXT,
    tags TEXT[]
);

With the column tags being an array of text values. I have since realized that due to the way I will be querying this data, I want to switch from using an array column to store my labels to a many-to-many lookup table instead.

CREATE TABLE  blog_posts(
    id INTEGER PRIMARY KEY,
    blog_title TEXT,
);

CREATE TABLE tags(
    id INTEGER PRIMARY KEY,
    label TEXT

);

CREATE TABLE blog_tags(
    blog_id INTEGER REFERENCES blog_posts(id),
    tag_id INTEGER REFERENCES tags(id)
);

I know how to do the alter schema commands needed to perform the migration for the schema itself but my question is how can I write a migration that will correctly transfer the data from the array to the new tables as well? (Also, I would preferably want the equivalent down migration)

Should I just write the SQL for the SELECTs and INSERTs that would be needed to accomplish this and have those run as part of the migration? Or is having any sort of SELECT or INSERT in a migration script considered a bad practice?

Should I even be trying to do a scripted migration like this at all and should this just be a manual step I have to do?

Is there some other method I have completely missed here?

Doing the needed SELECTs and INSERTs as part of the migration seems like the right approach, but I have never done a migration that required that so I find myself uncertain if that is the right approach.

This is all on PostgreSQL 9.6


texts: PostgreSQL: Separate tables vs single table to perserve disk space?

texts:
I have 2 tables with the below schemas with equal number of rows. When I run the SELECT relname, relpages FROM pg_class ORDER BY relpages DESC command, they show up as 23GB each even though the data types (total number of bytes) for thew two schemas result in different sizes . What is the best way to figure out how much space I would save by combining tables? Also, is there any way to determine, how much space each row is actually taking up?

                                 Table "public.table1"
Column        |           Type           |                      Modifiers
--------------+--------------------------+-----------------------------------------------------

 field1       | smallint                 |
 field2       | smallint                 |
 field3       | integer                  |
 field4       | smallint                 |
 timestamp    | timestamp with time zone |
 user_id      | integer                  |
 status       | boolean                  |
 id           | integer                  | not null default

                                  Table "public.table2"
 Column          |           Type           |                     Modifiers
 ----------------+--------------------------+----------------------------------------------------
 user_id         | integer                  |
 begin_timestamp | timestamp with time zone |
 end_timestamp   | timestamp with time zone |
 field           | smallint                 | not null
 id              | integer                  | not null default


texts: SQL server 2016/2017 in terms of better performance

texts:
Which SQL version or edition should we use out of SQL server 2017std/ent or 2016std/ent in terms of better performance gain when you are dealing with SP's (approx 1000 or more)in single database which are equipped with complex joins over tables, views, udfs and also having SORT and ORDERBY clauses in many of the SPs.
Thanks


texts: Finding the last record within three years of the first record with subqueries

texts:
I have the following table with student enrollment data:

CREATE TABLE [dbo].[Enrollments](
    [SequenceID] [bigint] NULL,
    [SchoolYear] [int] NULL,
    [StudentID] [varchar](8) NULL,
    [ProgramID] [varchar](10) NULL,
    [DepartureCode] [varchar](2) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (29735, 2012, N'9800384', N'5331', N'10')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (33183, 2013, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (33549, 2013, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37105, 2014, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37810, 2015, N'9800384', N'5331', N'22')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (29544, 2012, N'9100926', N'5729', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37320, 2014, N'9100926', N'5729', N'01')
GO

I want to

Find the first year that the student was enrolled in a program.
Find the latest enrollment from all the enrollments occurring within 3 years of the first year.
Get the DepartureCode for the enrollment in 2.

Output for sample data above would be:

+--------------+--------------+--------------+--------------+---------------+
| SequenceID   | SchoolYear   | StudentID    | ProgramID    | DepartureCode |
+--------------+--------------+--------------+--------------+---------------+
|     37105    |         2014 |     9800384  |         5331 |           01  |
|     37320    |         2014 |     9100926  |         5729 |            01 |
+--------------+--------------+--------------+--------------+---------------+

This is the full record for the last enrollment within 3 years of the first enrollment.

I'm doing this with subqueries as below, but wondering if there is another way that is easier to read?

SELECT
    MainQueryEnrollments .StudentID,
    MainQueryEnrollments .ProgramID,
    MainQueryEnrollments .SchoolYear,
    MainQueryEnrollments .DepartureCode
FROM
    Enrollments AS MainQueryEnrollments
JOIN
    ( -- find SequenceID of the last enrollment within
      -- three years of the first enrollment
    SELECT
        SubQueryEnrollments.StudentID,
        SubQueryEnrollments.ProgramID,
        MAX(SubQueryEnrollments SequenceID) AS SequenceID
    FROM
        Enrollments AS SubQueryEnrollments
    JOIN
        ( -- find the SchoolYear of the first enrollment
          SELECT
            StudentID,
            ProgramID,
            MIN(SchoolYear) AS SchoolYear
        FROM Enrollments
        GROUP BY
            StudentID,
            ProgramID
        ) FirstEnrollment
    ON FirstEnrollment.StudentID = SubQueryEnrollments.StudentID
    AND FirstEnrollment.ProgramID = SubQueryEnrollments.ProgramID
    AND SubQueryEnrollments.SchoolYear - FirstEnrollment.SchoolYear <= 2
      -- enrollments within three years of the first enrollment
    GROUP BY
        SubQueryEnrollments .StudentID,
        SubQueryEnrollments .ProgramID
) AS LastEnrollment3Years
ON MainQueryEnrollments.SequenceID = LastEnrollment3Years.SequenceID


texts: Oracle explain for cascading delete is wrong

texts:
My deletes in Oracle 11 are very slow. The tables are linked by foreign keys and every foreign key constraint has a delete cascade set on it.

If I hit the explain button on a statement like this DELETE FROM TOP_LEVEL_TABLE WHERE SOMETHING = 'whatever' it only shows my the involvment of the TOP_LEVEL_TABLE even if 30 other tables are involved.

How can I get a more realistic result?


texts: How can I add "in" keyword in this code to accept more than one value in gender and age parameters using SQL Server 2012

texts:
I want the parameters in Age and Gender to accept more than one value

    Create Procedure [Admission].[SelectPlayerData]

@MembershipNumber varchar(20),
@PlayerName varchar(70),
@PlayerNameEnglish varchar(70),
@Age varchar(200),
@Gender varchar(100),
@Mobile1 varchar(20),
@Mobile2 varchar(20)
as
set nocount on
begin
 Declare @BaseQuery nvarchar(max)
='SELECT        Evaluation.Players.PlayerID, Evaluation.Players.MembershipNumber, Evaluation.Players.PlayerName, Evaluation.Players.PlayerNameEnglish, Evaluation.Players.Age, Evaluation.Gender.GenderEnglish,
                         Evaluation.Players.Mobile1, Evaluation.Players.Mobile2
FROM            Evaluation.Players INNER JOIN
                         Evaluation.Gender ON Evaluation.Players.GenderID = Evaluation.Gender.GenderID'
    , @ParamList nvarchar(max) = N'@p1 varchar(20), @p2 varchar(70), @p3 varchar(70),@p4 varchar(200), @p5 varchar(100), @p6 varchar(20) , @p7 varchar(20)'
    , @WhereClause nvarchar(max) = ' WHERE 1=1';

IF @MembershipNumber IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.MembershipNumber = @p1';
    END

IF @PlayerName IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.PlayerName = @p2';
    END

IF @PlayerNameEnglish IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.PlayerNameEnglish = @p3';
    END

IF @Age IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Age in (@p4)';
    END

IF @Gender IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Gender.GenderEnglish in (@p5)';
    END

    IF @Mobile1 IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Mobile1 = @p6';
    END

    IF @Mobile2 IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Mobile2 = @p7';
    END

    SET @BaseQuery = @BaseQuery + @WhereClause;

    EXECUTE sp_executesql @BaseQuery, @ParamList,
     @p1 = @MembershipNumber,
     @p2 = @PlayerName,
     @p3 = @PlayerNameEnglish,
     @p4 = @Age,
     @p5 = @Gender,
     @p6 = @Mobile1,
     @p7 = @Mobile2;

end

the following execution does not work properly

USE [SportsActivities]
GO

DECLARE @return_value int

EXEC    @return_value = [Admission].[SelectPlayerData]
        @MembershipNumber = NULL,
        @PlayerName = NULL,
        @PlayerNameEnglish = NULL,
        @Age = N'12,14',
        @Gender = N'Male,Female',
        @Mobile1 = NULL,
        @Mobile2 = NULL

SELECT  'Return Value' = @return_value

GO


texts:
I recovered a database with last full backup, all differentials after the full, and then all log backups after the last differential.

Can I trust the database is right or do I have to do it again the right way: with full, then last differential, then all transaction backups after that?


texts: Should fields that aren't needed often be kept in separate tables?

texts:
I have a tagging system on my site. I also want to add descriptions to the tags just like on the S.E. sites.

The tags are used for a lot of stuff. Its how categories know what products to display, how users can further drill down into categories, how product attributes are set, how coupons know which products they can be applied too, etc.

Because if this I want the tag table to be as efficient as possible. The tag descriptions won't be needed for any of those tasks, I just want to add them so the people entering data can keep track of what each tag is for (if the tag name is not self explanatory)

I would like to just add a description column to the tag table, but I am worried about hurting the performance on all the other logic that doesn't need the description. I am wondering if it would be better for performance to keep them in a separate tag_descriptions table?

By the way I am using the CakePHP framework which uses ORM (and I think it selects * for model associations)


texts: structure when storing books in elasticsearch

texts:
I want to use elasticsearch as a web search engine for books. A book has several editions with different titles in different languages, ISBN's as well as author names in different languages. I want that a book is found by any combination of title language and author name language i.e. the Latin name of Aristotle and the english title of one of his works.

How do I store all possible names of the author, all ISBN's, all titles with respective edition-id's of one book and information about the language in order to get the matching title, it's edition-id and its language as a result to a query?

I believe I need to use 'Nested Type', but I am not sure.

Like that I only find the _id of the book, but not more:
{
    _id: 1
    _source: {
        title: [
            Odyssey
            Odyssee
            Odisea
        ]
        isbn10: [
            2080674722
            5941453868
            2670361734
        ]
        fullname: [
            Homer
            Omero
        ]
    }
}


texts: Query with HAVING clause is taking too long

texts:
I have query like this:

SELECT  msg.id, CONTENTS, UNIX_TIMESTAMP(msg.created_at) 'created_at',
        category, msg.`uid`,name, user_name, profile_pic, STATUS,
        isNotificationOn, onesignal_id, email, gender, phone,
        users.id userId, UNIX_TIMESTAMP(users.createdAt) 'createdAt',
        ( SELECT  COUNT(1)
            FROM  message_likes
            WHERE  uid = '006GA968vpb0O62xaOpKLuhR1cC3'
              AND  message_id = msg.id
        ) AS isLiked,
        ( SELECT  COUNT(*)
            FROM  follower_list
            WHERE  follower_uid = '006GA968vpb0O62xaOpKLuhR1cC3'
              AND  following_uid = msg.uid) AS isFollowing,
        COUNT(DISTINCT share.uid) + msg.shares 'shares',
        COUNT(DISTINCT likes.uid) 'likes',
        msg.`application`,
        `isModified`, `modificationReason`, `isVoilation`, `voilationReason`,
        `followers_count`, `following_count`
    FROM  user_messages msg
    JOIN  users  ON msg.uid = users.uid
    LEFT JOIN  message_shares SHARE  ON msg.id = share.message_id
    left JOIN  message_likes likes   ON msg.id = likes.message_id
    LEFT JOIN  followers_count  ON msg.uid = followers_count.following_uid
    LEFT JOIN  following_count  ON msg.uid = following_count.follower_uid
    GROUP BY  msg.id
    HAVING  created_at > '0'
      AND  category LIKE '%%%'
      AND  msg.uid LIKE '%'
      AND  msg.application LIKE 'demo'
      AND  isLiked='1'
      AND  msg.id <= '999999'
      AND  isVoilation <= '1'
    ORDER BY  msg.id DESC
    LIMIT  10 OFFSET 0

This will take more than 8 seconds where my data is about 20k rows per table.
But if I replace isLiked='1' with isLiked='%' it will execute within 1 second (60 - 70 ms) which is 1000 times faster. So what the mistake I am doing?

UPDATE:
After moving conditions from having clause to where clause, i am getting responce in 2 seconds.
Here is updated query:

SELECT  msg.id, CONTENTS, UNIX_TIMESTAMP(msg.created_at) 'created_at',
        category, msg.`uid`,name, user_name, profile_pic, STATUS,
        isNotificationOn, onesignal_id, email, gender, phone,
        users.id userId, UNIX_TIMESTAMP(users.createdAt) 'createdAt',
        ( SELECT  COUNT(1)
            FROM  message_likes
            WHERE  uid = 'CqITnqfe5iZHT8x74zYxuEwzGMY2'
              AND  message_id = msg.id
        ) AS isLiked,
        ( SELECT  COUNT(*)
            FROM  follower_list
            WHERE  follower_uid = 'CqITnqfe5iZHT8x74zYxuEwzGMY2'
              AND  following_uid = msg.uid) AS isFollowing,
        COUNT(DISTINCT share.uid) + msg.shares 'shares',
        COUNT(DISTINCT likes.uid) 'likes',
        msg.`application`,
        `isModified`, `modificationReason`, `isVoilation`, `voilationReason`,
        `followers_count`, `following_count`
    FROM  user_messages msg
    JOIN  users  ON msg.uid = users.uid
    LEFT JOIN  message_shares SHARE  ON msg.id = share.message_id
    left JOIN  message_likes likes   ON msg.id = likes.message_id
    LEFT JOIN  followers_count  ON msg.uid = followers_count.following_uid
    LEFT JOIN  following_count  ON msg.uid = following_count.follower_uid
    WHERE category LIKE '%'
      AND  msg.uid like '%'
      AND  msg.application = 'shayari'
      AND  msg.id <= '999999'
      AND  isVoilation <= '1'
    GROUP BY  msg.id HAVING isLiked=1
     ORDER BY likes.created_at desc

Now the glitch is if i remove order by clause it will execute in few milliseconds and with order by clause it is taking about 2-3 seconds.
Here is explain query:


texts: Postgresql configuration for tiny server and append-only data

texts:
Currently my database is runing on tiny-sized hardware (digital ocean 5$) and I want to keep this hardware.

It is:

512M memory
1 CPU

My database works with  these conditions:

Data is never deleted, only appended
Less than 1% queries are "update" and it's always affect one table
50% / 50% queries are SELECT / INSERT
I am working only with small amount of data (the last month) which is about 10k rows summary. (Previous data still in tables but application doesn't use it anymore)

What I have to tune in postgresql configuration?

For example ( if I understand it right) I don't need auto-vacuum at all.

May be there are some tiny/small/medium presets like for mysql?


texts: SQL Service Broker: "orphaned" messages in sys.sysxmitqueue

texts:
I have a database running under SQL 2016 that is using service broker to handle some business logic.  I did not design the system, I'm still not 100% sure how it works, but I definitely know that it's in a really bad state at the moment.

Someone before me had created a SQL agent job to clean up old broker conversations, I'm assuming because they're not closing them out correctly.  I noticed that this job was taking longer and longer to run each day (it selects TOP (X) rows, then does END CONVERSATION WITH CLEANUP if it's older than a couple of days and in DO or DI status).  I did some digging and looked at the service broker system tables:

sys.sysdercv:  ~268,000,000 rows, ~144GB in size

sys.sysdesend:  ~268,000,000 rows, ~20GB in size

sys.sysxmitqueue:  130 rows, size insignificant

Judging by the Enqueued Messages/sec performance counter, it looks like we create ~90 messages per second.

In the sys.sysxmitqueue table, I can see that the 130 rows that are "stuck" in there have an enqtime sometime on 7/1/2016 (this particular database was actually moved from another instance via backup/restore a few months ago, I was not part of that process, so I don't know if they missed any broker related steps).  How can I clear these out?  I was hoping that the dlgid value was a conversation handle, and I'd be able to end the conversations related to these messages.  There are no conversations for those values though.

And while I'm at it, is there any better way to clean out the old conversations than looping through them?  I've seen some people say that this is the way to go:

ALTER DATABASE  SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

However, I'm not sure if that's the best way to do it, or how long it will take given the size of our system tables, or if it will lose any messages in progress (I'm assuming it will).

Any advice you could provide would be greatly appreciated.  I've never had to deal with service broker before, and it has definitely been a frustrating experience so far.


texts: I am about to build a C# wpf application. I need to easily port the application e.g. one click installation. I am not sure should I use SQLite or MySQL db? The range of data won't run into billion at most it maybe go upto few millions? What is the best suggestion here? I will have both write and read functionality with also transaction controls for some operation.


texts: Files copying to different machine not happening from batch file executed from SQL Jobs

texts:
I am trying to execute a batch file from SQL Server 2012 JObs with the following command

cmd.exe /c "D:\Batch Files\SQLDBBkupCopy.bat".

The objective of the batch file is to copy SQL DB backup to a different machine in the network. The Job successfully ran, but there was no files copied.

From the Jobs history I could able to see this error message:

  Executed as user: SLC\sqlagentservice. Invalid drive specification  0
  File(s) copied.  Process Exit Code 0.  The step succeeded.

Here is my Batch file script:

***********Start******
@echo off
cls
echo.------------------------>>CopyLog.txt
echo.%Date% %Time%:- INITIATING FOLDERS......>>CopyLog.txt
set folder1="B:\SLCBackup\ForSymantec"
set folder2="\\testsvr\c$\SQLBKUP\SLCDBAREN"
echo.%Date% %Time%:- Initiation Complete.....>>CopyLog.txt
echo.%Date% %Time%:- Copying from %folder1% to %folder2% >>CopyLog.txt
xcopy /E /C /R /I /K /Y %folder1% %folder2%
echo.%Date% %Time%:- Complete...>>CopyLog.txt
*****End*********

However, if I try to copy in the same machine like:

set folder2="c:\test1"

it then works.


texts: How to troubleshoot occassionally slow insert?

texts:
Problem:

I'm running a pretty simple test while learning about partitioned tables.  Simply inserting rows quickly into a table.  Usually this takes 3ms or less.  Occasionally it takes 10-50ms.  Sometimes it will be a few queries in a row that take longer.  Seems random, can't notice any pattern.  I'm wondering how you would go about troubleshooting that, or finding out why?

The setup:

Database is a single table.  As said I'm learning partitioning and right now am doing it in 5min blocks based on the logtime.  This is testing a logging scenario so doing FIFO and dropping older partition tables.  So keeping 3 partitions + 1 that is actively being logged to.  Database is on a server on the LAN.  There is nothing else using this database or even accessing the server at the moment.

Table (startup):

CREATE TABLE reallog (
    datapoint_id int not null,
    logtime timestamp not null,
    value real not null
) PARTITION BY RANGE (logtime);

CREATE INDEX ON reallog (logtime);

Insert (every 20ms):

INSERT INTO reallog VALUES (...),(...),(...)..x100

Get list of partition tables (every 1s along with drop & create partition table if needed (5min)):

SELECT i.inhrelid::regclass AS child FROM pg_inherits i WHERE i.inhparent = 'public.reallog'::regclass;

C# application running on local machine.  Running a loop every 20ms that does a multi-row insert of 100 rows to the reallog table.  Using stopwatch class to time things and using npgsql library to run commands.  Timing is showing it's the execution of the insert command which is taking longer occasionally and not other parts of the program.

Sorry if this question is perhaps too vague and not specific enough.  Thanks.


texts: Plug a PDB in a new CDB without XML manifest

texts:
In oracle 12c the ALTER PLUGGABLE DATABASE "PDB1" UNPLUG INTO 'PDB1.XML' statement create the XML manifest.
Is it possible to plug a PDB in a new CDB if it isn't correctly unplugged first and the source CDB isn't active?


texts: How do I selectively purge MySQL table rows using a list of column values?

texts:
I am new to databases and working with MySQL. I have a large MySQL table with the following columns: Id, Link, Timestamp.

And I have a list of links. I want to keep only the rows with link column that matches an entry in this list.

Any pointers to how I accomplish this?


texts: XML errors while copying dependents during a redefinition to an interval partitioning scheme

texts:
I am  partitioning an existing table. I used the following process:

Checks

DBMS_REDEFINITION.CAN_REDEF_TABLE(...);

Successful

Create duplicate table

 CREATE TABLE "USER"."MYTABLE_ONLINE"
 ( "SERIAL_ID" NUMBER(15,0),
   "CONTEXTID" NUMBER(15,0) NOT NULL ENABLE,
   "MYTIME_TIME" DATE DEFAULT SYSDATE )
 PARTITION BY RANGE ("INSERTED_TIME") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
 (PARTITION "p1_1"  VALUES LESS THAN (TO_DATE('01-jan-2012','DD-MON-YYYY' )))

Successful

Copy dependencies

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(v_name,
  'MYTABLE', 'MYTABLE_ONLINE',
  copy_indexes=>dbms_redefinition.cons_orig_params,
  copy_triggers=>TRUE,
  copy_constraints=>TRUE,
  copy_privileges=>TRUE,
  ignore_errors=>FALSE, num_errors=>error_count,
  copy_statistics=>false);

And I get the following error.

ORA-06502: PL/SQL: numeric or value error
ORA-31606: XML context 27 does not match any previously allocated context
ORA-06512: at "SYS.DBMS_METADATA", line 1475
ORA-06512: at "SYS.DBMS_METADATA", line 7481
ORA-06512: at "SYS.DBMS_REDEFINITION", line 803
ORA-06502: PL/SQL: numeric or value error
ORA-31606: XML context 27 does not match any previously allocated context
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1869
ORA-06512: at line 6
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:
*Action:

How can I fix this?


texts: 500GB Data Warehouse - how to split it into FileGroups?

texts:
OK, so I have:

500 GB DW
5 GB in smaller DBs
220 GB TempDB
350 GB in Log files.

My machine is Fujitsu Primergy 64 cores (with HT) and 192 GB RAM.

I have several IO locations:

540 GB in-server HDD 15k RAID10
1 TB HDD 15k RAID10 on SAN (separete controller)
2 TB HDD 15k RAID10 on SAN (same controlller as below)
800GB SSD RAID10 on SAN (same controller as above)

Data warehouse has 2 fact tables that are absolutely crucial and quite large.

Now i want to organize DB into several Filegroups and put them on different drives. Filegroups I'm thinking of:

FILEGROUP1: for 1st crucial Fact Table
FILEGROUP2: for 2nd crucial Fact Table
FILEGROUP3: for tempDB
FILEGROUP4: for dimensions data
FILEGROUP5: for the rest of facts data
FILEGROUP6: for dimensions indexes
FILEGROUP7: for the rest of facts indexes
FILEGROUP8: for 1 log file of one smaller DB (its in full-recovery and its quite large)
FILEGROUP9: for the rest of log files
FILEGROUP10: for source tables
FILEGROUP11: others

How should I organize them across available drives? I was thinking about sth like:

800 GB SSD: FILEGROUPS 1-3
2 TB RAID10: FILEGROUPS 5+7+8
1 TB RAID10: FILEGROUPS 4+6+10+11
540 GB in-server: FILEGROUP 9

I know that having multiple filegroups on the same drive is pointless regarding performance, but in future i could actually add some more drives, so i want to separate them now.

Also - how much files per filegroups should i create? Considering 1 or 2. Except TempDB where im going for 4.

Any thoughts? Thx in advance!


texts: Maintenance plan is backing up database transaction log but it is not selected

texts:
I have a strange problem, the transaction log for my database is being backed up however it is not selected.

I change the names of the server and databases in the screenshots to protect my information from the bad guys. ;)

Here is the issue that server "XYZ" is having, the screen shot below is showing the sub-plan for the "Transaction Log Backup" maintenance job. As you can see here "Database2" is showing up in the plan task.

However in the properties plan task "Database2" is not selected. In order for the transaction log to be backed up the database has to be selected, like "Database1", right?

Despite the fact that "Database2" is not selected the transaction log is being backed up. I verified the properties of the database when the last transaction log was done.

So, I am a little confused about this and would like to know if anyone else here came across this and how you resolved it.

Server specs: SQL Server 2008 SP2 running on Windows Server 2008 R2 Standard


texts: Distinct result with row_id for limit query

texts:
There are 2 tables:

CREATE TABLE sample1 (
  id int,
  name varchar,
  score int,
  mode int
)

CREATE TABLE sample2 (
  id int,
  mode int
  ...
)

I need to select distinct name with MIN(score) and ROW_NUMBER(). Must sort by ROW_NUMBER() ASC. The mode must exist in table sample2 for a row from sample1, so there is INNER JOIN.

ROW_NUMBER() is needed for sequential LIMIT queries so I can select data in batches. I don't care about consistency of that LIMIT+ROW_NUMBER() trick.

My query:

WITH sample1 AS (
    WITH sample1 AS (
        SELECT a.name, MIN(a.score) s
        FROM sample1 a
            JOIN sample2 USING (mode)
            GROUP BY a.name
    )
    SELECT *, ROW_NUMBER() OVER (ORDER BY s ASC) AS n
    FROM sample1
) SELECT * FROM sample1 WHERE n > $1 LIMIT $2

I am concerned about efficiency of that select, in fact it's 3 level deep subquery. Perhaps there are less ugly and more efficient ways to achieve same result?

The number of rows in this table will be approximately 1,000,000 and distinct set is expected to be 100,000. Queries are frequent, actually it's a hot table for select, only score column will be updated in batches of the same size as limit select.


texts:
After delving into Autogrowth and the best practices surrounding it (primarily being proactive and adjust the file group sizes ahead of time), there was one looming question that I did not seem to find a thorough answer to. Most articles, including TechNet, always mention the practice of setting the Autogrowth to 10-15% as a fail safe. There seems to be minimal mention of utilizing the MB growth option as a fail safe.

Say for instance you have a current database size of 500GB with a file growth of approximately ~1GB/day, give or take a couple hundred MB. Each weekend, you adjust the necessary file groups by X amount to cover the growth - say boosting it to 540GB to cover nearly a month's worth of growth. In the event that you fail to adjust it once it reaches 540GB (simply forgot, unexpected table expansion, etc.), MSSQL kicks into action and attempts to grow the database by 15% but ends up on a continual time out because the growth amount is too large for the server to handle automatically. This causes application timeouts, and thus manual intervention is needed regardless.

I bring this up because in testing, Autogrowth by percentage seems to offer for timeouts in these test cases. However, if I set the Autogrowth to a set MB, for instance 1,000MB, it grows without any issue and does so in a timely manner.

So to summarize my question, why do articles (at least the ones that I have read) heed the practice of utilizing Autogrowth by percentage instead of Autogrowth by MB in the event proactive manual file growth has not occurred? It seems to me in testing and in theory, you'd prefer Autogrow to adjust by a set MB amount.

Am I false in this thinking, or am I just reading the wrong things? It'd be great to hear what type of administrative techniques you guys utilize here.

Thanks!


texts: How do I create a MySQL slave server of a bunch of MyISAM databases on a master

texts:
I have a MySQL master server and slave became corrupt because of space issues on the master. Binary logs are potentially corrupt and I don't trust them. The databases are using MyISAM tables.

I want to create a new slave, but I can't afford to take the master down or lock the tables for a mysqldump.

Is there a way I can get a known place on the master to seed the slave without creating an outage? If not, what is the way to do this with the briefest outage?


texts:
What measurement do you know if this sql code is efficient based on execution plans?
How do you know if this sql code is efficient based on execution plans?

SELECT [CustomerID], [CompanyName], [City], [Region]
FROM [Northwind].[dbo].[Customers]
WHERE [Country] = 'Germany'
ORDER BY [CompanyName]

SELECT [CustomerID], [CompanyName], [City], [Region]
FROM [Northwind].[dbo].[Customers]
WHERE [Country] = 'Germany'