texts: MySQL - SELECT Columns from results of another Query

texts:
I'm trying to use a dynamic range of columns fetched from another query, specifically I want the results of this query:

SELECT COLUMN_NAME as DYNAMIC_COLUMN FROM information_schema.columns
WHERE table_name='TheTableName' AND column_name like 'TheColumnName%'

As columns for the SELECT statement of this query:

SELECT TheColumnResults
FROM AnotherTable

My (wrong) approach is something like this:

SELECT(
SELECT COLUMN_NAME as DYNAMIC_COLUMN FROM information_schema.columns
WHERE table_name='TheTableName' AND column_name like 'TheColumnName%'
) AS TheColumnResults
SELECT TheColumnResults
FROM AnotherTable

I just need a guidance on how to approach this or even if it's possible to set a dynamic range of columns at the SELECT statement.

Other (failed) approach I've used:

SELECT *
FROM DATABASE.AnotherTable
INNER JOIN information_schema.columns
WHERE Exists (SELECT COLUMN_NAME AS DYNAMIC_COLUMN FROM
information_schema.columns
WHERE table_name='TheTableName' AND column_name like 'TheColumnName%' AND TABLE_SCHEMA = 'DATABASE')


texts: Query to extracting the 14th date from a date after excluding the sunday and saturday in Postgresql

texts:
I need a query to extract the 14th day or any other day before a day after excluding Saturday and Sunday in Postgresql.

For Example, I need 14th day before 2020-01-30 after excluding Saturday and Sunday.

Date Dimension  is " YYYY-MM-DD"

I am using 'ISODOW' < 6 condition to exclude Sunday and Saturday. But from the count,  i am not sure how to fetch the date

 extract('ISODOW' FROM the_day) < 6


texts: MySQL Indexing and performance when size of index is greater than size of data in table

texts:
I wrote this query for seeing if the size of the indexes in certain tables were larger than the actual amount of data in the table, using MySQL 5.6 and InnoDB as the engine.

SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2)
'Size in MB',
IF(index_length > data_length, 1, 0) as poorIndexing
FROM information_schema.TABLES

The results are that some of the tables have much larger index_lengths than their actual data table.  I was under the impression that this meant that MySQL would skip over the indexes and simply do a full table scan because there is less data to parse in the entire table than the index.

However, is this true if an individual index is smaller than the aggregate?  I.e. if I'm doing something like "SELECT * FROM user WHERE id = 244", and the id is indexed, but so is the address, phone, username, email, etc. does InnoDB just use the id or does MySQL see the size of the entire operation and simply skip over it.  Or is it only a problem if the query is :

SELECT username FROM users WHERE username = 'johnrambothe14th';

And the total amount of indexes for username is greater than the total amount of data?


texts: MySql slave thinks that the query didn't run on the master, no helpful error

texts:
We have a master-slave replication that was running perfectly until recently.
Today i've found the error:

  Query caused different errors on master and slave. Error on master: message (format)='Size of tree cache used in bulk insert optimisation. Note that this is a limit per thread!' error code=126; Error on slave:actual message='no error', error code=0. Default database:'dbname'. Query:'UPDATE table SET something='0', something2='' WHERE something3=4'

We are using GTID based replication.
I tried to skip 1 GTID knowing that the query wasn't important anyway, but it didn't help (the slave sql didn't want to run).
Only setting ignore_error = 126 worked, but then I turned it off immediately, and another query did the same thing.

The error that the slave shows for the master doesn't seem to be an error, only a documentation for bulk insert optimization.

What's the best way to make the replication work again?

edit: I've executed a problematic query on the master, and it worked.


texts: sqlite - very slow performance of WHERE EXISTS without index on EXISTS

texts:
I've got a strange case of (very) slow performance of an update query when I don't have an index on the table in the EXISTS clause.

For example:

UPDATE tbl1
   SET col1 = "Y"
 WHERE EXISTS (SELECT NULL FROM tbl2 WHERE tbl1.col2 = tbl2.col2)
   AND tbl1.col3 IN (1,2);

tbl1 has ~4.5M rows. When filtered above by col3, it comes down to ~400k rows.
tbl2 has ~100k rows. All rows are unique on col2, and every row in tbl2 has a match in tbl1.
tbl1 has an index on col2 and an index on col3.
I am using sqlite version 3.29.0 (from Python 3.7.2)

When I run this query without an index on tbl2.col2, the query never completes (I let it run for multiple minutes). If I add a unique index on tbl2(col2), the query finishes almost immediately.

This is confusing to me because I would think that the query should be constrained by the lookup into tbl1, since I'm using an EXISTS query for tbl2 (rather than IN()). The unique index on tbl2.col2 isn't adding any new information that a full table scan wouldn't give it, since every row in tbl2.col2 is unique.

Why is the performance so slow without that unique index?


texts:
I need bit(64) representation of current epoch (the operations I need should be much easier in bits), so

SELECT extract('epoch' from CURRENT_TIMESTAMP)::bit(64)

But this gives me

ERROR: cannot cast type double precision to bit

What am I doing wrong here?

UPD: In fact, I understood I should do it the other way, but the ERROR itself is still very strange for me.


texts: Is there a way remove SQL Server listener IP on maintenance

texts:
Is there a any way to stop listening listener on the SQL Server on production server. I want to be sure nobody is trying to connect my production server when it is maintenance mode.

I disabled all users when they have permissions to connect any database. I am sure nobodys can connect any database on that server. But i don't want to be busy my network traffic from any services or application or user while it is on maintenance. So i want to delete Listeners IP to that server.

My possible actions are listed

I can delete listener on SQL Server or Listener resource on WSFC.
I can delete Ip of listener on    the listener on SQL Server.


texts: Passing database name to input parameter from a stored procedure

texts:
I have some issue with this application. I know that there are a lot of topics about passing a Database name as input but still I couldn't figure out what is the issue my code.
Hopefuly, you guys can help me out !
I have few database and everyday, a new database is created with XXXXX-YEARMONTHDAY as name.
What i want to achieve is to pass a new database name as input parameter from my stored procedure.
When it's hard written, it's working like a charm :
GO
/****** Object:  StoredProcedure [dbo].[WinccToAlarms]    Script Date: 03-12-20 08:26:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[WinccToAlarms]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO Reporting.dbo.LiaisonAlarmes
    SELECT * FROM [WIN-9SEBDMDC9O9_HMI#B5N2_ALG_202012020924].dbo.AlgRtTextsFRA
END

But i want to dynamically name the [WIN-9SEBDMDC9O9_HMI#B5N2_ALG_202012020924] part of the request.
Here is the code that i wrote :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE WinToAlarms20
    -- Add the parameters for the stored procedure here
    @NomDBAlarms sysname
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SET XACT_ABORT ON

    IF DB_ID(@NomDBAlarms) IS NULL
    BEGIN
    RAISERROR('Nom de base de donnée invalide',16,1)
    RETURN
    END

    DECLARE @Sql NvarChar(max)

    -- Insert statements for procedure here
    SET @Sql = N'INSERT INTO Reporting.dbo.LiaisonAlarmes
    SELECT * FROM ['+(@NomDBAlarms)+'].dbo.AlgRtTextsFRA'

    Exec @Sql
    END
GO

Can you help me figure out what is wrong with this piece of code?
Thanks a lot !!!


texts: Auditing - parameter values not being written to log

texts:
I am using auditing to track usage of some procs in the database. In the log I can see a record for the Procedure being run and, immediately afterwards,the SQL statement that is being run. The statement shows the parameter name rather then the value i.e. WHERE CaseId=@P_CaseId rather than WHERE CaseId =100

Now I know there was a hotfix for SQL 2008 (not R2) that made this happen: http://support.microsoft.com/kb/967552.

Did this not get rolled into R2? Is there a different fix I can apply? Is there a workaround anyone knows?


texts: Cant connect to Server using SSMS on my desktop

texts:
SQL SERVER 2008 R2
When attempting to connect to the default instance of sqlserver on our local server from SSMS on my desktop I'm receiving the following error.

  A network-related or instance-specific error occurred while
  establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections. (provider: Named
  Pipes Provider, error: 40 - Could not open a connection to SQL Server)
  (Microsoft SQL Server, Error: 5)

I've verified that the server is set for remote connections inside
the instance - properties - connections.
I've verified that TCP\IP and Named Pipes are enabled under
Configuration Manager - sql server network configuration - protocols
for MSSQLSERVER
I've verified that the browser service is running and that the sql
server service is running.
I've verified that the 1433 port is opened in the windows firewall.

I'm not sure what else to check.  I'm using the correct servername. If I attempt to connect using IP address I get the following error.

  A network-related or instance-specific error occurred while
  establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections. (provider: Named
  Pipes Provider, error: 40 - Could not open a connection to SQL Server)
  (Microsoft SQL Server, Error: 5)

Any help would be appreciated.


texts: Starting oracle rac node2 error: ORA-29701: unable to connect to Cluster Synchronization Service

texts:
I have a oracle RAC /12c/. I got these error when I try start the base from node2.
```ORA-01078: failure in processing system parameters

ORA-01565: error in identifying file '+DATA/mydborcl/spfilemydborcl.ora'

ORA-17503: ksfdopn:2 Failed to open file +DATA/mydborcl/spfilemydborcl.ora

ORA-29701: unable to connect to Cluster Synchronization Service```

I am very new to oracle. How can I start the mount the database on node2 and solve the problem?


texts: Where do you find the citext module in postgres 9.3 on ubuntu 14.04?

texts:
So I'm trying to use citext, but it appears to be MIA from ubuntu 14.04 postgres install.  Where can you find this mythical beast called citext for postgresql 9.3?

project_test=> CREATE TABLE "customers_addresstype" (
project_test(>     "id" serial NOT NULL PRIMARY KEY,
project_test(>     "customer_id" integer NOT NULL REFERENCES "customers_customer" ("id") DEFERRABLE INITIALLY DEFERRED,
project_test(>     "display" citext NOT NULL,
project_test(>     UNIQUE ("customer_id", "display")
project_test(> )
project_test-> ;
ERROR:  type "citext" does not exist
LINE 4:     "display" citext NOT NULL,
                      ^
project_test=> CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA ext;
ERROR:  could not open extension control file "/usr/share/postgresql/9.3/extension/citext.control": No such file or directory

$ ls /usr/share/postgresql/9.3/extension
plpgsql--1.0.sql  plpgsql.control  plpgsql--unpackaged--1.0.sql

$ ls /usr/lib/postgresql/9.3/lib
ascii_and_mic.so     euc_cn_and_mic.so   latin2_and_win1250.so  pgxs               utf8_and_big5.so      utf8_and_euc_jp.so   utf8_and_gbk.so        utf8_and_sjis2004.so
cyrillic_and_mic.so  euc_jp_and_sjis.so  latin_and_mic.so       plpgsql.so         utf8_and_cyrillic.so  utf8_and_euc_kr.so   utf8_and_iso8859_1.so  utf8_and_sjis.so
dict_snowball.so     euc_kr_and_mic.so   libpqwalreceiver.so    tsearch2.so        utf8_and_euc2004.so   utf8_and_euc_tw.so   utf8_and_iso8859.so    utf8_and_uhc.so
euc2004_sjis2004.so  euc_tw_and_big5.so  pg_upgrade_support.so  utf8_and_ascii.so  utf8_and_euc_cn.so    utf8_and_gb18030.so  utf8_and_johab.so      utf8_and_win.so

I don't see in the repository and locate -i citext finds nothing.


texts:
I am creating a database design for manufacture company for my coursework. The database design should be able to do these tasks:

Buy items from suppliers. (M - N)
Items are used for making products. Multiple items can be used to create multiple products. (M - N)
The products are sold to customers. Multiple order can have multiple products. (M - N)
A customer can have multiple orders. (1 - M)
A order can have multiple transaction. (1 - M)

So, with these business rules, I am not sure of how to create a un-normalized form.

My UNF (un-normalized form) looks like this.

ORDER
(
    order_no, order_date, customer_no, customer_name, customer_phone, customer_address,
    {
        prod_no,
        {
            item_no, item_desc,
            {supplier_no, supplier_desc}
        },
        prod_desc, unit_price, ord_qty, line_total
    },
    order_total,
    {trans_no, trans_amount, trans_status}
)

Should I create two UNF for two actions?

Order => to sell product to customers.
Item => to buy items from suppliers and create products.


texts: Problem filtering information in MySQL query

texts:
I've a little problem with a SQL query.

The objective is to get an employee profile related to a role.

Table EMPLOYEE (Louis,Mary,Christian):

EmpId
EmpName

Table ROLE (Chef,Waiter,NULL):

RoleID
RoleNAME

Table EMPROLE:

EmpId
RoleId

An employee should have 0..n roles. To get an employee profile:

IF Chief -> BOS
ElseIF waiter -> CHA
Else -> CUS

The objective is to print EmpName, all roles, and the profile.

I can print all roles with group_concat and get the role using case but that only works if the role is the first occurrence in the group_concat column.

My query:

select EMPLOYEE.EmpName,GROUP_CONCAT(ROLE.RoleName) as role_,
(CASE WHEN ROLE.RoleName = "Chief" THEN "BOS"
CASE WHEN ROLE.RoleName = "waiter" THEN "CHA"
ELSE THEN "NULL"
END) AS PROFILE
FROM EMPLOYEE,ROLE,EMPROLE
WHERE EMPLOYEE.EMPLOYEEID=EMPROLE.EMPLOYEEID AND EMPROLE.ROLEID=ROLE.ROLEID group by EMPLOYEE.EmpName;

Output:

+----------+--------------------------------+--------+
| EmpName  | roles                          | PERFIL |
+----------+--------------------------------+--------+
| Louis    | Waiter,Chief                   | NULL   |
| Mary     | Chief,Waiter                   | BOS    |
| Christian| NULL                           | NULL   |
+----------+--------------------------------+--------+

How can I get the information right?


texts: Mysql locking - How to acquire a lock in the DB for each userId

texts:
Hi I have a J2EE Server(clustered) that executes requests from multiple users.

I would like that each request of a given user will be handled like so:

{
  acquireLockForUser(userId); // i.e. insert a lock row for a dedicated table
  doSomeStuffInDbForUser(user);
  releaseLock(userId);  // i.e. delete a lock row from the dedicated table
}

This will make sure that each user request will be handled by a single thread of my servers.

Whats the way to achieve that? (Is it possible to insert into a table 'userLock' by id and lock it? how to insert\fetch from this "lock" table?)


texts: Circular references for tracking currently active entity

texts:
I want to store active entity in one of my tables let's say I have 2 tables:

Pirate
Ship

Named it like that to keep things interesting

I want each pirate to own many ships but have only one active ship. For that Ship will have PirateId column and Pirate will have ActiveShipId. Obviously for that to work one of those columns has to be NULLable depending on the order I insert new entries.

I also have IsActive column on the Ship table, ActiveShipId is supposed to be a shortcut and will possibly give me better performance - Lets suppose I want to get active ship without circular reference:

SELECT * FROM Ship WHERE PirateId = @0 AND IsActive = 1

With circular references:

SELECT * FROM Ship WHERE Id IN (SELECT ActiveShipId FROM Pirate WHERE Id = @0)

Will this kind of circular reference cause me any problems?
Is there any better way to design this schema?
Is ActiveShipId redundant when I have IsActive, or will it provide any performance benefits?


texts: PostGIS error building with `make` in ./extensions: make[1]: *** No rule to make target

texts:
Whenever I run make in extensions, I'm getting error.

make
for DIR in postgis  postgis_tiger_geocoder  postgis_topology address_standardizer; do \
    echo "---- Making all in ${DIR}"; \
    make -C "${DIR}" all || exit 1; \
done
---- Making all in postgis
make[1]: Entering directory '/home/ecarroll/code/postgis/extensions/postgis'
cp ../../doc/postgis_comments.sql sql_bits/postgis_comments.sql
make[1]: *** No rule to make target '../../raster/rt_pg/rtpostgis_for_extension.sql', needed by 'sql_bits/rtpostgis.sql'.  Stop.
make[1]: Leaving directory '/home/ecarroll/code/postgis/extensions/postgis'
Makefile:26: recipe for target 'all' failed
make: *** [all] Error 1


texts: Will subquery (using IN) run multiple times for update statement (SQL Server 2017)

texts:
Where an update statement is updating the value returned by a subquery - will the subquery be run for each row?

UPDATE a
SET DM = 1
FROM TableA a
WHERE a.ID NOT IN (SELECT DISTINCT ID FROM TableA WHERE DM = 1)

Column ID is not unique in the table so we can have multiple rows for an ID

Will it always update all rows for the ID to DM = 1 or sometime just one?


texts: Not able to install PostgreSQL 9.4 on Ubuntu

texts:
When I try to install PostgreSQL 9.4.+ on Ubuntu, it is installing version 9.3.9. I have used the following command:

sudo apt-get install postgresql postgresql-contrib

Then I have tried with sudo apt-get install postgresql-9.4.
But that gave me an error like "package not found".

How can I install 9.4 on Ubuntu?

Distribution details:

Distributor ID:   Ubuntu
Description:  Ubuntu 14.04 LTS
Release:  14.04
Codename: trusty


texts: SQL for finding composite key elements with same field

texts:
I have a table with a composite key (a,b) wherein I want to return the first key and the count of the second key that have the exact same set. For example if we have:
 a | b
-------
01 | 2
01 | 3
02 | 1
02 | 2
02 | 3
03 | 2
04 | 2
04 | 3

Since 01 and 04 have the same second column (2,3) and the others have none we get:
01 | 1
02 | 0
03 | 0
04 | 1

This should apply to any set of composite keys (a,b) and not its not just inclusion but equality we're looking for.


texts:
I need to update rows in a child table,but I am getting following error while I run the code,

Company Table

CREATE TABLE `company`
(
 `Company_id` int(11) NOT NULL AUTO_INCREMENT,
 `Company_name` varchar(255) DEFAULT NULL,
 `BuildingNameNumber` varchar(255) NOT NULL,
 `Street_name` varchar(255) NOT NULL,
 `Area` varchar(255) NOT NULL,
 `Landmark` varchar(255) NOT NULL,
 `PIN_code` int(6) NOT NULL,
 `PhoneMobile_number` char(15) NOT NULL,
 `Website` varchar(255) NOT NULL,
 `Fax` varchar(255) NOT NULL,
 `Email` varchar(255) NOT NULL,
 `Upload_logo` varchar(255) DEFAULT NULL,
 `tin_number` varchar(50) NOT NULL,
 `CreatedOn` datetime DEFAULT NULL,
 `CreatedBy` varchar(255) DEFAULT NULL,
 `UpdatedOn` datetime DEFAULT NULL,
 `UpdatedBy` varchar(255) DEFAULT NULL,
 `is_active` bit(1) DEFAULT b'0',
 PRIMARY KEY (`Company_id`),
);

Service Table

CREATE TABLE `service` (
`service_id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `serial_number` varchar(256) DEFAULT NULL,
  `service_name` varchar(256) DEFAULT NULL,
  `price` float(10,2) NOT NULL DEFAULT '0.00',
  `tax` float(5,2) NOT NULL DEFAULT '0.00',
  `is_active` bit(1) NOT NULL DEFAULT b'1',
  PRIMARY KEY (`service_id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `service_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`Company_id`) ON DELETE CASCADE ON UPDATE CASCADE
);

When I trying to update the service table I am getting error as

**Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`etailershop`.`service`, CONSTRAINT `service_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`Company_id`) ON DELETE CASCADE ON UPDATE CASCADE)  0.031 sec**

I was trying to find the error, I do understand the foreign key constraint but I could not find what is wrong with this, similar structure and constraint tables are working fine.


texts:
I was wondering if any of you can share some experience regarding integrating Flyway and Git. We're currently developing a project, each sprint we need to make database changes for new features of course. Once every two weeks we merge changes made in dev DB to prod DB (the code reside on different branches in Git and we merge every two weeks). We want to start using Flyway for DB version control. Right now the idea is to create sql scripts on dev branch when we need a change, and run migrate on dev for every change we need. Then every two weeks when merge happens, the sql scripts will be merged to prod branch and then be run (using flyway migrate) using pipeline.
Does that sound like a good approach? Can anyone share some experience about it or suggest other approaches?


texts: What steps we need to perform from DB end in case of storage migration?

texts: What are the impacts / steps from DB2 DB side before VMware SvMotion Storage migration ?


texts: MySQL User with Host set to a Range of IP Addresses

texts:
I have a hosted MySQL DB that I need to give access to from my customers site. They have a fast broadband connection which can present them with a range of IP addresses from a fixed pool.

So I need to lock down the access to their MySQL user so that the Host setting is a range of addresses within a class. Not the whole class.

I realise I can use a wildcard such as 81.91.71.% but that is too generous. Their range is more like

81.10.20.1 --> 81.10.20.15

Is there a way to do this other than create 15 users, one for each individual IP address?
thanks
Mark


texts:
This command gives the date of the last update for a table

SELECT UPDATE_TIME
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'MyDB'
AND TABLE_NAME = 'MyTable'

But I want to find the time of last update of a particular column of a table.
I can't use triggers because I want to know the time of last update of a specific column from a system table.

I hope I explained well my problem.


texts: Exception in SQL client but not in stored procedure

texts:
I am using Oracle 11g and SQLDeveloper for this scenario.  In this scenario, I am trying to simulate an exception within a stored procedure but it is not happening.  My DB runs in GMT.  My client machine is in CDT.

Can anyone explain why I receive an exception when I perform this in my client:

SELECT *
FROM dual
WHERE current_timestamp > to_date('03-10-2013 02:30','MM-DD-YYYY hh:mi');

ORA-01878: specified field not found in datetime or interval
01878. 00000 -  "specified field not found in datetime or interval"
*Cause:    The specified field was not found in the datetime or interval.
*Action:   Make sure that the specified field is in the datetime or interval.

But I do not get any exception output when I do the exact same query in a stored procedure:

create or replace
PROCEDURE           "DMS_TEST_EXCEPTION" AS
    v_Num1      NUMBER := 1;
BEGIN
  DECLARE
  v_code  NUMBER;
  v_errm  VARCHAR2(64);
  BEGIN
    SELECT '1' into v_Num1
    FROM dual
    WHERE current_timestamp > to_date('03-10-2013 02:30','MM-DD-YYYY hh:mi');
    EXCEPTION
      WHEN OTHERS THEN
         v_code := SQLCODE;
         v_errm := SUBSTR(SQLERRM, 1, 64);
         DBMS_OUTPUT.PUT_LINE (v_code || ' ' || v_errm);
  END;
END DMS_TEST_EXCEPTION;

Connecting to the database CERT CAM DR.
Process exited.
Disconnecting from the database CERT CAM DR.


texts: Downloading files based on most recent date and timestamp

texts:
I have a requirement to download zipped files from an SFTP site using C# and Script Task within SSIS.

We have multiple files on the server with the same name, but with different date and timestamps.

Is there a way download the files based on most recent date and timestamp?


texts: How to UPDATE by COUNT from JOINed tables

texts:
I have a schema as

CREATE TABLE companies
(
company_id int(11) NOT NULL AUTO_INCREMENT,
company varchar(255),
projects int(11),
PRIMARY KEY(company_id)
) ENGINE=InnoDB

CREATE TABLE employees
(
employee_id int(11) NOT NULL AUTO_INCREMENT,
company_id int(11),
name varchar(255),
PRIMARY KEY(employee_id)
) ENGINE=InnoDB

CREATE TABLE project_map
(
employee_id int(11),
project_id int(11),
PRIMARY KEY(employee_id,project_id)
) ENGINE=InnoDB

I need to UPDATE column projects in table company, by counting all projects done by employees of each company.

UPDATE companies
SET project = count of project_map by employees of each company


texts:
The current database assumes all times are local to the user. Now we're changing this so the database stores everything in UTC and our application converts the UTC time in the database to the correct timezone for each user.

Now we need to update every datetime in the database to UTC time. Is there a better way than having our application (which has a Timezone library) update each one individually?

Due to daylight savings time (BST), a simple DATEADD isn't an option. There are roughly 10 million cells to update.


texts: Data from Same Column Joined in Two Different Ways

texts:
In the following sybase query I would like to pull the vendor column twice: the first time I would like to pull the vendor associated with the ReceiveWorksheet table by associating it with a key RCW_VEN_FK, and I then would like to pull from the same table again but this time associating it with the StockInventoryLocal table by the key SIL_VEN_FK_Default.  The goal is to pull the data into two different columns - one which will show the default vendor and one which will show the vendor associated with the receiving worksheet.

I've been looking for quite some time on how to accomplish this.  The first time I received an error that the item was already created (when trying to simply pull from the Vendor table twice), I then tried with aliasing and was unable to accomplish it in that manner.

Does anyone have a suggestion or a resource on how to pull this information from the Vendor table twice and associate it with different keys in different tables and have it populate as two different columns?

ALTER VIEW "ecrs"."00_Test3_BD"( /* view_column_name, ... */ )
AS
SELECT "ecrs"."Vendor"."VEN_Code",
"ecrs"."Vendor"."VEN_CompanyName",
"ecrs"."StockInventoryLocal"."SIL_VEN_FK_Default",
"ecrs"."ReceiveWorksheet"."RCW_VEN_FK"
FROM ( "ecrs"."Vendor" JOIN "ecrs"."StockInventoryLocal" As "DefaultVendor" ON "ecrs"."Vendor"."VEN_PK" = "ecrs"."StockInventoryLocal"."SIL_VEN_FK_Default" ) JOIN "ecrs"."ReceiveWorksheet" As "WorksheetVendor"  ON "ecrs"."Vendor"."VEN_PK" = "ecrs"."ReceiveWorksheet"."RCW_VEN_FK"

I tried the above and received an error "Table 'DefaultVendor' not found."


texts: How do I copy a view from one server to another?

texts:
I want to copy a view from server S1 --> Database D1 to Server S2 --> Database D2.

I generated the script by right clicking the database(D1)--> tasks--> generate script.  However, when I execute this script in Database D2 it says that D1 doesn't exist.  The error is on line1.

USE [D1]  -- Line 1
GO


texts: Index fragementation ? is it unavoidable

texts:
This is follow up question to @martin smith excellent answer https://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean

After that i have watched MCM video of Paul randal which shows why index fragmentation matters more in large scans only for huge tables..If i understood correctly, external/logical fragmentation occurs when logical continuity of pages differ from physical order in which they should be..

Suppose i have big table  and identity as clustered key and no updates to clustered key and all pages are in logical order,does Sql guarantee all these pages lay in disk in close proximity...during first inserts it may block some space for this table,but after that many inserts may happen to other tables as well,so data might not be in close proximity...Is my understanding correct ?


texts: Ola Hallengren MaintenanceSolution - Differential - the value for @ModificationLevel is not supported

texts:
I just installed this great tool, mostly it's working fine, just that if I include for a differential backup the parameter @ModificationLevel = 50 (or anything else) the action fails:

  Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 2268
  The value for the parameter @ModificationLevel is not supported.

Example:

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'ALL_DATABASES', @CleanupTime = 2160, @Compress = 'Y',
@Directory = '\\cem01nas2\sql_backups',
@BackupType = 'DIFF', @ChangeBackupType = 'Y', @ModificationLevel = 50
GO

Gives the error, but if I remove the @ModificationLevel parameter it works. Any ideas?


texts:
This is a strange issue, i am unable to create linked server using provider Oracle Provider for OLE DB. But am able to connect to it using sqlplus, was able to do telnet and tnsping.

when i'm trying from ssms it is giving an error saying cannot initialize the data source object of OLEDB provider.

I have enabled the allowin processes for the Oledb provider.


texts: Best practice for shut down and restart of Replicated SQL Servers

texts:
We have SQL Server 2008 R2 and SQL Server 2012 in production. We have transactional and snapshot replication implemented in our environment and unfortunately the subscribers are also the distributors. Now, after almost 5 years we have to shut down all the servers for maintenance (power outage) and then restart them when the maintenance is done. I know this is not ideal but this is how it is going to be done.

My only concern is that is there any sequence of steps recommended while restarting the replicated servers? I mean something like Publisher should be shut down before the Subscriber etc. TIA


texts: How to migrate MongoDB 2.4.14 file to MongoDB 3.6?

texts:
I have a database file from MongoDB 2.4 and we want use this on mongodb 3.6.
note: the file is original from the db path. Not from mongodump.
This process is messy as it requires doing so on many of our clients.

Any advice will be welcomed as I'm a bit lost in this process.

Thanks


texts: Cumulative duration between value resets


texts: Handling .bak and .dif files to optimize storage usage

texts:
I'm working with a number of different servers that are severely lacking in backups.

I want to implement a back up plan using simple mode with full backups weekly followed by a series of differential backups throughout the week.

Is it a viable plan to perform the full and differential backups by overwriting the same file? Again I am trying to limit the amount of storage used and from my understanding if I'm taking a differential backup on some interval the data is cumulative therefore only the most recent .dif file in conjunction with the full .bak file will be necessary. Therefore each time a differential backup is performed it would overwrite the previous. Is this safe or am I misunderstanding something crucial?


texts: Find out the number of rows/columns changed - SQL Server?

texts: I found row/colmodctr options in sys.sysindexes but this has no equivalent in sys.indexes. Because sys.indexes is the recommended catalog view how can I find row and column change number without using sys.sysindexes?


texts: Preventing users from accessing DB while indexes are being rebuilt

texts: I need to rebuild the indexes in two very large (almost 750 table) database but want to prevent users from accessing the database while this happens. Is there a state I can put the database in that prevents the users from accessing it but still allows me to rebuild the indexes? For reference I plan on the script provided at http://instadba.com/quick-script-to-defragment-your-sql-server-indexes/ to do the rebuild.


texts: Does partitioning sometimes require denormalization?

texts:
Specific example of what I'm asking:

Say that a SaaS app has an Order table with CustomerID and a child OrderItem table.

We want to partition both tables and we want to use CustomerID as the partition key.

The issue is that the OrderItem table does not have a CustomerID column (its only foreign key is to the Order table).

Would it be a best-practice to denormalize the OrderItem table and add a CustomerID column to it for the sake of partitioning?


texts: How to run a cold backup with Linux/tar without shutting down MySQL slave?

texts:
I run the following before tar-ing up the data directory:

STOP SLAVE;
FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;

However, tar will sometimes complain that the ibdata* and ib_logfiles* files are updated during the process. What am I missing?

The slave machine is in a cold standby machine so there are no client processes running while tar is running.

CentOS release 5.6 64bits, MySQL 5.1.49-log source distribution.


texts: Extracting Multiple Values from Single Field

texts:
I'm an SQL beginner and I'm having trouble searching correctly for the answer to this question, although I'm sure that it's been asked before.  I have an column in an SQL table that looks like this:

custom_fields (this is the column name)
age_range=35-49
gender=Male
how_heard=Other  (All three of these values are in the same field)

Is there any way to reach inside of this field with an SQL statement to get the values individually?  I'd like to make three lists of the individual values to export to CSV files in a vertical columns.

Basically, the result should be a column like (for each value):

age_range
35-49
45-55
25-35
... etc.

I've been trying:

SELECT custom_fields.age_range FROM arp3_cam WHERE 1

But this is obviously incorrect.  I'd really appreciate any hints on how to filter for values inside the same field -- feel free to redirect me to an answer if this has already been asked somewhere else.  Thanks!

EDIT: based on other posts I've been reading, this setup violates first normal form, however, I still need to extract the data, if possible.  I definitely wish the values in "custom_fields" had been added as individual columns.


texts: Does SQLCMD "!!" command work in Azure Data Studio

texts:
In SQL Server Management Studio, it is possible to run batch commands when in SQLCMD mode, using the !! command. However, in Azure Data Studio, this does not seem to work (SQLCMD has been available in ADS since version 1.13.0 in November 2019), I'm running ADS as Windows Administrator.
How can I execute batch commands in Azure Data Studio using SQLCMD mode?
Did I miss something, or is there some of the (scarce) documentation I need to read?
- In SQL Server Management Studio:

- In Azure Data Studio


texts:
For example, I have articles, and authors. An article needs at least one author. But you can delete multiple authors at the same time and concurrently. So I need to prevent having a state where the articles doesn't have any authors.

I thought about using advisory lock and does this:

--- Setup

CREATE SCHEMA IF NOT EXISTS advisory;
DROP TABLE IF EXISTS advisory.authors;
DROP TABLE IF EXISTS advisory.articles;

CREATE TABLE advisory.articles (
    articleid bigserial PRIMARY KEY,
    text text NOT NULL
);

CREATE TABLE advisory.authors (
    id bigserial PRIMARY KEY,
    articleid bigint NOT NULL REFERENCES advisory.articles (articleid),
    name text NOT NULL
);

INSERT INTO advisory.articles (text)
        VALUES ('article 1'),('article 2');
INSERT INTO advisory.authors (articleid, name)
        VALUES (1,'a'),(1,'b'),(1,'c'),(2,'alone');

--- Final query

BEGIN TRANSACTION;
   SELECT pg_advisory_xact_lock(2); --article id

   WITH authors_count_per_article AS
   (
     SELECT COUNT(*)
     FROM advisory.authors
     WHERE articleid = 2
   )
   DELETE
   FROM advisory.authors
    WHERE EXISTS (SELECT 1 FROM authors_count_per_article WHERE count > 1)
      AND advisory.authors.name = 'alone'; -- will return 0 because only one author
COMMIT;

Is it a good thing to use an advisory lock like this?
Is there a better way to write the query where I check for the author count? I find it quite complicated.
Any tips for a good lock id?

I am using PostgreSQL 10


texts:
I'm planning to take a dump of a table with the --single-transaction option enabled.

I know that if we execute any DDL statements during the dump process we might get an inconsistent dump. Will the same happen when there are any temporary tables created? We have temporary tables created in our app, and do not have any control over that.


texts: MySQL: How does AES_ENCRYPT turn the password into a key?

texts: I am trying to figure out how to create the best secure password for AES_ENCRYPT. Specifically, how long should the password be, and how it translates into the 128 bit key length that AES typically uses.


texts: Directory lookup failed when adding sample database to SQL Server 2012

texts:
I am trying to add the AdventureWorks sample database to SQL Server 2012, but I get an error message:

  Msg 5133, Level 16, State 1, Line 1
  Directory lookup for the file "C:\Users\A\Documents\SQL Server Management Studio\Data\AdventureWorks2012_Data.mdf" failed with the operating system error 5(Access is denied.).


texts: I want to create the best indexes for each table in my database. Is there a query or tool in SQL Server to help with this process?


texts: Creating Excel files in SSIS based with a predefined format

texts:
I need to create a statistical report in Excel and wondering if I can use SSIS to read data from the database (calculate the aggregated numbers) and load them into an Excel file with a specific layout. From what I can see, in SSIS 2008 R2, we don't have control on the target excel file, cell by cell, or perhaps I missed something. Is there any way to achieve it through SSIS?

Thanks for your help in advance.


texts: Can any one Explain me What is the Use of Cascading Replication in Postgresql?

texts:
What are the advantages of cascading replication in PostgreSQL.

I have done Testing On this With three nodes.

M1---->S1----->S2

What is the Use if My master(M1) goes Down(failover). Where the secondary Slave(s2) Server Useful for us in this setup.


texts: Use one table and a "type" column to represent two types of vote_counter or use separate table?

texts:
I am building a blog system, the blog can have its comment. And like to provide the upvote/downvote feature for both the blog and comment. Since the vote count number of blog/comment should be persisted, i choose to use MySQL to act as the data store.
It seems that i have two choice, the first one is, use a general table and a type column to represent these two kinds of counter, like:
vote_counter
------------
entity_id   bigint          // the id of blog or comment, depends on the "type" column value
type        tinyint         // for example, 1 for blog and 2 for comment
count       integer

Another option is to use one table for one "voteAble" type, for example:
blog_vote_counter          comment_vote_counter
-----------------          --------------------
blog_id    bigint          comment_id    bigint
count      integer         count         integer

I am pretty new to MySQL, and i am desired to know that which kinds of table design should be the better one, there pros and cons, and the reasons.


texts: How do I use the smallest amount database file space to rebuild an index on Mircosoft SQL Server Express due to the 10GB limit

texts:
How do I use the smallest amount of database file space to rebuild an index on Mircosoft SQL Server Express due to the 10GB limit

I have a database file size of 7GB but I have two indexes that are on the two largest tables that are very fragmented

If I try to rebuild the index PK_Sale_Dtl the file size balloons to 10GB and the rebuild fails and I get nowhere but if I drop the index and recreate it the file doesn't barely increase at all

Why is this? And which is better rebuild or recreate index?


texts:
In my Entity Relationship Diagram, an entity type TEACHER had a relationship of TEACH with another entity type called CLASS. CLASS was a week entity type of TEACHER,so when mapping to a Relational Data Model, I had introduced the Primary Key, named TCode, of TEACHER into CLASS as a Foreign Key.

Now, the relation named CLASS contains the following attributes: ICode (FK), CDate, CStartTime, CEndTime.

Primary Key is composite of ICode, CDate, CStartTime.

Following partial dependency exists here: CStartTime -> CEndTime

I need some help on how to convert this into 2NF. I have read up and seen examples of customers and shops etc. etc. but I could not map the understanding into my example.

I'll appreciate if somebody can tell what to do and why?


texts:
I have a MySQL server and I want to enable remote access for a certain IP and localhost only. For some reason I couldn't manage to do it as it doesn't work every time I try. I typed the following command (of course user, password and db where different):

mysql> GRANT ALL ON foo.* TO bar@'192.168.0.1' IDENTIFIED BY 'PASSWORD';

I also disabled skip-networking.

What am I doing wrong then?


texts: Ranking System with Different Categories

texts:
I want to create a system where the user can be ranked by several different factors.

For example, the user competes in the following categories

Wins
Points

I need to know what the position of the ranking in each category last week and today.

What is the best way to do this:

Creating a table for each category (userWins and userPoints), and update as needed?
Create a table with columns where I can tell that this line is part of a category?

On each approach, how do I update and sort the rank?

Imagine this scenario even though I may have 20 different categories.


texts: Many-to-many tag filter query with a junction table get slower with more tags. Can it be faster?

texts:
Part 1

I have this query to find some videos containing all given tags. Here with tags 1,9,27,13,67 as an example.

SELECT *
FROM video v
WHERE v.id IN (
    SELECT tr1.vid as video_id FROM (SELECT video_id vid FROM tag_rel WHERE tag_id=1) tr1
    INNER JOIN (SELECT video_id vid FROM tag_rel WHERE tag_id=9) tr2 USING (vid)
    INNER JOIN (SELECT video_id vid FROM tag_rel WHERE tag_id=27) tr3 USING (vid)
    INNER JOIN (SELECT video_id vid FROM tag_rel WHERE tag_id=13) tr4 USING (vid)
    INNER JOIN (SELECT video_id vid FROM tag_rel WHERE tag_id=67) tr5 USING (vid)
    -- more joins if more tags --
)
AND is_x=0
AND is_y=0
AND deleted IS NULL
LIMIT 0, 60

It executes in ~1.6 seconds.

If I run the inner select alone, it executes in ~0.8 seconds, and the result is 77 video ids.

Now, if I replace the inner select with these (example) video ids:

SELECT *
FROM video v
WHERE v.id IN (
    1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77
)
AND is_x=0
AND is_y=0
AND deleted IS NULL
LIMIT 0, 60

This query executes in ~0.06 seconds.

Question 1: Why is this the case, and why doesn't the full query run in ~0.86 seconds, which is the sum of the two operations (get video ids by tags and get videos by video ids)?

Part 2

For my application, I can't inject the ids because the inner select might return millions of ids. So I need them in one query, together with the support of checking is_x, is_y and deleted, and offsetting the limit for pagination.

I have spent months going back and forth, and this is the fastest query for most cases.

Question 2: Can this whole query be done faster/better with other techniques?

Question 3: By adding more tags, the query gets drastically slower, but isn't more joins just filtering down the number of possible results for the engine, so it should be faster with more tags?

Info

tag_rel contains about half a billion rows with about .5 million unique tag ids. The video table has around 30 million rows.

Full query explain:

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table   | partitions | type   | possible_keys            | key     | key_len | ref                           | rows    | filtered | Extra       |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  1 | SIMPLE      | tag_rel | NULL       | ref    | tag_id,video_id          | tag_id  | 4       | const                         | 1847596 |   100.00 | Using index |
|  1 | SIMPLE      | tag_rel | NULL       | eq_ref | tag_id,video_id          | tag_id  | 8       | const,db.tag_rel.video_id     |       1 |   100.00 | Using index |
|  1 | SIMPLE      | tag_rel | NULL       | eq_ref | tag_id,video_id          | tag_id  | 8       | const,db.tag_rel.video_id     |       1 |   100.00 | Using index |
|  1 | SIMPLE      | tag_rel | NULL       | eq_ref | tag_id,video_id          | tag_id  | 8       | const,db.tag_rel.video_id     |       1 |   100.00 | Using index |
|  1 | SIMPLE      | tag_rel | NULL       | eq_ref | tag_id,video_id          | tag_id  | 8       | const,db.tag_rel.video_id     |       1 |   100.00 | Using index |
|  1 | SIMPLE      | v       | NULL       | eq_ref | PRIMARY,is_x_2,deleted   | PRIMARY | 4       | db.tag_rel.video_id           |       1 |    50.00 | Using where |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Indexes:

tag_rel.tag_id      = unique(tag_id, video_id)
tag_rel.video_id    = unique(video_id, tag_id)
video.is_x_2        = (is_x, is_y, deleted)


texts: How do I set up primary and foreign keys referencing two different tables?

texts:
I need to have two primary keys also to be the foreign keys in the same table. These two keys are referenced in different tables. I keep getting error messages that are unclear to me. My code is as follows:

CREATE TABLE ASSIGNMENT (
        ProjectID       INT             NOT NULL,
        EmployeeNumber  INT             NOT NULL,
        HoursWorked     NUMERIC(6,2)    NULL,
CONSTRAINT  AssignmentPK            PRIMARY KEY(ProjectID,EmployeeNumber),

CONSTRAINT  AssignmentFK            FOREIGN KEY(EmployeeNumber, ProjectID)
        REFERENCES  EMPLOYEE(EmployeeNumber)
            ON UPDATE NO ACTION
            ON DELETE NO ACTION,

    REFERENCES  PROJECT(ProjectID)
        ON UPDATE NO ACTION
        ON DELETE CASCADE
);

The current error message is:

  Msg 142, Level 15, State 2, Line 2
  Incorrect syntax for definition of the 'TABLE' constraint.

Please advise.


texts: History table implementation: "Tuple-versioning" vs Effective Date

texts:
I'm designing a database that will have history tables (SCD Type 4 I guess) for auditing purpose.

The question is what are the advantages of having tuple-versioning (start and end dates) vs just effective date?

1) tuple versioning

CREATE TABLE HistoryTable (
   Column1   Type1,
      :        :
   Columnn   Typen,

   StartDate DATETIME,
   EndDate   DATETIME
)

2) effective date

CREATE TABLE HistoryTable (
   Column1   Type1,
      :        :
   Columnn   Typen,

   EffectiveDate DATETIME
)

For (1) so far I see disadvantages - I have to update [end date] on the previous history record while inserting new/current one.

For (2) I can only insert new record without searching and modification the previous one.

From query point of view both approaches look quite the same.

I know that SQL Server 2016 has temporal tables but it's not available to us so far.


texts: Prevent unique constraint violations caused by concurrent inserts

texts:
I have a situation where I need to insert a unique record into the database:

SELECT user FROM users WHERE id = 'some_randomly_generated_id'
If user does not exist, INSERT INTO users WHERE id = 'some_randomly_generated_id'

The problem is, a matching record can be added after the SELECT query but before the INSERT query, causing a unique constraint error. How would I prevent/go about this? These are my current thoughts after some research:

Run these queries under a transaction and use SELECT user FROM users WHERE ... FOR SHARE to lock the row so no new record could be inserted (I might be wrong on this, please correct me).
Handle the unique constraint error in the application and pretend that the record is inserted successfully. (No transaction, could be better since it allows greater concurrency and is trivial to implement)
Run only the INSERT IGNORE query instead, which silently ignores the unique constraint error. (Similar to the 2nd approach in my opinion, just that the error is not handled in the application).

Thanks in advance. My dialect is MySQL and isolation level is REPEATABLE READS if that helps make answering this question easier.


texts: MySQL transaction and replication between two datacenters

texts:
I'm using a master MySQL in an east coast data center and want to setup a replication server on a west coast data center. The app server on the west coast may at times need to write to the master.

Since the data centers can't setup a private link between them using the internal interfaces, I have to use a public interface. Does MySQL use any encryption when sending data over the public network? How can I secure or increase security between the two sites?

Let's assume site-to-site VPN is a last resort option.


texts: Should a ledger DB store a separate line for each side of a transaction?

texts:
I'm working on a database that needs to do manage transfers between several accounts.  We are considering using a simple ledger system for this (like what you find in Quicken).

I have two concerns with how to design the system:

Tracking the related transactions, for this I considered using a single LedgerEntry for both sides of a transaction
Keeping the totals easy to calculate, for this I want to use a separate LedgerEntry for both sides of a transaction

Here's a fiddle w/ both options and their problems.


texts: mariadb galera cluster stop one node automaticly

texts:
I have  Mariadb galera cluster. I have 4 nodes. but When one node become slow, it stop itself automaticly. that's the error. What should I do?

151030 17:17:14 [Note] WSREP: view((empty))
151030 17:17:14 [Note] WSREP: gcomm: closed
151030 17:17:14 [Warning] WSREP: 0x7f8cab043c40 down context(s) not set
151030 17:17:14 [Warning] WSREP: Failed to send SYNC signal: -107 (Transport endpoint is not connected)
151030 17:17:14 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1
151030 17:17:14 [Note] WSREP: Flow-control interval: [16, 16]
151030 17:17:14 [Note] WSREP: Received NON-PRIMARY.
151030 17:17:14 [Note] WSREP: Shifting JOINED -> OPEN (TO: 55363)
151030 17:17:14 [Note] WSREP: Received self-leave message.
151030 17:17:14 [Note] WSREP: Flow-control interval: [0, 0]
151030 17:17:14 [Note] WSREP: Received SELF-LEAVE. Closing connection.
151030 17:17:14 [Note] WSREP: Shifting OPEN -> CLOSED (TO: 55363)
151030 17:17:14 [Note] WSREP: RECV thread exiting 0: Success
151030 17:17:14 [Note] WSREP: recv_thread() joined.
151030 17:17:14 [Note] WSREP: Closing replication queue.
151030 17:17:14 [Note] WSREP: Closing slave action queue.
151030 17:17:14 [ERROR] WSREP: Local state seqno (55362) is greater than group seqno (55333): states diverged. Aborting to avoid potential data loss. Remove '/mnt/mariadb/kohadata/mysql//grastate.dat' file and restart if you wish to continue. (FATAL)
     at galera/src/replicator_str.cpp:state_transfer_required():33
151030 17:17:14 [Note] WSREP: applier thread exiting (code:8)
151030 17:17:14 [Note] WSREP: starting shutdown
151030 17:17:14 [Note] /usr/sbin/mysqld: Normal shutdown

151030 17:17:14 [Note] WSREP: Stop replication
151030 17:17:16 [Note] WSREP: rollbacker thread exiting
151030 17:17:16 [Note] Event Scheduler: Purging the queue. 0 events
151030 17:17:16 [Note] WSREP: dtor state: CONNECTED
151030 17:17:17 [Note] WSREP: mon: entered 4827 oooe fraction 0 oool fraction 0
151030 17:17:18 [Note] WSREP: mon: entered 4827 oooe fraction 0.00103584 oool fraction 0
151030 17:17:18 [Note] WSREP: mon: entered 8943 oooe fraction 0 oool fraction 0.000111819
151030 17:17:18 [Note] WSREP: cert index usage at exit 0
151030 17:17:18 [Note] WSREP: cert trx map usage at exit 0
151030 17:17:18 [Note] WSREP: deps set usage at exit 0
151030 17:17:18 [Note] WSREP: avg deps dist 3.22926
151030 17:17:18 [Note] WSREP: avg cert interval 0.0052105
151030 17:17:18 [Note] WSREP: cert index size 2
151030 17:17:18 [Note] WSREP: Service thread queue flushed.
151030 17:17:18 [Note] WSREP: wsdb trx map usage 0 conn query map usage 0
151030 17:17:18 [Note] WSREP: MemPool(LocalTrxHandle): hit ratio: 0.866667, misses: 4, in use: 0, in pool: 4
151030 17:17:18 [Note] WSREP: MemPool(SlaveTrxHandle): hit ratio: 0.994795, misses: 25, in use: 0, in pool: 25
151030 17:17:18 [Warning] WSREP: Waiting for 28 items to be fetched.

I have another error

151030 17:44:28 [Note] WSREP: 1.0 (koha-db58): State transfer from 2.0 (koha-db59) complete.
151030 17:44:28 [Note] WSREP: Member 1.0 (koha-db58) synced with group.
151030 17:48:13 [Warning] WSREP: last inactive check more than PT1.5S ago (PT1.54315S), skipping check
151030 17:48:15 [Warning] WSREP: last inactive check more than PT1.5S ago (PT2.09676S), skipping check
151030 17:48:22 [Warning] WSREP: last inactive check more than PT1.5S ago (PT2.0889S), skipping check
151030 17:48:25 [Warning] WSREP: last inactive check more than PT1.5S ago (PT1.59553S), skipping check
151030 17:48:28 [Warning] WSREP: last inactive check more than PT1.5S ago (PT3.07406S), skipping check
 151030 17:57:17 [Warning] WSREP: last inactive check more than PT1.5S ago (PT18.6297S), skipping check


texts: Merge replication Locking on MSMerge_genHistory Table and lots of unhappy chickens

texts:
I do contracting with about 10 chicken farms.  Each chicken has a GUID so we can track it through the entire life process (govt. requirement).  We have 2 million chickens in the tables at any one time.  One of my largest clients wants me to implement merge replication to another server so that they have 2 databases that look the same at any one time. SQL Server A will have data handling with users in the warehouses inputting data. SQL server B will have bulk data coming into it from grocery stores concerning our chickens and their sales information and will merge with Server A every 5 min.  If the GUIDs are the same the information with the latest updated date will win the Resolver.  We have about 1000 batch requests per second CPU is about 45% on a 16 Core Processor and 250GB of memory. On our Publication (server A) DB.  Server B looks the same except we have the bulk info come in 2 times a day with about 10000 DML queries.  We are trying to merge 900+ tables of which 45 of them are busy with Transactional processing all the time.   The 865 or so tables get updated about once a week. We are getting consistent  dead locks on our MergeGenHistory table. Look below.    We have also set our generation_leveling_threshold as below
UPDATE sysmergepublications SET generation_leveling_threshold = 200000 WHERE NAME = ‘datbaseName’.
Updating statistics on the merge replication tables every 5 min, right before the merge agent runs.

We have created a custom agent those settings are also below.  Everything not shown in the pick below is set at the default value.


texts: Is there a way to list the "last backup" file of a Database?

texts:
I'm trying to do something really simple, but with one problem:

declare @cmd varchar(1000)

set @cmd='restore verifyonly from disk =''\\server\backup_folder\''?'''

exec sp_msforeachdb @cmd

Obviously its not working. I need the name of the backup file to run Verifyonly ( in the above code, i'm taking the Database name ).

Is there a way to automate Verifyonly, to run in all backup files?

How can I use the name of the backup file that has been made, in my code?

Thank you very much.

( People are saying MsForeachdb is not safe. I will try to search another option, like Foreachdb )

SQL SERVER 2008

EDIT1:  Well, As i see here, I don't think verifyonly is a good idea, but will be good to know how to do it ( only for knowledge )


texts: How does an OLAP cube database differ from an RDBMS?

texts: Is a cube database, simply an RDBMS—such as PostgreSQL or MySQL—configured for OLAP via a star or snowflake schema? Or is a cube database different from an RDBMS, and if so how?


texts: MS SQL Server 2005 DB backup with partitioned tables to non-partitioned SQL Server 2012

texts:
I have a database with several partitioned tables on MS SQL Server 2005 Enterprise, and I have to restore this DB on MS SQL Server 2012 Standard. Since Standard doesn't support partitioning - it's a bit complicated to restore all the data to the new server. I have to find a simplest and quickest solution.
My first idea is to remove partitions on 2005 DB, then make a backup and restore it on SQL Server 2012. But my database is large and to merge it's partitioned data takes couple of hours for just one table.
The quickest way, from my point of view, would be the data transfer from old to new DB, but it will probably also take too much time.
Please share your ideas - may be I'm not aware of some features in 2012 that might be helpful.


texts: Switch Between Windows Authenticated Users On Single Windows Account

texts:
We have a CRM that has been developed in house in Microsoft Access 2003. We are also in the process of moving from an MS Access backend to a SQL Server backend.

I have been researching different types of authentication for SQL Server and everything suggests sticking to Windows Authentication because it is the most secure and reliable way to connect to SQL Server.

The issue with Windows Authentication is that sometimes I may go out on the floor and login as myself with my permissions in the CRM, and I will be able to manipulate the data I have the authority to reach in our current database. With our new SQL Server database I am planning on using the Principle of Least Privilege, which means that I will have issues signing into our CRM when the windows user is authenticating with lower permissions, meanwhile I am logging into the applicaton with heightened permissions.

Is there a way I could prompt for a Windows Login for my CRM application or do we have to log out of our domain account and into another to use Windows Authentication.


texts: Possible to use first CTE as filter for second CTE?

texts:
I'm trying to optimize a query on a large database that essentially fetches 50 images for a site and the URL's for the pages they appear on. The current query works okay for smaller sites but larger ones, such as the one I'm testing with that has 50,000 images, the query effectively times out.

I've narrowed down the slowness to the second CTE (y) because it's fetching all matching records in that table that consists of over 150M rows. What I'd like to do is use the first CTE (x) as an input to the second so that only records tied to resources fetched in x are fetched in y, since x is where the filtering occurs and results are limited to 50 per query. Is this possible?

WITH x AS
(
    SELECT vi.resourceid
    FROM vw_image vi
    WHERE vi.siteid = 2294
    AND vi.childtype = 'i'
    ORDER BY vi.id
    OFFSET 1 ROWS
    FETCH NEXT 50 ROWS ONLY
),
y AS (
    SELECT rop.resourceid as resourceid, r.url as url
    FROM resourceonpage rop
    INNER JOIN resource r ON rop.pageid = r.id
    WHERE rop.siteid = 2294
)
SELECT i.*, string_agg(y.url, ',') as urllist
FROM x
INNER JOIN vw_image i ON x.resourceid = i.resourceid
LEFT JOIN y ON y.resourceid = x.resourceid;


texts: Lots of small SQL statements in SQL Server and measuring network time between DB and Client

texts:
I am load testing an application and it appears from New Relic that each transaction executes a significant number SQL statements (approx 250). As we scale up, the service that makes these calls appears to degrade; however New Relic is a bit unclear as to why this is as it does not account for all the time (just the SQL execution time).

In Oracle I would be able to run a trace which would show the latency of the network round trips associated with execution of each SQL statement. Is there an equivalent set of SQL Profiler (or similar) metrics that will help me trace the time between the client service issuing the SQL request and it receiving the response?


texts: Require to Send a formatted HTML Email via Database Mail in Sql Server 2008 R2

texts:
I need to send an e-mail from SQL Server in the below format, as requested by the client:

Dear colleague,

See attached file with your current list of high priority ticket notifications.

Following tickets are listed:

* High priority tickets: last article time is at least 7 calendar days in the past.
* Very high priority tickets: last article is at least 3 calendar days in the past.

Please check if the priority of these tickets is still correct.
If so, don’t forget to focus on them.

Thanks in advance for reserving some time to work on them.

As of now I have been using the below format to send short messages:

Declare @BODY VARCHAR(50)
declare @id  varchar
declare @email_id varchar(50)
DECLARE emailCursor CURSOR FOR
SELECT id, email_id FROM email_address
OPEN emailCursor
FETCH NEXT FROM emailCursor INTO @id, @email_id
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @BODY = 'The following ticket'  +  @id  + 'is open'
    EXEC msdb.dbo.sp_send_dbmail
        @recipients = @email_id,
        @profile_name='Currency Rate Notifier',
        @subject = 'Open tickets',
        @body = @BODY
    FETCH NEXT FROM emailCursor INTO @id, @email_id
END
CLOSE emailCursor
DEALLOCATE emailCursor

Any help would be appreciated.


texts: What can go wrong using the same sequence across multiple tables in postgres?

texts:
We are considering using a shared sequence to assign ids to primary keys for all of the tables in our database. There are about 100 of them. Only a couple are inserted to frequently and regularly. We want to rule out it being "a terrible idea for an obvious reason" before we moved to the phase of actually trying it and testing it at load.

Our peak load is of the order of 1000 inserts a second, across a couple of tables.

Our research thus far indicates that
- sequence generation speed shouldn't be an issue
- sequence fragmentation (gaps) will happen, but shouldn't be an issue
- id exhaustion won't be an issue

We're not sure if we're missing other big things. We'd be grateful for people's opinions, especially from people who have tried it before and had either positive or negative experiences.

For context - we have two main motivations for doing this.

One motivation to do this is so that we can define a bunch of dictionaries (we call them scopes) and have human readable words assigned to those ids, so we want to make sure that ids in different tables never overlap. So, in one scope, id 12345 might be assigned the value "Green" and in another it might be assigned "Verde". (Actually, we don't use it for internationalisation, but we might one day).

The other motivation is to make it easy to have several deployments in the field and know (by uniquely setting each deployment's sequence couple of most significant digits) that our deployments won't overlap primary keys. (Like a GUID lite).


texts: Table with contacts: how to differ between people and organizations?

texts:
In several projects, I've seen the same problem pop up. But so far, I have not found a satisfying solution. Here's the issue. I need a table with contacts. These 'contacts' could be individual people, or organizations. From one point of view, I prefer to have these contacts mixed (one table with both types), but from another point of view, I prefer to have them separated. Here's some arguments:

A company that sells products to the general public has 'clients' of both types. When relating a sale, it is most convenient to refer to a 'client ID' without having to specify what type of client this is (for each sale).
A company can have many providers that it uses. Some are individual people (like a plummer or a consultant), others are companies. When relating a 'purchase order' I again prefer to relate to just one provider ID, without having to differentiate between provider types.
When managing the actual contacts, I do want to differentiate between the two types. That way, I can have the right fields for each. For instance, "First Name" and "Last Name" are not useful for companies, but they do are for people. Also, if I have a separate table, I can actually link people working at specific organizations.

Being pretty common - I guess - I hope that others have tackled this problem before. What is the way you set this up?


texts: Question regarding Excel connection to SSAS cube

texts: Looking for a way to change the connection in an existing spreadsheet from old SSAS server to the new SSAS server without losing the data in the spreadsheet. Hardware refresh has made this change necessary. We use Excel 2010. Nothing I have tried so far has worked. I created a new connection file (.odc) however, I can't seem to get the existing spreadsheet to connect using it or save with the new connection. Any help would be greatly appreciated.


texts: How to delete tablespace and table after delete datafiles?

texts:
I deleted datafiles with asmcmd. I need to delete tablespace and tables on this tablespace. I tried:

drop tablespace tablespace_name including contains cascade constraints

getting error:

ORA-14405: partitioned index contains partitions in a different tablespace

When I tried drop table, index or partition, getting this event and do nothing.

optimizer stats update retry.

How can I drop tablespace and all tables on this tablespace easily?

Thanks.

(Oracle 11.2.0.4.0)


texts: Which DBMS are fast enough for an online game (few thousand players)?

texts:
I'm currently making an MMORPG game, which could have a few thousand players online at the same time (probably not; just wishful thinking). First we wanted to use MySQL, but I have heard it isn't fast enough for this scale.

Which DBMS is fast enough? How much like SQL Server is it (as I have learned SQL Server in school)?


texts:
New to db admin here and I have a question. My setup is Windows Server 2012 R2, MySQL, using MySQL Workbench.

I have Database A on Server A, how can I make an exact clone of this database (Database B) on Server B.

I realise I can export/import but that's not what I require. I need Database B to exactly mirror Database A, all changes made on Database A (i.e inserts, updates, deletes) should be reflected on Database B in real time. I only need to read from Server A, not write to it.

Is this possible?

Server A and Server B are different PCs (different IP addresses) but will have the same setup, so compatibility won't be an issue.

The reason I want to do this is for testing. Until now I have been exporting/importing the database daily, which works however there must be an easier/automated way that I am missing?

I have read up on database mirroring and cloning however I think I am way off the mark?

Any help or suggestions would be appreciated.


texts: Mysql says it failed to start but is up anyway?

texts:
I'me running MySQL 8 under Ubuntu 20.04 WSL2
I'm starting MySQL with sudo service mysql restart
Output is:
* Stopping MySQL database server mysqld      [ok]
* Starting MySQL database server mysqld      [fail]

But when I now run sudo service mysql status, I get:
 * /usr/bin/mysqladmin  Ver 8.0.21-0ubuntu0.20.04.4 for Linux on x86_64 ((Ubuntu))
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          8.0.21-0ubuntu0.20.04.4
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 45 sec

Threads: 2  Questions: 4  Slow queries: 0  Opens: 430  Flush tables: 3  Open tables: 27  Queries per second avg: 0.088

Error log is:
2020-09-11T22:15:33.048074Z 11 [System] [MY-013172] [Server] Received SHUTDOWN from user debian-sys-maint. Shutting down mysqld (Version: 8.0.21-0ubuntu0.20.04.4).
2020-09-11T22:15:33.136150Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21-0ubuntu0.20.04.4)  (Ubuntu).
2020-09-11T22:15:33.231510Z mysqld_safe mysqld from pid file /var/lib/mysql/LAPTOP-77E86FL8.pid ended
2020-09-11T22:15:34.784720Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2020-09-11T22:15:35.002180Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2020-09-11T22:15:35.073728Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 1024 (requested 8161)
2020-09-11T22:15:35.073742Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 4000)
2020-09-11T22:15:35.392127Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.21-0ubuntu0.20.04.4) starting as process 6495
2020-09-11T22:15:35.412902Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-09-11T22:15:35.992356Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-09-11T22:15:36.261631Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2020-09-11T22:15:36.404215Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-09-11T22:15:36.404659Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2020-09-11T22:15:36.456568Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.21-0ubuntu0.20.04.4'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu).

My issue is that sudo service mysql restart returns a non-zero status code.
What may I check or look for?
Thank you!


texts: Include stored procedure text output in the maintenance plan log

texts:
I have a maintenance plan subtask in SQL Server 2012 Enterprise that executes a stored procedure (or a SQL Server agent job).

In the log file for this maintenance plan, there are a few lines for this task, such as the start- and end- date, and the SQL statement used to execute the stored procedure.

Can I somehow generate text output in the stored procedure and have it included in the log?

It does not absolutely have to be part of the log file, but I would greatly prefer it that way. I would like to avoid another table which has to be separately cleaned up, backed up, documented, etc.


texts: Migrating from Postgres 8.4 to 9.4 in different data centers

texts:
I'm trying to migrate a Postgres 8.4 database in a Brazilian machine to Amazon RDS Postgres 9.4 in North Virginia.

The database uses encoding LATIN1, tablespace pg_default, lc_collate and lc_ctype C.

When the web application is connected to the 8.4 database, the accented words are shown normally.

  União Estável

When I connect it to the 9.4 restored database (created using the same encoding, tablespace, collate and ctype) the accented words are shown weird.

  UniÃ£o EstÃ¡vel

The web application HTML pages have charset defined to iso-8559-1.

Commands used for pg_dump and restore

pg_dump --file=example --format=custom --host=exampleIP --username=exampleusername --lock-wait-timeout=5000 exampledatabase

pg_restore --dbname=exampledatabase --format=custom --host="awsRdsDns" --username=exampleusername --verbose exampleFile

I also tried dump with --encoding=LATIN1 but nothing changed

When I select those data through PgAdmin3 in both databases, the accented words are shown normally.

What am I doing wrong?

The HTML charset is set to iso-8859-1. Altering HTML charset didn't change it. The RDS client_encoding is UTF8 because the majority of databases uses UTF8, there's only 6 other databases that uses LATIN1. Is there a way I can change it only for those 6 databases, without affecting the others?

Conclusion

I found out that my problem was my web application CLIENT_ENCODING was UTF8, so my solution was to set it to LATIN1, but because my question was directed to "other problem" I decided to acept that answer.


texts: Why does my query run faster after I force full table scan?

texts:
I have a MERGE statement that took forever to execute the select as the table it is running from (SUB_DATA) is rather big (read 1.4 Tb, 700 million rows). My SELECT looks like this.

SELECT MIN(TRUNC(DATETIME,'DD'))    AS FIRST_ACTIVITY,
       MAX(TRUNC(DATETIME,'DD'))    AS LAST_ACTIVITY,
       NVL(PHONE_NUMBER, '-1')      AS PHONE_NUMBER,
       MAX(NVL(PHONE_ID, '-1')) KEEP (DENSE_RANK LAST ORDER BY DATETIME) AS LAST_PHONE_ID,
       MAX(TYPE_ID) KEEP (DENSE_RANK LAST ORDER BY DATETIME)             AS TYPE_ID
FROM SUB_DATA   AD,
     SUB_PROF   ICP
WHERE DATETIME BETWEEN minDate AND maxDate
AND   PROFILE_ID = ICP.ICP_SEQID
AND   REF_RT_ID in (13, 63)
AND   DURATION > 3
AND   PHONE_NUMBER like '123%'
GROUP BY NVL(PHONE_NUMBER, '-1')

There are two INDEXES on the table, one on PHONE_NUMBER and one on another column not used here.

I struggled to get the query to run faster and one of the more senior guys at work suggested I force a FULL TABLE SCAN. I did this and it worked wonders.

Even though it worked, it goes against the general concept that full table scans are bad.

Could someone please explain why this is the case?


texts: Simple query not returning blank records

texts:
Very simple example here to a very frustrating treasure hunt for the mystery character.

I have a table with a varchar(1) NOT NULL column - everything default - ANSI_PADDING & NULLS are ON for the table as well. We'll call this column CodeNum

A simple query would be

SELECT *
FROM tableA
WHERE CodeNum = ' '

If I run this query against DatabaseA  it returns the all the rows that have a blank or "nothing" in the column as expected.

If I run the same query against DatabaseB for the same table (used compare scripts and even tools to verify they are the same) it returns no results.

I tried looking for hidden carriage returns and other characters by looking for CHAR(1) through CHAR(255). Still no results.

As a test I tried setting the column in DatabaseB equal to '' with a blanket update statement.

UPDATE tableA
SET CodeNum = ''

Now my query returns the results as expected. So to me that says that there is some character in that column but I'd really like to figure out what that character is so I can track down its origin. Any thoughts on what else I could be missing?


texts: How long will a truncate table take on 252M Rows (170GB data)?

texts:
I understand that there are many variables in this and am not looking for an exact number of min:sec :) I've read many resources that state that a TRUNCATE uses far less resources than a DELETE and hence will perform faster. But there are no order of magnitudes mentioned. As a TRUNCATE is a DDL (Data Definition Language) operation and not a DML (Data Manipulation Language) operation and only the system tables are part of the transaction, I'm presuming that it should not take very long.

Anyone with experience on doing this with a table anywhere near this size that can lend some insight? We talking up to a minute, up to an hour, days, weeks?


texts: How to configure multiple hot standbys in Postgres?

texts:
I currently have a postgres server setup with a hot standby.  Is it possible to setup a second standby server?  The purpose of this copy would be to provide real-time reporting off the database, not be a true standby server.

I cannot find any information on configuring a second standby server, so I would like to know:

Is it possible?
If so, how to configure it in recovery.conf
If not, other suggestions for replicating for reporting purposes.


texts: SSIS Row Count: Getting a null variable error where there is clearly a selected variable

texts:
Validation error. Build Files Count VIE [245]: The variable "(null)" specified by VariableName property is not a valid variable. Need a valid variable name to write to.

From what I can tell, this error is thrown when a variable is not assigned to the VariableName property; however, I definitely have a variable assigned, as seen in the image below:

I've deleted the Row Count component and remade it, but the error continues to show up. Here is a snapshot of the Data Flow in question:

I'm not sure if its inclusion in a Conditional Split may be causing this error, but none of the other Row Count components seem to be throwing this error.


texts: Optimize query to filter active IDs for a given day from big table

texts:
I have a PostgreSQL table called users with 4 columns:

user_id   integer,       -- we can have a lot of the same user_id
name      text,          -- name of the user
value     text NOT NULL, -- enumeration values: ok, not ok, on,off
timestamp timestamp default current_timestamp NOT NULL, -- date of insertion
primary_key(user_id,name)

I have to select distinct user_id where timestamp='date x' and value='on' not having value='off' after. I made this query:

select distinct user_id,name
from users
where (timestamp='date x' and value='on')
and user_id not in (select distinct user_id from users  where value='off')

But it is very slow because there are more than 300K users to handle. Is there a way to make the query faster?

user_id name    value    timestamp
1       mike    not ok   2018-03-27 10:24
1       mike    on       2018-03-27 11:22
1       mike    ok       2018-03-27 13:33
1       mike    off      2018-03-27 15:33

2       joe     not ok   2018-03-28 10:24
2       joe     on       2018-03-28 11:22
2       joe     ok       2018-03-28 13:33

The result wanted is:

  user_id    name
    2         joe

because user_id 1 has a value off in the table.


texts: Should I expect better performance out of MySQL?

texts:
I'm new to MySQL and don't know what performance to expect. Does the performance I'm seeing below seem outrageous, or about right?

I have a table with 4M rows, indexed on userId, and I want to count all of the rows with a certain userId, so I run:

SELECT count(userId) FROM Test_schema.Events where userId = 7205

It takes 0.95 seconds to run this query!

To make sure it's using an index I tried explain:

> explain SELECT count(userId) FROM Test_schema.Events where userId = 7205
# id, select_type, table,  type,  possible_keys,     key,               key_len, ref, rows,    Extra
   1, SIMPLE,      Events, index, idx_Events_userId, idx_Events_userId, 138,        , 3972452, Using where; Using index

I believe this shows that the query will indeed use my index on userId. My server has 13Gb of memory, with 8Gb free.

Should it take MySQL a full second to count 54 rows out of 4 million? Is this about the performance I can expect from MySQL, or should I be hiring an expert to get this down? My target would be well under 100ms, which is about what I can expect from distributed data stores for a similar problem.

--- update ---

I used MySQL Workbench to profile the query, and saw:

"Sending data" is a single huge bottleneck. Is this about a connection between my client & server more than query perf time?


texts: How to connect two PostgreSQL databases in two different locations

texts:
There is a project running on PostgreSQL with postgres_fdw. Due to legal reason some user data should be stored only in the remote db on the remote server in the closed country. Now the table that is connected to the remote server becomes unavailable when we lose connection to the remote database and storage.
The main database must be available even if the remote database crashed. It should not crash the main database.
How to connect two different databases from different locations to ensure data integrity (now it implemented on postgres_fdw)?

And the main requirement of our customer, the data should storage at a remote database only.

A materialized view not suitable, because it stores physically data at main server side.

Any ideas?


texts: Optimizing performance of INSERT INTO table from INNER JOIN between two large tables

texts:
I have the following query:

DECLARE @accountCard TABLE (
    numUsu VARCHAR(10)
    ,quantity INT
    )

/*Takes less than a minute*/
INSERT INTO @accountCard
SELECT a.numUsu
    ,count(*)
FROM tableB a
INNER JOIN tableA b ON b.numUsu = a.numUsu
GROUP BY a.numUsu

/*This one takes the time*/
INSERT INTO finalTable
SELECT tarjeta = 'FIXED'
    ,fieldA = c.fieldA
    ,fieldB = c.fieldB
    ,fieldC = convert(BIGINT, b.numUsu)
    ,fieldD = ''
    ,fieldE = ''
    ,fieldF = rtrim(fieldF)
    ,fieldG = rtrim(fieldG)
    ,fieldH
    ,fieldI
FROM @accountCard a
INNER JOIN tableB b ON a.numUsu = b.numUsu
INNER JOIN tableA c ON b.numUsu = c.numUsu
WHERE quantity = 1

tableB has 2.7 million registers, and tableA has 1.7 million. Both tables have non-clustered, non-unique indexes for the column with which I do the join. Currently the second INSERT is taking too long: we have left it running for over 2 days and it was still not finished.

I ran the Estimated Execution Plan feature in SQL Server and it returned that the most expensive process was Searching for Keys (Clustered) in tablaA, but using the primary key, which isn't numUsu.

What can I do to speed it up?

Update 1:

select session_id,
    status,
    command,
    blocking_session_id,
    wait_type,
    wait_time,
    last_wait_type,
    wait_resource
from sys.dm_exec_requests r
where r.session_id >= 50
and r.session_id <> @@spid;

There's only one session running and the result is:

+------------+-----------+---------+---------------------+----------------+-----------+----------------+---------------+
| session_id |  status   | command | blocking_session_id |   wait_type    | wait_time | last_wait_type | wait_resource |
+------------+-----------+---------+---------------------+----------------+-----------+----------------+---------------+
|         59 | suspended | INSERT  |                   0 | PAGEIOLATCH_SH |         0 | PAGEIOLATCH_SH | 5:1:2431256   |
+------------+-----------+---------+---------------------+----------------+-----------+----------------+---------------+


texts: ERROR unknown variable > 'keyring_okv_conf_dir=/usr/local/mysql/mysql-keyring-okv'

texts:
Env: Ubuntu 18.04.5 LTS + MySQL 5.7.32
I have a KMIP server working and tested. I am now trying to configuring Keyring to enable symmetric key encryption on my database
Per official docs, I added the following lines to /etc/mysql/my.cnf
[mysqld]
early-plugin-load=keyring_file.so
keyring_okv_conf_dir=/usr/local/mysql/mysql-keyring-okv

restarting mysql:
uadmin@Linux01:/var/keyring$ sudo service mysql restart
[sudo] password for uadmin:
Job for mysql.service failed because the control process exited with error

code.
See "systemctl status mysql.service" and "journalctl -xe" for details.
> -- Unit mysql.service has finished shutting down. Nov 18 08:53:12 Linux01 systemd[1]: Starting MySQL Community Server...
> -- Subject: Unit mysql.service has begun start-up
> -- Defined-By: systemd
> -- Support: http://www.ubuntu.com/support
> --
> -- Unit mysql.service has begun starting up. Nov 18 08:53:12 Linux01 mysql-systemd-start[117506]: ERROR: Unable to start MySQL server: Nov
> 18 08:53:12 Linux01 mysql-systemd-start[117506]:
**> 2020-11-18T13:53:12.240218Z 0 [ERROR] unknown variable
> 'keyring_okv_conf_dir=/usr/local/mysql/mysql-keyring-okv' Nov 18**
> 08:53:12 Linux01 mysql-systemd-start[117506]:
> 2020-11-18T13:53:12.242657Z 0 [ERROR] Aborting Nov 18 08:53:12 Linux01
> mysql-systemd-start[117506]: Please take a look at
> https://wiki.debian.org/Teams/MySQL/FAQ for tips on fixing common
> upgrade issues. Nov 18 08:53:12 Linux01 mysql-systemd-start[117506]:
> Once the problem is resolved, restart the service. Nov 18 08:53:12
> Linux01 systemd[1]: mysql.service: Control process exited, code=exited
> status=1 Nov 18 08:53:12 Linux01 systemd[1]: mysql.service: Failed
> with result 'exit-code'. Nov 18 08:53:12 Linux01 systemd[1]: Failed to
> start MySQL Community Server.


texts:
I have tried to make this work in multiple forms but none have been successful. I need to read an int from my table to order my data as desired. I have previously gotten this to work as multiple queries but Im hoping to do it all in one now.
    SELECT * FROM (
        (SELECT * FROM table_a WHERE int_a <= *myint* ORDER BY int_a DESC)
        UNION
        (SELECT * FROM table_a WHERE int_a >= *myint* ORDER BY int_a DESC)
    ) uniontable

MySQL 5.7


texts: Msg 8985, Level 16, State 1,Could not locate file 'MyTal_Log'

texts:
Query:

DBCC SQLPERF (Logspace)
USE master;
GO
ALTER DATABASE Tal
 SET RECOVERY FULL;
GO
BACKUP DATABASE Tal
TO DISK = 'D:\SQLServerBackups\MyTalLog.bak'
 WITH FORMAT;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE TAL
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE ('MyTal_Log', 1,TRUNCATEONLY);
GO
-- Reset the database recovery model.
ALTER DATABASE TAL
SET RECOVERY FULL;
GO

OUTPUT:

  (7 rows affected) DBCC execution completed. If DBCC printed error
  messages, contact your system administrator. Processed 440 pages for
  database 'Tal', file 'Tal' on file 1. Processed 2 pages for database
  'Tal', file 'Tal_log' on file 1. BACKUP DATABASE successfully
  processed 442 pages in 0.434 seconds (7.939 MB/sec). Msg 8985, Level
  16, State 1, Line 16 Could not locate file 'MyTal_Log' for database
  'master' in sys.database_files. The file either does not exist, or was
  dropped.


texts: SQL Server 2016 - Disparity Between View Definition and View Design

texts:
I've run into an interesting issue with some databases I've inherited, and could use some help unpacking it all.

The Issue

After adding a column to a table, views selecting * from that table are now breaking in strange ways. The best way for me to illustrate is to repro it, so here goes.

Repro

Here's my table definitions:

CREATE TABLE TestTable1 (
    col1 INT,
    col2 varchar(12)
)

CREATE TABLE TestTable2 (
    col3 INT,
    col4 varchar(12)
)

And some test data:

INSERT INTO TestTable1 VALUES
  (1,'abc')
, (2,'def')
, (3,'ghi')
, (4,'jkl')

INSERT INTO TestTable2 VALUES
  (1,'ABC')
, (2,'DEF')
, (3,'GHI')
, (4,'JKL')

Now the View definition (and yes I know views should have an explicit column set. Whoever wrote this one did not):

CREATE VIEW TestView AS

SELECT
    tt1.*
    , tt2.col4
FROM TestTable1 AS tt1
INNER JOIN TestTable2 AS tt2
ON tt1.col1 = tt2.col3
GO

As you'd expect, this is the result when I SELECT * FROM TestView:

col1        col2         col4
----------- ------------ ------------
1           abc          ABC
2           def          DEF
3           ghi          GHI
4           jkl          JKL

Super sensible. Here's where it runs off the rails - I add a column to TestTable1:

ALTER TABLE TestTable1
ADD col5 varchar(8)

And having done so, here's what the view now returns:

col1        col2         col4
----------- ------------ --------
1           abc          NULL
2           def          NULL
3           ghi          NULL
4           jkl          NULL

Because of course it does. However, if I run the same query used to define the view, I get:

col1        col2         col5     col4
----------- ------------ -------- ------------
1           abc          NULL     ABC
2           def          NULL     DEF
3           ghi          NULL     GHI
4           jkl          NULL     JKL

Which is exactly what I'd expect. So what's happened? I've tried every method I can find to query the view definition to see what's changed:

SELECT VIEW_DEFINITION FROM information_schema.views WHERE TABLE_NAME = 'TestView'

EXEC sp_helptext 'TestView'

SELECT definition
FROM sys.objects AS o
JOIN sys.sql_modules m ON m.object_id = o.object_id
WHERE o.object_id = object_id( 'dbo.TestView')
  and o.type      = 'V'

And each one returns the definition exactly as it was originally defined. It's not until I use the SQL Server Management Studio Design tool that I see something different:

SELECT        tt1.col1, tt1.col2, tt1.col5 AS col4, tt2.col4
FROM            dbo.TestTable1 AS tt1 INNER JOIN
                         dbo.TestTable2 AS tt2 ON tt1.col1 = tt2.col3

It's the work of a lunatic. It's easily fixed by updating the view to use explicit columns rather than the wildcard *. However, what I'm seeing is that this database and table are referenced in myriad views across multiple databases.

The Question

So my question is thus - where is the metadata/definition for this nonsensical version of the view, and why does it trump what is supposedly the unambiguous definition of the view?


texts: PostgreSQL seems to create inefficient plans in simple conditional joins

texts:
Consider these two queries:
SELECT
    t1.id, *
FROM
    t1
INNER JOIN
    t2 ON t1.id = t2.id
    where t1.id > -9223372036513411363;

And:
SELECT
    t1.id, *
FROM
    t1
INNER JOIN
    t2 ON t1.id = t2.id
    where t1.id > -9223372036513411363 and t2.id > -9223372036513411363;

Note: The -9223372036513411363 is not the minimum value in the tables and the condition reduces the result (from the total number of rows, 350 million) to 17 million.
Personally, I expect PostgreSQL to come up with the same plan for both queries, because having t1.id = t2.id automatically implies the second condition. But unfortunately, PostgreSQL is creating two different plans with the plan for the second one being much better:

First query: http://explain.depesz.com/s/uauk
Second query: link: http://explain.depesz.com/s/uQd
EXPLAIN ANALYZE for the second query: http://explain.depesz.com/s/Snkx
(Second query finishes in 215 seconds, while the first one didn't finish after 1000 seconds until I terminated it).

I would highly prefer the first query, since I want to create a view from the join and put the where condition on queries on the view, where I see a single id column (I join using USING so a single id column is visible in view). Also, I will join more than two tables and I would prefer not to add such condition for each join.
Is there any reason for this behavior? Or is it a bug? Are there any workarounds?

Replacing ON t1.id = t2.id with USING (id) makes no difference in both queries.
This is PostgreSQL 9.3
The actual number of returned rows is 17,658,189
Analyze has been run on the tables. However, statistics related settings of PostgreSQL are its default values.
Observation: Explain for query 1 has a good estimate for the final result, but uses a poor plan for querying t2. For 2nd query, estimates of the number of rows from t1 & t2 are good, but estimate for the final merge is about half the number of actual rows.
The id column is primary key in both tables. Tables have around 350,000,000 rows. t1 is around 20GiB & t2 is 14GiB.
Replacing INNER JOIN with LEFT OUTER JOIN produces similar results
Selecting less rows (by increasing the minimum ID value in where condition) doesn't make any differences, until the number of rows become too low in which case it uses a totally different plan.

What I'm trying to achieve
I have a DB with a lot of rows, and new data is being inserted to it continuously. We want to generate different reports for this data, which includes different kinds of queries like: searching for different data, sorting by each column, aggregation queries and so on.
In current design, we have no UPDATE operations. Currently, I'm experimenting with a highly normalized design (based on ideas promoted by Anchor modeling and/or 6NF). Such design would use JOINs and VIEWs extensively to make working with DB pleasant, and so needs a database to be able to do these efficiently.
As far as I can tell (based on problems like this), PostgreSQL doesn't seem to be a good fit to this design (with around 11 tables and a number of views) and seems to almost always perform worse than a less normalized design with one or two tables and no views. I was hoping that this problem in planning JOIN queries is my fault, but it doesn't seem so yet. With this problem, it seems that I should forget using VIEWS and use verbose queries with lots of repeated conditions, or forget using either PostgreSQL or this design.
Tables
The actual number of columns is a bit more, but they are not in any relation with other tables, and so should be irrelevant to this discussion:
CREATE TABLE t1
(
  id bigint NOT NULL DEFAULT nextval('ids_seq'::regclass),
  total integer NOT NULL,
  price integer NOT NULL,
  CONSTRAINT pk_t1 PRIMARY KEY (id)
)

CREATE TABLE t2
(
  id bigint NOT NULL,
  category smallint NOT NULL,
  CONSTRAINT pk_t2 PRIMARY KEY (id),
  CONSTRAINT fk_id FOREIGN KEY (id)
      REFERENCES t1 (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)


texts: What is running on my server every night?

texts:
My data disk queue is high every night at about 1:00 AM UTC. I did sql profiler trace and found that there are many queries running at the same time similar to these:

Can someone tell me what is going on there and how to stop or to control this?
Thanks in advance.

Version: SQL-Server 2014

EDIT:
I turned off the network, same issue. So, there is something inside SQL server.


texts: Query Governor Cost Limit relationship with Time

texts:
Based on Technet, Query Governor Cost Limit refers to the estimated elapsed time, in seconds. This meets this excerpt from the Microsoft SQL Server 2008 Administrator's Pocket Consultant book.

Walking to the book Microsoft SQL Server 2012 Unleashed, pages 30 and 31, the cost limit is proportional to the estimated time, and one should apply a factor (K), to take into account the system's specifics, so it would be K x time(s).

Now, if I take this (somehow older) article, it feels like establishing a relationship between cost limit and time is not doable.

Can I say that in the real world, despite some more rare exceptions, the 2nd approach could be taken as a rule of thumb?


texts: Access points of geometric path data in PostgreSQL 8.1

texts:
Summary:

I would like to perform basic operations on the points of geometric path data contained in a PostgreSQL 8.1 server which I do not have administrative access to. In particular I would like to be able to iterate over these points using SQL and to access paths as arrays (or something functionally equivalent).

Background:

I'm a novice database user who is working with an unsupported legacy application which is using PostgreSQL 8.1 as a back-end database. I would like to be able to manually run some sanity checks on the data in the database in pure SQL (or PL/pgSQL), i.e. I'd like to be able to avoid using an external programming language, which is what I'm currently doing.

Several tables in the database include columns containing geometric data. Here is a description of one such table:

psql -U "${appuser}" -d "${appdatabase}" -c '\d+ path_table'

            Table "public.path_table"
 Column |  Type   | Modifiers | Description
--------+---------+-----------+-------------
 id     | integer | not null  |
 path   | path    | not null  |
Indexes:
    "path_table_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "path_table_id_check" CHECK (id > 0)
Has OIDs: no

I consulted the section on Geometric Functions and Operators from the PostgreSQL 8.1 Manual, but to no avail.

Since this might be relevant, I'll mention that I believe that PostGIS 1.3.6 is installed, i.e.:

$ psql -U "${appuser}" -d "${appdatabase}" -c 'SELECT PostGIS_full_version();'

                                  postgis_full_version
----------------------------------------------------------------------------------------
 POSTGIS="1.3.6" GEOS="3.1.1-CAPI-1.6.0" PROJ="Rel. 4.7.1, 23 September 2009" USE_STATS
(1 row)

My understanding is that I can get a list of all installed functions by using the psql \df meta-command, i.e.

$ psql -U "${appuser}" -d "${appdatabase}" -c '\df'

My understanding is also that all of the available PostGIS commands should be prefixed by the string 'ST_' (denoting spatial type), so that the following should produce a list of all the available PostGIS commands:

$ psql -U "${appuser}" -d "${appdatabase}" -Alt -c '\df' | cut -d'|' -f2 | grep -i '^st_'

Inspecting this list didn't produce any likely candidates besides the ST_Dump function, which doesn't appear to apply to a path type:

SELECT st_dump(path_column) FROM table;

ERROR:  function st_dump(path) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.

Apparently the st_dump argument needs to be of type geometry, so I tried following the hint and casting the path to a geometry type:

SELECT st_dump(path_column::geometry) FROM table;

ERROR:  cannot cast type path to geometry

I also consulted the Geometry Accessors of the PostGIS 1.3.6 Manual but apparently I don't know enough to be able to cull a solution from the documentation.


texts:
We all know that a simple statement such as:

SELECT * FROM stuff;

should not produce an ordered result. However, when I try to demonstrate this, it always comes out in primary key order.

Moreover a statement such as:

SELECT thing,whatever FROM stuff
GROUP BY thing,whatever;

always appears to sort thing by the last field in the GROUP BY clause, which is not at all helpful.

The question is, under what circumstances does SQL SERVER order the result even if unasked, and what can I do to stop this?

I am trying to demonstrate to my students that order is indeterminate unless specified, but this is not helping my case.

I admit that I am working with a small set of sample data.

Thanks