texts: MySQL - Move data between partitions aka re-partition

texts:
I have a mysql table whose partitions look as below

p2015h1 - Contains data where date < 2015-07-01 (Has data from 2016-06-01. Hence only month worth of data)
p2015h2 - Contains data where date < 2016-01-01
p2016h1 - Contains data where date < 2016-07-01
p2016h2 - Contains data where date < 2017-01-01

I'd like the new partitions to be quarterly based as below -

p0 - Contains data where date < 2015-10-01
p1 - Contains data where date < 2016-01-01
p2 - Contains data where date < 2016-04-01
p3 - Contains data where date < 2016-07-01

I started by reorganizing the first partition & executed the below command. All went well.

alter table `table1` reorganize partition `p2015half1` into (partition `p0` values less than ('2015-10-01'));

Now as the existing partition p2015h2 has data that includes data upto 2015-10-01, how could I move this part into the partition p0 ? I would need to do the same thing with the other partitions too as I continue building the new ones.

I did try to remove partitioning on the table fully, but, the table is billions of rows in size & hence the operation will take days. Post this I will have to rebuild the partitions which will take days again. Hence, I decided to take the approach of splitting partitions.

I'm stuck at this point in time. I'd fully appreciate any guidance here please.


texts: How long has it been since last card top-up?

texts:
We have a table for our stored-value cards:

CardNo   TransactionType   Amount    TransactionDate(datetime2)
         (1 for Top-up, 0 for purchasing products)
=========================================================
0001        1              1000.00    20170809(only date is displayed for brevity)
0001        0              200.00     20170810
0001        0              300.00     20170811
0001        1              200.00     20170811
0001        0              200.00     20170812

We would like to know for each card (customer), the time between each purchase and the most recent top-up at that time. So the intended result is:

RowID   GapDays
================
1       Null (or 0)
2       1
3       2
4       Null
5       1

  Why is there a gap of 2 days for a row id of 3?

The purchase happens before the top-up.

The date column is of type datetime2, has a "time" portion (omitted here for brevity), and the data is ordered by that. You may utilize any index. Non-existent index will be added if necessary.


texts:
My senior DBA told me that SQL Query execution by default doesn't lock the table.

I was having some issues with my SQL Server Reporting Services (SSRS) report which seems to be getting some issues with locking and getting some errors.

I did some Googling but fell short of finding anything.

Do SSRS reports lock the tables that are being queried?

Is there any MSDN documentation that document this behavior specifically?


texts:
Given a table of date ranges, e.g. Start Date, End Date, I've seen a few examples online of how to merge ranges on the fly, but I'm able to merge them as the user makes updates.

So for example, if 01/01 to 01/02 exists and a user inserts 15/01 to 15/02, then the original range can be updated to 01/01 to 15/02.

Is there a well known way for dealing with this, or do I need to work it all out and put it in a stored procedure?

The table looks like this...

CREATE TABLE [dbo].[dateRange](
   [Id] [int] NOT NULL,
   [Start] [datetime] NOT NULL,
   [Finish] [datetime] NOT NULL
)

Given two ranges...

INSERT INTO dateRange (Id, Start, Finish) VALUES (1, '01-01-17', '01-02-17');
INSERT INTO dateRange (Id, Start, Finish) VALUES (2, '03-01-17', '01-04-17');

The user can delete, update and insert new ranges. Deletes are simple. I think there are three cases for inserts (updates have similar possibilities)...

New range covers an existing range...

INSERT INTO dateRange (Id, Start, Finish) VALUES (3, '15-12-16', '15-02-17');

Results in deletion of 1.
New range is covered by existing range. So...

INSERT INTO dateRange (Id, Start, Finish) VALUES (4, '15-01-17', '16-01-17');

.. is ignored.
New range partially overlaps a new range.

INSERT INTO dateRange (Id, Start, Finish) VALUES (5, '15-12-16', '15-01-17');

Insert, again, is ignored and the overlapped row is extended. It's more complicated if both ends of the new range are overlapped because one of the overlapped ranges needs to be deleted.

The window functions sound cool but I'm thinking of doing it in C#.

For every user, I want zero overlapping date ranges. I'm using SQL Server 2014.


texts:
I need to find the airport(departure or arrival) that had the most passengers in the last year.

The tables are:

PASSENGER(id,name,surname)
TAKES(id_passenger,id_flight)
FLIGHT(id,departure_id,arrival_id,departure_date,arrival_date,id_plane)
AIRPORT(id,name,city,country)
PLANE(id,type,capacity)

What I think I need to do is:
Count the number of passengers each airport has and return that
but I do not know how to pick the one with the most passengers among those two.
Do I need to use nested select queries? Would it be useful to use something like

WITH maxdep as
  SELECT ( max passengers from departure airport)
maxar as SELECT  ( max passengers from arrival airport)
select max (val1, val2),  from ... ?

How do I return just one?

EDIT:
I was thinking of a case select from the two previous with statements.
Can that work?

WITH departures as (SELECT departure_id, count(id_passenger)
    from passenger, takes, flight
    WHERE passenger.id=takes.id_passenger
    and takes.id_flight=flight.id
    and flight.departure_date between '01-JAN-2013' AND '01-JAN-2014'
    group by 1
    order by 2 desc
    where ROWNUM <2)

arrivals as (SELECT arrival_id, count(id_passenger)
    from passenger, takes, flight
    WHERE passenger.id=takes.id_passenger
    and takes.id_flight=flight.id
    and flight.arrival_date between '01-JAN-2013' AND '01-JAN-2014'
    group by 1
    order by 2 desc
    where ROWNUM <2)
SELECT CASE
        WHEN departures.count > arrivals.count THEN departures.departure_id
        WHEN departures.count < arrivals.count THEN arrivals.arrival_id
        ELSE departures.departure_id || 'has the same number of visitors as'|| arrivals.arrival_id
        END;

I am sorry if this is messed up

Will this work?


texts: database creation using codeigniter database dbforge class gives "Access denied for user 'username'@'localhost' to database 'database_name' "

texts:
Using codeigniter database dbforge class I have created a database and loaded that database as the current database and created tables in localhost WAMP server. But when I uploaded it to the server I am not able to create the database using dbforge class.

$this->dbforge->create_database($database_name)

This command line gave me the following error:

Error No 1044: Access denied for user 'username'@'localhost' to database 'database_name'

I am using the 'cpanel' hosting provider .

EDIT1: How we can give permission for a mysql user to create database in cPanel hosting provider...?

EDIT2: The Privileges list of user does not show 'CREATE DATABASE' Privilege in Cpanel. The user is created through cPanel and it have been given all the available privileges, but the CREATE DATABASE privilege is not available in that list in cpanel.


texts: ROW_FORMAT - COMPRESSED vs COMPACT vs DYNAMIC

texts:
In MySQL InnoDB, what is the difference between COMPRESSED, COMPACT and DYNAMIC for ROW_FORMAT?

What are the benefits between each other?


texts: User can't see database in SQL Server 2008 R2

texts:
I took a backup of a SQL Server database and restored it as a new database on a different server.

On this new instance I have done the following:

recreated the login,
associated the login with the db user using sp_change_users_login 'UPDATE_ONE', 'username', 'username'
ensured the users default schema was dbo.
ensured the users was a member of the db_readdata role.
checked to make sure the user showed up in sys.dabase_principals
granted the user View Definition
granted connect to the user.

When I log in as the user I still do not see the database.  They can see other databases but not this one.

What else is there to check?

I ran EXEC sp_change_users_login 'Report' and this shows no output however running autofix, or update_one appear to run successfully.  The user also shows up under security>Users.

I am using SQL Server 2008 R2 Developer Edition.


texts: Why is there ASYNC_NETWORK_IO wait type on Shared Memory connections?

texts:
I have an app (windows service) local to the SQL Server box.  It calls a stored procedure on the SQL Server box that returns a ton of data and thus causes ASYNC_NETWORK_IO wait.

The database connection from is of Shared Memory type (I double checked for sanity). Why would there be a ASYNC_NETWORK_IO wait type on Shared Memory connection?

I thought ASYNC_NETWORK_IO had to do with a network lag?


texts: Scaling Percona datacenters: setup and replication

texts:
Since our startup has taken off nicely we are now meeting with some of those issues that you always assumed would NEVER effect you.

We have already scaled alot of our application stack: we offloaded our high read/write tables for temporary information to a separate Percona server where the tables are running with "Engine=MEMORY", as well as migrated other sections to a cassandra cluster.

Now we are left with a "lean" database where we have a read/write load of 88%/12%. At this point i have a few questions i would like to get some feedback on:

1. Read Slaves

With our read/write setup, a number of (e.g. 2-3) read-slaves should reduce the read load to a minimum on our write master. How scaleable is a read-slave solution: if we double/tripple our load is adding additional read slaves going to continue to offer enough capacity for reads? I read up on this post: What are the limits on the amount of slaves per master? however, not coming out of a scaleablility background this this may seem foolish, but is this a freezable solution? There are alot of people pushing sharding rather than a read-slave solution, however, i really dont see a need at the moment with our read/write load to rewrite large sections of our application... any thoughts?

2. Multiple datacenters and replication

Furthermore, we are looking at serving different geographic locations with datacenters near by to reduce network lag (we deal with mobile applications which dont really like lag). The plan would be to use the much mentioned semisync. replication (see: Is it a good idea to split MySQL DB into two servers and Is MySQL Replication Affected by a High-Latency Interconnect? ) for a master-master replication, where each datacenter has a single master, and multiple read slaves. Again, in my naivety, i am very interested in knowing if this would be within the bounds of "best practices" when scaling.

3. Hardware and Config

I have been busy the past few weeks benchmarking our live system, and i have come to the conclusion that, regardless of the solution we choose for Point 1 and 2, the server we are currently using will not make it for very much longer, could i get some thoughts on our setup:

CPU: Intel(R) Xeon(R) CPU E31275 @ 3.40GHz mit 8 cores (hyperthreading)
RAM: 16GB
Raid 10 with a strip size of 64 KB and controller cache enabled
Software: Percona 5.5
Database size: 83.7GB
Top 5 Tables:
 21302MB  table1
 7656MB  table2
 5477MB  table3
 4352MB  table4
 3663MB  table5

my.cnf settings:

 max_heap_table_size=64M
 tmp_table_size=64M
 default_storage_engine = InnoDB
 innodb_buffer_pool_size = 10G
 innodb_file_per_table   = 1
 innodb_old_blocks_time=1000
 innodb_buffer_pool_instances=10
 innodb_log_file_size=256M
 innodb_flush_method=O_DIRECT
 innodb_read_io_threads=10
 innodb_write_io_threads=10
 join_buffer_size = 67108864 #64M
 expand_fast_index_creation=ON

Will moving to a Percona XtraDB Cluster solution solve some of our issues, e.g. replication stability?

I know that these are alot of questions which are very theoretical, and i appreciate anyone who takes the time to read and comment on my thoughts. As a small startup in Europe we really dont have the venture capital to just "go to the cloud", and we prefer to have more control ourselves. While we are looking into consultants, etc. I thought that stackexchange was the right place to bounce some ideas off.


texts: Size limit of character varying Postgresql

texts:
What is the size limit of various data types in postgresql? I saw somewhere that for character varying(n), varchar(n) n must be between 1 to 10485760. Is that true?

What are the valid sizes for character(n), char(n) and text?


texts: What are the right ways to analyse and tune up innodb_thread_concurrency in mysql 5.7?

texts:
I'm in a plan to increase thread concurrency in my production MySQL server 5.7 but not sure, before what are the params/configurations that need to be measured and configured to increase the thread concurrency to the right optimal value. When checked with the MySQL official documentation, there seems to be a note stating

The correct values of these variables depend on your environment and workload.Try a range of different values to determine what value works for your applications. Before limiting the number of concurrently executing threads, review configuration options that may improve the performance of InnoDB on multi-core and multi-processor computers

When referred with other blogs, came up with some formulas as

concurrency = 2 x no.of cpu

And in some other similar blogs, there they stated along with disk as

concurrency = 2 * (NumCPUs + NumDisks)

How to benchmark my MySQL workload to tune up with the thread concurrency?

Currently, I'm having

thread concurrency = 8
RAM = 64 GB
CPU = 20 core

Occasionally, There seems to be sudden increase in overall thread usage by slow queries and all 8 threads were under operation resulting in making normal running queries to get slowed down :(

Hence what's the right way to tune up with thread concurrency ?


texts: SQL to read XML from file into PostgreSQL database

texts:
How can I write SQL to read an XML file into a PostgreSQL XML value?

PostgreSQL has a native XML data type with the XMLPARSE function to parse  a text string to that type. It also has ways to read data from the filesystem; the COPY statement, among others.

But I don't see a way to write native PostgreSQL SQL statements to read the content from a filesystem entry and use that to populate an XML value. How can I do this?


texts: Full Transaction Log on closed source system

texts:
I have a closed source application which has the transaction log filling up on a daily basis to 60+gigs.

I have ran a trace on the database and no inserts and updates are occurring. The following statement though is being executed THOUSANDS of times per minute which doesn't seem right.

set transaction isolation level read uncommitted

If this is not the cause of the issue, is there anything else I can do to find the cause of this issue without turning off full backups as its required.

Thanks


texts: MySQL Cluster - Got error 1601 'Out extents, tablespace full' from NDB

texts:
I am trying to do some tests on MySQL Cluster.
I am creating my database using following commands:

CREATE DATABASE test1;
use test1;
CREATE LOGFILE GROUP lg1
ADD UNDOFILE 'undofile.dat'
ENGINE = NDB;
CREATE TABLESPACE ts1
ADD DATAFILE 'datafile.dat'
USE LOGFILE GROUP lg1
ENGINE = NDB;
CREATE TABLE `dane` (
  `id` int(11) NOT NULL,
  `data` blob NOT NULL,
  PRIMARY KEY (`id`)
)
TABLESPACE ts1 STORAGE DISK
engine=ndb;

Then I populate table with some data and random blobs 50KB each. But I am getting following error:

mysql> INSERT INTO dane(id, data) VALUES(3790, randomBlob(51200));
ERROR 1114 (HY000): The table 'dane' is full
mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1296 | Got error 1601 'Out extents, tablespace full' from NDB |
| Error   | 1114 | The table 'dane' is full                               |
+---------+------+--------------------------------------------------------+
2 rows in set (0,00 sec)

mysql> select count(*) from dane;
+----------+
| count(*) |
+----------+
|     2507 |
+----------+
1 row in set (0,03 sec)

It is not cluster memory usage problem:

ndb_mgm> ALL REPORT MEMORYUSAGE
Connected to Management Server at: localhost:1186
Node 3: Data usage is 6%(164 32K pages of total 2560)
Node 3: Index usage is 7%(178 8K pages of total 2432)

2500*50KB ~= 1GB
I have this error any time I am trying to insert more than 1GB of blobs.
How I can extend size of this tablespace?


texts: Logging/profiling an unpredictably slow stored procedure

texts:
I have a stored procedure that sometimes runs significantly slower than others. I have two different timers measuring the execution time at different scopes. One is in the client code, measuring only the OCI execute call. The second is inside the PL/SQL procedure itself, timing the inside of the procedure's main block.

Normally, if the procedure's timer takes, say, 5 seconds to run, the client's timer is only a few milliseconds greater. This is completely reasonable. The problem is that sometimes the client's timer will be much greater than the procedure's, even double.

I've eliminated any issue on the client or in the transport layer. I enabled client and server logging in sqlnet.ora at support level. On the client, the gap between request and response packets is essentially equal to the client code's timer. On the server, a similar gap is seen between the incoming request and the outgoing response.

The question remains, if the overall call takes 10 seconds, and inside the procedure takes only 5 seconds, where did the other 5 seconds go?

I don't expect the exact answer to the above question, but I would like to hear ideas on how to go about finding the answer. What logs can I turn on to give me insight into what Oracle is doing both after the request arrives but before the actual call to the procedure, and after the procedure until the response is sent back to the client?

Edit:
Perhaps there's something wrong with my timing methodology? I'm using dbms_utility.get_cpu_time() (which provides time in hundredths of a second, hence the division by 100.)

PROCEDURE foo (elapsed_time OUT NUMBER) IS
  start_time NUMBER;
BEGIN
  start_time := dbms_utility.get_cpu_time();
  -- ... PL/SQL stuffs
  elapsed_time := (dbms_utility.get_cpu_time() - start_time) / 100;
END;


texts: Should SSIS packages and SQL database be on same server?

texts:
I'm a bit new to SSIS. I have a lot of 'dev', building, query, tuning performance on SQL Server but am not the master of admin/ overall performance modeling.

So here's my question --- Should SSIS packages be run on the same server as the data warehouse (SQL server databases?)

For one, I already get a cryptic error message that certain 'bulk insert' tasks in SSIS can only be run against local databases. Maybe this can be worked around. Maybe it cannot and is much faster, hence put the ETL on the same box as the db.

Second --- even if that can be worked around, wouldn't it be faster anyway to have the ETL on the same server as the database in any case? Or barring that, as close as geographically as possible? (ie not different continents).

In particular, the ETL ones run overnight, and the DB is not worked on overnight (possibly backup processes, however) -- but I don't foresee much tripping on each other.

If bulk insert tasks can only be done on local DBs, wouldn't the test/ dev environment need to be setup on the DB server as well? Just wondering what is sensible. It's a smaller shop here but we stretch things.


texts:
Experts,
While migrating a database from one server to another, I need to transfer the logins as well. My question is when I script them out and create them on different servers, do those logins have their old password?
If no - Is there a way to transfer the logins between servers without losing the passwords?


texts:
Where can I find the checksum result for a fixpack. I am downloading files, but they got corrupted.
I cannot find an MD5 value in Fixpack central.
Does anybody know if IBM publish them?
Does Download Directory checks a checksum, or it is just like any regular download manager?


texts: Backup status report from multiple server in sql server 2005

texts:
Per daily routine activities of DBA, as we know backups are important to be monitored and to make sure they are corrupt free, i am trying to fetch a report to get such status.

Moreover, we have SQL safe installed on three servers which do a compress backup of larger databases.

I tried a lot to look for a report from SQL safe, to fetch the backup status but could not.

I used a script and put it in a job to fetch the backup status on single server, but for multiple instances where native as well as SQL safe like a third party backup is running, how can i get the backup status on daily basis.

Kindly suggest.

Thanks-- Kapil


texts: MySQL Simple pivot table with MAX(), GROUP BY and only_full_group_by

texts:
I'm having trouble using GROUP BY with only_full_group_by.

Here is my employee_job table:

+----+-------------+--------+------------+
| id | employee_id | job_id | created_at |
+----+-------------+--------+------------+
|  1 |           2 |     10 | 2019-01-01 |
|  2 |           3 |     20 | 2019-01-01 |
|  3 |           3 |     21 | 2019-02-01 |
|  4 |           3 |     22 | 2019-03-01 |
|  5 |           4 |     30 | 2019-01-01 |
|  6 |           4 |     35 | 2019-02-01 |
+----+-------------+--------+------------+

I would like to select only the latest lines per employee_id, which I think gives me:

SELECT *, MAX(created_at)
FROM `employee_job`
GROUP BY employee_id;

The thing is, with only_full_group_by, which I can't disable, I get an error:

#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'XXX.employee_job.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Well... I tried to read about that, and it seems I don't get the error. Of course, if I add other fields to the GROUP BY, the result still contains multiple times the same id.

Can someone explain to me how to group my results, maybe if GROUP BY isn't the best way to do it, what is, please?


texts:
I have an email application that will be called upon to deliver to the UI the number of new messages for a given user on each page load. I have a few variations of things I am testing on the DB level but all are abstracted by the stored proc call.

I'm trying to slam the DB to see what the breaking point (# of requests per second) would be.

In a nutshell, I have a table such as this userId, newMsgCount with a clustered index on userId. SQL should be able to server hundreds or thousands of these responses per second. I think the laggard is my .NET app.

How can I make this a good test to achieve the test results based on SQL performance?

Is there a tool for this that i can give it a stored proc name and param for it to pund my  DB?

I want to see if the DB can return a min. of 250 responses per second.


texts: How do I delete a file that has been locked by UTL_FILE?

texts:
I wrote a flakey PL/SQL script that crashed out and forgot to close the file handle (type UTL_FILE.file_type).

Now I can't delete the file. Short of restarting the instance or rebooting the box, is there anything I can run within Oracle to clear the handle? I've system user access to the box in question.

Update: Oracle is running on Windows.


texts: Connecting to postgres from windows to virtual machine

texts:
I have the following setup

Windows 8
Oracle VM virtual box with arch Linux running on it
Installed and configured PostgreSQL on Linux
Setup the ports in virtual box so that

45432 port on windows goes to 5432 on Linux
40022 port on windows goes to 22 on Linux

On Linux PostgreSQL, I have created user erjik and database mydb.
Firewall is shut down on windows.

The problem is that I am trying to connect from intellij idea using JDBC PostgreSQL  driver, just trying test connection to address:

jdbc:postgresql://127.0.0.1:45432/mydb

username: erjik
password: xx

However, the test connection fails.

pg_hba.conf goes unchanged after installation with following settings:

local all all trust
// IPv4
host all all 127.0.0.1/32 trust
// IPv6
host all all ::1/128 trust

In postgresql.conf I have set only this settings:

listen_addresses = '*'

and

port = 5432

What could be the reason of connection attempt fail? Maybe I missed some settings.

EDIT1:
on Linux side the command:

psql -h localhost -U erjik mydb

executes successfully.


texts: Deny user to see any database through DB_NAME()

texts:
I've already prevented user to see any database (deny view any database to [login]) and it works.

But how to prevent user to list any other database using DB_NAME()?


texts: SQL Server Extended Event - How to reduce the fields of the event "sql_statement_completed"?

texts:
My goal: Use SQL Extended Event to perform Audit (strictly using Standard Edition, not Enterprise)

From previous post, I am able to capture and view the sql statement logged by the Extended Event. However, the event has so many redundant fields, such that it makes the .xel file really big (around 4x the original table for 100,000 new inserts)

The image attached shows all the columns captured. As observed, there are numerous fields: attach_activity_id.guid, client_app_name, cpu_time, ... etc
I was thinking this is supposed configure-able. However, it seems that, there is no checkbox in the configure page, as shown below:

Is it possible to reduce the fields? (basically i only need client_app_name and statement)


texts: How to extract Procedure, Function, etc. Source Code from an Oracle Export

texts:
I have generated an EXPDAT.DMP file of an Oracle database with the command:

exp userid=usr/pass@db owner=own rows=n compress=n

I then ran the following command to generate a file containing the 'Create Table...' statements:

imp userid=usr/pass@db full=y indexfile=output.sql

This is great for the Create Table statements, but I also want the Create Procedure / Create Function / Create View source code. I can see them there in the .DMP file, but I haven't found a way to extract them.

Is it possible to extract this information from the EXPDAT.DMP file? Or will I need to use another method?

The purpose is to have the source exportable to files that can be source-controlled.


texts:
I have the following tables and I want to know Jack's dob, salary and the total score of that employee who is also a player.
The relational schemas are:

Emp_Players (eid,pid,team) composite key is (eid,pid)

Plays(pid, team, matchName, scores) composite key is (pid, team, matchName)

Emp(empID, dob, salary) empID is pk

Players(pid, name, team) composite keys are (pid, team)

The query that I wrote is this, however I get an error because I'm using group by operator.
Select name, address, dob, sum(scores)
From Emp e
JOIN Emp_Players ep ON e.empID = ep.eid
JOIN Plays p ON p.pid = ep.pid
Group by p.pid;

Is there an alternate way that can work? Also I'm not sure if I'm doing the JOIN right? I am not able to use the where clause as where name = 'Jack'. It tells me

'no column named Jack'

I would really appreciate any help. Thanks.


texts: How to transfer data from Sybase to SQL Server database?

texts:
url reference How to import Sybase .db file into SQL Server database?

Please tell me how to transfer data from sybase to sql server database.


texts: MySQL date_format German date value not change to date type


texts: SQL Server's `mssql-conf setup`, error Aborted sudo

texts:
When I run sudo /opt/mssql/bin/mssql-conf setup to configure SQL Server 2017 I get the following error,

/opt/mssql/lib/mssql-conf/invokesqlservr.sh: line 15: 12710 Aborted                 sudo -EH -u mssql /bin/bash -c "$CMDLINE"


texts: Do I trace an Active Directory group based database login the same way as an AD user based login on SQL Server?

texts:
We are currently undergoing an PwC audit on our database logins. Some of our user accounts have sysadmin privs, which of course is a major security breach. I have successfully done traces on individual user accounts, but my question is how do I trace a database login mapped to an AD Group? The said group has over two dozen users. I don't really care what access each individual in the group requires, but I do care about what the group needs as a whole, so I can give appropriate access before taking the sysadmin priv off it.

What I do for individual users right now is to select the appropriate events in the SQL Server Profiler and filtered the LoginName column with the name of the user surrounded by a pair of %. Can the same be done for AD group based logins? I am tracing the said group right now, but there doesn't seem to be any activity going on (could be that no one from the group is currently using the database). I just want to confirm that this is the right way to do it for this type of login, or am I missing something?


texts: REPLICATE() return type for VARBINARY input

texts:
REPLICATE ( string_expression ,integer_expression )

As documentation says, it

  returns the same type as string_expression.

I see that return type is always VARCHAR.

declare @vb varbinary(10) = 0x01
declare @res sql_variant = REPLICATE(@vb, 2)

SELECT sql_variant_property(@res, 'BaseType')

  varchar

Is the documentation wrong?

I raised this as a documentation issue.


texts: How to log within a stored procedure that is encapsulated within a transaction

texts:
I have a stored procedure, let us call it sp_A which calls other stored procedures, let us call them sp_B and sp_C. For the sake of consistency an XA TRANSATCION is started within sp_A. What I actually want to do is to log if the execution of sp_B or sp_C fails and if so, which were the arguments passed to the failing stored procedure.
But as sp_B and sp_C do some inserts in some tables, I need to do a XA ROLLBACK if sp_B or sp_C fails.

So my question is: How can I log into a table while I am within a transaction
(or how to execute an insert statement outside of the transaction scope)?

To give you a little example of what I want to do (I've simplyfied the code and renamed most of the parameters/variables used to focus on the problem, I would never dream of using parameter names like a, b or c ;-) ):

The stored procedure sp_A looks like this:

CREATE PROCEDURE `sp_A`
(
  IN a INT,
  IN b VARCHAR(64),
  OUT c INT
)
sp_label:BEGIN
  DECLARE l_errorMessage TEXT;
  DECLARE l_spSuccess INT;
  DECLARE l_errorOccured INT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
    SET l_errorOccured = 1;
  END;

  XA START 'sp_A';

  CALL sp_B(a, b, l_spSuccess);

  IF l_errorOccured OR (l_spSuccessResult < 0) THEN
    XA END 'sp_A';
    XA ROLLBACK 'sp_A';
    SET c = -1;
    SET l_errorMessage = CONCAT('CALL sp_B(', a, ', ', b, ', l_spSuccess);');
    CALL sp_Log(l_errorMessage);
    LEAVE sp_label;
  END IF;

  CALL sp_C(a, b, l_spSuccess);

  IF l_errorOccured OR (l_spSuccessResult < 0) THEN
    XA END 'sp_A';
    XA ROLLBACK 'sp_A';
    SET c = -2;
    SET l_errorMessage = CONCAT('CALL sp_C(', a, ', ', b, ', l_spSuccess);');
    CALL sp_Log(l_errorMessage);
    LEAVE sp_label;
  END IF;

  XA END 'sp_A';
  XA COMMIT 'sp_A';
  SET c = 1;
END;

The stored procedure sp_B looks like this (sp_C looks similar):

CREATE PROCEDURE `sp_B`
(
  IN a INT,
  IN b VARCHAR(64),
  OUT c INT
)
sp_label:BEGIN
  DECLARE l_errorMessage TEXT;
  DECLARE l_spSuccess INT;
  DECLARE l_errorOccured INT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
    SET l_errorOccured = 1;
  END;

  INSERT INTO `someTable` (`a`, `b`) VALUES (a, b);

  IF l_errorOccured THEN
    SET c = -1;
    SET l_errorMessage = CONCAT('INSERT INTO `someTable` (`a`, `b`) VALUES (', a, ', ', b, ');');
    CALL sp_Log(l_errorMessage);
    LEAVE sp_label;
  END IF;

  SET c = 1;
END;

sp_Log just inserts data into a table which I want to use as my logging table.

So actually the logging within sp_A works just fine (because the call to sp_Log is outside a transaction) but the logging within sp_B or sp_C won't work because by rolling back the transaction started in sp_A, the inserts done by the call of sp_Log within sp_B or sp_C are reverted of course.

(I have simplified the example, in sp_B and sp_C. There is more going on than just one simple insert statement and just to have more fun, there are transactions started and commited/rolled back within sp_B and sp_C as well.)

I would appreciate any hints you could give me.
Thanks!


texts: Get all dates from an interval that are missing from other intervals

texts:
I am working with a database schema shown below and I am struggling with writing a query on that schema.

A hotel has a contract with a period of validity. Also it has many seasons, and each season has many periods. The task is to return all dates from the contract validity period that are not available in the seasonal periods.

This is my schema:

Ideally, I would like to get all possible intervals that do not cover the periods of the hotel. But if it is not possible then at least a list of dates.

Example (all dates are in the DD.MM.YYYY format):

Contract period: 1.01.2018 – 31.12.2018
Hotel periods: 1.01.2018 – 30.06.2018; 1.08.2018 – 31.12.2018

In this case I lose one month 1.07.2018 – 31.07.2018.
And I want to get this period, or several periods if there happen to be several. So, something like this:

BeginDate  EndDate
---------  ----------
1.07.2018  31.07.2018

If it's not possible to get a list of periods, then I want to get all missing dates, and group them in periods on the server side.


texts: How to get SEEK accessing converted ID via view

texts:
Assume I have a table:

-- just for test purposes
CREATE TABLE SomeTable (
    ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK__SomeTable__ID PRIMARY KEY CLUSTERED
    ,SomeColumn1 NVARCHAR(50) NULL
    ,SomeColumn2 DATETIME NULL
    );

-- populate table with some rows
INSERT INTO SomeTable DEFAULT VALUES;
GO 1000

Because a third-party application there is a view converting ID column of a table from INT to NVARCHAR (assume it's a must):

CREATE VIEW ThirdPartyView AS
SELECT
    ID = CAST(ID as NVARCHAR(10))
    ,C1 = SomeColumn1
    ,C2 = SomeColumn2
FROM SomeTable;

Then when I access one row by ID, I get an INDEX SCAN:

SELECT *
FROM ThirdPartyView
WHERE ID = N'1'

I understand why.

What can I do to get INDEX SEEK outside of a query?

EDITORIAL / Circumstances:

base table (SomeTable) definition could NOT be changed (cannot add columns)
view has to have the same columns (cannot add columns)
define any indexes is possible
I can make the view an indexed view but prefer to avoid that option
The other side expects the data type of the ID column is NVARCHAR. I have to meet this conditions, unfortunately. Or I've been told to meet them. Probably their application would fail if it's not.


texts: What are single read and single write speeds like with the Teradata REST API? Is it fast enough to be used as an operational database for a typical responsive web application? What is the difference in fetch time between REST, ODBC, JDBC, and .NET?


texts: Scenario/Method (idea) for transfering database from one server to another

texts:
The scenario is next.
Every day on linux mysql server script copy from samba share wich is "/var/lib/mysql/db2223" to another backup server over network lets say windows server. Database has lets say 200 GB of data.

One day linux mysql server get down and cannot be fixed.

Method: Take another machine install on it linux server configure and then create again in phpmyadmin only name of db "db2223" and copy all from backup to new "var/lib/mysql/db2223" directory.

Is this method good or bad ?


texts: MySQL: Do you need to use backticks on column names when using the dot to refer to a table name (table_name.`column`)

texts:
When using backticks with MySQL so column names don't collide with reserved words:

SELECT `count`, `type`
FROM    table1

Do you still need to use backticks to prevent it colliding with reserved words when you're doing this:

SELECT  table1.count, table1.type
FROM    table1

Should you still add the backticks like this:

SELECT  table1.`count`, table1.`type`
FROM    table1

Any help appreciated.


texts: ORA-01555: snapshot too old: rollback segment number with name "" too small

texts:
This error appears when I am trying to move LOB segment from one tablespace to another one.

Also during exporting the same error appears.

What can be a problem?


texts: mssql-server crashing: Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 8

texts:
We have an Azure Linux VM with a local MSSQL server. I recently had to reboot the VM, and since then I haven't been able to start the MSSQL server. The error logs would say Recovery of database 'MyDB' (6) is 1% complete ..., then hang the VM to the point that I could not access it over SSH, and eventually the mssql-server service would just stop. The last time this happened there was finally an error message in the logs:

2017-07-30 10:20:35.19 spid24s      Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 8
2017-07-30 10:20:38.85 spid24s
Process/System Counts                         Value
---------------------------------------- ----------
Available Physical Memory                5837422592
Available Virtual Memory                 140733193256960
Available Paging File                    5837422592
Working Set                              4294967296
Percent of Committed Memory in WS                82
Page Faults                                       0
System physical memory high                       1
System physical memory low                        0
Process physical memory low                       0
Process virtual memory low                        1
2017-07-30 10:21:22.83 spid24s
Memory Manager                                   KB
---------------------------------------- ----------
VM Reserved                                 5883372
VM Committed                                5091232
Locked Pages Allocated                            0
Large Pages Allocated                             0
Emergency Memory                               1024
Emergency Memory In Use                           8
Target Committed                            5700608
Current Committed                           5091232
Pages Allocated                             2923720
Pages Reserved                                    0
Pages Free                                  2024712
Pages In Use                                 217880
Page Alloc Potential                        5197704
NUMA Growth Phase                                 0
Last OOM Factor                                   9
Last OS Error                                     0
2017-07-30 10:21:23.78 Server      Long Sync IO: Scheduler 0 had 1 Sync IOs in nonpreemptive mode longer than 1000 ms
2017-07-30 10:22:40.64 spid24s
Memory node Id = 0                               KB
---------------------------------------- ----------
VM Reserved                                 5883244
VM Committed                                5091196
Locked Pages Allocated                            0
Pages Allocated                             2923752
Pages Free                                  2024680
Target Committed                            5700568
Current Committed                           5091200
Foreign Committed                                 0
Away Committed                                    0
Taken Away Committed                              0
2017-07-30 10:23:21.93 spid24s
Memory node Id = 64                              KB
---------------------------------------- ----------
VM Reserved                                       0
VM Committed                                     36
Locked Pages Allocated                            0

The VM is running Ubuntu with 8GB ram.
Do I need more memory, or are there other solutions?


texts:
what is wrong with the following query  ??

declare  @dbName varchar (50)
declare  @fldName varchar (50)

set  @dbName ='student';
set  @fldName='stID';

declare  @gID smallint
set  @gID =4;

DEClARE @sqlstr nvarchar(max);

SET @sqlstr = 'SELECT  MAX([' + @fldName +'])
  FROM  [dbo].['+ @dbName +']
  WHERE [dbo].['+ @dbName +'].[gID ] = @gID';

  EXEC (@sqlstr)

Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable


texts: Oracle 12.2 shows high parse per execution ratio for unknown lob$ query

texts:
In an AWR report I see the following (most liekly recursive) query which happens very often and is parsed each time:

select obj#, intcol#, ts#, file#, block#, property from lob$ where lobj#=:1

The AWR entry for SQL ordered by parse calls:

Parse Calls Executions  % Total Parses SQL Id        SQL Module SQL Text
411,384     411,414     39.62          6y55dxn24t86q select obj#, intcol#, ts#, fil...

Any idea which component is generating that and why it is not a prepared statement? (since our app sets "module" everywhere we know its not directly executed from us).

We do use LOBs heavily, so this can be related to reorganisation or maybe data guard?


texts:
When trying to import a database from a .mysql file, I get an error:
ERROR 1115 (42000) at line 24: Unknown character set: 'ascii'

The part the error occurs at is from columns using ASCII:
`column` varchar(64) CHARACTER SET ascii NOT NULL

I checked in /usr/share/mariadb/charsets/, and ascii.xml is there. ASCII is listed in the Index.xml as well. However, when looking at the database it's not:
MariaDB [db]> show character set;
+---------+------------------+---------------------+--------+
| Charset | Description      | Default collation   | Maxlen |
+---------+------------------+---------------------+--------+
| big5    |                  | big5_chinese_ci     |      2 |
| latin1  |                  | latin1_swedish_ci   |      1 |
| latin2  |                  | latin2_general_ci   |      1 |
| ujis    |                  | ujis_japanese_ci    |      3 |
| sjis    |                  | sjis_japanese_ci    |      2 |
| tis620  |                  | tis620_thai_ci      |      1 |
| euckr   |                  | euckr_korean_ci     |      2 |
| gb2312  |                  | gb2312_chinese_ci   |      2 |
| cp1250  |                  | cp1250_general_ci   |      1 |
| gbk     |                  | gbk_chinese_ci      |      2 |
| utf8    |                  | utf8_general_ci     |      3 |
| ucs2    |                  | ucs2_general_ci     |      2 |
| utf8mb4 | UTF-8 Unicode    | utf8mb4_general_ci  |      4 |
| utf16   | UTF-16 Unicode   | utf16_general_ci    |      4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci  |      4 |
| utf32   | UTF-32 Unicode   | utf32_general_ci    |      4 |
| binary  |                  | binary              |      1 |
| cp932   |                  | cp932_japanese_ci   |      2 |
| eucjpms |                  | eucjpms_japanese_ci |      3 |
+---------+------------------+---------------------+--------+
19 rows in set (0.000 sec)

At this point, I don't know what's wrong or how to get the ASCII character set detected and working.
I am using wodby/mariadb with tag MARIADB_TAG=10.5-3.9.5.


texts: How to resolve error "object reference not set to an instance of an object" in SQL Server 2014

texts:
I installed SQL Server 2014 Management Studio on my system few months age. It was working fine until yesterday. But today, when I started SSMS and clicked on "New Query" option, I got an error

  object reference not set to an instance of an object

Please provide a solution to resolve this error.


texts: Most efficient ordering post database design

texts:
I have posts table that is has post_order column. I store order of each post in it. when I change the order of a row from 25 to 15, I should update all the row from 15 to end. It's good for few rows, But in thousands rows it's worst.

Is there any better design for ordering posts, that is more efficient?


texts:
I have a road_condition table in Oracle 12c:

create table road_condition (
    cond_id number(5,0),
    road_id number(5,0),
    cond_date date,
    condition number(5,0)
);

insert into road_condition (cond_id,road_id,cond_date,condition)
values (1,100,to_date('01-NOV-84','DD-MON-RR'),18);
insert into road_condition (cond_id,road_id,cond_date,condition)
values (2,100,to_date('01-JAN-09','DD-MON-RR'),6);
insert into road_condition (cond_id,road_id,cond_date,condition)
values (3,100,to_date('19-JUN-12','DD-MON-RR'),4);
insert into road_condition (cond_id,road_id,cond_date,condition)
values (4,100,to_date('29-APR-15','DD-MON-RR'),4);
insert into road_condition (cond_id,road_id,cond_date,condition)
values (5,200,to_date('29-APR-92','DD-MON-RR'),20);
insert into road_condition (cond_id,road_id,cond_date,condition)
values (6,200,to_date('05-APR-17','DD-MON-RR'),3);
commit;

Resulting table data:

COND_ID ROAD_ID   COND_DAT CONDITION
------- -------   -------- ---------
      1     100   84-11-01        18
      2     100   09-01-01         6
      3     100   12-06-19         4
      4     100   15-04-29         4
      5     200   92-04-29        20
      6     200   17-04-05         3

Here is a graph of road #100:

Condition in the table is categorized like this:

20 to 15 is in Good condition and does not require any treatment
14 to 11 requires an Overlay treatment
10 to 8 requires a Full Depth Removal (FDR) treatment
7 to 0 requires Reconstruction treatment

Condition is from 0 to 20, 20 being a perfect road.

When roads are inspected, the condition is stored as a whole number(integer), never as a decimal.

Question:

Rather than show the line in the graph as a single colour (blue), I would like to stripe the line as coloured categories.

I've mocked up road #100 in Excel:

Idea:

To stripe/categorize the data as described, I think I need to create dummy points (rows) at the beginning and end of each range along the line.

Perhaps the simplest way to do this would be to create points (rows) at all intervals of condition, instead of just at the beginning and end of each range.

The graphing software that I'm using creates a new line in the graph for each Y-column/series (similar to the way Excel does).

Therefore, I believe I need each category to be isolated in a separate column, so it can be drawn as a separate line (with a separate colour).

Additionally, there needs to be overlap between each category, so that the graphing software does not display gaps between lines.

How can I create the dummy rows (interpolate the dates)?

Update:

I have a related question here: Date as x-axis: How to multiply and divide dates?


texts: Command line tool / script to backup a remote SQL Server database to local disk

texts:
I've been researching and googling for 10 hours and couldn't find anything, so I decided to ask here. I'm looking for a command line tool or a script (running under windows) that can backup a remote SQL Server to a local file (.sql, SQL Server backup, even CSV, all formats are acceptable).

P.S. I can't use remote SMB share for backing up DB. Also no GUI, just command line or scripts

P.P.S. The DB is in remote location and I want to backup the data in Database to any format possible in my local machine (Windows server, no SMB share connection in between available/possible)


texts: What's the most appropriate NLS_SORT parameter to cover all/most European languages?

texts:
I'm developing an Oracle 11g-based application which is likely to be used by clients in many different countries (mostly European). I want the program to change its NLS_SORT so that it's case insensitive on startup using the ALTER SESSION SET NLS_SORT='???'; command.

I'm going to have a config file which contains the NLS_SORT setting the user wants to use so it is editable, but I want a default which will cover European languages. Which is the best one to choose?


texts: Get monthly sums from several tables with same (date,amount) columns

texts:
I have records stored in "date,amount" columns from several tables: table1, table2, table3, table4. Amongst tables, field names are same.
Table 1:

date
amount

2018-02-17
10.00

2018-06-25
18.00

2018-07-09
10.00

2018-07-21
5.00

For each month available in any of the table, I want to sum the amounts coming from all four tables, keeping one column per table:

month
amount_table1
amount_table2
amount_table3
amount_table4

2018-02
10.00
null
null
12.00

2018-03
null
null
3.00
8.00

2018-04
null
16.00
3.00
null

2018-05
null
null
3.00
12.00

2018-06
18.00
1.00
null
null

2018-07
15.00
2.00
7.00
1.00

The following code is highly inspired from Roman Pekar's answer to the question "Multiple FULL OUTER JOIN on multiple tables", that uses an "anchor" table. I appreciate this solution as it is easy to read.
I attempted extending it to the case where records are stored for distinct dates and were not already summed by month. It is almost working, but for some reason the monthly sums in the output array are wrong.
SELECT
    X.month,
    SUM(A.montant) AS 'amount_table1',
    SUM(B.montant) AS 'amount_table2',
    SUM(C.montant) AS 'amount_table3',
    SUM(D.montant) AS 'amount_table4'
FROM (
    SELECT DISTINCT DATE_FORMAT(date,'%Y-%m') AS month FROM `table1`
    UNION
    SELECT DISTINCT DATE_FORMAT(date,'%Y-%m') AS month FROM `table2`
    UNION
    SELECT DISTINCT DATE_FORMAT(date,'%Y-%m') AS month FROM `table3`
    UNION
    SELECT DISTINCT DATE_FORMAT(date,'%Y-%m') AS month FROM `table4`
) AS X
    LEFT OUTER JOIN table1 AS A ON DATE_FORMAT(A.date_comptabilisation,'%Y-%m') = X.month
    LEFT OUTER JOIN table2 AS B ON DATE_FORMAT(B.date_comptabilisation,'%Y-%m') = X.month
    LEFT OUTER JOIN table3 AS C ON DATE_FORMAT(C.date_comptabilisation,'%Y-%m') = X.month
    LEFT OUTER JOIN table4 AS D ON DATE_FORMAT(D.date_comptabilisation,'%Y-%m') = X.month
WHERE 1
GROUP BY  X.month

I also attempted adding a stricted WHERE clause, with no more success.
WHERE
    DATE_FORMAT(A.date,'%Y-%m') = X.month
 OR DATE_FORMAT(B.date,'%Y-%m') = X.month
 OR DATE_FORMAT(C.date,'%Y-%m') = X.month
 OR DATE_FORMAT(D.date,'%Y-%m') = X.month

Can someone explain what's wrong with my code?
Thanks.


texts:
I love that SERIALs can wrap around with CYCLE.

However, I can't seem to figure out if that there are lingering values, will Postgres skip over them or throw an error.

In other words, I have a table that needs UNIQUE auto incremented values.  Many will be destroyed over time, but many will remain.  After the max value has been reached, does Postgres increment past it, or will it throw an error because the next incremented value already exists?


texts: Execute Stored Procedure with SET LOCK_TIMEOUT

texts:
Is there anyway to execute stored procedure with SET LOCK_TIMEOUT explicitly?

E.g. I have sproc called dbo.LoadData. It just takes data from table dbo.Abc and insert them in table dbo.Xyz.

Now I want to execute the sproc dbo.LoadData, but if the table dbo.Abc is locked for e.g. more than 10 seconds, stop the execution of the stored procedure.

I know the option SET LOCK_TIMEOUT can be implemented in the sproc dbo.LoadData itself, but I am wondering if there is any way how to call it explicitly (= not implementing the option in the stored procedure itself).


texts: Managed backup to Azure does not delete backup after retention period

texts:
I did set up SQL Server managed backup for our databases. I did define retention days to 4, but i just checked the azure storage account where the managed backups are and found that there is still all the files from the day i did set up managed backup (2015/12/1).

SQL i used on every user database:

    Use msdb;
    GO
    EXEC smart_admin.sp_set_db_backup
            @database_name='TestDB'
            ,@retention_days=4
            ,@credential_name='MyCredential'
            ,@encryption_algorithm ='AES_128'
            ,@encryptor_type= 'Certificate'
            ,@encryptor_name='MyBackupCert'
            ,@enable_backup=1;

We are using SQL Server 2014 Enterprise with Azure VM.

Set up tutorials and information about the managed backups: MSDN

Is SQL server managed backup to Azure supposed to automatically delete backups after given retention days have passed or do i need to implement custom job for that?


texts: What is the difference between optimize table and analyze table in mysql? I have read the online docs, but not sure what the difference is.


texts: Unable to call SQL Agent Job on another server

texts:
I am trying to start a SQL Agent job on another SQL Server and keep getting the error message that the job is not found.
EMSSQL3 is the server from which I'm trying to call the job which is located on server TSTSQL7. TSTSQL7 is set up as a linked server on EMSSQL3. The job exists on TSTSQL7 and I've given execute permissions on msdb.dbo.sp_start_job on TSTSQL7 to the domain user that is running the job on EMSSQL3. I have also given permissions to the LinkedServer user to execute msdb.dbo.sp_start_job. The command I have in the job step on EMSSQL3 is EXEC [TSTSQL7].[msdb].[dbo].sp_start_job @job_id=N'B384797B-C9B1-48DC-8D35-CF51C14B401F'. I have the database in the job step on EMSSQL3 set to msdb.
I have also tried the same command start job using the @job_name=N'Restore Stage2' and still get this message:
Message
Executed as user: EMS\sql. The specified @job_id ('B384797B-C9B1-48DC-8D35-CF51C14B401F') does not exist. [SQLSTATE 42000] (Error 14262).  The step failed.
I can see the job on TSTSQL7 and I can run the job manually from TSTSQL7, but I need to trigger the job on TSTSQL7 to start when another job step finishes on EMSSQL3. Any ideas what I'm missing here?


texts: `SELECT` on `information_schema`.`tables` returns less rows if I `SELECT` the `CREATE_TIME`

texts:
Context

We have a MySQL 5.6.28 server with a lot of schemas on it. After a crash, we have some schema where a table x exists, and other schemas where it's an orphan table.

On a non-corrupted schema, doing a SELECT * FROM x returns the rows in x. Dropping the table works.

On a corrupted schema, doing a SELECT * FROM x or doing a DROP TABLE or doing an ALTER TABLE returns Unknown table x. Doing a CREATE TABLE x (...) Engine=InnoDB return Error 1813 Tablespace for table 'my_corrupted_schema.x' exists. Please DISCARD the tablespace before IMPORT. Doing a CREATE TABLE x (...) Engine=HEAP|MyISAM or even creating a view CREATE VIEW x AS SELECT 1 works. Doing an ALTER TABLE x ENGINE=InnoDB on the HEAP or MyISAM table fails with Error 1050 Table './my_corrupted_schema/x' already exists
Looking at information_schema.INNODB_SYS_TABLES or information_schema.INNODB_SYS_TABLESPACES or information_schema.INNODB_SYS_FOREIGN_COLUMNS  we see that the table x is there. Dropping the Heap|MyISAM table or the view works fine.

Now comes the funny part:

We looked at the information_schema.TABLES. We do

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CREATE_TIME, ENGINE
FROM information_schema.`tables` AS t
WHERE t.table_name = 'x';

And we see only x tables in non-corrupted schemas (11 rows, matching the 11 schemas where a SELECT * FROM x works). Looks very like orphans table.... But then, we do:

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, /*CREATE_TIME, */ENGINE
FROM information_schema.`tables` AS t
WHERE t.table_name = 'x';

And we see x tables in all schemas (905 rows), including corrupted ones...?!

How is that possible?! How can we fix that without having to drop and recreate every schema? It seems that CREATE_TIME is NULLABLE in information_schema.TABLES (which makes little sense IMO but anyway) but how can including that column in our select change the returned rows?! How can we fix these tables x in the corrupted schemas?

Edit: After testings, including any of the commented out columns in the SELECT below will return only non-corrupted tables, while leaving all commented out columns will return all schemas, including those where the table is corrupted... What's the common point between all these columns?

SELECT
    TABLE_CATALOG, --  OK
    TABLE_SCHEMA, -- OK
    TABLE_NAME, -- OK
    TABLE_TYPE, -- OK
    ENGINE, -- OK
    VERSION, -- OK
--  ROW_FORMAT, -- BAD
--  TABLE_ROWS, -- BAD
--  AVG_ROW_LENGTH, -- BAD
--  DATA_LENGTH, -- BAD
--  MAX_DATA_LENGTH, -- BAD
--  INDEX_LENGTH, -- BAD
--  DATA_FREE, -- BAD
--  AUTO_INCREMENT, -- BAD
--  CREATE_TIME, -- BAD
--  UPDATE_TIME, -- BAD
--  CHECK_TIME, -- BAD
    TABLE_COLLATION, -- OK
--  CHECKSUM, -- BAD
    CREATE_OPTIONS, -- OK
    TABLE_COMMENT -- OK
FROM information_schema.`tables` AS t
WHERE t.TABLE_NAME = 'x'


texts:
I'm using postgres 9.3 on Ubuntu 14.04 and I'm currently setting up a WAL backup. These are my changes to the postgresql.conf:
[as described here: http://www.postgresql.org/docs/9.3/static/continuous-archiving.html ]

# - Settings -
wal_level = archive                     # minimal, archive, or hot_standby
                                        # (change requires restart)
# - Archiving -
archive_mode = on               # allows archiving to be done
archive_command = 'cp %p /home/userName/Documents/WALS'

I did a 'sudo service postgresql restart' after changing the config and there are wal files generated:

root@control:/var/lib/postgresql/9.3/main/pg_xlog# ls -hlrt
total 49M
-rw------- 1 postgres postgres  16M Dez  2 14:24 000000010000000000000002
-rw------- 1 postgres postgres  16M Dez  2 14:24 000000010000000000000003
drwx------ 2 postgres postgres 4,0K Dez  2 14:24 archive_status
-rw------- 1 postgres postgres  16M Dez  2 14:25 000000010000000000000004

root@control:/var/lib/postgresql/9.3/main/pg_xlog# ls archive_status/
000000010000000000000002.ready  000000010000000000000003.ready

I assume that the archive_status means that WAL segments 2 and 3 are ready to be archived. But I don't find anything in the Documents/WALS (which exists).

Could someone give me a hint?


texts: How to find out the segment behind a corrupted block when the database cannot be opened?

texts:
Short Version:

alter database open fails with ORA-00600.
alert.log and dbv both point out that one data block (type: 6) is corrupted.
I cannot select from dba_extents as that requires an opened database
I cannot use RMON with the backup validate check logical command as the database is in NOARCHIVELOG mode.
I cannot set the database to ARCHIVELOG mode as an instance recovery is required
I cannot recover the instance as that would require me to fully startup and normally shutdown the database.

=> Is there any way I can find out what exactly is corrupted without opening the database?

Detailed Version:

One of our Development databases (non-productive) recently doesn't start up any longer and provides the following errors when trying to connect with a regular user:

  ORA-01034: ORACLE not available
  ORA-27101: Shared memory realm does not exist

I can connect as sys as sysdba, and I can successfully perform the following commands:

shutdown immediate;
startup mount;

but when I try doing

alter database open;

ORA-00600 along with various internal error codes is shown.

Using the alert.log and the dbv command line tool, I found out a data block is corrupted:

  DBVERIFY - Verification starting : FILE = D:\ORADATA\ORCL\SYSTEM01.DBF
  Page 62507 is influx - most likely media corrupt
  Corrupt block relative dba: 0x0040f42b (file 1, block 62507)
  Fractured block found during dbv:
  Data in bad block:
   type: 6 format: 2 rdba: 0x0040f42b
   last change scn: 0x0000.0eb6f2bc seq: 0x1 flg: 0x06
   spare1: 0x0 spare2: 0x0 spare3: 0x0
   consistency value in tail: 0xfaae0601
   check value in block header: 0xf67f
   computed block checksum: 0x376f

So type: 6 apparently means it's a regular data block which usually is a table, an index or similar. In order to find out which database object was compromised, I've first tried using queries like the following:

select owner, segment_type, segment_name
from dba_extents
where file_id = 1 and 62507 between block_id and block_id+blocks-1;

This however fails with the same ORA-01034 as any other query. This results in a recursive problem, as I would need to open the database in order to find out why I can't open the database.

After googling for a while, I also found I could query the v$database_block_corruption view even if the database is not opened. I do however need to populate that view with data by running e.g. RMAN:

  RMAN> backup validate check logical database;

which unfortunately results in the new errors RMAN-03009 and ORA-19602 which basically mean that a backup (even a validation check) is not possible in NOARCHIVELOG mode.

So I tried setting archivelog mode by executing

alter database archivelog;

on the mounted database.

This however produced yet another error:

  ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

After googling for some more time, I found out that "instance recovery" can be achieved by completely starting up the database and then shutting it down regularly using shutdown or shutdown immediate.

Which leads me back to my initial problem: I cannot fully start up (i.e. open) the database.

Is there any way I can find out which table (or index, ...) is broken, without having to open the database?


texts: Is it possible to have SQL server look for entities under other schemas than DBO when the schema name isn't specified in the query?

texts:
Not sure if this is a thing, and I understand it's better practice to write your queries using the two-part naming convention, but it would be cool if SQL Server had a way to setup a schema priority list.

That way when you execute a query that references entities without their correlating schemas, the server is smart enough to search for those entities in each schema on the priority list, in order, until it finds the entity.

I would find this mostly helpful in a lot of ad-hoc queries I write that won't ever be formalized.


texts:
Mysql is showing 5 root users:

| root   | %         |
| root   | 127.0.0.1 |
| root   | ::1       |
| root   | localhost |
| root   | mysrverhostname |

What are these 5 for? Are there some I can delete?


texts: Trying to allocate 3G for buffer pool in InnoDB fails

texts:
I'm trying to boost my InnoDB driver on my Windows 7 PC since each page request takes just about 1.7 seconds to load versus 0.002 seconds on my Ubuntu 12.10 workstation.

The setting I'm playing around with is the well-known innodb_buffer_pool_size line, which should increase the performance a lot, and it did on my Ubuntu workstation - but my Windows 7 workstation can only seem to allocate 1G of RAM, my system currently have 16GB.

If I try to exceed 4GB I get [ERROR] innobase_buffer_pool_size can't be over 4GB on 32-bit systems, so I try to allocate 3G but now my server wont start, same goes for 2G. 1GB works but did only contribute to about 0.1s worth of speed increase - which isn't much.

Is there something more I have to account for on Windows to get better performance, and why can I not allocate more than 1G of RAM? (I get no errors when I try to allocate more than 1GB, the server just won't start)


texts: SQL Replication: Which tables/views should be a part of replication?

texts:
We need a reporting Database & the Production Database.

The reporting team is unsure of which tables in the database are used in the reporting tools and hence wants us to replicate the complete database to the reporting.

I would like to understand what will be the pros and cons of replicating the complete database?

What I think:

Pros: The reporting databases will serve as a disaster recovery option too.

Cons: More disk consumption on Reporting Server, higher resource utilization and more difficult maintenance.

Anything more?


texts: Clustered Sequential GUID Primary Key vs Non-Clustered GUID and Clustered Sequential ID Primary Keys

texts:
I'm trying to figure out what the optimal solution is in regards to size and performance when using GUIDs as a primary key. The main reason for wanting GUIDs is that it's more appropriate in our micro-service architecture. There seems to be a lot of debate on what's actually best.

The two main solutions appear to be:

Have the primary key as a sequential GUID (generated by the
application, rather than SQL Servers built in NEWSEQUENTIALID() as it isn't truly sequential)
with a clustered index.
Have two primary keys, a randomly generated GUID with a
 non-clustered index and a basic int IDENTITY column with a
 clustered index -- the approach is described best in this stackoverflow response here

I'm not sure which solution is best. The first method seems to be the most popular and widely used from what I've read. The second seems to have this benefit from that linked stackoverflow response:

  Then there's another issue to consider: the clustering key on a table will be added to each and every entry on each and every non-clustered index on your table as well - thus you really want to make sure it's as small as possible.

Any advice would be appreciated.


texts: Table with too many nullable columns - is there a better design?

texts:
I have a user table with around 30 columns. Most of these columns are almost always null unless user decides to share additional info about themselves. For example, weight, height, religion, is_smoking, is_drinking, star_sign, school, job, etc.

In my app, I have on option to filter users based on these values. I might be adding one or two columns like these in future.

I was just wondering if there is a better design approach for situations like these. For some reason, it doesn't feel right to have all these nullable columns in the same table with login credentials.

PS: I am using PostgreSQL 11.4 as the database management system.


texts: Disadvantages of reorganizing all tables nightly

texts:
I consider scheduling a SQL Server job that reorganizes all tables nightly. We don't have enterprise, so we cannot rebuild online (this means we need to reorganize). We also don't want to invest too much time in fragmentation and space maintenance so we are seeking a solution that can be implemented quickly and does not require further time investments in the future.

Testing on a restored production backup showed that reorganize is almost as good as rebuild for reducing fragmentation and regaining space.

Is this a reasonable idea? Does this scheme create problems for availability or maintainability? Is this a reasonable long-term solution?


texts: Is there any limit for IN results in SQL Server?

texts:
Is there any limit for the content that the IN filter can handle? For example:

SELECT Name
FROM People
WHERE Job IN (All the values goes here)

Microsoft docs for IN says:

  "Explicitly including an extremely large number of values (many
  thousands of values separated by commas) within the parentheses, in an
  IN clause can consume resources and return errors 8623 or 8632. To
  work around this problem, store the items in the IN list in a table,
  and use a SELECT subquery within an IN clause."

but is there any exact or approximate number for

  many thousands of values


texts:
I need to make a specific query because I can't seem to find any working solution
I am creating an app like this one
https://anonymous-message-board.freecodecamp.rocks[][1]
basically, the application consists of many boards and each board have a number of threads and each thread have a number of replies
what I need to do is when some user heads to a specific board (makes a get request) I need to send him the threads of that board and the replies of each thread
the replies should be an array within the thread knowing that the threads are sorted by the created_on and limited a to 10 threads also the replies are sorted with created_on and limited to 3 results
here is :
the database tables
CREATE TABLE threads (
    _id SERIAL NOT NULL PRIMARY KEY,
    board_id INT NOT NULL REFERENCES board(id),
    text VARCHAR(255) NOT NULL,
    delete_password VARCHAR(255) NOT NULL,
    reported  BOOLEAN NOT NULL,
    created_on DATE NOT NULL DEFAULT CURRENT_DATE,
    bumped_on DATE  DEFAULT CURRENT_DATE
);

CREATE TABLE board (
    _id SERIAL NOT NULL PRIMARY KEY,
    name VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE replies (
   _id SERIAL PRIMARY KEY NOT NULL,
   thread_id INT NOT NULL REFERENCES threads(id),
   text VARCHAR(255) NOT NULL,
   delete_password VARCHAR(255) NOT NULL,
   reported  BOOLEAN NOT NULL,
   created_on DATE NOT NULL DEFAULT CURRENT_DATE
);

however, I tried doing a lot of queries without getting the desired result
my main obstacle was that I need to select the replies for each thread
I tried this :
but as you know here I can't select many columns from a subquery
  SELECT  id as _id, txt as text, delete_password, reported, created_on, bumped_on ,
 (
    select * from replies
    where thread_id = thd.id
    order by created_on DESC
    limit 3
 )  subquery
 from threads thd where board_id = 1
 order by created_on DESC
 limit 10;

tried this also but didn't get the replies array as desired:
select id as _id, txt as text, delete_password, reported, created_on, bumped_on, array_agg(replies) as replies
(select count(*) replycount from replies group by thread_id)


texts:
I have a simple table called events

It has a date, start time, end time and status

I have multiple events for the same date, start and end.

To know if someone can make an appointment, I have to check if for that date and start, end I have a line that the status is false (not occupied).

The table definition with some example code:

CREATE SEQUENCE events_id_seq;

CREATE TABLE events(
  id int4 NOT NULL DEFAULT nextval('events_id_seq'::regclass),
  event_date date NULL,
  status bool NULL DEFAULT false,
  event_start time NULL,
  event_end   time NULL
) ;

INSERT INTO events(event_date,status,event_start,event_end)
VALUES
('2016-10-10', TRUE, '08:00:00','08:30:00'),
('2016-10-10', TRUE, '08:00:00','08:30:00'),
('2016-10-10', FALSE, '08:00:00','08:30:00'),
('2016-10-10', TRUE, '08:00:00','08:30:00'),
('2016-10-10', TRUE, '08:30:00','09:00:00'),
('2016-10-10', FALSE, '08:30:00','09:00:00'),
('2016-10-10', FALSE, '08:30:00','09:00:00'),
('2016-10-10', FALSE, '08:30:00','09:00:00');

select distinct event_date, event_start, event_end, (
    select count(*) from events e1
    where
        e1.status = false and
        e1.event_date = e.event_date and
        e1.event_start = e.event_start and
        e1.event_end = e.event_end
) as free_slots from
events e
order by event_date, event_start;

http://sqlfiddle.com/#!15/698b7

It works, but since I'm not a SQL expert, I am just seeking advice on other forms to solve this, just to learn a little more.


texts: How can I enforce a one-to-zero-or-one relationship with multiple types?

texts:
I am creating a database structure to represent a football league, but there is a certain part that I do not know how to solve, that is:

A member and he can be a referee, a management or a player (but only one of these types).

I include the following drawing to exemplify the situation:

The attributes for each type are:

member: id (primary key), name, surname, etc.
referee: id (primary key), practice, league (foreign key to league)
management: id (primary key), practice, club_id (foreign key to club)
player: id (primary key), number, club_id (foreign key to club)

Question

With this scheme, a member can be a referee and a player at the same time, but I want to enforce a rule that dictates that a member can be only a refeere or a player, so how can I achieve this goal?


texts: MYSQL 8 writes on ibdata1 only when no queries

texts:
I'm facing a strange behavior with MySQL 8 running in docker: it has a constant 7MB/s write to ibdata1 when no queries are being made (monitored with netdata and filetop-bcc). As soon as I start inserting some data, the write drops, and resume as soon as I stop the inserting process. I've looked at several other threads about ibdata1, but none seems to give a reasonable explanation of why there's such behavior.

Here's a graph of disk usage. You can see at first and at last the constant disk write, and in the middle the small amount of time an application inserting/selecting data was running.

SHOW FULL PROCESSLIST lists no on-going transaction

SELECT * FROM information_schema.innodb_trx returns an empty result

My config:

[mysqld]
max_binlog_size = 1048576
innodb_sort_buffer_size = 20971520
sync_binlog = 0
join_buffer_size = 1G
innodb_buffer_pool_size = 22G
innodb_log_file_size = 3G
innodb_buffer_pool_instances = 23
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2

Does anyone have a clue about what could be going on ?
MySQL is giving me quite a high disk PSI (from 3 to 10%), even if my computer has a nvme SSD.

EDIT: Apparently it has to do with InnoDB Buffer Pool Flush Pages Requests, which is around 200 requests/s on idle, and drops to 0 as soon as the process connects to mysql. However, I don't understand what it means


texts:
I am trying to do a simple per month of year sum of values on a big (>10M) table.
However, it faces significant performance issues (7 sec with 4G innodb pool size, in local db with 1G innodb pool size it takes over a minute). The script seems fairly simple.

SELECT
  year(date_time),
  month(date_time),
  sum(value)
FROM measurements
WHERE
  source_id = 2
GROUP BY year(date_time), month(date_time)

I have tried using MySQL 5.7 and MariaDB 10.2. Using EXPLAIN in both cases provides:

SIMPLE  measurements    ref source_id,source_date   source_id   4   const   4418476 Using where; Using temporary; Using filesort

It seems I cannot avoid using temporary table. The table has 2 indexes: date_time and source_id, date_time.


texts: Get row with the biggest value from each group

texts:
I have this query:

SELECT  VIAGEM.IDVOOREGULAR COD_VOO_REGULAR,
        VIAGEM.DATAPARTIDA DATA,
        VOO.ORIGEM,
        VOO.DESTINO,
        (((EXTRACT (DAY FROM (DATACHEGADA-DATAPARTIDA))*24*60*60 +
           EXTRACT (HOUR FROM (DATACHEGADA-DATAPARTIDA))*60*60 +
           EXTRACT (MINUTE FROM (DATACHEGADA-DATAPARTIDA))*60)/60)-VOO.DURACAO) ATRASO
 FROM   VIAGEM,
        VOO_REGULAR,
        VOO
WHERE   VIAGEM.IDVOOREGULAR=VOO_REGULAR.ID AND
        VOO_REGULAR.IDVOO=VOO.ID;

and this result:

And I want to get just the biggest ATRASO from each group of COD_VOO_REGULAR.

How can I do it?


texts:
I have a catalog-like huge table at my client's infrastructure, that I pull into my db: no pk, and I wasn't able to find a way to create a pk from the existing columns - the table has no restrictions on duplicates etc. Also the row-number approach does not work, as the source is being overwritten with truncate daily, and it will create a wrong data-structure.

The only solution I came up with is a creation of all-field-pk, which would solve this particular issue, but it would introduce the unsolvable performance issue.

So the actual question is:
How to create a pk for a table with such data?

UPD: I really look for a solution, that generates the pk from the data, not row number etc.


texts:
I am trying to change the value of  defaults column from Y to N if a new value is inserted. The default column is now only restricted to have only 1 Y based on the different membershipType.

The Table:

 ID  titlePromo   membershipType         defaults
 --  ----------   --------------         ------
  1    Promo 1     Membership Promotion      Y
  2    Promo 2     Membership Renewal        Y
  3    Promo 3     Membership Grad           Y
  4    Promo 4     Membership Promotion      N
  5    Promo 5     Membership Promotion      N
  6    Promo 6     Membership Grad           N

Example:
Promo 1 defaults column value = 'Y'
However, a new 'Y' defaults value is inserted in either existing row with the same membership Type or a newly created row with the same membership Type.
So once a default value is being changed to 'Y', the defaults column value in promo 1 will automatically changed to 'N'.

How do i achieve that?

CREATE TRIGGER trgDefaults ON FYPDB.dbo.Promotions
AFTER INSERT, UPDATE
AS
BEGIN;
UPDATE FYPDB.dbo.Promotions SET defaults = 'N'
WHERE defaults = 'Y'
  AND membershipType IN (SELECT membershipType FROM Inserted);
END

The trigger control that i used:

USE FYPDB
GO
CREATE TRIGGER trgDefaults ON dbo.Promotions AFTER INSERT, UPDATE AS BEGIN; UPDATE dbo.Promotions SET defaults = 'N' WHERE defaults = 'Y' AND membershipType IN (SELECT membershipType FROM Inserted); END

However, it changes my insert defaults value from a 'Y' to a 'N'. It's not what i wanted.


texts:
I'm using the nested set architecture to store URL hierarchies in the following InnoDB table on MySQL 5.7. From my research, I've learnt that using a SPATIAL index will significantly speed up any queries to retrieve a full path on tables that contain millions of rows. However, EXPLAIN is showing that sk_set shows up in possible_keys, but is then not used in the query plan. I've tried everything I can think of, and I could really use some expert guidance at this point. Any help is much appreciated.

My table definition:

--
-- Table structure for table `site_uri_layout`
--

CREATE TABLE `site_uri_layout` (
  `site_root_id` bigint(20) NOT NULL DEFAULT '0',
  `lft` bigint(20) NOT NULL DEFAULT '0' COMMENT 'Left hand side of nested set node',
  `rgt` bigint(20) NOT NULL DEFAULT '0' COMMENT 'Right hand side of nested set node',
  `set` linestring NOT NULL,
  `id` bigint(20) NOT NULL DEFAULT '0',
  `language_id` bigint(20) NOT NULL DEFAULT '0',
  `uri` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='nested set' ROW_FORMAT=COMPACT;

--
-- Dumping data for table `site_uri_layout`
--

INSERT INTO `site_uri_layout` (`site_root_id`, `lft`, `rgt`, `set`, `id`, `language_id`, `uri`) VALUES
(2, 1, 32, '', 1, 0, ''),
(2, 2, 3, '', 5, 0, ''),
(2, 4, 17, '', 3, 0, ''),
(2, 5, 6, '', 8, 0, ''),
(2, 7, 14, '', 11, 0, ''),
(2, 8, 9, '', 14, 0, ''),
(2, 10, 11, '', 7, 0, ''),
(2, 12, 13, '', 15, 0, ''),
(2, 15, 16, '', 9, 0, ''),
(2, 18, 19, '', 6, 0, ''),
(2, 20, 21, '', 2, 0, ''),
(2, 22, 23, '', 16, 0, ''),
(2, 24, 31, '', 4, 0, ''),
(2, 25, 30, '', 10, 0, ''),
(2, 26, 27, '', 12, 0, ''),
(2, 28, 29, '', 13, 0, '');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `site_uri_layout`
--
ALTER TABLE `site_uri_layout`
  ADD PRIMARY KEY (`site_root_id`,`lft`,`rgt`),
  ADD KEY `id` (`id`),
  ADD KEY `lft` (`lft`),
  ADD KEY `rgt` (`rgt`),
  ADD SPATIAL KEY `sk_set` (`set`);

My query:

SELECT
p.*
FROM
site_uri_layout AS n
LEFT JOIN site_uri_layout AS p ON (MBRWithin(Point(0, n.lft), p.set))
WHERE
n.id = 13
ORDER BY p.lft;

EXPLAIN shows:

1   SIMPLE  n       ref id  id  8   const   1   100.00  Using index; Using temporary; Using filesort
1   SIMPLE  p       ALL sk_set              16  100.00  Range checked for each record (index map: 0x10)

My test data set contains only 16 rows. I've edited out the raw geometric data. The 'pretty printed' nested set looks like this:

1
 5
 3
  8
  11
   14
   7
   15
  9
 6
 2
 16
 4
  10
   12
   13

I was expecting the optimizer to use sk_set as a key for this query, which was the whole point of setting up a SPATIAL index. What am I doing wrong?


texts: sql server transactional - replication - the internal procedures often deadlock with user processes

texts:
Very often I have deadlocks on my subscription server, involving the replication internal stored procedures and user processes either ad hoc or procedure queries. For example:

  Deadlock of the replication procedure frame procname=Bocss2.dbo.sp_MSupd_dbotblBOrderItem line=79 stmtstart=8684 stmtend=14714 sqlhandle=0x03002a004d01df507b1245016ba3000001000000000000000000000000000000000000000000000000000000

With the following query:

SELECT 'INSERT INTO @TempTable1 (OrderNo, OrderSeqNo, OrderItemDate)
VALUES (LTRIM(''',strBxOrderNo, '''),', sintOrderSeqNo, ', ''',
  CONVERT(VARCHAR(20), sdtmOrderItemStatusUpdated, 113), ''')'
FROM tblBOrderItem
WHERE sintOrderItemStatusId = 4 -- In Picking
AND sdtmOrderItemStatusUpdated BETWEEN GETDATE() -30 AND GETDATE() -1
 -- Give the warehouse a chance to deliver the order
AND decFinalPrice > 0
ORDER BY strItemNo

I don't want to enable the snapshot isolation level - where writes don't block readers - at the cost of maintaining a row version at the tempDB, but I am considering partitioning the big tables that are usually the culprits on the deadlocks.

what stats I could have a look at so that I could guess whether or not this is a good idea?


texts: Keep track execution plans on the server

texts:
We are still on SQL Server 2014, so we do not have query store.  Is there any other tool that can be used to track execution plan of stored procedures running on the server.

If a stored procedure timed out I should be able to check if the execution plan for a stored procedure changed.  How can i achieve that?


texts: Double Concurrent Restore from one backup for 3 node AG?

texts:
I have a 400GB database on SQL 2014 Enterprise Edition Instance.  I took a full backup as a single file (not striped), it restores to 8 datafiles and 1 log file.  I put the backup in a shared folder that all instances have access to.

I need to restore without recovery (NORECOVERY) on two instance, then follow through with the other steps to join them in the AG.

Using one SSMS I used 2 query windows to, restore the database on the two different instances (different VM servers in different locations) at the same time.  I used sp_whoisactive to monitor progress as both restored.

Everything worked fine, the final t-log and join with the wizard gave me a stable synchronized database in the AG.

About 30 minutes in I got wondering if, this was counterproductive.  Clearly it worked, the time to completion was similar (or a bit longer) then similar single restores. But there are multiple distractions and ongoing process, so my perception may be wrong.  From watching the Wizard I know it only restores to one instance at at time.

All things being equal is doing a double concurrent restore from one backup to two instances an optimal timing choice? (is it faster or slower then one after the other?)

I found 2 related posts that do not seem to address my question

Would it be possible/wise to execute 2 parallel restores from a single backup?
Restoring two databases to precisely the same time


texts: Tracking changes in stored procedures within a database

texts:
Could you please help me out here.

I have multiple databases and each of the databases have the same tables and stored procedures.

I want to be able to compare the stored procedures and see if there were any changes made to one stored procedure in one database, that has not been replicated across the remaining databases.

I also want to be able to fine out if there were changes made to any tables (structure or new tables added in one and not in the rest). I do not need to know if the data within a table has been updated or if one table has more rows than the other.


texts: How to store date & time in DECIMAL(18,3)?

texts:
I am currently working with a database scheme that contains a DECIMAL(18,3) column that is supposed to contain date and time. Sadly, I do not have any example data and need to create it myself.

However, I do not know how to correctly format the dates I want to insert, because when you add together year, month, day, hours, minutes and seconds, you get 14, if you add 3 more numbers for the milliseconds you get 17. Where is the eighteenth number hiding from me?


texts: mysql - Access denied for user 'user'@'localhost' (using password: NO)

texts:
I'm trying to fix the infamous "Access denied" error, but I don't understand why my fix doesn't work. Can someone please help me?

EDIT: This is the sequence of steps I took to fix the problem. It is still not working. Can anyone tell me what I'm doing wrong?

$ sudo service mysql stop
mysql stop/waiting
$ sudo service mysql start
mysql start/running, process 5718
$ sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
[1] 5758
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 85
Server version: 5.5.32-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> UPDATE mysql.user SET Password=PASSWORD('test')
    -> WHERE User='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 4  Changed: 0  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

$ rake db:create
Access denied for user 'root'@'localhost' (using password: NO)Please provide the root password for your mysql installation


texts: Is it possible to make a trigger that does not allow INSERT?

texts:
I'm currently learning how to implement sqlite commands through C code and I've been trying to figure out the best way to ensure that no data can be inserted to a table (which will only happen during a certain period of time).
I have a database History and in this database exists the table events with the following schema:
CREATE TABLE events
    (
     id integer primary key autoincrement,
     msg varchar(20) NOT NULL,
     ago varchar(30) NOT NULL
    );

I know a mySQL implementation would likely use INSTEAD OF INSERT on the table, though I also know INSTEAD OF can't be executed on tables, only views in sqlite. (To be honest I'm new to databases so I only kinda understand this concept).
Would anyone with some more database experience be able to help me with writing (or at the very least understanding the theory behind) a trigger that does not allow data to be inserted to the table?
Edit: This article is where I first got the idea that this would be possible


texts: SQL Server R2 and HyperThreading - Max Worker Threads and performance

texts:
SQL Im getting this error:

This file is generated by Microsoft SQL Server
version 10.50.2500.0
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.

Computer type is Intel(R) Xeon(R) CPU           X5650  @ 2.67GHz.
Bios Version is HP     - 2
24 X64 level 8664, 2 Mhz processor (s).
Windows NT 6.1 Build 7601 CSD Service Pack 1.

Memory
MemoryLoad = 98%
Total Physical = 24565 MB
Available Physical = 386 MB
Total Page File = 49129 MB
Available Page File = 23110 MB
Total Virtual = 8388607 MB
Available Virtual = 8353094 MB
2012-03-05 09:50:27.67 Server      ***Unable to get thread context for spid 0
2012-03-05 09:50:27.67 Server      *

2012-03-05 09:50:27.67 Server      *
2012-03-05 09:50:27.67 Server      * BEGIN STACK DUMP:
2012-03-05 09:50:27.67 Server      *   03/05/12 09:50:27 spid 2556
2012-03-05 09:50:27.67 Server      *
2012-03-05 09:50:27.67 Server      * Non-yielding Scheduler
2012-03-05 09:50:27.67 Server      *
2012-03-05 09:50:27.67 Server      *
2012-03-05 09:50:27.68 Server      Stack Signature for the dump is 0x00000000000000FB

I read a post where SQL 2008 R2 perfomance was giving problems with proccesors that have hyperthreading. Do you think this is the main cause of my problems? Would be a nice idea to disable the hyperthreading?


texts: SELECT query to find rows for specific entry

texts:
I have a table location. And I want to extract rows from it for the column pattern.

And I did this query SELECT COUNT(DISTINCT username) FROM location;
+--------------------------+
| COUNT(DISTINCT username) |
+--------------------------+
|                    58636 |
+--------------------------+

So I got the whole number of rows.

My question is: how to get rows for each different username entry but not on the whole. The username meaning is not known, and goal to see how many rows each username has.

So for e.g.

username Nick - 10 rows

username Paul - 20 rows


texts: (MySQL) SET column with values from a foreign key constrant

texts:
I am making a junction table that tracks relationships between users. Among other things it is formatted with a group leader, and with a variable amount of members. All users have a user id and so this relationship between leader and members must constrain to the defined uuids in the users table.

Currently, the only way I know how to accomplish this is to make a bunch of columns named member1, member2 and so on, and then make foreign key constraints on each column, but this is inefficient as the number of members in a group varies from entry to entry. So I thought to use a SET type for the members column. However, the SET column type forces you to define constants for it, and this simply isn't possible with a wide amount of user ids.

So in essence, my question is, how can I make a set whose values are constrained to the values in another table? I'm trying my best to follow the standard practice of using junction tables, but if you have a better organizational solution which allows me to sidestep this issue entirely while still accomplishing the same or similar goal that would also be most appreciated.

Edit: Group id is synonymous with leader id because groups are identified by their leaders.


texts: Trouble connecting to Oracle XE running in Centos Virtual Box

texts:
I have a centos vm that I run using virtual box. I installed Oracle XE on this vm, and am now having trouble connecting to it from my SQL Developer running on my host machine (OS X).

Things I checked:

I can ssh into centos fine.
When I run nmap on the host machine while the centos vm is running I see port 1521 open. (nmap -sT -O localhost)
I can run telnet localhost 1521 and it connects fine.
tnsping xe on the centos vm returns OK message
I can connect as my user using sqlplus on the centos vm
I have port forwarding setup in VirtualBox to forward requests on port 1521 to centos.

What I cannot do is connect using SQLDeveloper on the mac.

Here is my listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = XE)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
    )
  )

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

DEFAULT_SERVICE_LISTENER = (XE)

and my tnsnames.ora:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

I am not sure what else I am missing, or what other troubleshooting steps I can take to resolve this.


texts: What does it mean for MySQL to spend a long time "starting" queries?

texts:
What does it mean if suddenly queries start spending a long time in the starting thread state?
This is our RDS Performance Insights dashboard slicing by "waits", showing huge bumps in sessions stuck on "starting":

I found info on these in the General Thread States docs, but the description of the "starting" thread state is not very helpful:

The first stage at the beginning of statement execution.

The slicing by query only puzzles me further. COMMIT statements seem to correspond to the "starting" waits, but COMMIT happens at the end. It'd make me think "locks!", but there are distinct thread states for locks which don't show up here at all.

From comments:
Top waits from RDS Performance Insights:

Top queries from RDS Performance Insights:


texts:
I've been running a restore job on 38m row table for over 24 hours that doesn't seem to want to come to an end. When I check pg_stat_activity it tells me that the restore job is still running, and the query column shows valid insert queries slotting data into the database.

However, when I run a count(*) on the table, I've been getting the same number just shy of 38m for the last 15 hours.

I don't think the issue is a hardware one, as previously a restore of this table took just 3 hours (it had around 25m rows at that stage).

Has anyone else experienced anything similar?

Also, is there any way to determine the number of rows in a .backup file? Perhaps, then I can know if it has captured all of the data.

Or is the only way to be sure to ignore the backup and run a complete insert all of the data again?


texts: Can I install Oracle 12c and NOT use pluggable databases?

texts:
I've just installed Oracle 12c.   I did not select any of the fancy new options, I just wanted an Oracle instance.   I'm fairly new to Oracle but I was used to 11.2.0.4.

Every command I try now fails and it's always something like "Cannot do [x] on pluggable database", etc.

I just want a normal oracle instance and I want to be able to create users and import my database but every turn I take has some frustrating error having to do with pluggable databases.  I don't care at all about them, and I specifically tried to avoid any install option that looked like it would make a "pluggable" database but apparently it still did.

Is there any way I can just keep life the same in 12c or am I stuck with this entirely clumsy pluggable paradigm?
Thanks very much.


texts: Getting current number of connections in mysql

texts:
I want to know if there is any parameter to show the current number of mysql connections (or peak value in the past 10 minutes or something else). Right now, I only see max and max_used connections.
mysql> show global status like '%connection%';
+-----------------------------------+---------------------+
| Variable_name                     | Value               |
+-----------------------------------+---------------------+
| Connection_errors_accept          | 0                   |
| Connection_errors_internal        | 0                   |
| Connection_errors_max_connections | 434                 |
| Connection_errors_peer_address    | 0                   |
| Connection_errors_select          | 0                   |
| Connection_errors_tcpwrap         | 0                   |
| Connections                       | 2380515             |
| Max_used_connections              | 152                 |
| Max_used_connections_time         | 2020-07-09 19:28:43 |
+-----------------------------------+---------------------+
9 rows in set (0.00 sec)

mysql> show global variables like '%connection%';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_connection | latin1            |
| collation_connection     | latin1_swedish_ci |
| max_connections          | 300               |
| max_user_connections     | 0                 |
+--------------------------+-------------------+


texts: Different CREATE_TIME for tables in Master and Slave

texts:
We have found that the value of CREATE_TIME column in information_schema.tables for many tables was different in MASTER and SLAVE. Upon going through the logs, it was found that the CREATE_TIME value in the SLAVE was that of the scheduled weekly OPTIMIZE routine. Hence it can be inferred that the OPTIMIZE TABLE statements are causing the TABLES to be recreated in SLAVE. However, the scheduled job runs on MASTER only. But the CREATE_TIME in MASTER is not getting affected.

Why is the OPTIMIZE TABLE command behaving differently on MASTER and SLAVE? And if the table is being reconstructed at SLAVE, is the table becoming temporarily unavailable during that period?


texts: Users vs. People - where to keep the email address?

texts:
I'm designing an app similar to Facebook, called Headtome.  I want to have users that create public profiles, including email addresses.  Then, Mary can choose to message John, and that message goes straight to his publicly available email.  More creepily, if John and Mary mark themselves as in a relationship, then only John can view Mary's profile.

Here is (part of) the current database structure:

Table users:

id | username | password | person_id |
--------------------------------------

Table persons:

id | first_name | last_name | nickname |
----------------------------------------

Table person_emails:

id | person_id | email_address | is_main |
------------------------------------------

[I use a separate table for emails because a person may decide to show multiple email addresses.]

I argue that the users table should also have an email_address column.

The Pros

If a user forgets their password, we need an email address to reset it
A user might use a different email for logging in than the one they show on the profile
Or, they may choose not to show an email at all (don't message me)
A user's profile data is still available (through person_id)
A user email might be forever, whereas the public ones are temporary (job-related, etc.)
When Mary messages John, she's messaging John the Person, not John the User

The Cons

Managing two separate email addresses might get confusing for users
Or it could lead to duplication
The whole point is to create a profile, so persons is the main table
If John and Mary are dating, isn't it John the User who is allowed to see her profile?

What is the right approach here? Not that it should make a difference, but this is in MySQL.  Also, this is a fictional example exemplifying a real dilemma.


texts: Which ports need to be opened in order to use Named Pipes

texts:
I have a SQL Server with both TCP\IP and Named Pipes protocols enabled and configured.

From the server itself I can connect to the database using either TCP or Named Pipes, by specifying the connection as np:servername\instance

However from another server I can only connect using TCP\IP using servername\instancename. Adding the np: prefix just causes a connection timeout failure.

Do I need to open any specific ports in order to use Named Pipes? This is a named instance running on a dynamic port. The dynamic port is opened in the firewall.

The reason I'm looking into this is because I'm experiencing intermittent issues with an ODBC connection - timeouts, connection dropouts etc. From what I've read these issues have in the past been resolved by enabling Named Pipes - which I've done, but then by default my connections are using TCP, and by force, I can't get it to connect from this server.