texts: How can you see PostgreSQL procedures in Azure Data Studio?

texts: If I use pgAdmin 4 to query PostgreSQL, I can see procedures section under materialized views. But, If I use Azure Data Studio I cannot see the procedures section. Any idea how to get to see the Procedures in Azure Data Studio? I can see functions, but I cannot see procedures. Thanks.


texts: Is there a faster way of importing 8m+ rows?

texts:
I'm creating a new feature on my website in order to provide player locations (my website is for a multiplayer gaming service), and am going to use a simple IP lookup in my database.

For this I need to import an IP location CSV (country  IP address to city) into my local MySQL database*, but it's taking a while:

I started importing the data roughly 40 minutes ago, and it looks like it'll take a few hours.

Is there a faster way of importing the data. I'm currently using MySQL Workbench and it uses no more than 15% of my CPU (using an Intel Xeon X5650 with 16GB RAM on Windows 10 Pro x64), so the issue may be that MySQL Workbench simply isn't optimized for a data import of this scale.

Is there a faster way of importing this data?

*Will be Amazon Aurora when I have the money to do so, so I'm not worried about the query speed.


texts: Updating multiple mysql rows where column has specific value

texts:
I have 1 CSV file with 50,000 rows of data and 2 columns.

The columns are user ID's and twitter profile photos.

24525,https://pbs.twimg.com/profile_images/666407537084796928/YBGgi9BO.png
27753,https://pbs.twimg.com/profile_images/2284174752/64pe9ctjko2omrtcij7a.png
24434,https://pbs.twimg.com/profile_images/638751551457103872/KN-NzuRl.png
19911,https://pbs.twimg.com/profile_images/1240079072/logo-mysql-170x170.png

I have a MYSQL database and am interested in updating 1 table that looks like this.

umeta_id  - primary key ( we do not have this in our csv )
user_id -  This is the first column in our csv
meta_key -  We want to update when meta_key is 'twitter_photo'
meta_value - twitter profile photos go here

I can update 1 row with query like this

UPDATE `wp_usermeta`
SET meta_value='https://pbs.twimg.com/profile_images/666407537084796928/YBGgi9BO.png'
WHERE meta_key LIKE 'twitter_photo'
AND user_id='24525';

I have been struggling to figure out how to do multiple rows.  I tried this but it does not work.

 UPDATE `wp_usermeta` SET `meta_value` = CASE `user_id`
                      WHEN 24525 THEN 'https://pbs.twimg.com/profile_images/666407537084796928/YBGgi9BO.png'
                      WHEN 27753 THEN 'https://pbs.twimg.com/profile_images/2284174752/64pe9ctjko2omrtcij7a.png'
                      WHEN 24434 THEN 'https://pbs.twimg.com/profile_images/638751551457103872/KN-NzuRl.png'
                      ELSE `meta_value`
                    END,
         WHERE `user_id` IN (24525, 27753, 24434) AND `meta_key` LIKE 'twitter_photo';

Any help in the right direction would be much appreciated.


texts:
Consider the following json:

{
  "floor": [
    {
      "level": 1,
      "apt": [
        {
          "no": 1,
          "area": 40,
          "rooms": 1
        },
        {
          "no": 2,
          "area": 80,
          "rooms": 3
        },
        {
          "no": 3,
          "area": null,
          "rooms": 2
        }
      ]
    },
    {
      "level": 2,
      "apt": [
        {
          "no": 4,
          "area": 100,
          "rooms": 3
        },
        {
          "no": 5,
          "area": 60,
          "rooms": 2
        }
      ]
    }
  ]
}

Using Postgres jsonpath, if I wanted to get the apartments with 3 rooms, the following would suffice:

'$.floor[*].apt[*] ? (@.rooms == 3)'

My question is how would I do the same if I wanted to compared against an ARRAY such that I could provide the number of rooms in an array similar to ANY in SQL

'$.floor[*].apt[*] ? (@.rooms = ANY{3,5,6,10,11} )'

Using Postgres 12.2 on Linux Ubuntu 18.04, 64Bit


texts: Balancing the size of oracle datafiles for a tablespace

texts: I have a tablespace where I had to add datafiles, because a partion run full. The new datafiles are not filled yet. Can I move data from one datafile to the other?


texts: Server has antivirus. Do I need to enable external locking on MySQL?

texts:
From MySQL docs:

  If you only read tables with other programs, external locking is not required

Assuming the bytes of MySQL files does not match viruses, the antivirus will not change the contents of the file.

However, the antivirus may change file attributes and "do some other things".

Do I need to enable external locking on MySQL to prevent (possible) data corruption?

(I'm using both InnoDB and MyISAM.)


texts: Difference in Value between latest two rows?


texts: Partitioning an existing table into multiple filegroups

texts:
I am working on a SQL Server 2016 SP2 Standard edition server, with an in-house application that is 24/7 used in multiple locations across multiple countries. The current application has one database per Customer (company using the application and central server). Eventually, there will be hundreds of Customers with databases in this system.

There is also one "Central" database, which includes all archival data from all of the individual DBs. A customer Id value is used to keep all of the different data separated.

To speed access to archival data, and to simplify maintenance, the current plan is to partition this Central DB, with the appropriate by-customer tables partitioned into one partition per customer, each on a separate physical file in a separate DB partition.

However, this DB and the relevant tables are already in use in an unpartitioned way. I am researching what needs to be done to split things out into the desired data structures.

Example: There is a table (Orders) that includes CustomerID. The desire is to split this existing table so there is one Customer per Filegroup/file based on the CustomeId. The partitioning function, partitioning scheme, and other miscellany is simple enough to figure out. However, the only examples I can find for splitting a table across filegroups involve creating a new table. The documentation doesn't explicitly state that ONLY a newly created table can do this in partitioning, and some older articles I have read (SQL 2005 and 2008) say that you can't do this.

So, the final question: Other than by creating a duplicate table with the correct partitioning, migrating the data, and renaming the old and new tables, is there any way to partition an existing table into multiple different new filegroups / physical files?

Directions to examples for SQL 2016 (or 2012 if it can be done there or later) will be accepted as well.

TIA


texts: Migrate data to new PostgreSQL RDS instances

texts:
I'm still new to PostgreSQL and reading as I go, but so far haven't found exactly what I'm looking for (though there are similar things and maybe I'm lacking on the terminology).

We have an RDS instance in AWS (PostgreSQL), the data is encrypted using the AWS KMS. I want to create a new RDS instance, which I want to encrypt using a new custom key, then migrate all the data from the old instance to the new one.

The problem is that the way AWS works, the private key is stored away and hidden from me, so I can't decrypt the data and then move it to the new instance, the solution I'm thinking about is use some sort of database client, maybe write a query or a dump of the data and then push it to the new instance.

Has anyone dealt with such an issue before? In PostgreSQL terms, how can I get all the data using a client or something and then push it to the new instance?

I'm going to install pgadmin and see if that helps, any suggestions would be most welcomed.


texts: Is it better to delete then insert, or to update then insert in SQL server?

texts:
I'm using SQL server 2005.  I have a table that is basically a list of ids with timestamps that is recording the last time an event happened for that id.

I have a list of ids in a table variable, and I want to update the table with the current time for all the ids that are already in the table, and insert new records for the ids that are not yet in the table.  SQL 2005 doesn't have a MERGE statement or a ON DUPLICATE KEY UPDATE.

Which of these forms is preferred:

UPDATE IdTimes SET LastEventTime = GetDate() WHERE Id in (SELECT NewId FROM @NewIds)

INSERT INTO IdTimes(Id, LastEventTime)
   SELECT NewId, GetDate() FROM @NewIds
   WHERE NOT EXISTS (SELECT Id FROM IdTimes WHERE NewId = Id)

Or:

DELETE FROM IdTimes WHERE Id IN (SELECT NewId FROM @NewIds)
INSERT INTO IdTimes(Id, LastEventTime) SELECT NewId, GetDate() FROM @NewIds

Or is there a better solution to achieve this?


texts: Removing WALS when replication slot is not busy

texts:
One query in case of asynchronous replication with replication slots and no wal archiving if a slave node remains disconnected from master for more than wal_keep_segments, it will not be able to do the replication when it comes online.

Now my question is when the slave connects to the master again will the XLOG in master also keep on building  as the slaves replication is stuck or postgres will remove the replication slot if it is not busy / active?

Postgres Version - 10.4


texts: How to handle database of multiples languages and countries

texts:
Imagine that I have a database of books. I would like to store multiples informations for multiples languages and countries.
Imagine that I would like to support 10 countries and 10 languages.

I want to store at least the name and description for each language.
The price, the currency and availability, the release date for each country.
And other rows.

Here are my options to store this on database and have the possibility with one request to get the infos for all countries and languages at a time :

Solution 1
- have one column per language and country, but in the end I have 100 columns

Solution 2
- have a table "price", a table "currency", "availability" etc, which store the information for one country in one row.
The same for "name", "description" which store the information for one language in one row

solution 3
- have 2 tables, one will contain every data for one country (so one row contain the price, the currency, the availability etc for one country and book). And the other the same but for one language (and contain the name and description for one language per row).

solution 1 makes it easy to retrieve informations for all countries and languages at a time, but this makes a very large table (i have millions of rows) and I believe it is slower to query this table due to number of columns

solution 2, the problem is to retrieve infos for all countries and languages will need one join for each lang and country, so I end up having a lot of join and be stuck with the maximum join limit (61 ?)

solution 3, will lower the number of joins (but still one join per lang and one per country).

What is the best ? How do people do when they want to show informations like that ?

thanks

EDIT:
What do you think of this solution, which is kind of solution 2, but with only one join for each table, instead of one join per language and per country :
http://sqlfiddle.com/#!9/7ae955/1


texts: How to Set Up a Shadow Production Database for PostgreSQL?

texts:
I'm working on a Django project and I'd like to avoid the common pitfall of using SQLite for development and PostgreSQL for production referenced in Two Scoops of Django.

I'd like to take this a step further and use a "shadow production database" (shadow prod DB) for PostgreSQL. How can I get this set up? I think that once the shadow prod DB is set up and configured, swapping out the database settings from "production" to "development" (shadow Prod) will be trivial in the Django application's settings.py file.

However, when it comes to setting up the framework where the "shadow prod DB" is actually recording all of the same transactions that are occurring in the production database I have no idea where to start. Any help is greatly appreciated.

Further, what do I do if I want to get the shadow prod DB onto a laptop. What happens when the database size grows prohibitively large?

Thanks, Scott.

For posterity, I originally heard about the concept of a "shadow prod DB" on the podcast Scaling Postgres which linked to this post from Heap.


texts: CPU usage on RDS instance monotonically increasing with no change to query volume

texts:
I have two RDS instances: a R/W master and a read-only replica.

On 29th June, the replica stopped registering replication data - not sure if this is related.

On July 3rd, the master's CPU usage started increasing monotonically, and drastically:

It's almost at critical 100% now.

The query volume and hasn't really changed, to my knowledge. The only thing which happened around then was a django-celery daemon in my web tier grabbed a whole CPU core - a force kill of that seemed to fix problems on the web tier, but it seems likely that the DB tier problems may be related.

DB size also started increasing monotonically at the same time:

There are no long queries in the processlist, and no INSERTs at all really, so I'm not sure how to find out which tables are growing, and where that CPU is going.

Are there MySQL diagnostics while can show me table size trends? Profile global ongoing queries? Profile global CPU usage?

I've already rebooted the server a couple of times, to no avail.

There's obviously still lots of space left on the server, but when we get to 100% CPU usage, things are going to get ugly, so any help much appreciated!


texts: Check value when I have a list of elements separated by commas in a column?

texts:
I am looking to return a value in a database.
Table named TIPOLOGY is structured like this:

Name   Owner   ID       Office  Type   LIST_OF_VALUES
Jones  Lasi      1       12345   TKT    AUD,PAX,CCF
Jones  Lasi      2       54321   TKT    AGT, FLT
Jones  Lasi      3       45689   TKT    IND,INV,CAS

I need to find the office and I have like information NAME, OWNER, TYPE and a VALUE.

So my basic query is:

SELECT Office FROM typology WHERE NAME=Jones AND OWNER=Lasi AND TYPE=TKT AND "AUD" in LIST.

Value AUD is only on one line, so it can return only one office.

However I don't know how to search for a value that is in a list inside the column LIST_OF_VALUES.


texts: MySQL still seems to be establishing a connection even when access is denied, causing aborted connects and instability

texts:
Lately the server's been going a bit haywire and we've spent several weeks chasing down leads to no avail.  All we can tell is that when the websites go down (and come back up, and go down, and come back up...) the aborted_connects MySQL variable starts increasing somewhat quickly.  The only way we've been able to restore order has been to restart the server.  After that, it can take as little as an hour or as long as a few days, but the problem always comes cropping up again.  This began immediately after upgrading from 5.1 to 5.6.

I've looked through the log files, specifically for threads which have a Connect logged, but no corresponding Quit, and I've found something.  The culprit appears to be the following two lines:

43375 Connect    Access denied for user 'test'@'ch.websitepulse.net' (using password: NO)
43375 Connect    test@ch.websitepulse.net as  on

I assume that "websitepulse" is a service we use to monitor the database and make sure it's up, but the only guy who would know for sure isn't here today.  We  likely knowingly use (and maybe pay for) this service.

Throughout the log file, there are a good number of lines pretty much identical to the two above, and the thread has no corresponding quit message.  It would seem that the first line, access being denied, would indicate that there wasn't even a successful connection established, so there would be no reason for a Quit.  The second line, however, has me wondering.  It appears that despite access being denied, it still connects somehow.  No queries are ever executed, and it's always just these same two lines.

The two spaces on the second line between "as" and "on" in the second line are no mistake.  There is nothing there, or after "on", as I suspect there should be.  Our database guy said that the number of aborted connections corresponded to the number of connections by user NULL@NULL.  The as <blank> on <blank> would seem to indicate that this user is our NULL.

Does this sound right to anyone?  If so, how might we go about fixing it?  I'm really in the dark, and Googling it hasn't turned up anything promising.

Thanks so much.


texts:
I keep getting this error whether I run my query from the terminal or from within Java. Anyone has some instructions how to resolve this?

Thread stack overrun:  5768 bytes used of a 131072 byte stack, and 128000 bytes needed.
Use 'mysqld -O thread_stack=#' to specify a bigger stack.

BTW: I am using mac osx lion


texts: How can I design a table with greater than in where statement and ordering to avoid filesort?

texts:
I have a list of documents that I store along with creation date and rank:
CREATE TABLE `document` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` INT UNSIGNED NOT NULL,
  `rank` INT UNSIGNED NOT NULL,
  `created_at` TIMESTAMP NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_combo` (`category_id` ASC, `created_at` ASC, `rank` ASC)
) ENGINE=InnoDB;

I would like to get a list of 10 documents that belong to category 30, were created_at is after certain point in time and order it by rank:
EXPLAIN
SELECT
    id
FROM
    docs.document
WHERE
    category_id = 30
    AND created_at > 1592862179
ORDER BY
    rank
LIMIT 10

It's not surprising that this query has to use filesort:
+------+-------------+----------+------+---------------+-----------+---------+-------+-------+------------------------------------------+
| id   | select_type | table    | type | possible_keys | key       | key_len | ref   | rows  | Extra                                    |
+------+-------------+----------+------+---------------+-----------+---------+-------+-------+------------------------------------------+
|    1 | SIMPLE      | document | ref  | idx_combo     | idx_combo | 4       | const | 19596 | Using where; Using index; Using filesort |
+------+-------------+----------+------+---------------+-----------+---------+-------+-------+------------------------------------------+

If the table is large and I have many records for many years and want to retrieve data for specific date range, filesort becomes performance issue. I would like to be able to retrieve results directly from the index. Is there a way to redesign the table, index or the select query for mysql to only use the index for retrieval and avoid temp tables or filesort?


texts: If using a function in a case, does it run on each condition and result or is cached?

texts:
In this example:
select case when someFunction() is null or someFunction() = 0 then 999 else someFunction() end

someFunction() runs three times or just one?


texts:
I need to split a comma delimited string, manipulate it, and then concatenate it back into a single string retaining the original order of the data (if possible).
For example, take a column definition list of a CREATE TABLE statement (as a string) like so 'BrentOzarColumn INTEGER, PaulWhiteColumn DATETIME, ErikDarlingColumn VARCHAR(100)'. I'd like to split the comma delimited list to a result set, such as using SQL Server's built in function STRING_SPLIT() like so: SELECT TRIM([Value]) AS CoolDataPeople FROM STRING_SPLIT('BrentOzarColumn INTEGER, PaulWhiteColumn DATETIME, ErikDarlingColumn VARCHAR(100)', ',').
Without specifying an ORDER BY clause, this repeatedly yields (by coincedence?) the following results that appear to be ordered by their same ordering as they are in the string:

Once I have the result set above, I want to apply some additional string manipulations to each row (such as append some constant text), and then concatenate every row back with a function like STRING_AGG() (goodbye days of STUFF ... FOR XML PATH :) in the same order as the original string. So an example of my final result could be 'BrentOzarColumn INTEGER SQLROX, PaulWhiteColumn DATETIME SQLROX, ErikDarlingColumn VARCHAR(100) SQLROX'.
Ultimately my question is: Are the results of the STRING_SPLIT() function returned in a deterministic order? I know without an ORDER BY clause, ordering is not guaranteed when selecting from a dataset like a Table or View, but was wondering if there's a difference with functions?
As I type this out, I have a hunch the answer is no, the ordering is not deterministic therefor I'm not guaranteed the order of the results. Furthermore, I'm betting there's possible additional nondeterminism added for each function I run on top of the results, especially when I combine them back together with STRING_AGG(). (Regardless of the answer, I appreciate your help, and you're all cool data people. ;)


texts: Query Rows That Have a Following Row That Meets Criteria

texts:
Will try to do my best to explain this, as I don't really know how to word this to search for an answer.  I'm a constantly learning but still newish SQL user who thus far has been able to solve most questions by searching, but can't think how to word this.

I want to pull a query, which is all contained in one single table.  The database is logging rows as follows;

User    Login Type

Bob        1

Ryan       1

Tom        1

Tom        3

I want the query to show me the rows that have a "1" login type immediately followed by a "3" login type.  So my query would result would look like this:

User  Login Type

Tom    1
Tom    3

How would I format such a query? Normally something like this would be easy by doing a few joins, but I can't seem to wrap my head around how to do it with only one table involved.  I've looked into joining the table into itself, but can't seem to nail down how to get the results I want (if that is even what I should be doing, it is the only thing that seems logical to me)


texts: Alternative query to this (avoid DISTINCT)

texts:
Note: I work with MSSQL 2008, but I guess it's valid for many others DB engines
I have this table "Users":

UserID  User    CountryID
1     user 1    1
2     user 2    2
3     user 3    3
4     user 4    4
5     user 5    4
6     user 6    3

And this table "Countries"

CountryID   Country
1           MX
2           USA
3           CAN
4           ENGLAND

As you can see, every user belongs to a country.

If I want to know, all the different countries where I have at least, one user on the Users table, right now I do this query:

select distinct country
from Users inner join
countries on users.CountryID=countries.CountryID

And achieve the next result set:

CAN
ENGLAND
MX
USA

Which is indeed, all the different countries, where I have at least one user on muy table Users.

My doubt is, is possible to achieve the above result set, with out using "DISTINCT", I mean only using JOINS and conditions ?

Here it's de DDL scripts:

USE [TEST]
GO
/****** Object:  Table [dbo].[Users]    Script Date: 09/21/2012 16:21:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
    [UserID] [int] NULL,
    [User] [nvarchar](50) NULL,
    [CountryID] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (1, N'user 1', 1)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (2, N'user 2', 2)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (3, N'user 3', 3)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (4, N'user 4', 4)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (5, N'user 5', 4)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (6, N'user 6', 3)
/****** Object:  Table [dbo].[Countries]    Script Date: 09/21/2012 16:21:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Countries](
    [CountryID] [int] NULL,
    [Country] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (1, N'MX')
INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (2, N'USA')
INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (3, N'CAN')
INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (4, N'ENGLAND')
INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (5, N'BRAZIL')


texts: SQL DatabaseMail smtp error 5.7.57 at one site, email works at another with the same details

texts:
I have several SQL databases on different servers. I have used a script to set up Database mail.

EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'Emailer'
    ,@description = 'Email out reports'
    ,@email_address = 'XXXXXXX@XXXXXXX.com.au'
    ,@display_name = 'SA'
    ,@username = 'XXXXXXX@XXXXXXX.com.au'
    ,@password = '**********'
    ,@mailserver_name = 'smtp.office365.com'
    ,@replyto_address = 'XXXXXXXX@XXXXXXXX.com.au'
    EXECUTE msdb.dbo.sysmail_add_profile_sp
    @Profile_name = 'Emailer'
    ,@description = 'Email out reports'
    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'Emailer'
    ,@accoun t_name = 'Emailer'
    ,@sequence_number = 1;
    EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'Emailer'
    ,@principal_name = 'public'
    ,@is_default = 1;

    UPDATE msdb.dbo.sysmail_server
    SET enable_ssl = 1

This script works, and then once I've run the script I try and send an email using Database Mail. On one server the email works, on another server I get the error:

  The mail could not be sent to the recipients because of he mail server
  failure. (Sending Mail using Account 1 (2015-08-21T11:27:55).
  Exception Message: Cannot send mails to mail server. (The SMTP server
  requires a secure connection or the client was not authenticated. The
  server response was: 5.7.57 SMTP; Client was not authenticaed to send
  anonymous mail during MAIL FROM).)

What could be causing this?


texts: Return rows that meet a condition in two different columns

texts:
I have the following table describing the color and smell of an animal. I am trying to filter it to see what animals are the same color and smell. I do not care if multiple animals use the same color or use the same smell, but if they use the same color AND smell, that is what I want to see.

I have tried using union and also and select statement with in after a where. I think I understand how to compare multiple columns if they are in different tables, but I can't seem to do it if the columns are in the same table.

Table: Animals

Name| color| smell
-------------------------
cat | red  | bad
dog | red  | bad
rat | red  | good
bat | red  | decent
bee | red  | okay
ape | red  | okay
ant | red  | bad

I want to be able to return the following:

Name| color| smell
-------------------------
cat | red  | bad
dog | red  | bad
ant | red  | bad
bee | red  | okay
ape | red  | okay

Thanks for the help.


texts: Concurrency when simulating autoincrement with triggers on MySQL

texts:
I am updating an old application to implement virtual tenancy, which involves adding a tenant_id column to the primary key of every single table. The application uses auto-incrementing columns heavily, which presents a problem with InnoDB, since InnoDB doesn't allow auto-incrementing columns in multi-partite primary keys.

The standard solution to this problem seems to be "use triggers": e.g., https://stackoverflow.com/questions/18120088/defining-composite-key-with-auto-increment-in-mysql. At this point, I cannot find the reference I modeled my original solution from.

I know only enough about databases to know that I am in huge danger of shooting myself in the foot here. In particular, I am very concerned about concurrency, both in terms of making sure we don't start generating duplicate primary keys by accident (correctness), but also in terms of making sure that we don't start generating all sorts of locks that would slow down the application.

So my question has two parts:

Given the scheme below, how can I be sure that the code that selects the next value in a sequence for a table and tenant_id is atomic? Is it possible for two instances of the trigger to search for the same row at the same time?
How will this sequencing scheme behave during transactions? Will the sequence be rolled back if the transaction is rolled back? (We don't necessarily want that.) Will the row containing the sequence be locked for the entirety of the transaction? If so, is there some way to exclude the sequence table from the transaction?

Note that I am entirely willing to accept that this scheme is a terrible idea. We have even thought about doing the sequence generation at the application level. But before we commit to that kind of work, I wanted to figure out whether our current approach is useable.

The sequence table is defined as possible:

CREATE TABLE IF NOT EXISTS `MyApplicationSequence` (
    `table_name` VARCHAR(50) DEFAULT NULL,
    `tenant_id` SMALLINT NOT NULL,
    `sequence` INT NOT NULL,
    PRIMARY KEY (`tenant_id`, `table_name`)
);

And for each table that requires it, we create a trigger like the following:

CREATE TRIGGER `SEQ_MyApplicationTable1`
BEFORE INSERT ON `MyApplicationTable1` FOR EACH ROW
BEGIN
    UPDATE
        `MyApplicationSequence`
    SET
        `sequence` = `sequence` + 1,
        NEW.`original_primary_key` = `sequence`
    WHERE
      `table_name` = 'MyApplicationTable1' AND
       `tenant_id` = NEW.`tenant_id`
    LIMIT 1;

    -- If we there was no sequence already, start a new one at 1
    IF ROW_COUNT() = 0 THEN
        INSERT INTO `MyApplicationSequence` (`table_name`, `tenant_id`, `sequence`)
        VALUES ('MyApplicationTable1', NEW.`tenant_id`, 1);
        SET NEW.`original_primary_key` = 1;
    END IF;
END;


texts: Export and split QUERY results in multiple files

texts:
I would like to know if it's possible to export and split query results in multiple files. Let's assume I have a query returning 100 results and I want to save the results in 5 different csv files (20 results per file). Is there any way to do it?
Regards,


texts: how to insert data into extra columns of target avro table when source table is having less no of columns compared to target using hive or impala?

texts:
Suppose I am having a source Avro table having 10 columns and my target Avro table is having 12 columns, while inserting data into the target table I need to add null values to the extra 2 columns.
But when I execute the below query it has thrown the exception

AnalysisException: Target table 'target_table' has more columns (8) than the SELECT / VALUES clause returns (7)

insert overwrite table target_table select * from source_table;

How to make advantage of Avro table automatic schema change detection here?
Note: Suppose if I want to insert only 5 columns to the target and the rest should be default null. So how to achieve this?


texts: Does blocking always mean open transaction?

texts: This may seem a bit dull question but does blocking always mean that there is open transaction and that may cause transaction log grow to infinity because open transaction prevents log truncation (database in SIMPLE mode)?


texts: How to retire a physical standby in Oracle 11.2

texts:
I have a production Oracle 11gR2 RAC database with a single node physical standby. Now it has become the time to retire the old standby and pass that role to a RAC on fresher hardware.

We don't use Data Guard Broker.

I haven't been able to find much on this particular process, so I figured maybe there wasn't all that much to it. Apparently there is at least a tad more than I have been able to think of.

After stopping the log apply on the old standby, I attempted to simply clear the standby log destination and set a new destination for log shipping. Oracle then complains that the DB_UNQUE_NAME of the new physical standby is not a part of the current Data Guard configuration.

SQL> alter system set log_archive_dest_3='';

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter system set log_archive_dest_3='SERVICE=PDB10SBY LGWR ASYNC REGISTER VALID_FOR=(online_logfile,primary_role) REOPEN=60 DB_UNIQUE_NAME=PDB11SBY' scope=both sid='*';
alter system set log_archive_dest_3='SERVICE=PDB10SBY LGWR ASYNC REGISTER VALID_FOR=(online_logfile,primary_role) REOPEN=60 DB_UNIQUE_NAME=PDB10SBY' scope=both sid='*'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16053: DB_UNIQUE_NAME PDB10SBY is not in the Data Guard Configuration

A quick query to v$dataguard_config confirms that there is an active configuration containing the DB_UNIQUE_NAME of the old standby database.

SQL> select * from
  2  v$dataguard_config;

DB_UNIQUE_NAME
------------------------------
PDB10
PDB10_STBY

So then the question is, what are the steps required to retire an existing Data Guard configuration so that a new physical standby database can be deployed?


texts:
Note, this is 2008, not 2008 R2.

This isn't an unknown problem, but I can't seem to get it to work. Seems like all of the solutions I've tried relate to 2008 R2 and above versions. Chrome updated and now our reports are showing in a small window instead of a full browser window.

I've searched high and low for a solution for this one. This seems to be the universal solution but it doesn't want to work, even after restarting SSRS:

SSRS 2008 R2 - SSRS 2012 - ReportViewer: Reports are blank in Safari and Chrome

I'm at a loss on this one. Does 2008 R2 differ that much in terms of reporting services configurations? The easy answer here is to use IE for reports, but our SAAS apps run better on Chrome and link to our reporting services reports.

Other fixes tried:

Anything from I could find from the Chrome Store: SSRS Fix, IE Tab which works, but we have over 75 machines and buying licensing right now isn't an option


texts: Return both Result set AND a warning from a stored procedure

texts:
In Data Studio when you call SYSPROC.ADMIN_CMD() directly and it returns a warning, the results panel shows both the warning and the result set:

CALL SYSPROC.ADMIN_CMD('EXPORT TO /tmp/exportcat/storeent1.ixf OF IXF modified by codepage=1208 MESSAGES ON SERVER select  from storeent1' );

IWAQ0003W SQL warnings were found
SQLState=      Routine "SYSPROC.ADMIN_CMD" execution has completed, but at least one error, "SQL3022", was encountered during the execution. More information is available.. SQLCODE=20397, SQLSTATE=     , DRIVER=4.18.60
 ROWS_EXPORTED MSG_RETRIEVAL                                                                       MSG_REMOVAL
 ------------- ----------------------------------------------------------------------------------- ----------------------------------------------------
          NULL SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('180908937_DB2INST1')) AS MSG CALL SYSPROC.ADMIN_REMOVE_MSGS('180908937_DB2INST1')

I want to wrap it, so my proc retrieves and shos the details about the warning:

CREATE PROCEDURE xexport (
in @TABNAME VARCHAR(128)
, in @COLLIST CLOB
, in @WHERECOND CLOB
) DYNAMIC RESULT SETS 1 MODIFIES SQL DATA
P1: BEGIN
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE SQLCODE INTEGER DEFAULT 0;
    DECLARE @SAVESQLCODE INTEGER;
    DECLARE @SAVESQLSTATE CHAR(5);

    DECLARE LOC1 RESULT_SET_LOCATOR VARYING;
    DECLARE @S , @S2 CLOB;
    DECLARE @ROWS_EXPORTED BIGINT;
    DECLARE @MSG_RETRIEVAL , @MSG_REMOVAL CLOB;
    DECLARE @MYMSGS XML;

    DECLARE DETAILSCUR CURSOR FOR MSG_RETRIEVAL_STATEMENT;

    DECLARE MYMSGSCUR CURSOR WITH RETURN FOR
        SELECT M from XMLTABLE('$msgs/*/M' PASSING @MYMSGS AS "msgs"
        COLUMNS "M" CLOB PATH '.') AS U;

    if length(@COLLIST) > 0 then
    else
        SET @COLLIST = xtabcols4(UPPER(@TABNAME), '', 1, '');
    end if;

    SET @S = 'EXPORT TO /tmp/exportcat/' || LOWER(@TABNAME) || '.ixf OF IXF modified by codepage=1208 MESSAGES ON SERVER select ' || @COLLIST || ' from ' || @TABNAME || ' ' || @WHERECOND;
    SET @MYMSGS = XMLCONCAT( @MYMSGS , XMLELEMENT( NAME "M" , 'CALL SYSPROC.ADMIN_CMD(''' || REPLACE(@S,'''','''''') || ''')'));

    BEGIN
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING begin
            select SQLCODE , SQLSTATE into @SAVESQLCODE ,  @SAVESQLSTATE from sysibm.sysdummy1;
            if (@SAVESQLCODE < 0) then RESIGNAL; end if;

            SET @MYMSGS = XMLCONCAT( @MYMSGS , XMLELEMENT( NAME "M" , 'ADMIN_CMD() warning: SQLCODE=' || RTRIM(CAST(@SAVESQLCODE as char(100))) || ';SQLSTATE=''' || @SAVESQLSTATE || '''' ) );

            ASSOCIATE RESULT SET LOCATORS (LOC1) WITH PROCEDURE SYSPROC.ADMIN_CMD;
            ALLOCATE CUR1 CURSOR FOR RESULT SET LOC1;
            FETCH CUR1 INTO @ROWS_EXPORTED, @MSG_RETRIEVAL, @MSG_REMOVAL;
            PREPARE MSG_RETRIEVAL_STATEMENT FROM @MSG_RETRIEVAL;

            OPEN DETAILSCUR;
            BEGIN
                DECLARE @EOF INT DEFAULT 0;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET @EOF = 1;
                FETCH FROM DETAILSCUR INTO @S, @S2;
                WHILE @EOF = 0 DO
                    SET @MYMSGS = XMLCONCAT( @MYMSGS , XMLELEMENT( NAME "M" , @S || ': ' || @S2 ) );
                    FETCH FROM DETAILSCUR INTO @S, @S2;
                END WHILE;
            END;
            CLOSE DETAILSCUR;

            SET @MYMSGS = XMLDOCUMENT( XMLELEMENT( NAME "R", '' , @MYMSGS ));
            OPEN MYMSGSCUR;

            --RESIGNAL;
        end;
        CALL SYSPROC.ADMIN_CMD( @S );
    END;
END

But if I uncomment --RESIGNAL, Data Studio only shows the warning


texts: PosgreSQL query slow when sorting on indexed timestamp colum

texts:
I've the following issue: the table below:

    CREATE TABLE public.current_read
(
    id bigint NOT NULL DEFAULT nextval('current_read_id_seq'::regclass),
    cube_id bigint NOT NULL,
    end_time timestamp with time zone NOT NULL,
    period_type character varying COLLATE pg_catalog."default" NOT NULL,
    energy_balance double precision NOT NULL,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT current_read_pkey PRIMARY KEY (id),
    CONSTRAINT current_read_cube_id_fkey FOREIGN KEY (cube_id)
        REFERENCES cubemgmt.cube (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

-- Index: id-current_read-cube_id+end_time+period_type

-- DROP INDEX public."id-current_read-cube_id+end_time+period_type";

CREATE INDEX "id-current_read-cube_id+end_time+period_type"
    ON public.current_read USING btree
    (cube_id, end_time DESC, period_type COLLATE pg_catalog."default")
    TABLESPACE pg_default;

-- Index: ix-current_read-cube_id+end_time

-- DROP INDEX public."ix-current_read-cube_id+end_time";

CREATE INDEX "ix-current_read-cube_id+end_time"
    ON public.current_read USING btree
    (cube_id, end_time DESC)
    TABLESPACE pg_default;

-- Index: ix-current_read-cube_id+period_type

-- DROP INDEX public."ix-current_read-cube_id+period_type";

CREATE INDEX "ix-current_read-cube_id+period_type"
    ON public.current_read USING btree
    (cube_id, period_type COLLATE pg_catalog."default")
    TABLESPACE pg_default;

-- Index: ix-currrent_read-end_time

-- DROP INDEX public."ix-currrent_read-end_time";

CREATE INDEX "ix-currrent_read-end_time"
    ON public.current_read USING btree
    (end_time DESC)
    TABLESPACE pg_default;

ALTER TABLE public.current_read
    CLUSTER ON "ix-currrent_read-end_time";

has currently around 60 million entries.

When I do a simple query:

SELECT energy_balance
                  FROM public.current_read
                  WHERE cube_id = 5
                  AND period_type = '1min'
                  ORDER BY end_time desc
                  LIMIT 120

it takes around 3-4 seconds to return the results.
If I remove the ORDER BY statement, I get (random) results in just a few miliseconds.

Explain plain looks like the following:

"Limit  (cost=730066.65..730080.65 rows=120 width=16) (actual time=1560.745..1613.023 rows=120 loops=1)"
"  ->  Gather Merge  (cost=730066.65..772726.46 rows=365630 width=16) (actual time=1560.743..1613.000 rows=120 loops=1)"
"        Workers Planned: 2"
"        Workers Launched: 2"
"        ->  Sort  (cost=729066.63..729523.67 rows=182815 width=16) (actual time=1538.981..1538.991 rows=101 loops=3)"
"              Sort Key: end_time DESC"
"              Sort Method: top-N heapsort  Memory: 30kB"
"              Worker 0:  Sort Method: top-N heapsort  Memory: 30kB"
"              Worker 1:  Sort Method: top-N heapsort  Memory: 30kB"
"              ->  Parallel Bitmap Heap Scan on current_read  (cost=11257.81..721839.14 rows=182815 width=16) (actual time=432.012..1510.892 rows=138113 loops=3)"
"                    Recheck Cond: ((cube_id = 5) AND ((period_type)::text = '1min'::text))"
"                    Heap Blocks: exact=120165"
"                    ->  Bitmap Index Scan on "ix-current_read-cube_id+period_type"  (cost=0.00..11148.13 rows=438756 width=0) (actual time=360.098..360.098 rows=414338 loops=1)"
"                          Index Cond: ((cube_id = 5) AND ((period_type)::text = '1min'::text))"
"Planning Time: 0.297 ms"
"Execution Time: 1613.112 ms"

So it looks like that query is not taking index "ix-current_read-end_time" into consideration.

Any idea how to make sure the index is used or how to improve the query timing?

Thanks a lot,

Cheers

Fritz


texts: Transaction Log maintenance when switching to Simple Recovery

texts:
Background:

I recently inherited 50+ SQL Servers with 450+ databases. The nightly backups are roughly 8TB and, needless to say, we're using more disk space than we'd like. All of the databases are set to FULL recovery and the transaction logs have never been backed up.  I've gone through all of the SQL Servers and identified low priority ones that only need a nightly backup and a where a day of data loss is acceptable.

Question:

I'm switching a lot of low priority databases to SIMPLE recovery mode from FULL. Will the existing transaction logs be truncated (when checkpoints are created)? Some of the existing transaction logs are 50-100GBs; what is the best approach in determining what I should shrink them down to for the purposes of moving forward? I obviously don't want to keep them that large. Or, will they shrink on their own over time (I don't think they will)?


texts:
I have to design a table of Mobile Phones and SIMS. Some phones have more than 1 SIM slot (2). Some phones have no SIMS (or only 1), and any 1 SIM can only be in 0 or 1 phone(s).

A table that has PHONE_ID, SIM_ID is the starting point. Both columns are nullable. A primary key of (PHONE_ID, SIM_ID) allows for any phone to have more than 1, but it also unfortunately allows for any 1 SIM to be in more than 1 phone! How do I ensure that any 1 SIM can only be in 1 phone, while still allowing any number of phones to have no SIMS?


texts: Checking if a transitive key dependency is in 3rd normal form

texts:
I'm currently trouble determining if one of my schemas is in third normal form.

I have a table with columns image_id (A), upload_time (B), uploader_id (C), and image_file (D). A is a key (unique identifier for every row in the table), and BC is also a key (every pair of uploader and upload time is unique). Thus, we have A -> BCD, and BC -> A (and transitively BC -> A through A -> BCD).

Is this table in 3rd normal form? I'm not entirely sure, because I know 3NF allows for a partial key transitive dependency (e.g. XY -> Z, Z -> X), but I'm not sure if it allows for a total key transitive dependency like I have above.


texts: Sudden MySQL error: Unable to connect to any of the MySQL Hosts

texts:
I am using MySQL Community Server 5.5.18 on Windows 7. My application was running absolutely fine without any troubles but since morning it is giving strange messages while opening reports. Reports are on Crystal Reports but the error has nothing to do with the Crystal Reports. This message started appearing suddenly. This application is running very well from last two years.
Error image is below:

Please let me know how to fix this issue.


texts: Fetching rows from million rows table(Optimization)

texts:
I have millions of rows in a table of Greenplum database out of which I have to fetch around 45k rows and store them in a python List.

It's taking more than 2hrs to fetch the data. How can I optimize the time taken to fetch data?

resultList = []
for(item in list):
  result = SELECT column_1, ... column_n from TABLE WHERE column = item
  resultList.append(result)


texts: using enum or other datatype for 3 repetitive values

texts: I am creating a dashboard to display information from a mysql database. The information being displayed primarily deals with tracking information. I have a script that is first pulling in information, making a decision as to what the data is and then storing it in a table. One of the columns in the table refers to the shipping company (UPS, FedEx, USPS). Is it more efficient to use ENUM to represent the value of 1 of the 3 shipping companies, or is there a more efficient way of storing these repeated values?


texts:
The setup is SQL Server 2008 R2:
Full Backup - Weekly  ~1.1TB
Differential - Every 4 hours ~66GB
Transactional - Every hour

What I want to know is why is the very first differential backup about 60GB, when a few transactions have taken place? I get it that differential acts in an aggregating way. But shouldn't the first differential be substantially smaller than the last differential in between full backups?


texts: 'mysql' database in fresh MySQL installation

texts:
I installed MySQL database and according to show databases command I have a database named 'mysql'. Is this database used for holding some internal values for MySQL? In addition, is it possible to view the content of rows of tables in 'mysql' database? Usual SELECT * FROM tablename does not seem to work:

mysql> describe servers;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| Server_name | char(64) | NO   | PRI |         |       |
| Host        | char(64) | NO   |     |         |       |
| Db          | char(64) | NO   |     |         |       |
| Username    | char(64) | NO   |     |         |       |
| Password    | char(64) | NO   |     |         |       |
| Port        | int(4)   | NO   |     | 0       |       |
| Socket      | char(64) | NO   |     |         |       |
| Wrapper     | char(64) | NO   |     |         |       |
| Owner       | char(64) | NO   |     |         |       |
+-------------+----------+------+-----+---------+-------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM servers;
Empty set (0.00 sec)

mysql>


texts:
We have a write-heavy SQL database (SQL Server 2008) and the insert speed decreases after some time. I read these similar questions: Sql insert speed up, SQL: What is slowing down INSERTs if not CPU or IO? and Speed up INSERTs.

Also, I read this post about how to analyze SQL Server performance which helped me to know how to find bottlenecks (for example, by querying sys.dm_exec_requests and sys.dm_os_wait_stats), but still I have difficulties in interpreting the query results and fixing the problem.

First, I started with querying the sys.dm_exec_requests which only returned one session id (select query) with status "running" (i.e., I found NO SUSPENDED SESSIONS). So, if nothing has blocked my insert, why does it become slow?

Next, I used sys.dm_os_wait_stats to check statistics about all wait types. The result showed that LATCH_EX, CXPACKET AND PAGEIOLATCH_SH had the most wait_time (694379 ms, 310364 ms and 308335 ms respectively).

Then I used sys.dm_os_latch_stats to find the most prevalent latch type which in my case was ACCESS_METHODS_DATASET_PARENT.

First of all, I don't know how to find the query related to each session_id in the results of querying sys.dm_exec_requests.
Secondly, what numbers of wait-time (in sys.dm_os_wait_stats) should be considered as high? And if the above mentioned numbers (results of sys.dm_os_wait_stats) are high, how can I decrease them?
As far as I understand, ACCESS_METHODS_DATASET_PARENT is related to parallelism and one of the solutions I've found was reducing the degree of parallelism. Is that right?
In MySQL, there are some tunning settings which could be done right after installation (e.g., increasing innodb buffer pool size), is there something similar in SQL Server?

Some more info:

Using sys.dm_db_index_usage_stats, number of reads and writes are
80336 and 70672 respectively.
One of our most busy tables is trans_all (showing all payments
transactions) with NO TRIGGERS, NO CONSTRAINTS (this is the same for
all tables), but it has a CLUSTERED INDEX, namely, PK_trans_all
consist of    the following columns: gs_id(smallint), pt_id(tinyint),
fueling_time(datetime), purchase_type(tinyint).
Database size is 2.6 GB and the size of trans_all table is 155 MB.

UPDATE_1

Regarding the ACCESS_METHODS_DATASET_PARENT, I tried the solution that I've found (i.e., changing the degree of parallelism. I changed it to 1 to make sure that query never go to parallelism), but it didn't fix the issue :| should I change it again? what is the default that I should use?

UPDATE_2

I just checked the size of transaction log file for my database which is 2.4 GB and %log space used is 98%. Could this be the reason to slow down my inserts? Should I increase the log file size?

Also, I used sys.database_files to check the file size, and the amount of empty space for my database. Result shows that filesize is 195 MB and empty space is 0.187 MB.


texts: mongodb - what roles the user to grant access to all databases

texts:
I have created mongouser as testuser with below roles. I want to give access for new databases for this user. what roles the user to grant access to all databases.

db.createUser({ user: "testuser" , pwd: "testuser", roles: ["dbAdminAnyDatabase", "readWriteAnyDatabase", "enableSharding"]});


texts: Sp_forEachDB ' DBCC Check DB' - why does it only check the first DB?

texts:
I set up a checkDB job, and I used sp_foreachDB, thinking I was really clever. Looking at the job in detail, however, it only seems to do the Master Database then stops, reporting success.

I can grow my own like this:

Create procedure JM_CheckDB

as

declare @DB_Name varchar(255),
@sql nvarchar(max)

declare c cursor local for

SELECT name FROM master.dbo.sysdatabases

open c
fetch from c into @DB_Name

While @@FETCH_STATUS = 0
begin

set @sql = 'Use ' + @DB_Name + ' DBCC CheckDB'

print @sql
exec sp_executesql @sql

fetch next from c into @DB_Name

end

close c
deallocate c

But I'd rather use the same thing everyone else uses.

Question: What's the right way to do sp_foreachdb 'dbcc checkdb'?

Answer: From SpBlitzErik - use Ola hallengren's scripts, you dummy.

Followup Questions:

How often do I need to run it? It needs to run at the same interval that I keep my backups, right? If I keep the backups for 1 day, I need to run it every day?
Can I restore the databases to my test environment and checkDB there?
If I've got lots of sql servers, but they all use the same SAN, am I going to want to stagger my CheckDB's?


texts: How to solve the error "Error 'Got error 0 'No error' from NDBCLUSTER' on query"?

texts:
I'm currently working with a large network of MySQL 5.1 DBs (using MyISAM tables) and I'm evaluating a move to MySQL Cluster. I would like to have the MySQL Cluster replicate off of one of the existing 5.1 hosts to ensure that it can handle the load we want to send it, and to test failure conditions with real data loads, etc.

So far, I've got the MySQL Cluster mysqld loaded up with all the MyISAM tables and I've got it connected to the replication master. Replication is working just fine (5.1 MyISAM -> 5.5 MyISAM). However, as soon as I convert one of the tables to ENGINE=NDBCLUSTER I get these errors:

121027 16:09:40 [ERROR] Slave SQL: Error 'Got error 0 'No error' from NDBCLUSTER' on query. Default database: 'accounts'. Query: 'UPDATE heartbeat SET ts = NOW() WHERE id = 1', Error_code: 1296
121027 16:09:40 [Warning] Slave: Got error 0 'No error' from NDB Error_code: 1296
121027 16:09:40 [Warning] Slave: Got error 0 'No error' from NDBCLUSTER Error_code: 1296
121027 16:09:40 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.027069' position 11283558

"heartbeat" is just some table we have that gets updated once a second. I can run the query by hand while connected to the cluster's API node (the same node that is reading the bin logs). It seems that the failure only occurs when the query is run on the slave thread.

Also, the query itself succeeds -- the heartbeat table is updated with the replicated value of NOW().

I am running 5.5.27 ndb 7.2.8 on 64-bit Linux hosts. I have 4 data nodes, one management node, and one API node (for now). This same error happened with 5.5.22 ndb 7.2.6 -- I'd upgraded to see if that would fix the problem, but no luck.

I have dug around with mysqld-debug trace logs and gdb. Near as I can tell there's a problem writing to the ndb_apply_status table, but I cannot pinpoint exactly what the problem is (nor am I sure that I am on the right track there).

This issue is also being tracked on the MySQL Forums.


texts: Microsoft SQL secondary read-only DB for SSRS?

texts:
I know that Microsoft SQL has AlwaysOn with a read-only secondary replica. However the cost of enterprise edition for all of our SQL servers makes this not possible. We have SSRS configured to run reports against our OLTP databases but it puts high loads on them.

Are there any other options of getting a secondary copy that could be used to point SSRS at?


texts: Mysql throwing table full error when adding new column

texts:
I keep getting table full error when adding a new column to 30gb table on innodb mysql database.
ibdata1 size : 844mb
Current Settings
innodb_data_file_path : ibdata1:12M:autoextend
tmp_table_size : 256M (increased from 64M)
max_heap_table_size : 256M (increased from 64M)
Changed settings above and restarted server. Still throwing error.


texts:
I have a table with two columns, I want to count the distinct values on Col_B over (conditioned by) Col_A.

MyTable

Col_A | Col_B
A     | 1
A     | 1
A     | 2
A     | 2
A     | 2
A     | 3
b     | 4
b     | 4
b     | 5

Expected Result

Col_A   | Col_B | Result
A       | 1     | 3
A       | 1     | 3
A       | 2     | 3
A       | 2     | 3
A       | 2     | 3
A       | 3     | 3
b       | 4     | 2
b       | 4     | 2
b       | 5     | 2

I tried the following code

select *,
count (distinct col_B) over (partition by col_A) as 'Result'
from MyTable

count (distinct col_B) is not working.
How can I rewrite the count function to count distinct values?


texts: Are multiple indexes on MYSQL table reason for slow UPDATES and INSERTS?

texts:
The performance of my (LAMP stack) site has degraded significantly over the last couple of days despite no code updates. It seems that only inserts, updates, and deletes of a particular MySQL table cause the problem. Any page that updates, inserts, or deletes an entry of the jobs "table" takes around 10 seconds to load. (E.G. UPDATE jobs SET title = 'sdfldsfjlk' WHERE job_id = 134324)
SELECT queries seem to execute as before, though they seem to be slower if an update is taking place at the same time.
The table has around 180,000 entries. I noticed in the PHPMyAdmin view, that in addition to the "normal" index on the primary field, there is an index on the "entry_date" field (see image). Could that be an issue in this case? I've no idea why an index on that field would have been created.
If not, what else could be the source of the problem? I've checked space on the disk, which seems OK. (7 GB available) according to df.

SHOW CREATE TABLE job\G

Create Table: CREATE TABLE `job` (
    `job_id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) NOT NULL DEFAULT '0',
    `entry_date` date NOT NULL DEFAULT '0000-00-00',
    `timescale` varchar(20) COLLATE latin1_german2_ci NOT NULL DEFAULT '0000-00-00',
    `title` varchar(60) COLLATE latin1_german2_ci NOT NULL,
    `description` text COLLATE latin1_german2_ci NOT NULL,
    `start_date` varchar(60) COLLATE latin1_german2_ci NOT NULL,
    `address_town` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
    `address_county` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
    `postcode1` varchar(4) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
    `postcode2` char(3) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
    `status` tinyint(4) NOT NULL DEFAULT '0',
    `cat_id` int(4) NOT NULL DEFAULT '0',
    `price` decimal(4,2) NOT NULL DEFAULT '1.00',
    `emailcount` smallint(5) NOT NULL DEFAULT '-1',
    `emailcount2` int(11) NOT NULL DEFAULT '-1',
    `recemailcount` int(11) NOT NULL DEFAULT '-1',
    `archive` tinyint(4) NOT NULL DEFAULT '0',
    `post_url` varchar(100) COLLATE latin1_german2_ci NOT NULL,
    PRIMARY KEY (`job_id`),
    KEY `entrydatejob_id` (`entry_date`,`job_id`)
) ENGINE=MyISAM AUTO_INCREMENT=235844
           DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci

UPDATE - Firstly thank you to all the contributors. I appreciate it very much. So over the last couple of days, the problem stopped, which made trying to find what the issue might be even more difficult. But now it seems to have returned once again. Let me start by providing the machine type hosted on google cloud: g1-small (1 vCPU, 1.7 GB memory). I'll continue updating this with the further information that's been requested by those commenting.


texts:
Right now there are two ways to build an index of a table in MySQL:

Create the table structure first, then import the data and add indexes later.
Create the table structure with indexes and then import the data.

In the first process we will have contiguous data (all fields) pages and then we have index pages. So when we query using index, MySQL has to first load the index pages and find out the matching keys and have to look up those primary key on the data pages. For this it has to then again load the data pages to get the data. This is useful when we have larger index scans as we have all index loaded contiguously.

In the second way of index creation the filtered index page will most probably contain the data page near to it as they were created at the same time. So I guess look up will be faster for a small range scans.

Is my understanding correct?

UPDATE:

I should have mentioned that the "PRIMARY KEY is enabled" (an auto increment id column) in the first way of  importing data. So internal rowid is not generated and lot of IO is saved as we are not going to add a PRIMARY KEY.

As you noted there is fragmentation when we import data using the Second method.

Considering my requirements is for larger range scans (scanning ~100M rows) I guess I will go with the first way of importing data.

update on Jun 8 11:30

CREATE TABLE `table_dummy` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `column1` bigint(20) DEFAULT NULL,
  `column2` bigint(20) DEFAULT NULL,
  `column3` bigint(20) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `column4` tinyint(1) DEFAULT NULL,
  `column5` tinyint(4) DEFAULT NULL,
  `column6` bigint(20) DEFAULT NULL,
  `column6_created_at` datetime DEFAULT NULL,
  `column7` int(11) DEFAULT NULL,
  `column8` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `twtaccount_id_2` (`column1`,`column2`),
  KEY `twtaccount_id` (`column1`,`created_at`,`column5`),
  KEY `twt_user_id` (`column3`,`created_at`,`column5`),
  KEY `original_status_id` (`column6`,`column3`,`followers_count`)
)

ENGINE is INNODB

this table has about 800M records and I took a dump and imported it in two ways.

Method 1: table was created with just the primary key and entire data was imported. Then I gave an alter statement to add the remaining indexes.

Method 2: The table was created with indexes and the dump was done. This took much larger time.

P.S The size of the resulting table through 'Method 1' was lesser by ~30GB when compared to size of table dumped through 'Method 2'. Method 1 took very less time than 'Method 2', almost twice the speed.

My primary concern is the performance of the table when I make a select  scanning a wide range of index ('index only scan').


texts: Importing Multipe Trace Files To A SQL Server Table

texts:
I am in the process of importing 200+ trace files (which are massive), and my current approach is to perform a loop and insert the trace data (see the below script).  I looked around to see if there was a faster way to do this, whether through SSIS or C# and it appears that they still call the below function, similar to the script below this.

Anyone have any other methods that they use to import multiple traces?  Don't get me wrong, the below code works, but I'm curious if there's something faster that I'm not considering.

DECLARE @start INT = 1, @stop INT = 223, @sql NVARCHAR(MAX), @path VARCHAR(1000)

WHILE @start <= @stop
BEGIN

       SET @path = 'N:\Traces\TraceFile_' + CAST(@start AS VARCHAR(5)) + '.trc'

       SET @sql = 'INSERT INTO SavedTraces
                   SELECT *
                   FROM ::fn_trace_gettable(''' + @path + ''', default)
                    '

       EXECUTE sp_executesql @sql

       SET @start = @start + 1
       SET @path = ''
       SET @sql = ''

END

Data Notes: 490MB (~.5G), which holds 11,700,000+ rows, requires 13:11 minutes to import.


texts:
I have a database with 112 stored procs, 7 views and 88 tables.
The problem is over time I have created tables and stored procs as I needed without taking the time to think about what I might be doubling up on work. I already know I have a couple of reports I generate that use essentially the same information but are pulling from two different tables. I am trying to bring everything down to as few tables and stored procs as possible.

The only thing I can think of is running a query that queries the stored procs to see what tables they all reference, this should get me a list of all the tables that are actually even being used any more.

Also I would want to know which procs are actually being run so a query that shows last run time for each proc.


texts:
I'm trying to understand cubes so thought I would create a simple datamart to represent my filesystem.

So my question is, if I wanted to create a simple datamart to represent my filesystem, how would it look?

I'm just thinking one fact for the files, and a few dimensions for file extension, maybe a date dimension and few others.

eg
factFiles
dimExtensions

Some guidance/suggestions would be appreciated.


texts: Get a list of Databases which were not modified with in a date range - SQL server

texts:
can we get an output for list of databases which were not modified for last two months in a server and based on that we have to delete all of them.

I have tried below is the query:
--identify when a user database was last accessed--

SELECT DatabaseName
      ,MAX(LastAccessDate) DatabaseLastAccessedOn
FROM    (
           SELECT  DB_NAME(database_id) DatabaseName
                 ,last_user_seek
                 ,last_user_scan
                 ,last_user_lookup
                 ,last_user_update
                   FROM sys.dm_db_index_usage_stats
       ) AS Pvt
       UNPIVOT
        (
           LastAccessDate FOR last_user_access IN  (
               last_user_seek
              ,last_user_scan
              ,last_user_lookup
              ,last_user_update
           )
       ) AS Unpvt
GROUP BY
       DatabaseName
HAVING DatabaseName NOT IN ('master' ,'tempdb' ,'model' ,'msdb')
ORDER BY
       2

Need a query to find DBs which were not accessed/modified for the last 2 months and delete them. i have SQL express edition.


texts: Bulk Import appending the data to the data file

texts: We have a bulk import task running in SQL Server 2016 which imports more than 5 million rows. The task itself is configured at cloud using third party services. The strange thing is that the new data being imported is appended to the data file (.mdf) and already allocated empty space is not being used. Tables are being truncated and reloaded. All the tables have clustered indexes defined. We have even used pre-processing task of truncate all related tables -> reorganize all related tables -> DBCC updateusage but to no avail. We have even double the size of the data file but we still see some auto growth events. Although such events decreased by 80 percent. After the import we are left with 70 percent of data file being empty. Am I missing something or any specific setting/change in SQL Server 2016 which is why we are experiencing such behavior? Thanks.


texts:
I am experiencing an issue with a stored procedure I wrote some time ago that has recently stopped working. The SP populates a cursor and runs through a loop processing the data in another SP.

The issue is that since recreating the SP with a simple cosmetic change, the NOT FOUND Handler for the cursor always fires immediately as if the cursor were empty, and the SP returns a 1329 No data processed error.

However, running the select query that populates the cursor succesfully returns the data fine.

Here is the code:

DELIMITER //

     CREATE PROCEDURE `Import_Leave`()
        BEGIN

        DECLARE AbsenceID INT;
        DECLARE PersonID INT;
        DECLARE EmpID INT;
        DECLARE Start_Date DATETIME;
        DECLARE Numdays INT;
        DECLARE Category VARCHAR(50);
        DECLARE WorkFlow INT;
        DECLARE exit_loop BOOLEAN ;
        DECLARE absence CURSOR FOR SELECT AbsenceID,PersonID,EmpID,Start_Date,Numdays,Category,Workflow FROM staging_holidays WHERE EmpID is NOT NULL;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;

        OPEN absence;

        shoop_da_woop: LOOP

          #SELECT exit_loop;

          FETCH absence into AbsenceID,PersonID,EmpID,Start_Date,Numdays,Category,Workflow;

          #SELECT exit_loop;

          IF exit_loop THEN
            CLOSE absence;
            LEAVE shoop_da_woop;
          END IF;

    #Code to process the data goes here, but is never executed asthe cursor is coming back empty

        END LOOP shoop_da_woop;

        END//

        DELIMITER ;

I cannot figure out why this cursor is not fetching the data succesfuly when the underlying SELECT statement works fine. This SP used to work but has stopped working since the last time it was recreated. The commented out SELECT statements before and after the FETCH statement are in for debugging and they confirm that the NOT FOUND HANDLER is firing as soon as the FETCH completes.

Any help would be appreciated!


texts: group by, order by and pick last value of a column in each group

texts: I am using PostgreSQL 9.6. I have one "events" table. I need to group by an "item_id" column then order by "date" column in each group and then pick last value of "value_num" column in each group. Pls, help me how this could be done.


texts: How can I use function's parameter in it?

texts:
I have a function like this:

DROP FUNCTION IF EXISTS user_repo //
CREATE FUNCTION user_repo(user_id INT) RETURNS VARCHAR(20)
BEGIN
    DECLARE vote_value mediumint;
    DECLARE score mediumint;

    SELECT coalesce(sum(r.vote_value), 0), coalesce(sum(r.score), 0)
      INTO vote_value, score
    FROM reputations
    WHERE owner_id = user_id
      AND date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK));

    RETURN CONCAT_WS(',', vote_value, score);
END;//

It works as well. Now I want to also pass another parameter to the function to determine the time range. I mean I want to pass a string instead of  WEEK. Here is my new function:

DROP FUNCTION IF EXISTS user_repo //
CREATE FUNCTION user_repo(user_id INT, range VARCHAR(10)) RETURNS VARCHAR(20)
BEGIN
    DECLARE vote_value mediumint;
    DECLARE score mediumint;

    SELECT coalesce(sum(r.vote_value), 0), coalesce(sum(r.score), 0)
      INTO vote_value, score
    FROM reputations
    WHERE owner_id = user_id
      AND date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 range));

    RETURN CONCAT_WS(',', vote_value, score);
END;//

But it throws this error:

  The following query has failed:

CREATE  DEFINER=`root`@`localhost` FUNCTION `user_repo`(`user_id` INT,
                `range` VARCHAR(10) CHARSET utf8)
       RETURNS VARCHAR(20) CHARSET utf8mb4
       NOT DETERMINISTIC
       CONTAINS SQL SQL
       SECURITY DEFINER
BEGIN DECLARE vote_value mediumint;
DECLARE score mediumint;
SELECT  coalesce(sum(r.vote_value), 0), coalesce(sum(r.score), 0)
    INTO  vote_value, score
    FROM  reputations
    WHERE  owner_id = user_id
      AND  date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 range));
RETURN CONCAT_WS(', ', vote_value, score);
END

  MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'range)); RETURN CONCAT_WS(',', vote_value, score); END' at line 9

Does anybody what's wrong?


texts: Is it good to have the total amount and the discount in the order table

texts:
I have two tables Orders and OrderItem.

Order
-------------------
order_id
order_number
order_date
total_amount
total_discount //discount sum up of individual item's percentage discount
coupon_discount //discount from coupon
net_amount

and:

OrderItem
--------------------
order_item_id
order_id
item_id
quantity
price
discount_per

I am confused as to what data should I store in these two tables.
I think that the item price and the discount should be there because they might change frequently. But what about the others?
I am also confused as to whether I should store the discount percentage or just the calculated discount or both?


texts: Windows Administrator doesn't have permission on SQL Server

texts:
My previous system administrator created a SQL Server 2008 instance on my local machine with his domain User.
Now, I cannot create, delete, or view any databases or tables on that instance. Even with my domain User, or Windows local administrator.
It says "CREATE DATABASE permission denied in master".
The users section in Management studio shows only two users 'BUILTIN\Users' and 'sa' which is disabled. 'Builtin\Users' has only public role.
It seems no user have permission to this instance. How can my Local Administrator get permission to it ?
I cannot delete the instance as it has important databases.


texts: VACUUM returning disk space to operating system

texts:
VACUUM usually does not return disk space to operating system, except in some special cases.
From the docs:

  The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained. In contrast, VACUUM FULL actively compacts tables by writing a complete new version of the table file with no dead space. This minimizes the size of the table, but can take a long time. It also requires extra disk space for the new copy of the table, until the operation completes.

The question is: how can this database state when one or more pages at the end of a table become entirely free be achieved? This can be done via VACUUM FULL, but I haven't got enough space to implement it. So are there any other possibilities?


texts: How to fix CRC error when doing a SQL Server database backup?

texts:
I'm in a pickle and any help would be greatly appreciated. I'm running our office's ERP Software (Acumatica) on a local server with Windows Server 2016 and SQL Server 2017.
Acumatica is currently running fine, we are not getting any errors whatsoever while using the app. We have 20+ employees constantly accessing Acumatica imputing data and so for. I have not receive any complains.
The problem that I'm having is that I'm getting an error when trying to backup the database using SQL Server Management Studio. When the backup process reaches 90% I get a SQL Server database cyclic redundancy check (CRC) error, and the backup fails.
I tried manually copying the .MDF and .LDF files to an external hard drive but the .MDF file fails just at the very end (99% with 120MB left to copy) with an error

Cannot read from source

I was able to copy the .LDF file. The .MDF file is around 140 GB in size and the .LDF file 180 GB.
What can I do to fix this and be able to do a full backup?.. The latest successful backup that I have is from 3 weeks ago. I really need to be able to backup the data before this gets any worse.
I have been thinking about running chkdsk with the repair command but I'm afraid it will make it worse and loose the database and/or data.
Any advice would be very highly appreciated.
Regards.
Carlos


texts: Is there a way to rewrite this MySQL query to use window functions, and not use session variables?

texts:
I have the following query

drop table if exists tradesAndActions;
create table tradesAndActions (transik int, ca_date date, terms float, multiply bit, originalPrice float, originalShares float);
insert into tradesAndActions values
  (1, '2010-01-01', 10, 0, 50.0, 1000.0),
  (1, '2010-02-01', 2, 1, null, null),
  (2, '2010-02-01', 2, 1, 10.0, 50.0),
  (2, '2010-02-01', 10, 0, null, null)

;

set @currentTransik := 0;
set @adjustedPrice := 0.0;
set @adjustedShares := 0.0;

select *,
  @adjustedPrice :=
      if(@currentTransik = a.transik,
        if(a.multiply = 1, @adjustedPrice * a.terms, @adjustedPrice + a.terms),
        if(a.multiply = 1, a.originalPrice * a.terms, a.originalPrice + a.terms)
      ) adjustedPrice,
  @adjustedShares :=
      if(@currentTransik = a.transik,
        if(a.multiply = 1, @adjustedShares / a.terms, @adjustedShares),
        if(a.multiply = 1, a.originalShares / a.terms, a.originalShares)
      ) adjustedShares,
  @currentTransik := a.TRANSIK currentTransik
from
  (
    select *
    from tradesAndActions b
    order by b.transik, b.ca_date, b.multiply desc
  ) a
;

By way of background, this is about taking a bunch of trades, and adjusting the prices to account for corporate actions, to answer the question: "what would the trade price have been if there was no corporate action".
I am pretty sure the query returns the desired result. However, I am wondering if it can be rewritten to not use any session variables, by way of window functions.

Going a bit deeper: some corporate action adjustments require you to add the term to the price, others require you to multiply the term and the price. The effect is cumulative for successive corporate actions. I have arranged for there to be a flag indicating whether a given adjustment term is a multiply type, or an add type.

The difficulty in using window functions here is that you only have things like

sum(x) over (partition by y ...)

or

exp(sum(log(x)) over (partition by y ...)

There is no way, as far as I know, to specify some cumulation function that does different things depending on whether or not you should multiply or add.

But perhaps there is some sort of multi stage approach that does the same thing?
Note: this is not a practical question in that I need some help to get a job done; I already have a working query. This is more a case of me exploring the limits of window functions. I am wondering if a window function would be more elegant. For some reason I have some unexplained preference for window functions over iterating session variables.


texts:
I did full vacuum on postgresql-9.4 database and I have a lot of tables with nonremovable row versions. I would like to know what are these or where I can read about them.

INFO:  vacuuming "public.profile_generals"
INFO:  "profile_generals": found 0 removable, 654 nonremovable row versions in 51 pages
DETAIL:  0 dead row versions cannot be removed yet.


texts: How to disable case sensitivity in SQL Server 2005 for object name (Table name)?

texts:
I have created a new database in SQL Server 2005 and execute generated script from the old database.

In that I have a table called MstCity

When I try to execute the following query which has the table name in uppercase MSTCITY

SELECT City FROM MSTCITY WHERE City = 'Junagadh'

I get an error

  Invalid object name 'MSTCITY'

When I execute this SELECT statement it works fine

SELECT City FROM MstCity WHERE City = 'Junagadh'

In the old database both SELECT statement work fine. And both database are in SQL Server 2005 only.


texts: Is it too much to ask Oracle to provide expiration dates with a list of backups?  I am not looking for expired backups.  I am looking for a list of expiration dates for each backup.


texts: Extracting an an integer value from an xml column

texts:
I'm just wondering how I can extract an integer out of an XML column. Currently all the digits are 4 which the below query is able to do. But the number is going to grow to 10,000's soon which will mean 5 digit and this query will not be able to do it. Is there anyway that I can dynamically get any integer out that is next in the middle of <siteID> and </siteID> from the Xml column. Any suggestion will be highly appreciated.

SELECT
SUBSTRING(msg, patindex('%[0-9][0-9][0-9][0-9]</siteID>%',msg),4)  AS DOMAIN
FROM table  a(NOLOCK)
WHERE msg like '<DLR%'
ORDER BY 1 DESC


texts:
We cloned a VM server and on Server A MySQL is located on the F drive, but on Server B it's located on the E drive. I updated the ini file and the Windows path variables, but it's still trying to pull the data, config file from the F drive.

Is there anyway to update the paths to get this to work?


texts: Importing datetime fields in "m/dd/yyyy" format using PostgreSQL COPY command

texts:
I'm looking to import a series of CSV files that contain a datetime field in the format of m/dd/yyyy h:mm:ss into a PostgreSQL table using the native /copy command.

I get the following error on import:

ERROR: date/time field value out of range: "5/13/2010 0:00:00"
HINT: Perhaps you need a different "datestyle" setting.

I tried setting the datestyle setting to ISO,MDY and SQL,MDY to no avail.I also attempted to modify the field type in the target table to character to force the fields to be read as strings, but that didn't work either.

Any ideas? Ideally I would like to have the data directly read into the target table as dates, but would settle for character strings. Thanks in advance!

Test Case

PostgreSQL Target Table:

CREATE TABLE testtable
(
    count_date timestamp without time zone,
    location character(40),
    count smallint
)
WITH (
    OIDS=TRUE
);

Data:

"count_date","location","count"
5/12/2010 0:00:00,"SEGMENT A",50
5/12/2010 0:00:00,"SEGMENT B",40
5/12/2010 0:00:00,"SEGMENT C",30
5/13/2010 0:00:00,"SEGMENT A",20
5/13/2010 0:00:00,"SEGMENT B",10
5/13/2010 0:00:00,"SEGMENT C",5


texts: Elevation data in a database (tens of billions of information)

texts:
I have a need of a service which gives me the elevation for a coordinate (latitude, longitude). There is a dataset called SRTM3 where you have this information in a list of files. Each file contains a representation of the elevation value in a 1201*1201 square and each elevation value is stored as a big endian integer. There are around 20 000 files in this dataset.

For example, for the latitude 50.4161 and longitude 14.12345, you would find the data inside one of these 1201*1201 values of the file N50E014.hgt.

My question is this : if I were to import all these data in a table, I would have around 20 000 * 1201 * 1201 = 28 848 020 000 records.

Is it the right way to go in storing this in a database? If yes, what system would you recommend? The only request I would have to make is something like:

SELECT elevation
FROM elevation e
WHERE e.min_latitude < latitude
AND e.max_latitude > latitude
AND e.min_longitude < longitude
AND e.max_longitude > longitude`

Or should I just load the data from the raw file directly on request? But it means loading a file on each request (I could keep some of the most used in memory but it does not seem efficient).

EDIT: I was thinking about something more around the line of a task queue than opening the file on each request but let's forget this and keep our focus on database system. @Paparrazi said that a conventional database can handle this much data and advised with a cluster index. I thought of that previously but in this case, once the data are loaded, the only operation will be a read (never or almost never a write). It seems kind of useless to use these conventional databases with lot of feature like transactions, ... when you don't need it. Is there another alternative ? In bigdata oriented database (cassandra, mongo, ...) ?

EDIT2: I am looking into @MickyT answer with postgis. It seems to fit my needs. I will try to import the data into the index clustered table or the postgis raster one, benchmark the 2 solutions and post my result here.


texts: Change bucket count for Primary Key Nonclustered Hash on existing In-Memory table

texts:
I'm on SQL Server 2019 Enterprise edition. I have a table with this definition
CREATE TABLE dbo.MyMemoryTable
(
    FirstId int NOT NULL,
    SecondId int NOT NULL,
    MyCount int NOT NULL,
    CONSTRAINT PK_MyMemoryTable PRIMARY KEY NONCLUSTERED HASH (FirstId) WITH (BUCKET_COUNT = 16000)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)

It's prefilled with data that are frequently queried and refreshed. There are few schema bound native compiled procedures.
While the bucket count was ok for some time and was 2x the number of unique values, it's not enough now and I'd like to change it to 32 768. I would like this operation to be online.
In my test environment, I used the following ALTER to change the bucket count:
ALTER TABLE dbo.MyMemoryTable
ALTER INDEX PK_MyMemoryTable REBUILD WITH(BUCKET_COUNT = 32768);

Which passes without errors, but scripting out the table still shows the previous 16k buckets
as well as DMV's
SELECT
  QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(h.object_id)) as [table],
  i.name                   as [index],
  h.total_bucket_count,
  h.empty_bucket_count,

  FLOOR((
    CAST(h.empty_bucket_count as float) /
      h.total_bucket_count) * 100)
                            as [empty_bucket_percent],
  h.avg_chain_length,
  h.max_chain_length
FROM
        sys.dm_db_xtp_hash_index_stats  as h
  JOIN sys.indexes                     as i
          ON h.object_id = i.object_id
          AND h.index_id  = i.index_id
JOIN sys.memory_optimized_tables_internal_attributes ia ON h.xtp_object_id=ia.xtp_object_id
JOIN sys.tables t on h.object_id=t.object_id
WHERE ia.type=1
ORDER BY [table], [index];

How to achieve this without downtime? I could drop and recreate the table, but I need to drop the schema bound procedures first and that would cause errors.
EDIT: Tested it on a newly created table and it worked.
I've added some data and it worked as well.
I've added more rows than the bucket count, still worked
I've added natively compiled procedure and it worked.
I've tested it again on my real table and it worked now. I have no idea what happened.
The only thing that is suspicious that when I ran the DMV query, it showed me MyMemoryTable but also something called TT___MemoryTable or something like that - maybe that prevented the update?


texts: Automate SQL Server install and restore DB

texts:
I’ve been tasked to automate our sql server express installation and with that, create a database from a .bak file.  I found a way to automate the install using a Configuration.ini file which works great.  The part I’m having trouble with is restoring a database as part of (or after) the process.

Does anyone know if this is even possible?  I know 3rd party tools like Red Gate can probably do it but they are rather expensive and the company I work for does not have a big budget for development tools.


texts: How do you confirm a scheduled event is working?


texts: MariaDB Slave cause "Copying to tmp table"

texts:
DB: MariaDB 10.0.23

STOP SLAVE;
run query
SHOW PROFILE;

Status  Duration
starting    0.000028
Waiting for query cache lock    0.000006

init    0.000004
checking query cache for query  0.000011
checking privileges on cached   0.000005
checking permissions    0.000007
checking permissions    0.000005
checking permissions    0.000005
sending cached result to clien  0.000154
updating status 0.000005
cleaning up 0.000003

After starting slave, long "Copying to tmp table" shows up:

START SLAVE;
run query  EXPLAIN outputs same query plan like before starting the slave.
SHOW PROFILE;

Status  Duration
starting    0.000046
Waiting for query cache lock    0.000007
init    0.000006
checking query cache for query  0.000109
checking permissions    0.000007
checking permissions    0.000005
checking permissions    0.000006
Opening tables  0.000020
After opening tables    0.000009
System lock 0.000007
Table lock  0.000005
After opening tables    0.000007
Waiting for query cache lock    0.000005
After opening tables    0.000031
init    0.000050
optimizing  0.000032
statistics  0.000139
preparing   0.000050
executing   0.000007
Copying to tmp table    0.000025
Copying to tmp table    1.760070   <<<<<<<<<<<<<<<<<<<<
Sorting result  0.003707
Sending data    0.000013
end 0.000005
removing tmp table  0.000261
end 0.000006
query end   0.000006
closing tables  0.000010
freeing items   0.000013
updating status 0.000068
logging slow query  0.000008
cleaning up 0.000005


texts: Update field1.table1 based on compound join between table1 and table2 - Oracle

texts:
I want to run update statement on Oracle 11g, but it fails with the following error:

  SQL Error: ORA-01427: single-row subquery returns more than one row
  01427. 00000 -  "single-row subquery returns more than one row"

Here's what I'm trying to do in pseudocode:

Update Table1
Set Flag_Column=1
Where
Table1.ColumnX=Table2.ColumnX
and
Table1.ColumnY=Table2.ColumunY

Is there a way to do this in Oracle SQL?

My code looks like this:

update Table1
set Flag_Column=1
where ColumnX=
    (select distinct Table1.ColumnX
    from Table1, Table2
    where Table1.ColumnX=Table2.ColumnX
    and Table1.ColumnY=Table2.ColumnY)
and ColumnY=
    (select distinct Table1.ColumnY
    from Table1, Table2
    where Table1.ColumnX=Table2.ColumnX
    and Table1.ColumnY=Table2.ColumnY)


texts: Oracle Text - Changing SYNC to MANUAL not working always

texts:
Oracle version is 11g. When the Oracle Text index is created, SYNC option is given as "ON COMMIT". Before inserting large number of records, SYNC is changed to "Manual" for performance reason. Below query is run using CallableStatement for this purpose

{call CTX_DDL.REPLACE_INDEX_METADATA('IXFTS_ABC_TABLE','REPLACE METADATA SYNC (MANUAL)')}

In some environments this works fine but in production environments the SYNC option is still "ON COMMIT". No error is thrown when this is not updated. The index is not a partitioned index. Any pointers to troubleshoot further will be helpful.

[Update] I could reproduce this in my local machine rarely. Looks like running the same sql 2 or 3 times is working. Not sure why this fails the first time.


texts: SQL Server 2008 - Partitioning and Clustered Indexes

texts:
So let me preface by saying I do not have total control over my db design, so a lot of the aspects of the current system cannot be changed for the purposes of this scenario.

Comments about how we should rethink aspects of the design are likely correct but unhelpful :)

I have a very large table, approx 150 fields wide and about 600m rows,  that drives a large number of processes.  This is in a data warehouse situation so we don't have ANY updates/inserts outside the scheduled load process, so it is heavily indexed.

A decision has been made to try partitioning this table, and I have some concerns about indexing a partitioned table.  I don't have any experience with partitioning, so any input or links are appreciated.  I couldn't locate specifically what I am after on BOL or msdn.

Currently we cluster on a field that we'll call IncidentKey which is a varchar(50) and not unique - we could have between 1-100 records with the same IK (no comments please).  We do often get new data on old IncidentKey records so it's not sequential either.

I understand I need to include my partition field, IncidentDate, in my clustered index key for the partition to work correctly.  I'm thinking it would be IncidentKey, IncidentDate.

The question is, how will the mechanics of a clustered index work on a 2 part key in a partitioned table, if a record in a "new" partition should be before a record in an "old" partition in the clustered index?

For example, I have 5 records:

IncidentKey    Date

ABC123        1/1/2010
ABC123        7/1/2010
ABC123        1/1/2011
XYZ999        1/1/2010
XYZ999        7/1/2010

If I get a new record for ABC123, 2/1/2011 it will need to be in the clustered index BEFORE XYZ999, 1/1/2010.  How does this work?

I'm assuming fragmentation and pointers, but I can't find any info on the physical storage and configuration of non-partitioned clustered indexes on partitioned  tables with dual-part keys.


texts: Job showing the running sign but not currently running

texts:
I have a script to get the currently running jobs:

-- get the running jobs
--marcelo miorelli
-- 10-dec-2013
SELECT sj.name
,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
--AND sj.name = 'JobName'
and not exists( -- make sure this is the most recent run
    select 1
    from msdb..sysjobactivity new
    where new.job_id = aj.job_id
    and new.start_execution_date > aj.start_execution_date )

when I run that on my server it says that there is no job currently running, however, when I look at the Job Activity Monitor, I see the picture below.

The second step of this job, runs a powershell script using a proxy.
you can see the second step here.

any reason why it would show the second step of my job as running?
how can I fix it?


texts: Performance degradation while updating tables having 10s of millions of records

texts:
I want to update tables ( my be 20-30 ) having 10s of millions of records each.

The problem is that it is taking too much time for the update process and also at that time CPU usage also goes very high. I want to do in such a way that it can not use much CPU while processing the data. If the processing time is increased then it will not be a problem for me but it should use limited CPU resources for processing ( updating ) the table. I am using PostgreSQL as database and server operating system is Linux.

Sample query of mine can be like this

UPDATE TEMP
SET CUSTOMERNAME =
  ( select customername from user where user.customerid = temp.customerid );


texts:
Amazon Athena database -- is there a way to generate an Data Schema for Amazon ML in an automated way
(e.g. a SQL command that would generate the Amazon-format)
Please see:
https://docs.aws.amazon.com/machine-learning/latest/dg/creating-a-data-schema-for-amazon-ml.html
NOTE:  I obtained an answer from AWS support; see below.
Although the reference above doesn't explicitly name the format as JSON, it looks very much like a JSON format to me.


texts:
I'm getting following error via Microsoft IIS 8' Error Log:

  Query execution was interrupted, max_statement_time exceeded

MySQL is 5.7.11-log running on Windows 2012 and per MySQL :: MySQL Server Version Reference :: 1.5 Option/Variable Changes for mysqld 5.7 max_statement_time was introduced in 5.7.4 and removed in 5.7.8, but error still there...

This feature with some changes was ported from Twitter MySQL patches Statement Timeout · twitter/mysql Wiki.

WITHOUT switching to MySQL fork maintained and used at Twitter, what are my other options?

Please advise.

* UPDATE *

MySQL Bugs: #81071: Query execution was interrupted, max_statement_time exceeded


texts:
I've got a simple temp table that I'm creating below

CREATE  TEMPORARY TABLE IF NOT EXISTS Helper0910912001524518044820 (
    usergroupID INT,
    StoreID INT ,
    INDEX st (`usergroupID`)
    ) engine=memory AS
SELECT  usergroup0.*
    from
    (
        SELECT  156825 as usergroupID,
                MVStoreID as StoreID,
                1 as assignmentCount
            FROM  MVMasterEntity_AttachedStores
    ) usergroup0 ;

/* queryTime - 0.5284 seconds */

I then query against it with the following query....

Select usergroupID, group_concat(StoreID) as Inspects
    from Helper0910912001524518044820
    group by usergroupID;

but am not having any luck with getting this query to use indexes.  I've tried the following indexes.

( StoreID ) , ( StoreID, UsergroupID ), ( UsergroupID ), ( UsergroupID, StoreID )

The explain that I get is always the same.

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  AssHelper0910912001524518044820 ALL                 903452  Using filesort

Any help explaining why I can't use indexes for this query would be greatly appreciated.


texts: Get multiple columns from a select subquery

texts:
SELECT
   *,
   p.name AS name,
   p.image,
   p.price,
   (
       SELECT ps.price
       FROM product_special ps
       WHERE p.id = ps.id
         AND ps.date < NOW()
       ORDER BY ps.priority ASC, LIMIT 1
   ) AS special_price,
   (
       SELECT ps.date
       FROM product_special ps
       WHERE p.id = ps.id
         AND ps.date < NOW()
       ORDER BY ps.priority ASC, LIMIT 1
   ) AS date
FROM product p LEFT JOIN product_special ps ON (p.id = ps.id)

As you can see I'm repeating the same subquery just to get another column out. I'm wondering is there a better way of doing this?

id is the primary key in both tables. I've no problem making product_special.priority unique if that can help.


texts:
Can anyone help me?

I want to use query using CASE WHEN with logic where time less than 03:00 PM on Monday until Friday will get value start from tomorrow 03:00 PM until today 03:00 PM

  SELECT SUBSTR (A.TXT_TXN_DESC, 7, 15)                  AS billing_no,
         TO_CHAR (dat_txn, 'dd/mm/yyyy hh24:mi:ss.')     AS waktu,
         ''                                              AS note
    FROM ncbshost.v_ch_nobook A
   WHERE     cod_drcr = 'C'
         AND (CASE
                  WHEN (SELECT TO_CHAR (SYSDATE, 'dd/mm/yyyy hh24:mi:ss')
                          FROM DUAL) <=
                       TO_CHAR (SYSDATE, 'dd/mm/yyyy') || ' 15:00:00'
                  THEN
                      dat_txn BETWEEN (CASE
                                           WHEN (SELECT TRIM (
                                                            TO_CHAR (SYSDATE,
                                                                     'DAY'))
                                                   FROM DUAL) =
                                                'MONDAY'
                                           THEN
                                               TO_DATE (
                                                      TO_CHAR (SYSDATE - 3,
                                                               'dd/mm/yyyy')
                                                   || ' 15:00:00',
                                                   'dd/mm/yyyy hh24:mi:ss')
                                           ELSE
                                               TO_DATE (
                                                      TO_CHAR (SYSDATE - 1,
                                                               'dd/mm/yyyy')
                                                   || ' 15:00:00',
                                                   'dd/mm/yyyy hh24:mi:ss')
                                       END)
                                  AND TO_DATE (
                                             TO_CHAR (SYSDATE, 'dd/mm/yyyy')
                                          || ' 15:00:00',
                                          'dd/mm/yyyy hh24:mi:ss')
                  ELSE
                      a.dat_txn BETWEEN TO_DATE (
                                               TO_CHAR (SYSDATE, 'dd/mm/yyyy')
                                            || ' 15:00:00',
                                            'dd/mm/yyyy hh24:mi:ss')
                                    AND TO_DATE (
                                               TO_CHAR (SYSDATE, 'dd/mm/yyyy')
                                            || ' 23:59:59',
                                            'dd/mm/yyyy hh24:mi:ss')
              END)
ORDER BY SUBSTR (A.TXT_TXN_DESC, 7, 15) ASC


texts: profile name not valid when creating SQL server 2014 availability group

texts:
I tried creating an availability group but keep encountering this error during the validation checks

  Create failed for Database 'HADR Seeding Test
  2d074a6f-c035-4046-ae4e-045436ca56c5'.

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  profile name is not valid (Microsoft SQL Server, Error: 14607)


texts: Format timezone to +-#### instead of +-##

texts:
I am curious if there is a way to configure Postgres (9.1+) to format the TZ part of a "timestamp with time zone" as +-####, just like PHP's date.format method does using "O".

PHP Docs:

  O | Difference to Greenwich time (GMT) in hours | Example: +0200

As requested an example:

create table example (time1 timestamp with time zone);
insert into example values ('2000-01-01 00:00:00+0200');
select * from example;
         time1
------------------------
 1999-12-31 23:00:00+01
(1 rows)

-- How can I return the time as it was inserted (with it's timezone).
select time1 at time zone 'CEST' from example;
      timezone
---------------------
 2000-01-01 00:00:00

-- This is the correct time,
-- but I would like to attach the timezone to it
-- and return '2000-01-01 00:00:00+0200'

Thanks