texts: Test password complexity without creating a user

texts:
Is there a way to check if a particular password string passes sql server/windows password validation/complexity rules (that is be valid for creating a user) using SQL but without creating a user ?

Thx!


texts: Catch event of auto_shrink in SQL Server Log?

texts:
I am aware of the disadvantages of AUTO_SHRINK being configured on SQL Server databases. Recently, in a new environment, I discovered several production servers with the Auto Shrink option turned on.

Now, before I change this configuration, I wonder: Since the auto_shrink event starts at 25% unused space I wonder if and/or when this is actually happening on the SQL Servers. I  tried to find the relevant SQL-Event-Logfile entries. I started manual shrinking on test databases but there is no Event-Log entry showing up.

Does anyone know how to find out when shrinking has happened within the latest days or weeks on a SQL Server?


texts:
Is there a "best" way to automatically restart SQL Server on a regular basis? I read that I could create a batch file to net start, net stop the agent and service in a batch file and schedule that, but I was curious if there was a better way of doing this.

Please nevermind how this is indicative of a larger problem of operation, I've been arguing this for a month and we've ended up here.

Thanks :).


texts: Verify failed SQL Server connections to instances on a given server and email them

texts:
I am trying to monitor the SQL Server instances on standalone server / cluster server. I would like to setup something from commandline to check for connectivity to each SQL Server instance on the server. Wherever it fails to connect to an instance I would like to have an email (we have smtp server setup) sent to our DBA team.

Each server is having more than 5 instances and we are unable to track them without customer complaining about connectivity, hence trying to be proactive.

Can we try something like osql or sqlcmd?


texts: What permission should be enabled to create user?

texts:
What permission do i need to enable to a user to be able to create other user's ,role's .
I tried giving him the role db_securityadmin still didn't work.


texts: set default datatype = char (instead of varchar)

texts:
I'm setting up a database in SQL-2014 that is exchanging data with a mainframe. All the fields are char, so there are trailing spaces. In table designer, my default field type is varchar.

Is there a way to change this one database so the default field type will be char as I create new tables?


texts: Check all values in a set with PostgreSQL

texts:
I'm trying to validate jsonb data with a check constraint to a table that has two fields, header and body, both are jsonb fields, that I expect to be arrays.

Here is an equivalent table:

CREATE TABLE "StoredReports" (
    id serial NOT NULL PRIMARY KEY,
    name text NOT NULL UNIQUE,
    header jsonb NOT NULL CHECK (jsonb_typeof(header) = 'array'),
    body jsonb NOT NULL CHECK (jsonb_typeof(body) = 'array')
);

The values at every index in body should also be arrays of the same length as the header. And I'm trying to enforce that with a CHECK constraint.

This query gives me the correct results for individual rows.

SELECT jsonb_array_length(header) = jsonb_array_length(jsonb_array_elements(body)) FROM "StoredReports"

I've tried to use array_agg to group it all and then use ALL, however it looks like it doesn't work with functions that return sets

I can do it with a sub select like this:

SELECT bool_and(t.x) FROM (SELECT jsonb_array_length(header) = jsonb_array_length(jsonb_array_elements(body)) AS x FROM "StoredReports" WHERE id = 1) t

But check constraints don't allow for sub selects.

Is there a way to create this constraint without creating a function or trigger?

Edit:

To clarify, I would like to ensure that all child arrays of the body field, have the same length as the header.

Example

With this being in the header field:

[
    { "name": "header1" },
    { "name": "header2" }
]

I would expect the following to be insertable in the body field

[
    ["field1", "field2"],
    ["field3", "field4"],
    ["field5", "field6"]
]

However this should fail, as it doesen't have length 2 on the second element of the array:

[
    ["field1", "field2"],
    ["field3"],
    ["field5", "field6"]
]


texts: Poor performance filtering mysql spatial join

texts:
First of all, sorry for the crossposting. I ask this question in stackoverflow and they sent me here

I have a mysql table with spatial references (GEOGRAPHIC_FILTER), like cities, countries, natural parks, etc, each with a GEOMETRY field. I use this table to "filter" other feature tables with spatial entities (hiking tracks, mtb tracks) by spatial clauses from the geographic filter name (f.e. find hiking trails near paris (france)). But Im getting very poor performance when I try to refine this spatial queries with alphanumerical clauses.

First table: 130.000 records

CREATE TABLE `GEOGRAPHIC_FILTER` (
    `pk_id` INT(10) NOT NULL AUTO_INCREMENT,
    `name` TEXT NULL,
    `friendly_url` TEXT NULL,
    `type` TINYTEXT NULL,
    `bbox` GEOMETRY NOT NULL,
    `country_code` VARCHAR(2) NOT NULL,
    `country_name_es` TINYTEXT NOT NULL,
    `simple_name` TEXT NULL,
    `weight` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`pk_id`),
    SPATIAL INDEX `bbox` (`bbox`),
    FULLTEXT INDEX `name` (`name`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

Second table: 150.000 records.

CREATE TABLE `hiking_trails` (
    `PK_ID` INT(10) NOT NULL AUTO_INCREMENT,
    `BBOX` POLYGON NOT NULL,
    `COUNTER` INT(11) NOT NULL,
    `FRIENDLY_URL` TEXT NOT NULL,
    `USERNAME_FK` VARCHAR(100) NOT NULL,
    `PRODUCER` TEXT NULL,
    `SERVICE_URL` TEXT NULL,
    `TITLE` MEDIUMTEXT NOT NULL,
    `MAP_TYPE` TINYTEXT NOT NULL,
    `DESCRIPTION` TEXT NULL,
    `SERVICE_LOGO` TEXT NULL,
    `FK_MAPTYPE_ID` INT(11) NOT NULL,
    `PLAIN_TEXT_BODY` LONGTEXT NOT NULL,
    `AREA` DOUBLE NULL DEFAULT NULL,
    `CATEGORY` TINYTEXT NULL,
    `LANG` VARCHAR(2) NULL DEFAULT NULL,
    `MAIN_CATEGORY_ES` TINYTEXT NULL,
    `CATEGORY_EN` TINYTEXT NULL,
    `MAIN_CATEGORY_EN` TINYTEXT NULL,
    `WIKIPEDIA_CATEGORY` TINYTEXT NULL,
    `LINE_LENGTH` DOUBLE NULL DEFAULT NULL,
    `MIN_H` SMALLINT(6) NULL DEFAULT NULL,
    `MAX_H` SMALLINT(6) NULL DEFAULT NULL,
    `AVG_H` FLOAT NULL DEFAULT NULL,
    `AVG_ASPECT_UP` FLOAT NULL DEFAULT NULL,
    `AVG_ASPECT_DOWN` FLOAT NULL DEFAULT NULL,
    `HARD_INDEX` FLOAT NULL DEFAULT NULL,
    `ELEVATION_PROFILE` LONGTEXT NULL,
    PRIMARY KEY (`PK_ID`),
    INDEX `FRIENDLY_URL_INDEX` (`FRIENDLY_URL`(333)),
    FULLTEXT INDEX `PLAIN_TEXT_INDEX` (`TITLE`, `DESCRIPTION`, `PLAIN_TEXT_BODY`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

Now, Im trying to get 'hiking_trails' records near a given geographic reference (for example "Paris (Francia)" ):

This query runs in 0.2 secs

SELECT SQL_CALC_FOUND_ROWS t.FRIENDLY_URL, t.AREA, t.SERVICE_URL, (
X( Centroid( t.BBOX ) ) - X( Centroid( g.BBOX ) )
) * ( X( Centroid( t.BBOX ) ) - X( Centroid( g.BBOX ) ) ) + ( Y( Centroid( t.BBOX ) ) - Y( Centroid( g.BBOX ) ) ) * ( Y( Centroid( t.BBOX ) ) - Y( Centroid( g.BBOX ) ) ) AS Square_Dist, t.TITLE, t.DESCRIPTION, t.PRODUCER, t.MAP_TYPE, t.PK_ID, t.LINE_LENGTH, t.MIN_H, t.MAX_H, (
t.MAX_H - t.MIN_H
) AS DESNIVEL, t.AVG_H, t.AVG_ASPECT_UP, t.AVG_ASPECT_DOWN, t.HARD_INDEX
FROM rutas_senderismo AS t
JOIN GEOGRAPHIC_FILTER AS g ON MBRIntersects( t.BBOX, g.BBOX )
WHERE g.name = 'Paris (Francia)'

But if we add another filter (trails length, order by trails length, etc) this query runs in more than 30 seconds:

SELECT SQL_CALC_FOUND_ROWS t.FRIENDLY_URL, t.AREA, t.SERVICE_URL, (
X( Centroid( t.BBOX ) ) - X( Centroid( g.BBOX ) )
) * ( X( Centroid( t.BBOX ) ) - X( Centroid( g.BBOX ) ) ) + ( Y( Centroid( t.BBOX ) ) - Y( Centroid( g.BBOX ) ) ) * ( Y( Centroid( t.BBOX ) ) - Y( Centroid( g.BBOX ) ) ) AS Square_Dist, t.TITLE, t.DESCRIPTION, t.PRODUCER, t.MAP_TYPE, t.PK_ID, t.LINE_LENGTH, t.MIN_H, t.MAX_H, (
t.MAX_H - t.MIN_H
) AS DESNIVEL, t.AVG_H, t.AVG_ASPECT_UP, t.AVG_ASPECT_DOWN, t.HARD_INDEX
FROM rutas_senderismo AS t
JOIN GEOGRAPHIC_FILTER AS g ON MBRIntersects( t.BBOX, g.BBOX )
WHERE g.name = 'Paris (Francia)'
AND t.LINE_LENGTH < 25000

Can I optimize this query?


texts: Getting Max for partition column in clustered columnstore index

texts:
SQL 2014 Enterprise
I have a datawarehouse table with ~ 800 million rows (total table size ~25 GB)
this table is partitioned by the import_date column (on daily basis)
it has a clustered columnstore index on it
when I execute a simple SELECT MAX(t.import_date) FROM stat.t_auftragsstatistik t it does an CLUSTERED COLUMNSTORE INDEX SCAN over ~150 million rows / 33156 pages
you can find the execution plan at https://www.brentozar.com/pastetheplan/?id=ryuskalug

On the other hand, when I SELECT COUNT(*) FROM stat.t_auftragsstatistik AS ta WHERE ta.import_date = '20170201' (which is the most recent date) it returns me ~375k rows and needs only 6 page read to gather this information.

Question:

why is the Max() so slow (~7 seconds) and reads so much data (with nonclustered index on import_date it would do a simple index scan in reverse order and stop after the first match (-> 3 reads))
why does it ignore the partitioning (even if I would partition on e.g. weekly basis, it would have only to scan the latest partition with at least one row)


texts: Does write stalls on separated MDF file affect performance

texts:
My user database MDF file resides on separated disk. There is nothing else on the disk than this MDF file. I can see average write stalls higher then 20ms on the file using appropriate DMV. I assume that writes are only made assynchroniously by checkpoints or lazy writer.

I doubt whether there is any direct negative impact on the workload performance because of these write stalls? Or is there any good reason we should try to increase write performance of the disk?


texts: SQL Server 2008 R2 SQL Server has encountered x occurrence(s) of I/O requests taking longer than 15 seconds to complete on file '

texts:
When I alter or create a stored procedure directly on production or QA database, after a few seconds I start experience timeouts and application becomes unavailable.

Log files shows this error:

  SQL Server has encountered 3 occurrence(s) of I/O requests taking
  longer than 15 seconds to complete on file [C:\Program Files\Microsoft
  SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\QA_Database.ldf] in
  database [QA_Database] (9).  The OS file handle is 0x0000000000000568.
  The offset of the latest long I/O is: 0x0000002821a200

We have SQL Server 2008 R2 installed, including latest Service Pack.

I tried to reproduce it on QA, but to no avail. I have no clue what it could be.

Stored procedure execution time is around 1 second and it runs occasionally. It returns just few records. It happened with 2 different stored procedures and I think that changing, not running, a stored procedure triggered something that caused the issue.
Once I deleted it, issue was gone immediately.


texts:
I would like, from TSQL, to use a view to extract data from a table, output it with BCP, then use non 3rd party tools to ZIP it with a password and AES encryption. Optionnally, I could use a 3rd party tool that doesn't require a local installation (standalone EXE on a network drive for example).

I tried using sp_OA*, but found this post saying they were buggy, memory-leaky and deprecated features. So I am looking for alternatives, if there are any. Google searches all came back to the same solutions: 7zip, Winrar, or sp_OA*.

I don't want the code, just the name of the features or methods that could help me, so I would know what to search for.

MSSQL2008 and up. Can be a Powershell solution. Not SSIS if possible. For the record, it's to daily update a software with current data, as it doesn't have direct access to the source.

If 7zip is the best/only way to go, I'll have a ton of paperwork to fill in and meetings to attend to justify installing non-database software on a SQL server. :-)


texts: Comparing Tool for Reporting Services Objects

texts:
I have three Reporting Services Instances (Server), all in version of SQL-Server 2017.

All instances have the same tree structure with hundreds of objects (datasources, datasets and reports)

Now I'm looking for a tool which compares the same objects between two instances. I already use the redgate comparing tools but they do not cover reporting services.

Any suggestions?


texts:
I have mongodb (wired tiger) with two shards.
I added third shard and enabled the balancer.

I can see the logs where the data chunks are getting moved to the new shard.
After the movement of chunk where the disk space of old shards( from which data is moved) will be returned to the OS or compact command has to be executed.


texts: How to generate this MongoDB diagnostics HTML report?

texts:
I'm seeing this kind of report in many JIRA bugs and I want to generate it for my mongo instance.

What software was used for generating it?


texts:
I having problems from first steps, while migrating Oracle Forms from 10g to 11g. Some of libraries dose not want to compile. the compiler just hangs up when I try to compile them, and Forms builder hangs up when I try to open them. Error signature

AppName: frmcmp.exe  AppVer: 11.1.2.1    ModName: orapls11.dll
ModVer: 11.1.0.7     Offset: 00002541

I checked libraries relations, everything looks fine. Does anybody had similar problems?


texts:
I have 3 tables:

Device: Id, Name.

Machine: Id, P1, P2, date_time, IdDevice.

Parameters: Id, T, V, I, date_time, IdDevice.

In device there isn't a lot of records. But in the other way, inside Machine and Parameters every minute is inserted a new record. So right now we can find 1M records and the next year we expect 20M records inside of each of this 2 tables.

IdDevice and date_time for both tables are indexed.

How can I improve the performance?

For example for this sentance it's taking 10 min and it isn't done yet.

SELECT p.T, p.V, p.I, m.P1, m.P2, p.date_time
FROM Device d
INNER JOIN Parameters p ON d.ID = p.IdDevice
INNER JOIN Machine m ON d.ID = m.IdDevice
GROUP BY d.ID
ORDER BY p.date_time DESC


texts: Create an SSL connection with ODBC driver to MySQL(5.6) using openSSL

texts:
I want to create an asymmetric SSL connection from MySQL to an ODBC driver but don't fully know how to generate the keys and certificates (I'm using openSSL as recommended here: https://dev.mysql.com/doc/refman/5.6/en/creating-ssl-files-using-openssl.html ).

I've been following this post to try and create the keys

https://stackoverflow.com/questions/5244129/use-rsa-private-key-to-generate-public-key

It seems like the key generation works but I don't know where or how the keys are stored to use them with the ODBC driver.

Also it may be helpful to know that I am generating these keys on the server host machine.


texts: Steps after restoring to a different server

texts:
We have a 30 GB Database on SQL Server 2008 R2 Standard Edition. Yesterday we backed up the database and restored it to a different Virtual Machine which has exactly the same specs as the previous machine - The only exception being the new VM has a lot more RAM.

However we found that the performance of the older VM was much better than this new VM. My questions are:

Should we re-create / refresh all the indexes in the Database after such a restore ? If so then is there a good way to refresh all indexes with ease (as opposed to one index at a time).
Is there any other obvious step which we are missing - We don't have an in-house DBA as the work we do on the database front is not much.


texts:
What is the MySQL service name?

Get-Service | where{$_.name  -like "*MySQL*"}

Is that correct?


texts:
Is it possible to whitelist access to a specific table (for example containing password hashes plus salts)?

My ideal situation would be zero permissions for anyone with access to insert, update, delete and select achieved through stored procedures.

How do I go about setting this up?


texts: Return a default value in group by if selected column is null

texts:
I am trying to run a group by statement which works fine however, one of the "group" is null.  How do I return "Unknown" for that row?

For example:

    10000
A   9999
B   9990
C   4

etc.

First line I'd like to return as "Unknown" in the query.


texts: What are the low hanging fruits of a SQL Server 2014 migration?

texts:
I have read about new features and changes of the SQL Server 2014 version. But I still don't know the benefits every server will have by upgrading (from 2008 R2) to 2014?

Are there any new features / functionality (low-hanging fruits) every instance and database will benefit of immediately, without a single change to the database schema?


texts: Using MySQL active-passive with RBR DDL without breaking replication?

texts: new to RBR and am wondering how to remove a column in the middle of a table in an active-passive setup without breaking replication? Pre-RBR I would just have run the alter on one side and then failover writes. Running Percona 5.6 server in a scaled out environment.


texts: Why can't I alter a security policy conditionally?

texts:
I have the following rather simple statement in SQL Server Mgmt Studio 2016 CTP 3.2:

if exists(select null from sys.security_policies where name = 'OrganisationSecurityPolicy')
begin
    alter security policy [security].OrganisationSecurityPolicy
    with (state = off)
end

A security policy with that name does indeed not exist so nothing should happen. However, I get an error:

Msg 33268, Level 16, State 2, Line 3
Cannot find the object "security.OrganisationSecurityPolicy" because it does not exist or you do not have permissions.

Is there something special about security policies that makes this impossible? If I replace the alter security policy with an alter table for a non-existing table, the statement simply isn't executed.


texts: Tracking errors from prepared SQL statements

texts:
I use extended events to store database errors, like this:

CREATE EVENT SESSION [ErrorCapture]
ON SERVER
ADD EVENT sqlserver.error_reported
(
    ACTION
    (
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.sql_text,
        sqlserver.username
    )
    WHERE
    (
        [severity] >= (11)
    )
)
ADD TARGET package0.asynchronous_file_target
(
    SET filename='J:\ServerXmlOutput\ErrorCapture.xel'
)
WITH
(
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=10 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
);

This helps me to easily trace which application is causing them.
I have something similar to track slow queries and other special cases.
However, I noticed from the SQL Server Profiles that a PHP/Laravel application is not sending some statements directly, but using RPC:Completed: they look like prepared statements, receiving the argument at run time.
How do I expand my ErrorCapture to include errors caused by statements executed via RPC:Completed?


texts: SSRS- database and Report Server - can they be different?

texts:
I have inherited a few database servers. I am currently planning for a Server upgrade, but this DB server has a ReportServer and ReportServerTempDB database in it.
When I open Reporting Service configuration Manager I am not able to connect to the above mentioned database server.

Is it possible that the Reporting Server for the above databases is configured in a different server other than the database server? How should I go about identifying these for further information.

I am asking because I have to move the ReportServer and ReportServerTempDB but I am not able to access the ReportServer Configuration Manager for  the encryption keys.


texts: Importing a large mysql dump while replication is running

texts:
So we have a simple master/slave mysql replication running between two CentOS Servers.

The master has multiple databases. eg..
Database1
Database2
Database3

The issue is we have a mysql dumpfile of a new 60GB database (Database4).

What's the best way to import Database4 without breaking replication?

I was thinking we could stop replication, and import the mysqldump onto both master and slave. Then restart replication, but was hoping there was an alternate way that would minimize downtime.


texts:
https://www.brentozar.com/archive/2013/04/cloud-alternatives-to-microsoft-sql-server/

I liked the idea of VMware vFabric Data Director so I can host data in own cloud / data centre. It seems this product is now end of life.

Do you know of any similar products still available in the market? I was looking for ways of reducing our licencing overhead with the costs of SQL server increasing, I also like the concept of database as a service taking away DBA bottlenecks in projects.


texts: Spliting tables with *almost* same values

texts:
I have different kind of documents which have common fields (not all of them, but most) and I thought to put them in the same table and use ENUM to differentiate them.
Then I realized that if have thousands of docs of every kind I will have a (very?) slow SELECT/UPDATE's right?

So my question is: should I split the tables and have one table for each document? or is the initial approach the way to go?
Thank you


texts: How to know the problem when i make this Full Backup Scenario?

texts:
i'm working new on database administrator and i have some questions
if i want to make SQL full backup database every Saturday at 6:00 AM
and for some reasons the device was shutdown
the questions are :
whats the issues for my backup ? is it rollback ?
how can i know that was happened , when its not the time of work ?
what the a good plan to be sure to take the backup every week with out problems


texts: Is it possible to configure Reporting Services to emit browser compatibility headers?

texts:
We have a dashboard that hosts some Reporting Services (2008) reports within frames. The dashboard runs in the browser's standards compliance mode, but this is causing some of the reporting services reports to break in unexpected ways; for example, a table of static text isn't maintaining the column widths, and embedded images aren't being sized correctly.

The solution appears to be to add either a real HTTP header (X-UA-Compatible: IE=7) or as a meta tag within the page. Is it possible to configure Reporting Services to add this header?


texts:
Actual database in localhost:30000 ------------- I used this as Shard1
  another mongod  in localhost:30001 ------------- Shard2
  another mongod  in localhost:30002 ------------- Shard3

Added 1 config server and Router pointing the configdb

Now in Router,

1. mongo admin
2. sh.addShard("localhost:30000")..30002           //Added 3 shards successfully
3. sh.enableSharding("myDatabasename in shard1")  //Enabled successfully
4. sh.shardCollection(...)                       //Shard Collection successfully

I checked each shards and I am able to see that it is split-ed in ascending order like

shard key starting with A-D in shard2 as chunk 1
shard key starting with E-H in shard3 as chunk 1
shard key starting with I-O in shard1 as chunk 1
shard key starting with P in shard1 as chunk 2               //had upto P only

Now I need to insert a new record means in which mongo instance I have to insert?

I tried in shard1 and inserted a new record with sharding key starting with E, which is getting inserted in shard1 itself whereas it has to be inserted in shard3.


texts: TPC-DS query generator does not generate all queries

texts:
I have downloaded the DSGEN tool from the TPC-DS web site and already generated the tables and loaded the data into MS SQL server.

I am using the following command to generate the SQL statements(on windows):

dsqgen /input ../query_templates/templates.lst /directory ../query_templates /dialect sqlserver /scale 1

The command gives a single query_0.sql file as output and it has only 22 queries out of 99 queries. Also in the command prompt, an error is shown as given below:

RUNTIME ERROR: Distribution over-run/under-run
Check distribution definitions and usage for cities
index=-1, length=1000

Can anybody help?


texts: MySQL: Why queries with subqueries are much faster than single query?


texts: SSRS 2008R2 SQL Service Account Connection Issue

texts:
In our SSRS report manager page we get the below error on the Datasources. This all worked fine previously and seems to suddenly have stopped working. I have no idea what the issue could be.

All SPN are set up and the rsreportserver config file contains correct authentication...

    <AuthenticationTypes>
        <RSWindowsNegotiate/>
        <RSWindowsKerberos/>
        <RSWindowsNTLM/>
    </AuthenticationTypes>

In the SQL Server logs I see this error...

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error.

I followed the checklist listed here for SPN - http://blogs.technet.com/b/taraj/archive/2009/01/29/checklist-for-double-hop-issues-iis-and-sql-server.aspx but still not fixed the issue.

What is causing this issue??


texts: Create function in central database or repeat in each database?

texts:
One of my developers has written a SQL function that works like the VB.Net function (LastIndexOf) and wants to publish it. My question is what would be the reason to put this in a central database versus putting it in each user database?

The developer was trying to put it in sys schema on his master db so he wouldn't have to qualify calls to it from user databases... sigh

But I wasn't sure what the valid excuse would be to centralize it (obviously not master database) versus each user database?


texts:
looks like i do it wrong. I turned auto_explain on with adding next valued to postgresql.conf:

shared_preload_libraries = 'pg_stat_statements,pg_wait_sampling,pg_stat_kcache,auto_explain'
auto_explain.log_min_duration = '3s'

in my monitoring I can see long running queries that exceeded limit of 3s and successfully finished.

'values' here says how long query lasted:

But when I look into my log 'postgresql-Mon.log' (and next days) there is nothing about those queries. Only a few strings about modules during database startup process.

Am I missing something?


texts: Debug why MySQL/mariadb table is crashing

texts:
I have a MyISAM table that keeps crashing. How would I figure out why it is crashing? The error messages say nothing other than that it crashed. Is there a log setting I could activate?

This is mariadb 10.0.17 on Arch Linux.


texts: Refresh materialize View fast on commit multiple table

texts:
In a  materialized view with refresh fast on commit i have the below query

select pp.ROWID AS PP_ROWID,aup.rowid AS AUP_ROWID,
       pp.hierarchicode,pp.id, aup.userid
       from app_useracess aup, core_organization pp;

when the app_useracess changes the materialize view change the data with fast method but when a commit occure on core_organization the materialize view does not refresh , unfortunately even i refresh it with this statement :

DBMS_SNAPSHOT.REFRESH( 'CORE_POWER_AUTHORIZE_ALLOW','F');

it does not refresh , and when i refresh it completely with C as a secound parameter it sense the data changes .
is it possible to refresh the materialize view with fast method and on commit on the core_organization  table ??

update :
the MV DDL is :

create materialized view CORE_POWER_AUTHORIZE_ALLOW
refresh fast on commit
as
select pp.ROWID AS PP_ROWID,aup.rowid AS AUP_ROWID,P.ROWID AS P_ROWID ,
pp.hierarchicode,pp.id, aup.userid,p.id as pID
  from core_power p ,app_userspower aup, core_power pp
 where p.id = aup.powerid
 and pp.hierarchicode like p.hierarchicode || '%';

i also use  dbms_mview.explain_mview and the result is :

REFRESH_FAST_AFTER_INSERT       Y                   REFRESH_FAST_AFTER_ONETAB_DML   Y                   REFRESH_FAST_AFTER_ANY_DML      Y
REFRESH_FAST_PCT                N           PCT is not possible on any of the detail tables in the materialized view

and there is so funny that the materialize refreshed base on this query :

select e.LAST_REFRESH_TYPE,e.LAST_REFRESH_DATE from dba_mviews e where e.mview_name = 'MV_NAME';

but the count(*) from my MV does not changes .


texts: Running Excel addin for MDS2019 vs MDS2016

texts:
We are upgrading master data services (MDS) of SQL2016 to SQL2019.Is there a way to run excel 2019 addin for connecting to SQL 2019 MDS along side(parallely) with excel 2016 addin for connecting to MDS 2016 on user's desktop machine so that same user can connect to MDS 2019 with excel 2019 and MDS2016 with excel 2016 simultaneously?.The reason we want to run parallelly is we doing a migration for UAT environment to MDS2019 and we don't want to restrict their access to MDS 2016 till the time they are on prod.
We are open for any solution like techniques (if any) from desktop virtualization perspective or anything from SQL...Appreciate all the responses
-Kev


texts:
Currently on one of our dev servers we log everything what happend in database, but lots of traffic in is generated by patroni cluster and is not needed in further investigation. Is possibile to exclude application from postgres logs?

Something like "log from every application except 'patroni' and 'postgres'".

application_name="all -postgres -patroni"

Yes, i know, i can filter it later, but will be nice if this can be done before write anything to disk.


texts: Table Partitioning and updates to partition subset

texts:
I am partitioning a large table (2 Billion records ) on an integer say AssetID that has 70,000 unique values, due to partitioning limitation of 15,000 I will create a partition on say 10,000 values using ranges. Each asset will have several thousands of records.

I have a staging table that updates a subset of assets every day. And currently, we are deleting assets and re-inserting them.

If I was able to partition by Asset ID then I could simply swap the partition, but since I am partitioning by asset range it gets a bit more complicated.

Each day I update approximately  2% of the data. Updates can happen on any asset in the dataset.

What is a recommended approach to updating a subset of data in the partition?
Say that I have an update for an asset in patition 10.

Do I have to switch out the partition 10 to a new staging table and perform updates there and then switch it back in?
What would the advantage of this be as compared to updating data directly?


texts: How to resolve foreign key references across different databases (iSeries and SQL Server)

texts:
I've got a system that has a DB2 iSeries database. We are rewriting a portion of the system and implementing it using SQL Server and Entity Framework.

Originally, I was lead to believe that there wasn't much of a tie-in between the tables we are moving to SQL Server and the DB2 ones. However, I'm finding a few spots where there are foreign-key references across them.

Are there any standard ways of handling this kind of scenario, where we need to maintain foreign-key references across databases? I can think of a few options:

Duplicate some of the data from SQL Server to DB2 on iSeries, enough to satisfy the foreign key constraints. I hate this idea not only because of the duplication of data but also because if we only duplicated the necessary data it would only exist to satisfy a constraint, but we couldn't do anything useful on it like JOIN to it.
Remove the foreign key constraints from the iSeries and completely unlink the two databases. This has obvious issues, but thought I would throw it out there in case there are any benefits that I'm not seeing.
Use some kind of database link to keep the two in sync. I know not how this would work, how it would perform, or even if it is possible to have foreign key constraints across a link. Perhaps this would need to be done with a trigger as I imagine a regular foreign key reference would not work in this case?

Does anybody see any other options, and what would be the best approach?

I don't believe there is anything on the DB2 side that would ever need to read/write anything to the SQL Server side, or vice versa, so we could keep the databases accesses from the application separate. I believe it is just to keep the foreign key references.


texts: How do you restore a particular table in MySQL?

texts: One of our users has deleted a table by mistake. How can I restore only that table?


texts: Select products for an order via check boxes in Access 2010

texts:
I have a lookup table where I have defined some products. I have another table that defines orders. This table defines which products is in the order by having a ID that is linked to another table that has a link to the orders table and to the products lookup table.

Lookup products:

ID: 1
PName: Basketball

ID: 2
PName: Football

Orders:

ID: 1
Products: 3

ID: 2
Products: 5

Products:

Products: 3
PID: 1

Products: 5
PID: 1

Products: 5
PID:2

So in the orders table, one can see that Order 1 has ordered a Basketball, while Order 2 has ordered both a Basketball and a Football.

Now, when you place an order in the Place Order-form, I want the user to be able to choose between the available products, and let them pick them from a list of checkboxes. How is this possible? (When the shop manager adds another product, it is automatically added to the list.)

Is this the correct approach to my problem, or is there a better solution to this?

Edit: I found a solution to this. One could use a list you can select the product, and press an arrow button to add it to another list. The same way you choose fields when making a combo box.


texts: Database design. And get last modified time with each column in PostgreSQL

texts:
My database/table each endpoint/scope  have multiple table,
e.g user:  user, user_information, user_role ...
I was wondering should I separate table like below, or not?

This is my first time to build a product not like before just make some onetime  work small website. and this it might add more feature in future version.

I'm not sure about is this overdesign?  Does separate table like this any benefit in the future ??
and cons I know is use more table join, and more difficult to me to maintain build the query.

any suggestion, share experience will be really appreciate.

For now I only can imagine I might want to know each column last modified time?

So if I want to know each column last modified time, is there any original build method in PostgreSQL? or I have to add for each column like email_last_modified_date, username_last_modified_date ...

endpoint/scope user

CREATE TABLE IF NOT EXISTS "user"(
    "id" SERIAL NOT NULL,
    "create_date" timestamp without time zone NOT NULL,
    "last_modified_date" timestamp without time zone,
    "last_modified_by_user_id" integer,
    "status" integer NOT NULL,
    PRIMARY KEY ("id")
  );

CREATE TABLE IF NOT EXISTS "user_information"(
    "id" SERIAL NOT NULL,
    "create_date" timestamp without time zone NOT NULL,
    "last_modified_date" timestamp without time zone,
    "last_modified_by_user_id" integer,
    "user_id" integer NOT NULL,
    "email" varchar(100) NOT NULL,
    "username" varchar(50),
    "password" varchar NOT NULL,
    "first_name" varchar(50),
    "last_name" varchar(50),
    "website" varchar,
    "description" varchar,
    "birth_date" timestamp without time zone,
    "country" varchar(50),
    "gender" integer,
    "file_type" integer,
    "file_name" varchar(50),
    "file_extension" varchar(50),
    "file_portrait" boolean,
    PRIMARY KEY ("id"),
    FOREIGN KEY ("user_id") REFERENCES "user" ("id") ON DELETE CASCADE ON UPDATE CASCADE
  );
CREATE TABLE IF NOT EXISTS "user_role"(
    "id" SERIAL NOT NULL,
    "create_date" timestamp without time zone NOT NULL,
    "last_modified_date" timestamp without time zone,
    "last_modified_by_user_id" integer,
    "user_id" integer NOT NULL,
    "role" integer NOT NULL,
    PRIMARY KEY ("id"),
    FOREIGN KEY ("user_id") REFERENCES "user" ("id") ON DELETE CASCADE ON UPDATE CASCADE
  );

merge table ?

CREATE TABLE IF NOT EXISTS "user"(
    "id" SERIAL NOT NULL,
    "create_date" timestamp without time zone NOT NULL,
    "last_modified_date" timestamp without time zone,
    "last_modified_by_user_id" integer,
    "status" integer NOT NULL,

    "information_last_modified_date" timestamp without time zone,
    "information_last_modified_by_user_id" integer,
     .... user_information

    "role_last_modified_date" timestamp without time zone,
    "role_last_modified_by_user_id" integer,
    ... user_role
    PRIMARY KEY ("id")
  );


texts: Gaps and Islands Across Fields With Start and End Timestamps


texts: Default values in function, arguments dependency, PostgreSQL

texts:
I would like to write a function with two arguments, which would have dependency. Something like this:

    CREATE OR REPLACE FUNCTION years_compare(
        IN year1 integer DEFAULT date_part('year'::text, ('now'::text)::date),
        IN year2 integer DEFAULT year1 - 1)

...

When I call a function years_compare(2019), the second argument would have a value 2018. But how can I write a default value for the second argument?


texts:
I have a series of ETL jobs in which I create a swap table using CREATE TABLE table1_swap LIKE table1. In order to make the populating of table1_swap faster I do not include the indexes. When I'm finished loading however I need to re-apply those indexes to the newly populated table. These indexes are created outside of the scope of these ETL jobs so I would rather not have to hardcode the CREATE INDEX calls if I don't have to.

Is it possible to "transfer" or "copy" a set of indexes from one table to another?


texts: Get old database off external hard drive

texts:
My hard drive crashed yesterday, luckily I had a back up. I didn't restore from backup though cause I needed a fresh system anyways. However there was a development database I would like back.

Is there a way to grab an old database file from my back up and somehow get that database onto my new localhost?


texts: How to reset object permissions to default?

texts:
I've just been added to a project using Postgresql 11.4 where database security has been an afterthought.  As part of tightening things up, I've used ALTER DEFAULT PERMISSIONS to set sane ACLs on any new objects (schemas, tables, functions, sequences) created.  However, there are hundreds of existing objects already in this database with wacko permissions.

Is there a command that will reset the permissions of an object (or group of objects) to the defaults (as seen with \ddp)?  Something like the equivalent of REASSIGN OWNED for object ownership, but instead for acls.


texts: PostgreSQL: Another seq scan where an index scan is expected

texts:
Here's the query:

SELECT DISTINCT ON(points.track_id)
  participants.contest_number,
  points.track_id,
  points.lat,
  points.lon,
  points.alt,
  points.timestamp,
  points.h_speed,
  points.v_speed,
  points.distance
FROM
  tracks_competitiontrackpoint points
  JOIN tracks_competitiontrack tracks ON points.track_id=tracks.track_ptr_id
  JOIN competitions_contestparticipant participants ON tracks.participant_id=participants.id
WHERE
  tracks.task_id = 24 AND points.timestamp <= 1409663400 AND tracks.online = true
ORDER BY
  points.track_id, points.timestamp desc, points.id;

Query plan:

Unique  (cost=103411.24..104217.95 rows=527 width=38) (actual time=2507.814..2558.081 rows=113 loops=1)
  ->  Sort  (cost=103411.24..103814.60 rows=161342 width=38) (actual time=2507.805..2541.076 rows=82022 loops=1)
        Sort Key: points.track_id, points."timestamp", points.id
        Sort Method: external merge  Disk: 3984kB
        ->  Hash Join  (cost=69.66..85041.85 rows=161342 width=38) (actual time=2253.401..2408.140 rows=82022 loops=1)
              Hash Cond: (points.track_id = tracks.track_ptr_id)
              ->  Seq Scan on tracks_competitiontrackpoint points  (cost=0.00..73678.24 rows=2581475 width=34) (actual time=0.711..1398.816 rows=2575197 loops=1)
                    Filter: ("timestamp" <= 1409663400)
              ->  Hash  (cost=69.10..69.10 rows=45 width=8) (actual time=1.831..1.831 rows=122 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 5kB
                    ->  Hash Join  (cost=12.60..69.10 rows=45 width=8) (actual time=0.522..1.738 rows=122 loops=1)
                          Hash Cond: (participants.id = tracks.participant_id)
                          ->  Seq Scan on competitions_contestparticipant participants  (cost=0.00..48.74 rows=974 width=8) (actual time=0.008..0.679 rows=976 loops=1)
                          ->  Hash  (cost=12.04..12.04 rows=45 width=8) (actual time=0.383..0.383 rows=122 loops=1)
                                Buckets: 1024  Batches: 1  Memory Usage: 5kB
                                ->  Bitmap Heap Scan on tracks_competitiontrack tracks  (cost=5.30..12.04 rows=45 width=8) (actual time=0.194..0.297 rows=122 loops=1)
                                      Filter: (online AND (task_id = 24))
                                      ->  Bitmap Index Scan on tracks_competitiontrack_online  (cost=0.00..5.29 rows=139 width=0) (actual time=0.144..0.144 rows=159 loops=1)
                                            Index Cond: (online = true)
  Total runtime: 2559.586 ms

Or highlighted results: http://explain.depesz.com/s/ENi

I use DISCTINCT ON to query the latest result for each tracks_competitiontrackpoint.track_id ordered by tracks_competitiontrackpoint.timestamp. Index is enabled on tracks_competitiontrackpoint.track_id, tracks_competitiontrackpoint.timestamp, tracks_competitiontrack.participant_id, tracks_competitiontrack.task_id - pretty much everything that. Still there'a sequential scan for two tables, one of which is taking a lot of time. Could you please explain why?


texts: Is Query Slower When There Are More Columns in one Table?

texts:
I'm trying to build a login function and was wondering:

Does more columns in a table mean the longer (and slower) the query takes to return a result?

I'm asking because I'm debating on whether to

put only the essential columns to allow users to login (userID, username, password, email, etc.) and separate the other data (first name, last name, address, etc.) to another table with a FK constraint
just put everything in one table.

Would login processing time be affected if I choose option 2 (due to SQL query taking longer because of the many columns)?


texts: Filestream or FileTable...what should be used to solve the problem

texts:
I've been assigned with a project that includes converting the columns in the existing tables to use FileTable or Filestream. Basically, the requirement is to move blob fields to filetable storage if possible. Columns that matters are XSLT, XMLData, and Results from ExportRunResults table and there is a UserFiles table that has a column FileData that also needs to undergo the same process. I have attached the schema files for both and how the data looks like in the screenshots. I would like to know if it is feasible to convert the existing columns and tables to use Filetable or Filestream and instead of having the values stored inside the tables we can use either technologies to store files in the file system and pointers in the table. All these files could be .pdf, jpg etc.

Appreciate any help on this as I haven't found any useful article that would help achieve the task in my scenario.

-- ExportRunResults

-- UserFiles

USE [UserSpecificFiles]
GO
/****** Object:  Table [dbo].[UserFiles]    Script Date: 6/3/2020 1:42:07 PM ******/
IF  EXIST (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserFiles]') AND type in (N'U'))
DROP TABLE [dbo].[UserFiles]
GO
/****** Object:  Table [dbo].[UserFiles]    Script Date: 6/3/2020 1:42:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserFiles](
    [UserFileID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [int] NOT NULL,
    [UserSpecificFileTypeID] [int] NOT NULL,
    [FileData] [varbinary](max) NOT NULL,
    [FileSize] [int] NOT NULL,
    [FileName] [nvarchar](400) NOT NULL,
    [SavedDateTime] [datetime] NOT NULL,
    [UploadingUser] [nvarchar](200) NULL,
    [LastDateViewed] [datetime] NULL,
    [FirstDateViewed] [datetime] NULL,
    [MimeTypeID] [int] NULL,
 CONSTRAINT [PK_UserFiles] PRIMARY KEY CLUSTERED
(
    [UserFileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

USE [ExportConfig]
GO

/****** Object:  Table [dbo].[ExportRunResults]    Script Date: 6/3/2020 2:11:37 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ExportRunResults](
    [ExportRunID] [INT] NOT NULL,
    [XSLT] [VARCHAR](MAX) NULL,
    [XMLData] [NVARCHAR](MAX) NULL,
    [Results] [VARCHAR](MAX) NULL,
    [SavedDateTime] [DATETIME] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[ExportRunResults] ADD  CONSTRAINT [DF_ExportRunResults_SavedDateTime]  DEFAULT (GETDATE()) FOR [SavedDateTime]
GO

ALTER TABLE [dbo].[ExportRunResults] CHECK CONSTRAINT [FK_ExportRunResults_ExportRuns]
GO


texts: postgresql prepared statements with JSONB containment

texts:
I'm trying to write a prepared statement using a date held in JSONB.  A prepared statement doesn't allow the containment operator @> so I used the function name itself jsonb_contains which executes, but now postgres doesn't use the gin index.
Testing in a psql session performance is excellent when I do this
select *
from tab
where jsonb_obj @> '{"date_el":"2001-01-31"}'

because it uses the gin index.  But that won't parse in a prepared statement in java.
When I run this in psql:
select *
from tab
where jsonb_contains(jsonb_obj->>'date_el', '2001-01-31')

performance is very bad.
Has anyone ever had this problem?


texts: Importing and exporting data from a database to another using SyBase is taking a long time

texts:
In order to update a database(Sybase Adaptive Server Anywhere 8) I'm taking all the data from one database on clients computer and using

SELECT * FROM tabelName OUTPUT TO 'C:\path' FORMAT ASCII DELIMITED BY ';';

Then I'm using those files to update another database that's connected to a server by making temporary tables, checking if anything is missing and then dropping the temporary table.

INPUT INTO tempTable FROM 'C:\path' FORMAT ASCII DELIMITED BY ';';

INSERT INTO mainTable
SELECT *
FROM tempTable
WHERE id NOT IN (SELECT id FROM mainTable);

DROP TABLE tempTable;

So I am wondering if there is a better way to do this since two tables are taking a very long time to export and import the data(they do have a ton of columns and data though but still and by ton of data I mean ~100 columns and ~100,000 worth of rows). It's especially weird since another table with similar amount of data goes by very quickly(although it does have less columns). The two tables that are taking a while are reading data ~1000 per sec while others are much faster.

Each client has the database.db file on their laptop. They get some data and need to submit it but have no connection, so they use their own .db file and update the one on the server later.

Getting the SQL definitions might be hard since tables were made a while back and I have no clue how to get them. The destination database is the database that holds all the data while source ones are the ones that are not connected and can be used offline if needed.

Some recommendations, tips would be greatly appreciated.


texts: Turning off auto shrink on all SQL Server databases. Why doesn't this work?

texts:
I figured I could use sp_MSforeachdb to solve this problem, but I get an error message.

sp_MSforeachdb '
BEGIN
USE [?]
DECLARE @dbid INT
SET @dbid = DB_ID()
IF(@dbid > 4)
BEGIN
    --PRINT ''[?]'' + CONVERT(VARCHAR, @dbid)
    --ALTER DATABASE [?] SET AUTO_SHRINK OFF
END
END;
'

If I run the above query with the PRINT line uncommented, I get a list of all databases except the system databases. However, when I uncomment the ALTER DATABASE line, I get these two error messages:

  Msg 5058, Level 16, State 2, Line 9
  Option 'AUTO_SHRINK' cannot be set in database 'master'.
  Msg 5058, Level 16, State 1, Line 9
  Option 'AUTO_SHRINK' cannot be set in database 'tempdb'.

This seems to break the operation at some point, so that only some of the databases get auto shrink disabled.

Any idea how I can disable auto shrink on all databases? Bonus question: why doesn't my approach work?


texts: /usr/libexec/mysqld: Normal shutdown, but my team doesn't do that?

texts:
One of my databases has been shutting down abnormally, but the log said "normal":

130422 13:23:01 [Note] /usr/libexec/mysqld: Normal shutdown

130422 13:23:01 [Note] Event Scheduler: Killing the scheduler thread, thread id 379021
130422 13:23:01 [Note] Event Scheduler: Waiting for the scheduler thread to reply
130422 13:23:01 [Note] Event Scheduler: Stopped
130422 13:23:01 [Note] Event Scheduler: Purging the queue. 41 events
130422 13:23:01 [Note] Error reading relay log event: slave SQL thread was killed
130422 13:23:01 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
130422 13:23:01 [Note] Slave I/O thread killed while reading event
130422 13:23:01 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.002426', position 107
...
130422 13:23:04  InnoDB: Starting shutdown...
130422 13:23:04  InnoDB: Waiting for 201 pages to be flushed
130422 13:23:08  InnoDB: Shutdown completed; log sequence number 6942784551767
130422 13:23:08 [Note] /usr/libexec/mysqld: Shutdown complete

There is no related error or stack trace in the mysqld.log, just some errors:

No data - zero rows fetched, selected, or processed

from Event Scheduler.

I'm pretty sure my team doesn't stop it (via init script or mysqladmin shutdown, ...). grep through the last binlog file, I didn't see any SHUTDOWN statement.

Is there any other causes? Let me know if you need more information.

PS: mysql  Ver 14.14 Distrib 5.5.28, for Linux (x86_64) using readline 5.1

UPDATE at Mon Apr 22 18:23:33 ICT 2013

Searching through the source code, I found this:

$ grep -lir 'normal shutdown' mysql-5.5.28
mysql-5.5.28/scripts/mysqld_safe.sh
mysql-5.5.28/Docs/mysql.info
mysql-5.5.28/sql/share/errmsg-utf8.txt

$ grep -lr 'ER_NORMAL_SHUTDOWN' mysql-5.5.28
mysql-5.5.28/Docs/mysql.info
mysql-5.5.28/sql/mysqld.cc
mysql-5.5.28/sql/share/errmsg-utf8.txt

mysql-5.5.28/sql/mysqld.cc

  if (sig != 0) // 0 is not a valid signal number
    my_sigset(sig, SIG_IGN);                    /* purify inspected */
  if (sig == MYSQL_KILL_SIGNAL || sig == 0)
    sql_print_information(ER_DEFAULT(ER_NORMAL_SHUTDOWN),my_progname);
  else
    sql_print_error(ER_DEFAULT(ER_GOT_SIGNAL),my_progname,sig); /* purecov: inspected */

Looks like that there is no other reason for this message can be logged into the log file. One possibility I'm thinking is someone (who has SUPER privilege) turned off the binary log then run SHUTDOWN and close the db connection.

PS: In this bug report, there is one person also got this problem on 5.5.24.


texts: diskgroup "DATA" space exhausted, but disk space is available from asm_diskgroup query

texts:
I'm trying to restore database, and it failed due to disk space issue:
rman> restore database
...
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup "DATA" space exhausted
...

Then I checked the diskspace via this query
SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage
2>      FROM v$asm_diskgroup;

NAME                              FREE_MB   TOTAL_MB PERCENTAGE
------------------------------ ---------- ---------- ----------
DATA                                67032     153600  43.640625

Looks like there are 67GB available, so what was wrong?
Is there a way to get the disk space needed?


texts:
I have Postgresql 11 running on Windows server and master/slave replication working perfectly.
I have enable the wal_archiving and also i have enabled automatic backups using pg_dump
I need to ask if point in time recovery is possible on Windows and steps to perform it.


texts:
I have been working with a client with high traffic website(500k visitors and 600-800 active users at any given time). This uses wordpress and the DB is using MyISAM engine. The problem we had was high CPU usage in the server. All the time CPU load is 15-20. We used litespeed and MySQL 5.1 with CentOS 5.9 in Dual Xeon L5506, 12GB RAM server with Sata HDD.

So I analyzed the database and found there is only 4GB of data and index size of that DB and decided to convert to InnoDB. Once we did, we ended up having 80-150 CPU load and server was about to crash. So we transferred MySQL to another server with same config but to MySQL 5.5.

In New DB server CPU load is 1-2 and web server still on 4-6 constant CPU load.

Here is my my.cnf

[mysqld]
innodb_file_per_table=1
local-infile = 0
default-storage-engine = InnoDB
max_connections = 1000

innodb_buffer_pool_size = 8G
innodb_flush_method = O_DIRECT
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 8
innodb_file_format = Barracuda

myisam_sort_buffer_size = 16MB

query_cache_type = 1
query_cache_limit = 2M
query_cache_size = 256M
thread_cache_size = 16K

key_buffer_size = 128M
max_heap_table_size = 128M
tmp_table_size = 128M
join_buffer_size = 32M
read_buffer_size = 32M
read_rnd_buffer_size = 1M
sort_buffer_size = 32M
table_cache = 4K
open_files_limit = 65535

log-slow-queries = /var/log/mysql/slowqueries.log
long_query_time = 3

PS : Not all of our  DB's are InnoDB, so MyISAM values are placed after good analysis.

Statistics : For 4 hours

Questions since startup: 7,339,471 Documentation
ø per hour: 1,704,102
ø per minute: 28,402
ø per second: 473

   Traffic              ø per hour
Received 4.8 GiB    1.1 GiB
Sent     248.5 GiB  57.7 GiB
Total    253.3 GiB  58.8 Gi


texts: how to get overdue complaints using join two tables

texts:
First table

Complaint_id         Completetion_date        Templateid
53035                 2018-11-16 00:00:00        5
47956                2018-11-03 00:00:00        13
3953                 2017-11-27 00:00:00        17

Second table

Template_month                        id
8                                      5
6                                      13
4                                      15
2                                      17

In first table completion date is 16 Nov 2018 and tempalteid is 5 and in the second table id 5 have 8 month means need to calculate 8 months from 16 nov this is not overdue complaint. In the second row of first table completion date 2018-11-03 00:00:00   having templateid 13 and in the second table there is 6 month means periods is overdue then in the result 3953 is overdue id

I am using this query

select complaint_id
from complaints as comp
INNER JOIN template ON comp.templateid=template.id
where comp.completion_date::date + ('interval ' || template.template_month::text || ' month')


texts: What is the SQLCLR wait type in Activity Monitor within SQL Server Management Studio (SSMS)?

texts: When I check our SQL Prod server, every time I open Activity Monitor I always see SQLCLR on the first list on the resource wait section in the Activity Monitor. It always has a high number. What is SQLCLR for? Is it bad for the server having that SQLCLR with high resource wait number in the activity monitor?


texts: Is declaring VARCHAR size make sense for performance? Is there any difference (in speed) between VARCHAR(50) and VARCHAR(255)? Or defining length is logic/design constraint?


texts:
When I try to log into the mysql shell with:

mysql -u root

then I get:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

How do I log onto the mysql shell?

I have been googling this left and right and trying all those proposed solutions on stackoverflow and elsewhere but nothing seems to work.

I have tried adding -p at the end. Same error.

I have tried going by this:
https://stackoverflow.com/questions/11657829/error-2002-hy000-cant-connect-to-local-mysql-server-through-socket-var-run
The first answer, I already have mysql-server installed.
The second answer doesn't work.
The third answer, i cannot implement step no. 3:

mysql -u root

and in the second step the process just hangs with message:

mysqld_safe Logging to syslog.
2017-09-26T16:30:40.563159Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-09-26T16:30:40.566955Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

So the question is, how do I log onto the mysql shell?


texts: This simple, single table query does a filesort, why?

texts:
The table:

CREATE TABLE `test` (
  `status` tinyint(4) NOT NULL,
  `type` varchar(32) CHARACTER SET ascii NOT NULL COMMENT 'The ID of the target entity.',
  `created` int(11) NOT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
  KEY `status` (`status`,`type`,`created`,`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

the query

EXPLAIN
SELECT *
FROM test
WHERE status = 1 AND type = 'discussion'
ORDER BY created DESC, title ASC
LIMIT 10 OFFSET 0\G

the result

  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: status
          key: status
      key_len: 35
          ref: const,const
         rows: 1716
     filtered: 100.00
        Extra: Using where; Using index; Using filesort
1 row in set, 1 warning (0.00 sec)


texts: Remove InnoDB files (ibdata1 and iblog files) after migration to MyISAM

texts:
I changed the only two InnoDB tables on a replication slave to MyISAM, so the slave now only has MyISAM tables.

Can I now remove the ibdata1 file completely?


texts: MySQL Won't Start - InnoDB Table Corruption

texts:
I've found lots of articles and questions related to this issue, but none that seem to apply or work in my situation. MySQL will not start after I had to do a fsck repair on my server's hard drive (unsure of cause: power failure, SSD corruption..?). The server now boots, but the main corruption appears to be in the InnoDB data file. I can't get the mysql process to start even with innodb_force_recovery=6.

At recovery level 6 I get this in the logs:

190925 21:25:07  InnoDB: Error: trying to access tablespace 36241408 page no. 0,
InnoDB: but the tablespace does not exist or is just being dropped
[...repeats 100 times...]

After running innochecksum I get:

    >innochecksum /var/lib/mysql/ibdata1 -v
    file /var/lib/mysql/ibdata1 = 320864256 bytes (19584 pages)...
    checking pages in range 0 to 19583
    page 51 invalid (fails old style checksum)

However, I was able to apparently recover the database using a 3rd party tool (Stellar Repair). It reports successful repair and I can browse my data. This leads me to believe the information is intact, but I just don't know how to repair it myself. I'd like to know if I'm missing something before I shell out $200 for this software.
-Thanks

Update:

Out of desperation, I removed all my ibdata and log files and started mysql. Then I 'hot-swapped' my old ibdata file into the /var/lib/mysql directory. To my amazement, this almost worked. The phpMyAdmin database recovered enough to let me login, however, all the other data I care about just gives a "table not found" error when I attempt to view or dump. This missing data was recovered however when using the 3rd party recovery app. I might have to turn this into an ad for recovery service to help pay for a license...


texts: LEFT JOIN performance for WHERE on secondary table

texts:
I've been developing for years and through out the carrier I've been using only LEFT JOIN as it provides a more logical sense of mapping my one to many data.

However recently I ran across a performance thought:

Say I have table user with millions of rows and table gender with only 2 rows

user                                gender
=====================               ================
id name      genderId               id gender show
=====================               ================
1  Sam       1                      1  Male   0
2  Samantha  2                      2  Female 1
3  Another millions of records

If I'm doing

select * from user u left join gender g on u.genderId = g.id where g.show = 1;

Does SQL engine joins the whole million user records first, then scan the million rows of records for join projected value?

Or is the engine smart enough to query and join only the rows with correct gender? Or is RIGHT JOIN the correct method to provide best performance?

If the behavior is dependent on engine type, let's talk about MySQL InnoDB.


texts: MySQL 100% CPU Usage Reading from a 500.000 Rows table

texts:
I'm using MariaDB 10.1

My config is the follow

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

key_buffer_size = 2G
read_buffer_size = 2G

symbolic-links=0
skip-name-resolve=1
query_cache_limit       = 4M
query_cache_size        = 256M

expire_logs_days        = 10
max_binlog_size         = 100M

max_connections  = 8096
back_log = 4096
open_files_limit = 20240
innodb_open_files = 20240

table_open_cache = 8096
table_definition_cache = 8096

tmp_table_size = 2G
max_heap_table_size = 2G

innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 10
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
performance_schema = 0
innodb-file-per-table = 1
innodb_table_locks = 0

thread_cache_size = 8096
thread_handling = pool-of-threads
thread_pool_size = 20
thread_pool_idle_timeout = 20
thread_pool_max_threads = 2048

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I have one table MYISAM (DESCRIBE)

MariaDB [ip2proxy]> DESCRIBE ip2proxy_px2;
+--------------+------------------+------+-----+---------+-------+
| Field        | Type             | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| ip_from      | int(10) unsigned | NO   | PRI | NULL    |       |
| ip_to        | int(10) unsigned | NO   | PRI | NULL    |       |
| proxy_type   | varchar(3)       | YES  |     | NULL    |       |
| country_code | char(2)          | YES  |     | NULL    |       |
| country_name | varchar(64)      | YES  |     | NULL    |       |
+--------------+------------------+------+-----+---------+-------+
5 rows in set (0.03 sec)

MariaDB [ip2proxy]> SELECT COUNT(*) FROM ip2proxy_px2;
+----------+
| COUNT(*) |
+----------+
|   482450 |
+----------+
1 row in set (0.00 sec)

We do more than 8.000 reads / second against that database to search for IPs.
The MySQL Is going to 100% in all Cores (12 Threads) and then it is crashing.

We want all the requests to be executed without any delays.

The query we execute is:

SELECT proxy_type FROM `ip2proxy_px2` WHERE {IPLONG} BETWEEN ip_from AND ip_to LIMIT 1;

Any idea what im doing wrong?


texts: Why was default of innodb_file_per_table changed?

texts:
Is there a disadvantage in using file per table storage? I wonder why the default of the value innodb_file_per_table was changed with MySQL 5.7.

Documentation shows that the default value changed (to "OFF"), so there must be a reason:
https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_per_table

Reference: Is innodb_file_per_table advisable?


texts: How do I show the number of tables in all databases?

texts:
I am looking for a query that return all the databases in my MySQL database (i.e. like SHOW DATABASES; but includes the number of tables in the tabular result.)

Something that outputs:

+--------------------+--------+
| Database           | Tables |
+--------------------+--------+
| information_schema |     40 |
| db1                |     97 |
| db2                |    135 |

etc.

Is there a simple query or command that can be typed from the mysql command line that produces that.


texts: MongoDB Sharding : What should be the Sharding key if we want to find records with more than one field

texts:
I am having collection ("links") with listed fields:

collection-name: "links"
1) url
2) title
3) score
4) description
5) ratings (embedded_many)
and having index on "url", "title", "score"

because I need to find links based on these ("url", "title", "score") fields. query like : matching "url", "title" and "score" equal, gte, lte.

I would like to apply sharing on links collection.

Please guide me what should be the sharing-key and why ?

1) Cardinality for each field
url: 50,40,059
title: 50,10,105
score: 50,40,059

2) If null values exists on those fields
title: could have null value.

3) Is those fields changing?
only 'score' is changing after period of time.


texts:
Facing trouble related to column referencing.detailed problem below
Say I have 2 tables Table Metadata and Table Transactions

Table Metadata has the following information regarding columns

Attribute   Value   desc
EmpID      123456   employee of the month
Name        XYZ     defaulter

The Table Structure of the Transactions table is as follows:

EmpID   Name  Type  Title
0       ABC    A        …
1       DEF    A        …
2       GHI    A        …
3       JKL    A        …
12345   XYZ    A        …

I want to be able to join such that I get the resultset as

EmpID   Name  Type  Title  desc
0       ABC    A        …
1       DEF    A        …
2       XYZ    A        …  defaulter
3       JKL    A        …
12345   PQR    A        …  employee of the month

How can I achieve this? I am using postgresql. So I do understand that I have to use information schema, but confused about the joins. Any help?


texts: Error 9002 state 3 after RESTORE DATABASE

texts:
I'm trying to restore a database from a validated backup (through RESTORE VERIFYONLY).

The command I've used :

RESTORE DATABASE [RESTORE] FILE = N'SharePoint_Config_3c0993c1866f419e9f8e906a920a6ae8'
FROM  DISK = N'0000008B.bak'
WITH
    FILE = 1,
    MOVE N'SharePoint_Config_3c0993c1866f419e9f8e906a920a6ae8' TO N'F:\RESTORE.mdf',
    MOVE N'SharePoint_Config_3c0993c1866f419e9f8e906a920a6ae8_log' TO N'F:\RESTORE_log.ldf',
    NOUNLOAD,
    REPLACE,
    STATS = 10
GO

At the end of the restore operation, I got this error :

...
100 percent processed.
Processed 16888 pages for database 'RESTORE', file 'SharePoint_Config_3c0993c1866f419e9f8e906a920a6ae8' on file 1.
Processed 1707474 pages for database 'RESTORE', file 'SharePoint_Config_3c0993c1866f419e9f8e906a920a6ae8_log' on file 1.
Msg 9004, Level 16, State 3, Line 1
An error occurred while processing the log for database 'RESTORE'.  If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Then the database is stuck in RESTORING status. The error message suggest to rebuild the log, BUT in RESTORING state I can't do anything : the database can't be accessed for repair with DBCC CHECKDB.

I've tried :

RESTORE DATABASE [RESTORE] WITH RECOVERY
GO

Result :

Msg 4333, Level 16, State 1, Line 1
The database cannot be recovered because the log was not restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

I've googled It many times, searching on this forum, but can't find a solution to apply suggested procedure "it might be necessary to rebuild the log."

My question is  : how to get rid of this status, ie make the database status to be EMERGENCY so I can try to run repair tools on it ?

Hope somebody can help me.
Regards.

Update 1

I've managed to get our database in "SUSPECT" status, with Sahnky's tweak. From there, and with some external help, I have run DDBCC CHECKDB with REPAIR_ALLOW_DATALOSS and got our database out of SUSPECT status.

I've omitted to mention that I've tried to RESTORE without the FILE option to perform a full restore, but it hangs with errors (I can't remember which ones).


texts: How can I check which database objects are using certain tablespace in Oracle DB? Any query or somehow through SQL Developer GUI?


texts: Using an AWS database that can store more than 65,535 bytes in a row

texts:
I have a MySQL Barracuda table with about 150 fields and most of them are TEXT types.

If I wanted to store more than 65,535 characters in a row, would it be possible to do so using Amazon's AWS database ? If so which kind of database ? RDS ?
I am using a semi-VPS kind-of hosting company which has the application in vanilla PHP connected to this database. Would it be possible to migrate just the database to AWS and keep the application running on my hosting company ? (I should be able to connect to this AWS database from my host's PHP application.


texts: How do you update a large MySQL table that's on a server?

texts:
I have an application on a web server.

The PHP front end for the application queries a MySQL database which has a table that contains 60 million taken domain names. My application queries this table to check if a domain name is available and it works surprisingly fast.

Every day, however, the list of taken domain names is updated by the organization which provides it.  I have to download the compressed new list of words and then use it to update the domains table for it to remain accurate.

I am not sure, however, how to go about updating the table which is live on the web MySQL server and takes up roughly 3 gigabytes.

I've researched it a bit, and the best so far I've found is to create a new table/database then delete the old one and switch them over. The changes to the table, however, are probably less than 1% daily, so this feels overkill and inefficient.

Would be great to know if there is a better way.


texts:
Need to create step in SQL job which check if file exist because if file exists it needed to be stop but if file not exist to continue with next step.
xp_fileexist not allowed in sql query.


texts: Is there a way to specify the initial size of MySQL File-Per-Table Tablespaces?

texts:
Preparing for massive data import into a MySQL 5.7 community instance, I'd like to know if there is a way to allocate upfront the total amount of space that will be used by the volume of information?

The purpose would by to eliminate overhead caused by the gradual auto-extend increments of the tablespace that, according to the reference guide, will be 4MB at a time, tops.


texts: logical reads on global temp table, but not on session-level temp table

texts:
Consider the following simple MCVE:

SET STATISTICS IO, TIME OFF;
USE tempdb;

IF OBJECT_ID(N'tempdb..#t1', N'U') IS NOT NULL DROP TABLE #t1;
CREATE TABLE #t1
(
    r int NOT NULL
);

IF OBJECT_ID(N'tempdb..##t1', N'U') IS NOT NULL DROP TABLE ##t1;
CREATE TABLE ##t1
(
    r int NOT NULL
);

IF OBJECT_ID(N'dbo.s1', N'U') IS NOT NULL DROP TABLE dbo.s1;
CREATE TABLE dbo.s1
(
    r int NOT NULL
        PRIMARY KEY CLUSTERED
);

INSERT INTO dbo.s1 (r)
SELECT TOP(10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.syscolumns sc1
    CROSS JOIN sys.syscolumns sc2;
GO

When I run the following inserts, inserting into #t1 shows no stats I/O for the temp table.  However, inserting into ##t1 does show stats I/O for the temp table.

SET STATISTICS IO, TIME ON;
GO

INSERT INTO #t1 (r)
SELECT r
FROM dbo.s1;

The stats output:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.
Table 's1'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 9 ms.

(10000 rows affected)

INSERT INTO ##t1 (r)
SELECT r
FROM dbo.s1;

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.
Table '##t1'. Scan count 0, logical reads 10016, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 's1'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 45 ms.

(10000 rows affected)

Why are there so many reads on the ##temp table when I'm only inserting into it?


texts: Postgresql 9.4.1 stuck all queries when making multi updates

texts:
Before i used 9.3.5 and servers with ubuntu 12 with 32GB memory.

After upgrade to 9.4.1, with more power server 60GB memory on each in wall replication and ubuntu 14, started get db stucks when run multi update from resque/sidekiq background workers. resque/sidekiq work via pgpool 3.3

Queries can be as simple, update column where primary id = id. and complex, doesn't metter. usually average connections about less 300, but when queries stucks, count connections grow to maximum and i get

LOG:  process 16121 still waiting for ShareLock on transaction 2448707428 after 1000.121 ms
DETAIL:  Process holding the lock: 16139. Wait queue: 16121.
LOG:  process 16146 still waiting for ExclusiveLock on tuple (665346,11) of relation 997395 of database 16455 after 1000.102 ms
DETAIL:  Process holding the lock: 16121. Wait queue: 16146.
ERROR:  canceling statement due to lock timeout

then

FATAL:  sorry, too many clients already

then only restart help, when run restart got this

WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.

Current settings,

Database size 190GB
max_connections = 400
shared_buffers = 13GB
work_mem = 19660kB
maintenance_work_mem = 2GB
effective_cache_size = 40GB

But i played with different, didn't help. On 9.3 all was fine. Nothing more in logs.

Also in one day i did pg_repack, on next day db was down with usual queries.

P.S.

I found problem, issue was in

commit_delay = 50

After set to 0, issue was solved


texts:
I've a double-column index which isn't used but would be a perfect match for the query:

mysql> EXPLAIN SELECT COUNT(*) as cnt
    -> FROM foobar USE INDEX(index_status_created_at)
    -> WHERE foobar.created_at < "2017-06-20"
    -> AND foobar.status IN ("status1", "status2", "status3");
+----+-------------+---------------+------+--------------------------+------+---------+------+---------+-------------+
| id | select_type | table         | type | possible_keys            | key  | key_len | ref  | rows    | Extra       |
+----+-------------+---------------+------+--------------------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | foobar        | ALL  | index_status_created_at | NULL | NULL    | NULL | 4374176 | Using where |
+----+-------------+---------------+------+--------------------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

Unfortunately, the status are not evenly distributed, so especially for these 3 status there ar enot many rows and the total result would be under 10% of the total table size.

Do you have any ideas why this index is not used at all?


texts: How to do a case sensitive search in WHERE clause?

texts:
I want case sensitive search in SQL query. But by default, MySQL does not consider the case of the strings.

Any idea on how to do a case sensitive search in SQL query?


texts: Dynamic execution query produces error when called from the procedure, but works when executed manually

texts:
create procedure createTable(@tableName varchar(25), @columnName varchar(25), @columnType varchar(25))
as
    begin

    declare @sqlQuery as varchar(MAX)
    set @sqlQuery = 'create table ' + @tableName + '(' + @columnName + ' ' + @columnType + ' primary key)'

    print (@sqlQuery)
    exec @sqlQuery

    end
go

Above code produces below output and error:
create table proba2(ceva int primary key)
Msg 2812, Level 16, State 62, Procedure createTable, Line 9 [Batch Start Line 13]
Could not find stored procedure 'create table proba2(ceva int primary key)'.

Strangely, if I copy the first line in the output(which is the printed query itself) and execute it, it works; the table will be created. Nonetheless, the exec in the stored procedure does not. As far as I know, the exec call of the procedure should work. It should even work without specifying 'primary key' in the end of the string.


texts: Always On Availability Groups Network Configuration in AWS

texts: I have set up a 3-node, multi-subnet Always On Availability group in AWS, which seems to be working well. However, each node only has a single network adaptor with 3 IP addresses assigned to it (1 primary, 1 WSFC IP resource, 1 listener). Is this sufficient for high-availability, or do I need additional networks? I believe with a traditional cluster you would need multiple networks & adaptors to ensure there is no single point of failure. I'm not sure if this is the case in AWS, as I assume they have redundancy built-in at their end?


texts: Use sys.dm_tran_session_transactions for active transaction information

texts:
I am reading this SQL Server DBA book and just couldn't figure this out:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
USE AdventureWorks2014;
GO
BEGIN TRAN
SELECT *
FROM HumanResources.Department
INSERT INTO HumanResources.Department (Name, GroupName)
VALUES ('Test', 'QA');

SELECT session_id, transaction_id, is_user_transaction, is_local
FROM sys.dm_tran_session_transactions
WHERE is_user_transaction = 1;
GO

-- Got sessions_id 52

SELECT s.text
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) s
WHERE c.most_recent_session_id = 52
GO

-- Here, I got "select @@trancount" instead of the select and insert queries

-- I reran the query without specifying session_id

SELECT s.text
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) s

and didn't see the select and insert queries at all:

use [master]
select @@trancount
SELECT s.text  FROM sys.dm_exec_connections c  CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) s


texts: Unable to query remote schema object using partial database link name

texts:
I came across a very interesting situation today and I'm putting this question to the community in hopes that someone can explain why this happened. I haven't been able to find an explanation in Oracle's documentation.

We have a database link configured in our 12c instance, and assume the link is named database.domain.subdomain.com. This link connects to a 10g instance.

In queries that leverage this link, we use the partial link name: select * from table1@database (omitting .domain.*). This works fine.

A complete rebuild of the remote server and instance was necessary due to a hardware failure. When it was brought back up, we encountered a problem querying the table with the same query select * from table1@database however using the full name instead of partial worked fine: select * from table1@database.domain.subdomain.com

Querying other tables using the partial name was fine as well select * from table2@database

I discovered that there was a session open from before the rebuild. After I terminated this session, select * from table1@database resumed working as expected.

My question is, why did was I unable to query that specific object using the partial dblink name?


texts:
We have an option to do recovery through bin-logs, but we thought recovery through bin-logs is not 99% consistent because there is no LSN(Log Sequence Number)/SCN (System Change Number) in bin-logs (Statement/mixed/row).

Is there any possibility to archive innodb-redo-logs ?

Is there any chance to do recovery by using archived Innodb Redo-logs (at the time of instance start-up) ?

Looking for valuable suggestions.

Note: I'm searching for oracle style recovery in MySQL.

Thank you all :)


texts: Way to check where the MySQL connection was initialized?

texts:
Is there a way to identify who created the MySQL connection?

For example:
in file a.php
$conn = mysqli_connect($dbhost, $dbuser, $dbpwd, null, $port, $socket);
I would like to know that this connection was initialized from a.php

I switched on the logger but all it told me that the connection was made but not who initialized it.
Connect    root@localhost on  using Socket


texts: MongoDB pinging failed for distributed lock pinger caused by WriteConcernFailed:waiting for replication timed out. Error details: { wtimeout: true }

texts:
W SHARDING [replSetDistLockPinger] pinging failed for distributed lock pinger :: caused by :: WriteConcernFailed: waiting for replication timed out. Error details: { wtimeout: true }

In my MongoDB logs i found these kind of error at particular time (18:21 -18:39)on all primaries and secondaries in my replica set and config servers  what causes this kind of issues & will it effect to the cluster and whats the solution to resolve this kind of issue


texts: PostGIS window function not picking up index?

texts:
I am executing this query ST_ClusterDBSCAN with PostGIS extension and PostgreSQL. I have indexed point_data but an EXPLAIN ANALYZE shows me that the index is not being picked up. How can I rearrange my query so that it is indeed picked up?

Index:

CREATE INDEX index_point_data ON point_data USING GiST(geom)

Query:

SELECT id, ST_ClusterDBSCAN(geom, eps := 350, minPoints := 3)
OVER () AS cluster_id, geom
FROM point_data

Plan:

WindowAgg  (cost=0.00..148.64 rows=3984 width=44) (actual time=195.029..197.227 rows=3984 loops=1)
  ->  Seq Scan on point_data  (cost=0.00..98.84 rows=3984 width=40) (actual time=0.022..0.578 rows=3984 loops=1)
Planning time: 0.136 ms
Execution time: 197.593 ms


texts: How can this query be improved to be faster?

texts:
I am trying to make a search in a db which contains tables poems and authors. I am using replace function to ignore accent characters in db while making the search.

If the title column matches the input it gets high order in search results.
If the content matches the input it gets a lower order in search results.

The below query works correctly but I want it to be faster.

I considered an extra column that will hold the accent-ignored copies of content and title columns but this almost doubles the db size so I skipped that option. It is around 7mb now. Since it is a mobile app, I don't want its size grow to 15mb just to ignore the accents.

Select poems._id, title, name, surname, photoName
from poems,authors
where ( replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(content, 'Â','a'),'İ','i'),'Î','i'),'Û','u'),'I','i'),'Ş','s'),'Ç','c'),'Ğ','g'),'Ö','o'),'Ü','u'),'â','a'),'ş','s'),'ı','i'),'ö','o'),'ç','c'),'ü','u'),'ğ','g'),'î','i'),'û','u'),'''','')
        LIKE '%input%'
      OR
        replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(title, 'Â','a'),'İ','i'),'Î','i'),'Û','u'),'I','i'),'Ş','s'),'Ç','c'),'Ğ','g'),'Ö','o'),'Ü','u'),'â','a'),'ş','s'),'ı','i'),'ö','o'),'ç','c'),'ü','u'),'ğ','g'),'î','i'),'û','u'),'''','')
        LIKE '%input%')
  AND authors._id = poems.authorId
ORDER BY (CASE WHEN replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(title, 'Â','a'),'İ','i'),'Î','i'),'Û','u'),'I','i'),'Ş','s'),'Ç','c'),'Ğ','g'),'Ö','o'),'Ü','u'),'â','a'),'ş','s'),'ı','i'),'ö','o'),'ç','c'),'ü','u'),'ğ','g'),'î','i'),'û','u'),'''','')
                    LIKE 'input%' THEN 1
               WHEN replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(title, 'Â','a'),'İ','i'),'Î','i'),'Û','u'),'I','i'),'Ş','s'),'Ç','c'),'Ğ','g'),'Ö','o'),'Ü','u'),'â','a'),'ş','s'),'ı','i'),'ö','o'),'ç','c'),'ü','u'),'ğ','g'),'î','i'),'û','u'),'''','')
                    LIKE '%input%' THEN 2
               WHEN replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(content, 'Â','a'),'İ','i'),'Î','i'),'Û','u'),'I','i'),'Ş','s'),'Ç','c'),'Ğ','g'),'Ö','o'),'Ü','u'),'â','a'),'ş','s'),'ı','i'),'ö','o'),'ç','c'),'ü','u'),'ğ','g'),'î','i'),'û','u'),'''','')
                    LIKE '%input%' THEN 3
               ELSE 4 END)


texts: In Postgresql 12 how to I make the primary server a slave after a deliberate failover

texts:
I am using PostgreSQL 12 on Debian 10.6.
I have been reading up about replication and I have set up an asynchronous replication between one primary and standby. I wanted to test failover so I stopped the primary server and promoted the standby which was successful. I'm having difficulty finding a tutorial online about how to make the old primary a standby server now that the old standby is the new primary. What follows are the steps that I have taken, it looks like it's working but is there anything I may have missed or not thought of?
On the new primary
Update the pg_hba
echo "host    replication     replicator      <new standby ip>/24        md5" >> /etc/postgresql/12/main/pg_hba.conf

On the new standby
Make a back up of the lib
sudo mv /var/lib/postgresql/12/main /var/lib/postgresql/12/main.bk

Make a backup of the old tablespaces directory, and make a new empty directory
sudo mv /tablespaces /tablespaces.bk
sudo mkdir /tablespaces/

Make a base backup of the new primary
pg_basebackup -D /var/lib/postgresql/12/main -F p -R -c fast -l mffb -P -v -h <primary_ip> -p 5432 -U replicator

If WAL Archiving is set, I'm taking these extra steps:
On new primary:
echo "archive_mode = ON" >> /var/lib/postgresql/12/main/postgresql.auto.conf
echo "archive_command = 'test ! -f  /wal_archive/%f && cp %p /wal_archive/%f'" >> /var/lib/postgresql/12/main/postgresql.auto.conf
echo "restore_command = ''" >> /var/lib/postgresql/12/main/postgresql.auto.conf

On new standby
echo "restore_command = 'cp /wal_archive/%f %p'" >> /var/lib/postgresql/12/main/postgresql.auto.conf


texts: Mysql database replication on different vlan/subnet/another site

texts:
Saw the posts for "MySQL high availability, failover and replication with Latency"

Is it possible to work it in a DR site when its not in the same vlan?


texts: Database is too large due to appended backup

texts:
My database MDF file is at around 600mb. LDF file is at 147MB.

I seriously doubt that it should be anywhere near this.

I suspect it's because the database has been backed up by my host with the "Append to the existing backup set" option checked.

How can I get the database to a reduced size? My host has sent me the .bak file and I've restored it.

Also is there a way to check if the database is actually this big, i.e is in the data causing the large size?


texts: How to restore mysql DB from only myi files?

texts:
Our server's hdds crashed and we didn't have a current backup, so we sent our hdds to a data recovery company. They have been working on the hdds for over a week and they have finally managed to save some files from the hdds.

My problem is the saved files are only myi files and they are named numerically, so I don't know which one belongs to which mysql table. Is it possible to recreate myd and frm files from a myi file? Or is there anything else I can do to get current data from myi?


texts: Stored procedure running for a long time

texts:
We have a stored procedure which is running for ever, whereas when it is run as script it takes just couple of minutes to complete. When I just declare the params and assign values to them and run the script it's done in couple of mins. There is no application that is consuming the result set,it's a DW proc.

I tried couple of things:

I tried to flush the plan for that procedure  and run again but didn't run.
I tried to run the SP with recompile  but didn't run.

What can be the possible reasons that it runs as plain script and not as SP even after flushing the cache for that SP?


texts: Why can't I read my transaction log backup file using fn_dump_dblog?

texts:
I'm using this blog on sqlskills.com as a guide to practice finding the relevant LSN in a transaction log backup for point in time recovery.

After backing up the transaction log, I attempt to read it with:

SELECT COUNT(*) FROM fn_dump_dblog (
NULL, NULL, 'DISK', 1, 'D:\TEMP\test_tlog2.bak',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);

I get the following error:

  Msg 615, Level 21, State 1, Line 1
  Could not find database ID 0, name '0'. The database may be offline. Wait a few minutes and try again.

The database id isn't 0, it isn't offline, I don't see any params I'm missing or changes to the function. I can query the database and I can view the tlog backup file from within SSMS backup using the contents option, shows correct media set 1.

  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4263.0 (X64)   Aug 23 2012 15:56:56   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

Am I missing something fundamental?


texts: Difference between Relational database and time series database?

texts:
I have a bit stupid question maybe. I recently came across a term "time-series database". I would like to ask what is time series database and is it same as relational database?

Thanks,


texts: Get the start and end dates of the previous month the query is run with PostgreSQL/intervals

texts:
I am looking for a way to get the start and end datetime for the previous month the query is run that I can reuse inside a query.
I was looking at interval but I would like the query to work out that if I run the query in March 2021 that I am looking for data from '2021-02-01 00:00:00' to '2021-02-28 23:59:59'.
Is this possible using interval or something similar or does it need an intermediate step?


texts: Subtract time difference between two columns in a table

texts:
Problem: How can I subtract the TIMEDIFF between two sensors (sensor 1 and sensor 2 in the example below) and return the the value in mili seconds?

How do I get with the resolution of milliseconds?

I am using the command line below to try to create the table with milliseconds resolution but returns error.

CREATE TABLE via1
(
    id int auto_increment primary key,
    sensor VARCHAR (10) NOT NULL,
    date timestamp(6) default current_timestamp(6)
);

  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(6) default current_timestamp(6))' at line 1

Server version: 5.5.44-0 + deb7u1 (Debian)


texts:
Does anyone know why I should experience variable CHECKDB completion times from day to day? I'm seeing times anywhere from 45 to 90min for the task to complete.

I am running CHECKDB on 8 SQL Server databases comprising of about 350Gb worth of data. I run the checks nightly at 11PM using a Maintenance Plan.


texts: How to resolve "Error log file size is increasing in mysql 5.5" issue w/o restarting mysql

texts:
I have an Ubuntu server with MySQL 5.5 installed. In /var/lib/mysql a .err file is kept on updating with these warnings only:

[Warning] IP address '234.22.11.221' could not be resolved: Temporary failure in name resolution
[Warning] IP address '234.22.11.221' could not be resolved: Temporary failure in name resolution

Its size is currently around 2 GB.

I googled this error and in most of the places it has been written that I need to write skip-host-cache in my.cnf file and then need to give a restart mysql.

Currently this server is carrying lot of running web services, applications and giving a restart may effect all these applications.

As far as I understand, these warnings are not native mysql errors.

Also, can someone tell me whether these warnings effect my database in future if I will not take any action and is it possible that due to other web applications, these errors are generating? What is the other way around for this without restarting mysql?

IS there any probability , that mysql will not come up after restart ???


texts: Modify number of file WiredTigerLog in mogodb

texts:
How can I modify the number of WiredTigerPreplog and WiredTigerLog in journal directory ?
There are allways 2 WiredTigerPrepl and 1 WiredTigerLog.
Thanks
MongoDB : "version": "4.4.4"


texts:
I deleted all rows from 20 tables after disabling the constraints, triggers using the stored proc SP_MsForEachTable. Now before the import happens, I need to reseed all the identity columns back to start auto numbering from 1.

So I created a stored procedure that pass in the table name as a parameter, since it seems this will take time as its doing it individually, is there another way to do this for all of the tables at once?

   CREATE PROCEDURE [dbo].[USP_ReseedID]
@table_name varchar(50)
AS
BEGIN
    DBCC CHECKIDENT ( @table_name, RESEED, 0)

END


texts: Number of TempDB files if we can't use parallelism

texts:
Currently we have 4 cores and 4 tempdb files.  I just came on as the DBA about 2 months ago and, for some reason, 3 of the tempDB files are 2GB and 1 is 1GB.  Obviously, that is not best practice.

Being the OCD and proactive person that I am, I want to resize that file to match up with the other 3.

But wait, there's more.  It's used on a system that has an application that gains performance by NOT using any parallelism, so we have it disabled. MAXDOP = 1, cost threshold = 0.  It's the only application on the system and the SQL isntance is basically designed for this one application.

What are everyone's thoughts on this?  Will it help with any slowdowns that were seeing, given that our MAXDOP = 1?  Also, what implications/issues should I be on the lookout for when I change the file size?  I'll be doing it at night, off production hours.

Thanks for the help guys.


texts: Why does restarting MongoDB 3.0.x boost performance?

texts:
I have a MongoDB in my Development environment running on Windows, the server has 64GB of RAM, but MongoDB has only consumed 26.9GB (here we are building the database), so it should be able to hold all the indexes and data in ram:

DB Stats:
Data Size: 27.8071 GB
Index Size: 3.96 GB

After 3 or 4 hours running, insert performance drops to only 2-3k inserts per second, despite all indexes being in RAM (or they should be).

However, I've noticed if I restart the server, insert speeds rates increase to x3 or x4.

I'm wondering why this is, and could it be caused by running on Windows?

The collections being inserted (with any frequency) have (at this time) 20 mil, 44 mil, and 51 mil.

The first collection of 20mil has a very random index based on a 256 bit hash, the other two collections are indexed on ObjectId. This is the bottle neck I cannot shift without sharding, but it is concerning that the insert rate changes so much after a restart.

I do not want to periodically restart my Primary node, causing clients to have to fail over to a secondary.

Edit: I should also say, I am running with write concern unacknowledged.

Please find example image of logs, apologies I did not capture a text output of this log.

Some typical slow queries when they are popped up by the profiler, set at 100ms:

2015-08-18T11:56:34.768+0100 I COMMAND  [conn7] command Slice_BTC.$cmd command: insert { insert: "Transactions", writeConcern: { w: 0 }, ordered: false, documents: 765 } keyUpdates:0 writeConflicts:0 numYields:
 acquireCount: { w: 7 } }, Database: { acquireCount: { w: 7 } }, Collection: { acquireCount: { w: 7 } } } 790ms
2015-08-18T11:56:35.184+0100 I QUERY    [conn10] query Slice_BTC.Outputs query: { $query: { t: ObjectId('55d2cf76137e6e233c231ecf'), i: 96 } } planSummary: IXSCAN { t: 1, i: 1 } ntoreturn:1 ntoskip:0 nscanned:1
:0 writeConflicts:0 numYields:0 nreturned:1 reslen:224 locks:{ Global: { acquireCount: { r: 1 } }, Database: { acquireCount: { r: 1 } }, Collection: { acquireCount: { r: 1 } } } 378ms

Typical size of Transactions object: 178 bytes.
Typical size of Outputs object: 204 bytes.


texts: MySQL: Migrating database with utf8 collation and charset but latin1 data to new full UTF-8 database

texts:
With my current situation I have mysql database with collation and character sets on database and tables set to utf8, but all data is still latin1. This is due to server being configured (my.cnf) with:

character-set-server = latin1

Here is more information on old database server:

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

Now we have new properly configured database instances with full UTF-8 and I need to dump the data. I have been trying it different ways without any luck. The data now on original database is garbled and I need to export it to full UTF-8 database.

I have tried this:

mysqldump -h DB_HOST -u DB_USER -p DB_PASSWORD --opt --quote-names \
--skip-set-charset --default-character-set=latin1 DB_NAME > DB_NAME-dump.sql

mysql -h DB_HOST -u DB_USER -p DB_PASSWORD \
--default-character-set=utf8 DB_NAME < DB_NAME-dump.sql

But it does not help as the data in .sql files and in new database is garbled UTF-8