texts:
I'm trying to fill a table with the results of a query, however, the results of said query depends on the result of other queries and then it ends up having several thousand selects making the process really long. I was wondering if what i'm trying to achieve is possible through a single query.

What i want to do is this.

Table: Store
fields: storeid, store_name

Table: Product
fields: codproduct, product_name, price1, price2

Table: PriceList
fields: pricelistid, codproduct, storeid, price1, price2

I'm keeping base prices in the product record itself and custom prices per store in a the table called "PriceList". i get the storeid from the user that is logged in.

I'm currently running a query to let me know if there is a custom price from where the user is logged on, if there is none, then i use the base prices in the "productos" table. But i'm running several thousands of selects each time.

Is there a way to acchieve that in a single query?

the final query needs to return:
codproduct, product_name, price1, price2

price1 and price2 being the base price from "product" if there is no custom price in the "PriceList" table.


texts: Capture Extended Events Histogram only during time window

texts:
We have a problematic time window during night on the weekends. I want to set up multiple Extended Events sessions to capture some data and get insight.

Most of them will target a file, which I can read later, but my problem is with Histogram.

I can configure a job to alter XE session to start at specific time, but I can't stop the session, because it would delete the Histogram data from memory.

AFAIK I cannot save the Histogram results from memory to disk, while it's running.

Maybe there is a way to only capture data in histogram if I set up a timestamp predicate on the actual Event (haven't tested this) and let the XE session run during the weekend and read the results on Monday.

Is there any other way how to capture the Histogram result only during a specific time window?


texts:
Hello I am in a problem of designing a web-app database schema.

Suppose I have 10 shops and every shop gives deals on every different day Sunday, Monday and so on and for limited time period like some shops give deals from 4 to 5 pm, some 8 to 10 pm, 4 to 10 pm.

Apart from that I have few categories on which a shop will give the deals like beer, wine, foods etc.  For example, suppose categories are 1, 2, 3, 4, & 5.

Shop A gives deals on 1 and 2 on Sunday from 4 to 6 pm
Shop A gives deals on 2 and 3 on Mon from 7 to 8 pm
Shop A gives deals on 1 and 5 on Wednesday from 7 to 8 pm

Shop B gives deals on 1 and 2 on Sunday from 4 to 6 pm
Shop B gives deals on 2 and 5 on mon from 7 to 8 pm
Shop B gives deals on 1 and 4 on sturday from 7 to 8 pm

Shop C gives deals on 1 and 3 on Sunday from 5 to 6 pm
Shop C gives deals on 3 and 5 on wed from 5 to 8 pm
Shop C gives deals on 2 and 4 on fri from 4 to 8 pm

There is a possibility that any shop can provide the same type of deals every day or maybe 3 or 4 or 5 days of the week and the rest of the days are different like

Shop D  gives deals on 1 and 3 on Sunday, Mon, Tue and so on from 5 to 6 pm.

Shop E gives deals on 1 and 5 on Sunday, Mon, Tue and Fri from 5 to 6 pm.
Shop E gives deals on 2 and 4 on Wed and Thu from 4 to 6 pm.

In this case there will be redundancy, duplicity of the same type of data that I also want to stop.

Now I am totally confused how should I design the database for this problem?

Help will be appreciated.

Thanks


texts: Equivalent of MRG_MYISAM in databases other than MySQL?

texts:
Does anyone know if other database have something equivalent to MRG_MYISAM (aka the MERGE table type/storage engine)?

I know about fragmenting, but this is not quite the same AFAIK.  We're using MRG_MYISAM to avoid large amounts of duplicate data across customer specific databases, so MRG_MYISAM is perfect.

That said, I'd like to know if there are equivalent things in other DBs, particularly other open source DBs.


texts: I have many scripts running that truncate entire tables and rewrite data daily, however this  fills my transaction logs very quickly.  All these scripts use INSERT INTO .. SELECT. So recently I've changed my database to the Bulk-Logged recovery model, and I added the with (TABLOCK) to all my insert statements.  This doesn't seem to be doing anything, because according to https://www.mssqltips.com/sqlservertip/1185/minimally-logging-bulk-load-inserts-into-sql-server/ if the table has a clustered or non-clustered index, it still gets fully logged.  Should I then drop all the indexes before inserting, and recreate them after the table is populated?  Or is there a better method to do this?


texts: How to make Swingbench 2.6 and Oracle 18c work together?

texts:
Swingbench 2.6 supports Oracle 12c and I have been using it for 12c successfully. But I want to use it with Oracle 18c for testing purposes.

In case of using different wizards from swingbench\winbin folder I was getting scripts hanging except oewizard script and could only run CRUD tests with it using SYSTEM user instead of SOE.

So how to fix Swingbench scripts and Oracle configuration for that?


texts: MySQL HDD activity with empty process list

texts:
What does this mean and how can I understand what is mysql doing? I use InnoDB, my database is huge. This happens when I disconnect my app.

Thanx.


texts: Controlling how tempdb activity is split across drives (tempdb on both solid state and spinning drives)

texts:
I'm seeing the following tempdb I/O behavior over a one hour period:

There is a fair amount of disk I/O generated by multiple DW workloads executing on the machine, some of which cannot fit in the ~280GB of memory allocated to SQL.  One aspect that is interesting is that so much of the I/O is concentrated on the spinning disk drives (E) rather than the solid state drives (F and G) that are handling I/O much more efficiently.

We have pre-allocated the full 300GB each (600GB total) on the F and G drives to tempdb (using 12 files), and we have pre-allocated 1.3TB to tempdb on the E drive (currently 1 file).  The I/O data above suggests that tempdb usage is distributed across files based on the current size of the file.  I wasn't able to find documentation on this, but I also ran a query like the following to investigate further:

-- While running this query, writes to tempdb are distributed to E/F/G drives
-- in proportion to their current size.  This was shown by both
-- sys.dm_io_virtual_file_stats and the space used on the tempdb files before and after
SELECT TOP 100000000 *
INTO #temp
FROM [A_Really_Big_Table]

The ideal behavior would be that F and G are used exclusively unless they are both full, in which case the spinning disk drive should provide additional tempdb space so that occasional very large workloads can be serviced without running out of tempdb space.

Are we on the right track in observing that tempdb usage is distributed across files based on the current size of the files?  It was a little bit surprising to see this type of distribution rather than to see an equal usage of each file (which likely was the assumption of whoever set up this hardware and decided to allocate just one to tempdb on the spinning disk).

Based on Paul White's comment in response to this question, we are thinking about the following approach:

Shrink the tempdb files on spinning disk.  Based on our inital testing, this should shift the current distribution of work more to the solid state drives
Configure the solid state tempdb files to pre-allocate their space (as we already do now)
Configure the spinning disk tempdb files to start with no allocation.  Make sure that instant file initialization is on.  Tempdb will grow on the spinning disk only as needed (which will likely be at most once per week).
Create a maintenance plan that shrinks the tempdb files on spinning disk after times of peak load, putting the distribution back in favor of the solid state tempdb files.

Does this seem reasonable?  Are there any alternative approaches or potential problems to consider?  We will obviously test the approach to the extent possible, but won't be able to do so on completely equivalent testing hardware.


texts:
We currently have SQL Server Dev, QA, and StagingUAT instances sitting on same VM.  The production resides in its own server.

The issue is, if one database MDF or TransactionLog on Dev, suddently consumes too much space, it wipes out hard drive space from all other Devops instances. They all share the same drive. This is a single point of failure. I inherited this legacy system.

Currently
The Dev, QA, and StagingUAT Database MDF have the same G:\ drive.

Dev Databases: on G:\Dev

QA Databases on G:\QA

Staging Databases on G:\Staging

Transaction Logs are on:

Dev Database: on H:\Dev

QA Databases on H:\QA

Staging Databases on H:\Staging

What is the best way to prevent issue of one instance suddenly takes too much space?

Method 1:
I recommended each Devops Instance having its own drive.

MDF Database:

Dev Database: on A:\Dev

QA Databases on B:\QA

Staging Databases on D:\Staging  (skip C:)

Transaction Log:

Dev Database: on E:\Dev

QA Databases on F:\QA

Staging Databases on G:\Staging

Method 2:

Have each subfolder have a max hard drive space it can consume.
G:\Dev can only take 10 GB max.

G:\QA can only take 10 GB max.

G:\Staging can only take 10 GB max.

We have current rule, databases on the same Devops instance should be on same drive. Currently we can only provision 1 VM for these devops instances.

Just curious best way to resolve this issue. When creating Devops instances,should each instance be separated from other instances?


texts:
Consider a .sql file with a few selects and updates. I would like to run this file from a script using the psql utility:

psql -h whatever.rds.amazonaws.com -U user dbname -f commands.sql

The problem is that I get an interactive screen with the results of the query and an (0 rows)(END), which requires pressing esc in order to go back to the normal shell.

I have tried using --echo-all and/or --single-transaction following the psql manual, with no luck.

How do I run my sql file in non-interactive mode, only displaying the results an exiting the script?


texts: MySQL unique constraint across two columns

texts:
I have a table defining relationships

Src smallint(5) unsigned NOT NULL,
Dst smallint(5) unsigned NOT NULL,
other fields

I need to add a constraint that says if a given values is present in one of the columns, then.
1) Cannot be duplicated in the same column.
2) Cannot be duplicated in the other column either.

This is invalid

src    dst
1      354
666    1

Since the value 1 is present in the first row, it cannot be present in the second row.

How can I define this type of constraint?

I'm doing a lightweight check at application lever. But I want the DB to ensure it.

Update: Currently I have 7 different types of relationships, and one table per relationship type.

Update 2: Originally this was only one table containing all the relationships, now I'm exploding it

# variante
Create TABLE `productsRelationships3` (
  `relSrc` smallint(5) unsigned NOT NULL,
  `relDst` smallint(5) unsigned NOT NULL,
  PRIMARY KEY `src-dst-3` (relSrc, relDst),
  UNIQUE  `src-3` (relSrc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# this is the import
INSERT INTO productsRelationships3 SELECT relSrc, relDst FROM  productsRelationships WHERE relType=3;
DELETE FROM productsRelationships WHERE relType=3;

#this is the retrieval. The dummy rows are there because I do a UNION
#SELECT relSrc, relDst, 3 as relType, relTypeDesc, 0 as fracQty, 28281 as source FROM productsRelationships3 LEFT JOIN productsRelationshipsDesc on 3=relTypeID WHERE relDst=28281 OR relSrc=28281;

# fraccion
#relType is from the old 1-table schema. It's going to be deleted
Create TABLE `productsRelationships6` (
  `relSrc` smallint(5) unsigned NOT NULL,
  `relType` tinyint(2) unsigned NOT NULL DEFAULT 6,
  `fracQty` int(2) unsigned NOT NULL,
  `relDst` smallint(5) unsigned NOT NULL,
  PRIMARY KEY `src-dst-6` (relSrc, relDst),
  UNIQUE  `src-6` (relSrc),
  UNIQUE  `dst-6` (relDst),
  CONSTRAINT `fk_type_desc_6` FOREIGN KEY (`relType`) REFERENCES `productsrelationshipsdesc` (`relTypeID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#import
INSERT INTO productsRelationships6 SELECT relSrc, relType, fracQty, relDst FROM  productsRelationships WHERE relType=6;

The other tables are basically the same as productsRelationships3


texts: Migrating a database from one PostgreSQL server to another cleanly

texts:
I have a Rails app server that uses Postgres for the DB backend.  I'm spinning up a new server and I want to get a clean and up to date copy of the database.  I've tried it once so far while the server was in production by doing the following:

As user postgres: pg_dump databasename > databasename.sql
scp the databasename.sql file over to the new server
As user postgres pn the new server enter psql and create the database and alter ownership
As user postgres run psql databasename < databasename.sql

All of this will occur after I shutdown Nginx/Passenger so all sessions will be terminated and no more data can come in/go out of the system.  The only thing running will be postgres which no one can connect to directly.

Does this sound like a reasonable plan to migrate the database from one server to another?  The database is 12MB in size, probably 30 tables, and has maybe 50K records so it's pretty small.

Any advice on how to do this cleanly and to make sure my data is "sane" would be great.


texts:
In MySQL, how to manually edit values of a BLOB column in few tuples?

For convenience of manual curation of database like this, should the column be created as BLOB or TEXT, or something else at the first place?


texts: Count the total occurences for each day in given dates

texts:
I have this table and don't enough SQL to make a query to achieve the result shown in the image below.

CREATE TABLE myDates (
    id integer NOT NULL,
    startDate date NOT NULL,
    endDate date NOT NULL
);

insert into myDates(id, startDate, endDate)
values
(1, '2017-12-26', '2017-12-29'),
(2, '2017-12-29', '2017-12-29'),
(3, '2017-12-14', '2017-12-29'),
(4, '2017-12-18', '2017-12-21'),
(5, '2017-12-26', '2017-12-29'),
(6, '2017-12-28', '2017-12-29'),
(7, '2017-12-26', '2017-12-29'),
(8, '2017-12-25', '2017-12-27')

Look at all days between 'startDate' and 'endDate' and count the total of occurrences per day:

Expected result

I just added this image from an Excel File to illustrate better what I am trying to achieve. I am moving from excel to sql. Ignore weekends and 22/12 and 25/12.

Regrads,
Elio Fernandes


texts: T-SQL (SQL Server 2012) Update based on lookup table

texts:
I have the following tables related to this question:

CREATE TABLE tblTitan(
titanId int IDENTITY (15483,1) PRIMARY KEY,
userId int UNIQUE NOT NULL,
titanName varchar(15) NOT NULL,
titanExperience int DEFAULT 0,
titanLevel int DEFAULT 1,
titanStep int DEFAULT 1,
titanBattleCount int DEFAULT 0,
titanWinCount int DEFAULT 0,
titanLossCount int DEFAULT 0,
titanDateCreated date DEFAULT GetDate(),
titanDateLegend date DEFAULT NULL,
titanNpcFlag bit DEFAULT 0,
titanActiveFlag bit DEFAULT 1,
titanImage varchar(150) DEFAULT 'basicTitan.png',
titanElementType int NOT NULL UNIQUE,
FOREIGN KEY (userId) REFERENCES tblUser (userId),
FOREIGN KEY (titanElementType) REFERENCES tblElement (elementId),
);

CREATE TABLE tblLevel(
levelId int IDENTITY (1,1) PRIMARY KEY,
levelNo int NOT NULL,
levelStep int NOT NULL,
levelExperienceMin int NOT NULL,
levelExperienceMax int NOT NULL,
);

Scenario

A titan has battles with other titans, during which an algorithm calculates a winner, and xp is awarded to the titan. After each battle, I want to check if a titan's experience crosses a threshold from the tblLevel table, which is a lookup table.

tblLevel has values such as the following:

levelId 1

levelNo 1
levelStep 1
levelExperienceMin 0
levelExperienceMax 100

levelId 2

levelNo 1
levelStep 2
levelExperienceMin 101
levelExperienceMax 200

levelId 3

levelNo 2
levelStep 1
levelExperienceMin 201
levelExperienceMax 300

Question

Using t-sql code, how may I update the tblTitan level and step values based upon the experience the titan has? eg: if a titan has 110 experience, I want to update the titanLevel and titanStep values from (1, 1) to (1, 2), as dictated by the tblLevel lookup table.

I have tried fiddling around with the update function, but I am not too sure how to use a subquery or other method to be able to check if the experience is between the min and max ranges.

An example attempt of my code is:

UPDATE tblTitan t, tblLevel l
AS
SET t.titanStep = l.levelStep
WHERE t.titanExperience = >= l.levelExperienceMin AND <= l.levelExperienceMax

This sort of command SEEMS to make sense in my head, but is completely invalid in T-SQL. If somebody could please help me with the code or point me in the right direction that would be greatly appreciated!


texts: Save SQL server job output results to text file

texts:
I would like to save results of the SQL job to a text file.

This is the query

SELECT sqltext.text,
req.session_id,
req.blocking_session_id,
req.cpu_time,
req.wait_type,
req.status
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Currently when I choose as below, it only written

  Job 'test2' : Step 1, 'check' : Began Executing 2019-07-31 23:04:17

text'

I need the query results saved in text file. Is there any way?


texts:
I got a set of files from a client which are, as far as I can tell, copies of a database in Oracle 11.

Each folder has the following structure:

- init<DB>.ora
- orapw<DB>
- arch
  - dbf or dbf.gz files
- bkup
  - gz files

How can I import these into Oracle 12? Or should I try installing Oracle 11?

I'm new to Oracle, please excuse me.


texts: MariaDB: Created_tmp_disk_tables high after start

texts:
today i looked in the status of my MySQL server which is based on MariaDB and i noticed that
Created_tmp_disk_tables is very high (over 168k). I think this is the reason for my high I/O usage. I try to optimize this by increasing tmp_table_size and max_heap_table_size and some other config parameters.

Now i restarted the server and took a look at the status variables:

Created_tmp_disk_tables 12
Created_tmp_tables      82

The server is running for 20 seconds, but why it has created 12 tmp tables on the hdd? I set max_heap_table_size  and tmp_table_size to 256MB so it's not possible that this is full after 20 seconds.

After 5 Minutes the Status looks like this:

Created_tmp_disk_tables 40
Created tmp tables 3300

The difference is not as big as it was before increasing the RAM. But i can't understand why tmp tables are created on the hdd when enough RAM is avaliable. I think this is not good because it slow down the querys and create high I/O usage. My server also have enough RAM, so that i want to avoid this and make MariaDB to create all tmp tables in the RAM.

I use MariaDB on Debian 7.3


texts: MySQL Slow Query Log - SELECT /*!N SQL_NO_CACHE */

texts:
Using mysqldumpslow, I can see that the most common entries in a MySQL slow query log are of the following form:

SELECT /*!N SQL_NO_CACHE */ from wp_posts (or wp_comments).

I can't seem to find much information about what this statement means and whether I need to worry about it enough to try and track down where WordPress is creating this SQL.


texts: Is there a Storage-Engine/DB that logs all changes and allows selective rollback of specific changes?

texts:
Is there a storage-engine or database that naturally support this use-case:

1) Key-Value storage. Small Fixed Length Key, and value within [1KB, 64KB]
2) Access is read-mostly
3) Latency more important than throughput.
4) Use discs for values; not a memory-database, but keys could be kept in RAM.
5) Transactions (that involve several values) are NOT required
6) It should be possible to roll-back to a point-in-time (say, 1 week ago max).
7) All changes are attributed to some specific entity (a user)
8) It should be possible to roll-back changes done by a specific group of users
   within a certain time interval.
9) Rollback happens rarely, so transaction logs should not be kept in RAM.
10) Online backups are possible.
11) I should be directly or indirectly accessible from a JVM.

While I know several engines / DB that generally fit, point 7 and 8 are more problematic.

What would fit this use case best?

[EDIT] This is my first "independent"/private project and I don't have much money to invest for software. Also I'm not sure yet if I will charge for it, or open-source it when it is done, so I would prefer a free storage, or at least one with a free version. Here a clarification of some questions raised in the first answer.

3) I'm sold to SSD already.
4) I said "use disc" simply because the data will not all fit in the RAM I can
   afford. Also I suspect I will have a lot of "cold" data, that gets created once
   and never accessed again, so it would seem a waste to keep it all in RAM, as
   would be the case with VoltDB, for example.
7) Adding explicitly the UserID is not problem in most database. I just thought that
   some storage system might have some kind of "special" ID that makes rollback
   easier.
8) This is where the real problem is. Writing a traditional "undo/rollback" by hand
   is time-consuming and error-prone. I know I can do all the rest with most DB.
   What I'm looking for is one that implements the undo/rollback for me, given some
   transaction ID or similar. And most importantly, without undoing other
   independent transactions that happened before or after.
9) I think redis does that; not sure.
10) I was (implicitly) thinking about a free one, where it's not always a given.


texts: Connect to a Windows Authentication SQL server in SQL Server Authentication mode?

texts: Is it possible to connect to a SQL server in SQL Server Authentication if the server has been installed with Windows Authentication?


texts:
I know stored procedures are more efficient through the execution path (than the inline sql in applications). However, when pressed, I'm not super knowlegeable about why.

I'd like to know the technical reasoning for this (in a way that I can explain it to someone later).

Can anyone help me formulate a good answer?


texts: MySQL id not saving in order (Laravel in production)

texts:
I'm really confused about this..
So, I created a Laravel app and hosted it on Heroku. I'm using ClearDB extension to be able to use MySQL. Problem is: when I save a new User on my DB, it is not being saved in ID order.
I got id 1, then id 11 for the second register, then id 21 for the third... Then I deleted them and tried again, and I got id 31. I think, there's a pattern, huh? It's going +10, +10... But why?
Look, the code I'm using to save a new register is only: DB::table('registers')->insert($registerData);
On the $registerData variable, I have only the following data: name, e-mail, a url for the picture and birth date.
Even my "migrations" table is in this order: 1, 11, 21, 31...


texts: 4 tables, find department with the highest average salary

texts:
ABC is an organization operating in a certain country. Below are the data for the database that manages the information on this organization's employees:

employee(employee_name, street, city)

works(employee_name, department_name, position, salary)

department(department_name, city)

manages(employee_name, manager_name).

I have 10 different select queries about this database, and I've solved 8 of them, but am stuck on solving 2.

The first query is 'Select the names of the employees that live in the same street and city as their managers'.

The second query is 'select the department with the highest average salary'.

Any help is appreciated, if any more details are necessary, please tell me.


texts: Postgres: Efficient schema for querying with one exact string match and two range matches

texts:
The table I need to query:
CREATE TABLE regions (
    state text NOT NULL,
    zip_begin text NOT NULL,  -- 9-digit zip code
    zip_end text NOT NULL,
    date_begin date NOT NULL,
    date_end date,

    data ...,
)

There are ~50 states and between 0-5M rows per state.  The zip and date ranges might overlap.
The following will be one of the most common queries in my OLTP application:
SELECT data
FROM regions
WHERE state = {state}
  AND {date} BETWEEN date_begin AND date_end
  AND {zip} BETWEEN zip_begin AND zip_end

This query usually yields one row, but may sometimes yield more.
From the Postgres docs, it sounds like a GiST index might do what I need, but I don't really understand how those work.


texts: According to the restrictions on stored routines and triggers, dynamic sql cannot be used (restriction lifted for stored procedures in version 5.0.13 and later). Why is this limitation in place? And why lift it for procedures, but not functions or triggers?


texts: What is the name for a table that keeps track of operations that have been performed on the database?

texts:
It's name is Trans_Log
It has the following fields:

TL_User (database user logged in)
TL_WinUser (name of windows user logged in)
TL_Date (date of operation?)
TL_Type (type of operation / function in the program)
TL_SubType (sub-type of operation)
EntityID1
EntityID2
EntityID3 ...
TL_Message (message relating to operation)
TL_Error_No
TL_Error_Source
TL_TableName
TL_RecID
TL_Prior_Value (value before operation)
TL_New_Value (value after operation)
TL_FormName
TL_FieldName
TL_CreatedUser
TL_CreatedDAte
TL_ModifiedUser (Seems like it would be for keeping track of an optimistic lock)
TL_ModifiedDate (Seems like it would be for keeping track of an optimistic lock)

I've seen tables like this before, and I know you can find out the actions of users in the database from them.  But I don't know this sort of table is called exactly.


texts:
I am creating a RESTful API. I am struggling to decide on the best way to design my database tables around my resources.

Initially, I though a table per resource would be a good way to go, but I'm now worried that this will result in exponentially bigger tables the further down the resource chain you go.

For example, imagine I have three resources - users, clients, sales. Users are subscribers to my api, clients are the users customers, and sales are purchases made by each client to the users account.

A sale resource is accessed as follows

GET /users/{userID}/clients/{clientID}/sales/{salesID}

So if there are 10 users, each with 10 customers, and for each customer there are 10 sales, the table size gets larger the further down the resource chain we go.

Im fairly confident that SQL can cope with large tables, but I'm not sure how read and writes will slow things down. The example above maybe doesn't illustrate it, but my api will have progressively more writes and reads the further down the resource chain we go. I therefore have the scenario where the biggest tables in my database, will be read and written to more times than smaller tables.

It will also be necessary to join tables before running queries. The reason is that I allow each user to have a client with the same name. To avoid getting the wrong client data, the users table and clients tables are joined by {userID}. This is also the case for sales. Will joining large tables and running reads and writes slow things down further?


texts: Problem when extracting the structure of DB2 (Back up Structure)

texts:
I want to extract the structure of a 9.7 DB2 database to use to clone it but some of the authorization statements are missing afterwards:
I tried something like this:

db2look -d dbName -a -x -o db2look.sql

what I noticed, is that the Authorization Statements on User Defined Functions and Stored Procedures are missing.

I expected to see something like this: https://www-01.ibm.com/support/docview.wss?uid=swg1IC65658

EDIT: A try indicated, that this command correctly extracted the GRANTstatements on the productive system.


texts: Microsoft SQL Server Error 1475 when Mirroring

texts:
Could anybody help me please.

I am trying to mirror our SQL Server 2005 to our DR site. I have successfully done half of the databases without any issues.

However, the remaining ones all have the below Microsoft SQL Server Error 1475:

Database mirroring cannot be enabled because the "DBNAME" database may have bulk logged changes that have not been backed up. The last log backup on the principal must be restored on the mirror. (.Net SqlClient Data Provider)
I am not sure why this is happening as I am doing nothing different to the ones that work and that is as below. The only difference is these databases are slightly larger in size than the ones now mirroring.

Change recovery Mode to Full,
Take a full backup on Principal,
Take a log backup on Principal,
Copy to DR and restore both with Norecovery,
Start Mirroring on the Principal.
I have researched the error but most of the comments state to do the above but this is not working for me.

Any help much appreciated.


texts: MariaDB default user accounts created during installation?

texts:
I have installed MariaDB 5.5.57 and phpMyAdmin 5.1.0 on my QNAP NAS.
I notice that 7 user accounts were created during installation;

What's the purpose of the three "Any" accounts, and do I need them?  (In other words, can they be safely deleted?)
Then why are there 4 "root" accounts? And again, do I need all of them?
Any help is appreciated.
Many thanks in advance - from a newbee :-)


texts: Does an analysis/optimization tool like SqlYog exist for SQL Server 2005 (and later)?

texts:
I'm curious about effective space allocation of my tables, so:

does something like SqlYog exist for SQL Server 2005 (and later)?

Of course I know SSMS, but I was looking for something more precise like column space allocation, and so on... I'd like to find a tool for analysis and optimization of structures and queries.


texts: Why do sequential GUID keys perform faster than sequential INT keys in my test case?

texts:
After asking this question comparing sequential and non-sequential GUIDs, I tried to compare the INSERT performance on 1) a table with a GUID primary key initialized sequentially with newsequentialid(), and 2) a table with an INT primary key initialized sequentially with identity(1,1). I would expect the latter to be fastest because of the smaller width of integers, and it also seems simpler to generate a sequential integer than a sequential GUID. But to my surprise, INSERTs on the table with the integer key were significantly slower than the sequential GUID table.

This shows the average time usage (ms) for the test runs:

NEWSEQUENTIALID()  1977
IDENTITY()         2223

Can anyone explain this?

The following experiment was used:

SET NOCOUNT ON

CREATE TABLE TestGuid2 (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))

CREATE TABLE TestInt (Id Int NOT NULL identity(1,1) PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))

DECLARE @BatchCounter INT = 1
DECLARE @Numrows INT = 100000

WHILE (@BatchCounter <= 20)
BEGIN
BEGIN TRAN

DECLARE @LocalCounter INT = 0

    WHILE (@LocalCounter <= @NumRows)
    BEGIN
    INSERT TestGuid2 (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
    SET @LocalCounter +=1
    END

SET @LocalCounter = 0

    WHILE (@LocalCounter <= @NumRows)
    BEGIN
    INSERT TestInt (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
    SET @LocalCounter +=1
    END

SET @BatchCounter +=1
COMMIT
END

DBCC showcontig ('TestGuid2')  WITH tableresults
DBCC showcontig ('TestInt')  WITH tableresults

SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate)) AS [NEWSEQUENTIALID()]
FROM TestGuid2
GROUP BY batchNumber

SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate)) AS [IDENTITY()]
FROM TestInt
GROUP BY batchNumber

DROP TABLE TestGuid2
DROP TABLE TestInt

UPDATE:
Modifying the script to perform the insertions based on a TEMP table, like in the examples by by Phil Sandler, Mitch Wheat and Martin below, I also find that IDENTITY is faster as it should be. But that is not the conventional way of inserting rows, and I still do not understand why the experiment went wrong at first:
even if I omit GETDATE() from my original example, IDENTITY() is still way slower. So it seems that the only way to make IDENTITY() outperform NEWSEQUENTIALID() is to prepare the rows to insert in a temporary table and perform the many insertions as a batch-insert using this temp table. All in all, I don't think we have found an explanation to the phenomenon, and IDENTITY() still seems to be slower for most practical usages. Can anyone explain this?


texts:
I've a file with the SQL statements to create tables, indexes, etc, and another file with data test to use in the test database. Then, I want that they can be removed.

How to remove table and indexes from a database? Is there a generic way to remove them in MySQL and PostgreSQL?


texts: InnoDB Read and Write speed constantly spikes

texts:
I am not a dedicated mysql admin but my friend and I have been working hard to fix the badly configured mysql files. We have reduced our number of pools to 2 from 20 and increased pool size to 90GB for a 124GB ram server. I've also enabled file_per_table. What is bugging me is the constant spikes in disk read/write speeds. Picture below is while the server is under decent load (file parsing into db) and it has been up with the updated config for about two days.

my.cnf file

max_connections = 1000
max_connect_errors = 10
max_allowed_packet = 1G
binlog_cache_size = 1M
max_heap_table_size = 3072M
table_open_cache = 20000
table_definition_cache = 20000
read_buffer_size = 64M
read_rnd_buffer_size = 8M
join_buffer_size = 64M
sort_buffer_size = 64M
key_buffer_size = 4M
thread_cache_size = 8
query_cache_size=0
query_cache_limit = 128K
ft_min_word_len = 4
memlock
thread_stack = 256K
transaction_isolation = READ-COMMITTED
tmp_table_size = 3G
tmpdir = /mysql-tmp
group_concat_max_len = 33554432
ignore-db-dir=lost+found
server-id = 1
secure-file-priv = "/tmp/"

# *** INNODB Specific options ***
innodb_buffer_pool_size = 92160M
innodb_buffer_pool_instances=2
innodb_data_file_path=ibdata1:12M:autoextend
innodb_file_per_table=ON
innodb_data_home_dir=
innodb_thread_concurrency = 0
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_log_buffer_size = 8M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_io_capacity = 30000
innodb_max_dirty_pages_pct = 60
innodb_lock_wait_timeout = 120
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_adaptive_hash_index=ON
innodb_large_prefix
innodb_autoinc_lock_mode = 0

numactl --hardware

available: 2 nodes (0-1)
node 0 cpus: 0 2 4 6 8 10 12 14 16 18 20 22
node 0 size: 65490 MB
node 0 free: 4880 MB
node 1 cpus: 1 3 5 7 9 11 13 15 17 19 21 23
node 1 size: 65536 MB
node 1 free: 9789 MB
node distances:
node   0   1
  0:  10  20
  1:  20  10

Harddisk info smartctl -a /dev/sda -d sat+megaraid,00

=== START OF INFORMATION SECTION ===
Model Family:     Seagate Constellation ES (SATA 6Gb/s)
Device Model:     ST500NM0011
User Capacity:    500,107,862,016 bytes [500 GB]
Sector Size:      512 bytes logical/physical
Device is:        In smartctl database [for details use: -P show]
ATA Version is:   8
ATA Standard is:  ATA-8-ACS revision 4
Local Time is:    Mon Jun 19 10:45:43 2017 CDT
SMART support is: Available - device has SMART capability.
SMART support is: Enabled

We are altering an existing config file and I do not want to touch stuff that I do not know about. I am not a mysql admin. If you see a setting that makes no sense to you, please let me know. In addition to reducing the pool count to 2 from 20 and increasing the pool size to 90gb from 20gb, I've also set read/write thread counts to 16 each. I did not get a chance to restart the server after adding the thread counts (clients are on it all the time) but I've done the same number for my other servers and they all felt much better.

I don't know ... we are all just doing try and see method here but this inconsistent read/write speeds are making me think something is not right and I am not sure where to look.

Update 1

I've written a script to go through the database and call OPTIMIZE TABLE on every table. I do not think that was done before, ever... After I've done that, pool usage value jumped to %95+ on all servers but the efficiency value decreased.

Here is a gist of more information

Thank you for your time!


texts: Will matching SQL Server network packet size to mtu increase performance

texts:
We have recently upgraded our production facility to sql server 2017, and moved to clusterless availability groups. There is a primary, a secondary on site, and a remote secondary. We were experiencing periodic interruptions to synchronizations with the remote secondary. The bandwidth is low at 6G, and sql traffic competes with all other traffic. The good thing is that the AG would "catch up" after 5-15 minutes. In looking into whether there was anything I could do to improve the situation, I discovered experimentally that the network MTU was 1400, and sql's network packet size was set to the default at 4092. As an experiment I set the packet size to 1400 to match the MTU. It has been days since we had an alert about the AG, so it "seems" to have help.

My question is whether this was the right thing to do? I have read many times not to change the network packet size unless MS advises you too, and to never set it less than the default of 4096. And yet...it seems to have helped. So I am looking for input from more experienced folks in a similar situation.


texts:
I have 2 Servers under  SQL Server Cluster (Server 1, Server 2)

2 Databases configured with Availability Group (Database 1, Database 2)

Server 1:
Database 1 PRIMARY REPLICA
Database 2 PRIMARY REPLICA

Server 2:
Database 1: SECONDARY REPLICA
Database 2: SECONDARY REPLICA

If Database 1 is starting to consume a lot of resources and I want to add a 3rd server (Server 3) to lower the stress on Server 1 is this given example possible?

Server 1:
Database 1 SECONDARY REPLICA
Database 2 PRIMARY REPLICA

Server 2:
Database 1 SECONDARY REPLICA
Database 2 SECONCARY REPLICA

Server 3:
Database 1 PRIMARY REPLICA
Database 2 SECONDARY REPLICA

Is this possible to configure with minimum impact on users? I just want to know the possibilities of scaling horizontal with Availability groups as we have not the possibility on scaling vertically by adding resources to the server, the backups can be taken on a secondary replica right?


texts: database design for inventory from transactions

texts:
I have a database design question.

We are working on an existing inventory system where stock is transferred from head-office to branch office, from branch to distributor and from distributor to dealer.

The system is used to generate inventory from transaction tables. For example for a distributor, we can find rows for that distributor from transactions( it marks items further transferred to dealers too).

This was easy as there was simple hierarchy.

Now there is a change. A stockist is being introduced. Stock can be transferred to distributors from branch or from stockist to distributors, any of these two.

Previously, we could get distributor inventory from b2d transaction tables( challan, challanrows, rowdetails).  Now, we have two channels of inward for same distributor.

Should we need change the database design? Do we need some double entry system? Or we should try to get the inventory table by merging data from 2 tables?


texts:
I currently have a foreign key between two entities, and I would like to make that relation conditional to the entityType of one of the tables.  Here's the hierachy of tables, this is done via FK refrences from child to parent

                  Store
            /                \
  Employees                    \
                             TransactionalStores
                            /       |         \
                     Kiosks         |          BrickMortars
                                 Onlines

I currently have a FK relation from Employee to store

ALTER TABLE Employees ADD CONSTRAINT Employee_Store
            FOREIGN KEY (TransStoreId)
            REFERENCES TransactionalStores(StoreId)

I would like to add the conditional:

WHERE TransactionalStores.storeType != 'ONLINE_TYPE'

Is this possible or must I subclass TransactionalStores into two new subTypes (e.g. PhysicalStores and VirtualStores)


texts: Edition upgrade on SQL Server failover clustered instance

texts:
We did Edition upgrade on our active node for the SQL Server instance SQL01 and that was successful.

I have a question/doubt --> Do I need to proceed the same on other node as well since SQL Server or its binaries were installed on passive node too?

If yes, which I assumed and tried to test and went ahead with checking what it shows when I start up the set up:

It takes me all the way to same screen where instance name is selected after selecting edition upgrade under maintenance and I am confused if the upgrade was success on active node?

Please suggest how does the upgrade of edition works on 2 node cluster with SQL Server FCI?

Thanks


texts: Best index plan for a username column inside an innodb table

texts: I have a username column in my table: username VARCHAR(50) I want to index this column, so I can find a username easily and send messages to users like: "this username has already been taken." So what is the best index plan. the table use innodb engine. Can I use fulltext index?


texts: Cannot insert record in remote database - Linked server

texts: I want to insert record into a table in my remote database table.


texts: SIZE is no valid input at this position error in MySQL 5.7

texts:
I want to create user-defined function including SELECT. I tried below SQL script.

DELIMITER //

CREATE FUNCTION SEARCH(TEXT CHAR(250))
    RETURNS VARCHAR(SIZE) # => ERROR: SIZE is no valid input at this position...
    NOT DETERMINISTIC
    BEGIN
        DECLARE result VARCHAR(SIZE);
            SET result = (
            select * from wp_posts
                where post_type='post'
                    AND post_status='publish'
                    AND (post_author='1' OR post_author='2')
                    AND NOT (post_author='3')
                    AND post_title REGEXP TEXT
                    )
                        ;
        RETURN result;
    END //

DELIMITER ;

SEARCH("python") ;

I do not why occur an error "SIZE is no valid input at this position". I missing something?


texts: Can MySQL 5.1 read a MySQL 5.5 MyISAM file?

texts:
We're in the middle of an upgrade from mysql 5.1 to mysql 5.5. Our only remaining holdout is the master, at 5.1, while all the slaves are at 5.5.

However, we have a stats process that runs on an admin slave, which rsyncs the myisam files to the master, who mount it in a stats DB, then imports those stats into the main production db.

All the tables use the default row_format for myisam, which is static. We're only using simple column types: int, varchar, enum, datetime.

We've already run this process, and it didn't fall down, which is encouraging. But I'm concerned with errors that didn't make it into the log.

So the question is: what are the complications of this process? The MyISAM file is created by a 5.5 instance, then moved to a 5.1 instance.

What's going to go wrong?

(This is a duplicate post to https://stackoverflow.com/questions/9415715/can-mysql-5-1-read-a-mysql-5-5-myisam-file, where there was no detailed answer available.)


texts: Counting non duplicates between 2 columns

texts:
Suppose we have two columns: Start & End.
Both columns store an integer id.

For each id in the Start column, I want to be able to determine whether the same id exists in the End column. If not, the count should increment by 1.

How can this be done?


texts: How can I see what query is emitted by the query parser?

texts:
Consider this snippet:

create table tbl_1(name text);
create table tbl_2(name text);

create function my_insert(_name text) returns text
    language sql
as $$
   insert into tbl_2(name)
   values (_name);
   select _name;
$$;

explain analyse
with x as (select name from tbl_1)
   ,y as (select my_insert(x.name) from x)
select 0
;

returns

Result  (cost=12.72..12.72 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
  CTE x
    ->  Seq Scan on tbl_1  (cost=0.00..12.72 rows=1360 width=32) (never executed)
Planning Time: 0.053 ms
Execution Time: 0.013 ms

Now I can understand why the clause x isn't being executed (and also y of course), but the plan doesn't mention anything about what happened to clause y.

This leads me to suspect that the query parser is emitting a query that doesn't contain the clause y.

How can I confirm this? How can I see what the parser is emitting here? Alternatively, how can see what query the planner evaluated?


texts: Restore a Database into a Current Database and merge

texts: I want to know how, or if it is possible, to backup a database then should the user wish, restore that database at a later date and merge it with the one they are currently using.  Both Databases will be using the same tables and have the same fields, the difference is in the data.  Ideally I would like to do this through SQL and not have to get a third party application.  Any ideas or suggestions, I'm kind of in a bind here.


texts: Spin up an extra sql azure instance to reorganize/rebuild indexes with no downtime

texts:
We have a pretty large sql azure database, over 300GB.

Many tables have been altered over time and we would like to create fresh indexes, including clustered ones.
Of course we would like to minimize downtime as much as possible, even if it costs us.

The database is a DTU based, premium instance.
We have upgraded/downgraded tiers multiple times and the experience is pretty seamless.
As I understand it, Azure spins up a new instance in the background, copies the database, and then switches new connections to the new instance.
No transactions are lost in the process and the connection break is very short, less than a minute.

This got me thinking of whether it would be possible to use a similar technique to rebuild indexes (and perform other potential maintenance tasks).
Any idea on how we could achieve this?
We are OK with paying for 2 instances during the transition.


texts: Error “There is insufficient system memory in resource pool 'internal' to run this query.” even after updating to SP3 on SQL Server 2008 R2

texts:
We are getting this error :
“There is insufficient system memory in resource pool 'internal' to run this query.” everyday, and the error lasts for about a minute, after that the queries are executed normally with no error.
At first we assumed this was a bug in SQL Server 2008 R2 SP2 10.52.4000.0 according to the article , and updated to Service pack 3, but the error still continues.
We use the Express edition, and are aware that there is a limit on the ram that SQL server instance can use to run queries. According to this article
The table here shows the limit on SQL Server Express as 1 GB.
On analysing the perfmon logs, the counter

MSSQL$SQLEXPRESS:Memory Manager\Total Server Memory (KB) had value
1443840 KB

and

MSSQL$SQLEXPRESS:Memory Manager\Target Server Memory (KB) had value
1443840 KB

We assume this is how high the SQL Server Express Editions can consume.
Also this was the Memory Consumption report that was taken after we had encountered the problem. Here we see that Stolen Pages are too high, tried searching regarding this and came up with the result that high stolen page value can cause Memory pressure.

Could this High Stolen page value be a reason for the error that we encounter?
The reason these Stolen pages being this high, are they not freed automatically?
How to Free these Stolen pages ?
What other possible reasons can cause this error?


texts: SQL Server Reporting Services Slow TotalTimeDataRetrieval Fast ExecuteReaderTime

texts:
I wrote a report that is executed by a few hundred users each day and the underlying stored procedure for the data set only takes milliseconds to execute.  According to the ReportServer.dbo.ExecutionLog3 table I noticed the TimeDataRetrieval was regularly showing 5-10 seconds.  The AdditionalInfo column shows the ExecuteReaderTime is a few milliseconds as expected and the TotalTimeDataRetrieval shows 5-10 seconds.  What can I do to reduce the TotalTimeDataRetrieval for each report execution?

A few more useful facts.  The server itself has 128 GB of RAM devoted to SSRS and the OS, so it isn't a memory constraint.  The number of rows retrieved is 1-20 rows and estimated memory usage is well under 1,000KB.

My best guess is that it is taking about 5-10 seconds to open a connection to the database.  I believe this can be substantiated by the ConnectionOpenTime attribute in the AdditionalInfo column.  It shows 5-10 seconds and the sum of the ConnectionOpenTime and ExecuteReaderTime equals the TotalTimeDataRetrieval.  Perhaps there is a way to force SQL Server to keep connections open longer?  Maybe the worker threads are being killed after being idle for more than a few seconds?


texts: mariadb import - all rows missing except those in final INSERT of InnoDB tables

texts:
This is a very strange bug. I'm using this on a mac:

mysql  Ver 15.1 Distrib 10.4.6-MariaDB, for osx10.13 (x86_64)

If I import an SQL dump of a WordPress DB (so not very complicated structure, but some long lines) from the command line, the table structure for all tables appears normal, but lots of content is missing from InnoDB tables.

Specifically, where the INSERTS have been 'chunked' into groups of records, only the final group is there.  If I use --skip-extended-insert to write a statement for every record, only one record is ever in the InnoDB table.

MyISAM data seems fine. Using MySQL 8.0.16 is fine, it's MariaDB that isn't.

(I've tried dropping and recreating the database).

Note: I had very similar, possibly identical, problem with Drupal on MariaDB recently when clearing the cache - which rebuilds the router table, but many rows were missing.


texts:
I am attempting to execute long running reads against a table which is also the target of insert bulk queries from sqlbulkcopy. In order to achieve this I have added the with(nolock) to the query shown below:

SELECT  MAX([ModifiedDate])

FROM    [dbo].[LogEvent] WITH(NOLOCK)

WHERE   [CompanyId] = '00000000-0000-0000-0000-000000000000'
AND     [UserId] = '00000000-0000-0000-0000-000000000000'
AND     [ComponentId] = 64
AND     [LogLevel] >= 70
AND     [LastUpdate] >= '2016-01-01'

However it seems that when the bulk inserts try to obtains their IX and X exclusive locks they are blocked by the select query and therefore fail.

I believe this is because the LogEvent table is a heap table with no clustered index and therefore even with NoLock a HOBT shared lock is taken out.

Will the HOBT shared (S) lock prevent inserts or does this only apply to lower level locks?

If so could I fix this simply by adding a clustered index?

This is very similar to this question Shared Lock issued on IsolationLevel.ReadUncommitted but I'm looking for an explicit confirmation that this will prevent inserts?


texts: Failover Availability Group from linked Server

texts:
I am hoping you can all help me in what feels like a really simple problem, but one I am struggling to find a solution for.

We are running SQL Server Standard 2016 and utilize availability groups between the 2 nodes. Being on standard, I can only have 1 database per Availability group.

Once a month, we need to patch our server, and it's an absolute pain to manually fail-over each group and then back for patching.
My solution is that I have a 3rd server, which has Nodes 1 and 2 as linked servers and this helps me manage the 50 odd availability groups as well as some other application related things we do.

I have a connection to the 2 nodes, and as a job, I collect and update a table on server 3 with each database, availability group name, node name, etc for the 2 main nodes. this runs every 5 minutes and alerts me of issues.

My final step is to now have a stored procedure on server 3, that will simply allow me to failover the availability groups or just a single one ect. It works perfectly except on the one node and on the other, i get this error :

  Availability-group DDL operations are permitted only when you are using the master database.  Run the USE MASTER command, and retry your availability-group DDL command.

My code for this command is as follwos :

SET @Query = 'EXEC (''ALTER AVAILABILITY GROUP [' + @CurrentAG + '] FAILOVER'') AT [' + @FailoverServer + '];'
        EXEC sp_executesql @Query

Which ends up with this command :

EXEC ('ALTER AVAILABILITY GROUP [MyAvailGroup] FAILOVER') AT [DestinationLinkedServerNode];

Thank you for any help on this.


texts: Include all records in between two dates

texts:
I have a table with the columns ID, start_time, and end_time.
I would like to extract all the records that are active during a date range.
  ID | start_time |  end_time
 ----|------------|------------
   1 | 2019-07-22 | 2019-07-25
   2 | 2019-07-20 | 2019-07-25
   3 | 2019-07-20 | 2019-07-21
   4 | 2019-07-20 | 2019-07-25
   5 | 2019-07-22 | 2019-07-22
   6 | 2019-07-25 | 2019-07-25
   7 | 2019-07-26 | 2019-07-27

Example Query parameters

start: 2019-07-22
end: 2019-07-25

Expected results (5 rows):
 ID | start_time |  end_time
----|------------|------------
  1 | 2019-07-22 | 2019-07-25
  2 | 2019-07-20 | 2019-07-25
  4 | 2019-07-20 | 2019-07-25
  5 | 2019-07-22 | 2019-07-22
  6 | 2019-07-25 | 2019-07-25

the row containing ID 7 is omitted, because it is not active in the specified date range
I want to include the records with start_time 2019-07-20 because they are actually active on July 22.
If I use the following:
where start_time >= '2019-07-22' and end_time <= '2019-07-25'

...then I am excluding the records starting on July 20.
How can I include those records?


texts: When should I NOT set the Maximum Server Memory option in SQL Server?

texts:
In SQL Server, I normally set the Maximum Server Memory setting to one of the popular best practices, such as leaving 10% or 4GB, whichever is higher.

http://www.brentozar.com/archive/2012/11/how-to-set-sql-server-max-memory-for-vmware/

I have a new server with 64GB RAM. Due to licensing constraints, this server must run the SQL Server database engine, SSIS, and SSAS. SSIS ETLs happen overnight, and users hit the actual database mostly during the daytime. In this scenario, would it be best to leave the default setting so that SSIS can take the memory if needed, and then the database engine can take memory when it needs it?


texts: When exactly are multiple users unable to simultaneously run a stored procedure with a temp table?

texts:
I have a question regarding a piece of documentation on Temp Tables that I recently read on TechNet.  The fourth paragraph of the Temporary Tables section on that page reads as follows:

  If a temporary table is created with a named constraint and the temporary table is created within the scope of a user-defined transaction, only one user at a time can execute the statement that creates the temp table. For example, if a stored procedure creates a temporary table with a named primary key constraint, the stored procedure cannot be executed simultaneously by multiple users.

I work in an environment where we make significant use of a handful of stored procedures that use indexed temp tables, and we've never encountered an issue where users are having to wait for one execution to complete before the next begins.  I hope that will continue to be the case, but I'm concerned that it could become an issue if this caveat is not properly understood.

Specifically, I am unclear on the following points:

Does this apply only to global temp tables, or to local ones as
well?  It seems strange that a table that isn't visible outside of
the session (as in the latter case) would prevent another session
from executing simultaneously.
What qualifies as a "named constraint"? Don't all constraints have names (even if they are system-generated)?  Is this referring to constraints with a user-defined alias?  This seems like poor phrasing to me.
Does "multiple users" actually mean multiple sessions?  These procedures are called through our application using a single service account, so 99.9% of calls to our scripts are made to the DB by that single account (and I'm unconcerned about the occasional call an admin may make on the backend).  If the service account can run the sproc in multiple sessions simultaneously, then this issue is moot for my purposes.


texts: Difference between Aurora Global database and Aurora regional database with multiple read replicas?

texts:
I am planning to migrate my 300GB RDS PostgreSql database to Aurora MySql using SCT and DMS. RDS Postgresql is in seven regions in current setup. Datapipelines are used to ingest data in these instances and keep them in sync. I was thinking that once I create global database in one region, I will be able to add secondary instances in six other regions. But, I read that global instances support just one additional secondary region.

The only relevant benefits for global database from the docs are:

Having an additional secondary instance will have faster replication
compared to having a read replica.
Faster disaster recovery as the
secondary instance can be promoted to primary under a minute.

Now I am wondering what is the difference between:

Having a Aurora global database in one region with writer and reader, adding a secondary region, adding five read replicas(from primary or secondary)
Having a Aurora regional database (with a writer and reader)  and adding six read replicas.


texts: DBIO Error: Utility 'mysqldump.exe' not found in MySQL home 'MySQL Connector/Net'

texts:
I am using dbeaver 3.6.10 and I would like to export my database (MySQL).

I select the database > right mouse click on it > tools > export database > next > start

I get the following error:

  IO Error: Utility 'mysqldump.exe' not found in MySQL home 'MySQL Connector/Net'

I do know where mysqldump.exe is located on my machine.

  C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe

But I am new to dbeaver and I am not completely sure where I can set the mysqldump.exe value in dbeaver in order to be able to create db dumps.

Can you anyone please point me in the right direction?


texts: Can I rely on the estimate execution plan to recommend indexes?

texts:
I have a database on Microsoft SQL Server 2016, with a table of about one million rows, let's call it books. This query takes more than one minute, which is not acceptable:

select * from books where publisher_id in (857413, 857317, 857316)

There is a proper FK from publisher_id to the publishers table.
I display the Estimated Execution Plan, and it tells me that 100% of the cost is in the "Clustered Index Scan (clustered)" on the primary key of the books table.
And what worries me, it does not recommend an index at all.
By looking at the query, it seems obvious that an index will help. And in fact, when I create the index the query returns results instantly.
Did something go corrupt in my database, maybe statistics? Or do you believe I should nor, in general, trust what I read in the estimated execution plan?


texts: Mix mode auth password,is it sa password?

texts:
During SQL Server 2014 Installation,I have set up Mixed Mode Authentication and given a password for it.My question is when I set Mix mode,did I set up password for SA login.So if want to use SQL authentication then will my login be SA in user name ?

** https://msdn.microsoft.com/en-us/library/ms144284.aspx says -- If you select Mixed Mode Authentication during setup, you must provide and then confirm a strong password for the built-in SQL Server system administrator account named sa. The sa account connects by using SQL Server Authentication.**


texts: How can I speed up this slow simple query?

texts:
SELECT * FROM `game`
WHERE (hometeam=29 OR awayteam=29)
  AND `sportid`=1
  AND`date` BETWEEN '2012-07-01' AND '2013-06-30'
  AND deleted=0

This simple query takes 0.5-1.1 seconds (without caching). The table holes 1.6M rows, so I think it should be much faster (<0.1 second). In the explain below, it shows it's searching through half of the table. You can see I've tried various index variations with no luck, it always wants to use the sportid index.

id | select_type | table | type | possible_keys                                                                            | key     | key_len | ref   | rows   | Extra
1  | SIMPLE      | game  | ref  | date,hometeam,awayteam,sportid,deleted,sportid_2,sportid_3,sportid_4,sportid_5,sportid_6 | sportid | 1       | const | 800368 | Using where

SHOW CREATE TABLE game

CREATE TABLE `game` (
 `gameid` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `sportid` tinyint(1) unsigned NOT NULL DEFAULT '1',
 `hometeam` smallint(5) unsigned NOT NULL,
 `awayteam` smallint(5) unsigned NOT NULL,
 `homescore` tinyint(4) unsigned NOT NULL,
 `awayscore` tinyint(4) unsigned NOT NULL,
 `stadiumid` smallint(5) unsigned NOT NULL,
 `neutral` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `date` date NOT NULL DEFAULT '0000-00-00',
 `time` time NOT NULL DEFAULT '00:00:00',
 `confid` smallint(5) unsigned DEFAULT NULL,
 `srs-predict` tinyint(3) unsigned NOT NULL,
 `srs-teamid` smallint(5) unsigned NOT NULL,
 `crs-predict` tinyint(3) unsigned NOT NULL,
 `crs-teamid` smallint(5) unsigned NOT NULL,
 `winner` smallint(5) unsigned DEFAULT NULL,
 `forfeit` tinyint(1) DEFAULT NULL,
 `playoff` tinyint(1) unsigned DEFAULT NULL,
 `H1` tinyint(3) unsigned DEFAULT NULL,
 `H2` tinyint(3) unsigned DEFAULT NULL,
 `H3` tinyint(3) unsigned DEFAULT NULL,
 `H4` tinyint(3) unsigned DEFAULT NULL,
 `H5` tinyint(3) unsigned DEFAULT NULL,
 `H6` tinyint(3) unsigned DEFAULT NULL,
 `H7` tinyint(3) unsigned DEFAULT NULL,
 `H8` tinyint(3) unsigned DEFAULT NULL,
 `A1` tinyint(3) unsigned DEFAULT NULL,
 `A2` tinyint(3) unsigned DEFAULT NULL,
 `A3` tinyint(3) unsigned DEFAULT NULL,
 `A4` tinyint(3) unsigned DEFAULT NULL,
 `A5` tinyint(3) unsigned DEFAULT NULL,
 `A6` tinyint(3) unsigned DEFAULT NULL,
 `A7` tinyint(3) unsigned DEFAULT NULL,
 `A8` tinyint(3) unsigned DEFAULT NULL,
 `reportedby` int(10) unsigned DEFAULT NULL,
 `lastscandate` date DEFAULT NULL,
 PRIMARY KEY (`gameid`),
 KEY `winner` (`winner`),
 KEY `date` (`date`),
 KEY `hometeam` (`hometeam`),
 KEY `awayteam` (`awayteam`),
 KEY `sportid` (`sportid`),
 KEY `lastscandate` (`lastscandate`),
 KEY `deleted` (`deleted`),
 KEY `sportid_2` (`sportid`,`awayteam`,`date`),
 KEY `sportid_3` (`sportid`,`hometeam`,`date`),
 KEY `sportid_4` (`sportid`,`awayteam`,`date`,`deleted`),
 KEY `sportid_5` (`sportid`,`hometeam`,`date`,`deleted`),
 KEY `sportid_6` (`sportid`,`hometeam`,`awayteam`,`date`,`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=1969140 DEFAULT CHARSET=utf8


texts: SQL Server - Windows Group Authentication

texts:
I am trying to understand how to create Windows Group based access to a SQL Server (2012) database the right way. I was able to successfully add a Windows group to the Server Login and I have a defined Role to which I would like to have the group mapped. My question is, does SQL Server need a User created and mapped to the Windows Group to allow users with Windows Authentication access the database? If so should it be per user or can I just have a single User Name for the Windows Group Login.

Just to demonstrate how I have the current set up:

Windows Group Name  -> domain\AdminGroup
Database Name       -> XYZ
DB Login Name       -> domain\AdminGroup
DB Role             -> xyzAdmin
User Name           -> xyzAdminUser (mapped to Role: xyzAdmin and Login: domain\AdminGroup)

Currently I have created a generic User (not individual ones for each user in the group) and mapped it to the Windows Group Login. But I am not sure why that step is required since it is Windows Authentication. Is that something more of a requirement to have explicit mapping from Login to User Name which SQL Serve requires? Not sure if SQL Server could handle this implicitly behind the scenes if there is any option to map a Windows Group Login to a Database Role. The reason I am asking is, if a User name needs to be created explicitly for either Windows Group as a whole or for individual users, I am not really sure if I as a User would ever need to use it since it is Windows based authentication unlike other login where I use my User Name and password to authenticate.


texts: Select records as groups based on sequence and date

texts:
I have data stored as:

CompID  | Seq | Udate     | Utime
--------+-----+-----------+-------
mx433   | 1   | 1/1/2012  | 13:54
mx433   | 1   | 1/2/2012  | 13:43
mx433   | 2   | 1/4/2012  | 14:23
mx433   | 3   | 12/1/2013 | 13:54

I want to archive the data based on older than 1 year, however I want to keep the last set of records for every company based on the highest sequence number. I can't come up with a SQL statement to select this in order to move to another table.


texts:
I  have a azure sql database in North Europe Region. It is almost 5 MB and it has Basic Pricing Tier. That database is not in Azure VM. I am using PaaS environment.
I configured Geo-Replication between North Europe and West Europe. When I tried to failover from West Europe to North Europe, it took 15 minutes. It is unacceptable, 15 minutes for 5 MB. Do you have any idea to reduce failover time?

Thank you


texts:
You can calculate cache misses as described here.
However, I am interested in how to minimize the phenomenon in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit.
I have some algorithms that are based on hash tables, which cause much cache misses by having random accesses.
I am interested in how you can minimize cache misses in PostgreSQL.

How can you minimize Cache misses in PostgreSQL by design?


texts: Postgres: How to log materialized view refresh

texts:
I would like to keep a record of when various Postgres materialised views are refreshed.  As far as I can see there are no built in logs.  The next obvious choice is to create a trigger, however it seems that triggers cannot be set on postgres materialized views.  I'm running out of options.  Can you recommend a way to do this without reaching up into the application layer?

Many thanks in advance. Max.


texts:
We have a set of large tables (millions of records) on a MySQL DB, having schema like so (simplified):

T1: id, uid, rid, text1, text2, int1, int2, ...
T2: id, rid, tag_id, created_at
T3: id, owner_id, tag_name

Indices:

T1: Primary(id), unique(uid,rid), index(rid), index(uid,int1)
T2: Primary(id), unique(tag_id,rid), index(tag_id), index(created_at)
T3: Primary(id), unique(owner_id,tag_name)

And a requirement to do a select which returns 'rids' having tag_name = XX but not YY:

SELECT t1.rid
FROM t1
LEFT JOIN t2 ON t1.rid = t2.rid
LEFT JOIN t3 ON t2.tag_id = t3.id
WHERE t1.uid = 123
AND t1.int1 = 3
AND t3.tag_name eq 'XX'
AND t3.tag_name != 'YY'
LIMIT 100

This naturally does not work, since the WHERE does not eliminate an rid having more than one tag. How can we achieve this with performance in mind for the large tables?

More about data:

A user represented by uid will have about 100,000 records in T1, out of which about 10% have T2 records 10,000 (rids which are tagged), and less than 10 tags in T3.
There are 1000s of users (uids) in T1.

A given rid can be one of:

) Has a single tag --> a single T2 record
) Has multiple tags --> Multiple t2 records
) Has no tags --> 0 T2 records

We can also alter the table structure and indices for T2 and T3 to accommodate for that, as long as we maintain ability to filter 'tags' and 'T2' creation time.


texts: Running different instances of SQL Server from different networks on one box

texts:
I want to be able to run SQLSMS and select an instance to connect to but the instances are on different networks.  I remotely log on to our server and from there, I want to be able to access our clients servers on different networks.

I currently move data from our client server to our server to process over an ftp connection which takes time.  Things would be significantly faster if I could log on to our server and have all the data already there and accessible essentially removing ftp.

I want to know if this is possible and if so, where can I look or go to accomplish this.


texts:
For reference, this issue was posted on StackOverflow.com before coming here.

In MySQL I am running multiple REPLACE queries to update hardcoded URLs in a database. Queries look like this:

UPDATE some_table
SET some_column = REPLACE(some_column, 'some_url', 'another_url')

The update process goes through every table and every string-based column in the database and runs a query like the above to ensure all potential occurrences are updated.

The system on which this is run also maintains some user content in the database. This content, which is (as far as I can tell) supposed to be unaffected by the update, becomes corrupted:

Before: Welcome to Company’s Learning
After : Welcome to Companyâ€™s Learning

The corruption looks like a string that may contain a unicode character which is not replaced correctly during the REPLACE query.

All tables are InnoDB, and utf8_unicode_ci. The server runs MySQL 5.5.25, on IIS 7.5.

Any idea why this kind of corruption is taking place?


texts:
I have a TVF that when called takes 10 secs to run.

However if I take the query inside that TVF, it takes only 1 sec. I have added correct indexes on this underlying query tables.

In the Execution Plan of the TVF it is performing a table scan. How do I find out why it is doing this?

Thanks.

Query...

select Username, Identifier
from [dbo].Unmapped(@CustID, @StartDate, @EndDate)

Table Scan properties...


texts:
I am trying to access a database in my UAT server through a windows login credential. When I try to access the database, I get the error Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' This windows account has sysadmin rights so I am not sure why I can't access it.
I believe that this database is part of a linked server because when I test the connection to the linked server, I get the same anonymous login error. I asked a fellow team member to try to test this connection, and he says his test succeeded with no error. When I change the security property in the linked server to "be made without using a security context" it gives me this error:
The OLE DB provider "SQLNxxx" for linked server "xxx" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNxxx" for linked server "xxxx".
OLE DB provider "SQLxxxx" for linked server "Mxxxxxx" returned message "Invalid authorization specification". (.Net SqlClient Data Provider)

As you can tell from the error above the linked server is a connecting from one SQL Server to another. I have tried to read from tables and views as well and still receive the same anon error.
I have researched multiple days online and could not find a good resolution. Some of the solutions they suggested are to change the security settings but that one did not work.
Please offer me some advice as what I can do to resolve this issue. Thank you in advance.


texts:
select date '0-1-1';

2000-01-01

Year 0 doesn't exist according to Wikipedia, so shouldn't Postgresql give an error instead of jumping to 2000?

  Year zero does not exist in the anno Domini system usually used to
  number years in the Gregorian calendar and in its predecessor, the
  Julian calendar. In this system, the year 1 BC is followed by AD 1

https://en.wikipedia.org/wiki/Year_zero

edit: Even though inputs like date 123-1-1 work as expected, somehow they made it so anything less than 100ad would require leading 0's to prevent this weird behavior, ex: date 0001-01-01. Given this information, I tried date 0000-01-01 and it does indeed give an error, specifically "22008 out of range".


texts: Can I speed up a large MySQL/MariaDB join through partitioning or merging?

texts:
I'm trying to run the following join query as part of a more complex one on MariaDB 10.1.26.

select distinct
    project_commits.project_id,
    date_format(created_at, '%x%v1') as week_commit
    from project_commits
    left join commits
    on project_commits.commit_id = commits.id;

Both join fields are indexed. However, the join involves a full scan of project_commits and an index lookup on commits.  This is corroborated by the output of EXPLAIN.

+------+-------------+-----------------+--------+---------------+---------+---------+-------------------------------------+------------+-----------------+
| id   | select_type | table           | type   | possible_keys | key     | key_len | ref                                 | rows       | Extra           |
+------+-------------+-----------------+--------+---------------+---------+---------+-------------------------------------+------------+-----------------+
|    1 | SIMPLE      | project_commits | ALL    | NULL          | NULL    | NULL    | NULL                                | 5417294109 | Using temporary |
|    1 | SIMPLE      | commits         | eq_ref | PRIMARY       | PRIMARY | 4       | ghtorrent.project_commits.commit_id |          1 |                 |
+------+-------------+-----------------+--------+---------------+---------+---------+-------------------------------------+------------+-----------------+

The sizes of the two tables are relatively large: project_commits contains 5 billion rows and commits 847 million rows. Also the server's memory size is relatively small (16GB).  This probably means that index lookups hit the (unfortunately magnetic) disk, and therefore performance takes a heavy hit.  According to the output of pmonitor run on the generated temporary table, the query, which has already run for more than half a day, will take another 373 hours to complete.

/home/mysql/ghtorrent/project_commits#P#p0.MYD 6.68% ETA 373:38:11

Could I somehow increase the query's performance either by partitioning the tables, so that the join can be performed in memory, or by forcing MySQL to perform a sort-merge join? As the time involved for running alternative strategies could be many hours, I'd rather have a suggestion, instead of trying things out.


texts: How to get only the first error with psql --single-transaction

texts:
I'm typing something like:

psql --single-transaction --quiet my_db < my_file.sql

When the script fails I get many lines saying: current transaction is aborted, commands ignored until end of transaction block.

In linux I can use | more to pipe the result, but in Windows thats may take a lot of time (to show the first error, because the pipe is not a pipe).

My question:

Is there a way to get only the first error?

The .sql file is generated by me, I can add some sets or commands if necesary
Y try --file (I obtain the same behavior)


texts:
We've been using MongoDB for several weeks now, the overall trend that we've seen has been that mongodb is using way too much memory (much more than the whole size of its dataset + indexes).

I've already read through this question and this question, but none seem to address the issue I've been facing, they're actually explaining what's already explained in the documentation.

The following are the results of htop and show dbs commands.

I know that mongodb uses memory mapped IO, so basically the OS handles caching things in the memory, and mongodb should theoretically let go of its cached memory when another process requests free memory, but from what we've seen, it doesn't.

OOM kicks in an starts killing other important processes e.g. postgres, redis, etc. (As can be seen, to overcome this problem, we've increased the RAM to 183GB which now works but is pretty expensive. mongo's using ~87GBs of ram, nearly 4X of the size of its whole dataset)

So,

Is this much memory usage really expected and normal ? (As per documentation, WiredTiger uses at most ~60% of RAM for its cache, but considering the dataset size, does it even have enough data to be able to take 86GBs of RAM ?)
Even if the memory usage is expected, why won't mongo let go of its allocated memory in case another process starts requesting for more memory ? Various other running processes were being constantly killed by linux oom, including mongodb itself, before we increased the RAM and it made the system totally unstable.

Thanks !


texts:
I Need to filter the query that have some conditional column as below:

 SELECT
     `c`.`fullname` AS `fullname`,
     IFNULL(`c`.`new_ic`,`c`.`old_ic`) AS `Ic`,
     `bla`.`loan_no` AS `Loan_No`,
     `bla`.`total_loan_amt` AS `Total_Loan_Amt`,
     `bla`.`monthly_installment` AS `Month_Inst_Amt`,
     `bla`.`disb_date` AS `disb_date` FROM (`customers` `c`
 JOIN `basic_loan_application` `bla`
    ON ((`bla`.`customer_uid` = `c`.`customer_uid`)))
 WHERE Ic LIKE '%800%' ORDER BY `bla`.`Basic_Loan_Application_Id` DESC LIMIT 0, 10

But I cannot Use the Ic Field  and return error as decribed below:

 Unknown column 'Ic' in 'where clause'

I want try to avoid nested select if possible.

Is there another solution for this problem?


texts: MultiSubnetFailover option not supported on one server?

texts:
I have a SQL Server Cluster with Availability Groups. I connect to that server from a Windows 2012 R2 server with PHP 7.0.18 and Apache using ODBC and PHP ODBC Drivers.

I have two Windows 2012 R2 servers with the same installation's and configurations. When i try to connect to the SQL Server using the Listener and the parameters multiSubnetFailover=yes;ConnectionPooling=0;  it will fail on the 2nd 2012 Server. But from the 1st server the connection will be successful.

The error message I get on the 2nd server is:

  Connecting to a mirrored SQL server instance using the MultiSubnetFailover connection option is not supported.

When i set MultiSubnetFailover to no it will connect successfully, but I want to set it on yes.

Is there anyone who also had this problem and was able to solve it?


texts: MySQL - Restore 1TB database using myloader

texts:
Im trying to restore a database which is 1.3TB and its in AWS Aurora. I want to restore a copy of this database to an EC2 instance(32 vCPU, 244GB MEMORY).

I used mydumper to take backup from AWS RDS and it took 6 Hrs.

But Im trying to load this backup using myloader. Its running more than 12hrs, but still 30% data only restored.

Myloader Settings:

Threads: 200
Queries Per thread: 10000

MySQL Settings:

max_connections = 3000
wait_timeout = 86400
interactive_timeout = 86400
innodb_buffer_pool_size =235G
bulk_insert_buffer_size = 5G
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_read_io_threads = 8
innodb_write_io_threads = 22
innodb_adaptive_hash_index = 0
innodb_old_blocks_pct = 45
transaction_isolation = READ-UNCOMMITTED
innodb_purge_threads = 2
innodb_change_buffering = inserts
innodb_change_buffer_max_size = 30
query_cache_size = 0
innodb_buffer_pool_instances=20
innodb_log_buffer_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 1G
max_allowed_packet=1G

Is there any additional settings that I should change for faster restore?


texts: Can two primary AlwaysOn servers be each other's secondary--for completely seperate sets of databases?

texts:
Can two primary AlwaysOn servers be each other's secondary?
We have a situation where our primary disaster recovery site it also a valid hospital satellite (SQLB) to our main campus hospital (SQLA) in town. The same application (XAPP) is using a local database at each respective site as shown in Figure A.
In theory a disaster "could" occur at each site. So the main campus could be the DR for the satellite site and vice versa. Can AlwaysOn be used to allow each SQL Server to store the other's secondary database/replica? And, even if this is possible, is it a good idea?
Figure A: Can SQLA be the primary for SQLB's databases and vice versa?


texts: Will repeated, identical expressions get processed again?

texts:
I am new to SQL server and I was trying to use an alias in a where clause and then I realized the order of processing makes it hard to do that, so I used this:

SELECT DISTINCT * FROM (SELECT LEFT(fiscal,4) AS fiscalyear FROM
Transactions )      fiscalyears
WHERE fiscalyear >= '2009'

I could have used this too, as it works too:

SELECT DISTINCT LEFT(fiscal, 4) FROM Transactions
WHERE LEFT(fiscal, 4) >= '2009'

Now, the question I have about this is: Is one better than the other method? The query execution plan looks identical for both (I don't know how to read a query plan in depth at all, just saw that it looks similar in both of these cases).
This is just one small table with small number of rows but will there be noticeable differences with huge amounts of data?


texts: AlwaysOn Shared network location change requrest

texts:
We have successfully setup the Alwayson with different subnets and it was working fine.
But unfortunately the existing NAS storage had a problem. Now our storage team provide new storage IP.

We need to replace the existing storage configuration with new one in Shared network location wizard . Is there any way without removing the existing setup due we already configured around 500 database.

Please find the below screen shot and do the needful.


texts: Are WHERE clauses applied in the order they are written?

texts:
I'm trying to optimize a query which looks into a big table (37 millions rows) and have a question about what order the operations are executed in a query.

select 1
from workdays day
where day.date_day >= '2014-10-01'
    and day.date_day <= '2015-09-30'
    and day.offer_id in (
        select offer.offer_day
        from offer
        inner join province on offer.id_province = province.id_province
        inner join center cr on cr.id_cr = province.id_cr
        where upper(offer.code_status) <> 'A'
            and province.id_region in ('10' ,'15' ,'21' ,'26' ,'31' , ...,'557')
            and province.id_cr in ('9' ,'14' ,'20' ,'25' ,'30' ,'35' ,'37')
    )

Are the WHERE clauses for the date range executed before the subquery?
Is it a good way to put the most restrictive clauses first to avoid big loops for other clauses, in order to do a faster execution?

Now queries takes so much time to execute.


texts: Force double quotes in phpMyAdmin export dump

texts:
I have a varchar column in my table but when I export it in phpMyAdmin (4.6.6deb5ubuntu0.5), the values are quoted with single quotes. How do I force double quotes? I don't see a setting.
CREATE TABLE _temp (
  col1 SMALLINT UNSIGNED NOT NULL,
  col2 VARCHAR(255) NOT NULL
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_general_ci;

-- suboptimal dump.
INSERT INTO `_temp` (`col1`, `col2`) VALUES
(1, 'L\'Amour Dure Trois Ans'),
…

-- desired.
INSERT INTO `_temp` (`col1`, `col2`) VALUES
(1, "L'Amour Dure Trois Ans"),
…

Surprised there isn't a question like this on SO yet. Couldn't find anything in the docs either.
I'm aware that I can find-and-replace after the fact. The question is how / whether you can prevent the suboptimal output in the first place.
I deem it suboptimal (not invalid) because I'd have liked to further work with the dump manually and the double quoted version is more human-readable / less error-prone. I am not looking for a permanent change; just a way to have one dump with custom settings.
I perform the export with Table › Export › Custom (SQL).
$ mysql --version — mysql  Ver 14.14 Distrib 5.7.34, for Linux (x86_64) using  EditLine wrapper

mysql> SELECT @@version; — 5.7.34-0ubuntu0.18.04.1


texts: Knowing if Oracle account has been locked?

texts:
We have an Oracle that locks up randomly; we get error ORA-28000: the account is locked.

Is there some log that will tell me if an oracle account was locked throughout the day? Right now the Oracle account works fine.

I don't have admin rights in this database. We have Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production.

Thanks.


texts: Speed and size with hidden index in postgresql

texts:
I have a table with 1M rows without index, and i don't understand why when i go for :  SELECT * from table WHERE id = 954000  i get the result
instantly ! I have no index how its possible to get me the answer
instantly ? To be sure , i run a command to know the size of a possible index :select pg_size_pretty(pg_total_relation_size('geopost') - pg_relation_size('geopost'));
The result suprise me because it outputing me 40kb . I'm litle confuse now , is postgres made a kind of hidden index ?

the query plan:
EXPLAIN ANALYZE
select * from geopost where id = 954000
Gather  (cost=1000.00..14682.07 rows=1 width=38) (actual time=36.633..38.140 rows=1 loops=1)  Workers Planned: 2  Workers Launched: 2  ->  Parallel Seq Scan on geopost  (cost=0.00..13681.98 rows=0 width=38) (actual time=26.701..27.117 rows=0 loops=3)        Filter: (id = 954000)        Rows Removed by Filter: 333333Planning time: 0.087 ms

regards


texts: Insert data between 30 minute time intervals

texts:
I want to divided my to my time interval depending to the start time and end time. To explain it further, let me have this example.
I have this table:

User           Elapsed Time        Start Time            End Time
User01         01:05               07-02-2015 08:45      07-02-2015 09:50

I want to divide its elapsed time to 30 minute time interval so that i will have an output like this.

Interval From        Interval To         User       Elapsed Time
07-02-2015 08:30     07-02-2015 09:00    User01     00:15
07-02-2015 09:00     07-02-2015 09:30    User01     00:30
07-02-2015 09:30     07-02-2015 10:00    User01     00:20

Thanks in advance


texts: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor on linux

texts:
I have configured oracle 12c on Rhel 7. When I try to connect database using following command it gets connected with no issues:

sqlplus system/Forest123@orcl

I'm facing problem when I try to put the connection string with hostname and port as shown below:

sqlplus system/Forest123@localhost:1521/orcl

Error occurred is

  ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

I have gone through the questions asked before, but none of them helped me solve this problem.

Following are my tnsnames.ora, sqlnet.ora and listener.ora files.

tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_12C.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.localdomain)
    )
  )

listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_12C.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

sqlnet.ora

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

hosts file

127.0.0.1 localhost
127.0.0.1 localhost.localdomain
127.0.0.1 localhost4
127.0.0.1 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
::127.0.0.1 localhost.localdomain localhost
172.31.15.86 oracle_12C.localdomain oracle_12C


texts: What will be the behavior of mongodb under this partial network partition

texts:
Consider a shard cluster:

Has only one shard, but the shard has 3 replicas A, B, C
Has 3 mongos instances
mongos and mongod instance are running across 3 different data centers.
The primary node (A) of the replica set is in Data center 1

It will look like this:

Data Center 1    | Data Center 2      |  Data Center 3

mongos  1        | mongos 2           |  mongos 3

Primary node (A) | secondary node (B) |  secondary node (C)

Now, suppose there is a network partition between Data center 1 and 2, A and B cannot see each other, but C can see both. In this case, if I understand correctly, B will try to elect itself to be the primary, but it will fail because A can still see the majority of the replica sets (A and C), and C can still see A.

In this case, if a client talks to the mongos 2 which is running in DataCenter 2, will all the read and write operation for this client fail because the network between Data Center 1 and Data Center 2 is broken, so it cannot reach the primary node?


texts: MySQL insert with value derived from primary id

texts:
I'd like to INSERT into a MySQL table, in such a way that one of the columns holds a value derived from the primary id.

For example:

insert into picture_table ('picture name','folder') values ('holiday_123.png',FLOOR(pic_id/1000)

Where pic_id is the auto_incrementing primary id of the table. It would be even better if this was somehow hard-wired into the table. Can this be done?

The best I've come up with is
insert, then update using last insert ID

The table may look like this:

CREATE TABLE IF NOT EXISTS `picture_table` (
  `pic_id` int(8) NOT NULL AUTO_INCREMENT,
  `picture_name` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `folder` varchar(8) NOT NULL,
  (...other stuff...)
  PRIMARY KEY (`pic_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1


texts: Partitioned view with dedicated "routing" column

texts:
I have an application (Microsoft Dynamics NAV to be exact) that splits up data for different companies in tables with the exact same structure.

I can not change that behavior, so I built VIEWs that make it easier to handle that data for reporting purposes, like this:

CREATE VIEW [ALL COMPANIES$Employee]
AS
SELECT 'Company 1' as Company, * FROM [Company 1$Employee]
UNION ALL
SELECT 'Company 2' as Company, * FROM [Company 2$Employee]
UNION ALL
SELECT 'Company 3' as Company, * FROM [Company 3$Employee]
UNION ALL
SELECT 'Company 4' as Company, * FROM [Company 4$Employee]

That works well for the read case.

Now I want to also use these views for easier interfacing with other applications, that is, inserting and updateing data.

I do know about partitioned views, but this is not exactly the same, as I don't have specific columns with CHECK constraints, but "routing" to the underlying tables would have to be done with only the information of the newly introduced column Company.

Is there anything like that? Otherwise, I'm happy about all other proposed solutions that save me from using dynamic SQL.


texts: Record doesn't show if selecting earlier date interval

texts:
i encountered a very strange behaviour on mysql database.

I have this complicated query:

SELECT
    category, subcategory, sum(hours), count(hours),
    group_concat(concat(user,': ', comment) separator ' ') as comments
FROM
    duomenys, workers
WHERE
    duomenys.user=workers.full_name
    AND date BETWEEN '2017-05-23' AND '2017-05-25'
    AND user='USERNAME'
    AND departament='Some departament'
GROUP BY
    category, subcategory
ORDER BY
    id ASC;

In short it selects records from time interval (concats identical comments and sums hours).

For example:

Category   Subcategory   Hours  Count  Comments
Category1  Subcategory1  2:30   3      Username: User comment.

The problem is, if i increase time interval BETWEEN '2017-05-22' AND '2017-05-25', one record from 2017-05-24 is not displayed. Not sure if it's only this one, a user spotted it missing. I can select any date, or even interval of one day date, it shows ok. But if i select 22 or earlier as starting date, it dissapears from results.

The record is normal, same as others, the query is the same, i cannot spot anything unusual.
Same behaviour happens in phpmyadmin if i use this query directly on the database.

Table structures are very simple, no external keys, etc.:

CREATE TABLE IF NOT EXISTS `duomenys` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Ivedimas` datetime NOT NULL,
`Data` date NOT NULL,
`Category` varchar(255) NOT NULL,
`Subcategory` varchar(255) NOT NULL,
`Hours` int(11) NOT NULL,
`Comment` text NOT NULL,
`User` varchar(64) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=18871 ;

CREATE TABLE IF NOT EXISTS `workers` (
`name` varchar(20) NOT NULL,
`full_name` varchar(80) NOT NULL,
`skyrius` varchar(80) NOT NULL,
`departament` varchar(80) NOT NULL,
PRIMARY KEY (`vardas`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I'm sorry if names don't match somewhere, i tried to translate fields to english, for easier reading.

EDIT:
I thought i found the answer. After carefully examing that record, i found this quotation mark used in the record " instead of “„ like on every other records. But changing the quotation mark didn't help. And it shouldn't, because i use mysql_real_escape_string before inserting into databse.
That record looks like this:

SCR-4.1.1-2017 „Scrambled ciklo, scrambled ir daliųjų medžiagų vežimo sertifikatų išdavimo taisyklės“ projekto peržiūra (pakartotinai).


texts: pt-online-schema-change with circular replication

texts:
So I have an alter table (adding 4 columns) to do on a fairly large table (74 million rows).  The problem is this table has firstly to remain online and writable during the whole operation and secondly is in a master->slave circle of 5 database servers.  (These servers are all running the latest stable MariaDB 5.5).  The table in question is InnoDB with no FK constraints and no triggers.

I've been playing with pt-online-schema-chage (part of the Percona toolkit) and it seems to do exactly what I want to do and isn't too slow that it makes the job unfeasible.

My main fear is replication lag.  The tool is able to check replication lag and throttle itself if needs be but that would only apply to the next slave right?  If it was a slave 2 or 3 levels of replication away, it wouldn't see that would it?

I guess I'm only going to be sure after testing, testing and more testing and then only really when it completes.  Is there a better strategy?  Is it possible to throttle pt-online-schema-change so that I could drag it out to happening over many days instead of many hours?


texts: How do I load just the data from SQL Server 2005 backup into a 2008 R2 database?

texts: I created a 2008 native database and I have the structures in place but need to load data from a 2005 backup.


texts: Obtain a "list" of stored procedures which include INSERTs without naming the columns involved

texts:
As part of some enhancement, a new column has been added to over 750 tables. Now my problem is within almost 3000+ of stored procedures, some old INSERTs do not name columns.

Is there any way I can get a "list" of all the stored procedures that are inserting without column names?

I have tried:

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE definition LIKE '%insert into%'
AND definition NOT LIKE '%) value%'
AND definition NOT LIKE '%)%' + CHAR(10) + '%value%'
AND definition NOT LIKE '%)%' + CHAR(13) + '%value%'
AND definition LIKE '%value%'

But it's still misses a lot.

P.S. - The above query is returning Insert Into (ColumnNames) SELECT scenario too , which i don't want.


texts: How do I execute an Oracle SQL script without sqlplus hanging on me?

texts:
For an automated task I would very much like to run some SQL scripts and make sure that sqlplus does not hang under any circumstancees, i.e.:

If the script contains any named substitution variable that has to be entered by the user, sqlplus should return with an error instead of prompting for the variable -- I cannot use set define off, as some of these scripts need to accept command line parameters that have to be resolved with &1
The script must not "hang" when it doesn't contain an exit; at the end.

Solved: I think now that I can achieve this by wrapping the sql-script in a secondary "caller script". I.e., the caller script calls the other script with @ and then has a fixed exit; after that. The other script doesn't need an exit that way.
Anything else: If it would require a prompt, it should return with an error.

How can i do this with Oracle (and sqlplus or something else)?


texts: IMPDP Failing with ORA-00001: unique constraint in oracle

texts:
When I try to import a dump which contains data from several tables, the import fails with ORA-00001: unique constraint as there are several entries which are matching in the destination database, but there are several other values in the same table which are not getting imported due to unique constraint error.  I need those values.

Can some one help me out in solving this . I cannot use truncate / replace.

I have used this command:

impdp system/oracle@LIQ727DO directory=DATA_PUMP_DIR dumpfile=EXPDP_ACM_TABLES_LIQ72.DMP
  logfile=IMPDP_ACM_TABLES_LIQ72.log remap_schema=LIQ728:LIQ727 TABLE_EXISTS_ACTION=append


texts: How do I discover the Oracle Express workspace name?

texts:
Is there a way to discover the WORKSPACE name if I've forgotten it?

I cannot login to the APEX admin console since I've forgotten what the workspace name was.

I do know the SYSTEM user password.

Could you please help me recovering my workspace name?


texts: Why different users from same login group can not access the database objects?

texts:
I have SQL login group as "SC_NT\xxxx" in server. There are multiple users using this login group to access database objects. User "A" can able to access db objects from .net application. But when user "B" tried to access same db objects, its showing like

Error: The EXECUTE permission was denied on the object 'stored_procedures',
database 'test',schema 'dbo'.

Both the users are using windows authentication for access the objects.  Kindly give me some clear idea to fix this issue??


texts: pgsql slow query on table of 500 million rows

texts:
I have a table that keeps measurements of latencies between nodes running MPI tasks in a large cluster. The table looks like this:

CREATE TABLE latency(
from_rank int,
to_rank int,
from_host varchar(20),
to_host varchar(20),
from_cpu varchar(20),
to_cpu varchar(20),
latency float8);

CREATE INDEX ON latency(from_host, to_host);

Now after a large experiment I collected over 500 million rows of data. I find querying these data painfully slow, below is an example of a SELECT COUNT(*)

psql (9.4devel)
Type "help" for help.

routing=# \timing
Timing is on.
routing=# SELECT COUNT(*) FROM latency;
   count
-----------
 522190848
(1 row)

Time: 759462.969 ms
routing=# SELECT COUNT(*) FROM latency;
   count
-----------
 522190848
(1 row)

Time: 96775.036 ms
routing=# SELECT COUNT(*) FROM latency;
   count
-----------
 522190848
(1 row)

Time: 97708.132 ms
routing=#

I am running both the PgSQL server and client on the same machine, which has 4 Xeon E7-4870s (40 cores/80 threads in total) and 1 TB of RAM. The effect of Linux file caching is obvious: the first query took well over 12mins while the subsequent ones took about 1.5min.

Is there anything I can do to make the query run faster, since 1.5min isn't exactly responsive.

Thanks.