texts: Percona xtradb cluster node2 won't start

texts:
I have configured Percona Xtradb cluster 5.6. The first node starts fine when bootstrapped, but the second node seems to have an issue with this variable setting:

wsrep_provider=/usr/lib/libgalera_smm.so

I am running the cluster nodes on Ubuntu 14.04. For the error log, this is what is throws at me:

150608 18:43:03 mysqld_safe Starting mysqld daemon with databases from
/var/lib/mysql

150608 18:43:03 mysqld_safe Skipping wsrep-recover for empty datadir:
/var/lib/mysql
150608 18:43:03 mysqld_safe Assigning 00000000-0000-0000-0000-000000000000:-1
to wsrep_start_position
2015-06-08 18:43:03 0 [Warning] Using unique option prefix key_buffer instead
of key_buffer_size is deprecated and will be removed in a future release.
Please use the full name instead.
2015-06-08 18:43:03 0 [Warning] TIMESTAMP with implicit DEFAULT value is
deprecated. Please use --explicit_defaults_for_timestamp server option (see
documentation for more details).
2015-06-08 18:43:03 0 [Note] /usr/sbin/mysqld (mysqld 5.6.24-72.2-56-log)
starting as process 5977 ...
2015-06-08 18:43:03 5977 [Note] WSREP: Read nil XID from storage engines,
skipping position init
2015-06-08 18:43:03 5977 [Note] WSREP: wsrep_load(): loading provider library
'/usr/lib/galera3/libgalera_ssm.so'
2015-06-08 18:43:03 5977 [ERROR] WSREP: wsrep_load(): dlopen():
/usr/lib/galera3/libgalera_ssm.so: cannot open shared object file: No such
file or directory
2015-06-08 18:43:03 5977 [ERROR] WSREP:
wsrep_load(/usr/lib/galera3/libgalera_ssm.so) failed: Invalid argument (22).
Reverting to no provider.
2015-06-08 18:43:03 5977 [Note] WSREP: Read nil XID from storage engines,
skipping position init
2015-06-08 18:43:03 5977 [Note] WSREP: wsrep_load(): loading provider library
'none'
2015-06-08 18:43:03 5977 [ERROR] Aborting

2015-06-08 18:43:03 5977 [Note] WSREP: Service disconnected.
2015-06-08 18:43:04 5977 [Note] WSREP: Some threads may fail to exit.
2015-06-08 18:43:04 5977 [Note] Binlog end
2015-06-08 18:43:04 5977 [Note] /usr/sbin/mysqld: Shutdown complete

I have tried commenting out #wsrep_provider and starting the node with the following command

$sudo /etc/init.d/mysql start --wsrep_provider="/usr/lib/libgalera_ssm.so"

But this does not seem to help. The node still fails to start.

The libgalera_ssm.so file is actually present on both servers, hence I am kinda confused why it works on one server and not the other. But it is worth pointing out that even node one gives the same error when I try to start it without using bootstrap-pxc and just use /etc/init.d/mysql start.


texts: Whats the difference between float and numeric in Postgresql?

texts:
Reading the Postgresql docs about the numeric data types
leads me to this question:
why do I get these unexpected results with the data types Float (SQL standard) and Numeric in Postgresql?
For example:
CREATE TEMP TABLE testnum (a numeric, b float);
INSERT INTO testnum VALUES (100,100);
INSERT INTO testnum VALUES (9*9*9,9*9*9);
INSERT INTO testnum VALUES (9^9^9,9^9^9);
SELECT (a/3)*3 AS numeric, (b/3) * 3 AS float FROM testnum;
SELECT (a/5)*5 AS numeric, (b/5) * 5 AS float FROM testnum;

Then run
SELECT (a/3)*3 AS numeric, (b/3) * 3 AS float FROM testnum;

numeric                                     |         float
                        99.9999999999999999 |                    100
                       729.0000000000000000 |                    729

In this test Float looks more accurate than Numeric.
Which one is the appropriate data type for precise numeric values like currency or inventory quantities?


texts: Can I get some rationale from the Optimzer why it chose a certain explain plan?

texts:
This is not about reading an explain plan.

This is not about adding any hints or measuring performance of a specific query.

I have always been wondering whether it's possible for a given explain plan for a query (or an autotrace of the query) to "ask" the Optimizer why it chose this plan vs. other plans available to it.

Is there a way to ask the database to display a comparison of different plans that it considered for the same query?
Is there a way to view what calculations went into the cost displayed for a plan? (For examples, what the Optimizer assumed wrt. disk access vs. in memory stuff, etc.)

Rationale for this question: With the EXPLAIN PLAN as I know it, when the database chooses a certain execution plan, and this is insufficient/too slow/... at the first moment a DBA or developer does not ad hoc know: Whether there were any alternatives to this plan at all and furthermore doesn't know why, if the developer knows of alternatives (e.g. an unused index), the alternatives weren't used.


texts: How to import multiple CSV files to a postgres table using pgadmin or other method?

texts:
I'm looking to import a folder of over 100+ csv files into one table on PostgreSQL using pgadmin. I have the following script but is there a way to change the file path to all csvs?

COPY schema.table FROM ‘C:\Documents\Data’ DELIMITER ‘,’ CSV HEADER;


texts: How to populate a table's foreign keys from other tables?

texts:
I have a database with the following tables:

Airport ( iata PK, airport, city, state, country, lat, long)

cancellation_cause ( cod_cancellation PK, description)

Manufaturer (id_manufacturer PK, manufacturer_name)

Model (id_model PK, model_name, id_manufacturer FK)

Airline( airline_code PK, description)

airplane_type (id_AirplaneType PK, airplane_type)

engine_type (id_engine PK, engine_tyoe)

Aircraft_type (id_aircraft PK, aircraft_type)

Airplane (TailNumber PK, id_model FK, id_aircraft FK, airline_code FK, id_AirplaneType FK, id_engine FK, Issue_date, status, year)

Flight (id_flight PK, cod_cancellation FK, TailNumber FK, iata_origin FK, iata_destin FK, Year, Month, DayofMonth, DayofWeek, DepTime, CRSTime, ArrTime, CRSArrTime, FlightNum, AtualElapsedTime, CRSElapsedTime, AirTime, ArrDelay, DepDelay, distane, TaxiIn, TaiOut, Cancelled, Diverted, CarrierDelay, WeatherDelay, NASDelay, SecurityDelay, LateAircraftDelay)

Note: PK - Primary Key;  FK - Foreign Key

I have a csv file "flights" with millions of records containing information such as "TailNumber, AirlineCode, etc." for each of the flights.

Also have a supplementary csv "Airplane" with information about the plane (TailNumber, engine type, year, etc.)

In tables Flight, Airplane and Model some fields can be copied directly but I don't know how to load data from foreign keys correctly.

Someone help me?


texts: "SQL1042C An unexpected system error" when creating a database

texts:
I installed DB2 Express-C 10.1 on OS X 10.8.4. I installed it in user mode, and it seems to have created an instance with my username (avernet). I checked the instance is indeed there by running db2ilist. The database manager is running, as when I run db2start, I get the message SQL1026N  The database manager is already active.

Now, I am trying to create a database by running db2 from the command line, and typing the command create database orbeon, but it returns:

QL1042C  An unexpected system error occurred.  SQLSTATE=58004

What am I missing here? How can I create a simple database from the command line (I prefer not to bother with Data Studio as it isn't available for OS X)?

In db2dump/db2diag.log, I see the follwing error (full file, in case you're interested):

2013-06-29-15.31.38.948217-420 E159047E344          LEVEL: Error (OS)
PID     : 1766                 TID : 140735175762304PROC : db2star2
INSTANCE: avernet              NODE : 000
HOSTNAME: huashan.local
FUNCTION: DB2 UDB, SQO Memory Management, sqloMemCreateSingleSegment, probe:100
CALLED  : OS, -, shmget                           OSERR: 17

Note that for the install to go through, I increased the OS X shared memory, per this recommendation.


texts: Can I trigger events based on timestamp data or should I use polling?

texts:
I'm asking this question for SQL Server 2008+ but this could really be for any DBMS.

Imagine you have scheduled data, something like:

ID   Data   Timestamp
0    NULL   2014-11-22
1    ...    2014-11-24
2    ..     2014-11-25

Is there a way to trigger my application when a timestamp is reached ?
Or shouldn't I do this in the first place and use polling instead because of performance reasons just like with triggers when new rows are added ?

I think that in theory the 'trigger based on timestamp' seems much better than the 'trigger based on new row' concerning the performance because you can easily group rows together based on their timestamps.
However, I have absolutely no idea how to actually do this in practice.

FYI: I'm inexperienced with DBMS systems, so maybe I have overlooked something very obvious. Sorry in that case.


texts: auto_increment incremented on non-affecting queries

texts:
When performing the following query on 'sessions':

INSERT IGNORE INTO sessions (user_id, archived) VALUES (3, 0)

while:

CREATE TABLE `sessions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `archived` tinyint(3) unsigned NOT NULL,
  `valid_until` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  UNIQUE KEY `user_id_UNIQUE` (`user_id`),
  KEY `fk_sessions_users1_idx` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

with trigger:

CREATE TRIGGER before_insert_on_sessions BEFORE INSERT ON `sessions`
FOR EACH ROW SET new.valid_until = IFNULL(new.valid_until,DATE_ADD(NOW(), INTERVAL 1 DAY));

the first time you execute the query, everything is fine and the following is inserted:

id  user_id date_created    date_modified   archived    valid_until
1   3       ...             ...             0           ...

now enter the same query two more times, and it will say:

No errors, 0 rows affected, taking 8,0ms

Now change the query to:

INSERT IGNORE INTO sessions (user_id, archived) VALUES (4, 0)

So basically you only changed the value of user_id.

You'd expect the next row to be:

id  user_id date_created    date_modified   archived    valid_until
**2**   4       ...             ...             0           ...

instead, you get:

id  user_id date_created    date_modified   archived    valid_until
**4**   4       ...             ...             0           ...

So basically what's happening, is that the unique_key (id)'s auto_increment is being updated on every query, even though there are no actual new rows being made.

This shouldn't be right, should it?

Server version:     5.6.22 Homebrew


texts: Migrating sequences from postgres to EDB database

texts:
Scenario :

We are migrating data from a postgres DB to new EDB database .
We have migrated the sequence and all table data also .
Both DB is installed on a remote unix server and on diffrent machines.
There are 300+ sequences .

Issue : As we have inserted all table data by running a bunch of insert command which was exported from Dbeaver . And those insert command does not contains nextval() function . So all sequences in DB is still initialized with initial value . How to update the nextval of all sequence of all schema with the last value in new DB(EDB) as it was in old DB(postgres) ?


texts: MySQL Workbench is not finding MySQL service?

texts:
I set up a local MySQL server, currently with no databases. I'm trying to create a new server instance profile in MySQL Workbench to manage the server, and during the Create New Server Instance Profile wizard, it gets to the Windows Management section and tells me that No MySQL service found.

I went into Task Manager and found the process mysqld.exe to be running, under the user name of NETWORK SERVICE. Then I went into the Services tab and found that MySQLServerName (I custom named the Windows service) is also there, and status is running.

I ran cports and looked at what ports mysqld.exe is using, and ran a telnet command on that port. It's reporting that the port is being listened in on. I then ran the MySQL Command Line Client to be sure, and after \r it gives me a proper connection ID and a list of databases (NONE at this point).

But with all this, I can't make the wizard find a running service. I've Googled this and found no answers, so please, if someone would help shed some light on this issue that'd be great!


texts: Strategy for making Postgres support multiple versions of client code

texts:
I'm about to embark on an overhaul of our code for pushing to Postgres, and want to solicit some feedback before going on.

The setup here is that we've got a lot of software deployed with a non-Postgres database where we periodically push rows into a central Postgres database. (RDS 11.4) The chances of us updating all deployed copies of the code simultaneously are zero. So, we're always going to have multiple active versions in the field...sometimes a lot of versions apart. That's okay in itself, but it does make it unmanageable to bake naive INSERT statements into the client code. Which I have done. It has only bitten us slightly, but it could eventually become enormously painful. Any and all of the following DDL changes on our central Postgres can break pushes from deployed software that's on an old version:

Remove a field
Rename a field
Retype a field
Add a NOT NULL DEFAULT NULL field

Stating the obvious, I've got to move hard-coded/fixed references out of client code and into something else. Most folks likely have some sort of stack with an ORM, etc between their collector/field application and Postgres. We do not. So, with much help from people here on SO, I've built the following solution, using "hsys" as an example table.

For each table version create a custom type, like hsys_v1.

CREATE TYPE api.hsys_v1 AS (
    id uuid,
    name_ citext,
    marked_for_deletion boolean);

For each table and version, write an INSERT handling function that accepts an array of the custom type.

CREATE OR REPLACE FUNCTION ascendco.insert_hsys_v1 (data_in api.hsys_v1[])
  RETURNS int
AS $BODY$

-- The CTE below is a roundabout way of returning an insertion count from a pure SQL function in Postgres.
with inserted_rows as (
        INSERT INTO hsys (
            id,
            name_,
            marked_for_deletion)

        SELECT
            rows_in.id,
            rows_in.name_,
            rows_in.marked_for_deletion

        FROM unnest(data_in) as rows_in

        ON CONFLICT(id) DO UPDATE SET
            name_ = EXCLUDED.name_,
            marked_for_deletion = EXCLUDED.marked_for_deletion

        returning 1 as row_counter)

    select sum(row_counter)::integer from inserted_rows;

$BODY$
LANGUAGE sql;

The idea here is that when I change the table, I'll be able to create the a hsys_v2 type and an insert_hsys_v2 (hsys_v2[]) function to match. Then old clients can continue pushing in the old format, so long as I rewrite insert_hsys_v1 to map/convert/coerce things to the new table format.

I wrote a GUI to grab my table definitions and put a code generator on top a few weeks ago. And then I stopped. I realize I'm wondering if I'm missing something I should consider, and am hoping for someone to point out a hole in this strategy. I'm not being lazy about reaching out to local programmers....there aren't any. (I'm in rural Australia.)

If there's nothing wrong with this strategy, I'll do the overhaul. As a bonus, the work so far made it easy to add in code builders for custom casts and views. Not sure if they'll be useful or not, but I'm generating them at the same time.


texts: How to get EstimatedRows values from SQL Server execution plan nodes

texts:
When I execute the following code in an Azure SQL Database (v12) or SQL Server 2016 instance, I get values in the EstimatedRowsRead column, but not in the EstimatedRows column, and I'm not seeing why. Any ideas?

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
,PlanHandles AS (
    SELECT  plan_handle
            ,total_elapsed_time =   SUM(total_elapsed_time)
            ,execution_count    =   SUM(execution_count)
      FROM  sys.dm_exec_query_stats
     GROUP  BY plan_handle
)
,Plans AS (
    SELECT  ph.plan_handle, qp.query_plan, ph.total_elapsed_time, ph.execution_count
      FROM  PlanHandles ph
            OUTER APPLY sys.dm_exec_query_plan(ph.plan_handle) qp
)
SELECT  p.total_elapsed_time
        ,p.execution_count
        ,p.plan_handle
        ,p.query_plan
        ,NodeId                 =   s.i.value(N'@NodeId', N'INT')
        ,EstimatedRowsRead      =   s.i.value(N'(@EstimatedRowsRead)[1]', N'FLOAT')
        ,EstimatedRows          =   s.i.value(N'(@EstimatedRows)[1]', N'FLOAT')
  FROM  Plans p
        CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/*') q(n)
        CROSS APPLY n.nodes('.//RelOp[IndexScan and IndexScan/Object[@Schema!="[sys]"]]') s(i)
 WHERE  s.i.value(N'(@EstimatedRowsRead)[1]', N'FLOAT') > 1.0;


texts: Which event to look for User Mapping changes

texts:
We have been having issues where login's are having their user mapping changed unexpectedly and I am looking to have an alert fire off whenever that happens. Is there a way for me to capture that? I have been able to create a trigger for altering a login or changing their server roles, but not the user mapping itself.

Edit
To be more clear, I am referring to database role memberships for users is what changing unexpectedly.


texts: SQL Server 2012 doesn't use enough memory

texts:
Under heavy Load, my SQL Server database is executing queries really slowly. I have 96 GB of RAM and my CPU is high (100%) but my physical memory usage is at 9%. I looked at the SQL Server memory usage which is in the screenshots below.  I enabled Locked In Pages but it didn't seem to help. I'm wondering if there is anything I can do in SQL Server to utilize more memory.

Version: SQL Server 2012 Web Edition SP3 (x64)
OS: Windows Server 2012 R2 (x64)

max server memory is set to the default. It's a web garden for one site. My database is 2 GB. I thought SQL Server would use more memory under a heavy load with more RAM, scaling up for the number of users/queries.


texts: Enable MySQL to use more than 1 core (multicore)

texts:
I am running a very intensive MySQL query and noticed that only 1 core is being used to 100% while the other cores are idle. The system is running Ubuntu 12.04 and MySQL 5.5.28

Is there a way to force MySQL to use all cores?


texts: What's the most cost effective or cheapest way for high availabity on Oracle Standard Edition One?

texts:
We are currently using Oracle Standard Edition One running on two physical database server.

If I wanted to get higher availability, what's the most cost effective way or cheapest way to achieve this?

Thank-you for your suggestions!


texts: How to add the numbers of two distinct SELECT statements Mysql

texts:
I have the following query that uses data from two tables:
(select age_at_visit from table1 where patient_id=577)
UNION
(select DATEDIFF(CURDATE(),visit_date)/365.25 from table2 where patient_id=577);

This works, and I get the two numbers. But what do I need to do in order to add them, and get the final solution?


texts: redis newbie - how to create hash within a hash?

texts:
I want to create this type of a structure in redis: (its basically json data)

{
    "id": "0001",
    "name":"widget ABC",
    "model": "model123",
    "service":"standard",
    "admin_password": 82616416,
    "r1":
        {
            "extid":"50000",
            "password":"test123",
        },
    "r2":
        {
            "ext":"30000",
            "password":"test123",
        },
}

What I've tried so far:

I've tried to create a hash without the "sub" hashes, just to make sure I have the basics down pat.
So here's what I started with from the redis-cli:

HMSET widget:1 id 0001 name 'widget ABC' model 'model123' service standard admin_password 82616416
HMSET widget:2 id 0002 name 'widget ABC' model 'model123' service standard admin_password 12341234

That seems to work. I can see that I have 2 "records" of widget data in a widget collection.

But my attempt to create a record with the r1 data fails.

This is what I tried:

HMSET widget:3 id 0002 name 'widget ABC' model 'model123' service standard admin_password 12341234 r1{extid 50000}

This creates a hash key "r1{extid" with a hash value of "50000}"

Any suggestions would be appreciated.  I think my problem is my lingo.  I just don't know enough about the redis syntax to know what words to use in my google search.

Perhaps I just have to "flatten" the data like so:

HMSET widget:3 id 0002 name 'widget ABC' model 'model123' service standard admin_password 12341234 r1_extid 50000 r1_password test123 r2_extid 30000 r2_password test123

Thanks.


texts: Does the partition key also have to be part of the primary key?

texts:
I am partitioning a table based on a column that is not a primary key?  I've read some conflicting information today on whether the partition column must be a part of the primary key.  My gut says no, but I am not 100% sure.  So questions...

Must the partition column be part of the primary?  Is it recommended one way or the other?
Do I have to create an index for the partition key, or does the DBMS do it automatically on its own?


texts: Should Service Broker use SQL or Windows Authentication?

texts: I was curious what the best practice would be, for properly setting up Service Broker in SQL Server 2008. Should I use a SQL authenticated user, or should I use a Windows NT Authenticated user based off AD or LDAP server?


texts: MongoDB Ops Manager - CPU Time - No data available

texts:
We have 5 groups and enabled on all groups monitoring agent. We use Ops Manager 1.6.2.251.

All metrics get collected and are available in a nice graph.
CPU Time is not available on all groups. Our VMs are OpenStack based with RHEL7.

Howto debug that? Found nothing relevant to CPU time in /opt/mongodb/mms/logs


texts: Problem with installation SQL Server 2019

texts:
I got a problem when I wanted to install SQL Server 2019. I don't have Setup Bootstrap on this path, I only have folders SSEI and License Terms on this path.

Windows Installer log file: C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log\20191105_001048\msoledbsql_Cpu64_1.log

I don't know why I don't have Setup Bootstrap on that path, it is the first time I'm installing SQL Server 2019.

The error:


texts: What is the purpose of SET NULL in Delete/Update Foreign Keys constraints?

texts:
I am probably being narrow minded, but if I create a foreign key constraint and a row gets updated or deleted, I lose that connection if the child table's column gets set to NULL.

What is the purpose of intentionally keeping these orphaned rows?


texts: Multi-tenant access to a Postgres database?

texts:
I want to share a Postgres database with multiple tenants. Is there a way I can provide each tenant a user such that their queries are "re-written" with filters? For example, if user 'bob' of tenant 'bobsbatteries' connects to the database and does a select on 'item', its rewritten to filter out anything not related to 'bobsbatteries':

select * from items

becomes

select * from items where tenant='bobsbatteries'

It should work with select, updates and deletes with joins, subqueries etc.


texts: Why is my Target Server Memory slightly higher than my Total Server Memory?

texts:
I'm doing a performance test on a high traffic (16000+ users, estimated 1000+ concurrent at peak) Microsoft Dynamics database.
The server has gone on line a few months ago, and I'm doing some tests to see how it is handling the current traffic.

While checking some performance counters during business hours I came across an oddity in the target and total server memory.
While I would only flag this as being an issue if the target memory was clearly higher than the total memory, combined with perhaps other counters (depending on the amount of difference) the results here are somewhat odd.

Using:

SELECT object_name, counter_name, cntr_value FROM sys.dm_os_performance_counters
where (object_name like '%Buffer Manager%' and counter_name like '%page life%')
or (object_name like '%Memory Manager%' and counter_name like '%memory grants pending%')
or (object_name like '%Memory Manager%' and counter_name like '%target server memory%')
or (object_name like '%Memory Manager%' and counter_name like '%total server memory%');

I came to the following results:

counter_name                 |    cntr_value
------------------------------------------------
Page life expectancy         |    14887
Memory Grants Pending        |    0
Target Server Memory (KB)    |    44040192
Total Server Memory (KB)     |    44040184

Clearly there is currently no memory pressure, and the page life expectancy is higher than required. However it looks like at some point the sql server was exactly 8 KB short of memory.

Something about these numbers seems odd to me, but I can't put my finger on it.
Is this an indication that at some point the sql server was on the verge of a memory problem?
Am I correct in thinking that because these numbers are so similar, I can dismiss it for now, and test again when a decent amount of time has passed?


texts:
I have inherited several databases that use GUIDs as PKs.  Instead of all the datatypes being uniqueidentifier most are varchar(50) and some varchar(100). Fields are true GUID some where created by

  myID = 'xxx'+convert(varchar(40),newID())

In general its a bit of a mess

What are the performance implications for this design and is it worth re-working the tables to convert the datatypes.  Tables are generally around 1/2 M records with a couple table in the 2-4M record range.

The impetus of this question is that I am trying (unsuccessfully) to optimize an proc that joins 24 tables and views and the server is not handling it very well.

Thanks for any insight


texts: How to backup a postgresql db to export on AWS lightsail

texts:
I want to take a backup from my postgresql db which I use for my Django project.
I'm using ubuntu 18.04 and want to import it in AWS lightsail.
I read pgadmin docs on how to take a backup with a definition for each option item under Dump options.
But I don't know what options should be yes or no for restoring this db when I want to deploy my project on AWS lightsail.
I took a backup with custom format and UTF8 encoding with postgres role name from my db.
under dump options I selected following options:

pre-data yes
data yes
post-data yes
blobs yes
comments yes
user column inserts yes
use insert commands yes
include create database statement yes
include drop database statement yes
qouting yes
verbose messages yes

When I want to restore it in a db in AWS lightsail I get the following error for some tables:
Running command:

/usr/bin/pg_restore --host
"blah-blah-blah"
--port "5432" --username "dbmasteruser" --no-password --dbname "cms_restore" --verbose "/home/shahriar/cmsproject_backup.txt"

pg_restore: error: could not execute query: ERROR:  role "postgres" does not exist
Command was: ALTER TABLE public.auth_permission_id_seq OWNER TO postgres;

Failed (exit code: 1).

But when I check the db in AWS I see my data in pgadmin.
What is wrong with the backing-up process or restoring process?


texts:
Consider the following table:

ID | GROUP_ID | ORDER_VAL | RESET_VAL | VAL
---+----------+-----------+-----------+-----
1  | 1        | 1         | (null)    | 3
2  | 1        | 2         | (null)    | 2
3  | 1        | 3         | (null)    | 1
4  | 1        | 4         | 4         | 2
5  | 1        | 5         | (null)    | 1
6  | 2        | 1         | (null)    | 4
7  | 2        | 2         | 2         | 3
8  | 2        | 3         | (null)    | 4
9  | 2        | 4         | (null)    | 2
10 | 2        | 5         | (null)    | 2
11 | 2        | 6         | (null)    | 4
12 | 2        | 7         | 14        | 2
13 | 2        | 8         | (null)    | 2

For each row, I need to compute the cumulative sum of VAL for all previous rows (ordered by ORDER_VAL and grouped by GROUP_ID), but each time a non-NULL RESET_VAL is encountered, I need to use that value for the sum. The rows that follow also need to build on top of the RESET_VAL instead of using the actual sum. Note that each group can have multiple reset values.

This is the result I expect for the above table:

ID | GROUP_ID | ORDER_VAL | RESET_VAL | VAL | CUMSUM
---+----------+-----------+-----------+-----+-------
1  | 1        | 1         | (null)    | 3   | 0
2  | 1        | 2         | (null)    | 2   | 3
3  | 1        | 3         | (null)    | 1   | 5
4  | 1        | 4         | 4         | 2   | 4
5  | 1        | 5         | (null)    | 1   | 6
6  | 2        | 1         | (null)    | 4   | 0
7  | 2        | 2         | 2         | 3   | 2
8  | 2        | 3         | (null)    | 4   | 5
9  | 2        | 4         | (null)    | 2   | 9
10 | 2        | 5         | (null)    | 2   | 11
11 | 2        | 6         | (null)    | 4   | 13
12 | 2        | 7         | 14        | 2   | 14
13 | 2        | 8         | (null)    | 2   | 16

If not for the reset values, I could use a window query:

SELECT temp.*,
       COALESCE(SUM(val) OVER (PARTITION BY group_id ORDER BY order_val ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
                0) AS cumsum
FROM temp;

SQLFiddle of the above

I was originally mistakenly thinking that I could just put RESET_VAL at the beginning of the COALESCE, but this won't work since it won't reset the value for following rows.

I also tried this solution, but it only resets back to zero, not to a value in the column. Adapting it to do so is proving non-trivial because that value must propagate to all following rows.

A recursive query seems like a natural fit, but I haven't been able to figure out how to do that as of yet.

I should probably mention that the table I actually have to deal with is much larger (hundreds of thousands to a couple million rows) than the example above, so please mention if there are any performance pitfalls with any answer.


texts: DBCC CHECKTABLE taking 15+ minutes to run on an empty table

texts:
I have a database where DBCC CHECKTABLE on a number of small or empty tables taking over 15 minutes to run.  When it finishes there are no failures or errors.  The performance on everything else on the server is at a very acceptable form.  There was nothing else running at the same time.

I have also tried DBCC CLEANTABLE and updated stats with fullscan.

I'm using SQL Server 2016 Enterprise Edition (13.0.5201.2)

Example table:

CREATE TABLE [Schema1].[Table1](
    [col1] [int] NOT NULL,
    [col2] [nvarchar](100) NOT NULL,
    [col3] [xml] NOT NULL,
 CONSTRAINT [PK_1] PRIMARY KEY CLUSTERED
(
    [col1] ASC,
    [col2] 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


texts: How to interpret this "db.explain()" for this mongodb query

texts:
db.tablebusiness.find({ "LongitudeLatitude" : { "$within" : { "$centerSphere" : [[106.772835, -6.186753], 0.0089967464101566] } }, "indexContents" : { "$all" : ["warung"] }, "Prominent" : { "$gte" : 15 } }).limit(50).explain;

{
        "cursor" : "GeoBrowse-circle",
        "nscanned" : 50,
        "nscannedObjects" : 50,
        "n" : 50,
        "millis" : 214,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : false,
        "indexBounds" : {
                "LongitudeLatitude" : [ ]
        },
        "keysChecked" : NumberLong(13215),
        "lookedAt" : NumberLong(13215),
        "matchesPerfd" : NumberLong(13215),
        "objectsLoaded" : NumberLong(6674),
        "pointsLoaded" : NumberLong(0)
}

The query search for businesses within a circle that contains certain character. Typical data looks like this

/* 4 */
{
  "_id" : "made's-warung__-6.18_106.78",
  "BuildingID" : null,
  "Title" : "Made’s Warung",
  "InBuildingAddress" : null,
  "Building" : null,
  "Street" : "Seminyak-Kuta",
  "Districts" : [],
  "City" : "Bali",
  "Country" : "Indonesia",
  "DistanceFromGoogleAddress" : 0.0,
  "Checkin" : 0,
  "Note" : null,
  "PeopleCount" : 106,
  "Prominent" : 106.0,
  "CountViews" : 0,
  "StreetAdditional" : null,
  "LongitudeLatitude" : {
    "Longitude" : 106.783746,
    "Latitude" : -6.177905
  },
  "Rating" : {
    "Stars" : 0.0,
    "Weight" : 0.0
  },
  "CurrentlyWorkedURL" : null,
  "Reviews" : [],
  "ZIP" : null,
  "Tags" : ["Asian Restaurant"],
  "Phones" : [],
  "Website" : null,
  "Email" : null,
  "Price" : null,
  "openingHour" : null,
  "Promotions" : [],
  "SomethingWrong" : false,
  "BizMenus" : [],
  "Brochures" : [],
  "Aliases" : [],
  "indexContents" : ["asian", "sian", "ian", "an", "n", "restaura", "estauran", "staurant", "taurant", "aurant", "urant", "rant", "ant", "nt", "t", "made’s", "ade’s", "de’s", "e’s", "’s", "s", "warung", "arung", "rung", "ung", "ng", "g"]
}

Also look at this one:

db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.044983732050783008 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$all" : [/^aru/, /^asi/] } }).limit(50).explain();

{
        "cursor" : "GeoSearchCursor",
        "nscanned" : 50,
        "nscannedObjects" : 50,
        "n" : 50,
        "millis" : 2977,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : false,
        "indexBounds" : {

        }
}
>

What does it mean? Is indexes used? Which one?


texts: Can an SQL Server Failover Cluster support a named instance?

texts: If I currently have someName\instanceName on a standalone SQL Server and I want to migrate to a failover cluster, http://itknowledgeexchange.techtarget.com/sql-server/moving-a-standalone-instance-to-a-cluster-while-keeping-the-name-and-ip/ hints that I can give the cluster the network name someName but that page doesn't deal with instance names.  On the other hand, Figure 8-10 on https://www.microsoftpressstore.com/articles/article.aspx?p=2224049&seqNum=2 shows an instance name (with the default value) suggesting that you can create named instances.  What isn't clear to me from that page is whether sql-cluster refers to SQL-C\MSSQLSERVER (generically clusterName refers to hostName\instanceName) or if those three fields are independent and sql-cluster\MSSQLSERVER is a valid way to address that named instance on the cluster?  (And, of course, if the "SQL Server instance name" was not the default but, say, myInstance, is sql-cluster\myInstance valid.)  I've found conflicting hints and no conclusive or authoritative answers on the web.


texts: Populate the columns of one table with the records of another

texts:
I have one table, FRUIT, describing some measurements that I make:

ID Tag Amount   Units
1  A     75    Apples
2  B     23   Bananas

I would like another table, HISTORY, that tracks the amount of fruit over time, much like a historian. That is every x seconds/minutes I would like to record the current amount of fruit at that time.

Time      A    B
earlier   35   45
Now Now   35   23
Now       75   23

The trick is that if the FRUIT table is updated to include say Carrots one simply adds a record. How then would one add a field to HISTORY to account for this?

My understanding is that the user has to specify the information for FRUIT i.e. when adding a record to FRUIT, this is simply an extra record. For HISTORY however I'm now adding an extra field. Is it possible to do this on the fly? I know before hand that every column other then time is of type numeric/double/float and I can fully specify the other properties for the field using similarly known information. I'm just not sure how to do this automatically.

If it helps any I'm in MSAccess for now but will switch to MySQL soonish.


texts:
I run Sql server instance and a dot.net core app on the same server for a while and today is the first time I came across with this issue, my website doesn't get data from the server and it gets this error:

There is insufficient system memory in resource pool 'internal' to run
this query. Error: 596, Severity: 21, State: 1. (Params:). The error
is printed in terse mode because there was error during formatting.
Tracing, ETW, notifications etc are skipped.   A severe error occurred
on the current command.  The results, if any, should be discarded.

and this error as well:

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

The sql server version:

Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64)
Jul 12 2019 17:43:08   Copyright (C) 2017 Microsoft Corporation
Express Edition (64-bit) on Linux (Ubuntu 16.04.6 LTS)

I typed the top command and I got this:

It used to work fine for more than 3 months without any error.
How can I find out what is causing Sql server to use all this cpu all of a sudden?


texts: What happens when two separate processes perform a full backup against the same database?

texts:
I have an instance of SQL Server 2012 RTM running. I have two separate applications that want to do a backup of a database lets call them "AppFull" and "AppFullCopyOnly".

AppFull does a full database backup followed by a truncate of the transaction log. AppFullCopyOnly also does a full database backup but with the COPY_ONLY flag so as not to interfere with the AppFull process.

So my question is: Will these applications interfere with each other if they are both processing the same database? I realise there will be an impact on the server, but what I need to know is whether either backup will not complete, integrity be compromised etc. I need to understand both scenarios namely they run in these orders:

AppFull and then AppFullCopyOnly
AppFullCopyOnly and then AppFull

PS. I cannot control when the applications run so that there is no collision and hence also not their order of execution. I also have no control over them both running - they both have to both run.


texts: How to escape special characters in MySQL

texts:
When I do select * .. | mysql ... > /tmp/file from a table with text, there are some problematic characters that prevent me from loading it to a different db using copy (postgres) or load into (mysql).
Character like tab, new line are translated automatically to \n and \t, but some of the problematic characters are escape ^[, CR ^M, ^U,^Z,^F,^H and maybe other that I haven't seen before.
Usually I would just replace it like echo "select * .." | mysql .. | sed 's/\r/\\r/g', but there are too many unknown chars there. So instead of replacing them after the select I would like a function that retrieve the text already escaped (I guess remove them is also fine).
How should I do it?


texts:
I have this query:

SELECT *
FROM location
WHERE to_tsvector('simple',unaccent2("city"))
   @@ to_tsquery('simple',unaccent2('wroclaw'))
order by displaycount

I'm happy with it:

"Sort  (cost=3842.56..3847.12 rows=1826 width=123) (actual time=1.915..2.084 rows=1307 loops=1)"
"  Sort Key: displaycount"
"  Sort Method: quicksort  Memory: 206kB"
"  ->  Bitmap Heap Scan on location  (cost=34.40..3743.64 rows=1826 width=123) (actual time=0.788..1.208 rows=1307 loops=1)"
"        Recheck Cond: (to_tsvector('simple'::regconfig, unaccent2((city)::text)) @@ '''wroclaw'''::tsquery)"
"        ->  Bitmap Index Scan on location_lower_idx  (cost=0.00..33.95 rows=1826 width=0) (actual time=0.760..0.760 rows=1307 loops=1)"
"              Index Cond: (to_tsvector('simple'::regconfig, unaccent2((city)::text)) @@ '''wroclaw'''::tsquery)"
"Total runtime: 2.412 ms"

But when I add LIMIT, the execution takes more than 2 seconds:

SELECT *
FROM location
WHERE to_tsvector('simple',unaccent2("city"))
   @@ to_tsquery('simple',unaccent2('wroclaw'))
order by displaycount
limit 20

Explain:

"Limit  (cost=0.00..1167.59 rows=20 width=123) (actual time=2775.452..2775.643 rows=20 loops=1)"
"  ->  Index Scan using location_displaycount_index on location  (cost=0.00..106601.25 rows=1826 width=123) (actual time=2775.448..2775.637 rows=20 loops=1)"
"        Filter: (to_tsvector('simple'::regconfig, unaccent2((city)::text)) @@ '''wroclaw'''::tsquery)"
"Total runtime: 2775.693 ms"

I think that it's some issue with ORDER BY and LIMIT. How can I force PostgreSQL to use the index and do the ordering at the end?

Subquery doesn't help:

SELECT *
FROM (
    SELECT *
    FROM location
    WHERE to_tsvector('simple',unaccent2("city"))
       @@ to_tsquery('simple',unaccent2('wroclaw'))
    order by displaycount
) t
LIMIT 20;

or:

SELECT *
FROM (
    SELECT *
    FROM location
    WHERE to_tsvector('simple',unaccent2("city"))
       @@ to_tsquery('simple',unaccent2('wroclaw'))
) t
order by displaycount
LIMIT 20;


texts: MariaDB maintain key order in COLUMN_CREATE

texts:
I am using a 10.3.11 table with the following structure

MariaDB> desc MetaData;
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| meta_data_id | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| foreign_id   | int(11) unsigned | NO   | MUL | NULL    |                |
| api_name     | varchar(128)     | NO   | MUL | NULL    |                |
| index1       | varchar(255)     | YES  | MUL | NULL    |                |
| index2       | varchar(255)     | YES  | MUL | NULL    |                |
| index3       | varchar(255)     | YES  | MUL | NULL    |                |
| index4       | varchar(255)     | YES  | MUL | NULL    |                |
| meta_data    | blob             | YES  |     | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+

The MetaData contains dynamic columns created using

INSERT INTO MetaData(meta_data) VALUES(COLUMN_CREATE('CallID', 'as','field1','value1','field3','value2','field2', 'value3','field4','value4
'));

The indexes are required because, as its names suggests, the dynamic column names can conform to any naming convention. index1 stores the value of the first dynamic column etc. and is indexed for ease of searching.

The problem is that COLUMN_CREATE appears to reorder the column name keys alphabetically, meaning that in the above example field2 is put in front of field3 and hence gives this.

MariaDB > select column_json(meta_data) from MetaData;
+-----------------------------------------------------------------------------------------+
| column_json(meta_data)                                                                  |
+-----------------------------------------------------------------------------------------+
| {"CallID":"as","field1":"value1","field2":"value3","field3":"value2","field4":"value4"} |
+-----------------------------------------------------------------------------------------+

This means that a search in index2 or index3 will not return the correct data. Is it possible to retain the column order or do I need to rethink the strategy by reordering the columns and index values before inserting into the table?

CREATE TABLE `MetaData` (
  `meta_data_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `foreign_id` int(11) unsigned NOT NULL,
  `api_name` varchar(128) NOT NULL,
  `index1` varchar(255) DEFAULT NULL,
  `index2` varchar(255) DEFAULT NULL,
  `index3` varchar(255) DEFAULT NULL,
  `index4` varchar(255) DEFAULT NULL,
  `meta_data` blob DEFAULT NULL,
  PRIMARY KEY (`meta_data_id`),
  KEY `foreign_id` (`foreign_id`),
  KEY `index1` (`index1`),
  KEY `index2` (`index2`),
  KEY `index3` (`index3`),
  KEY `index4` (`index4`),
  KEY `api_name` (`api_name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;


texts: SQL Server prevent SA from setting encryption off

texts:
Is there a way to prevent any user (including SA) from setting database encryption off (unless password or physical file - cert is provided)?

That is: not just some column or specific data - but securing the whole database from being detached and used on another server.


texts: WAIT STATS - high Wait Time on ASYNC_IO_COMPLETION

texts:
I currently look at my wait stats. I followed best practices explained by Paul Randal and go through the documents he references.

Looking at the statistic slices I take once / day for a while now from all production servers, I see significant high values for Avg_Wait_S, meaning the relation of total Wait in Seconds divided by WaitCounts. The value for wait count is very low compared to all the other wait types (the lowest), but on each of my servers the AvgWait_S for this type is between 120 - 2493 seconds! This looks like a massive wait time.

I read this can anyhow be caused by backups. But I wonder if this is kind of a 'normal' value? How to get more details about it?

Here is a sample resultset of my wait stats:

WAITTYPE            Wait_S      Resource_S  Signal_S    WaitCount   Percentage  AvgWait_S   AvgRes_S    AvgSig_S
BACKUPIO            46151.80    45093.07    1058.74     22819151    26.55       0.0020      0.0020      0.0000
CXPACKET            45057.81    27926.59    17131.22    20387211    25.92       0.0022      0.0014      0.0008
BACKUPBUFFER        16658.83    15867.71    791.12      8993341     9.58        0.0019      0.0018      0.0001
PAGEIOLATCH_SH      15326.95    15284.65    42.30       2131848     8.82        0.0072      0.0072      0.0000
ASYNC_IO_COMPLETION 14203.17    14203.17    0.00        9           8.17        1578.1303   1578.1303   0.0000
WRITELOG            8570.20     8377.81     192.39      2136964     4.93        0.0040      0.0039      0.0001
PAGEIOLATCH_EX      7691.32     7673.31     18.01       1777069     4.43        0.0043      0.0043      0.0000
SOS_SCHEDULER_YIELD 4548.90     43.44       4505.47     13294384    2.62        0.0003      0.0000      0.0003
LCK_M_S             3018.76     3018.06     0.71        1704        1.74        1.7716      1.7712      0.0004
ASYNC_NETWORK_IO    2678.83     2147.78     531.05      4372825     1.54        0.0006      0.0005      0.0001
LCK_M_SCH_S         2612.09     2612.08     0.01        37          1.50        70.5971     70.5968     0.0003


texts: Streaming Replication Resource Management (PGSQL) on FreeBSD 8.x

texts:
I am attempting to set up PGSQL-HA streaming replication on freeBSD 8.2 (willing to upgrade to 8.4 if need be).

The set up is simple: 2 nodes, one active one standby. I have successfully set up streaming replication between the two nodes, but I am now trying to set up a resource agent/manager to deal with automatic failover and STONITH.

I have attempted to use Heartbeat/Corosync/Pacemaker for this, but am running into difficulty building them from source (Pacemaker, in particular).

Is there an easier way than using Pacemaker for a simple 2 node set up?  I have little experience with this and especially with freeBSD.

Thanks!

*Note: FreeBSD 8.x is required, the servers are running other services that would make it too difficult to upgrade or change the OS


texts:
I have the following scenario:
Pub Databases:
 db-A
 db-B
 db-C

Sub Database:
 db-Sub

Table in Pub:
db-A.T1
db-B.T1
db-C.T1

Table in Sub:
db-X.T1_a
db-X.T1_b
db-X.T1_c

Replication:
db-A.T1 -> db-Sub.T1_a
db-B.T1 -> db-Sub.T1_b
db-C.T1 -> db-Sub.T1_c

Table from multiple databases replicating to a distinct tables in a single database
Transactional replication
The source tables have the same structure.

What we are observing is that data from db-A.T1 is going to db-Sub.T1_c etc. When we are from replicating a single db, it works but as soon as we bring another replication online this contamination is happening.
Would really appreciate an explanation to understand what is happening and how do I fix it. I am not a dba by any stretch.


texts: Python code in T-SQL environment gives "access a socket in a way forbidden by its access permissions" Error

texts:
Help on the following please.

I have this code Python Code

  import requests
  r = requests.get("https://www.google.com")
  r.status_code
  print(r.status_code)
  print("Python Rawks!!!!!!")

which runs fine in command line mode and returns the 200 status code.

The same code in T-SQL query window fails with OSError: [WinError 10013] An attempt was made to access a socket in a way forbidden by its access permissions

execute sp_execute_external_script@language = N'Python',@script = N'
          import requests
          r = requests.get("https://www.google.com")
          r.status_code
          print(r.status_code)
          print("Python Rawks!!!!!!")
'

  Error in execution.  Check the output for more information.
  Traceback (most recent call last):
  File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\requests\packages\urllib3\connection.py", line 141, in _new_conn    (self.host, self.port), self.timeout, **extra_kw)
  File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\requests\packages\urllib3\util\connection.py", line 83, in create_connection    raise err
  File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\requests\packages\urllib3\util\connection.py", line 73, in create_connection    sock.connect(sa)

  OSError: [WinError 10013] An attempt was made to access a socket in a way forbidden by its access permissions
  During handling of the above exception, another exception occurred:


texts:
We have an industrial program. For a device that is running 24 hours a day. The database is constantly used.
What I noticed today after 4 months: The database log file size was 80 Gig !

Query execution speeds were slower. Shrink did not reduce the volume of logs. With my search I set the Recovery Model to Simple, and then I did the Shrink . Logs volume reduced to 1 MB !

Recovery model set to Full.

But why did this happen? And how can I stop it?

If you need more info tell me to add it


texts:
I have a logging table

CREATE TABLE EMAIL_LOGGING
(
  ID               NUMBER(9)                    NOT NULL,
  SEND_DATE        TIMESTAMP(6)                 DEFAULT LOCALTIMESTAMP        NOT NULL,
  SEND_TO_USER_ID  NUMBER(9)                    NOT NULL);

and a package that inserts into it with this fragment:

  INSERT INTO EMAIL_LOGGING ("ID",SEND_DATE, SEND_TO_USER_ID)
       VALUES (NULL, LOCALTIMESTAMP,send_to_user_in);

When this is called from a DBMS_JOB from one package it inserts Greenwich mean time.  When it is called from a DBMS_JOB from another package that uses a db_link to another database it correctly inserts the local time.

I think this is caused by the client's timezone being used but adding this to the job did not resolve it:

execute immediate 'alter session set time_zone=local';

Of course the best solution is to move to DBMS_Scheduler but until that is done how can I ensure that the timestamp that is inserted is the local time?

select * from v$version where banner like 'Oracle%';
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
SELECT DBTIMEZONE FROM DUAL;
+00:00
SELECT SESSIONTIMEZONE from dual;
-04:00


texts: Why does CCI creation on an empty table request a huge memory grant?

texts:
On a QA server with very low memory, I experienced error 8545 when creating clustered columnstore indexes on new tables in SQL Server 2016:

  Error 8645: A time out occurred while waiting for memory resources to execute the query. Re-run the query.

I can easily reproduce a large requested memory grant on my local machine. For the following code:

DROP TABLE IF EXISTS dbo.MY_FIRST_FACT_TABLE;

CREATE TABLE dbo.MY_FIRST_FACT_TABLE (
    ID BIGINT NOT NULL,
    COL1 BIGINT NULL,
    COL2 BIGINT NULL,
    COL3 BIGINT NULL,
    COL4 BIGINT NULL,
    COL5 BIGINT NULL,
    COL6 BIGINT NULL,
    COL7 BIGINT NULL,
    COL8 BIGINT NULL,
    COL9 BIGINT NULL,
    COL10 BIGINT NULL,
    STRING1 VARCHAR(100) NULL,
    STRING2 VARCHAR(100) NULL,
    STRING3 VARCHAR(100) NULL,
    STRING4 VARCHAR(100) NULL,
    STRING5 VARCHAR(100) NULL
);

CREATE CLUSTERED COLUMNSTORE INDEX MY_FIRST_CCI ON dbo.MY_FIRST_FACT_TABLE;

I get a memory request of around 512 MB of memory. There is a warning for the excessive memory grant in the actual execution plan:

Even though the query uses 0 KB of memeory it still might time out depending on other activity on the server. Why does SQL Server request so much memory? What can I do about it?


texts: Store FOR XML result in xml variable (using WITH statement)

texts:
I am creating xml with a SQL query that works like this: First a CTE, then the SELECT statement that creates the XML.

That works. However I would like to store the output in a XML variable (a table variable is also ok if that's easier). But I can't seem to get it to work (see second snippet). Any suggestions?

The XML generation that works:

;WITH cte
       AS (SELECT ...
          )
  SELECT ...
  FOR XML PATH ('root')

This was my attempt to store it in a table variable, but I couldn't get it to work:

DECLARE @myXml TABLE(
    x xml
);
;WITH cte
       AS (SELECT ...
          )
  INSERT INTO @myXml SELECT ...
  FOR XML PATH ('root')

This is the resulting error message:

  Meldung 6819, Ebene 16, Status 1, Zeile 240
  Die FOR XML-Klausel ist in einer INSERT-Anweisung nicht zulässig.

(I can't use FOR XML in an insert statement.)


texts:
I've got two pairs of servers (each identical hardware and OS) with replication running on both pairs.
Running MySQL 5.5.34 on Windows Server 2008 R2.

I'm trying to track down a problem that has occurred twice now on on of the pairs.

I get a 1366 error "Incorrect integer value '\x00' for column Flag at row 1" for the following call:

UPDATE mytable SET Flag = 0 where Id = 10

When I issue the show slave status command.

On the other pair I don't ever get this error when replicating.

The type of Flag is tinyint(1).

I haven't been able to identify any differences between the two pairs yet.

Ideas?

Update:
I posted an answer to this below.  I believe it has something to do with the serialization of the value when sent from the master to the slave.  The representation of the BIT value when replicated is not in a form that can be applied to a column of type TINYINT.

In this case the stored procedure took a flag value as a BIT and used that to update a TINYINT column.  While we can agree that those datatypes should match, it is the case that on the master a call to that stored procedure works just fine, meaning the BIT parameter's value is properly converted over to the TINYINT column.


texts: Sql server - The provided statistics stream is corrupt


texts: Retrieving orders that only contain certain product IDS

texts:
I am trying to retrieve a list of transaction IDS that only contain at least 5 product IDS, the baskets could have many more product IDS but it must contain these 5 also.

My table looks something like this:

| transaction_id | trans_date | product_id | store_id |

The table is all at product_id line level detail, nothing is rolled up.

select * from Transactions
where trans_date in ('2019-03-07','2019-03-06','2019-03-08','2019-03-09','2019-03-10')
and product_id in ('000000000021003503','000000000060280873','000000000060281480','000000000060281642','000000000020059281')

This is recovering all the transactions that contain these ids but I need it to be rolled up to a transaction level, so if the transaction_id contained these ids (at least) then its included.

Hopefully this makes sense


texts: How much RAM is required for 5000 concurrent users MySQL

texts:
Going by this :
When should I think about upgrading our RDS MySQL instance based on memory usage?
for max_connections to be set at 5000 concurrent users,how much should be the RAM capacity be ?

If I add per connection over head for 5000 users, as per what I set the values as shown below, I get :

[ read_buffer_size (0.125) MB + sort_buffer_size (2 MB)  +  join_buffer_size (2 MB)  +  read_rnd_buffer_size (1 MB) +   thread_stack (0.25 MB) +    binlog_cache_size (0.03125 MB) ] * 5000

= ~ 26 GB.

And as per my understanding, innodb_buffer_pool_size we normally give as 75% of RAM - for optimal performance.

So my question is, per connection over head (26 GB) + 75% of RAM = RAM, then RAM capacity should be ~ 104 GB ??

Can any one pls help me out on this.


texts: How to prevent daily index fragmentation of 99%

texts:
I have a highscore table for 100.000 players that is being inserted into 2 times a day with one record per player. At the end of the day the index fragmentation for the indexes in that table is 99%. Is there a way to prevent this by tweaking the settings?

CREATE TABLE HighScore(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [user] [int] NULL,
    [player] [int] NULL,
    [round] [tinyint] NULL,
    [group] [int] NULL,
    [rank] [int] NULL,
    [delta] [int] NULL,
    [roundpoints] [int] NULL,
    [totalpoints] [int] NULL,
PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [HighScore_RoundGroup_Nidx] ON .[HighScore]
(
    [round] ASC,
    [group] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
GO


texts: Why does ORACLE cache recovery create uncommited blocks during instance recovery?

texts:
The following is an excerpt from an ORACLE book

  During cache recovery, Oracle replays the transactions from the online redo log files since
  the last checkpoint. During this roll forward operation, both committed and uncommitted
  changes are applied to the data files. At the end of the roll-forward operation, the data files
  will have committed changes, uncommitted changes that were written to the data files to free
  up buffer cache space and uncommitted changes applied by the roll-forward operation. The
  database can be opened as soon as cache recovery is complete.

The sentece in bold above is the one I don't understand. How can a redo log file generate uncommited data since all its information are recorded after COMMIT ?

Further it says

  In the transaction-recovery phase of instance recovery, Oracle applies
  undo blocks to roll back uncommitted changes in data blocks that were either written before
  the instance crash or made by the roll-forward operation during cache recovery.

I understand that if dirty blocks were written to disk without a prior COMMIT (if at all possible), during transaction recovery, undo blocks can be used to roll them back. BUT how can undo blocks can be used to rollback uncommitted changes (what arethose changes anyaway...) introduced by replaying redo logs during cache recovery ?

Hmmm I don't get it since online redo log, which should only contain committed information, create uncommited information during instance recovery.


texts:
I'm troubleshooting an Oracle instance that won't startup  due to ORA-15041. The trc file shows this error:

Failed to create file '+FRA_DATA' (file not accessible?)
ORA-19816: WARNING: Files may exist in db_recovery_file_dest that are not known to database.
ORA-17502: ksfdcre:4 Failed to create file +FRA_DATA
ORA-15041: diskgroup "FRA_DATA" space exhausted
*************************************************************
WARNING: A file of type ARCHIVED LOG may exist in
db_recovery_file_dest that is not known to the database.
Use the RMAN command CATALOG RECOVERY AREA to re-catalog
any such files. If files cannot be cataloged, then manually
delete them using OS command. This is most likely the
result of a crash during file creation.
*************************************************************
ORA-19504: failed to create file "+FRA_DATA"
ORA-17502: ksfdcre:4 Failed to create file +FRA_DATA
ORA-15041: diskgroup "FRA_DATA" space exhausted
*** 2016-02-22 11:10:10.612 4132 krsh.c
ARCH: Error 19504 Creating archive log file to '+FRA_DATA'
*** 2016-02-22 11:10:10.612 2747 krsi.c
krsi_dst_fail: dest:1 err:19504 force:0 blast:1
DDE: Problem Key 'ORA 312' was flood controlled (0x1) (no incident)
ORA-00312: online log 1 thread 1: '+RAC_DATA/oradb/redo01.log'
ORA-16038: log 1 sequence# 11427 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 1 thread 1: '+RAC_DATA/oradb/redo01.log'

*** 2016-02-22 11:10:10.685
USER (ospid: 25801): terminating the instance due to error 16038
ksuitm: waiting up to [5] seconds before killing DIAG(25722)

Is there a way to manage this with no connection to the database? So far all the solutions I have encountered from research requires a database connection, however I cannot even get the instance up.

Very new to database troubleshooting (actually a web developer), just tasked to troubleshoot this as our DBA left. Any help would be very much appreciated.

TIA!


texts:
How do you install a TFS plugin for SQL Server 2016 Management Studio?

The source control option is no longer in the option list so the existing workarounds no longer appear to work.


texts:
We have a dedicated SQL Server 2008 R2 machine that is experiencing some strange memory issues.. The machine itself has plenty of resources including two quad-core processors, 16gb of RAM and 64bit Windows Server 2008 R2 Enterprise (it is a Dell PowerEdge 2950).

The strange problem is that the system is reporting 82% of memory in use but sqlservr.exe is only reporting 155mb in use. The reason that I suspect SQL Server is the issue is because if I restart the sqlservr.exe process the memory consumption returns to normal for a period of time.

Does anyone have any ideas on how I can start to track this issue down?

Thanks, Jason


texts: How can we handle out-of-range errors when aggregating data?

texts:
For example we have some data in int8 fields and want to build aggregations: e.g. the sum of squares. The result should also be int8 (not a float).

So obviously calculating the sum of squares for int8-values can overflow the int8 range and we could get bigint out of range errors.

How can we handle that case?

e.g. is there a way to "catch" this error and return a default (e.g. max-int8)?
or other ideas, how to avoid this

Example:

create table test
( val int8);

insert into test VALUES (9223372036854775807), (9223372036854775807);

This query will fail with bigint out of range:

select sum(val*val) from test;

To make the sum work, we could cast to numeric:

select sum(val::numeric*val) from test;

but our application needs a result of type int8, so we could use least and greatest

select greatest(+9223372036854775807, least(-9223372036854775808, sum(val::numeric*val))) from test;

Is there some easy built-in way to "cast"/"convert"/"clamp" the numeric back to the int8 range?

link to sqlfiddle example


texts: Wrong column data types when using dtswizard

texts:
I need to copy a database from a MS SQL Server 2017 to a MS SQL Server 2012.
I am not sure what's the best way to go, but I'm currently trying to use dtswizard to do the job.
Everything seems to be pretty ok, but when checking the column mappings, I see that all money fields are to be converted to smallmoney fields and all nvarchar(xxx) fields are to be converted date fields (and several more mismatches).
I could change the mappings manually, but there are hundreds of tables with each dozends of columns and that will both be a PITA and quite error-prone...
Is this a know issue?
Am I doing something wrong here?
Is there a workaround?
I am using the Microsoft OLE DB Provider for SQL Server as data source for both the source and the target data source.


texts: Optimizing Slow Changing Dimension (SCD2) joins

texts:
Right now I'm working on developing a DB schema for an application which requires versioning several different sets of numerical data which must be joined together for use in views. The application requires that we're able to 'look back' and find the state of what these multiple variables were like at an arbitrary point in time.

I've built up a few view definitions using subqueries to fetch the various inputs along arbitrary join criteria as well as checking the version against a time_created field. This isn't a strict SCD2 implementation, but it's close. (link to Slow Changing Dimensions page in Wikipedia)

You can see a similar ( albeit greatly simplified ) implementation to our schema here: DB Fiddle link. The real schema has 6 subqueries/joins.

This works very well for my purposes, except that when we start scaling it up into the thousands of 'purchase' records we incur a significant query cost. A query against 2000+ rows is taking ~1s, which is unsuitable for our application.

The first solution that springs to mind is creating a table which joins a 'purchase' to it's various versioned input tables, to be created by the application when a purchase is created. This should be much more efficient, but requires more schema complexity.

Can anyone offer me advice on how to best plan my schema/queries so as to be most optimal for this kind of setup?

Admittedly this is a cross-post from SO, but it received zero interest there over 24 hours, so I figured it may be better suited for the DBA stackExchange. Apologies in advance if this causes any offence.
The original can be found here.


texts: Best method to synchronize two tables across SQL servers

texts:
I have one large table in a MySQL table that when updated, I want to synchronize with a duplicate of the table on a Microsoft SQL server. Here is the process that happens when a new row is inserted into the MySQL table, and how it is sent to the MS SQL server table:

When a new row is inserted into the MySQL table, there's a trigger that inserts the same row into a temp table on the MySQL server
A job on the MS SQL server periodically checks the temp table, and from there takes any rows and inserts them into the MS SQL using a MERGE statement.
Temp table is cleared out of any processed rows.

I have recently found out that very randomly some rows have just not been transferred over to the MS SQL server. What I am wondering is, is there a better way to do this, with reliability being the first priority, and speed being less of a priority, what other methods are out there? Thanks for any help, feel free to ask for clarification if I did not explain it well.


texts: Why does MySQL allow duplicate index names, but not duplicate foreign key names?

texts:
I can of course accommodate the requirement that foreign key names must be unique across all tables, but I've always wondered why the same requirement doesn't apply to index names?

Is there a good reason?


texts: How similar is using a secondary index in InnoDB to using a second table joining 'into' the PK?

texts:
Since InnoDB secondary indexes are separate B-trees with the key as the sort order and the PK as the 'value' stored, I assumed that reading into the main table by the secondary index was a lot like certain joining scenarios, but this seems not to be true for performance at least.

Here's an example

CREATE TABLE abc (a int unsigned, b int unsigned, c int unsigned, PRIMARY KEY (a), UNIQUE KEY (b));

a is the (ordered) set of numbers from 0 to 1000000. b is the "randomly" ordered set of the same numbers.  So they're not parallel.

The following is intended to be an imitation of the secondary index b on abc.

CREATE TABLE ba (b int unsigned, a int unsigned, PRIMARY KEY (b));

INSERT INTO ba SELECT b, a FROM abc ORDER BY b ASC;

In the following examples I will SELECT c so as to prevent Using Index.

EXPLAIN SELECT AVG(c) FROM  abc WHERE b > 702991 AND b < 900000;

+----+-------------+-------+-------+---------------+------+---------+------+--------+----------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra                            |
+----+-------------+-------+-------+---------------+------+---------+------+--------+----------------------------------+
|  1 | SIMPLE      | abc   | range | b             | b    | 4       | NULL | 385202 | Using index condition; Using MRR |
+----+-------------+-------+-------+---------------+------+---------+------+--------+----------------------------------+

EXPLAIN SELECT AVG(c) FROM ba JOIN abc USING (a) WHERE ba.b > 702991 AND ba.b < 900000;

+----+-------------+-------+--------+---------------+---------+---------+-----------+--------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref       | rows   | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-----------+--------+-------------+
|  1 | SIMPLE      | ba    | range  | PRIMARY       | PRIMARY | 4       | NULL      | 403560 | Using where |
|  1 | SIMPLE      | abc   | eq_ref | PRIMARY       | PRIMARY | 4       | perf.ba.a |      1 | NULL        |
+----+-------------+-------+--------+---------------+---------+---------+-----------+--------+-------------+

I wasn't expecting the explains to be identical  But, for example, why can't we use MRR in the latter query?  Why can't we buffer the as we read in off ba, sort those, and then do a range query into abc? It's at least relationally equivalent to the former query.

As expected, both queries yield the exact same result.  However, the secondary index one takes about 1 second and then join one takes about 10 seconds, with default mysql settings.

Can someone explain why that is?

Thanks


texts: UPDATE with UUID() sets all rows with the same value

texts:
Having a table t1 with the field f1 BINARY(16) the following command updates all rows with the same uuid value.
UPDATE t1 SET f1 = UNHEX(REPLACE(UUID(), '-', ''));

OK, somehow I must have totally misunderstood the behaviour as I expected that each row will be updated with another uuid value.
My questions:
Is there a way to force MariaDB to update each row with a different value? And, why is this? I didn't really find anything in the documentation pointing this out. Is this some standard behaviour?
MariaDB 10.5.9


texts: How do I create a user-defined aggregate function? (Part 2)

texts:
I'm refering to this question:

How do I create a user-defined aggregate function?

My table contains Zero-Values so I have tried the example of ypercubeᵀᴹ but I got the message that

(NOT EXISTS (SELECT 1 FROM testmult WHERE wert = 0))

is a boolean and it can't be muliplied with

EXP(SUM(LOG(wert)))

  (Error: "boolean * double precision")

So how can I solve it?


texts:
We have a production server with 500GB RAM. max server memory is set to 450GB. Whenever we checked the memory usage it's showing 450GB, but maybe SQL Server reached 450GB for some time? The memory is not released and it will be used whenever it is needed in future. So we have a free space for utilizing by SQL Server when it needs.

I need to configure an alert when SQL Server really uses 450GB of memory. If it's having FREE space among 450GB then how much it is and if the free space is used beyond 90% of available memory then I need an alert.

I can say in DB level even the SQL Server occupies DB files more than it wants and didn't release it. But I can tell to anyone like we would have unused space and it could be used by SQL Server at any time without increasing the file size.

Like that I am looking for script in Memory Level as well. I hope you are understanding my problem. My SQL Server is fully dedicated and no other applications or no other services (ssis,ssrs etc) are running. Find the below example DB space related script.

select
NAME = left(a.NAME,15),
[FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)),
[FILE_MAXSIZE_MB] = convert(decimal(12,2),round(a.maxsize/128.000,2)),[FILE_GROWTH] = convert(decimal(12,2),round(a.growth/128.000,2))
from
dbo.sysfiles a


texts: What index to add in case of join by two optional fields

texts:
I have query similar to the one below, it joins two tables by field which can have NULL value. The result matchs the data if both tables have the same data or consider the condition as optional.

SELECT B.X, A.Y FROM tableA A
INNER JOIN tableB B ON
    ISNULL(B.level5, A.f05) = A.f05
    AND ISNULL(B.level6, A.f06) = A.f06

The best index for table B I could think of is such which

CREATE NONCLUSTERED INDEX [Index_On_Table_B] ON B
( X ASC ) INCLUDE (level5, level6)

It helps a bit and from execution plan I can see the index is used but 90 % of the cost is spent in Nested Loops (Inner join).

Is there any way how to get this query working faster?


texts: Is it possible to query the avg time in ms per read/write

texts:
I was wondering if it was possible to obtain the avg block read and avg block write in ms with a query in SQL Server.

If SQL Server knows how many IO requests are made for a given wait type and how long it took to obtain in total, then we should be able to identify how long it takes per request.
I'm assuming that a request would be a block read.

Can we obtain that information with TSQL?


texts: MS Access 2007/2010 Compact & Repair not working

texts:
I have a database that I use in VB.  As records update theres a lot of useless info in there because access doesn't reuse the same spots.  So that information has to be removed.  Before I would use compact & repair and access 2000 and it would correct this.  It would correctly adjust the file size and remove all the wasted space or whatever happens in Access.

Compact and repair feature in 2007 and 2010 does not fix the file size.. why?


texts: MySQL Store Procedure - Setting AUTO_INCREMENT value

texts:
I am in trouble coding a store procedure as shown below

CREATE DEFINER=`dbcomexuser`@`%` PROCEDURE `clearance_imp`(IN CAPIfilename VARCHAR(12))
BEGIN

DECLARE `_rollback` BOOL DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
DECLARE _refID INT DEFAULT 0;

START TRANSACTION;

SELECT MAX(imp_id) + 1 INTO _refID FROM temp_imp;

INSERT INTO filenamedt (filename, filedate) VALUES (CAPIfilename, now());

INSERT INTO imp__description (imp_id, imp_description) (SELECT imp_id, TRIM(descricaoProduto) FROM temp_imp);

TRUNCATE TABLE temp_imp;

ALTER TABLE temp_imp AUTO_INCREMENT = _refID;

IF `_rollback` THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;
END

The main issue is that MySQL Workbench outputs that there is an error at line:

ALTER TABLE temp_imp AUTO_INCREMENT = _refID;

But I can't figure out what is going on.

The fact is that: I am loading my database from hundreds of files and data is being stored in many different tables.

After I load each file, I trucate temporary table, because it is faster than use delete, so I load another file. When I trucate table, MySQL start from 0 the auto increment number, but I need last int number to use it again as I am using it as a primary key.

Does someone can help me?


texts: Store large hierarchy in NoSQL each day and compare

texts:
I'm in the process of creating a utility that stores a fairly large hierarchy into a NoSQL document. The data of this is similar to an Active Directory / LDAP structure which contains sub groupings, etc for each record.

These are fairly large, a JSON representation is about 40MB per copy.

Each day a new copy of this hierarchy would be added as a new document in the collection.

Through a web interface, a user would be able to select any two copies (documents) and they would be compared. The differences between the two would be displayed, sort of like a diff command would display differences between two documents.

My questions are

Is NoSQL a good fit for storing a LDAP like structure for just viewing purposes?
If so, what sort of techniques are useful for comparing two documents and getting the differences?

I'm currently trying this in MongoDB and I haven't figured out how to do a direct comparison from a MongoDB command to get the differences. So, my attempt so far is to simply export each record as a JSON text file and run a custom diff command to compare and format as needed.

I am curious if someone has a better method for this.

An example document in NoSQL

{
    date: "2013-06-10",
    users: [
        {
             _id: 1,
             dn: "CN=B A Baracus,OU=Testing,OU=People,DC=my,DC=domain,DC=com",
             name: { first: 'B', middle: 'A', last: 'Baracus' },
             employeeId: ‘001234’,
             domain: 'mydomain',
             username: ‘babaracus’,
             created: new Date('Dec 03, 1924'),
             modified: new Date('Mar 17, 2007'),
             memberOf: [
                   "CN=TEST-DeleteMe,OU=Testing,OU=People,DC=my,DC=domain,DC=com",
                   "CN=CTX - Epic Prod,OU=Access,OU=Groups,DC=my,DC=domain,DC=com",
                   "CN=Public Folder Admins,CN=Users,DC=my,DC=domain,DC=com",
                   "CN=Exchange Services,CN=Users,DC=my,DC=domain,DC=com",
                   ...etc...
             ],
            ...etc...
        }
        ...etc...
    ]
}


texts: Reversing many-to-many relationships when transforming data into a galaxy schema

texts:
I'm building a galaxy schema for our analytics and sales data from our normalized data tables. I'm unsure how to treat many-to-many relationships in the facts tables.

For example, for each sale, we store split testing data for the visit funnel that led up to the sale. In our current structure, that information is conveyed in a many-to-many relationship from the sales table to the tests table. It looks something like this -

split_tests
* sale_id (int)
* test_id (int)
* variation_id (int)

A very simple relationship. However, in the facts table, I now need to reverse that relationship, since I need to put a foreign key from the tests dimension in the facts table.

From research I've, it would appear the way to address it would be create a bridge table to express that relationship. Something like the following -

wh_testsuites
* id (int)

wh_tests_to_suites
* suite_id (int)
* test_id (int)
* variation_id (int)

And then use the id column from the wh_testsuites table in the facts table.

It seems a bit of a roundabout solution to create a table that just stores auto-incrementing identifiers for this purpose, and adds another join for generating reports, but I couldn't find a better solution.

I would appreciate any advice on how to better handle those kind of relationships in any kind of data warehouse schema.


texts:
mysqldump: Got error: 1044: Access denied for user 'username'@'localhost' to database 'databasename' when using LOCK TABLES

I am able to loging properly and able to take full backup from SQLyog Tool but when i am trying to take backup from MySQL Enterprise Workbencg then getting error message.

  mysqldump: Got error: 1044: Access denied for user 'myuserid'@'%' to
  database 'mydatabasename' when doing LOCK TABLES

Operation failed with exitcode 2

And MySQL Connection status is ok.

  mysql> status;

  mysql.exe Ver 14.14 Distrib 5.6.19, for Win32 (x86)

  Connection id: 23921 Current database: databasename Current user: user
  id is ok here SSL: Not in use Using delimiter: ; Server version:
  5.6.17-log MySQL Community Server (GPL) Protocol version: 10 Connection: connection is ok here Server characterset: utf8 Db
  characterset: utf8 Client characterset: cp850 Conn. characterset:
  cp850 TCP port: 3306 Uptime: 21 days 21 hours 11 min 37 sec

  Threads: 24 Questions: 20500671 Slow queries: 3212 Opens: 121998 Flush
  tables: 1 Open tables: 2000 Queries per second avg: 10.842


texts: How do i store hash indices on my database

texts:
I'm storing (references to) images in a database. I want to have reverse image search, so I wish to store a multidimensional hash of the image - for example width to nearest 10px, height to nearest 10px, and perhaps a downsampled version of the image.
Originally I was going to store these hashes on disk, but then I realised I may be able to store in database in a much more organised (and perhaps performant) manner.


texts: AlwaysOn Statistics getting updated on Readonly node immediately after updating primary node with Fullscan

texts:
Version:
Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)
    Oct 20 2015 15:36:27
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3  (Build 9600: )

Once I update my statistics with full scan, with 10~20 seconds some of the statistics on the same table are getting update on secondary with a sample. I ran extended event trace to capture auto update stats on secondary node but nothing was logged.
On primary node once I run this:
Update statistics Tablename with fullscan
I see this about my stats status

When I query for the stats on secondary I get this. Look at the rows_sampled and last_updated column.

I also ran an extended event trace to capture the auto update stats but nothing was logged.  Here is the defination of my XE trace.

CREATE EVENT SESSION [AutoUpdateStats] ON SERVER ADD EVENT sqlserver.auto_stats(
ACTION(sqlserver.client_app_name,sqlserver.server_principal_name)
WHERE ([package0].[equal_uint64]([database_id],(25))))WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)GO


texts:
Assuming one has about 1k requests per second that require an insert.

Now, there are a lot of answers to this on the internet... but they're technically wrong in this specific context. Yes, pretty much any RDBMS can handle 1k inserts per second on standard hardware but IF AND ONLY IF you drop ACID guarantees. It's surprising how many terrible answers there are on the internet. Such as "you can always scale up CPU and RAM" which is supposed to give you more inserts per second but that's not how it works. The limiting factor is disk speed or more precise: how many transactions you can actually flush/sync to disk. And this is the tricky bit.

On decent "commodity hardware" (unless you invest into high performance SSDs) this is about what you can expect:

SQLite: 30 inserts/s
MySQL: 80 inserts/s

This is the rate you can insert while maintaining ACID guarantees. This essentially means that if you have a forum with 100 posts per second... you can't handle that with such a setup.

Read requests aren't the problem. You can have many thousands of read requests per second no problem but write requests are usually <100 per second.

Thus, this question is specifically aimed at how one can handle 1k inserts per second while still maintaining ACID guarantees - assuming that a single node can handle about 80 transactions per second.

One way I could see this working is if you buffer inserts somewhere in your application logic and submit them as larger transactions to the database (while keeping clients waiting until the transaction is over) which should work fine if you need single inserts only although it complicates the application logic quite a bit.


texts: Query execution involving two servers in SQL server , is really slow

texts:
I am running the following query , and it takes over 1 hour to finish execution. As there are so many joins and then one union is involved, is it normal for this query to be executed in 1 hour, or is it because of bringing result from two different servers ? In fact , there is one procedure , involving this select statement , creation of the procedure also took very long.

select reg.Associateid,reg.Companyid,Sum(Componentvalue) Componentvalue
from CTSINTCOSMYPT.IndiaPayrollDB_LiveFinance0910.pr.salaryregister reg
join [MP].[SuperannuationFundStatementTemplateUploadTemp] SAPrev
on reg.Associateid=SAPrev.Associateid and reg.Companyid=SAPrev.Companyid
join CTSINTCOSMYPT.IndiaPayrollDB_LiveFinance0910.pr.associatemonthlysalreimb  salreimb
on reg.Registerid=salreimb.Registerid
where Componentid=12 and ComponentValue>0
Group by reg.Associateid,reg.Companyid
Union all
select reg.Associateid,reg.Companyid,Sum(Componentvalue) Componentvalue
from CTSINTCOSMYPT.IndiaPayrollDB_LiveFinance0910.pr.salaryregister reg
join [MP].[SuperannuationFundStatementTemplateUploadTemp] SAPrev
on reg.Associateid=SAPrev.Associateid and reg.Companyid=SAPrev.Companyid
join CTSINTCOSMYPT.IndiaPayrollDB_LiveFinance0910.pr.associatemonthlyarrreimb  salreimb
on reg.Registerid=salreimb.Registerid
where Componentid=12 and ComponentValue>0
Group by reg.Associateid,reg.Companyid


texts: Difference between Flashback Query and Serializable Transaction Mode?

texts: Is there any conceptual difference between a SERIALIZABLE Transaction and a Flashback Query?


texts: Issue with a where clause by using Oracle

texts:
I'm currently facing a problem while I'm adding a trigger to the database that I use at home.

I'm using : Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

The problem that occurs is that, when I add my trigger it compiles, but impossible to access to the content of a VARCHAR2 variable, even if it contains something.

    typeEngine engine.type%TYPE = '74E';--engine.type is a VARCHAR2(4)

SELECT engine.capacity INTO capacityMax
FROM engine
WHERE engine.type = typeEngine;

That returns me a NO DATA FOUND Exception, but the data is in the database.

Thanking you all in advance if someone has the solution to solve that issue.


texts: MySQL DNS issue: skip-name-resolve connection fast but cannot log into email

texts:
MySql is used here with remote network connections, and after a reboot of the server these connections started taking 20-30 seconds to connect causing timeouts. Connections used to be nearly instant.

Adding skip-name-resolve fixes the delay as remote connections address the database by its IP address, however, this MySql server also hosts an email login database for an internal email server. The skip-name-resolve fix breaks all email logins from clients and servers.

The email server is a Postfix/Dovecot stack and using IMAP RoundCube Server and Thunderbird clients. I cannot figure out where in the email stack that addresses MySql configuration needs to be changed.

It seems the root of the problems would be a DNS issue, maybe an expired domain name in the configs somewhere. I spent hours looking and need some advice on tackling this. I'd rather solve the root DNS issue, if that's the problem - like track down an expired domain name or fault in the DNS addressing.


texts: Sql Server Mirroring Failover and Failback

texts:
I have the following mirroring setup for DR.

Principal (ServerA)
Mirror    (ServerB).

When we do a DR test we do manual failover from Principal (ServerA) to Mirror(ServerB), so now:

ServerB is Prinicpal
ServerA is Mirror

Applications do their testing against the new principal which is ServerB but don't want to take those changes or sync to ServerA (Mirror).

Is there any way to do this without breaking the mirror?

Finally it will go back to the original setup after the test, where Server A is Principal and Server B will be its mirror.


texts: How to add an “INCLUDE” part in an Index Created as a part of CONSTRAINT

texts:
I am going through the execution plan of a tsql query.

There is a key LookUp (Clustered) in the plan. To avoid this Key LookUp, I tried to Include a column (Status_Ind) to The Index (The Index used in execution plan). But I realize that optimizer is using two Indexes, One the Primary Key index and the 2nd one, an index automatically created as a part of a Constraint (A UNIQUE NONCLUSTERED Index). I find no way to add an include part in this Index.

How do we add an INCLUDE in an index which is automatically created (through CONSTRAINTS)? I am really a beginner in Query optimization.


texts: Which metrics should a product owner look at?

texts:
I am the product owner for an on premise, off-the-shelf, application, which relies on a SQL Server DB.

My company outsources DB administration to a bunch of very capable folks, and tickets are issued when something is wrong. (So that I can, for example, escalate with the editor's technical teams).

However, improvement and evolution projects are my responsability, but I have a hard time figuring out if one is in order or not.

As far as performance goes, I do not follow any indicators. Without micromanaging, is there any high-level KPIs that should be monitored by a product owner?


texts: Having values or some state in a database

texts:
Suppose I have a table with several price values in it

ID Int, Primary Key, not null
AveragePrice Decimal, not null
MinPrice Decimal, not null
MaxPrice Decimal, not null

Each price value can be known and present (represented as a positive value) or it can be

infinite (without upper bound)
unknown
irrelevant
customer specific

There may be more even states.

How to model this in a database?

Currently these states are modelled as negative constants, e. g. a price of -10 means "unknown", -20 means "irrelevant". Since prices are normally positive this kind of works but I hope there exists a more elegant solution.


texts: On docker volume restore: could not access file "$libdir/timescaledb-1.6.0": No such file or directory

texts:
I am trying to restore TimescaleDB data from one ubuntu machine to another ubuntu machine. On the both machines we have deployed TimescaleDB through docker and using the same tag.
Docker image used: timescale/timescaledb:latest-pg11
I am creating a tar file for backup for the docker volume on the old machine, using below command:
docker run --rm -v $VOLUME_NAME:/volume -v /tmp:/backup busybox tar cvf /backup/$VOLUME_NAME.tar -C /volume ./

Also I am restoring using below command on the new machine:
docker run --rm -v $VOLUME_NAME:/volume -v $(pwd):/backup busybox sh -c "rm -rf /volume/* ; tar -C /volume/ -xvf /backup/$VOLUME_BACKUP_FILE"

Database restore was successful but when I see the docker logs I am getting below error:
2021-03-06 06:28:55.064 UTC [239] ERROR:  could not access file "$libdir/timescaledb-1.6.0": No such file or directory
2021-03-06 06:28:55.064 UTC [239] STATEMENT:  CREATE TABLE "__migration_lock" ("id" int NOT NULL, CONSTRAINT "PK_1312144c987e032cce38cfa00a2" PRIMARY KEY ("id"))

Do you know why this error is coming and how to resolve it?
Thanks


texts: Is it possible to trace the password the application is attempting to use to connect?

texts:
I have an application attempting to connect to SQL Server. The application connects using a SQL Server account. The password for this account was accidentally changed. Now the application is attempting to connect to SQL Server using the wrong password.

Login failed for user ''. Reason: Password did not match that for the login provided. [CLIENT: ]

Ideally, we can just change the account/password the application is using to connect. If this were not an option, is it possible to trace the password the application is attempting to use to connect?


texts: Data exchange between 2 remote postgresql databases

texts:
As per subject I'm looking into a reliable way to synchronize 2 tables that have a same structure between two remote postgresql databases.

I was thinking about dblink but an not really sure as in the places where these db's are connectivity is a big issue and very unreliable and this would force me to expose my databases to the internet which is an idea I'm not comfortable with.

Another option would be to write myself a daemon implementing integrity checks but before proceeding I'd like to know if anyone has undergone a similar challenge and what solutions have been undertaken.


texts: Insert Into table ommitting columns that have default values

texts:
I have a PostgreSQL database with lots of tables of the same structure, 36 columns in total:

CREATE TABLE some_schema.some_table    (
    id integer NOT NULL DEFAULT nextval('some_schema.id_seq'::regclass),
    col2,
    col3,
    col4,
    [...],
    col35,
    mi_prinx integer NOT NULL DEFAULT nextval('some_schema.mi_prinx_seq'::regclass),
    CONSTRAINT some_table_pkey PRIMARY KEY (mi_prinx)
)

In many instances I will have to insert records from another table with the same structure:

INSERT INTO some_schema.some_table (col2,col3...col35)
    SELECT col2,col3...col35
    FROM some_schema.another_table_with_same_structure;

Is there a way of doing this without having to list all columns that don't have a default value? I think I can use DEFAULT VALUES somehow but I can't get the syntax right based on the documentation.


texts: Add ENum Attribute to existing table using Alter

texts:
I am using oracle XE and trying to add a attribute that restricts to only three  available value 'Small','Medium','large' to an existing table using Alter Table and Enum.

Tried doing,

ALTER TABLE TRUCK ADD TTYPE ENUM('SMALL','MEDIUM','LARGE');

but gets invalid option

ALTER TABLE TRUCK ADD TTYPE ENUM('SMALL','MEDIUM','LARGE');
                                 *

where the error highlights after ENUM.

I think I am having syntax error. Please help to resolve.


texts:
Is it possible to filter results of sp_BlitzIndex to show only unused and duplicate indexes? If so, how?

I haven't tried anything yet. Was just giving a thought of inserting sp output in a table and then filter it there. But I am sure there must be a better way.

I know this should go to Brent and team. But posting it here to get some more interesting answers.


texts: Disallow superusers and single-user mode in POSTGRESQL

texts:
I have a doubt about superusers and single-user mode.
I want to restrict the access to a certain database, in a way that only those users which I gave privileges can see the structure of my database. To achieve that, I had to erase the superuser, since it bypasses all permission checks. But then I found that if I have access to the computer, I can enter in "single-user mode" and grant superuser privileges or even creating new users.
My question
Can I disallow permanently the use of superusers and single-user mode?


texts: Why is a GiST index used for filtering on non-leading column?

texts:
I always learned and understood that an index can only be used when we have predicates for the leading (or all) columns. Now, to my surprise, I noticed that a GiST index is used in the following query. Why is that? Is this a special feature of GiST indexes?
CREATE TABLE t1 (
    i INT,
    j INT,
    k INT
);

INSERT INTO t1
SELECT i, j, k
FROM   GENERATE_SERIES(1, 100) AS i,
       GENERATE_SERIES(1, 100) AS j,
       GENERATE_SERIES(1, 100) AS k;

CREATE INDEX ON t1 USING GiST(i, j, k);

EXPLAIN SELECT * FROM t1 WHERE k = 54;

QUERY PLAN
Bitmap Heap Scan on t1  (cost=199.03..5780.51 rows=5000 width=12)
  Recheck Cond: (k = 54)
  ->  Bitmap Index Scan on t1_i_j_k_idx  (cost=0.00..197.78 rows=5000 width=0)
        Index Cond: (k = 54)

db<>fiddle here


texts:
I want to know how to find the server and instance name on my computer to connect to the server.

I am using the SQLCMD utility, and I am currently able to connect to the server with the line

>SQLCMD -S "(localdb)\mssqllocaldb"

I want to know if the server and instance name, "(localdb)\mssqllocaldb", is information that is listed anywhere I can access on my computer.

I'm trying to configure another computer to connect its own local server in the same way I am able to connect to (localdb)\mssqllocaldb, and I want to know what "(localdb)\mssqllocaldb" should be on their computer so that they can connect to it with SQLCMD.


texts: Why earthdistance  operator returns different value than earth_distance function?

texts:
Seeing a different value with <@> and earth_distance

with points as (
  select
    '(1,1)'::point as p1
    ,'(2,2)'::point as p2
)
select
  ((p1 <@> p2) * 1609.34::double precision) as d1
  ,(earth_distance(ll_to_earth(p1[1], p1[0]), ll_to_earth(p2[1], p2[0]))) as d2
from points

Result is:

157224.717583288 157402.326073052

Almost 200m difference - why is that?


texts:
Is there any way to set the collation for a string comparison based on an independent variable or column value?  Something akin to

SELECT COL1, col2
FROM [TABLE]
WHERE COL3 LIKE '%string%'
    COLLATE CASE CASE_SENSITIVE
                WHEN 1 THEN SQL_Latin1_General_CP1_CS_AS
                ELSE SQL_Latin1_General_CP1_CI_AS
            END

or

DECLARE @collation AS VARCHAR(50)
SET @collation = 'SQL_Latin1_General_CP1_CI_AS'

SELECT COL1, col2
FROM [TABLE]
WHERE COL3 LIKE '%string%'
    COLLATE @collation


texts:
I am trying to find free time blocks on a day between the business hours (08:00 to 18:00) of a service station. The schema of the database is as below,

CREATE TABLE IF NOT EXISTS `Bookings` (
  `id` int(11) NOT NULL,
  `date` date NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `bay_no` int(11) NOT NULL,
  `status` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO `Bookings` (`id`, `date`, `start_time`, `end_time`, `bay_no`, `status`) VALUES
(1, '2016-05-24', '09:00:00', '11:30:00', 1, 1),
(2, '2016-05-24', '12:30:00', '15:00:00', 1, 1),
(3, '2016-05-24', '14:00:00', '16:00:00', 1, 2),
(4, '2016-05-24', '08:00:00', '09:30:00', 2, 1);

ALTER TABLE `Bookings`
  ADD PRIMARY KEY (`id`);

The results needs to be generated for a day. status of 1 means booked and 2 are cancelled bookings which can be considered as free slots. The station has 2 bays which can handle bookings.

Expected results for a query for the date 2016-05-24 will be producing the below results

+-----------+------------+----------+--------------+
|  bay_no   | start_time | end_time | block_length |
+-----------+------------+----------+--------------+
|         1 |   08:00:00 | 09:00:00 |     01:00:00 |
|         1 |   11:30:00 | 12:30:00 |     01:00:00 |
|         1 |   15:00:00 | 18:00:00 |     03:00:00 |
|         2 |   09:30:00 | 18:00:00 |     08:30:00 |

I was trying the below query, but with no success.

SELECT available_from, available_to
FROM (
    SELECT @lasttime_to AS available_from, start_time AS available_to, @lasttime_to := end_time
    FROM (SELECT start_time, end_time
          FROM Bookings
          WHERE end_time >= '08:00:00'
            AND start_time < '18:00:00'
            AND status = 1
      ORDER BY start_time) e
    JOIN (SELECT @lasttime_to := NULL) init) x
WHERE available_to > available_from


texts:
I have a database DB in simple recovery mode with filegroups PRIMARY, A, B, and C. Each filegroup has a single file, PRIMARY.MDF, A.NDF, B.NDF, and C.NDF, respectively.

First, I run:

ALTER DATABASE [DB] MODIFY FILEGROUP [A] READ_ONLY
ALTER DATABASE [DB] MODIFY FILEGROUP [B] READ_ONLY

Next, I run:

BACKUP DATABASE [DB] READ_WRITE_FILEGROUPS TO DISK = N'C:\DB.BAK'

I copy the backup file to a separate server and perform a partial restore with:

RESTORE DATABASE [DB]
FILEGROUP = 'PRIMARY', FILEGROUP = 'C'
FROM DISK = 'C:\DB.BAK'
WITH PARTIAL, RECOVERY

The database is now online on the second server, and I can query data from filegroups PRIMARY and C. The database is aware of filegroups A and B, but they are marked as offline. Is there a way to restore the remaining two read only filegroups directly from the .NDF files?