texts: Select next row only if different from current row

texts:
I have a table called times that looks like so:

id  start_date  breakfast   lunch       snack       dinner
1   2016-10-10  06:30:00    01:30:00    05:00:00    08:00:00
2   2016-10-17  06:30:00    01:30:00    05:00:00    08:00:00
3   2016-10-24  06:30:00    01:30:00    04:45:00    08:00:00
4   2016-10-31  06:30:00    01:30:00    04:45:00    08:00:00
5   2016-11-06  06:00:00    01:00:00    03:30:00    08:00:00
6   2016-11-14  06:10:00    01:00:00    03:30:00    08:00:00
7   2016-11-21  06:20:00    01:00:00    03:30:00    08:00:00
8   2016-11-28  06:25:00    01:00:00    03:30:00    08:00:00

This represents 4 times during the day. So starting on 2016-10-10, the times are 6:30am, 1:30pm, 5pm, 8pm; and starting on 2016-10-24, the snack time changes to 4:45 and all other times remain the same.

Some of the rows are duplicates, with the exception of the start_date, which represents the beginning of the week in this context.

I want to show when the next time change is, ie, select the next row when the time for breakfast, lunch, snack or dinner are different than today's.

I tried using group by like so, but it returns the row with ID 2, when I want it to return the row with ID 3:

SELECT * FROM times
WHERE start_date > '2016-10-16'
order by start_date asc
group by breakfast,lunch,snack,dinner
limit 1

One more clarification, start_date is a DATE type column, the other 4 are TIME type columns. Id is just an int.


texts: Does the ANSI SQL Standard allow filters or parameters in JOIN conditions?

texts:
Does the ANSI Standard find the following SQL sinppet syntactically correct?  I am interested specifically in the last line:

SELECT name, dept_name
FROM employee JOIN department
     ON employee.dept_id = department.dept_id
     JOIN payroll ON payroll_type = 1;


texts:
I have 2 queries which when run at the same time are causing a deadlock.

Query 1 - update a column which is included in an index (index1):

update table1 set column1 = value1 where id = @Id

Takes X-Lock on table1 then attempts an X-Lock on index1.

Query 2:

select columnx, columny, etc from table1 where {some condition}

Takes an S-Lock on index1 then attempts an S-Lock on table1.

Is there a way to prevent the deadlock while maintaining the same queries? For example can I somehow take an X-Lock on the index in the update transaction before the update to ensure the table and index access are in the same order - which should prevent the deadlock?

Isolation level is Read Committed. Row and page locks are enabled for the indexes. It's possible that the same record is participating in both queries - I can't tell from the deadlock graph as it doesn't show the parameters.

Deadlock graph


texts:
I was trying (by a mistake) to update bit typed field with non-bit value. It took me some time to realize the cause for not getting this field updated (non-bit value). I was supprised that no native arithmetic overflow exception was raised by the SQL Server query engine.

Does anyone know why is works that way (no exception) for "bit overflow" updates?

On the side I immediately tested updating TINYINT with out-of-range value and it raised an (expected) exception.

I used Microsoft SQL Server 2014 (SP1-CU7) (KB3162659) - 12.0.4459.0 (X64)


texts: Service Broker Error - The conversation handle is not found

texts:
A new third party application using Service Broker on SQL Server 2017 is causing the error log to report numerous failed query delivery notifications due to conversation handle not found.
I've ensured the Service Broker is enabled from sys.service_queues and have restarted the Service Broker successfully, however the errors continue.
These errors continue every two hours, making me wonder if perhaps it's an App process?  I've read another option is to set the database to Trustworthy, which I haven't tried at this point.
The required components of Service Broker are present, which I've checked via system tables such as Message Types, Service Contracts, Service Queues, Services, and Endpoints. All queues have a status of 'Not Available' (by querying sys.dm_broker_queue_monitors, for example).
No messages exist in the sys.transmission_queue system table.
Querying the sys.service_queues system table and all rows have:
is_activation_enabled = 0,
is_receive_enabled = 1,
is_enqueue_enabled = 1,
is_poison_message_handling_enabled = 1

Could this be due to poison messages?
I know the basics when it comes to Service Broker, but this one has me stumped.
Here are a few questions I have:

Any other system tables or queries I should use to further diagnose this issue?

It seems the messages are stuck and can't be delivered. Any ideas why this is occurring or what is causing this error?

Any possible solutions to resolve these errors?

SQL Server Logs:


texts: Sql Server 2008 to R2 Restore Backup from R2

texts: I need to restore SQL Server 2008 R2 Backup to SQL Server 2008, but as you know, it's not possible. So, I decided to upgrade my SQL 2008 to R2 but I don't know which instance to select  in Instance to upgrade that contains ("MSSQLSERVER","SQLEXPRESS","<<Upgrade shared features only>>"


texts:
I'm using MySQL 5.5 with a table in InnoDB running:

  deleteme tinyint NOT NULL DEFAULT 0;`

UPDATE `import` SET  `deleteme` = 1; -- Set the delete field

LOAD DATA LOW_PRIORITY LOCAL INFILE "import.csv"
REPLACE INTO TABLE `import`  FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY """" LINES TERMINATED BY "\n"
IGNORE 1 LINES (`id`, `name`, `m_id`, `sku`)
SET  `deleteme` = 0;

DELETE FROM `import` WHERE  `deleteme` = 1;

Almost all of the tables have over 200,000 rows in them, and it's taking to long to update the tables.  Is there a better, faster way to do this?

This is on a VPS with 2gb ram w/ 4 Cores and it's not taxing anything doing these updates, it's just SLOW.

http://sqlfiddle.com/#!2/66559/4

Demo of working code.


texts:
Is there any easy way to manage users? Let's say, I need another user called user2.

This user needs a database called user2 aswell - With only privileges to THIS database.

It's easy to do, just by adding those one at a time etc - But when i reach 100 users, it's no so easy. Is there any way to manage these easily? I can't seem to find anything just by googling.


texts: Restricted user on my local SQL Server database?

texts:
I started working at a new job and when I opened up SSMS and tried connecting to my local DB (by connecting to ".") I got a cannot connect message.

I was finally able to connect to my local DB by connecting to ".\SQLEXPRESS"

The problem I am having now is that I am not able to create any databases, tables etc on my local DB. I get the CREATE DATABASE permission denied in database 'master'

I also tried running the Activity monitor as a test and received the error message "The user does not have permission to perform this action. (Microsoft SQL Server, Error: 297).

I am wondering if this has anything to do with the fact that I am not an "Administrator" on my computer or if it is just the way SQL Server was installed.

When I go into Security > Logins I see BUILTIN\Users and sa (which has a little red arrow pointing down next to it)

When I double click on the sa Login it shows that there is a pasdword for the SA account.

Does this mean that whoever installed SQL Server on my machine created an sa user during installation with a password which is why I have no permissions??


texts: Is there a non-blocking operation to back up MyISAM tables using Percona XtraBackup?

texts:
I've been reading through the website of Percona XtraBackup and it claims that:

  Percona XtraBackup can back up the following storage engines by briefly pausing writes at the end of the backup: MyISAM, Merge, Archive ...

Is there a flag or option I've missed with the xtrabackup binary to make this functionality happen?

More info...

I installed XtraBackup 2.3.4 onto a CentOS 7.2 server, as well as MySQL 5.6.30. I created a new MyISAM table with approximately 32 million rows and started the backup process with the following:

xtrabackup --user=user --password=password --no-timestamp --compress --backup --target-dir /tmp/my_backup

While the backup was underway I attempted to write a new row to the table, fully expecting it to be inserted into the table as this is what the website implies will happen. However, it was not. During the entire backup process the MyISAM table is locked.

I understand why the table is locked, hence why I was quite excited to read that XtraBackup could perform a backup without any significant write pauses until the end. I just don't understand how to make this functionality happen.


texts: Please tell me my data isn't gone... lie to me even

texts:
In a moment of ignorance I setup my webserver to auto backup all data to another machine.

not a problem except I just mearly cp -R /var/lib/mysql so all I ended up getting was hundreds and hundreds of backups of DB.opt and table.frm files

From my understanding I didn't pull any data in these files. Is this true?

If so are these any good to me at all?


texts: Grouping and Transposing Rows to Columns in PostgreSQL

texts:
I have the follwing situation: I have profiles and tasks. One profile can be used once for each task. I track usage in profiles_used table. I need to build a report that shows how many profiles of each group is available for each task.

Here are my tables:

select * from profiles;

 id |  name   | group
----+---------+---------
  1 |  name_1 | 1
  2 |  name_2 | 2
  3 |  name_3 | 3
  4 |  name_4 | 3
  5 |  name_5 | 3

select * from tasks;

 id | name
----+--------
  1 | task_1
  2 | task_2
  3 | task_3

select * from profiles_used;

 id | task.id | profile.id
----+---------+------------
  0 | 1       | 1
  1 | 1       | 2
  2 | 2       | 1
  3 | 3       | 4
  3 | 3       | 5

Magic: Run query that finds out how many not used profiles in each group available for each task.

Result:

             profiles.group
 task.id |  1  |  2  |  3
---------+-----+-----+-----
       1 |  0  |  0  |  3
       2 |  0  |  1  |  3
       3 |  1  |  1  |  1

Please help me with right direction to start.


texts: How to set full text catalog location SQL Server 2008/2012

texts:
I want to upgrade my SQL Server 2005 database to SQL Server 2012.  In SQL Server 2005 you can set the location of the full text catalog when you create it.  But in SQL Server 2012 (and in SQL Server 2008 from what I can deduce from online chatter) you cannot specify the location.

In SQL Server 2005 it was important to be able to set this location because, for performance reasons, we put the catalogs on separate disk.  I understand that the full text engine has changed starting in SQL Server 2008 but I can't find information on where the catalogs get physically created and if putting them on a separate disk is still a best practice.

How can I set the catalog location in SQL Server 2008/2012?  Or is the best practice of storing them on separate disks no longer necessary to follow?


texts: How to split data from column to multiple columns

texts:
I am just starting DBA and curious about how can I split entire column with data like: "1-2/456/£25.00-26.00/56/10+". My delimiter is "/" and I need to get data in 5 columns accordingly. So data to be displayed as: Col01: 1-2; Col02: 456; Col03:£25.00-26.00, Col04:56; Col05: 10+. I tried Jeff Moden, but it splits data to rows, not to columns. I am struggling to link function to use data from column, not just values.

SELECT * FROM [dbo].[DelimitedSplit8K]('Masha/Sasha/Nadia/Loki','/')

So I actually need column instead of 'Masha/Sasha/Nadia/Loki' and convert to columns, not rows.


texts: Question About Large InnoDB Table with Deleted Rows

texts:
Our server has a DB that's over 600 GB - we are deleting older rows that are non-essential to contain the growth. We can't optimize the table due to possible downtown on the table but we are interested to know if queries will be faster with smaller data in the table despite the fact that the table size will not shrink at all.

So basically if the size remains the same, but we are able to delete a significant amount of rows, will our more intensive queries be any faster?

thanks!


texts:
We have so many user ID locks from the front-end users. We need to provide a shell script to the user which they can run on OS level and unlock the ID themselves when the DBA is not present.

The sys password needs to be decrypted during runtime for script run & encrypted after script executes successfully. (Ideally they should not see the password)

We have multiple databases so the parameter has to be passed in the below manner only.

Example syntax : ./tmp/unlock_user.sh user instance_name

This is too much to ask. I know. If anyone can help, It will be great.

OS : Generic Linux
Database : 11gR2


texts: Does PostgreSQL do the caching like applications do?

texts:
Applications usually cache data on an implementation of hash data structures to speed up the query, typically Memcached or Redis. Does relational databases also do the same things internally?

For example, if I do the select * from account where id = 1 where id isn't indexed or b-tree indexed, does the engine caches the result to the cache using the key like account_id_1 so that the subsequent same queries could return the result from the cache until it's invalidated?

I'm asking this because I saw the following from the FoundationDB's documentation, and wondered whether that feature is something new for FoundationDB or relational databases have been doing this also although it's not distributed:

  Distributed Caching

  When your database scales out, you don’t need a separate distributed
  caching layer: you already have one. FoundationDB uses the aggregate
  memory of the entire cluster to cache commonly accessed data. Unlike a
  caching tier such as memcached, FoundationDB’s cache is completely
  synchronized to the database and provides all ACID guarantees.


texts: MySQL 5.6 OS X 10.8: how tell if my.cnf read?

texts:
With mysqld running, I expect to see that /etc/my.cnf has been used by at least one of the commands

ps aux | grep mysql | grep -v grep

mysqladmin variables | grep defaults-file

showing the value of --defaults-file. But I see no such item. Why not?

I think /etc/my.cnf is being read, since if I put an erroneous entry in its [mysqld] section, e.g., error = true then when I try to start mysqld it aborts with an ERROR message.

I'm using the installation by running mysql-5.6.10-osx10.7-x86_64.pkg from mysql-5.6.10-osx10.7-x86_64.dmg.


texts: Why does this RX-X lock not appear in Extended Events?

texts:
The Problem
I have a pair of queries that, under serializable isolation, cause an RX-X lock. However, when I use Extended Events to watch lock acquisition, the RX-X lock acquisition never appears, it is only released. Where does it come from?
The Repro
Here's my table:
CREATE TABLE dbo.LockTest (
ID int identity,
Junk char(4)
)

CREATE CLUSTERED INDEX CX_LockTest --not unique!
ON dbo.LockTest(ID)

--preload some rows
INSERT dbo.LockTest
VALUES ('data'),('data'),('data')

Here's my problem batch:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

INSERT dbo.LockTest
VALUES ('bleh')

SELECT *
FROM dbo.LockTest
WHERE ID = SCOPE_IDENTITY()

--ROLLBACK

I check locks held by this session, and see RX-X:
SELECT resource_type, request_mode, request_status, resource_description
FROM sys.dm_tran_locks
WHERE request_session_id = 72 --change SPID!

But I also have an Extended Event on lock_acquired and lock_released. I filter it on the appropriate associated_object_id...there's no RX-X.

After executing the rollback, I see RX-X (LAST_MODE) released, even though it was never acquired.

What I've Tried

I looked at all locks in Extended Events - no filtering. No RX-X locks acquired.

I also tried Profiler: same results (except of course it gets the name right...no "LAST_MODE").

I ran the XE for lock escalations - it's not there.

There's no XE specifically for conversions, but I was able to confirm that at least the U to X lock conversion is captured by lock_acquired

Also of note is the RI-N that gets acquired but never released. My current hypothesis is that the RX-X is a conversion lock, as described here. There are overlapping key-range locks in my batch that look like they should qualify for conversion, but the RX-X lock isn't in the conversion table.
Where is this lock coming from, and why isn't it picked up by Extended Events?


texts: How do I create "groups of users" and set permissions in SQL Server?

texts:
I am new to SQL Server, and I need to do something like the following:

Create a group of users that will allow them to only SELECT from Table 1, but be able to SELECT, UPDATE, DELETE and INSERT INTO Tables 2 and 3;
Create another group of users that will allow them to SELECT, UPDATE, DELETE and INSERT INTO all three of the aforementioned tables

All three of the tables are in the same database.
May someone please provide some sample code so that I can accomplish something like that? Thanks!


texts:
We ran a delete query on a database with 1.8bn rows. This delete would delete 1.2bn rows.

In hindsight, we would have broken up this query into 100m at a time but we are in a position where it has been running for 24 hours and the log file is at 2Tb which appears to be the maximum size allowed for a log file.

The database is in SIMPLE recovery mode.

Is there any saving this query? Or do we need to just restart the SQL Server and see what happens? Will the database be unusable? Is there anything we can do to kill this off as cleanly as possible?


texts:
I'm deploying a system with memcached and mysql cluster.
The command line is:

memcached -E lib/ndb_engine.so -e "connectstring=maddy:1186;role=dev"

I can't specify the -m argument since I used -E and -e.

My questions are:

Is the maximum memory 64M? If so, how can I change it? Any solution for about 1G-2G memory requirement?
Are there any more stable versions than memcached-1.6.0_beta1.tar.gz?


texts:
Is there a separate strategy for inserting / loading data into a table that is partitioned? We are using TVPs inside a single proc to load multiple tables into a SQL Server DB and I did not change the ingest strategy when we switched to a partitioned table.  Of course the queries were changed, but nothing for the actual ingest.  By strategy, I am simply referring to the actual insert statements. I can't imagine that there is any deviation, but wanted to double check.

I realize that the partition strategy itself can cause contention. For example, if partitioned by date and all of the data being loaded is for the current time period you will be loading into the same partition.

Any suggestions / comments are welcome.  I have searched and ready Brent Ozar's articles, but haven't seen anything that would lead me to believe I need to do anything differently.

Below are examples of our scheme and function that we use to partition the data based on a date field that exists within the table. The structures below allow for us to dynamically update the function and schemes to add more date ranges. As you can see from the function, all current rights will contend with each other because they are all hitting the same partition.  However, it really helps with our queries.

Partition Scheme

CREATE PARTITION SCHEME [Datetime2_Scheme]
AS PARTITION [Datetime2_Function] TO (
    [Partition1]
  , [Partition1]
);

Partition Function

CREATE PARTITION FUNCTION [Datetime2_Function](datetime2(7))
AS RANGE RIGHT FOR VALUES (
    N'2015-10-01T00:00:00.000'
  , N'2015-11-01T00:00:00.000'
);

As always greatly appreciate the feedback from Stack Overflow folks.


texts: Is an X lock on an "application" a bad thing?

texts:
I am digging into sys.dm_tran_locks and have found a single, persistent X lock of resource_type = APPLICATION that looks odd.  The database which on which the lock exists is rarely used.  The resource_description tells me it's db_owner and the naming convention looks to be related to our replication processes.

I would like to know if this is normal behavior and nothing to worry about or if I need to dig deeper to remove the lock.

(We are experiencing a variety of intermittent performance issues on this server and have yet to determine why - I have no reason to think this is related, but I did want to mention it.)

EDIT - I would also like to know what a lock on APPLICATION is - the MSDN descriptions are vague to me...


texts: What is the difference between a foreign key and a primary foreign key?

texts:
I understand that you use foreign keys to relate rows from one table to the other and if this foreign key also becomes a primary key of that table then it is a primary foreign key.

But, for example, if a hotel records the date and time of check-ins for each customer, and each customer has a customer ID, should I include the customer ID as a primary foreign key or just a foreign key? Because at one time there can be multiple check-ins so date and time is not enough as a primary key?


texts: Are PostgreSQL dynamic arrays more wasteful than plain text?

texts:
Today, someone was helping me to make a function in PostgreSQL. I was wanting the function to return in arrays of 100+ geometries per two columns instead of delimited text to make it easier to understand what is being returned. His response was that it was going to use more resources if it was array instead of text and that I should focus on my programming instead of wasting resources.

I don't know enough about PostgreSQL to know if the use of arrays in this case would be concern.

My questions are: do PostgreSQL dynamic arrays use up more memory than text? And if so, is it a significant concern to drop the use of arrays on large queries?


texts: MySQL - Are column names stored in each row in full length?

texts:
MySQL: I'm not finding this in the manual or in google, but do column names get stored in full in each row?

I suppose that this might change depending on compression, but leaving compression aside, suppose I have a normal InnoDB table with a column's name "really_long_name_for_a_column". Will this name (29 bytes) be stored in each row for every row in the table? Or is a shortened version recorded for each row or nothing at all?

Thanks!


texts: How to convert varchar into decimal and its sum

texts:
I have the following query:

SELECT SUM(cast(Amount as decimal(10,2))) as result
FROM tblserviceinvoice

and values of amount (which is varchar) are:

    2.00
  276,00
  528.00
  759.00
  759.00
  233.00
7,956.00
5,328.00

But as I run the said query, only 2569.00 is given as result. How can I fix it?


texts:
We are having a SQL Server R2 Web Edition database, based on Windows Server 2008 R2 with a strong CPU and 32GB RAM DDR3.

Our database works very hard (we run around 600,000 queries in 1 minute) and its CPU is going crazy (25% to 90%).

I was looking after slow queries, found some, fixed them, some other I just canceled to check if it will help, but it didnt, CPU is still very high.

I was wondering what else could it be? I am not a DBA, but I am working with a very experiense one, he create all the indexes and re-configured the DB, but CPU is still very high, and this DBA (with more than 20 year of experiense) is kind of hopeless and dont really know how to find the problem.

I was wondering, could it be caching problem? like that queries are not cached?

Anyone know what can it be? what could we do? how can we investigate it?

Maybe its just the high amount of queries?

I'd love to get every small piece of information you can give!


texts: How can I Use Access to combine tables of information

texts: I have two tables in access with a common ID number in both. One table has mail to info and the other table has account information. The account information can have multiple listings per ID. The mail to info has one listing per ID. I need to combine the two tables to create a new table with 1 row per ID but containing multiple columns with the added account information. How do I do that?


texts: How to design a database for my entity-relationship diagram about Projects, Tasks and Developers

texts:
I'm new to database and my entity-relationship diagram is as follows:

According to that I designed my SQL-DDL database statements as shown below:

CREATE TABLE Developer (
    DeveloperID int NOT NULL AUTO_INCREMENT,
    Name varchar(255),
    Primary key(DeveloperID)
);

CREATE TABLE Manager (
    ManagerID int NOT NULL AUTO_INCREMENT,
    Name varchar(255),
    Primary key(ManagerID)
);

CREATE TABLE Project (
    ProjectID int AUTO_INCREMENT,
    Name varchar(255),
    ManagerID int,
    Primary key(ProjectID),
    CONSTRAINT FK_Manager FOREIGN KEY (ManagerID) REFERENCES Manager(ManagerID)
);

CREATE TABLE Task (
    TaskID int AUTO_INCREMENT,
    DeveloperID int NOT NULL,
    ProjectID int NOT NULL,
    Descriptions varchar(255),
    Primary key(TaskIDTaskID),
    CONSTRAINT FK_Developer FOREIGN KEY (DeveloperID) REFERENCES Developer(DeveloperID),
    CONSTRAINT FK_Project FOREIGN KEY (ManagerID) REFERENCES Project(ProjectID)
);

But I'm not sure if this is correct or not, because I'm not using any other table for works (there are many-to-many relationships). If I create a table for that, I think it's useless for me. Can anyone tell me if this structure is correct or not? If incorrect, please tell me what should I do to improve it.


texts: How to make trigger code execute only for a specific application?

texts:
I was wondering if it is possible to enable a trigger just for my current application and not for any sql executed against the table.

The situation:

There are two applications working on the same database. App1 and App2.

I have a trigger on 'MyTable' which should only be fired when App1 is executing a query, but not when App2 is doing so.


texts: How Do I Use PowerShell to Get a SQL Server Backup Status?

texts: I need a power-shell script to obtain the following information Name of the database , LastBackupDate of the database and this information should be sent via an email converting the results to HTML format :) if we do have chance of getting the script done which will be .ps1 file I need to know how to run as a sql job.


texts:
I am designing a database for an application, which will be accessed by different front end programming languages, such as php, asp.net, android applications and iphone applications.

I am confused, whether is it good to accessing different programming languages directly to a database? and I learned somewhere, I have to create a middle layer between database (Backend) and Programming languages (Frontend).

Is this middle layer indicating Stored Procedure?
If it is not stored procedure what is required to create this middle layer?
If my overall logic is wrong kindly suggest me any other method to solve this doubt.
Is there anything related to middle layer which I need to concentrate while designing?

Thanks in advance.


texts: I have a website, which collects input from the user. When his data cannot be inserted into a database, I simply display "Sorry..." I am hosting my website on LAMP. I am new to MySQL and would like to know how to log all the errors. For example, I want to log/see why exactly user was not able to submit data properly: maybe he violated integrity constraints etc. Is it possible to do?


texts: Efficient way to store geographical routes in MongoDB

texts:
I need to store a taxi route into MongoDB, however I'm not sure what is the best way to do this.

Since the route is only needed for the taxi order, the first idea I had is to store it in the order, this would make the retrieval quite fast, since I won't need to join the order collection with the route collection. As well as that, the route can be stored in a simplified way for example:

{
   order_id: 1,
   ...
   route: [
        [ 1567767312, 58.542454, 110.15454, 45 ], //timestamp, lat, long, speed
        ...
        [ 1567768312, 59.448488, 10.484684, 20 ]

   ]
}

If say the measurement is done every 2 seconds, an average 15 min ride would be 450 points, which is not a lot.

However, I read that MongoDB does not like when documents are very different in sizes and since the route can be different all the time, this might cause issues as the order number grows.

The other obvious approach was to have a separate collection to store each reading a separate document:

{
    timestamp: "2019-09-06T10:49:38+00:00",
    coordinates: [ 58.45464, 128.45465 ],
    order_number: 1
}

With indexing, this should not be much slower in terms of fetching or writing than the method above. However, this will occupy way more space and the collection might grow really fast.

Just as clarifications. The route is only used for displaying it on the map when somebody opens the order, no need for geo queries.


texts:
I want to speed up following piece of code

delete from ssn_sdo
where
           (art_id=@art_id and skl_id=@skl_id and @level=0 )
        or (art_id=@art_id and skl_id=@skl_id and @level=1 and tip=@tip)
        or (art_id=@art_id and skl_id=@skl_id and doc_id = @doc_id)

My server is MS SQL-Server 2005.

art_id,skl_id and doc_id are integers, while tip is varchar(10) type of.

I want to make an index(es) on ssn_sdo table so this delete is going to faster.
What I was considering to do is to make three indexes, each one for every case:

doc_id (ASC), art_id (ASC), skl_id (ASC)
skl_id (ASC), art_id (ASC), tip (ASC)
skl_id (ASC), art_id (ASC),

Or is there better way to make one index which will include all three cases.
I am careful with indexes because I do not want to slow down inserts in this table.


texts: mssql suddenly cannot access physical file

texts:
SQL server 2019 on Centos 7 x86_64. It was running fine for quite long time, then got broken.
I was not doing anything special, neither update or restart or whatever. At some point the database went offline, and in errorlog I see these lines every time when I try to bring db online:

  FCB::Open failed: Could not open file /var/opt/mssql/data/base.mdf for file number 1.  OS error: 5(Access is denied.).
  Unable to open the physical file "/var/opt/mssql/data/base.mdf". Operating system error 5: "5(Access is denied.)".

I tried to su into mssql user. All files and paths were accessible. selinux is disabled on that machine.

I then tried to run sql service as root. Still same problem (root cannot get access to files?)

Tried to make all files 777. Tried to reboot in case someone is holding some lock or whatever. No luck. At this point I think that it is some weird bug in mssql and it just blames OS.

Dropped the base and restored from backup with same name and same file locations. Worked without any warning, and works ever since.

I would appreciate if anyone could share some wisdom as of what could be happening there, because even though the problem was easily solved, I really don't like to deal with haunted servers.


texts:
I installed PostgreSQL because I need to restore a large .backup file I received for upload to another platform (it's 38 million rows which was apparently too large for the person to export any other way). When I try to "restore" in pgAdmin 4 I get the message:

  Please configure the PostgreSQL Binary Path in the Preferences dialog.

Is this just the path to the PostgreSQL folder? I can't find documentation on this on the PostgreSQL site. All I can find is:

  Use the fields in the Binary paths node to specify paths to the PostgreSQL binary utilities and EnterpriseDB Postgres Advanced Server binary utilities.

Is there an easier way to restore the database other than through pgAdmin?


texts: is there a way to add Session ID and timestamp to your first responders scripts? In particular the sp_BlitzLock. I am trying to find out when and by who some adhoc scripts are run that cause blocking on old SQL Server versions (2008R2/ 2012) .


texts: Migrating MySQL database structure with foreign keys fails

texts:
I am trying to set up a new testing server with a somewhat complex database structure by dumping the old database and importing it. But even though I use SET FOREIGN_KEY_CHECKS=0;, the import fails with Can't create table 'DatabaseX.TableY' (errno: 150).

The referenced table probably doesn't exist yet, so I understand why it complains. But I just can't figure out how to reorder the dumpfile in such a way that the import works, or how to make MySQL ignore missing tables etc during import.


texts: How to obtain SIDs of Sql Server installed NT Service Accounts

texts:
I am using WMI to obtain members of server local groups, as well as the SIDs of the members. The WQL queries work for members that are local users, local groups, AD domain users and AD domain groups. However when the script is looking at members that happen to be SQL Server related, it cannot find the object in the class it thinks it should look at, and therefore I don't get the SID of the members. For example, here are some queries constructed dynamically for group members that happen to be SQL Server related:

select SID from Win32_SystemAccount where Domain='NT SERVICE' and Name='SQLBrowser'
select SID from Win32_SystemAccount where Domain='NT SERVICE' and Name='MsDtsServer100'
select SID from Win32_SystemAccount where Domain='NT SERVICE' and Name='SQLAgent$SQLEXPRESS'
select SID from Win32_SystemAccount where Domain='NT SERVICE' and Name='MSSQL$EPROVISIONAPP'
select SID from Win32_SystemAccount where Domain='NT SERVICE' and Name='MSOLAP$EPROVISIONAPP'
select SID from Win32_SystemAccount where Domain='NT SERVICE' and Name='ReportServer$EPROVISIONAPP'
select SID from Win32_SystemAccount where Domain='NT SERVICE' and Name='SQLAgent$EPROVISIONAPP'

The queries above do not return any result, even though the PartComponent of such members tells me that the member is from the Win32_SystemAccount class, and the Domain is 'NT Service'.

On the server, if I look at Local Users and Groups -> Groups, and look at the properties of a group, say "SQLServerSqlAgentUser$myservername$SQLEXPRESS", it has one member "NT SERVICE\SQLAgent$SQLEXPRESS (S-1-5-80-592940576-165...". I cannot see the entire value of the SID.

I cannot figure out how Microsoft is pulling the SID of such members, where it is getting them from.

Any ideas?


texts: Filter array attribute in jsonb column type using jsonb_array_elements_text

texts:
My Postgres version: 10.9

Using JsonB Column type which has a below structure:

columnName: favorites
{
"favouriteCity": "Boston",
"favoriteColors": ["Blue", "Red", "Green"],
}

Now, I want to filter all records which have favorite colors as Blue assigned.

I tried the below queries, but it didn't work.

select *
from r_fav
where favorites->>'favoriteColors' in ('Blue', 'Red')

or

jsonb_array_elements_text(favorites->>'favoriteColors' @>'Blue', 'Red')

Any idea how can I filter this?


texts: SQL How to Convert Single Tenant Databases to a Multitenant Database

texts:
We have a legacy application. For some reason, someone decided to create a database for each customer. So we have 500+ databases with the same schema.

I would like to convert into One multi-client database.
The Original customer database does not have CustomerId. New Singular database does.

We are thinking of replacing old databases with Views, so the legacy application insert/update will still work.

Old Customer Database:

CREATE TABLE [CustomerOne].[dbo].[CustomerTransaction]
(
    [CustomerTransactionid] [int] identity(1,1) primary key NOT NULL,
    [QuantityBought] [int] NULL,
)

New Reporting Database has CustomerId:

CREATE TABLE ReportingDB.[dbo].[CustomerTransaction]
(
    [CustomerTransactionid] [int] identity(1,1) primary key NOT NULL,
    [Customerid] [int] NOT NULL,
    [QuantityBought] [int] NULL,
)

Replace Old Database with Views

create view [CustomerOne].[dbo].[CustomerTransaction]
as
select
    [CustomerTransactionid]
    ,1 as [CustomerId]
    ,[quantitybought]
from ReportingDB.[dbo].[CustomerTransaction]
where Customerid = 1
with check option

This Fails:

-- Attempt inserting into View
insert into CustomerOne.dbo.Customertransaction
(Quantitybought)
values (4)

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'customerid', table 'ReportingDB.dbo.customertransaction'; column does not allow nulls. INSERT fails.
The statement has been terminated.

How do I get the final statement to succeeed without a Trigger? Otherwise we will have to utilize some kind trigger to get it working. Maybe there is no way to easily obtain unified database.

We are trying to avoid changing the old application code (insert and update statements).


texts: Should I worry about failed login attempts on SQL Server

texts: I have a couple SQL Server Express Databases which are accessible through a number of asp.net/IIS websites plus Remote Desktop (me only). The web sites use integrated security and I have one user account that I use.  The problem is the logs show almost continuous attempts at failed logins.  These occur every 5-10 seconds or so, from one IP for about 5 minutes, then the IP changes.  The user name being attempted is usually 'sa' or some variation (I don't have an 'sa' user).  I have blocked several hundred of these IPs using Windows Firewall, but they just keep coming.  This results in huge log files and I guess a lot of bandwidth being used.  So my question: Do I have enough security in place that I don't have to really worry about this - or is there something more I should be


texts: Selecting Records From two tables with many to many relationship

texts:
I have two tables user and rights.I want to fetch each user with its assigned right as I attached the screen shot of tables plus result I want.FYI I use PostgreSQL.
Any solution is highly appreciated.
Thanks In advance.


texts:
I am using Automysqlbackup.sh file to take MySql backups on Ubuntu.I have only 1 database.
But when i run this script then it generates several Warnings (plus a error mail) like below

Errors reported during AutoMySQLBackup execution.. Backup failed
Error log below..
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.

i am using the latest Version of AutoMysqlBackup file from link below
http://sourceforge.net/projects/automysqlbackup/

I also tried to solve the error by creating a .properties file for writing the password and reading it in the automysqlbackup script. But no help!

Can Anyone Help me?


texts:
The recommended way to shutdown postgres is to send a signal representing the different shutdown modes (fast, smart, immediate) or using init managers (initd, systemd, etc.) or their equivalent on non-Linux OS. In a containerized environment it's sometimes more convenient to gain access through psql than docker exec and thus more convenient to stop the server through psql. Is there a way to do that (given that the privileges have been granted)?

The solution can be hacky since this will mostly be used to stop a postgres to delete a PostgreSQL data directory in a development environment.


texts: Set default value for all columns with specific name within a database

texts:
I've got a database that includes an int column project_id on many tables, and I want to script setting the default value to a specific value for all columns with that name in the database. Note that the column will already have a default value set, and I want to change it.

I know how to do this for individual tables, but am unsure how to script doing it for all tables containing the column project_id without manually specifying the names of the tables.

** UPDATE **

Based on Aaron's answer, this is what I finally came up with:

DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @new_id INT = 9;

SELECT
    @sql += Query
FROM
  (
    SELECT
      'IF (EXISTS(SELECT 1 FROM sys.default_constraints WHERE name = ''DF_' + OBJECT_NAME(parent_object_id) + '_project_id'' AND type = ''D'')) BEGIN ALTER TABLE [' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + '] END;' + CHAR(13) + CHAR(10) AS Query
    FROM
      sys.default_constraints
    WHERE
      name = 'DF_' + OBJECT_NAME(parent_object_id) + '_project_id'

    UNION ALL

    SELECT
       'ALTER TABLE [' + OBJECT_NAME(object_id) + '] ' +
       'ADD CONSTRAINT [DF_' + OBJECT_NAME(object_id) + '_project_id] ' +
       'DEFAULT ((' + CAST(@new_id AS NVARCHAR) + ')) ' +
       'FOR [project_id];' + CHAR(13) + CHAR(10) AS Query
    FROM
      sys.columns
    WHERE
      name = 'project_id' AND
      system_type_id = 56
  ) AS Queries

PRINT @sql
EXEC sp_executesql @sql;


texts: Error code 1290 and inability to import data on table

texts:
I am running a 5.7.16 version of MySQL on my Windows 8 operating system. I am facing troubles when trying to load datas into a table. I tried with the script

LOAD DATA INFILE 'C:\Users\xxx\Dropbox\LAVORO\xxx\MySqlFiles\xxx.csv' INTO TABLE xxx.vce4 FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r' ;

... but the error code 1290 appears:

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

If I execute the statement:

SHOW VARIABLES LIKE "secure_file_priv";

... the result is 'secure_file_priv', 'C:\Users\xxx\Dropbox\LAVORO\xxx\MySqlFiles\'.

I am unable to solve the error code 1290, I tried to use the Table Data Import Wizard, which seemed to work (Import results: "Table xxx.vce4 has been used") BUT "0 rows imported".

What is wrong with this? I really can't figure out how to make this work.


texts: SQLCLR User-Defined Type (UDT) SCH-M blocking

texts:
I’m hoping someone can provide some guidance on a problem I’m having with schema locking on C# SQLCLR UDT’s.  I’ve been on the Internet for an extended period of time researching my problem without success on a solution.

Also, this "might" be a linked server issue as we see linked server queries accessing the UDT seem to block queries (sch-m running on the remote server).

Back story:

Back in 2006, we began porting some of our DB2 processes down to SQL Server 2005 (we’ve upgraded to SQL 2008 since then, but still need to use our UDT for a lot of current processes). Under DB2, we had made extensive use of the DB2 TIMESTAMP data type -- CCYY-MM-DD.HH.MM.SS.mmmmmm -- which has precision down to the microsecond. Because SQL Server, at that time, did not have DATETIME2, we were forced to create our own C# SQLCLR UDT to emulate the DB2 TIMESTAMP. The UDT was created as VARBINARY with properties that allowed us to reference things like:

            Column.Timestamp        (CCYY-MM-DD.HH.MM.SS.mmmmm)
            Column.Date                     (CCYY-MM-DD)
            Column.Microsecond     (mmmmmm)
            Etc.

When multiple processes access the UDT with just the column name (without referencing a specific property), we don’t seems to have problems.

However, when we have multiple processes accessing the UDT and reference one of the properties (.Timestamp, etc.), we encounter Sch-M locks and other processes also accessing that UDT are forced to wait.

I’ll admit that I have limited knowledge on all things CLR and UDT.

I simply do not understand why we’re getting Schema Modification locks while merely selecting the column and specifying one of the underlying properties.

I’d appreciate any suggestions you might have.

Thanks,


texts: Find queries executed by an user in sql server

texts:
I want to find all the queries executed by an particular user in a database over a period of time.

I learnt about trace and XEvents, but these are not setup yet, Will i be still able to get the queries executed by an particular user?


texts:
In a MySQL master-slave replication architecture, how do you detect if there have been (erroneously, since this should never happen!) direct writes on the slave?

For now, I'm running this command on the slave:

tcpdump tcp port 3306 and not host my_master_IP -A | grep -i -e INSERT -e UPDATE -e DELETE

whose output, if any, means writes on the slave, which is bad.

Is there a better method?


texts:
I have a package named pkg_cd. In it there are 8 procedures:

pkg_cd.PRC_CD_CASEDATAENTRY;
pkg_cd.PRC_CD_DEDUP;
pkg_cd.PRC_CD_CIBIL;
pkg_cd.PRC_CD_TVR;
pkg_cd.PRC_CD_SANCTION;
pkg_cd.PRC_CD_DOWNPAY;
pkg_cd.PRC_CD_DOC_CPA;
pkg_cd.PRC_CD_DOC_QA;
pkg_cd.PRC_CD_PDN_REQ;
pkg_cd.PRC_CD_PDN_AUTHORIZE;

I want to schedule all the 8 procedures to run every minute. How can I do this?


texts:
Various sources (eg Wikipedia, PSOUG) state that Oracle's null does not have a type. Is this true?

What about other RDBMSs?


texts: Best practice for a DBA to login to a database

texts: I've read that a DBA never should login to a database as system user. That makes me wondering... how should a DBA login to a database? Should he/she create a DBA account and use it for login purposes? But I think that would be the same as login as system. Or should he login to each schema as the owner of that schema ?


texts: Error: "A slave with the same server_uuid/server_id as this slave has connected to the master"

texts:
I am testing a node.js client which connects to a mysql DB (mariadb). It works fine on its own, but if I launch this client simultaneously on another host, the first instance gets disconnected with the following error. I have read some posts suggesting to change the server-id in my.cnf, but that didn't help. Besides, why would I need to change the id of the server since both clients are pointing to the same server?

{ Error: UNKNOWN_CODE_PLEASE_REPORT: A slave with the same server_uuid/server_id as this slave has connected to the master; the first event 'mysql-b
in.000024' at 334, the last event read from 'mysql-bin.000024' at 256, the last byte read from 'mysql-bin.000024' at 334.
    at Binlog.Sequence._packetToError (/home/myapp/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Binlog.Sequence.ErrorPacket (/home/myapp/node_modules/mysql/lib/protocol/sequences/Sequence.js:96:17)
    at Protocol._parsePacket (/home/myapp/node_modules/mysql/lib/protocol/Protocol.js:278:23)
    at Parser.write (/home/myapp/node_modules/mysql/lib/protocol/Parser.js:76:12)
    at Protocol.write (/home/myapp/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/home/myappp/node_modules/mysql/lib/Connection.js:91:28)
    at Socket.<anonymous> (/home/myapp/node_modules/mysql/lib/Connection.js:502:10)
    at Socket.emit (events.js:182:13)
    at addChunk (_stream_readable.js:283:12)
    at readableAddChunk (_stream_readable.js:264:11)
    --------------------
    at Protocol._enqueue (/home/myapp/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Immediate._start (/home/myapp/node_modules/@rodrigogs/zongji/index.js:234:31)
    at processImmediate (timers.js:632:19)
  code: 'UNKNOWN_CODE_PLEASE_REPORT',
  errno: 4052,
  sqlMessage:
   "A slave with the same server_uuid/server_id as this slave has connected to the master; the first event 'mysql-bin.000024' at 334, the last event
 read from 'mysql-bin.000024' at 256, the last byte read from 'mysql-bin.000024' at 334.",
  sqlState: 'HY000' }
{ Error: Connection lost: The server closed the connection.
    at Protocol.end (/home/myapp/node_modules/mysql/lib/protocol/Protocol.js:112:13)
    at Socket.<anonymous> (/home/myapp/node_modules/mysql/lib/Connection.js:97:28)
    at Socket.<anonymous> (/home/myapp/node_modules/mysql/lib/Connection.js:502:10)
    at Socket.emit (events.js:187:15)
    at endReadableNT (_stream_readable.js:1094:12)
    at process.internalTickCallback (internal/process/next_tick.js:72:19) fatal: true, code: 'PROTOCOL_CONNECTION_LOST' }


texts:
According to H2 syntax documentation available here I should be able to execute a query like:

ALTER TABLE EMPLOYEE ADD COLUMN SECOND_NAME VARCHAR (124) AFTER FIRST_NAME;

However apparently only this query works:

ALTER TABLE EMPLOYEE ADD COLUMN SECOND_NAME VARCHAR (124);

Is it a mistake in the documentation or am I missing something?
The BEFORE and AFTER parameters would be useful to me.


texts: MariaDB Spider sharding: automatic XA commit?

texts:
I'm planning to use Spider to shard my huge table across multiple backend nodes. What I'd like to do, is inserting on the Spider node(s) on the virtual big table, and Spider have it distribute it for me to the right backend node (shard), while maintaining the isolation level of a simple commit by an application. In a picture from the Spider documentation:

While reading these docs, especially the picture shown above, I assumed that Spider would split up the single commit for me into a two-phase group commit (XA 2PC), sending it to the backend nodes but only fully committing if all shards agree to commit their part.

This assumption is also supported by various other statements elsewhere like in MDEV-14090.

  Internally, Spider implements COMMIT by executing XA PREPARE on each shard, and then executing XA COMMIT on each shard.

I've tried to test this "simple-COMMIT-to-XA2PC-transaction" assumption using the following setup, and guess what I saw...

mariadb-server version 10.4.5+maria~bionic
mariadb-plugin-spider version 1:10.4.5+maria~bionic

CREATE OR REPLACE SERVER srvA
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'spider', PASSWORD 'spider', PORT 3306, HOST '192.168.199.10', DATABASE 'testdb');

CREATE OR REPLACE SERVER srvB
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'spider', PASSWORD 'spider', PORT 3306, HOST '192.168.199.11', DATABASE 'testdb');

create table mytable (
  id INT unsigned NOT NULL,
    some_field TEXT(100) DEFAULT NULL,
    PRIMARY KEY (id),
)
ENGINE=spider
partition by list (mod(id, 2)) (
    partition p00 values in(0)  comment = 'server "srvA", table "mytable_shard0"',
    partition p01 values in(1)  comment = 'server "srvB", table "mytable_shard1"'
);

Everything else on the Spider is left at the default settings.

Looking at Wireshark PCAPs, I see this in chronological packet-id order on the single bridge interface all my test nodes are connected on:

client connects to Spider node, setting connection parameters, disable autocommit, set transaction level SERIALIZABLE.
client doing insert on Spider node:

sending START TRANSACTION.
large 1000-row single-statement insert spreading shards (unknowingly about shards). awaiting response...

Spider communicates with srv1:

Spider connect to srv1, setting connection parameters, set transaction level SERIALIZABLE, etc.
Spider sends the insert statement of rows for srv1 to srv1.
Spider receiving OK from srv1, but in-transaction.

Spider communicates with srv2:

[omitted] same as with srv1.
client receiving OK, but in-transaction.
client sending COMMIT.
Spider sending COMMIT to srv1, srv1 acks commit
Spider sending COMMIT to srv2, srv2 acks commit
client receiving OK from Spider, not in-transaction.

My observations derived from the above events:

I don't see any XA commit statements happening at all from Spider to backend servers. All seem serial regular commits. Huh!?
All seems serially committed in one-phase shard after shard.
Spider seems to communicate to backend servers one-after-another (3 and 4 in the observation list do not cross in time), not in parallel. Why? It seems slower and unnecessary.

(I've double-checked with Wireshark filters mysql.query contains "prepare", mysql.query contains "xa" that no single MySQL query packet contains this.)

How can I obtain XA-2PC as depicted in the image from the documentation shown above? I need to have strict guarantees that ALL shards have seen and committed their part of the original client-side commit, OR rollback as a group.

Also, I'd like to speed up the inserts by having Spider sending the shards their part of the inserts in parallel. Is that possible?


texts: How can I have SQL Server send Job Notification alerts to an API

texts:
I am trying to get the notifications for a job out of my email and into a chat client(HipChat).  So far my googling has been less than successful so I will put the question to the community:  Is there a way to send notifications from SQL Server to an API?

My first thought is that I need to add an additional job step that would be a powershell routine to execute the api call.


texts: High number of Checkpoint pages/sec and memory pressure

texts:
Recently I read a blog post on mssqltips.com about memory bottlenecks on SQL Server. In this article I read following:

  The following performance counters on SQL Server: Buffer Manager object can also indicate memory pressure:

  High number of Checkpoint pages/sec
  High number of Lazy writes/sec
  High number of Page reads/sec
  Low Buffer cache hit ratio
  Low Page Life Expectancy

What caught my attention was that a high number of 'checkpoints pages/sec' can indicate memory pressure.

My understanding was that a checkpoint writes 'dirty' pages to disk. This can be triggered in different ways.

automatic (to maintain recovery interval)
indirect (to maintain target recovery time)
manual
internal

So high number of checkpoints indicates a very busy system (in case of automatic and indirect checkpoints).
Because a checkpoint never removes a page from the buffer cache I don't quite understand how high number of checkpoints/sec can indicate memory pressure. If there is memory pressure I would except to see a high number of 'lazy writes/sec'. The lazy writer removes 'cold pages' from memory, to make place for new pages.

How does a high number of checkpoint pages/sec indicate memory pressure?


texts: Generate and Insert 1 million rows into simple table

texts:
Description:

I try to insert 1 million rows into empty table on MSSQL 2012 Express.
Here my script:

-- set statistics time off
drop table t1
create table t1 (id int, a text, b text)
go

-- #1 - 1,000,000 - 30s -> 45s
with ID(number) as
(
    select 1 as number
    union all
    select number + 1
    from ID
    where number < 1000000 + 1
)
insert into t1
    select number, 'a_' + cast (number as varchar), 'b_' + cast (number/2 as varchar)
    from ID
    option(maxrecursion 0)

-- #2 - 1 million rows => ~140,000 rows = 120s (have to cancel query)
declare @count int
set @count = 0
while @count < 1000000
begin
    set @count = @count + 1
    insert into t1
        values(@count, 'a_' + cast (@count as varchar), 'b_' + cast (@count/2 as varchar))
end

-- #3 - ~1,300,000 rows - 18s -> 20s

with temp as
(
    SELECT  ROW_NUMBER() OVER(ORDER BY a.object_id) as tcount
    from sys.all_columns a,  sys.all_columns b
    where a.object_id = b.object_id
)
insert into t1
    select tcount, 'a_' + cast (tcount as varchar), 'b_' + cast (tcount/2 as varchar)
    from temp
go

declare @count int
set @count = 0
while @count < 3
begin
    with temp as (select max(id) + 1 as max_id from t1)
    insert into t1
        select max_id, 'a_' + cast (max_id as varchar), 'b_' + cast (max_id/2 as varchar)
        from t1, temp
    set @count = @count + 1
end

-- #4 -- 1,000,000 = 3s -> 4s (have to drop t1 first)
with a(k) as
(
select 1 as k
union all
select k + 1 from a where k < 99 + 1
) ,
t2 as (
select row_number() over(order by x.k) as k
from a x , a y , a z
)
select k as id , 'a_' + cast (k as varchar) as a, 'b_' + cast (k/2 as varchar) as b into t1
from t2

Question:

After researching , I found 4 solutions. Are there any better solution (not using copy data from files) ?


texts: Left join multiple tables for millions of rows on multiple columns

texts:
I am using PostgreSQL via pgadmin3 on Ubuntu 16.04 with 7.7GiB Memory and 4x.3GHz processor.  My only aim with this database is to merge multiple datasets (tables) and extract the final table.  I am wondering what slows my join operation down the most: Having many rows, merging on multiple columns, or mergng multiple tables.

I have the following tables:

combinations (121M rows)
----
source | target | year
bigint | bigint | smallint

coauthorships
----
source | target | year
bigint | bigint | smallint

adjacencies
----
source | target | year     | auth_first_order | auth_second_order | com_first_order | com_second_order
bigint | bigint | smallint | double           | double            | double          | double

Eventually there will be more tables.  Each row in each table is uniquely identified by (source,target,year). All tables have non-clustered indices on (source, target, year)

My initial idea was to construct a view and export the view to disk. This is the definition of view master:

SELECT comb.source,
 comb.target,
 comb.year,
 coauth.publications,
 adj.auth_first_order,
 adj.auth_second_order,
 adj.com_first_order,
 adj.com_second_order
FROM combinations comb
 LEFT JOIN coauthorships coauth ON comb.source = coauth.source AND comb.target = coauth.target AND comb.year = coauth.year
 LEFT JOIN adjacencies adj ON comb.source = adj.source AND comb.target = adj.target AND comb.year = adj.year;

Then I proceeded to export the view like so:

psql -U postgres -d department-networks -c "COPY master TO stdout DELIMITER ',' CSV HEADER;" > ~/master.csv

How can I improve the merge design? I have some freedom over the table look like. Given that the tables are not indexed, which method should I use? From pgadmin's help page it looks like both hash and GiST seem appropriate. Should I merge the keys so that I don't merge on multiple columns but just one? Should I join some tables separately so that in the end I only merge two tables? Should I not use postgres at all? I'm grateful for any help in this regard.

EDIT:

Here is the query plan:

"Merge Left Join  (cost=2615359.20..10115676.76 rows=146636992 width=52)"
"  Merge Cond: ((comb.source = adj.source) AND (comb.target = adj.target) AND (comb.year = adj.year))"
"  ->  Merge Left Join  (cost=2048532.06..8406982.78 rows=146636992 width=20)"
"        Merge Cond: ((comb.source = coauth.source) AND (comb.target = coauth.target) AND (comb.year = coauth.year))"
"        ->  Index Only Scan using combinations_source_target_year_idx on combinations comb  (cost=0.57..5107371.45 rows=146636992 width=18)"
"        ->  Materialize  (cost=2047466.59..2106034.64 rows=11713611 width=20)"
"              ->  Sort  (cost=2047466.59..2076750.61 rows=11713611 width=20)"
"                    Sort Key: coauth.source, coauth.target, coauth.year"
"                    ->  Seq Scan on coauthorships coauth  (cost=0.00..191745.11 rows=11713611 width=20)"
"  ->  Materialize  (cost=566827.14..581496.20 rows=2933811 width=50)"
"        ->  Sort  (cost=566827.14..574161.67 rows=2933811 width=50)"
"              Sort Key: adj.source, adj.target, adj.year"
"              ->  Seq Scan on adjacencies adj  (cost=0.00..51115.11 rows=2933811 width=50)"


texts:
Good Afternoon,
We have a pretty big SQL Box  32proc 128gb.

We had a change in our environment, not sure what exactly change,  db/code.  We're investigating that aspect as well.   One of the things we noticed from a baseline stats were the number of transactions.   It went from 10-20K to 60-80K.

SELECT name, transaction_type, COUNT(1)
FROM sys.dm_tran_active_transactions
WHERE name <> 'worktable'
GROUP BY name, transaction_type

Results:
name
implicit_transaction
INSERT
sort_init
UPDATE STATISTICS
UpdateQPStats
user_transaction
LobStorageProviderSession
SELECT
sort_fake_worktable
topn_fake_worktable
workfile
WorkFileGroup_fake_worktable

Some of the names are self explanatory, others not so much.  I have not been able to find any of these on the web.  Does anyone have an explanation or a link where I could find the answers?

Thanks,


texts: Modeling a scenario in which each Music Artist is either a Group or a Solo Performer

texts:
I have to design an entity-relationship diagram (ERD) for a business context that involves the delineation of music artists, as I will detail below.
Scenario description

An Artist has a Name, and must be either a Group or a Solo Performer (but not both).

A Group is made up of one or more Solo Performers and has a Number of Members (which should be calculated from the number of Solo Performers making up the Group).

A Solo Performer may be a Member of many Groups or of no Group and may play one or more Instruments.

Question
How to construct an ERD to represent such scenario? I’m confused with the ‘or’ part of it.


texts: How to find exact string from a sentence

texts:
I'm using SQLITE and I'm trying to find the exact word on a sentence, but I'm not getting just the word I'm getting for instance if I'm looking for Water I'm getting also aWater, wateri24 and that's not what I'm looking for.

I've created an example here on sqliteonline to show you how do I store the sentences, there are in table b.

This is an example of what is happening

NOTE: I'm looking for an answer that uses ignore cases, for instance if I want to find water I want to get wAtER and WatER, and so...

What I'm missing?


texts: MySQL Large History list length & Storage Space

texts:
Currently, my MariaDB database server is running out of space, hence I am trying to delete old records (using multiple threads with DELETE FROM tbl WHERE index > ? and index < ?).

My understanding is that InnoDB will reuse the deleted rows (which are marked for reuse) for future newly inserted records - so effectively, the storage space growth should be slowed down.

However, it seems that my space is still increasing.
Hence, a theory I have is that the purging process is slow - resulting in the deleted rows not being "marked for reuse" quickly.

My current History List Length value is 13836102 and still growing.

I have already configured innodb_purge_threads=32 and innodb_max_purge_lag=100000 - and it feels like that the purging process is still having a hard time to catch up.

Question :

1) When the History List Length gets too big (millions and beyond), does it really meant that I'm not effectively reusing the deleted rows, and consuming more OS disk space for newly inserted records?

2) Is the large History List Length value a concern? (does it eventually consume more-than-needed space, affects IOPS, etc?).


texts: Improve PostgreSQL SELECT performance in a parent table (inheritance)

texts:
I have a PostgreSQL 9.2 database where each account has a schema, like below:

My_Database
 |-> Schemas
    |-> AccountA
    |-> AccountB
    |-> AccountC
    |-> AccountD
    |-> AccountE
           .
           .
           .
    |-> AccountZ
    |-> MasterAccount

All schemas have a table called imovel which has a column called id, the structure is:

CREATE SEQUENCE MasterAccount.imovel_id_seq;

CREATE TABLE MasterAccount.imovel (
    id integer DEFAULT nextval('MasterAccount.imovel_id_seq') NOT NULL PRIMARY KEY,
    title varchar(80) NOT NULL
);

CREATE TABLE AccountA.imovel (
    id integer DEFAULT nextval('MasterAccount.imovel_id_seq') PRIMARY KEY
)
INHERITS (MasterAccount.imovel);

.
.
.

CREATE TABLE AccountZ.imovel (
    id integer DEFAULT nextval('MasterAccount.imovel_id_seq') PRIMARY KEY
)
INHERITS (MasterAccount.imovel);

Today I have 127 accounts and when I run a SELECT in a child table, the response is fast. But when I run the same SELECT in the father table, it is slow. I saw the EXPLAIN and it seems that PostgreSQL uses each child's index, instead of use only the father index.

Is it possible to improve this behavior?

I need to use SELECT in the master table for global searches.

SELECT in the child table

SELECT in the father table


texts: Reference multiple rows of one table from another

texts:
this is a programming challenge I've undertaken to learn some MySQL fundamentals and I can't figure out how to implement a one-to-many relationship in the manner I need. See the below code:

CREATE TABLE Product (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    price FLOAT(7,7) NOT NULL,
    needed_for_bundle INT(2) NOT NULL
)

CREATE TABLE Bundle (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    price FLOAT(7,7)
)

I can't find anywhere how to do this or I'm just not understanding the examples I've found that were similar. If these were python objects, I would have a list of product IDs in the bundle table/object that reference the 3-4 items in the bundle. I know this is a one-to-many relationship but I'm not understanding lexically and theoretically how it would work in MySQL.

Help would be much appreciated. Thank you all.


texts: Connection's MAC address randomly changes on Windows 10, then stops

texts:
Because reasons (tm), we want to identify the client's MAC address on SQL Server side (local network, not about security, enables a very convenient feature in way transparent for applications).
We do that by querying net_address from master.dbo.sysprocesses. All clients are set up to use TCP/IP connections, the address value has always been correct, and all connections from the same computer have always had the same net_address.

In Windows 10 clients, however, every single connection gets its own net_address, and they do not repeat. The only pattern we can see about these addresses is that the last two digits remain the same within approximately 6 seconds. So if another connection gets assigned with EBC1E384F4C2, all subsequent connections' addresses will also end with C2 until the 6 seconds elapse, then the suffix changes for another 6 seconds.

We have tried in various combinations:

running cliconfg.exe and making sure only TCP/IP is enabled, and that there is an alias for the server name that is also set to use TCP/IP
disabling and enabling back connection pooling in the ODBC Data Sources
adding and removing Network Library=DBMSSOCN or Network=DBMSSOCN to and from the connection properties, depending on the connection type
setting and removing a fixed MAC address in the network adapter properties
removing and adding back TCP/IP v6 support from and to the network connection properties
reading about MAC randomization in Windows 10 which claims it only applies to Wi-Fi connections, and we only have wired connections

Nothing seemed to have any effect.

The only thing that did seem to have effect is:

You toggle the connection pooling status in ODBC Data Sources (does not matter if it was on or off, what matters is that you flip the status to the opposite), but only provided that
The system has been running for at least about 12 minutes after startup. If you flip the pooling status earlier than that, it will seemingly not have any effect.

The moment you do that, the addresses stop changing, but they do not revert to the actual MAC address of the network adapter, instead they freeze at whatever values they had when the freeze happened, and from then on each application will receive the same MAC for all its subsequent connections, but that MAC will be different among different application on the same device. If you close and run an application again, it will receive its frozen MAC address again as if you never closed it.

What is going on? How do we stop it from happening so that TCP/IP connections properly use the network adapter's MAC as opposed to a random address?

Alternatively, is there a better way to reliably identify and distinguish client devices (not users) on a local network from SQL Server (e.g. hostname is no good because it depends on the connection string)?


texts:
I have two tables ProjectsTable and ExpenseTable given following and a trigger ExpenseSum which update ProjectsTable when a value is inserted in ExpenseTable table. Now, I want to modify Trigger so that it can check ExpenseTotel whether it exceeds TotalCost or not if it exceeds TotalCost then give a simple message and prevent to insert in ExpenseTable otherwise insert in ExpenseTable and update in ProjectsTable. Can anyone please help regarding this issue ...

CREATE TABLE ProjectsTable
(
  ProjectID     NUMBER(6) NOT NULL,
  ProjectName   VARCHAR2(200) NOT NULL,
  TotalCost      NUMBER(10,2),
  ExpenseTotal  NUMBER(10,2),
  CostRemaining     NUMBER(10,2),
  PRIMARY KEY (ProjectID)
);

CREATE TABLE ExpenseTable
(
  ID            NUMBER(6) NOT NULL,
  ProjectID     NUMBER(6) NOT NULL,
  ExpenseAmount NUMBER(10,2),
  ExpenseDate   NUMBER(4),
  CONSTRAINT fk
  FOREIGN KEY (ProjectID)
  REFERENCES ProjectsTable(ProjectID)
);

CREATE TRIGGER ExpenseSum AFTER INSERT ON ExpenseTable FOR EACH ROW
BEGIN
    UPDATE ProjectsTable P
    SET ExpenseTotal =  ExpenseTotal + :New.ExpenseAmount
    where P.ProjectID = :New.ProjectID;
END;
/


texts: How to migrate data from a large amount of csv files with time x space measurements to a database that needs to be available for analysis

texts:
I have made a simulation experiment to obtain time series measurements at different points along a power line. These measurements were done for different types of power lines in different test conditions. So, for each combination of the 8 categories among test conditions and power line types, which gives a total of 60 thousand possible combinations of categories values, I have 6 measurements fields, and, for each of them, a csv file was generated. Each csv file contains data related to a type of measurement for a range of time values (rows, ~ 800 time points) and a range of distance values (columns, ~ 100 columns). Unfortunately, I can not change the format this software outputs the data.
The csv files occupies a total of almost 6TB (more than 8 million files). I need to make this data easy to be analysed from different perspectives (e.g. choosing values for 7 of the available categories and compare the time series in a given space point among different possible values of the 8th category), preferentially using python to make queries and analyze data.

I am trying to migrate the csv files to a PostgreSQL database but it is not a requirement, it is just a solution I am working on. So, I modeled the data to have a big table named "measurements" where each row has 8 categories columns (where the entries are integers as foreign keys for each category table), 2 columns for time and space points (float) and 6 columns with the measurements (float). However I checked the disk usage of my database for some insertions and I saw that comparing with csv files, it is occupying almost 3 times of disk space.
It is being also very time consuming to transform data from csv files to insert to SQL tables (I am currently using python pandas dataframes as an intermediary format).
Any suggestions on how to model the data so that the disk usage is lower? Is there a better way to model the data to optimize queries as the mentioned above?  Is there another type of database that may be more suitable for this problem (maybe NoSQL?)?


texts: Why do I have to drop a snapshot in SQL Server 2012 to restore a database?

texts:
I never had to drop a snapshot with SQL Server 2008, but now when I try to restore in SQL Server 2012, I get the error:

  System.Data.SqlClient.SqlError: The operation cannot be performed on a database with database snapshots or active DBCC replicas.

Why is this necessary in 2012 and not in 2008?


texts: Append to an existing backup setup when doing a Transaction Log backup?

texts: When doing transaction log backups, is it best practice to just keep appending to the same file or creating a new .trn file for each backup?


texts:
I'd use a listagg() to generate a list of components a certain customer has and compare it to another list generated the same way somewhere else to detect inconsistencies. The problem is that sometimes a customer can have too many components and exceed the maximum output of listagg(). I was wondering if there could be a way to generate a hash from a group of ordered rows, in a way I could use to make the same validation. Sort of:

with my_tabe as
(
    select 'user1' as usrid, '1' as prodcode from dual union
    select 'user1' as usrid, '2' as prodcode from dual union
    select 'user1' as usrid, '3' as prodcode from dual union
    select 'user2' as usrid, '2' as prodcode from dual union
    select 'user2' as usrid, '3' as prodcode from dual union
    select 'user2' as usrid, '4' as prodcode from dual
)
select
    userid,
    ORA_HASH(prodcode) WITHIN GROUP (ORDER BY prodcode)
from
    my_table
group by userid

Is there any function that could be used to that end?


texts: Several mongod instances that do automatic failover without duplication of data?

texts:
Because we don't have much storage space and we already have a RAID system for data availability, we don't want to use mongodb's replication set with duplication of data.

I just want to know if mongodb's replication allows any automatic failover of mongod instances without duplication of data. I just need the mongodb service to be available, not the data.

I read the replication section on the mongodb site but i did'nt find any mention about my use case. What I want exactly is to have 2 machines running a mongod instance on each and doing automatic failover between them. They are connected to servers which store data (no additional duplication of data as we have already our RAID system). If the automatic failover is not possible in this case, we may need to do it manually.

Any experiences, suggestions or related links are welcome. Thanks in advance.


texts: Adding a Clustered index, should I drop the nonclustered indexes

texts: I am adding a clustered index as an online operation to a table that has 3 billion rows. It has taken over 17 hours so far. If I do this again on another table with just as many rows should I first drop the nonclustered indexes?


texts: Is a partition a relation in its own right, or is a partitioned table a single relation? Specifically, do we need to increase max_fsm_relations when we split tables up into loads of partitions?


texts: Restore script in SQL server doesn't work in W7

texts:
I need a little help with a restore process, I can't find specific help in other similar questions, sorry if my question does not correspond.

I have this script in my system:

RESTORE DATABASE [Basesistema] FROM DISK = N’C:\Sistema\backup.bak’ WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

It work perfect in Windows XP, but not in Windows 7…

The message is:

  Error al buscar el archivo ‘C:\Archivos de programa\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Basesistema.mdf’ en los directorios, error del sistema operativo: 3(failed to retrieve text for this error. Reason: 15105)

English translation:

  Failed to find file 'C: \ Program Files \ Microsoft SQL Server \
  MSSQL10.SQLEXPRESS \ MSSQL \ DATA \ Basesistema.mdf' directories,
  operating system error: 3 (failed to retrieve text For This error.
  Reason: 15105)

What can I do?
Thanks for your help!!

Followup:

Well, actually I need restore from a script embedded in my system, not from SSMS; but SSMS (even as Admin) did not have access to the backup file.
If I move the file to C: or D: is the same...

I have SQL running under Windows Autentication account (integrated security), my only windows account have admin rights... I don't understand what happens.

The curious is the path "MSSQL10.SQLEXPRESS" doesn't exists, SQL is in "..\MSSQL10_50.SQLEXPRESS\MSSQL..". Does this have something to do?


texts: In Oracle 12c, is there an alternative to CREATE TYPE when I only have read-only privileges?

texts:
I have read-only access to a Oracle 12c system where there is zero chance I can get additional privileges or get the DBAs to change anything on my behalf (such as do CREATE TYPEs for me).

I'm using Oracle 12c feature WITH FUNCTION myfunc() .. SELECT .. to create functions as part of my SQL queries, which is working great.

Except, I would like to do something similar as shown in this, which is:
WITH FUNCTION myfunc(p_numbers t_numbers) .. SELECT ..
where t_numbers would normally be declared:
CREATE TYPE t_numbers AS TABLE OF NUMBER;
which I can't do.

Is there any alternative way to accomplish this kind of "inline" function without CREATE TYPE privileges?  I want to be able to pass in an arbitrary quantity of numbers to an inline function as discussed in this.


texts: Are older versions of MS SQL Server DEVELOPER edition now free?

texts:
Microsoft SQL Server Developer Edition is now free  https://blogs.technet.microsoft.com/dataplatforminsider/2016/03/31/microsoft-sql-server-developer-edition-is-now-free/

I see that I can download 2016 and 2014, but are there downgrade rights to use 2012 and earlier versions?


texts: MySQL protection by iptables. Data was stolen

texts:
My MySQL data is losed.. second time.

I configure iptables correctly and mysql users. Users protected by static ip of dedics and only my ip for acces is by CIDR diapason.

Iptables configuring for blocking ssh and mysql (3306) port with acces to VPS by static dedicks ips.

But my data was stolen... please help how to protect my db more?

Sorry for my english.


texts:
Lets say I have a table with 3 fields:

id1
id2
mapTypeId

What I want is a primary/unique key that is any combination of id1 and id2.  For example if I have this record already in the database:

1, 2, 3

I should not be able to insert a record like:

2, 1, 3

Right now I have id1 and id2 as my composite primary key however I am able to insert the data above, is there anyway to prevent this on the database level?


texts:
I am working on one project and it has around 100K records. We can afford a downtime of 1 day but we need the data to be always available to us in latest state.

I've gone through two options:

1) Mirroring (to be deprecated in future versions)

2) AlwaysOn Availability Groups

Neither looks like a perfect fit in my scenario because mirroring would be obsolete soon and Availability Groups are only available in Enterprise Edition of SQL server. We are only having BI edition.

Are there any good options apart from these? We are ready to spend money even for 3rd party tools, but can't afford Enterprise Edition at this moment.


texts: Count/Statistic for number of SHOW INDEX queries in MySQL/MariaDB 5.5

texts:
We have a lot of SHOW INDEX FROM queries coming from our ORM layer.
Usually there is a mechanism to prevent this but it doesn't seem to work
at the moment. To better analyze the problem it would be helpful if we
could see how many such queries happened on a server.

Is there a counter
or a statistic which can provide us this information?

We use MariaDB 5.5.


texts: ERROR: check constraint is violated by some row

texts:
I've got the following error:

ERROR: check constraint "cc_at_least_one_mapping_needed" is violated by some row

Query:

ALTER TABLE integrations.tax_aggregates
DROP COLUMN IF EXISTS myob_id,
ADD COLUMN myob_id integrations.FOREIGN_IDENTIFIER;
COMMENT ON COLUMN integrations.tax_aggregates.myob_id IS 'Foreign key for MYOB';

ALTER TABLE integrations.tax_aggregates DROP CONSTRAINT IF EXISTS cc_at_least_one_mapping_needed,
ADD CONSTRAINT cc_at_least_one_mapping_needed CHECK ((("qb_id" IS NOT NULL) :: INTEGER +
                                                      ("xero_id" IS NOT NULL) :: INTEGER +
                                                      ("freshbooks_id" IS NOT NULL) :: INTEGER +
                                                      ("myob_id" IS NOT NULL) :: INTEGER +
                                                      ("ppy_id" IS NOT NULL) :: INTEGER) > 0);

                                                      DROP INDEX IF EXISTS integrations.ix_tax_aggregates_myob_ids_ids;
CREATE INDEX ix_tax_aggregates_myob_ids_ids ON integrations.tax_aggregates USING BTREE (myob_id)
  WHERE myob_id IS NOT NULL;

What am I doing wrong? How can I solve the problem?

UPDATED:

Query:

ALTER TABLE integrations.accounts
DROP COLUMN IF EXISTS myob_settings,
ADD COLUMN myob_settings JSON;

ALTER TABLE integrations.accounts DROP CONSTRAINT IF EXISTS cc_at_least_one_setting_needed,
    ADD CONSTRAINT cc_at_least_one_setting_needed CHECK (("qb_settings" IS NOT NULL) or
                                                          ("xero_settings" IS NOT NULL) or
                                                          ("freshbooks_settings" IS NOT NULL) or
                                                          ("myob_settings" IS NOT NULL) or
                                                          ("ppy_settings" IS NOT NULL));

Select results:

SELECT * FROM integrations.accounts WHERE qb_settings IS NOT NULL AND xero_settings IS NOT NULL and freshbooks_settings IS NOT NULL AND myob_settings IS NOT NULL AND ppy_settings IS NOT NULL

It returns 0 rows

SELECT * FROM integrations.accounts WHERE qb_settings IS NULL OR xero_settings IS NULL OR freshbooks_settings IS NULL OR myob_settings IS NULL OR ppy_settings IS NULL;

RETURN (59 rows):

"account_id"|"global_settings"|"qb_settings"|"xero_settings"|"freshbooks_settings"|"myob_settings"|"ppy_settings"

"30374"|""|"{""id"":""1412494720"",""active"":true,""settings"":{""secret"":""iisCCNSiH4jmRArWUPi0rxa9LntQdAA3kc7lbLtf"",""token"":""qyprdigrVpUijVKDxJ72iBZrnh4tvlwMA1QuVxxyPSgLIz4j"",""expires"":""2016-01-31 04:34:37""},""plan"":""QuickBooks Online Essentials""}"|""|""|""|""

ERROR:  check constraint "cc_at_least_one_setting_needed" is violated by some row


texts: SQL Server Management Studio 2014 Backup - Set Property Devices Error

texts:
I recently upgraded my local SQL Server to Version 2014, which also installed the SSMS 2014.
When using SQL Server Management Studio 2014 to create a backup (both local and remote, Server Versions 2008 - 2014), I get the following error:

  To accomplish this action, set property Devices.

In the dropdown I chose Disk and I've also chosen a path for the backup. The path exists on the server and I've got full permission on this path.
If I use the old SQL Server Management Studio 2012 with the same settings, the backup works without any issues.
It also works using T-SQL BACKUP DATABASE SOMEDATABASE TO DISK = 'D:\SomeDir\SomeDataBase.bak' WITH COMPRESSION

So the issue has to be specific with the Management Tools Version 2014.
The only solutions I found on the internet were due to missing path, which isn't the case for me.
Has anyone experienced and/or solved this issue?

Edit: Added Screenshot (UI in German)

Translation:
- Vollständig: Full
- Datenträger: Disk

Media and security options unchanged.
Master database was just taken as example, happens to every other database.

Update 21.07.2015

The problem seems to be related to me using an English language pack on a German Windows and a German SQL Server 2014 installation.
If the Windows display language is set to German, the issue disappears.
If the Windows display language is set to English, the issue reappears.

Thanks for all contributions.


texts: Hide information table in Mysql Workbench

texts:
is there any way to hide the table information subcolumn in mysql workbench?
I work with version 8.0
Thank you very much in advance.


texts: What's the difference between INSERT ... SELECT and SELECT INTO

texts:
Let's say I have a table item and an other one versionned_item. From time to time, I want to copy data from item to versionned_item. What I did is

   WITH
     subset AS (SELECT ...)
     INSERT INTO versionned_item SELECT ... FROM subset

But in the documentation I came across the SELECT INTO statement that seems to do the exact same thing so I am wondering if there are differences / good practices about those 2 kind of inserts.


texts: Where and how to set the variables for Percona MySQL Audit Variables

texts:
I am trying to set the variable values for Percona MySQL Audit Plugin in my.cnf file before installing the audit plugin on MySQL Server like 'audit_log_rotate_on_size','audit_log_rotations' and 'audit_log_file'.But MySQL is failing to install the plugin with the following error

ERROR 1123 (HY000): Can't initialize function 'audit_log'; Plugin initialization function failed.

Can any one tell me how to set the desired values for 'audit_log_rotate_on_size','audit_log_rotations' and 'audit_log_file' in the my.cnf file before installing the audit plugin on Percona MySQL.

I am using Percona MySQL Community Version 5.5.38


texts: UPDATE Only one row (using update and join)

texts:
I'm trying to get a fast, simple sql query to update only one row at a time while using join too.

I have tried LIMIT, but to no success.

Query:

UPDATE
table1
JOIN
table2 ON table2.col=table1.col
SET
table1.row1='a value'
WHERE
table1.row2 LIKE '%something%'
LIMIT 1

Message:

  Error Code: 1221. Incorrect usage of UPDATE and LIMIT


texts: Why does Casting this string as a decimal fail?

texts:
Why does casting this result of REGEXP_SUBSTR() to a DECIMAL fail?
SELECT
    REGEXP_SUBSTR('Cost (-$14.18)', '(?<=Cost [(]-[$])[0-9.]+') AS _extracted,
    CAST(REGEXP_SUBSTR('Cost (-$14.18)', '(?<=Cost [(]-[$])[0-9.]+') AS DECIMAL(8,2)) AS cost_1,
    CAST((SELECT _extracted) AS DECIMAL(8,2)) AS cost_2,
    CAST((SELECT _extracted) * 1 AS DECIMAL(8,2)) AS cost_3,
    CAST('14.18' AS DECIMAL(8,2)) AS cost_4;

+------------+--------+--------+--------+--------+
| _extracted | cost_1 | cost_2 | cost_3 | cost_4 |
+------------+--------+--------+--------+--------+
| 14.18      |  14.00 |  14.00 |  14.18 |  14.18 |
+------------+--------+--------+--------+--------+

Casting a plain string as in cost_4 seems to work. Multiplying the REGEXP_SUBSTR() result by 1 also appears to work.  But simply casting the result as I've done with cost_1 and cost_2 fails to produce the correct fixed point version of _extracted.
Oddly, in my application using the backreference as I've done for cost_2 actually produces the correct result.  Was unable to reproduce elsewhere but thought it worth mentioning.


texts: Transaction has set autocommit = 0, but still the update happens before I commit

texts:
I have multiple MySQL InnoDb tables, I am trying to alter/insert/modify many of them in a SQL transaction, I set autocommit to false, so that if some SQL statements fail, I do commit the rest of statements:

set autocommit = 0;
START TRANSACTION;
use database1;
update `table1` set tkey = '3' .....;
update `table1` set tkey = '4' .......;
update `table1` set tkey = '5' .......;
update `table1` set tkey = '6' .......;
ALTER TABLE `table2` ADD COLUMN `.....` bool NOT NULL; -- This fails
COMMIT;

In above transaction the alter against table2 fails, but still the updates to table 1 have happened, how can I avoid that? I want a rollback even if one statement fails.


texts: Avoiding "Waiting for table metadata lock" when `ALTER TABLE DROP PARTITION`?

texts:
I have some tables that many users need to access to:

mysql> show create table v3_cam_date\G
*************************** 1. row ***************************
       Table: v3_cam_date
Create Table: CREATE TABLE `v3_cam_date` (
  `campaignid` mediumint(9) NOT NULL DEFAULT '0',
  `totalclick` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `totalview` int(11) unsigned NOT NULL DEFAULT '0',
  `realclick` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `clickcharge` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `viewcharge` int(11) unsigned NOT NULL DEFAULT '0',
  `uv` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `uc` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `dt` date NOT NULL DEFAULT '0000-00-00',
  `ctr` decimal(5,3) NOT NULL DEFAULT '0.000' COMMENT '=-1: meaning not available(N/A)',
  `moneyc` int(11) unsigned NOT NULL DEFAULT '0',
  `moneyv` int(11) unsigned NOT NULL DEFAULT '0',
  KEY `ix_campaignid_dt` (`campaignid`,`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (TO_DAYS(dt))
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
 PARTITION p01 VALUES LESS THAN (734502) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (734683) ENGINE = InnoDB,
 PARTITION p03 VALUES LESS THAN (734863) ENGINE = InnoDB,
 PARTITION p04 VALUES LESS THAN (734959) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (735141) ENGINE = InnoDB,
 PARTITION p06 VALUES LESS THAN (735210) ENGINE = InnoDB,
 PARTITION MERGER_2013227 VALUES LESS THAN (735291) ENGINE = InnoDB,
 PARTITION pcurrent_2013227 VALUES LESS THAN (735292) ENGINE = InnoDB) */

When an user want to drop a partition ALTER TABLE v3_cam_date DROP PARTITION pcurrent_2013227, it may cause many transactions to be in the Waiting for table metadata lock state:

     Id: 31560182
   User: alice
   Host: 192.168.3.40:36132
     db: db
Command: Query
   Time: 806
  State: Waiting for table metadata lock
   Info: SELECT COUNT(DISTINCT A.`campaignid`)  INTO _campaigncomplete
        FROM `ox_campaigns` A
        INNER JOIN `selfserving_users` B ON B.`user_id` = A.`uid`
        INNER JOIN `v3_cam_date` C ON C.`campaignid` = A.`campaignid`
        WHERE A.`revenue_type` = 5 AND A.`deleted` = 0 AND A.`expire` = DATE_ADD(    (SELECT `sys_date_cpc` FROM `000_sys_params_v4`) , INTERVAL 1 DAY)
                AND A.`isExpired` = 0 AND IF( NAME_CONST('_permitid',3) = -1, 1=1, IF( NAME_CONST('_permitid',3) = 0, A.`uid` IN (SELECT C.`user_id` FROM `selfserving_users` C WHERE C.`groupid` =  NAME_CONST('_groupid',17) ) ,A.`uid` =  NAME_CONST('userid',5770)))

What is the efficient way to accomplish this without locking?


texts: different db stats in MongoDB replica set

texts:
I have a MongoDB (3.2.6) replica set with two nodes and an arbiter. The secondary node was out for quite a long time and was recently brought back up with an initial sync.
So at the moment the rs.status() is as follows:

rs.status()

{
    "set" : "anonymizedReplicaSet",
    "date" : ISODate("2017-05-24T07:33:08.629Z"),
    "myState" : 2,
    "term" : NumberLong(-1),
    "syncingTo" : "anonymized01:27017",
    "heartbeatIntervalMillis" : NumberLong(2000),
    "members" : [
        {
            "_id" : 1,
            "name" : "anonymized01:27017",
            "health" : 1.0,
            "state" : 1,
            "stateStr" : "PRIMARY",
            "uptime" : 157061,
            "optime" : Timestamp(6423603132856545, 12785),
            "optimeDate" : ISODate("2017-05-24T07:40:52.000Z"),
            "lastHeartbeat" : ISODate("2017-05-24T07:33:06.790Z"),
            "lastHeartbeatRecv" : ISODate("2017-05-24T07:33:06.790Z"),
            "pingMs" : NumberLong(0),
            "electionTime" : Timestamp(6415474245823889, 1),
            "electionDate" : ISODate("2017-05-02T09:56:38.000Z"),
            "configVersion" : 10
        },
        {
            "_id" : 2,
            "name" : "anonymized02:27017",
            "health" : 1.0,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "uptime" : 157062,
            "optime" : Timestamp(6423603141446477, 10075),
            "optimeDate" : ISODate("2017-05-24T07:40:54.000Z"),
            "syncingTo" : "anonymized01:27017",
            "configVersion" : 10,
            "self" : true
        },
        {
            "_id" : 3,
            "name" : "anonymizedas:27017",
            "health" : 1.0,
            "state" : 7,
            "stateStr" : "ARBITER",
            "uptime" : 157061,
            "lastHeartbeat" : ISODate("2017-05-24T07:33:06.790Z"),
            "lastHeartbeatRecv" : ISODate("2017-05-24T07:33:07.248Z"),
            "pingMs" : NumberLong(0),
            "configVersion" : 10
        }
    ],
    "ok" : 1.0
}

However, I noticed that the two nodes do not agree on their stats, e.g., when I issue db.stats() simultaneously to the two servers I get:

Primary Node db.stats()

{
    "db" : "mydb",
    "collections" : 8,
    "objects" : 43933967,
    "avgObjSize" : 575.327071124718,
    "dataSize" : 25276400557.0,
    "storageSize" : 11909304320.0,
    "numExtents" : 0,
    "indexes" : 12,
    "indexSize" : 997511168.0,
    "ok" : 1.0
}

Secondary Node db.stats()

{
    "db" : "mydb",
    "collections" : 8,
    "objects" : 44016455,
    "avgObjSize" : 576.927973845236,
    "dataSize" : 25394324199.0,
    "storageSize" : 9882480640.0,
    "numExtents" : 0,
    "indexes" : 12,
    "indexSize" : 901128192.0,
    "ok" : 1.0
}

Shouldn't the number of objects on the two nodes agree? In addition, I receive different results when issuing mydb.mycollection.count() commands. Is this normal behavior? Or is something wrong with the syncing of the two nodes?


texts:
I have inherited an Sybase ASE 16 database server and my initial task consist on deploy some Key Performance Indicators to monitor the RDBMS instance performance.

I have 3 tools available in the company:

Cacti
Nagios
Ganglia

And I'd like to have easy to read, nice-looking performance graphs like this:

I'm starting to look for information about configurations to be made to achieve this.

Any advice?


texts: PostgreSQL for windows create tablespace on network location (nfs shared) failed because directory not exist

texts:
My server is Windows Server 2016. The server mounted a NFS remote storage with below command:
mount \\192.168.6.68\nfsshare z:
mkdir z:\pg_data

All the relevant permissions such as read/write/execute were set correctly on z:
I installed PostgreSQL 12.3 to C:\PostgreSQL. And then created tablespace with below commands:
create tablespace my_db_space location 'z:\pg_data'

PostgreSQL replied me:
Error, directory 'z:\pg_data' does not exist.

I also tried 'z:\pg_data', 'z:/pg_data', but all got the same error message.
I also tried another way, which is, I installed PostgreSQL in Z:\postgresql. The installation was successful. but postgreSQL service failed to start because of the error "directory not found".
In Linux, the same situation didn't happen the same error.
It seems that on Windows, PostgreSQL can only use local storage as its data location, right?
Any siggestion ?


texts: Is there a way to easily validate that a SQL Script is compatible with a specific SQL Server version?

texts:
I am currently working on a project that needs to operate in different SQL environments. Most of my developers have SQL Server 2017. The application runs in SQL Azure, and the client has recently started running some environments using SQL Server 2016. The project is using SSDT to generate Dacpac files which works fine for development purposes, however we recently ran into a case where a developer used a system function that was introduced in 2017. The stored procedure scripts that referenced this function worked fine in our dev and Azure environments and failed in the 2016 environment.

Is there an easy way to validate a script against some arbitrary SQL Server version, without requiring us to create multiple test environments for each SQL Server version we wish to support? The method does not need to be 100% foolproof, but should catch the vast majority of potential compatibility issues.


texts: difference between MySQL and noSQL databases

texts: All these days I've been using MySQL, recently I saw a kind of similar named Database NoSQL. What is NoSQL and what is the difference between them ?


texts: Should I use UUIDs or integer primary keys to optimize for massive writes of relational data?

texts:
I am working on a computer vision data pipeline and am unsure of how to structure my database to optimize for writes.

I have massive amounts of image data that is being collected on an ongoing basis. Image frames are used to build 1-3 second video clips which are to be labelled by a remote workforce. Workers label each clip (using a web application I built) with various properties (does the clip contain object x?).

My current pipeline generates the video clips and sends them to S3. An Amazon Aurora (with MySQL compatibility) database is used to track each image frame, clip, and associated tags.

The 'frames' table contains an entry for every single image frame, with associated metadata.

The 'clips' table contains an entry for every clip, has a field 'start_frame_id', which is a foreign key defining the first frame from the 'frames' table in the given clip. The associated clips are accessed from S3 by the remote workforce, using the sha256 hash of the clip as a key.

The 'labels' table contains an entry for each label created by a worker, and is related to the 'clips' table.

Both the 'clips' and 'frames' table contain a sha256 hash of the original file.

This database needs to be heavily optimized for writes, as the number of frames and clips will be massive (approximately 500K frames will be added per day, clips are 20fps). All uploads to S3 and writes to the database are done from local machines.

The prototype that I have built uses auto-incrementing integers for primary keys. However, this requires the client to execute database writes in small chunks. Since each clip needs to have a reference to its start frame, it is necessary that I commit all the frames for a given clip in order to obtain the primary key of the first frame, before I can commit the clip. This solution also makes it tricky/impossible to later add insert-only write replicas. For this reason, I am debating using UUIDs instead of integers, but I know this can cause performance problems with joins.

Should I use UUIDs or integers?


texts: SQL Server - How can varbinary(max) store > 8000 bytes?

texts:
In SQL Server 2005 and 2008 R2, I'm calling into a stored procedure that has an out parameter defined as varbinary(max). The out parameter returns 10020 bytes according to DATALENGTH.

However, SQL Server errors if I try to define a varbinary with > 8000 bytes such as varbinary(10000). E.g.

The size (10000) given to the type 'varbinary' exceeds the maximum allowed for any data type (8000).

What is happening here? How can SQL Server return more bytes than allowed in the data type? Is SQL Server using some other data type behind the scenes to hold > 8000 bytes?


texts: MySQL server won't start with foreign_key_checks=0

texts:
I'm running Ubuntu with MySQL version 5.5.44

  mysqld  Ver 5.5.44-0ubuntu0.14.10.1 for debian-linux-gnu on x86_64 ((Ubuntu))

I have a DB with foreign keys. I would like to have the foreign key check disabled at the system level (not at the session).

I saw the system var foreign_key_checks that seems to control this behavior but when I set it in /etc/mysql/my.cnf (by adding foreign_key_checks = 0) the server won't start. The error log looks like:

150729 15:38:18 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
150729 15:38:18 [Note] Plugin 'FEDERATED' is disabled.
150729 15:38:18 InnoDB: The InnoDB memory heap is disabled
150729 15:38:18 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150729 15:38:18 InnoDB: Compressed tables use zlib 1.2.8
150729 15:38:18 InnoDB: Using Linux native AIO
150729 15:38:18 InnoDB: Initializing buffer pool, size = 128.0M
150729 15:38:18 InnoDB: Completed initialization of buffer pool
150729 15:38:18 InnoDB: highest supported file format is Barracuda.
150729 15:38:18  InnoDB: Waiting for the background threads to start
150729 15:38:19 InnoDB: 5.5.44 started; log sequence number 56348937
150729 15:38:19 [ERROR] /usr/sbin/mysqld: unknown variable 'foreign_key_checks=0'
150729 15:38:19 [ERROR] Aborting

How can I configure my MySQL server to start with foreign key checks off at the system level?

Edit: behavior is the same with MySQL 5.6.25:

  mysqld  Ver 5.6.25-0ubuntu0.15.04.1 for debian-linux-gnu on x86_64 ((Ubuntu))