texts: Postgres looped table (start sequence from 1 and rewrite old rows)

texts:
I want table to contain logs and be looped, so when it came to last key in sequence it will start over from one and will rewrite old logs.

I can do it if i mark sequence  as cycle and do requests like that:

INSERT INTO logs (initiator_id, target_id, action, message) VALUES ($1, $2, $3, $4)
ON CONFLICT (id) DO UPDATE SET target_id=$2, initiator_id=$1, action=$3, message=$4;

What i don't like, is that i need to repeat same inserts if there is conflict (line 2)

What i'am curios about, is it a good way to do, or there is other more practical ways for tables that you need clear lets say every 3 mounts?

Or maybe there is a way to generate unique id without using sequences?

For now i decided to make 2 columns primary key (date, initiator_id) as log is always 1 action at a time.


texts: Stored procedure to output date differences

texts:
I have a table of records which, in very simplified form, comprise a pair of dates, well actually let's say three dates:

ID
creation_date
process_date
done_date

and I am required to output records of counts as follows:

ID  P=C  P=C+1   P=C+2+   D=C   D=C+1   D=C+2+

where "P=C+1" for example is the number of records with a specific ID for which process_date equals creation_date + 1, and similarly for the other columns (and C+2+ indicates two or more days after creation_date).

So being a bit shaky with stored procedure syntax, and even the SQL statement required for this, using temp tables (presumably), I would welcome any help.

Typical output might be as follows, grouping by ID and using vaguely plausible values off the top of my head:

ID   P=C  P=C+1   P=C+2+   D=C   D=C+1   D=C+2+
XY   130   29       4      140    13
AB    16    3               27    19       2
   :::::::::::::::::::::::::::::::::::


texts: How to massively change string capitalization in a table?

texts:
I have a project where authors' names have been inserted in all capital letters (ex. JOHN KRAUS, LEONARD MILES, NAOMI DAWSON). Now I need these values in "normal" case (ex. John Kraus, Leonard Miles, Naomi Dawson)
How can I achieve that?


texts: How to get all the users on a database with SA access?

texts:
I am new to SQL database and besides running basic queries I don't know much. Trying to figure out how to get users with SA access.

Can anyone guide me through this, will really appreciate the help.


texts: Are the cost percentages in this SQL Server plan over 100% for a valid reason?

texts:
I'm looking through the plan cache, looking for low-hanging optimization fruit and came across this snippet:

Why are many of the costs listed above 100% ?  Shouldn't that be impossible?


texts: Index Optimize cause DB size increasement

texts:
After Index Optimize the database size increased from 114 to 140Gb, is this normal?

Server RAM usage do not reduced after the process finishing, and stay at 62Gb/98%


texts: Speed impact of using varchar in SQL Server 2008

texts:
In the old days, using varchar over char was a big no no, since the varchar was stored on a different page than the rest of the row. Is this still the case today with SQL Server 2008? Will I see any memory/performance gain from changing 1-3 varchar(5) columns to char(5) columns on a table with

a) say 2.000.000 rows
b) if any of the varchar columns are part of indexes / the primary keys


texts: When converting a replica set with an arbiter to a shard, does the arbiter also get added to the shard?

texts:
I'm converting an existing replica set that has an arbiter to a shard as shown in this guide:

https://docs.mongodb.com/v3.2/tutorial/convert-replica-set-to-replicated-shard-cluster/

and it's unclear if the arbiter needs needs to be included in the sh.addShard() method as shown here:

https://docs.mongodb.com/v3.2/tutorial/convert-replica-set-to-replicated-shard-cluster/#add-initial-replica-set-as-a-shard

I'd guess that it doesn't since it doesn't carry a copy of the data but I couldn't find a distinct answer one way or the other.


texts: SQL Server : primary keys advice to my whitepaper needed

texts:
I've tired to explain to every new junior developer in our r&d team why he should use Primary Keys and how to do that. So I decided to write small whitepaper, which every new developer should read. Here is the draft of it.

DISCLAIMER:

I know and understand the difference between clustered index and primary key
In the following question PRIMARY KEY means PRIMARY KEY CLUSTERED, and WITHOUT PRIMARY KEY means WITHOUT PK AND CLUSTERED INDEX
Note that it is a whitepaper for junior and not SQL programmers
All the things that they do - reviewed before entering to the main development branch
I'm not going to explain to them - when there will be benefit of using clustered index and NONclustered primary key - else I will fall to premature optimizations hell

The question is - what else should I add to the document? May be - what I should change in it? And what to explain more in detail?

Here comes the draft:

Primary keys have to constrain any table within database.
Without PK a table considered as a heap and SQL Server has very limited uses of this type of data - the only thing I should say it is suitable - a buffer for fast bulk-loading data from the outside of SQL Server's engine.
Avoid using natural primary keys. Primarily because of theirs natural (gauss) distributions. For example in PhoneBook table with primary key based on Family and Name will be many Smiths and Wilsons and much fewer Zimmerbergs and this states, that pages containing Smiths and Wilsons will be splitted more often, than other pages, and queried also more often - which multiplies the performance impact. That leads to performance degradation, because of primary key's page fullness and most of searches will hit PK's sparsed pages.

Moreover, even using SSN or ID Number, which have (I hope) flat distribution, as PK does not solve the problem of PK's page splitting, because this numbers are not in any organized order.

Secondary - natural PKs are often composite. That creates composite foreign keys and wide indexes and as a result hurts performance. So, ...
Avoid using composite PKs. Better using simple surrogate PK and composite UNIQUE index, than all-in-one composite PK because it leads to composite FK and wide indexes due to statement, that every secondary index on table with PK has to include whole PK within.
Avoid using surrogate primary keys other than integer or uniqueidentifier types
During database design phase it is very important to identify entities and corresponding tables which may have in perspective "zillions" of rows, or tables which keys have to be NOT ONLY table-wide unique, but DB or even world unique, or which have to be joined with other tables by this key over several hop-tables - this tables better to have UUID PK, others - ordinary integer, because SQL Server very well fine tuned to use integer PKs.
These two types can guarantee both vector distribution (IDENTITY or NEWSEQUENTIALID()) and monotonous sequence.
Main database design rule - Twenty minutes spent to well thought-out design will save days or even weeks during production database's maintenance


texts:
I have a basic rhetorical/theoretical question for people experienced using both MongoDB and RDBMS. Surprised SO has no Data Munging or Data Engineering forum.

In making an aggregation pipeline in Mongo, we use $unwind to explode nested array values so each has its own document so that we may group or do other aggregation.

To me this smells just like getting the data to First Normal Form. The documents tend to look just like records in 1NF at this stage. It seems like that is the goal. I have searched and searched and not found that phrase uttered in any Mongo topics, certainly it was not said in courses I took in MongoDB University. And this would apply to other non-relational data systems as well.

Is 1NF essentially the same thing as the data form necessary for an aggregation step? Is there any data manipulation term for it?


texts: Find month to month percentage growth rate


texts: Recover two weeks old stored proc which has changed since

texts: I accidentally changed a stored procedure on Microsoft 2016 SQL Database. Then I noticed the change after complaints from the reporting side. Made few changes and run but didnt work. Not it seems I need to recover the stored proc 2 weeks ago. Can someone explain the process.


texts: When setting up security for linked servers, what does the Impersonate checkbox do?


texts: Insert Into Existing Table Using OpenQuery

texts:
I am attempting to insert data into a linked server using openquery.  I have attempted the below syntax, but get an error of

  Cannot get the column information from OLE DB provider "MSDASQL" for linked server "Aurora".

What is the proper way to write this syntax so that my update statement will execute as it should?

INSERT INTO OPENQUERY(AURORA, 'Select firstname, lastname, address, city, state from testdb.address_info') VALUES
('jo', 'bob', '11111 no where drive', 'city1', 'state1')


texts: Update Table Query: Automatically handle "Enter Parameter Value" prompts?

texts:
EDIT: Here are two database Files that demonstrate an applied example of the problem:

  Example_DBs.zip (Zipped due to 2 link limitation.)

Note: The missing Field in "Not Working.mdb" is intentional. Please have a look at the attached diagram for further clarification.

I'm trying to create a single, Update Query in Access 2007 that does all of these 3 things:

Update (skipping zero-length values)
Append
Automatically handle missing Fields without showing any "Enter Parameter Value" prompts.

I've managed to accomplish the 1st & 2nd items on my own, but I'm stumped on the 3rd. In essence, what I'm looking for is a way of telling Access to automatically use a zero-length/null value for any missing Field(s) the Query encounters, so long as it does not involve manually modifying the Table beforehand. Automation is key. And that's where you guys come in.

Here is a diagram of the desired results from a set of sample Tables:

And here is the Update Query's SQL code adjusted for those sample Tables:

UPDATE Table_1 RIGHT JOIN Table_2 ON Table_1.ID = Table_2.ID SET Table_1.ID = NZ([Table_2].[ID],[Table_1].[ID]), Table_1.Company = NZ([Table_2].[Company],[Table_1].[Company]), Table_1.Address = NZ([Table_2].[Address],[Table_1].[Address]), Table_1.Phone = NZ([Table_2].[Phone],[Table_1].[Phone]);

Any help would be greatly appreciated.


texts: Best Azure service for large no of SQL Server databases

texts:
We have a dedicated SQL Server machine for a SAAS application with about 200+ databases, the server has E5-2620 with 64 GB RAM. Now we want to move all the databases to Azure, so we're looking into the options: we can either go with a Virtual Machine like (DS13 v2) which can support all these databases, or we can use Azure SQL Elastic Pools like Standard with 400 eDTUs.

My question is: in the long run, and from a management and scalability point of view, which option is better? And also will the Standard Elastic Pool with 400 eDTU's provide the same performance as the virtual machine?

One of the major concerns for us is the performance of the SQL databases in Azure, as this is one of the reason to move the whole application to the Azure so that in the future as the no of users grow we can easily upgrade our instances to cater the increased load.


texts:
MySQL restore bd from directory copy.
Hello, I recently lost the external storage where my MySQL data directory was located, but I have copied the entire data directory in another location, the problem is that I copied it without stopping mysql before, I would like to know how to get mysql from the directory data where information is saved before the crash


texts: MySQL trigger before insert replace the data with relational table

texts:
I have 2 table orders and transactions

I want to write trigger in orders table, which will replace the value of currently inserting row (orders table INSERT query) with transactions data and also update a flag of transactions table.

MySQL query for insert is

INSERT INTO `cl201_orders`(`order_id`, `transaction_id`, `transaction_ammount`, `gateway_id`, `name`, `address`)  VALUES ('a23', NULL, NULL, NULL, 'John', 'NZ');

MySQL query for transactions data selection will be

SELECT `transaction_id`, `transaction_ammount`, `gateway_id`
FROM `transactions`
WHERE `order_id` = 'a23'
  AND gateway_id IS NOT NULL
  AND active = 1
ORDER BY `transaction_id` ASC
LIMIT 1;

Before order Insert

transactions table

transaction_id | order_id | transaction_ammount | gateway_id | active
----------------------------------------------------------------------
123            | a23      |  10                 | NULL       | 1
252            | a23      |  33                 | 23         | 1
321            | a23      |  25                 | NULL       | 1
431            | 1cd      |  50                 | 45         | 0

orders table

order_id | transaction_id | transaction_ammount | gateway_id | name | address
-----------------------------------------------------------------------------
1cd      | 431            | 50                  | 45         | Roy  | IN

After order Insert

transactions table

transaction_id | order_id | transaction_ammount | gateway_id | active
----------------------------------------------------------------------
123            | a23      |  10                 | NULL       | 0
252            | a23      |  33                 | 23         | 0
321            | a23      |  25                 | NULL       | 0
431            | 1cd      |  50                 | 45         | 0

orders table

order_id | transaction_id | transaction_ammount | gateway_id | name | address
-----------------------------------------------------------------------------
1cd      | 431            | 50                  | 45         | Roy  | IN
a23      | 252            | 33                  | 23         | John | NZ

Table structure:

CREATE TABLE `prefix_orders` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `order_id` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
    `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `customer_email` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
    `transaction_ammount` decimal(10,2) NOT NULL DEFAULT '0.00',
    `transaction_id` int(11) unsigned DEFAULT NULL,
    `gateway_id` int(11) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `order_id` (`order_id`),
    KEY `gateway_id` (`gateway_id`),
    KEY `transaction_ammount` (`transaction_ammount`),
    KEY `transaction_id` (`transaction_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `prefix_transactions` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `transaction_id` int(11) DEFAULT NULL,
    `order_id` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
    `gateway_id` int(11) DEFAULT NULL,
    `transaction_ammount` double(10,2) DEFAULT NULL,
    `active` tinyint(1) DEFAULT '1',
    PRIMARY KEY (`id`),
    UNIQUE KEY `transaction_id` (`transaction_id`,`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I can achieve this at PHP end but I'm curious to know how I can do it with trigger

Please Note:

For The Sake Of Simplicity I have omitted several column from both the tables.
MySQL version: 10.0.24


texts: Replicate only some tables of MariaDB database

texts:
I currently have a ready-made structure cluster with MariaDB Galera with two servers that keeps both updated database. However I now have the need to create a new similar structure but with one difference, keeping only a few tables updated in the other server (which will be master also put to another application access).

How can I do that?
Can I Keep two masters but with partial synchronization?
Do I have to use another tool?


texts:
We have a Windows 2012 VM running SQL Server 2012. There are around 20 databases with a total size of 5.83 TBs. There is 64GB of RAM and 16 logical processors on the box.

The server is extremely busy with a lot of users hitting the databases with queries during 7 - 6 window and a lot of jobs hitting it in the evening after 6. Most of the jobs run integration server packages that are stored in MSDB. On top of all this there is snapshot replication on one dB and backup/restore on 2 other databases every night.

Now, there is a need to load balance on the server, we have been talking on a couple of points which are below:

Separate into two servers, A & B, A only runs jobs and B is for users, every night back databases from A and put them on B.
Always On Availability Groups with readable secondary.
Separate Integration Services and database engine into 2 servers. We have earlier separated SSRS and SSAS from database engine.
Have two servers with heavy load dBs on one server and other dBs on other.

Which approach would you guys go with?

Approach 1 & 2 -- are kind of automatically out of the picture as users need read/write access. They not only read data from the databases but also write the data in their own schema.

Approach 3 -- separate SSIS & database engine -- I am against this as I have noticed that IS does not take more that 2GB of memory at its heaviest load and not to forget that even though we separate IS & engine the IS will still be hitting the databases and will be locking tables which users are trying to read. I agree that putting it on different server will allow IS to have its own processors and memory but whatever little performance we see will be offset by transferring data over the network.

Approach 4 -- is seeming most feasible with taking 1/4th of busy databases on other server and keep 3/4th of less busy one on same server.

I have allotted 50GB to the database engine and left 14GB for OS and other processes.


texts: SQL Server Trigger does not update all table records

texts:
This is my first question so please go easy on me. We are using Access 2013 as a front-end and SQL Server 2012 with MS SQL Server Management Studio 11.0.2100.60 backend with ODBC connections.

I am trying to write a series of triggers to update a couple of different tables. When I paste the data manually in Access (our front-end) the trigger only updates 71 of 80 records of the sample data. If I disable all triggers and pull out only the query portion all of the records (80) are updated. Why is my trigger not updating the entire table?

Here is the trigger:

ALTER TRIGGER [dbo].[TRIG_Update_Inventory_Ind_Trees_Abs_Plot_Numb]
   ON  [dbo].[Plot_Location]
   AFTER INSERT
AS
    if @@rowcount = 0
RETURN
    UPDATE Inventory_Ind_Trees
    SET Inventory_Ind_Trees.Abs_Plot_Numb = Plot_Location.Abs_Plot_Numb
    FROM Plot_Location
    WHERE Plot_Location.Invent_Plot_ID = Inventory_Ind_Trees.Invent_Plot_ID

The portion I run as a stand alone query that updates the entire table is here:

UPDATE Inventory_Ind_Trees
SET Inventory_Ind_Trees.Abs_Plot_Numb = Plot_Location.Abs_Plot_Numb
FROM Plot_Location
WHERE Plot_Location.Invent_Plot_ID = Inventory_Ind_Trees.Invent_Plot_ID

The records that are not updated are left as NULL. I am able to change them manually but I want this to be an automatic process.

Any suggestions where to look for the answer, or any help pointing me in the right direction would be greatly appreciated.

I only want to update the new records that have been put into the Inventory_Ind_Trees table with the corresponding Abs_Plot_Numb values from the Plot_Location table, but I was not quite sure how to do that.


texts: SQL Server 2012 running background task that fills memory

texts:
In my installation of SQL Server 2012 on my development machine (Win8.1), the process "SQL Server Windows NT - 64 Bit" is running continuously, using up disk resources and filling up my memory over time, even though I'm doing nothing. If I reboot the computer or restart the service called SQL Server (MSSQLSERVER), then the memory allocation is reset and immediately begins to accumulate again.

How can I identify the operation that SQL Server is running to cause this usage? I don't recall changing any operations or configuration.

Edit:
The problem is not just that memory is filled, but also that it continually uses half my disk operation speed (the "Disk" column in Task Manager Processes is at 50-75%).  There appears to be an ongoing operation that perhaps has a memory leak.  In other words, I am doing no activity and sitting watching the Task Manager and the memory is going up steadily.


texts:
I have setup MySQL with the default conf /etc/my.cnf as following:

[mysqld]

user=root

innodb_file_per_table

innodb_file_format=Barracuda

innodb_large_prefix

log_bin_trust_function_creators

sql-mode='NO_AUTO_CREATE_USER'

max_sp_recursion_depth=10

show_compatibility_56=ON

performance_schema

character-set-server=utf8mb4

[client]

socket=/home/abc/mysql_home/socket

[mysql]

default-character-set=utf8mb4

Accordingly, when I run select @@global.sql_mode, @@session.sql_mode, @@sql_mode; from Linux machine, I see :

NO_AUTO_CREATE_USER | NO_AUTO_CREATE_USER | NO_AUTO_CREATE_USER

1 row in set (0.00 sec)

Where as if I connect to the same instance and same user from the Windows machine (SQL developer), I see the following :

 NO_AUTO_CREATE_USER |STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER |STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER

1 row in set (0.00 sec)

Basically, mode STRICT_TRANS_TABLES is getting added for the session variable though I have not made any session level updates. This MySQL strict mode is causing further issues.

Would like to know how this STRICT_TRANS_TABLES is getting added by default only for the remote connection?

Commands used for creating the user :

CREATE USER 'USER_MYSQL'@'localhost' IDENTIFIED BY 'USER_MYSQL';
GRANT ALL PRIVILEGES ON *.* TO 'USER_MYSQL'@'localhost' WITH GRANT OPTION;
CREATE USER 'USER_MYSQL'@'%' IDENTIFIED BY 'USER_MYSQL';
GRANT ALL PRIVILEGES ON *.* TO 'USER_MYSQL'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'USER_MYSQL'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Using MySQL 5.7 version.


texts: Store SSRS Report export directly to database

texts:
As a requirement, I have to check for a certain condition on some data in the database, if the condition is true, then I have to create a pdf report and store it on a table.

I have everything set up as a T-SQL SP except the pdf report part.

Is there anyway to execute an SSRS Report, export it to PDF and store the pdf in a varbinary column directly from T-SQL ?

Otherwise the only solution would be creating a Windows Service to do this (I Know how to do this but the T-SQL way would give me more flexibility).

Thanks!


texts: MySQL 5.6 events are created disabled and they don't run

texts:
I am using the normal syntax to create an event, but they are created disabled and they never run:

create event p1 on schedule at now() on completion preserve enable do call proc1();

I added enable although it is default to solve this problem, but no good as well, and I need to preserve the event such that I can find it show events / select * from information_scheme.events;

I appreciate your help very much with any privileges required, configuration, or syntax to solve this problem.


texts: Generic or better yet custom message of the day in Oracle sql plus

texts:
I'm trying to create a message of the day type notification when users login to sqlplus.

Currently I use the login.sql to add a dbms_application_info and set a module and action for the message.  This has limitations so I was wondering if Oracle has anything like a MOTD feature. Or maybe someone has created their own solution.

Thanks in advance.


texts: Split 100 million row table into multiple tables

texts:
I have a SQL Server 2012 database. There is one table that has more than 100 million rows. I want to split that table into multiple similar tables, with 1 million rows in each table. What is the best way to achieve this quickly?

I have tried paging, but it takes too much time, and sometimes gives an out of memory exception in SSMS.

There are 12 columns. 11 are varchar and one is datetime. There is no primary key available in the table, and no ID column. It is a very old database table that we migrated to SQL Server 2012.

Its size is around 20 GB. There are no indexes available. I don't want more complexity, I just need to split it.


texts: how to get a better execution plan for empty sets?

texts:
I have a query I'm trying to optimize, something like this:

SELECT 1 FROM HugeView
WHERE (Col1 = 'a' AND @val = 1) OR (Col2 = 'a' AND @Val = 0)

(where Col1 and Col2 are on different tables)

If I hardcode the @Val value (1/0) - SQL Server knows to build an execution plan in which it only accesses the relevant table for either Col1 or Col2.

But when using a variable all the tables in HugeView are accessed.

Can you advise on a way of doing it in a way that would help SQL Server not access the unecessary table?

Limitations:

Can't use option (recompile)
Can't encapsulate this code in an SP
Can't change HugeView

Unfortunately all aforementioned limitations are part of the product's design and/or dev scope - nothing I can do about that - just work within what I have.

I do however know that @var is either 1 or 0, and can create and query (and join on) temp or "real" tables as I like.

Also notice I don't select from the tables of Col1 or Col2 - I only use them for filtering.

I tried creating and joining on empty tables, or top(@somevar) depending on @var's value - but didn't help.

UNION ALL won't help as in any case the optimizer doesn't know the value of @val. Also can't change the query - if I could then of course all would've been solved.


texts: Importing a SQL Dump File through XAMPP's Command Line

texts:
A few days ago, I exported a .SQL dump file with all my databases, which is about 150MB. The thing is I cannot import it through phpMyAdmin since the file is way to big.

I remembered there was BigDump and I tried to use it, since it's ideal for such big imports as mine. The problem is that I need to set a database to import the dump file on, which is something I cannot do, since the dump file contains some databases I dumped, meaning that BigDump needs a database to be configured with so it can import the file.

I've been searching around and found out that one option could be using XAMPP's mysql.exe command line, which I already tried to, but since the import command requires a database to make the import, it makes it impossible.

Do you have any another idea or know how to make an import without the need to provide a database?

Regards and thanks in advance!


texts: Postgres drop type XX000 "cache lookup failed for type"

texts:
I'm currently working on a PostgreSQL 9.2.x Database with a lots of Clients, and tables and functions. We deploy code constantly and sometimes it is necessary to even drop a type or a function due this deployment.

Example:

1.Script to create the needed functions in the first place

CREATE TYPE tmp._myEnum AS ENUM ('OLD', 'NEW', 'BOTH');

CREATE OR REPLACE FUNCTION tmp._get_status()
RETURNS tmp._myEnum AS
$BODY$
BEGIN
    RETURN 'OLD'::tmp._myEnum;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER COST 10;

CREATE OR REPLACE FUNCTION tmp._my_testfunction()
RETURNS VOID AS
$BODY$
BEGIN
    CASE tmp._get_status()
        WHEN 'OLD'::tmp._myEnum THEN
            RAISE INFO 'myEnum is OLD';
        WHEN 'NEW'::tmp._myEnum THEN
            RAISE INFO 'myEnum is NEW';
        WHEN 'BOTH'::tmp._myEnum THEN
            RAISE INFO 'myEnum is BOTH';
        ELSE
            RAISE INFO 'myEnum has an unexpected value';
    END CASE;

    FOR i IN 1..10 LOOP
        RAISE INFO 'Step [%]',i;
    END LOOP;
    RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER COST 10;

2.Scenario that leads to the exception:

a)One Client is constantly using tmp._my_testfunction() like this

SELECT tmp._my_testfunction()

b)In order to deploy a change to the Composite type i execute in another session

DROP FUNCTION IF EXISTS tmp._get_status();
DROP TYPE IF EXISTS tmp._myEnum;

CREATE TYPE tmp._myEnum AS ENUM ('OLD', 'NEW', 'BOTH','NOTHING');

CREATE OR REPLACE FUNCTION tmp._get_status()
RETURNS tmp._myEnum AS
$BODY$
BEGIN
    RETURN 'OLD'::tmp._myEnum;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER COST 10;

c)The Client that is constantly using tmp._my_testfunction() imidiatly throws

ERROR:  cache lookup failed for type 386318
CONTEXT:  PL/pgSQL function tmp._my_testfunction() line 3 at CASE

How can I prevent that?


texts: Database planning... avoiding parallel fields

texts:
this is my first post here.

I'm a programmer and lately I've had an interest in databases.  I'm working on a side project and would like some tips as how to best construct my database.

Here's the scenario:
I am attempting to see how the '6 degrees of seperation' theory holds up on social media.
I will have a scripted social media account which will act in much the same way as a web crawler... It will visit a social media account and catalog every UserID and UserName into a database file, UserID acting as the primary key.  I would like to have a table (or collection of tables) which will keep track of which UserID's were linked to which account... i.e. UserID 12345 had links to/from UserID's 44444 and 55555 on their page.

The problem is, in my database classes I'm told it is bad practice to have parallel fields or columns that hold more than one attribute.  I would like to avoid this.

From what I described UserID 12345 would have either two entries in one column(44444 & 55555), or two parallel fields holding one userID each.  What is the best way to work around this?


texts: How to restore single table in Sql Server 2016

texts:
I'm wondering how I can restore a single table in SQL Server 2016.
For example,
I backup the table using SSMS task script option.
Delete a row from that table.
Now I want to restore the table to bring the deleted row back, How can I do this in SSMS?
Thanks!


texts: What is the best practice for storing large data when nvarchar(4000) is sufficient 99% of the time?

texts:
I have a table with about 100MM rows that stores information about a user.

CREATE TABLE [dbo].[UserData](
        [UserDataID] [int] IDENTITY(1,1) NOT NULL,
        [UserID] [int] NOT NULL,
        [DataId] int NOT NULL,
        [DataValue] [nvarchar](4000) NOT NULL,
        [EncryptedDataValue] [varbinary](max) NULL)

I need to be able to store data in DataValue that is > nvarchar(4000). But, only a very small percentage of the rows actually need this column as nvarchar(max). I know that as soon as DataValue is over nvarchar(4000), internally, sql will store the data as a blob[?], substantially increasing the time it takes to make this change. (not sure how read/write time will be affected later).

I thought of a few potential options...

Change DataValue from nvarchar(4000) to nvarchar(max) and just eat the time it takes to make the change; not worrying that only 1% of the rows are using MAX?
Alongside DataValue, add a DataValueXL column that is nvarchar(max) and introduce application logic to save in the appropriate column depending on the size of the data? (Marking both as NULL)
Create a new table FKed to UserDataId to store only large DataValues > 4000?

Which--if any--should I go with?

thanks


texts: Postgres 9.2 select multiple specific rows in one query

texts:
I have a table with three columns,

Id Serial.
Value real.
timein Timestamp.

I want to select the values based on a range of days ie. from two days ago until now. The table may contain one or two rows per day, if it does contain two rows, I want to select the second row plus all the rows following it in the period of time.

To clarify:

id | value | timein
1  | 20.5  | 2013-04-25 11:25:42
2  |  4.2  | 2013-04-26 09:10:42
3  |  3.1  | 2013-04-26 15:45:42
4  |  100  | 2013-04-27 14:52:42
5  | 15.5  | 2013-04-28 17:41:42
6  | 23.3  | 2013-04-29 07:32:42
7  | 21.4  | 2013-04-29 14:32:42

If I wanted to select the values from day 26 (only the second row) plus all the values until day 29 and so on, can it be done in one query?


texts: Remotely Connect to MySql from MS Access

texts: Does anybody know if it is currently possible to remotely connect to a MySql server from MS Access? What does one need to do to go about doing this?


texts: Diferent result between query and application (MySQL)

texts:
I have this scenario:

I have this query:

SELECT min(id)
  FROM ( SELECT c.id
              , id_Cliente
           FROM creditos AS c
          INNER JOIN  creditos_ren AS r
                  ON r.id = (SELECT rr.id
                               FROM creditos_ren AS rr
                              WHERE rr.id_credito= c.id
                                AND rr.cancelado=false
                                AND rr.fecha_vto>='2018-07-15'
                                AND rr.fecha_vto<'2018-07-24'
                              ORDER BY rr.fecha_vto
                              LIMIT 1
                            )
          LEFT JOIN  creditos_ren AS rx
                 ON rx.id =(SELECT xx.id
                              FROM creditos_ren AS xx
                             WHERE xx.id_credito=c.id
                               AND xx.id != r.id
                               AND xx.fecha_vto >= r.fecha_vto
                               AND xx.cancelado=false
                             ORDER BY xx.fecha_vto
                             LIMIT 1
                           )
          WHERE c.id NOT IN ( SELECT d.id_credito
                                FROM campanas_instancia_lineas AS d
                            )
            AND c.gestion_local = 0
            AND c.enumerador IN ('CR_NORMAL','CR_ENTREGADO','CR_NUEVO','CR_MOROSO')
          ORDER BY r.fecha_vto
                 , c.total_vencido desc
                 , rx.fecha_vto
        ) AS p
 GROUP BY id_Cliente;

When run the query in the WorkBench, the result for example are 260 matches.

But, when I run the same query in the application the result are 497 matches.

I use javaEE with nativeQuery.

I try to use VIEWs, with the same result

If someone help me, please.

Thanks in advance


texts: Cannot Start SQL Server Reporting Service

texts:
That's the first time I use SSRS so my apologies if I'm asking something stupid.

I have a SQL Server 2008 R2 already installed with SSRS. When I try to start the SQL Server Reporting Service I receive this error:

  System.ServiceProcess.TimeoutException: Time out has expired and the
  operation has not been completed.    at
  System.ServiceProcess.ServiceController.WaitForStatus(ServiceControllerStatus
  desiredStatus, TimeSpan timeout)    at
  ReportServicesConfigUI.Panels.ServerInformationPanel.StartStopServiceTask(Boolean
  start)

Can someone help me with this issue? Which tests cna I do in order to debug/fix it? Thanks in advance!


texts: A Common Username for Both Database hosted in Azure

texts:
I have 2 azure databases created in one SQL Server. I.e.

Database 1 = db1 in xsqldb01.database.windows.net
Database 2 = db2 in xsqldb01.database.windows.net

I would like to create a username or user login where I can read only data (Select grant only) of both databases. How can I achieve this? Do I need to create a user login with same name in both database?


texts: Is it possible for a node to host multiple cluster in relation to Always On AG

texts: In my example, I have 3 database servers with their own DR servers, own availability group, own clusters.  Is it possible to setup another server which can be part of the 3 clusters and be a secondary replica on each of them?


texts:
Is it possible to feed the result of an aggregate select into the same select with Postgresql?

I'm aware of the WITH clause -- but, this would be true syntactical sugar, wouldn't it?

SELECT some_agg(x) as y, some_func(y) FROM z;

Is Postgres smart enough to run the aggregation once if it's written as:

SELECT some_agg(x), some_func( some_agg(x) ) FROM z;


texts: Difference in location of transaction isolation level declaration - inside/ouside a transaction?

texts:
I have been looking though some stored procedured on our system today when I saw the following structure:

(pseudocode indicative of actual SP, server is using the default isolation level):

CREATE PROCEDURE ...
AS
BEGIN
  IF NOT EXISTS(SELECT ...)
  BEGIN
    RETURN;
  END

  BEGIN TRAN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    DML...
    DML...
    DML...
  COMMIT
END

I have not seen the transaction isolation level being declared inside a transaction boundary and was wondering what the effect of it was?

Is the isolation level restored once the transaction is committed (so if there were additional statements after the commit - what would their isolation level be)?

In this case, I assume there wouldn't be a difference if the isolation level were to be declared just before the transaction started. Is that the case?

Mostly, I was somewhat startled to see this, as I have only ever seen transaction isolation declared outside of a transaction, not within in and was wondering - why? What are the benefits of doing so.


texts: Any checklist/guide when transferring a database to a new server?

texts:
I'm looking for steps that I need to follow to transfer a database to a new server.  So far I have:

I restored the current database that I have in SQL Server 2005 instance to another SQL Server 2008R2 from backups
Transferred all the database and security logins, Linked server
Re-created the SQL Server agent jobs (including an SSIS package) and maintenance plans
Expanding all the other Objects in SSMS but nothing else to move
Planning to choose the database task: "Take-Offline" instead of "Detach", not sure why!
Point an existing application (built on VB)to the new server, and hopefully it will run the reports (thats calling my SQL stored procedures) successfully!

So am I missing anything else that I should transfer?


texts:
I have innodb table with a column in JSON format

My table products:

id_products      int
name_products    varchar --> fx-6350<br>
feature_products json    --> {"cache":{"l1":"128k","l2":"6Mb","l3":"8Mb"},"frequency":"3.9GHz","socket":am3+"}

How can I make a query with result "frequency" and "3.9GHz" and query for everything?


texts: Restore database to a instance in network

texts:
We are creating a application which requires sql database as backend to store data. So when user install my application he chooses a sql instance and its details. There we plan to restore the database which is required for storing the users data etc.

So now if the user selects a sql instance of a another pc in the network then, how i should restore the database? Giving network path in backup database t-sql command is not a good idea. So in this cases how the database is restored to a network path? What is the best practice for this.


texts: Using WHERE col IN with many data OR select all rows

texts:
I have a query where I select many data at once like

  WHERE id IN ( 1,2,3,4,5,6,7,8 ).

Imagine that we might have more than 10.000 Numbers inside the IN query. It's better to do the select as I'm doing it using IN() or just select all data and do the parse with PHP Code for the IDs I want?


texts: Two SQL Server databases setup with same .mdf and .ldf files

texts:
In a SQL Server 2008 R2 instance two databases (the second one being a restore/copy of the first one) have been setup against the same .mdf and .ldf files.

How would I go about either deleting the second one or copying/moving the files for the second one without affecting the first (production!) db?

Update: Indeed one of them is Standby/Read-Only.


texts: upload data from on-premises folder to Azure DB as the server admin is SQL authenticated user

texts:
I have azure SQL DB, with SQL authenticated user as server admin, we have a requirement to upload data from on-premises folder to Azure DB. How can we achieve this?
1.SQL authenticated user is not recognized in AD
2.is there any service account other than this server admin to az-sql-dbs?


texts: Is there some function in PostgreSQL to simply check if the input string is valid UTF-8 according to PG?

texts:
For several years now, I've been hunting this extremely quick-footed little bug which knows every hiding place in my metaphorical coding house. He lurks in the walls, sometimes takes months to show himself, and BAM! He's back, without forewarning. And the hunt continues. And then he runs into a hole in the wall and I can't get him out again no matter what. And so the wait begins...

To make a long story short: PHP and PostgreSQL simply do not have a fully consistent idea of what constitutes a valid UTF-8 string. I can now say this for 100% sure.

To avoid the errors that inevitably are logged when I try to INSERT data which PHP claims is valid UTF-8 but PostgreSQL thinks is not (regardless of who is actually right), I must make some kind of pre-query whose only job is to check if the string is valid UTF-8 according to PostgreSQL. Is this possible somehow?

I'm thinking of something like this:

SELECT is_string_valid_utf8_according_to_postgresql($1);

This would then return a true/false so that I can know for sure if I can go ahead and execute the real, INSERT query, or skip it.


texts: Determining which isolation level is appropriate

texts:
This is a homework question.

  For the following transactions state the isolation level that will
  maximize throughput without lowering the integrity of the database.
  Explain the answer.

  Change the course identified by coursed_id = ’CPSC1350’ from one
  department to a different one.

  The Courses table contains information about courses: their id, their name, the
  department that offers it, the id of its instructor, and the maximum
  number of students who can take it (max_size). Courses(coursed_id:
  string, cname: string, dept: string, instructor_id: string,
  max_size:integer) – Primary Key: coursed_id – Foreign Key:
  instructor_id references Instructors

  Assume that PostgreSQL is used.

I believe the transaction can be done using read committed because

dirty reads are not OK because there is an update going on, thus a read is involved
non-repeatable reads are OK because it is unlikely someone else is changing the value
phantoms are OK because there are no SELECT statements

Am I on the right track?


texts:
I have three tables and all tables hold a primary key of empnum this is an image of my desired result set

However my query is returning 9 rows instead of 3!  See screenshot

This is my DDL -->

CREATE TABLE [dbo].[__One](
    [empnum] [char](12) NOT NULL,
    [empid] [char](50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[__Three](
    [empnum] [char](12) NOT NULL,
    [empjobdesc] [char](64) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[__Two](
    [empnum] [char](12) NOT NULL,
    [empprimarydutydesc] [char](64) NULL,
    [emphourssched] [int] NULL,
    [empdesiredsellamt] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[__One] ([empnum], [empid]) VALUES (N'ec28191', NULL)
GO
INSERT [dbo].[__Three] ([empnum], [empjobdesc]) VALUES (N'ec28191', N'Rigalo')
GO
INSERT [dbo].[__Three] ([empnum], [empjobdesc]) VALUES (N'ec28191', N'Fitzgerald')
GO
INSERT [dbo].[__Three] ([empnum], [empjobdesc]) VALUES (N'ec28191', N'Gooey Glue')
GO
INSERT [dbo].[__Two] ([empnum], [empprimarydutydesc], [emphourssched], [empdesiredsellamt]) VALUES (N'ec28191', N'RGM', 23, 5520)
GO
INSERT [dbo].[__Two] ([empnum], [empprimarydutydesc], [emphourssched], [empdesiredsellamt]) VALUES (N'ec28191', N'FZG', 12, 4840)
GO
INSERT [dbo].[__Two] ([empnum], [empprimarydutydesc], [emphourssched], [empdesiredsellamt]) VALUES (N'ec28191', N'GGRW ', 19, 5000)
GO

And this is the query I write that returns the 9 rows instead of the 3...

;With CTE As (
Select * from __One
),
CTE2 As (
Select * from __Three
), CTE3 As (
Select * from __Two
)
Select Distinct a.empnum, a.empid, c.empprimarydutydesc, b.empjobdesc, c.emphourssched
FROM CTE a
LEFT OUTER JOIN CTE2 b
ON a.empnum = b.empnum
LEFT OUTER JOIN CTE3 c
ON b.empnum = c.empnum

What in the syntax needs to be changed in order to only return my desired 3 rows?


texts: Mysql: Problem using GREATEST with IFNULL and join

texts:
I have a query selecting the greatest value of 2 timestamps.

To achieve it I'm calling:

SELECT GREATEST( IFNULL( tableb.create_time, 0 ) , IFNULL( tablea.create_time, 0 ) )

I'm using IFNULL because the create_time value can be null.
The problem is that the result is something like '2014-0' instead of '2014-08-14 17:04:39'

Here's a fiddle to illustrate my problem

The result is OK when:

I'm not using IFNULL
I'm using IFNULL but I have no join in the query (so the GREATEST is performed on 2 timestamps in the same table)

So for me the problem comes really from the use of GREATEST and IFNULL in a join query.


texts: What problems would using the reserved keywords DATE and TIME as column names cause in Oracle?

texts:
DATE is an SQL Reserved Word and TIME is a PL/SQL Reserved Word in Oracle (listed in SQL> HELP RESERVED WORDS). What problems would using them as column names cause in Oracle?

The database server runs:


texts: I do not understand SQL Server Transaction Logs

texts:
I understand that a Microsoft SQL Server Database has two components:

A database file
A transaction log file

Whenever I make a change to a table, does it get written to both the database file AND the transaction log file? Or does it just go to the transaction log and then convert to the database file after a while?

Furthermore: when I do a backup of the transaction log file, does it clear ALL logs? Or does it just remove some?


texts: How to fix DB2 Unsupported functionality: GENERATED BY DEFAULT

texts:
On IBM DB2 I try the following direct SQL:

CREATE TABLE users (
   user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
   user_name VARCHAR(16) NOT NULL,
   email_address VARCHAR(60),
   PRIMARY KEY (user_id)
)

Then I get the following error:

  The table definition statement failed because some functionality was specified in the table definition that is not supported with the table type.  Unsupported functionality: "GENERATED".. SQLCODE=-1666, SQLSTATE=42613, DRIVER=3.63.123 SQL Code: -1666, SQL State: 42613

GENERATED BY DEFAULT is supposed to be supported according to IBM DB2 Documentation.

Is there something incorrect in my SQL?

EDIT:

I used sqlalchemy and ibm_db_sa with the following code:

users = Table('users', metadata,
    Column('user_id', Integer, primary_key=True),
    Column('user_name', String(16), nullable=False),
    Column('email_address', String(60), key='email')
)

which produces:

CREATE TABLE users (
   user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
   user_name VARCHAR(16) NOT NULL,
   email_address VARCHAR(60),
   PRIMARY KEY (user_id)
)

However, by adding autoincrement=False the IDENTITY is removed and the SQL executes without problem:

users = Table('users', metadata,
    Column('user_id', Integer, primary_key=True, autoincrement=False),
    Column('user_name', String(16), nullable=False),
    Column('email_address', String(60), key='email')
)

which produces:

CREATE TABLE users2 (
    user_id INT NOT NULL,
    user_name VARCHAR(16) NOT NULL,
    email_address VARCHAR(60),
    PRIMARY KEY (user_id)
)


texts: mysql auto adjusting max connections values

texts:
I have set max connections to around 2000 in my.cnf file.

max_connections=2048

I have upgraded to 5.5.20 and now I see the following line in the error log.

120201 19:40:24 [Warning] Changed limits: max_open_files: 1024  max_connections: 214  table_cache: 400

Why does mysql changed the max connections value to 214 ?

# mysqladmin variables | grep max_connections
| max_connections | 214

Soft and hard open files restricted by OS is 1024

# ulimit -Sa | grep "open files"
open files                      (-n) 1024

# ulimit -Ha | grep "open files"
open files                      (-n) 1024

The number of actually used max connections:

# mysql -e"show status like '%used_connections%'"
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 95     |
+----------------------+-------+


texts: Load csv file to mysql using load data local infile

texts:
I need a simple way to load a csv file from my computer to the database (which is on a remote server).
I'm trying to load the file using a php page that executes this code:

$file = $_FILES['csv']['tmp_name'];
$handle = fopen($file,"r");
$name = $_FILES['csv']['name'];

$import = "LOAD DATA LOCAL INFILE '" . $name .
                "' INTO TABLE temporal_load
                  FIELDS TERMINATED BY ','
                  optionally ENCLOSED BY '\"'
                  LINES TERMINATED BY '\n'
                  IGNORE 1 LINES
                  (num,ticker,company,sector,industry) ";

mysql_query($import) or die(mysql_error());

But when I select a file on the php page and try to load it, shows the error message:

File 'test11-14-15.csv' not found (Errcode: 2)

I already reviewed that the variable mysql.allow_local_infile is ON and the
database connection was made this way:

mysql_connect(HOST,USER,PASSWORD,false,128);

The file is not on the remote server, the file is on my computer, but it should work anyway because I'm using LOCAL.

What am I doing wrong???


texts:
MySQL runs fine for weeks or days, and then suddenly and without any user action, all attempts to connect begin failing. This is /var/log/mysqld.log:

160916 14:42:01 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
Version: '5.5.51'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
160930 03:05:51 mysqld_safe Number of processes running now: 0
160930 03:05:52 mysqld_safe mysqld restarted
160930  3:05:53 [Note] /usr/libexec/mysql55/mysqld (mysqld 5.5.51) starting as process 8961 ...
160930  3:05:53 [Note] Plugin 'FEDERATED' is disabled.
160930  3:05:54 InnoDB: The InnoDB memory heap is disabled
160930  3:05:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160930  3:05:54 InnoDB: Compressed tables use zlib 1.2.8
160930  3:05:54 InnoDB: Using Linux native AIO
160930  3:05:54 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
160930  3:05:54 InnoDB: Completed initialization of buffer pool
160930  3:05:54 InnoDB: Fatal error: cannot allocate memory for the buffer pool
160930  3:05:54 [ERROR] Plugin 'InnoDB' init function returned error.
160930  3:05:54 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160930  3:05:54 [ERROR] Unknown/unsupported storage engine: InnoDB
160930  3:05:54 [ERROR] Aborting

160930  3:05:54 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete

160930 03:05:54 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

What causes this, and how can I deal with it?


texts: MySQL Queries Truncate Causing Connection Spikes

texts:
We have a MySQL Master Instance & Read (failover only) on AWS. MySQL 5.6 Aurora running Magento. Performance is great, and better than standard MySQL (we have high traffic site so see the benefits).

We suffer some issues with when Magento Indexing happens even under pretty low loads.

Truncate functions happen on some key table one of them being:
TRUNCATE TABLE catalog_product_index_group_price
When the above happens other queries build up / bottleneck in the process. The state changes to: "checking permissions" and they hold until the truncate is complete
Its fairly fast, but what it does is slows/blocks the website for a second or 2 when the site is under load.
No foreign key constraints on the table; catalog_product_index_group_price
We have query_cache_limit set to 4mb (which has helped queries perform better).

Looking in Performance Schema we are trying to see what could possible be holing this up with no luck.

Having read a lot of about 5.6-5.7. We have read statements like:

"Fixed as of the upcoming 5.7.18, 8.0.1 release, and here's the changelog entry:

A TRUNCATE TABLE operation held the dict_sys mutex while scanning for and
removing pages from the buffer pool, causing concurrent DDL operations to
stall. The mutex is now released during the scan and acquired again when
the scan is completed. "

Does anyone have any ideas or insight into the above. Especially being able to verify this behaviour. We are also going to log ticket in with AWS too.


texts: How to design a query that gives count of different parameters based on different condition from a single table

texts:
I have a table books containing columns as bookid, issue_date,return_date.

Once a book is issued from library an issue_date corresponding to it is made into the table while return_date can have valid date or null value.

Now the situation is I want the number of books for the following cases:

1. no. of books issued and returned on the same day as SAME_DAY
2. no. of books that have not been returned until now (i.e. return_date is null) as NOT_RET
3. no. of books that were issued and returned on separate days as SEP_DAY

I want all these numbers as output of a single query as for example :

SAME_DAY    NOT_RET    SEP_DAY
______________________________
   12          13       10


texts: Expand a range columns into a single column

texts:
I'm thinking this is an easy query but I don't know how to start. I have a database table called Booklet with columns id, controlNoFrom and controlNoTo. Sample data looked like this:

id       | controlNoFrom | controlNoTo
---------+---------------+-------------
20150105 |             1 |         100
20150106 |           101 |         150
20150107 |           151 |         225
---------+---------------+-------------

What I want is a query that can present the data like this:

id       | controlNo
---------+-----------
20150105 |         1
20150105 |         2
20150105 |         3
...      |       ...
20150105 |        99
20150105 |       100
20150106 |       101
20150106 |       102
...      |       ...
20150106 |       149
20150106 |       150
20150107 |       151
20150107 |       152
...      |       ...
20150107 |       224
20150107 |       225
---------+-----------

It's easy if it is the other way around, but I don't know what kind of SQL to be used in this scenario. What would you call this approach and what would the SQL statement in OracleDB look like?


texts: Have table with 4 000 000 rows. Need audit any data changes in table. I create triggers for this table and audit all changes data,but is there any better way for audit changes in table ? And also one more question, how audit droped table,view,stored procedures,functions,triggers ? In sql server I can create ddl trigger but as I know mysql does not support it. My database is Mysql Community Edition


texts: Selecting a group from groups based on one row condition

texts:
There is a group of rows which have the losscode and service odom. The contractnumber and service odometer are used to group the rows.

Please, may you help me to find the way to select only the row group which have the slosscode='REVSTD'?


texts: How to perform a multiplication by a different select query

texts:
Hope my question is accepted here and I haven't made a mess out of renaming the tables and columns so you can understand what I need! :)

Query A:

DECLARE @var VARCHAR(7)
SET @var = 'xtpo'
SELECT
    row_number() over(order by dbo.tbl_A.field_1) AS '1st column',
    dbo.tbl_A.field_2 AS '2nd column',
    dbo.tbl_B.field_1 AS '3rd column',
    dbo.tbl_A.field_3 AS '4th column',
    COUNT(temp0.reference) AS 'Qty'
FROM dbo.tbl_C tbl_c
JOIN dbo.tbl_D tbl_d ON tbl_d.field_4 = tbc_c.field_4
JOIN dbo.tbl_A tbl_a ON tbl_a.field_3 = tbl_d.field_3
JOIN dbo.tbl_B tbl_b ON tbl_a.field_4 = tbl_b.field_4
JOIN (SELECT
         tbl_e.field_1 f1,
         tbl_e.field_2,
         tbl_e.field_3 AS f3
      FROM dbo.tbl_E tbl_e
      GROUP BY
         tbl_e.field_1,
         tbl_e.field_2,
         tbl_e.field_3,
         tbl_e.field_1) temp0 ON dbo.tbl_A.field_2 = temp0.f3 AND temp0.f1 = tbl_c.field_2
WHERE tbl_c.field_1 LIKE @var
GROUP BY dbo.tbl_A.field_a, dbo.tbl_A.field_2. dbo.tbl_B.field_2

Query A is a listing containing 5 columns.

Query B:

DECLARE @var VARCHAR(7)
SET @var = 'xtpo'
SELECT COUNT(DISTINCT PcbID) FROM dbo.tbl_C WHERE dbo.tbl_C.field_5 LIKE @var

How can I integrate Query B on Query A so the 6th column of the listing is a multiplication of Query A's 5th column and the single result of Query B?

Thanks in advance,

gtludwig


texts: Need a way to query a table, and JOIN it with the TOP 1 related record from an other table

texts:
As a follow up to this question, I'm wondering if there is a better and/or more efficient way to gather the data in question.

As stated, this query does return me 95% of the data I need --

SELECT dv.Name
      ,MAX(hb.[DateEntered]) as DE
FROM
    [Devices] as dv
    INNER JOIN
    [Heartbeats] as hb ON hb.DeviceID = dv.ID
GROUP BY dv.Name
HAVING MAX(hb.[DateEntered]) < '20130304';

Is there a way to achieve a the same result (for each Device, select the TOP Heartbeat ordered DESC by DateEntered) but also select the entire row from the [Heartbeats] table? Right now, I only get the DateTime for that row.

If I include the additional columns in the GROUP BY clause, I can then add them to the select; but then I get multiple rows per [Devices] row which I don't want. It sounds odd, but what I basically want to do is do a query against [Devices] and then do a for...each over that set and add the top [Heartbeats] row for that [Devices] row. Is that possible?

update
This is the structure of the Heartbeats table:

CREATE TABLE [dbo].[Heartbeats](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DeviceID] [int] NOT NULL,
    [IsFMSFMPUp] [bit] NOT NULL,
    [IsFMSWebUp] [bit] NOT NULL,
    [IsPingUp] [bit] NOT NULL,
    [DateEntered] [datetime] NOT NULL,
 CONSTRAINT [PK_Heartbeats] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [CommonQueryIndex] ON [dbo].[Heartbeats]
(
    [DateEntered] ASC,
    [DeviceID] ASC
)
INCLUDE ( [ID],
[IsFMSFMPUp],
[IsFMSWebUp],
[IsPingUp]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HeartbeatDeviceIndex] ON [dbo].[Heartbeats]
(
    [DeviceID] ASC
)
INCLUDE ( [ID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
/****** Object:  Default [DF_Heartbeats_DateEntered]    Script Date: 03/05/2013 10:45:45 ******/
ALTER TABLE [dbo].[Heartbeats] ADD  CONSTRAINT [DF_Heartbeats_DateEntered]  DEFAULT (getdate()) FOR [DateEntered]
GO
/****** Object:  ForeignKey [FK_Heartbeats_Devices]    Script Date: 03/05/2013 10:45:45 ******/
ALTER TABLE [dbo].[Heartbeats]  WITH CHECK ADD  CONSTRAINT [FK_Heartbeats_Devices] FOREIGN KEY([DeviceID])
REFERENCES [dbo].[Devices] ([ID])
GO
ALTER TABLE [dbo].[Heartbeats] CHECK CONSTRAINT [FK_Heartbeats_Devices]
GO


texts: Windows Server and SQL Server updates and patching - and how to rollback (revert) if necessary

texts:
Say we have a server as SQL Server 2017 on Windows Server 2016, both have some pending updates recently

Below few questions:

What's the correct order of installing updates - first Windows, and then SQL Server ? vice versa ?
Or the order does not matter ?
How do I know if my Windows or SQL Server update failed ?
Is it possible that any of updates can fail and bring server to an unusable state ?
How to rollback (revert) Windows or SQL Server update if it was unsuccessful
(or even broke the system) ?


texts: How to use sqlcmd to create file names for output based on the day of the week or the date?

texts:
I have a SQL agent job that queries the database and exports the results into a csv file.
It needs to run daily and The output file (Output.csv) gets overwritten everytime the job runs. Is there a way to create new files instead of overwriting? Say monday.csv, tuesday.csv etc or maybe {date}.csv

This is the command:

SQLCMD -S SQL-CLUST1 -Q "SELECT * FROM TABLE" -o "\\\PATH\output.csv" -W -s","

Step type: Operating system (CmdExec)

Run as : SQL Proxy

Note: The output file and append output to existing file is unavailable in the advanced tab.


texts: How can I recover all the Stored procedures, Functions, Events from previous server version

texts:
Until recently I have been running MySQL5.5.8 - foolishly a couple of days ago I decided to upgrade to take advantage of new features to be found in version 8. Before the upgrade I did a backup, using MySQL Administrator of most, not all, databases - some were expendable. Perhaps foolishly I also backed up the mysql table which caused some issues when trying to import from the backup but after editing the sql file I appear to have all the databases back and accessible.

The problem though is there are no stored procedures ( of which there were many ) which means my development website will no longer load. The same is true of the functions, events and triggers - it appears that none of these were backed up by MySQL Administrator

The above shows the structure used for the datafiles. Within innoDB_datafiles folder is the ibdata1 file which looks to be approx 12Gb.

All the databases on the orginal server appear to have their own folder with various files for the tables.

Within the mysql database are the proc.myd and proc.myi files. There are similar files for events ( event.myd & event.myi ) and for functions (func.myd & func.myi ) - I can not see the equivalent for triggers so assume they are referenced elsewhere but not found that yet.

With the new MySQL8 server I created a new Stored Procedure to see if the proc.myd/myi file would be generated in the equivalent mysql directory - as per below screenshot

It appears that MySQL8 does things rather differently and I don't have a clue what to do next to be honest. I have tried running the following command in a gui (heidi)

select
    `r`.`specific_name` as 'name'
    from `information_schema`.`routines` as r
    where `r`.`routine_type` = "procedure";

This query would previously list all procedures perfectly well but on MySQL8 nothing! Similar story for functions:

select
  `r`.`specific_name` as 'name'
  from `information_schema`.`routines` as r
  where `r`.`routine_type` = "function";  // no results

There was success when I tried for triggers - they appear to have been within the backup file.

select `trigger_name` as 'name'
    from `information_schema`.`triggers`;

Again, success for a newly created event

select `event_name` as 'name'
    from `information_schema`.`events`; // :-)

The question is basically how can I restore my server to a healthy state with all the procedures, functions and events based upon the details within either the respective myd or myi files or within the original ibdata1file?

Any help and advice to help rectify this would be greatly appreciated!


texts: Faster way to GROUP or ORDER dynamic fields?

texts:
We have this query in our app, that searches a table for a song title using MATCH AGAINST and picks the most relevant results.

The table has around a million results and is growing.

The app has been running on a bare metal cPanel server with the default MySQL config, recently it's been moved to a VPS and the query has gone from taking around 2 seconds, to taking around 8.

SELECT trackcatalogue_request.id
  ,trackcatalogue_request.artistId
  ,trackcatalogue_request.albumId
  ,trackcatalogue_request.songTitle
  ,trackcatalogue_request.artistName
  ,IF(trackcatalogue_request.songTitle = 'test', 1, 0) as trump
  ,MATCH(trackcatalogue_request.songTitle,trackcatalogue_request.artistName) AGAINST ('test') AS score
FROM trackcatalogue_request
WHERE (trackcatalogue_request.songTitle LIKE '%test%'
OR MATCH(trackcatalogue_request.songTitle,trackcatalogue_request.artistName) AGAINST ('test'))
AND (trackcatalogue_request.id NOT IN(/* Blacklisted ids*/))
AND (trackcatalogue_request.isBanned = 0) AND (trackcatalogue_request.isExplicit = 0)
AND (trackcatalogue_request.isXmas = 0)
GROUP BY trackcatalogue_request.artistId, trackcatalogue_request.songTitle
ORDER BY trump DESC, score DESC
LIMIT 200

We think it's because VPS's disk performance is worse.
Removing the GROUP or ORDER brings the query back down to 2 seconds which is acceptable...

How can we optimise this query?

Here's the EXPLAIN

1    SIMPLE    trackcatalogue_request    range    PRIMARY,idxIsExplicit,idxIsXmas,idxIsBanned    idxIsExplicit    1        1078658    Using where; Using temporary; Using filesort

And the SHOW CREATE TABLE

CREATE TABLE `trackcatalogue_request` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `albumId` bigint(20) DEFAULT NULL,
  `trackNo` int(11) DEFAULT NULL,
  `songTitle` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `artistName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `artistId` bigint(20) DEFAULT NULL,
  `albumTitle` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `releaseYear` varchar(4) COLLATE utf8_unicode_ci DEFAULT NULL,
  `comment` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `genreId` int(11) DEFAULT NULL,
  `xReference` bigint(20) DEFAULT NULL,
  `entryDate` date DEFAULT NULL,
  `peakPosition` int(11) DEFAULT NULL,
  `top40` bigint(20) DEFAULT NULL,
  `preRelease` date DEFAULT NULL,
  `dateAdded` datetime DEFAULT NULL,
  `isExplicit` tinyint(4) NOT NULL,
  `isXmas` tinyint(4) NOT NULL,
  `isBanned` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idxArtistNameSimple` (`artistName`),
  KEY `idxSongTitleNormal` (`songTitle`),
  KEY `idxIsExplicit` (`isExplicit`),
  KEY `idxIsXmas` (`isXmas`),
  KEY `idxIsBanned` (`isBanned`),
  KEY `idxArtistId` (`artistId`),
  KEY `idxAlbumId` (`albumId`),
  KEY `idxGenreId` (`genreId`),
  KEY `idxTrackNo` (`trackNo`),
  FULLTEXT KEY `idxArtistName` (`artistName`),
  FULLTEXT KEY `idxSongTitle` (`songTitle`),
  FULLTEXT KEY `idxSongTitleArtistName` (`songTitle`,`artistName`),
  FULLTEXT KEY `idxAlbumTitle` (`albumTitle`)
) ENGINE=MyISAM AUTO_INCREMENT=1085946 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


texts: Can't create a backup of data of a single table

texts:
I try to use a command:

pg_dump -U postgres -d "ProjectsManagement" -t public.Statuses -a > path

But it doesn't work and outputs:

  pg_dump: соответствующие таблицы не найдены (English: Tables not found)

But this table exists and without a -t argument it works.

What is happening?

\l output:

  ProjectsManagement | postgres | UTF8      | Russian_Russia.1251 |
  Russian_Russia.1251 |


texts:
I'm using Oracle 11g database in archivelog mode and auditing is not enabled.
Two days before, a few lines from a child table in my database got deleted. As the schema password was shared, I'm unable to track how it happened.I checked the trace files as well as queried tables v$log_history, V$SQL, DBA_HIST_SQLTEXT to get the history, but couldn't find any relevant information. Also, I reviewed the data by restoring the backup on this same date. But, since this data was created and got deleted on the same date, I'm unable to track this data even from the backup.
As I'm a newbie, I don't know how to tackle this issue. Are there any other methods/queries by which I can check if any delete operation had been performed in my database?


texts: Query performance differs greatly between a development setup and production

texts:
I'll try to keep this question straightforward, though I am dealing with a big ball of mud.

When I run my test query acrossed linked servers both located locally (on a shared virtual host), the query is fast at about 9 seconds.

When I run the same query acrossed linked servers (one local, one about 1,200 miles away) it is MUCH slower at 5:23

I am trying to learn how to analyze an execution plan, but are there other probable causes for this sort of thing?

Edit: Based on @Mat's comment, here is an example.

DISCLAIMER: I do not vouch for the quality of this code.

SELECT * INTO TempQTYOnHand FROM [RemoteServer].remoteDB.dbo.QuantityOnHand

DELETE FROM [RemoteServer].remoteDB.dbo.QuantityOnHand

INSERT INTO [RemoteServer].remoteDB.dbo.QuantityOnHand
SELECT DISTINCT ProductId, Onhand FROM Inventory
WHERE ProductID IN (SELECT ProductId FROM TempQTYOnHand)

DROP TABLE TempQTYOnHand

Is this a "chatty" query, and also, I guess chatty would mean that it is round tripping for each row on the INSERT?


texts: Query MongoDB Collection Compression Settings?

texts:
How can i query the settings for collection compression in MongoDB?
I configured WiredTiger as storage engine and as collection compression the zlib algorithm.

Here is a snippet of my configuration YAML file:

storage:
   dbPath: "/data/wiredTiger"
   engine: "wiredTiger"
   wiredTiger:
      collectionConfig:
         blockCompressor: "zlib"

Using mongo version 3.0.5.

Any help please


texts: EXECUTE AS does not appear to be changing security context

texts:
We have legacy code that still makes use of xp_cmdshell calls.  When we migrated to SQL Server 2008, we created a stored procedure that used code after the following pattern:

EXECUTE AS 'DOMAIN\ID2'
EXEC master..xp_cmdshell @command
REVERT

When I pass in WHOAMI as the command, what it shows is not the 'DOMAIN\ID2', but rather the ID of the service account that SQL Server is running under (i.e. 'DOMAIN\ID1').  Should it not be returning 'DOMAIN\ID2' as it's supposedly running unders a different security context?  If so, any idea why it would not be changing context?  This process was created by another dev who is now long gone and I'm not really familiar with security and impersonation as I probably should be.


texts: Hash join hint returns an error when using local variable

texts:
The query below is using the StackOverflow2010 sample database:
DECLARE @Id INT = 18471

SELECT c.UserId FROM dbo.Comments AS c
INNER HASH JOIN dbo.Users AS u
ON c.UserId = u.Id
AND c.UserId = @Id

With a hash join hint, it returns the following error:

Query processor could not produce a query plan because of the hints
defined in this query. Resubmit the query without specifying any hints
and without using SET FORCEPLAN

It works when I add OPTION(RECOMPILE) or replace the variable with a string literal.
What could be causing this issue?


texts: MySQL - Many to Many Join. Left Join already Present

texts:
I am building a Attendance System for a Client and have become a tad stuck with JOIN's. I already have a left JOIN running, and want to add a JOIN on a many-to-many relationship but can't get my head around it.

The Problem:

I have a query that checks if a student is currently in a class, if they are it brings back the Student_ID, Class_ID and Attendance Status E.G Late, Present or Absent.

This query runs fine *IF the student is currently in a class, if they are not it returns an empty set. *

What I would like it to return is the student ID even if the student is in no class.

This is my query:

SELECT  student.Student_ID as 'Student_ID',
                  case when class.class_id is null then 'No Class ATM' else class.class_id end as 'Class_ID',
                  case when Attendance.Attendance_Status is null then 'Not Present' else Attendance.Attendance_Status end as 'attendence_status'
          FROM Student
          INNER JOIN Student_Has_Class ON(student_has_class.student_id = student.student_id)
          INNER JOIN class ON(class.class_id = student_has_class.class_id)
          INNER JOIN Subject ON(class.subject_id = subject.subject_id)
          LEFT JOIN Attendance on(Attendance.class_id = class.class_ID
                                  AND Attendance.student_id = student.student_id)
          WHERE NOW() between class.class_start_timestamp and class.class_end_timestamp
          AND Student.student_id like '1'

I think it needs a Left Outer Join but I can't seem to get my head around how to format the query correctly.

I have tried the below query but doesn't work!!!

SELECT  student.Student_ID as 'Student_ID',
                  case when class.class_id is null then 'No Class ATM' else class.class_id end as 'Class_ID',
                  case when Attendance.Attendance_Status is null then 'Not Present' else Attendance.Attendance_Status end as 'attendence_status'
          FROM Student
          LEFT OUTER JOIN Student_Has_Class ON(student_has_class.student_id = student.student_id
                                                AND Student.student_id like '1')
          LEFT OUTER JOIN class ON(class.class_id = student_has_class.class_id)
          INNER JOIN Subject ON(class.subject_id = subject.subject_id)
          LEFT JOIN Attendance on(Attendance.class_id = class.class_ID
                                  AND Attendance.student_id = student.student_id)
          WHERE NOW() between class.class_start_timestamp and class.class_end_timestamp;

Any help would be very much appreciated.

There is a many to many relationship between: Student and Class called Student_Has_Class


texts: How to use floating-point storage for time data type

texts:
At Postgres documentation page Date/Time Types, it says:

  For the time types, the allowed range of p is from 0 to 6 when
  eight-byte integer storage is used, or from 0 to 10 when
  floating-point storage is used.

I tried to produce SQL statement with time(p), when p=10? I always get

  WARNING:  TIME(10) precision reduced to maximum allowed, 6

No matter if I put seconds in float or not. I realize I'm missing something very obvious. Please give an example when p=10.


texts:
I want to have the whole database to be encrypted, instead of encrypting the data before storing into database.

Is it possible to encrypt the whole postgres database and what best method to use?

Thanks :)


texts:
I was given a sever to use for tests that has MySQL commercial version installed. I would like to test backups using MySQL MEB, but cannot find the download site. I have seen many blogs mentioning that I need a file

meb-3.12.3-linux-glibc2.5-x86-64bit.tar.gz

But cannot find a website where I can actually download the MEB file. Should MySQL Backup already come pre-packaged with the MySQL commercial version and just need to find the executable file? I am new to MySQL enterprise, but familiar with mysqldump and xtrabackup. Unfortunately, mysqldump is too slow, and xtrabackup does not work with MySQL Enterprise.


texts: Set "Encryption Enabled" to FALSE...what can go wrong?

texts:
I'm migrating a database from SQL Server 2012 to SQL Server 2014 on the same server.

I'm configuring LOG SHIPPING on a database that is apparently encrypted:

When setting the log shipping I got this message (when it started to restore the database from a backup):

I'm the new DBA here and there's no report of master keys, encrypted keys etc.

I'm setting log shipping from SQL2012 to SQL2014 (with norecovery). What could go wrong if I set this database to Encryption enabled - false and try to restore the database with the log shipping wizard?

There are some questions about this problem , like This one, but all of them say I need the password.

I Just found this certificate on master > security > certificates on the primary server (2012):

And there are some code samples over the Interet but I really can't trust them (I don't think it's too simple):

`USE MASTER
GO
ALTER DATABASE DatabaseName
SET ENCRYPTION OFF
GO
USE DatabaseName
GO
DROP DATABASE ENCRYPTION KEY
GO`

Edit1:

In this question Dylon says:

  If you are receiving the thumbprint error the certificate was not created properly from the Source Server's Cert/Key backups.

I'm confused about this topic and all I want is to disable encryption, restore the database on the other server to log shipping, and some day, activate encryption.

Edit2:

On Database > tasks > manage database encryption there are some options too:

Disabling this, I would be able to backup the database again, and restore it on the new instance?


texts:
This is probably a basic question, but I'm trying to build an SQL query for an access database that returns orders from a certain customer and I want to cross reference the customerID with the customer name from the customers table.

This is the query that I'm using

SELECT OrderTable.OrderNumber, OrderTable.CustomerID, OrderTable.OrderDate
FROM [OrderTable] INNER JOIN [CustomerTable]
    ON CustomerTable.CustomerID = OrderTable.CustomerID
WHERE OrderTable.CustomerID = 1

I don't get an error and it returns the correct records (all orders w/ customer ID = 1), however it does not return the customer name...it just returns the customerID for each order (always 1 as expected).

What am I doing incorrectly?

Thank you!


texts:
I am trying to create a database to store information regarding equivalent courses transferring from one school to another. I am struggling to conceptualize the relationship in a way that can translate into a database. I currently have two tables, each holding the courses offered by each school. These tables contain the course code, the course name, the course description and the number of credit hours.

School 1 offers courses A, B, C, D.
School 2 offers courses W, X, Y, Z.

The equivalent courses could be:

A = W  --Taking class A at School 1 is equivalent to taking class W at School 2.

A+C = Y   --Taking classes A and C at School 1 is equivalent to taking class Y at School 2.

D = X+Y+Z  --Taking class D at School 1 is equivalent to taking classes X, Y and Z at School 2.

And so on. One or more classes at School 1 will be equivalent to one or more classes at School 2. Every course from School 1 needs to have an equivalent course at School 2, as there is a "General Elective" catch all. However, not every course at School 2 will be represented.

I'm not extremely technical and am more interested in just design at this stage. Any help is greatly appreciated.


texts: One table to save all records vs create one table each day

texts:
One vendor need to store in a database multiple records. There are 5 tables (a table per element - elements are: pressure, flow, volume, temperature and distance). Each table have the following columns: ID, Timestamp, Value;
Each day, a table will have approximately 50.000 records.

Database used is SQL Server 2017, on a Windows Server 2016 machine.
There will be a lot of analytics to make (charts, reports, etc.) (daily, weekly, monthly, yearly)

So, the vendor wants to create these 5 tables every day for each day of one year (example: "Table_yyyyMMdd_Pressure") and store into it values from that day only; this approach will get the database to have something like 1825 tables per year.

Our request was to create only 5 tables and store all records there - which will get to have approximately 18 000 000 records in one year per table.

From my point of view, our approach is better taking into account:

queries will be run only on those 5 tables once (per table) instead of running queries multiple times per each table per each day;
easier to create reports on a single table than on multiple tables
easier to change the structure of the tables if will be necessary
less complex to write code/procedure

Can you please give me more pro's and con's regarding these 2 approaches?

Thank you!


texts:
I am trying to insert rows from server1.table1 to server2.table2. I already create a linked server in order to query tables in both servers and it works.

However because server2.table2 has an identity column,and though I put the hint set identity_insert table2 on,
  the insert operation failed and I got the following errors in french:

L'objet "192.168.0.36.wt_delivery.dbo.table2" est introuvable, car il n'existe pas ou vous ne disposez pas des autorisations requises.

which mean that the object 192.168.0.36.wt_delivery.dbo.table2 is not found or we don't have the required permission to do the operation.

when I create the same table without the identity column the insert operation succeed!!

The query was executed from server1

Is there a specific permission that allow the operation to succeed or the problem is not at permission level?

Thank you


texts: Any different guidance for tempdb with Availability Groups?

texts:
I have an Availability Group set up on SQL Server 2014 running on a two-node Windows Server Failover Cluster. The setup consists of two stand-alone instances + synchronous with automatic failover.

Many Microsoft articles I've read advocate using multiple files for TempDB to increase performance. It seems they recommend using 8 files.

Should I do that in the case of a this configuration? Will it improve performance?


texts:
We're setting up a PostgreSQL cluster, and we're looking at using repmgr to handle standby promotion and so on.

Our cluster has one master and a hot standby, and uses the streaming replication features of PostgreSQL 9.1.

Since we're using streaming replication, writes to the master will no longer go through when the standby fails. So we would like to use repmgr to not only promote the hot standby when the master fails, but also to use repmgr to disable streaming replication when the standby fails.

Are there any suggestions on how to do this? All the documentation seems centred around a failure with the master.


texts:
the GeoJSON Spec show,

{
  "type": "Feature",
  "geometry": {
    "type": "Point",
    "coordinates": [125.6, 10.1]
  },
  "properties": {
    "name": "Dinagat Islands"
  }
}

But when I wrap that in a call to ST_GeomFromGeoJSON, like this,

SELECT ST_GeomFromGeoJSON($${
  "type": "Feature",
  "geometry": {
    "type": "Point",
    "coordinates": [125.6, 10.1]
  },
  "properties": {
    "name": "Dinagat Islands"
  }
}$$);

I get the error,

  ERROR:  invalid GeoJson representation


texts: Would using SELECT FOR UPDATE and a subsequent INSERT guarantee uniqueness in text column?

texts:
I have a table with a text column that I need to be unique. Because I can't put a unique constraint on the column in MySQL, I want to know if the following approach would guarantee uniqueness in the column:

Create a varchar column for holding the hash of the unique text column. Put a plain INDEX on it.
Start transaction
Use SELECT FOR UPDATE to see if hash exists
a) Use a subsequent INSERT if hash doesn't exist

b) If hash exists, SELECT by unique text to see if it exists. If unique text does not exist, do a subsequent INSERT, otherwise do 'UPDATE'
End transaction

If I use the above approach for all inserts in that table, can that guarantee uniqueness?


texts: Can I simply copy mdf and log files into the data folder to add a new database?

texts:
I have a copy of the mdf and log files of a database. The database is a MSSQL 2012 database. To use it, I did the following steps (a bit by chance actually, because I was trying to copy a mdf and log file out of the data directory first, and it wouldn't let me while the server was using them):

I stopped the MSSQL server process in Windows
Copied them into the DATA directory.
Then I started the MSSQL servcer process again.

In SQL server management studio, the new database appeared. I thought I might have needed to attach it.

Does MSSQL server automatically attach all mdf's in the data directory? I'm worried it is not working as it should, even though it looks like it.


texts: MySQL cluster write performance with Galera (MariaDB)

texts:
I am currently evaluating the migration from our current database server to a three node cluster.

Our current server is a VM running MariaDB 10.1 with asynchronous replication to another server in order to perform backups of the database without any performance hits.

The read to write ratio is about 70/30, we are running an IoT platform with many sensors sending raw data that is written to the database before it is consolidated and presented to the end user.

Because of the high write to read ratio, I want to make sure that the cluster can perform at least as well as our current server on writes before migrating.

For this test, I started a VM on the Google Compute Engine with 16 vCPUS, 60GB of RAM and SSD local storage with 10,000 read IOPS and 15,000 write IOPS.

On this VM running Debian 8.3 I installed MariaDB 10.1 (which includes the Galera cluster), in order to run some benchmarks with Sysbench 0.5.

These are the MySQL parameters that I changed from the default mysql.cnf file:

[mysqld]
binlog_format=ROW
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_log_file_size=2G
innodb_buffer_pool_size=4G
sync_binlog=0

When running the update test from sysbench, I am noticing a huge overhead from enabling wsrep on the single node that I have currently deployed.

This is the command that I am using to run the benchmark:

sysbench --db-driver=mysql --num-threads=32 --max-time=30 --max-requests=0 --oltp-tables-count=32 --oltp-table-size=100000 --oltp-test-mode=complex --test=/root/sysbench/sysbench/tests/db/update_index.lua --mysql-db=sbtest --oltp-skip-trx=on run

With wsrep_on=OFF, I get around 54000 updates per second on the benchmark.

Setting wsrep_on to ON, I get around 19000 updates per second.

I was expecting a performance hit from enabling synchronous replication, but I did not expect it to be this much.

It doesn't look like there's a bottleneck from the CPU nor the IO, so why is the performance hit from enabling wsrep so high?


texts:
I am in a new shop and the below error started to show up on 2/22 repeatedly until 3/7.

Event Type: Error
Event Source:   SQLSERVERAGENT
Event Category: Alert Engine
Event ID:   318
Description:
Unable to read local eventlog (reason: The event log file is corrupted).

Event Type: Information
Event Source:   SQLSERVERAGENT
Event Category: Alert Engine
Event ID:   311
Date:       2/22/2011
Time:       6:52:44 AM
User:       N/A
Computer:   XXXXXXX
Description:
Attempting to re-open the local eventlog...

Event Type: Warning
Event Source:   SQLSERVERAGENT
Event Category: Alert Engine
Event ID:   312
Description:
Successfully re-opened the local eventlog - NOTE: Some events may have been missed.

Then, the server shut down unexpectedly. The shutdown stopped the events. But, my concerns are: What was happening? Does anyone know what this means? More importantly, what kind of measures do we need to take to prevent this from happening?


texts: Postgres long autovacuum halting database

texts:
I have a fairly large table (1 million rows) and my database is stuck on an autovacuum (>30 mins) on the this table, causing the whole database to chock.  Application won't even load now.

-00:37:31.137859 autovacuum: VACUUM public.users

SELECT n_tup_del, n_tup_upd FROM pg_stat_all_tables WHERE relname = 'users';

These are my autovacuum settings on my users table:

autovacuum_vacuum_scale_factor=0.0,
autovacuum_vacuum_threshold=5000,
autovacuum_analyze_scale_factor=0.0,
autovacuum_analyze_threshold=5000

These suggested settings I used from Slow PostgreSQL Performance? Don't Forget to Vacuum your Database

Do I just have to wait it out? What are my options?

Update

I have upgraded to Postgres 9.5 and have also increased my RDS IOPS to 900 and the vacuum process still maxs out the IOPS and can't do anything else to the database.  The process was running for at one point 1 day before the upgrade.

I also have removed the custom autovacuum settings I had, and now just using the default.

Here is an attachment of results of these queries;

SELECT * FROM pg_stat_activity;
SELECT * FROM pg_stat_database;
SELECT * FROM pg_stat_user_tables;
SELECT * FROM pg_stat_user_indexes;
SELECT * FROM pg_locks;

http://www.filedropper.com/output_5


texts: Normalization for three somehow identical tables

texts:
These three tables are nearly identical. But, there are some exceptions.

But, I can't find best normalized structure. Could you please help me?

Here is the informations must be hold in database:

 1) APARTMENTS
      CITY_ID
      VILLAGE_ID
      STREET_NAME
      BUILDING
      APARTMENT
      LIVING_SPACE
      ELECTRIC_SUBSCRIBER_NUMBER
      GAS_SUBSCRIBER_NUMBER
      WATER_SUBSCRIBER_NUMBER

 2) GARDENES
      CITY_ID
      VILLAGE_ID
      STREET_NAME
      BUILDING

      LIVING_SPACE
      ELECTRIC_SUBSCRIBER_NUMBER
      GAS_SUBSCRIBER_NUMBER
      WATER_SUBSCRIBER_NUMBER

      TOTAL_GARDEN_SPACE

 3) PLOT_OF_LAND
      CITY_ID
      VILLAGE_ID
      STREET_NAME
      BUILDING
      LAND_SPACE

What is the best structure for this tables?

Yet, I have made two structures:

FIRST ONE:

But, it seems not the best to me. So, i have made second choice:
SECOND ONE:
May be I must make on table with the name POSSESSİONS? And inside this:

POSSESSIONS
   ID
   TYPE (1-APARTMENT, 2-GARDEN, 3-PLOT_OF_LAND)
   SUBSCRIBER_ID
   ADDRESS_ID

   LIVING_SPACE
   TOTAL_GARDEN_SPACE
   LAND_SPACE

What is your recomendations?


texts:
I have a table my_tables which gets referenced as foreign key in several tables.

I would like to select all rows of my_table which are not referenced in an other table.

AFAIK, it should be possible to do this in a generic way (with some introspection magic).


texts: How can I import data from a NetCDF file into SQL Server?

texts:
I'm being asked to investigate the feasibility of importing data from a NetCDF file into SQL server.

The file could be upwards of 300-400Mb and provided 4 times a day. However I'm only interested in a small percentage of the data. (Maybe as little as 3-5%). We estimate importing ~4000 rows on each run and accumulating the data.

From what I have read a NetCDF file is an XML file. Which may also include binary data.

What would be the best options for importing the data? Should I import the whole file into working table and then select what I want? Or would it be possible to parse/query the file using SQL XML features and only import the data i'm interested in.

I'd appreciate any recommendations from anyone who has first hand experience with NetCDF and what tools they used.


texts:
I would like to create a MongoDB database that would be very similar to how various news services store and display their data. The database would be queried by an android phone.

Let's have a look at the BBC Android app:

You can see that the news stories are broken up into their various continents, Africa, Asia with a little picture and a text description of the story next to it.

I would like to find the best schema one for this.

There are 2 scenarios of DB schema I can think of:

Scenario 1

I would store all the information used to render the view within one document in a collection called "NewsGroup". The phone would need to make one database call to get all the information and this is quick and efficient.

If the user clicks on a story, the storyId could be used to pull the story out of the "Story" collection to get further details on the story, like the actual text of the story, readers comments etc.

The disadvantage of this scenario is that the information seems to be duplicated (title and picUrl are the same in both collection "Story" and "NewsGroup") and I'm scared this might lead to redundancy.

Scenario 2

I only store the storyId in the "NewsGroup" collection. The phone needs to make 2 calls to the database, one to the "NewsGroup" collection to get the list of all storyIds per continent and then one to the "Story" collection to get the story title and picUrl so that the list can be displayed. This might cause delays in the user experiences however it would improve the integrity of the databases as there is no redundant information.

General Comment:

It would be easier if MongoDB has a join operation like SQL to combined both collection when the phone queries the database hence one single operation on the server side, but MongoDB cannot perform joins.

I can also use MapReduce to simulate a join but I'm concerned about the slow performance of MapReduce in MongoDB: https://stackoverflow.com/questions/3947889/mongodb-terrible-mapreduce-performance


texts: How can I use Invoke-SqlCmd with variables to output the SQL command only or some similar method?

texts:
I have a big *.sql file that I run with Invoke-Sqlcmd and variables to do some tasks, and when I have an issue, it's a pain to debug because of the variables and I want to do logging of the SQL script about to be run.

Is there a reasonably native way to produce the final SQL about to be executed with variable substitution without actually running?

Here's a sample I run to restore a *.bak to various environments for example:

Invoke-Sqlcmd -InputFile $sqlFile -QueryTimeout 0 -ServerInstance 'SQLServer' -Database 'Master' -Variable @("DatabaseBAK=$TransactDB", "Database=$TargetDatabase") -Verbose

SQL File:

USE [master]

print 'Killing connections to database $(Database)'
DECLARE @kill varchar(8000) = '';

SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('$(Database)')
 and session_id <> @@SPID
EXEC(@kill);
GO
USE [master]

print '[Info] RESTORE DATABASE [$(Database)]'
print '[Info] FROM DISK = N''$(DatabaseBAK)'''
print 'Starting restore-------------------->'
RESTORE DATABASE [$(Database)]
FROM DISK = N'$(DatabaseBAK)'
WITH FILE = 1
    ,NOUNLOAD
    ,REPLACE
    ,STATS = 5
GO
print '<--------------------End restore'

I would like to somehow just output the above with variable substitution the way Invoke-SqlCmd does it.


texts: Postgres table size is not growing after adding row

texts: I couldn't see any growth in table size when I added a row in the relevant table. I see the same memory space even after adding 5 rows. Could anyone please help me and explain what I'm doing wrong?


texts:
I am not able to find concrete information about patching after install. So I have downloaded Oracle 12cR2 software from Oracle and I have successfully installed it. But now I am wondering if I need to apply the latest CPU (database and ojvm update)?

edit: So as my question state then should I apply the latest CPU (database and ojvm update) as a the first order of business after fresh install?


texts: Execute T-SQL Only If AlwaysOn Database Is Primary

texts:
Little background on the system. It is SQL Server 2012 AlwaysOn Availability Group with 1 primary and 1 secondary.

I have been following this article (SSIS with AlwaysOn) to make SSIS work with AlwaysOn. I have a table that records the current role of the server and a job that runs every two minutes checking if it recently failed over. The problem I am running in to is the secondary only accepts readonly connections for the SSISDB and my script won't execute because of it.

Here is the script:

USE master;

DECLARE @last_role TINYINT;
SET @last_role = (
   SELECT TOP 1 [replica_role]
   FROM [dbo].[replica_role]
);

DECLARE @current_role TINYINT;
SET @current_role = (
   SELECT ROLE
   FROM sys.dm_hadr_availability_replica_states
   WHERE is_local = 1
);

IF (@last_role = 2 AND @current_role = 1)
BEGIN
    USE SSISDB;
    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'x'
    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
END

USE master;
UPDATE dbo.[replica_role] SET [replica_role] = @current_role;

I am confused as to why the error is happening because the IF statement is evaluating to false so the USE SSISDB is not getting executed. I could turn the statements into strings and use exec but I would rather not if possible. When I execute the script this is the error I get:

The target database ('SSISDB') is in an availability group and is currently accessible
for connections when the application intent is set to read only. For more information
about application intent, see SQL Server Books Online.


texts: SQL Server backup versus third party snapshot based backup using VSS

texts:
Is it safe to assume that SQL Server instance or database level backup to a NAS share or tape using the SQL Server backup command (internally create .bak file) are transactionally consistent backups?

Your database can be online while a backup to a shared drive is in progress. How does this type of backup guarantee application consistency?

I am comparing this sort of backup against VSS-aware hardware level snapshot, and the backup option using a third party tool, which claims 100% application consistent backup. VSS performs actions like freezing the IO till snapshot is completed.

How does SQL Server native backup maintain its consistency in absence of VSS?


texts: Mariadb (v5.5.65) replication unsuccessful. Slave_IO_State stuck at "Waiting for master to send event"

texts:
I am trying to configure a master-to-master replication of Mariadb. When I make changes to the master, the slave does not reflect the new changes.
This is the guide that I referred to when setting up the replication: https://www.vpsserver.com/community/tutorials/9/setup-a-master-to-master-replication-between-two-mariadb-servers/
Apologies for using images, as I'm unable to copy the output. Thanks in advance.
SHOW GRANTS

SHOW SLAVE STATUS

SHOW PROCESSLIST;


texts: Is it normal that our Wordpress' database crashed multiple times in this month alone?

texts:
I just started a new job at a small magazine as an IT guy. It all worked fine, but I'm worried about some issues with our VPS installation.

Since I started working here (26 days ago), our Wordpress' databases crashed more than once. When I check with our host, they tell me about possible server overload. I am not convinced.

Actually, it did just happen again, even though we are experiencing one of those slow-news days, and about 30 people were on the site when the crash occurred.

To be honest, I worked a lot with Wordpress and this never happened to me, let alone at this frequency. My colleagues told me that this is how things were always going.

Is this normal? Should we be looking for another contractor?

EDIT — some technical details on our setup

The technical details are a bit problematic: the company hired a third-party contractor to manage all the hosting stuff. Those guys refuse to cooperate with me, resisting even giving me information I requested and proper access to log files.

But what I know is that it's a VPS running under Ubuntu with NginX and MySQL (no details on the versions used.).

When I asked for log files related to the last crash, I got a long list of this :

160226 12:56:35 [ERROR] /usr/sbin/mysqld: Table '<table_name>' is marked as crashed and should be repaired
160226 12:56:35 [Warning] Checking table:   '<table_name>'