jdbc equivalent to libpq "hostaddr" connection string parameter Is there an equivalent to libpq's hostaddr parameter in the JDBC connection string? I want to connect via a local TCP proxy (on 127.0.0.1), but use ssl=verify-full with the CA bundle of the remote Postgres instance.

It doesn't seem to be obvious how to do this in the JDBC driver without some sort of class overloading of the HostVerifier?  Anyone tried this before?

If your proxy is just a TCP forwarder, like iptables DNAT, your postgresql certificate has to include a SubjectAlternativeNames with the IP address you connect to (the proxy address).
Otherwise, if your proxy decode SSL and creates a different connection to postgresql, then the same change should be made to the proxy server.


Why should I renew stored procedures? Is there a reason significant reason to recompile stored procedures?
Of course the data in a database changes over time and the resultset of some stored procedures could differ due to changed underlying data. But unless there are changes of table structures, the stored procedures should be good forever?
I'm talking about Sybase ASE 15.

the data in a database changes over time

This is the usual reason. While the concept of compiling a stored procedure is implementation specific, it usually involves producing a query plan for the procedure and/or the individual statements it is formed from, and storing it for later reuse (to save that part of the process being repeated every time the procedure is called).
As your data grows, the balance of data in each table/index may change in such a way that the optimal plan when the procedure was last compiled is now much more inefficient than other options so the procedure will no longer be performing as well as it could. If you had just a few rows in a table when the procedure was created (or last compiled) a scan might have been more efficient than one or more seeks for instance, but that may no longer be the case later. Also which index it is most efficient to touch first can change over time especially for queries on wide tables and those that join in several tables.
The database engine will usually have some heuristics built in to cause a recompile after massive data changes, as well as after structural changes, but these are usually quite conservative in their action so manually kicking off a recompile is sometimes necessary. Much the same as the heuristics involved in deciding when to resample index statistics histograms (which in tern feeds into the query plan decisions generally and in your stored procs).
Sometimes procs with parameters will need different plans to be efficient for different inputs - sometimes there are circumstances where it is beneficial to always for a recompile because the difference is so high that you don't want to risk using a slow cached plan (in MS SQL Server the WITH RECOMPILE hint is there to deal with such circumstances, or the per-statement OPTION (RECOMPILE) for a finer grained approach). Procedures complex enough that this is a major issue are often "code smells" which mean your design needs a tweak, though they are not always easily avoidable.
A final reason to recompile stored procedures, functions, views, and other programmatic objects, is when changes elsewhere are made in a way that breaks dependency checking, meaning an automatic recompile does not happen when it is actually needed. For instance:

You have a proc that uses a view on a table.
Someone drops and recreates the view (instead of using ALTER VIEW).
The dependency information proc->view is now lost.
The table is altered.
As the dependency chain is broken, the engine does not know that the statements in the procedure may need to be recompiled meaning that procedure may start failing completely or giving incorrect results.
A recompile (at this stage or after the view was recreated) fixes this.


Should I use tempdb or memory optimized table variable? I would like to change some of my stored procs to use memory optimized tables.
Before I do this, could you help me to clarify some things?
parallel execution
Here I can read, the tempdb doesn’t support it for insert-operation (What's the difference between a temp table and table variable in SQL Server?).
But what about with data retrieving, like select?
memory underrun
With memory optimized tables could it be possible, that I get an error, if the server is less of RAM?
In this thread (https://www.brentozar.com/archive/2014/06/temp-tables-table-variables-memory-optimized-table-variables/) I can read about this.
performance boost
Memory optimized tables do the boost, or not (What's the difference between a temp table and table variable in SQL Server?)?
This thread (https://www.brentozar.com/archive/2014/04/memory-ssd-tempdb-temp-table-sql-2014/) describes, that tempdb (starting with SQL 2014) can also reduce disk IO.
Should I use tempdb or memory optimized table variables?
I'm using Microsoft SQL 2014 (12.0.5000.0) Enterprise 64 bit. The SQL queries are a bit complex: e.g. there is a query, that uses 2 views and each of these views depending on other views.
The result of the whole query is not big (a couple thousands of rows).
Some of the tables, from which the views are depending, have 40 - 50k rows.
I alraedy realized, if rewrite a view into a table valued function, it increases the performance. Especially if the original view is embedded in another view, and used more time.
But what about the 3 points above? For me the links are somehow conflicting.

Parallel Execution:

SQL Server does support parallel inserts into temp tables in SQL Server 2014 with the SELECT INTO syntax. In SQL Server 2016 parallel inserts are also supported into temp tables that are heaps. In SQL Server 2016 SP1 parallel inserts into heaps require the TABLOCK hint.

A query that modifies table variables will not contain any parallel zones. However, a query that references a table variable may run in parallel.

Memory:

Yes, memory-based tables can only be in RAM and cannot spill to disk. See this article.

Performance Boost:

My advice would be to first measure the performance of your stored procedure to confirm that the temp tables are the source of the unacceptable performance. If you confirm that consider the following simpler options first:

Only save data to temp tables that you need. Apply filters when inserting data as soon as possible.
Create columns with the right data types.
Create indexes as appropriate for your temp tables.
Drop your temp tables as soon as you no longer need them.
Make sure that your tempdb database follows best practices. This could be a separate post but here's one resource.

If none of that works and you feel confident that you understand the limits of memory optimized table variables go ahead and test your procedures with them. The previously referenced blog post does mention that as a use case:

  As replacement for traditional table variables, and in some cases for #temp tables that are local to a stored procedure.  This is particularly useful if there is a lot of tempdb contention in the system.


Best way to organize compound indexes on partitioned table (date by range)? I have a table with an auto increment primary key, but in order to use the partition feature in MySQL I had to include the date in the primary key so I would use partition by range using the date column.

Since MySQL creates an automatic index on the primary key (I'm assuming this), does this mean that since my date column is not on the left part of the index, then would it be beneficial to create a single index on just the date column?

95% of my queries use the date column under WHERE
5% of my queries use the id under the WHERE clause
none of my queries use both columns under the WHERE clause

I used to have just a single index on the date column, but since I'm using partitions I'm forced to add the date column to the primary key. Should I create a single index on the date column? I'm asking this because my tables are big (30GB) and I don't want to add unnecessary overhead.

I have a table with an auto increment primary key, but in order to use the partition feature in MySQL I had to include the date in the primary key so I would use partition by range using the date column.

Not necessarily. Since AUTO_INCREMENT columns don't have to be PRIMARY KEYs, you could instead do something like the following and the AUTO_INCREMENT column would still work.

ALTER TABLE myTable DROP PRIMARY KEY, ADD KEY(myAutoIncrementColumn);

  Should I create a single index on the date column?

You have queries that are doing lookups on the AUTO_INCREMENT column, so I would still keep its index in place (PRIMARY or without). However since 95% of your queries are hitting the date column, you're going to want to add a secondary index:

ALTER TABLE myTable ADD KEY (myDateColumn);

If you changed the AUTO_INCREMENT column from being a PRIMARY KEY to simple a KEY index like I mentioned above, your date column isn't part of any non-unique indexes anymore, so you can now create the partition:

ALTER TABLE myTable
PARTITION BY RANGE ( UNIX_TIMESTAMP(myDateColumn) ) (
  PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-01-01') ),
  PARTITION p1 VALUES LESS THAN ( MAXVALUE )
);


Restore a copy of the encrypted database I have encrypted the backup using the method followed by the file server. Unfortunately, I did not keep a copy of the certificate of the process of encryption and unfortunately also the server was completely destroyed and is impossible to run again and I have to restore a copy of the database.
I have a copy of the rule but it is encrypted. Is there a solution to this problem?

I have encrypted the backup using the method followed by the file server.

Not sure what that means.

  Unfortunately, I did not keep a copy of the certificate of the process of encryption and unfortunately also the server was completely destroyed and is impossible to run again and I have to restore a copy of the database.

That's going to be impossible. If you don't have the encryption cert, you can't restore.

  I have a copy of the rule but it is encrypted.

Rule? Not sure what a rule has to do with this.

  Is there a solution to this problem?

Tell the people that you can't restore the database and it is lost forever.


MySQL user created via command line can't access PHPMyAdmin I'm trying to create a read-only user with access to one database in PHPMyAdmin.  Since I'm a server newbie I followed some instructions line-by-line on setting up my database and PHPMyAdmin.  As such, my root account is not accessible via the GUI; only a full access child account.  This account cannot create users via the GUI, so I did the following via command line:

mysql -u root -p
mysql> create user 'readonly-user'@'localhost' IDENTIFIED BY 'password';
mysql> grant select on mydatabase.* to 'readonly-user'@'localhost';

If I try to log in via PHPMyAdmin as readonly-user, however, I'm not able to get in.

Is there something I need to do to register this account with PHPMyAdmin?

You have to run the FLUSH PRIVILEGES; command.


Keeping rows ordered by counter value I'm storing linked words in a Cassandra v2 cluster and I'm having a hard time doing efficient reads. I'm trying to fetch the top X rows with the highest counter value.

Cassandra doesn't store the rows in any ordered way, so simply adding LIMIT X to the CQL query returns random-ish rows.
The ORDER BY command in CQL only works for columns that are (part of) the key.
OrderPreservingPartitioner doesn't exist anymore in Cassandra v2.

At the moment, when doing a read, I have to fetch all rows and manually select those X rows with the highest counter value. This literally slows some queries down by 100x, because even mildly popular words often have hundreds of thousands of words linked to them, not to mention the waste of bandwidth and other resources.

Are there any clever techniques to make this more efficient?

I'll try to address your concerns one at a time.

  Cassandra doesn't store the rows in any ordered way, so simply adding LIMIT X to the CQL query returns random-ish rows.
  The ORDER BY command in CQL only works for columns that are (part of) the key.

Not entirely correct, on either point.  The on-disk sort order in Cassandra is determined by the first clustering column of your primary key.  As a result, ORDER BY will also only work on that column (and only that column).

  I'm trying to fetch the top X rows with the highest counter value.

Cassandra will not allow you to put a secondary index on a counter column, nor can you make it a part of your key (which also means you cannot sort by it).  Therefore, querying data by the value of a counter column is not possible.

It would appear that your use case is not an appropriate fit for Cassandra.  I would suggest solving this issue with a relational database or something else that would provide you with the necessary aggregation tools.


Determine the name of the column with a null value nearest the last column I have five columns in a mysql table

-id
-field_1
-field_2
-field_3
-field_4
-field_5

Field_5 is the last column of the table i have named transactions. If i have a select statement like
select * from transactions where id = 4,how would i know the name of the column nearest the column field_5 that is null.

Try this

select
    case when field_4 is null then 'field_4'
         when field_3 is null then 'field_3'
         when field_2 is null then 'field_2'
         when field_1 is null then 'field_1'
                              else null
    end
from transactions;


MySQL 5.6 on OS X 10.8.2 disconnects on user creation? Via Homebrew I just upgraded from MySQL 5.5.29 to 5.6.10 and all my queries and existing users are working fine, but whenever I try to modify permissions or create users now MySQL disconnects me. Are there any my.cnf settings I need to change for 5.6, or system table updates I need to do etc?

mysql> create user 'something'@'localhost' identified by 'password';
ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> create user 'something'@'localhost' identified by 'password';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: *** NONE ***

ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> SHOW VARIABLES LIKE "%version%";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: *** NONE ***

+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 1.2.10              |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.6.10              |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | osx10.8             |
+-------------------------+---------------------+
7 rows in set (0.00 sec)

Alright another instance of "read your damn logs" :)

tail /usr/local/var/mysql/username.local.err told me:

2013-02-25 11:29:50 1616 [ERROR] Native table 'performance_schema'.'accounts' has the wrong structure
2013-02-25 11:29:50 1616 [ERROR] Native table 'performance_schema'.'hosts' has the wrong structure
2013-02-25 11:29:50 1616 [ERROR] Native table 'performance_schema'.'socket_instances' has the wrong structure
2013-02-25 11:29:50 1616 [ERROR] Native table 'performance_schema'.'socket_summary_by_instance' has the wrong structure
...

So a quick google turned up the answer: sudo mysql_upgrade -u root -ppass


what is causing arithmetic overflow in the query below? when I run the following query on MY_Database

select * from sys.sysfiles

I get the following results:

But when I run a dynamic query that gets the percent of free space I get:

  Msg 8115, Level 16, State 7, Line 93 Arithmetic overflow error
  converting numeric to data type numeric.

DECLARE @command NVARCHAR(MAX)
SELECT @command = 'SELECT  db_name() as db_name,
CAST(S.size/128.0 - CAST(FILEPROPERTY(S.name, ' + '''' +
               'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB
        ,CAST(100 * (CAST (((S.size/128.0 -CAST(FILEPROPERTY(S.name,
        ' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(S.size/128.0))
        AS decimal(5,2))) AS varchar(8)) + ' + '''' +  '''' + ' AS FreeSpacePct
        FROM sys.sysfiles S'

exec sp_executesql @statement = @command

I am struggling to find the reason of the arithmetic overflow.
why is it happening?

  Why divide by 128? It is because both sys.sysfiles and FILEPROPERTY give the number of 8K
  pages, not MB, and to convert from 8K pages to MB you divide by 128
  as it is explained here

Why is it dynamic?

Because I actually get the values from each database using sp_ForEachDB as you can see on the example below:

DECLARE @command VARCHAR(5000)
SELECT @command = 'Use ' + '?' + ' SELECT  db_name() as db_name,
CAST(S.size/128.0 - CAST(FILEPROPERTY(S.name, ' + '''' +
               'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB
        --,CAST(100 * (CAST (((S.size/128.0 -CAST(FILEPROPERTY(S.name,
        --' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(S.size/128.0))
        --AS decimal(4,2))) AS varchar(8)) + ' + '''' +  '''' + ' AS FreeSpacePct
        FROM dbo.sysfiles S'

EXEC sp_ForEachDB @command

I am struggling to find the reason of the arithmetic overflow. why is it happening?

Most likely the metadata is returning some unexpected values that your code cannot handle. For example:

-- Example values returned from sysfiles and FILEPROPERTY
DECLARE
    @size integer = 1,
    @spaceused integer = 10000;

-- The essence of the code in the question
SELECT
    CAST
    (
        100 *
        (
            CAST
            (
                (
                    (@size/128.0 - @spaceused/128.0)/(@size/128.0)
                )
                AS decimal(5,2)
            )
        )
        AS varchar(8)
    ) + '' AS FreeSpacePct;

...returns the error mentioned in the question, because the computed (negative!) value will not fit in decimal(5,2).

There are reasons why size might be reported as much lower than space used, including tempdb file growths, filestream files, bugs in older versions of SQL Server...too many to list. You could/should code defensively against this possibility (and also for offline/defunct files...and so on).

The question is tagged SQL Server 2014, so there's no need to use the deprecated sys.sysfiles view (for backward compatibility with SQL Server 2000):

I might write this query as:

SELECT
    DatabaseName = DB_NAME(),
    [FileName] = DF.name,
    FileType = DF.type_desc,
    SizeMB = STR(DF.size * Factor.PagesToMB, 10, 2),
    SpaceUsedMB = STR(FP.SpaceUsed * Factor.PagesToMB, 10, 2),
    FreeSpaceMB = STR(FS.FreeSpace * Factor.PagesToMB, 10, 2),
    FreeSpacePct =  STR(Factor.ToPct * FS.FreeSpace / DF.size, 7, 4)
FROM sys.database_files AS DF
CROSS APPLY (SELECT FILEPROPERTY(DF.name, 'SpaceUsed')) AS FP (SpaceUsed)
CROSS APPLY (SELECT DF.size - FP.SpaceUsed) AS FS (FreeSpace)
CROSS JOIN  (SELECT 8e0 / 1024e0, 1e2) AS Factor (PagesToMB, ToPct);

Main advantages:

It separates out the calculation steps
Uses float arithmetic to avoid overflows
STR formats the result and does not raise an error on overflow
It does not cause the error in the question to be thrown

A dynamic SQL version (to collect information for all databases):

DECLARE @SQL nvarchar(2000);

SET @SQL = N'
USE ?;

SELECT
    DatabaseName = DB_NAME(),
    [FileName] = DF.name,
    FileType = DF.type_desc,
    SizeMB = STR(DF.size * Factor.PagesToMB, 10, 2),
    SpaceUsedMB = STR(FP.SpaceUsed * Factor.PagesToMB, 10, 2),
    FreeSpaceMB = STR(FS.FreeSpace * Factor.PagesToMB, 10, 2),
    FreeSpacePct =  STR(Factor.ToPct * FS.FreeSpace / DF.size, 7, 4)
FROM sys.database_files AS DF
CROSS APPLY (SELECT FILEPROPERTY(DF.name, ''SpaceUsed'')) AS FP (SpaceUsed)
CROSS APPLY (SELECT DF.size - FP.SpaceUsed) AS FS (FreeSpace)
CROSS JOIN  (SELECT 8e0 / 1024e0, 1e2) AS Factor (PagesToMB, ToPct);
';

DECLARE @Results AS table
(
    DatabaseName sysname NOT NULL,
    [FileName] sysname NOT NULL,
    FileType nvarchar(60) NOT NULL,
    SizeMB char(10) NULL,
    SpaceUsedMB char(10) NULL,
    FreeSpaceMB char(10) NULL,
    FreeSpacePct char(7) NULL
);

INSERT @Results
EXECUTE sys.sp_MSforeachdb
    @command1 = @SQL;

SELECT R.*
FROM @Results AS R
ORDER BY R.DatabaseName; -- Or whatever

Usual caveats about using sp_MSforeachdb.


Can multiple instances of procedure create same #Temp simultaneously? I have a procedure which inserts data into #temp table.
After performing certain manipulations to the data, it stores data from #temp table to some other table.
I cannot run multiple instances of this procedure simultaneously. Why?
We create #temp table with the same name in multiple tabs. Then why shouldn't we be able to do the same for multiple instances of temp table?
Any solutions?
Example:
--exec dbo.testing_temp_table
CREATE PROCEDURE [dbo].[testing_temp_table]
AS
BEGIN

    SET NOCOUNT ON;

    -- Insert statements for procedure here
    IF OBJECT_ID('tempdb..#temp_1') IS NOT NULL drop table #temp_1
    select  * into #temp_1 from dbo.Emp

END

"I cannot run multiple instance of this procedure simultaneously. Why?"

Could you clarify what you mean by "cannot run"? Do you get an error?
Now, you don't need to check the existence of the table in your stored procedure with the IF OBJECT_ID() thing.
SQL Server will create an instance of the temp table per instance of the stored procedure, and it will only be visible to the creating scope, which is the stored procedure in this case, and to inner scopes, such as if you call another sub-procedure in your code. Once the procedure completes, the temporary table will be gone.
Under the covers, the query processor appends some unique string to the table name in TempDB for each instance.
BOL has all the details: CREATE TABLE (Transact-SQL)
-- In response to OP comment regarding the stored procedure generating the same suffix. --
You are not giving SQL Server enough credit here. If your claim was true, this would have been a major bug; executing multiple instances of a stored procedure which use temp tables is completely supported.
I suspect that you are testing it on a single connection, and seeing that upon re-execution of the same proc, you get the same suffix. This is an optimization for reusing the suffixes for repeated executions from the same session.
What you should test is:
CREATE PROCEDURE P
AS
BEGIN
CREATE TABLE #T(Col1 INT);
WAITFOR DELAY '00:00:10';
SELECT * FROM TempDB.sys.tables;
END

And then on two or more connection, execute concurrently
EXEC P

Here is what comes up for me:

name

#T_____________000000000004

#T_____________000000000005

HTH


How do I automatically grant all privileges to a schema created by a user in mysql? Someone new joined the company and I created an account for her in mysql using the following:

CREATE USER 'username' IDENTIFIED BY 'password'

She needs to create her own schema and so used:

GRANT CREATE ON *.* TO username;

The problem is that now she is able to create new schemas and tables but she cannot do anything with it. I'd like her not to have access to existing schemas/tables but be able to create her own db and do anything with it. Is this possible?

To grant all privs on database mydb to username

GRANT ALL PRIVILEGES ON mydb.* TO username;

To grant privs on a table mydb.mytable to username

GRANT ALL PRIVILEGES ON mydb.mytable TO username;

I suggest you drop the user first to clear up any server-wide privileges.

DROP USER username;
CREATE USER username;
GRANT ALL PRIVILEGES ON mydb.* TO username;

Why drop the user first? When you first create a user called username and login, username has no privs. Just run SHOW GRANTS; to see it. It should say something like GRANT USAGE ON *.* TO username. At that moment, username can only login. That's your chance to create privileges.

If you run

GRANT ALL PRIVILEGES ON *.* TO username;

username will have global database privileges (all databases)

If you run

GRANT ALL PRIVILEGES ON mydb.* TO username;

username will have database-wide privileges on the mydb database only.

If you run

GRANT ALL PRIVILEGES ON mydb.mytable TO username;

username will have table privileges on the mydb.mytable only.

You cannot mix them. If you do you accidently slip username privileges that username should not have.

If you want to see what privileges exist at the three levels run the the following queries

SELECT table_name,column_name
FROM information_schema.columns
WHERE table_schema='mysql'
AND table_name IN ('user','db','tables_priv')
AND column_name LIKE '%\_priv'
ORDER BY table_name,column_name;

Here are privs for MySQL 5.5

mysql> SELECT table_name,column_name
    -> FROM information_schema.columns
    -> WHERE table_schema='mysql'
    -> AND table_name IN ('user','db','tables_priv')
    -> AND column_name LIKE '%\_priv'
    -> ORDER BY table_name,column_name;
+-------------+------------------------+
| table_name  | column_name            |
+-------------+------------------------+
| db          | Alter_priv             |
| db          | Alter_routine_priv     |
| db          | Create_priv            |
| db          | Create_routine_priv    |
| db          | Create_tmp_table_priv  |
| db          | Create_view_priv       |
| db          | Delete_priv            |
| db          | Drop_priv              |
| db          | Event_priv             |
| db          | Execute_priv           |
| db          | Grant_priv             |
| db          | Index_priv             |
| db          | Insert_priv            |
| db          | Lock_tables_priv       |
| db          | References_priv        |
| db          | Select_priv            |
| db          | Show_view_priv         |
| db          | Trigger_priv           |
| db          | Update_priv            |
| tables_priv | Column_priv            |
| tables_priv | Table_priv             |
| user        | Alter_priv             |
| user        | Alter_routine_priv     |
| user        | Create_priv            |
| user        | Create_routine_priv    |
| user        | Create_tablespace_priv |
| user        | Create_tmp_table_priv  |
| user        | Create_user_priv       |
| user        | Create_view_priv       |
| user        | Delete_priv            |
| user        | Drop_priv              |
| user        | Event_priv             |
| user        | Execute_priv           |
| user        | File_priv              |
| user        | Grant_priv             |
| user        | Index_priv             |
| user        | Insert_priv            |
| user        | Lock_tables_priv       |
| user        | Process_priv           |
| user        | References_priv        |
| user        | Reload_priv            |
| user        | Repl_client_priv       |
| user        | Repl_slave_priv        |
| user        | Select_priv            |
| user        | Show_db_priv           |
| user        | Show_view_priv         |
| user        | Shutdown_priv          |
| user        | Super_priv             |
| user        | Trigger_priv           |
| user        | Update_priv            |
+-------------+------------------------+
50 rows in set (0.01 sec)

mysql>

Run the query to see the privs for 5.1 and 5.0


SSRS 2016 Mobile Report Color Settings In SSRS 2016 Mobile reports are a new option for Native mode report server. I have created several now and there appear to be consistencies between colors on different charts and graphs if the same columns are used in the setup of each element. However, I cannot find any way to consistently keep colors across slicer changes. Is there a work around, a setting a missed, a code behind, some way to ensure that when I change a parameter my chart legends do not suddenly swap colors?

This is with an "All" level setting on the parameters.

This is the new colors scheme after a parameter has been selected and the report updates with the new quantities. Totally unacceptable - this renders the report nearly worthless.

This behavior happens across reports and data sets for me. I would have tried to set an ordering as it appears to change based on quantity changes in each category, but there are no ordering settings for pie charts.

There is currently nothing in the Mobile Report editor itself that indicates a capacity to order parameters and most graphs have no ordering capabilities
(a couple of the bar charts allow for value or label based chart arrangement - part of the reason I was thrown off here!).

However, one can accomplish sorting from the data sets used in the report. The use of a single data set may cause issues in the parameters... so a standard ORDER BY x, x... approach will only go so far. Using the same values and a different data set, one can accomplish sorting and maintain filtering abilities.

To illustrate, the following query in Mobile Reports gives an oddly sorted State list, since it is actually sorted, in expected SQL fashion, after warehouse and business center lists.

SELECT
     WarehouseID
    ,PostalCode
    ,City
    ,County
    ,State
    ,BusinessCenter
FROM ...
ORDER BY WarehouseID, BusinessCenter, State

The ordering above solves some parameter list sorting and fixes legend color displays in the report, but doesn't address the State parameter sorting. Another data set with the exact same values contained in State can be used for the parameter, which enables correctly sorted and still functional parameter lists.

SELECT DISTINCT
State
FROM DIM.Geography
ORDER BY State

This resolves consistency of legend location and coloring across the dashboard and solves parameter selection list sorting.


How to quantify or locate command timeouts in Azure SQL I am trying to if confirm query timeout in Azure.

Our frontdoor is reporting a 503 response at 30 Seconds response, as its the SQL server default value, I am  trying to confirm if these are indeed query command timeouts.

I've searched for "Timeout" in the Logs section of the portal monitoring (not sure this how they would be represented)

Is there an method to locate query timeouts in Azure?

Answers or links with a decent resource would be highly appreciated as my searches are not proving fruitful at the moment

Is there an method to locate query timeouts in Azure?

Here are some links:

Monitor Azure SQL Database using Azure SQL Analytics (Preview)
https://docs.microsoft.com/en-us/azure/azure-monitor/insights/azure-sql

Monitoring performance by using the Query Store
https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15

Extended Events in Azure SQL Database https://docs.microsoft.com/en-us/azure/sql-database/sql-database-xevent-db-diff-from-svr


how to get a schematic diagram/layout of the an existing database I am looking for a schematic diagram of an existing database.
More precisely, I wan't to re-create the database model / schematics / the Entity Relationship Diagram (ERD) showing all the links between the tables of this existing database in MYSQL,I am using MYSQL workbench as an IDE,does it have database modelling tools to extract this?if not what other tools can do this?

UPDATE:

You can use Reverse Engineer under Database menu:

You just need to rearrange the tables.


How to include categories for which there are no values in an ssrs chart I've got a query that gets some data points for the last 6 months (prior to current), akin to this:

SELECT  [DataPoint], [When]
FROM    Table
WHERE
        [When] >= DATEADD(MONTH, -7, @CurrentMonth)
        [When] < @CurrentMonth

And my ssrs chart has a category like this:

=Format(DateAdd(DateInterval.Month, DateDiff(DateInterval.Month, CDate("1900/01/01"), Fields!When.Value), CDate("1900/01/01")), "MMM")

The horizontal axis is configured thusly:

Number: Category = Date, Type = Jan 2000

and I've tried setting:

Axis Options: Interval = 1, Interval type = Months

... but it doesn't help.

It works great when there are data points within each month, but I have some data sets that only have data for some months (ie. Dec/Jan) and it will only show those months.

Is there any way to get it to show the full 6 months of "data" (or lack thereof) without dumping empty data in the dataset?

The best solution I've found to this type of issue is to create a date dimension table that shows various information for dates- in this case it could simply be date and month, but other implementations I've seen include whether or not a particular day is a holiday or a day in which employees will work.

Then, apply the date criteria to this dimension table and left join the results onto it-

CREATE TABLE date_dim (Date_eval, Month)

...

SELECT  [t.DataPoint], [dd.Month]
FROM   date_dim dd
LEFT JOIN Table t
   ON t.[When] = dd.Date_Eval
WHERE
    Date_eval >= DATEADD(MONTH, -7, @CurrentMonth)
    Date_eval < @CurrentMonth

The downside of this approach is that you have to populate a table with data and that if you use a date dimension attribute that can change, such as whether or not employees work a certain day, the table is difficult to maintain automatically.

The upside is that you have a framework to build reports like this on, and one that can be reused as often as you need it.


SQL Server logs "blob data", viewable with journalctl What does it mean when I run journalctl -u mssql-server.service and it says B blob data,

Sep 06 17:34:44 x230 systemd[1]: Stopping Microsoft SQL Server Database Engine...
Sep 06 17:34:44 x230 sqlservr[22239]: [200B blob data]
Sep 06 17:34:44 x230 sqlservr[22239]: [190B blob data]
Sep 06 17:34:51 x230 sqlservr[22239]: [73B blob data]
Sep 06 17:34:51 x230 sqlservr[22239]: [146B blob data]
Sep 06 17:39:51 x230 sqlservr[22239]: [73B blob data]
Sep 06 17:39:51 x230 sqlservr[22239]: [146B blob data]

I'm not running DDL commands either, nor inserting or updating blob data. But it is possible something like mssql-cli is doing that in the background.

This happens when there are unprintable characters in the output.

It seems sqlservr is outputting \r (or ^M) (line feed) characters.

You can view the raw data using: journalctl -u mssql-server.service --all

From what I can see, this appears to be the same content as /var/opt/mssql/log/errorlog, so that might be an easier way to view logs.


SQL Server, Allow remote connections to this server option. What does this do? Can someone clue me in on what this option actually does and what would it affect if I were to suddenly uncheck it in an established production environment?

This checkbox is the GUI way of setting the remote access configuration option,

EXEC sp_configure 'remote access', 0; -- UI checkbox unchecked
EXEC sp_configure 'remote access', 1; -- UI checkbox checked

The feature is labelled incorrectly in the SSMS UI:

Incorrect: Allow remote connections to this server
Correct: Allow remote connections to from this server

and the documentation describes it wrong:

  Allow remote connections to this server

  Controls the execution of stored procedures from remote servers running instances of SQL Server. Selecting this check box has the same effect as setting the sp_configure remote access option to 1. Clearing it prevents execution of stored procedures from a remote server.

It should be:

  Allow remote connections to from this server

  Controls the execution of stored procedures from to remote servers running instances of SQL Server. Selecting this check box has the same effect as setting the sp_configure remote access option to 1. Clearing it prevents execution of stored procedures from to a remote server.

Documentation from BOL 2000

SQL Server 2000 was the last time this feature was documented. Reproduced here for posterity and debugging purposes:

  Configuring Remote Servers

  A remote server configuration allows a client connected to one instance of Microsoft® SQL Server™ to execute a stored procedure on another instance of SQL Server without establishing another connection. The server to which the client is connected accepts the client request and sends the request to the remote server on behalf of the client. The remote server processes the request and returns any results to the original server, which in turn passes those results to the client.

  If you want to set up a server configuration in order to execute stored procedures on another server and do not have existing remote server configurations, use linked servers instead of remote servers. Both stored procedures and distributed queries are allowed against linked servers; however, only stored procedures are allowed against remote servers.

   Note  Support for remote servers is provided for backward compatibility only. New applications that must execute stored procedures against remote instances of SQL Server should use linked servers instead.

Disabling the option prevents outgoing connections

If you try to execute a stored procedure on a remote linked server (i.e. sp_addlinkedserver):

EXECUTE [Hyperion].[SharePoint].[dbo].[GetUnreadDocuments]

it will run fine.

If you then disable remote access on the local server:

EXEC sp_configure 'remote access', 0;, or
uncheck "Allow remote connections to from this server"

the same linked stored procedure will fail:

EXECUTE [Hyperion].[SharePoint].[dbo].[GetUnreadDocuments]

  Msg 7201, Level 17, State 4, Procedure GetUnreadDocuments, Line 1
  Could not execute procedure on remote server 'Hyperion' because SQL Server is not configured for remote access. Ask your system administrator to reconfigure SQL Server to allow remote access.

Short version

Remote access controls outgoing access to remote stored procedures.

And the documentation incorrectly notes that using sp_addlinkedserver avoids the problem:

  This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use sp_addlinkedserver instead.

That is false.

See also

How to execute a stored procedure against linked server?


Is it necessary to delete a database before restoring the backup in MySQL? As a rule, I always drop the database, and then create a new one and only after that restore a dump file. But I'm wondering, is it necessary to do?

For example, if I lose some data and begin to restore my database, why I cannot just to restore it, like in MS SQL, without deleting it first?

Read the first few lines of the dump -- Does it say DROP DATABASE or anything like that?

(There are many options on mysqldump that control whether drops, creates, etc, are emitted into the dump, so there is no single answer to this question.)


How to re-create the ##MS_PolicyEventProcessingLogin## principal I'm getting a bunch of errors in my MS SQL Server logs about a missing ##MS_PolicyEventProcessingLogin## principal. I went to check the master database and yes, it is indeed missing. I don't know how this happened.

How do I recreate this principal?

Any account enclosed in "##" are certificate based accounts and you will not be able to simply create them as you would other logins/users. If you have a backup of the master database prior to the accounts disappearing you could might try restoring the database to get them back. Other than that I suspect you will need to reinstall or try rebuilding the master database.

Read through these two articles (here, here) for rebuilding the system databases. I would suggest taking a backup of the current state of the system databases. Script out all the logins you have in master as you will loose these.


How to populate another column with a sequence value generated for an identity column? I have a table that has a column that is determined by a sequence using the identity column thing, so after a record is inserted (or during, I'm not sure how it works behind the scenes, only that it does) it gets a number one higher than the previous record.

I'm writing a program where there is a condition that leads to a record having that number also be in another column. Is there a way to do this without just making the record and then modifying it immediately afterwards?

Thanks!

Edit: My code:

String insertion = "INSERT INTO DANIEL.UNIQUE_PHYSICIAN "
                    + "(SOURCE_ID, ROOT_ID, SOURCE_TABLE, CONFIDENCE_IS_SAME,"
                    + " IS_ROOT_PHYS, FIRST_NAME, LAST_NAME, POSTAL_CODE) VALUES "
                    + "(" + p.getPhysicianID() + ", " + Integer.toString(root_id) + ", STI.PHYSICIAN, 1, 0, " + p.getFirstName()
                    + ", " + p.getLastName() + ", " + p.getPostalCode() + ");";

and the table:

CREATE TABLE UNIQUE_PHYSICIAN
(
  SOURCE_ID NUMBER
, ROOT_ID NUMBER
, SOURCE_TABLE VARCHAR2(20 BYTE)
, CONFIDENCE_IS_SAME FLOAT(126)
, IS_ROOT_PHYS CHAR(1 BYTE) NOT NULL
, UNIQUE_ID NUMBER NOT NULL
, FIRST_NAME VARCHAR2(20 BYTE)
, LAST_NAME VARCHAR2(20 BYTE)
, POSTAL_CODE VARCHAR2(20 BYTE)
, CONSTRAINT UNIQUE_PHYSICIAN_PK PRIMARY KEY
  (
    UNIQUE_ID
  )
  USING INDEX
  (
      CREATE UNIQUE INDEX UNIQUE_PHYSICIAN_PK ON UNIQUE_PHYSICIAN (UNIQUE_ID ASC)
      LOGGING
      TABLESPACE DATAL03
      PCTFREE 10
      INITRANS 2
      STORAGE
      (
        INITIAL 65536
        MINEXTENTS 1
        MAXEXTENTS UNLIMITED
        BUFFER_POOL DEFAULT
      )
      NOPARALLEL
  )
  ENABLE
)
LOGGING
TABLESPACE DATAL03
PCTFREE 10
INITRANS 1
STORAGE
(
  INITIAL 65536
  MINEXTENTS 1
  MAXEXTENTS UNLIMITED
  BUFFER_POOL DEFAULT
)
NOCOMPRESS
NOPARALLEL;

In this instance, I want to set ROOT_ID to be equal to the UNIQUE_ID assigned to that record.

If you have a condition that specifies 1005 whether root_id has to get the same value as unique_id, you could use a TRIGGER. Something like this:

CREATE OR REPLACE TRIGGER root_unique_trg
    BEFORE INSERT
    ON UNIQUE_PHYSICIAN
    FOR EACH ROW
    WHEN (NEW.is_root_phys = 1)
BEGIN
    :NEW.root_id := :NEW.unique_id ;
END ;

Tested in SQLfiddle


Recover data in a SQL Server table without backups We are having a database without backup to restore. (we didn’t keep backups as client not having enough space in his SQL Server 2012). Unfortunately some data has been lost and we need to recover those. Please provide a way of doing this.

If you have "lost the data" because of corruption, you may be able to retrieve some of the data from nonclustered indexes. Or if the corruption is only in nonclustered indexes you may have lost nothing and can rebuild the indexes to recover the data. Search for DBCC PAGE and you may find advice from some experts on what to do.

If you have "lost the data" because someone has deleted it and you have no backup, then that data is simply gone. It doesn't exist anywhere.

I would recommend your client make an insurance claim.


Problem calling Postgres function I wrote this function:
create or replace function field_summaries(gap interval , _tbl anyelement, _col text)
returns table(hour text, average numeric, minimum numeric, maximum numeric) as
$func$
begin
 RETURN QUERY
  EXECUTE format('select time_bucket($1, time)::text as hour,
                         avg(%I),
                         min(%I),
                         max(%I)
                  from %I d
                  where d in (select device_id from sensors)
                  group by hour', _col, _col, _col, _tbl)
     USING gap; --<< this passes the parameter to the EXECUTE command
END;
$func$
language plpgsql
stable;

to which I pass a table name and a column name.I call it like this :
select field_summaries('5minutes', NULL::m_09, 'current');

which works fine. I need to call this function from my node.js code. so I need to pass a string as an argument. When I pass "Null::m_09" as a string  I get an error. Is there a way to alter the function so that I can call it like this :
select field_summaries('5minutes', m_09, 'current');

Right now I get this when I do it:
ERROR:  column "m_09" does not exist
LINE 1: select field_summaries('5minutes', m_09, 'current');
                                           ^

Any help is much appreciated.

Define the table name parameter as text and then pass a string:
create or replace function field_summaries(gap interval , _tbl text, _col text)
returns table(hour text, average numeric, minimum numeric, maximum numeric) as
$func$
begin
 RETURN QUERY
  EXECUTE format('select time_bucket($1, time)::text as hour,
                         avg(%I),
                         min(%I),
                         max(%I)
                  from %I d
                  where d in (select device_id from sensors)
                  group by hour', _col, _col, _col, _tbl)
     USING gap;
END;
$func$
language plpgsql
stable;

Then call it like this:
select *
from field_summaries('5minutes', 'm_09', 'current');


Can't NoSQL DB ensure consistency if it is residing in a single server? As NoSQL DBs always prefer BASE over ACID.
Everyone always says that you can't have ACID when you are working with a NoSQL DBs.
For example, we have MongoDB (a popular NoSQL DB), which is hosted within a single server. No replicas. If the client app updates a document, strong consistency should take place.
(Just curious, not expert)

Yes, a write to a single instance, distributed database will be immediately consistent.  You likely still won't have ACID, as each NoSQL variant has its own ways of handling (or not) atomicity.
Although, I have to wonder what the goal would be here.  The main feature of a NoSQL database is its ability to support partition tolerance...which is lost in a single instance configuration.  I can't think of many scenarios like that where I wouldn't just run Postgres.


is it possible to populate a primary key of a table based on a column of another table? I have a table that contains a text-type column(for example graph path : 12 5 7 19 30 ..) and I want to split this text by space and put all of the output strings as a primary key in the other table.
Note that the strings found can be repeated so each time I will be adding a new primary key, I will check if it is already there or not.

Sounds like a simple INSERT ... SELECT statement:
insert into target_table (the_pk_column)
select u.key
from regexp_split_to_table('12 5 7 19 30', '\s+') as u(key)
on conflict (the_pk_column) do nothing;

or based on the column of the source table
insert into target_table (the_pk_column)
select u.key
from source_table st
   cross join regexp_split_to_table(st.graph_path, '\s+') as u(key)
where st.id = ... --<< whatever you want to select
on conflict (the_pk_column) do nothing;


Durability and other limitations in mongoDB? I've created web app that use mongodb, my mongo database currently has 4,05 GB , but every day it increase, next year it will probably have about 8 GB ,

I have python scripts that runs every 30 minutes , collect data from Clear Case system and store it in mongodb ,

problem is that server where is web app runned, often crashes and restarts, that cause data corruption in my mongo database, so my web app don't works good , when user type information that will run mongo query that will get corrupcted data.

I solve this problem with backups, every week I make backup and when I notice data corrupcion I replace my mongodb with backup, but this isn't good solution .

I tried repairDatabase , but I get error

{

"errmsg" : "exception: can't map file memory - mongo requires 64 bit build for larger    datasets",

"code" : 10084,

"ok" : 0

}

I'm newbie in mongodb, can someone tell me more about durability in MongoDB, and other limitations in mongoDB, e.g. How will size of mongo database affect to work on my web app , next year size will be at least 8 GB , maybe more ... Anyone has similar problems , how to solve it ....

You seem to be using the 32bit version of mongoDB.

It's a miracle you actually got to 4.05GB, because the 32bit version only supports up to 2GB of data.

Any serious MongoDB deployment should be on a 64bit server with a 64bit operating system running a 64bit build of MongoDB. The 64bit version of MongoDB theoretically allows database of almost unlimited size. In reality, you are of course limited to the storage capactity of your server, but MongoDB can always scale horizontally by creating a shard of multiple servers.

Running 64bit will also allow the database to use journaling (the 32bit version also allows journaling in theory, but it's disabled by default because it further limits the amount of data you can store). With journaling enabled, MongoDB is far less likely to require a repair after a sudden crash.


Is it possible to return a custom error for a logon trigger? I'm trying to get a logon trigger to return a custom error.  Thus far everything I have tried causes it to return the standard error:

My trigger looks like this:

CREATE TRIGGER Test_Logon_Trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN

IF ORIGINAL_LOGIN()= 'login_test'
    BEGIN
        raiserror('Custom Error',25,16)
        --PRINT 'Print'
        --ROLLBACK;
    END
END;

I have tried printing an error message and using a RAISERROR command separately and in combination.  I've also tried using the WITH NOWAIT clause on RAISERROR.  In all cases it just sends the message to the error log.  Does anyone know how to modify the error being returned displayed on the screen?  I'm even ok with returning a second error if necessary.

This behaviour is a security feature to make hacking more difficult, specifically because there is no variation in the output depending on the input. (Aside from exposing the fact there is a logon trigger active.)

If a password is mistyped, the system doesn't respond with an error saying "the password is wrong" or even whether the login name specified exists. If it told you why, this makes penetrating the system much easier because there's less guesswork involved.

So really if there is a way to do this using built-ins, I would have no hesitation to report it as a bug.

That of course doesn't solve what you're trying to do, which, IMO, is questionable for the same reasons.

Since login failures should be rare, what I would suggest is that all the restrictions be published internally in a predictable location (e.g., SharePoint). The main things with this are to make sure the list stays up-to-date (it could be data-driven), and also that it's visible to everyone who has access to the server (as opposed to everyone who is able to attempt to connect to the server, which is very different).


What MySQL storage engine should I choose? I have a database for my catalog webpage. Some tables are about 3GB with millions of rows.

What engine should I choose for my database, InnoDB or MyISAM?

There will only be about 1,000 new records a week, and about 1,000 deletes, but I will modify some data in one table quite often (price, quantity, etc.).  All other tables will be "sealed".

I need indexes to enable fast queries. Which storage engine is best for this scenario?

My recommendations are to start as follows:

Go for the latest version of the MySQL Server that you can get, there have been some pretty impressive work done on 5.6 although the production ready version is 5.5
Go for InnoDB - comes as the default for the higher MySQL versions
Configure InnoDB as follows:

file_per_table - creates an data file for each table instead of putting all tables in a single data files
innodb_buffer_pool_size - 80% of available RAM (if a dedicated machine) so that as much of your database is in memory

Table column characteristics:

use the smallest column size needed for the data
use numeric foreign keys
index the columns used in the searches, and setup the indexes to cover the most common searches, for example if you search on columns 4, then 6 alot create a multi-column index with column 4 first followed by 6

Remember these are just starting points you can fine tune specifics as you move along, but before you fine tune, measure tune then measure again


MariaDB ERROR 1048 GeomFromText returning as null I am trying to restore a databese from an old server onto a new server and my geomertry columns are not importing
example
UPDATE `property_floor` SET
`floorBounds` = ST_GeomFromText('MULTIPOINT((115.823402 -32.064224),(115.823509 -32.079125),(115.843336 -32.064224)')
WHERE `floorID` = '1'

Error in query (1048): Column 'floorBounds' cannot be null

I have tried it both with the SRID and without it. tried both ST_GeomFromText and GeomFromText
I am at a loss for what is wrong, Is this a bug? is my data malformed (looks good to me, but I could be having one of those blind moments) or has something changed?
floorBounds is of type multipoint and the data I am trying to import was exported from a functioning database.
mariaDB version 10.3.25-MariaDB-0+deb10u1
I only just noticed that the old server WAS NOT using mariaDB but MySQL (ver 5.7.31-0ubuntu0.16.04.1) But it should still be compatible, correct?


How to fill/insert fake data into a MS Sql Server I'm trying to test out some configuration settings for an MS Sql Server FILEGROUP and to do this I wish to fill my FILEGROUPdisk up so it will start populating the 2nd FILEGROUP.

I've just created a dummy table that on the PRIMARY FILEGROUP and have the following simple sql to insert some data. It's just taking so long and filling it slowly. Are there any better ways to quickly fill a table with junk data that really uses up a lot of hard disk space?

--IF OBJECT_ID('dbo.__RandomData', 'U') IS NOT NULL
--BEGIN
--    DROP TABLE dbo.__RandomData;

--    CREATE TABLE dbo.__RandomData (
--        RowId INT IDENTITY(1,1) NOT NULL,
--        SomeInt INT,
--        SomeBit BIT,
--        SomeVarchar VARCHAR(10),
--        SomeDateTime DATETIME,
--        SomeNumeric NUMERIC(16,2) )
--END
--GO

DECLARE @count INT
SET @count = 1

WHILE @count <= 1000000
BEGIN
INSERT INTO dbo.__RandomData
    SELECT    @count,
            CASE WHEN DATEPART(MILLISECOND, GETDATE()) >= 500 THEN 0 ELSE 1 END [SomeBit],
            CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) +
            CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) +
            CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) +
            CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) +
            CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) [SomeVarchar],
            DATEADD(MILLISECOND, (ABS(CHECKSUM(NEWID())) % 6000) * -1,
                DATEADD(MINUTE, (ABS(CHECKSUM(NEWID())) % 1000000) * -1, GETDATE())) [SomeDateTime],
            (ABS(CHECKSUM(NEWID())) % 100001) + ((ABS(CHECKSUM(NEWID())) % 100001) * 0.00001) [SomeNumeric]
    SET @count += 1
END

Do a CROSS JOIN between sys.columns, use that same 2, 3 , or 4 times. Have a TOP, with as many rows as you want. Use that as a base for INSERT ... SELECT.

CREATE TABLE mytable(c1 int identity(1, 1), c2 char(10))

INSERT INTO mytable
SELECT TOP(1000) 'Hello'
FROM sys.columns AS a
 CROSS JOIN sys.columns AS b
 CROSS JOIN sys.columns AS c

The cross join is a classic trick. Do a cross join between two tables that has 1000 rows each and you have 1,000,000 rows in the result. With 3 tables, you have 1,000,000,000 rows. You can have some "numbers" table you create for these purposes, with for instance 1000 rows in it. Or, do a quick and dirty and use some view that you know exists with a decent amount or rows in it (like sys.columns).


Subquery returned more than 1 value. This is not permitted + Trigger Error SQL Server This trigger will update Is_Confidential_or_VIP column to 1 or 0 based on the text inserted or updated into ConfidentialRegistrationFlag and VIPType columns :

create trigger [dbo].[Is_Confidential_or_VIP]
on  [EDB].[dbo].[Patient_Info]
after update,insert
AS
BEGIN
    declare @type1 nvarchar(max)
    declare @type2 nvarchar(max)

    (select @type1 = ConfidentialRegistrationFlag from inserted)
    (select  @type2 = VIPType from inserted)

    declare @flag int

    if (@type2 like 'MDP-A' or @type2 like 'MDP-B' or @type1= 'confidential')
       set @flag= 1
    else
       set @flag= 0

    update EDB.dbo.Patient_Info
    set Is_Confidential_or_VIP = @flag
    where EPN = (select EPN from inserted)
 END

I have a problem when enabling this trigger cannot insert data and error msg:

  Subquery returned more than 1 value

Actually Triggers fire once rather than multiple times if update statement modify more than one row.

Your trigger trying to update multiple rows which means Inserted table will also have more than one row and when you try to set Flag values, it return you error.

Join your table's key with Inserted

CREATE TRIGGER [dbo].[Is_Confidential_or_VIP] ON [EDB].[dbo].[Patient_Info]
    AFTER UPDATE, INSERT
AS
    BEGIN

        UPDATE  patinfo
        SET     Is_Confidential_or_VIP = CASE WHEN VIPType = 'MDP-A'  --- 'Like' Replaced with '='
                                                   OR VIPType = 'MDP-B'
                                                   OR ConfidentialRegistrationFlag = 'confidential'
                                              THEN 1
                                              ELSE 0
                                         END
         -- select case VIPType = 'MDP-A' OR VIPType = 'MDP-B'  OR ConfidentialRegistrationFlag = 'confidential' then 1 else 1  END,*
        FROM    EDB.dbo.Patient_Info patinfo
                INNER JOIN inserted i ON patinfo.EPN = i.EPN

    END


To find a version for your pc check with the software publisher install mongodb I want to install MongoDb on windows 8 but when i try to run C:\mongodb\bin\mongod.exeiget this error:

To find a version for your pc check with the software publisher.

Which version did you download and install?  If you have 64-bit Windows 8, you should download and install the build marked with "2008R2+".  Their download page indicates that:

  The Windows 2008+ build uses newer features of Windows to enhance
  performance. Use this build if you are running with 64-bit Windows
  Server 2008 R2, Windows 7, or greater.

If you are using 32-bit Windows 8, you should download the 32-bit Windows version.  Of course the 32-bit version has some limitations, such as a max database size of 2GB.


Tuning queries and indexes using index analysis tool I've been pointed to this script from several places which apparently helps with designing indexes in Microsoft SQL Server: http://indexanalysis.codeplex.com/

The problem is I'm using Sybase ASA 11, which seems to use a slightly different implementation of T-SQL than the one used by MS. I'm getting all sorts of errors when I try to run the script.

I know enough about database tuning to tell where to put indexes, and that's about it. I'm a programmer, so I don't know about it as much as I'd like to.

My question is: Is there anything similar to this tool for Sybase? I need to know where to put indexes without going to each and every query (if I can avoid it).

Sybase 3rd party tools are sparse unless you spend money it seems.

The usual vendor suspects have Sybase offerings:

Embarcadero's DB PowerStudio for Sybase ASE which claims to have index analysis (+ lots of other stuff)
Quest have some tools but not sure what you would be best


DB2 simple order by query I am very new to DB2.have installed Express C version and facing some problem while running a simple query.

SELECT A,B,C,D,E,F,G, H  FROM TABLE_NAME
 where A=9999  ORDER BY  D

Note: A through H are the only columns in the table.
When I do this, I get this exception:

  DB2 SQL error: SQLCODE: -1585, SQLSTATE: 54048, SQLERRMC: null
   Message: A temporary table could not be created because there is no available system temporary table space that has a compatible page size.

More exceptions ...

  DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-1585;54048;

This is just a simple query with where and order by clause. Why am I getting this error?

The ORDER BY, particularly in the absence of an index on column D, will require a sort operation. If there is not enough memory allocated in the sort heap for the statement, it will cause an overflow of data being sorted into a temporary table. The table width will be the sum of the column widths of the result set being sorted, plus some overhead.

There must be a system temporary tablespace with the page size appropriate to accomodate the temporary table width, and the error says that there is no such tablespace.

To create it you could run the following statement, as an example (assuming you use automatic storage in your database):

create system temporary tablespace TMPSYS32 pagesize 32 k

It may also happen that you don't have a bufferpool with that page size (each tablespace must have a bufferpool with matching page size). If so, you will need to create the bufferpool first:

create bufferpool BP32 pagesize 32 k
create system temporary tablespace TMPSYS32 pagesize 32 k bufferpool BP32


Delayed availability of historical data when using CDC I am using the SQL Change Data Capture feature to retrieve historical data from my tables.

During testing, I noticed that when I insert/update a row and query the change data right afterwards (see my earlier question on how I do that), the result will turn up empty.

I played around with it and noticed that if I wait 10 seconds between the data manipulation and the history query, the history will be up to date.

I am aware that CDC functionality is based on the transaction logs somehow and that this probably is the reason why there is a delay between data changes and the cdc history table being updated.

Now my question:

Is this delay somewhere explained in the CDC documentation? I could not find any mention of it.
Is there a way to manually make sure that the history table is up to date before I query it? (somehow triggering CDC to check the logs?)
How does CDC work together with transactions? Will I for example be able to query historical data that I have overwritten in the same transaction? Or does the transaction first have to commit for the cdc data to become availalbe?

CDC relies on a SQL Server Agent job to capture information from the transaction log. You can customize the job parameters by updating msdb.dbo.cdc_jobs - there are multiple properties as described in this Books Online topic:

maxtrans
maxscans
continuous
pollinginterval

That doc has some information about these parameters, as does this white paper.

But really, CDC is not meant to be a real-time system, and you should use caution adjusting these parameters too much to make it so. Most people acknowledge that you need to wait to let CDC catch up (see here and here). If 5-10 seconds is not fast enough for you, you might be using the wrong technology.

As for the separate transaction question, this is something you could test, no?


Changing the default size and AutoGrowth settings for new SQL Server Databases Our technicians regularly go to client sites to fix EPOS tills. Sometimes they create a new database and import the objects and data via a sync.

Ideally, I'd like to force all new SQL Server 2012 databases to default to our particular initial size and autogrowth settings, not the 4Mb out of the box. The sync routines are executables so we can't rewrite them to apply ALTER DATABASE and our technicians aren't always proficient in SQL Server.

Is there any way to reconfigure the new database defaults? I thought there could be something by tweaking the system tables but can't get anywhere.

For lack of a better description. Model is copied to create new databases. This ensure they have the same collation, size, data growth settings, and any other  parameter you want to set.


How can I display 9 rows of results in a 3x3 grid? I have a report that gathers data to be printed on labels, we want to print up to 9 labels per page, on a 3x3 grid.

Am I able to use something like groupings to get this layout generated automatically, or will I need to hard-code each of the 9 labels individually? I would like to be able to do it automatically so that I can re-purpose it for other label sizes.

I haven't tried anything yet, as I really can't get my head around how to do it.
I am using SSRS 2012, I am relatively new to it and SE, so I apologize if I am lacking any crucial information.

Example of the input data, regarding the favorite things of totally real people:

The result I would like:

Well, you can do something similar with SSRS.

First you have to map your dataset to a list. Inside the list you insert some text boxes and format them acording to the layout you desire.

Now in the report properties you configure to have three columns.

The tricky part is to adjust the widths and margins so the objects fit nicely in your page. For a letter size page (8.5 x 11 in) the list object should have a width less than 2.83 inches and a height of less than 3.66 inches to give space for the page and column margins.

In the preview check the print layout. You should see something like this:

The order is not exactly what you asked for, but if this is to print labels I guess it wont matter.


Optimal Table Structure We have a parent table, Journal and a child table, Stops.

A Journal can have X amount of Stops.

For a good 80% of our SQL queries, our customers are just interested in the first and last Stop for each Journal.

We often have queries that do something like this:

SELECT
      MINStop.PhysicalCity AS MinStopCity,
      MAXStop.PhysicalCity AS MaxStopCity,
      MINMax.DocNo AS JournalDocNo
FROM (
    SELECT
        MIN(Stops.StopsPkey) AS MINStopPkey,
        MAX(Stops.StopsPkey) AS MAXStopPkey,
        Stops.JournalPkey,
        Journal.DocNo
    FROM Journal
    LEFT OUTER JOIN Stops ON Journal.JournalPkey=Stops.JournalPkey
    WHERE Journal.Type=150
    GROUP BY Stops.JournalPkey, Journal.DocNo) MINMax
LEFT OUTER JOIN Stops MINStop ON MINStop.StopsPkey=MINMax.MINStopPkey
LEFT OUTER JOIN Stops MAXStop ON MAXStop.StopsPkey=MINMax.MAXStopPkey

It works fine, but definitely suffers at the larger locations.

It's just how it's always been done here, and I wanted to reach out for possible improvements. Would it be worth it to store the first and last Stop data in the Journal table, even if that data is duplicated?

We use Advantage Database 11.x so are a bit limited on the SQL features. Probably our only route would be through table modifications.

Here is a sketch for an Incremental Evaluation System (IES):

CREATE TABLE stops_min_max
( JournalPkey ... not null primary key
, MINStopPkey ... not null
, MAXStopPkey ... not null
);

CREATE TRIGGER ...
AFTER INSERT ON stops
BEGIN
        MERGE INTO stops_mean_max x
        USING (
            VALUES (__new.JournalPkey, __new.StopPkey, __new.StopPkey)
        ) y (JournalPkey, MINStopPkey, MAXStopPkey)
                ON x.JournalPkey = y.JournalPkey
        WHEN MATCHED THEN
            UPDATE SET (MINStopPkey, MAXStopPkey)
                     = (LEAST(x.MINStopPkey, y.MINStopPkey)
                       ,GREATEST(x.MAXStopPkey, y.MAXStopPkey))
        WHEN NOT MATCHED THEN
            INSERT (JournalPkey, MINStopPkey, MAXStopPkey)
            VALUES (y.JournalPkey, y.MINStopPkey, y.MAXStopPkey);

Triggers for update and delete should be added

Instead of looking for min, max in stops, they can be found in stops_min_max

Since I have no idea what your tables look like, and I don't have access to the DBMS you are using, a sketch is the best I can do.


Select starting node ID in CTE I have following table:

create table osoba(
id int primary key,
idNd int,
nazwim nvarchar(500),
)

Data looks like below:

id  idNd    nazwim
1   NULL    grandpa
2   1   child1
3   1   child2
4   2   grandchild1child1
5   2   grandchild2child1
6   3   grandchild1child2
7   3   grandchild2child2

I've got following cte:

;WITH ret AS(
       SELECT   *
    FROM    osoba
    --WHERE   ID = 1
    UNION ALL
    SELECT o.*
    FROM    osoba o INNER JOIN
            ret r ON o.idNd = r.ID)

Select * from ret returns all descendants for each node and starting node:

id  idNd    nazwim
1   NULL    grandpa
2   1   child1
3   1   child2
4   2   grandchild1child1
5   2   grandchild2child1
6   3   grandchild1child2
7   3   grandchild2child2
6   3   grandchild1child2
7   3   grandchild2child2
4   2   grandchild1child1
5   2   grandchild2child1
2   1   child1
3   1   child2
6   3   grandchild1child2
7   3   grandchild2child2
4   2   grandchild1child1
5   2   grandchild2child1

How to retrieve information about starting node for each record? I would like to get result like that:

idStartingNode  id  idNd    nazwim
1   1   NULL    grandpa
1   2   1   child1
1   3   1   child2
1   4   2   grandchild1child1
1   5   2   grandchild2child1
1   6   3   grandchild1child2
1   7   3   grandchild2child2
3   6   3   grandchild1child2
3   7   3   grandchild2child2
2   4   2   grandchild1child1
2   5   2   grandchild2child1
2   2   1   child1
3   3   1   child2
6   6   3   grandchild1child2
7   7   3   grandchild2child2
4   4   2   grandchild1child1
5   5   2   grandchild2child1

You add the column, in both parts of the recursive CTE.

Starting nodes have the ID as startingNode id.

The descendants have as starting node id, the starting node id of their parent:

WITH ret AS
  (
    SELECT id AS idStartingNode, o.*
    FROM    osoba o
    --WHERE   ID = 1
    UNION ALL
    SELECT r.idStartingNode, o.*
    FROM    osoba o INNER JOIN
            ret r ON o.idNd = r.ID
  ) ;


Need a function to run different queries based on parameters sent subquery must return one column error I am new to advanced sql programming. I am storing constants.
Constants have three possible scopes: local, user and global.

Two tables:

s0constants_user holds set values of constants

s0constants holds the constant definitions and values.

 CREATE TABLE public.s0constants_user (
constant_name bpchar(15) NOT NULL,
empfkey int2 NOT NULL DEFAULT 0,
constant_value bpchar(255) NULL,
locality_name bpchar(30) NOT NULL DEFAULT 1,
CONSTRAINT s0constants_user_pk PRIMARY KEY (constant_name, empfkey, locality_name)

);

   CREATE TABLE public.s0constants (
   constant_name bpchar(15) NOT NULL,
   constant_value bpchar(255) NULL,
   constant_data_type bpchar(1) NULL,
   cttype int4 NULL,
   displayname bpchar(30) NULL,
   actiontype int4 NULL,
   note text NULL,
   const_scopefk int4 NOT NULL DEFAULT 3, -- 1=local 2- user 3=global
   CONSTRAINT "S0CONSTANTS_pkey" PRIMARY KEY (constant_name),
   CONSTRAINT scope_fk FOREIGN KEY (const_scopefk) REFERENCES
   s0constants_scopelk(id)

I realize I could do this with separate tables for each scope but I'd like to make this work even if it is just a learning exercise.

I need a function to return and constant's value and data type given the constant name and empfkey.  There are different queries for each scope of constant.

local - constant_name,
empfkey,
locality_name user - constant_name,
empfkey global - constant_name

Constant names are unique over the three scopes.  I thought a case statement might do it but I'm having trouble limiting it to one row of data and how to set up the sub-queries.
This is my first(Edit SECOND) pass at this but now I have issues with "subquery must return only one column"

    FUNCTION public.const_get( stname text, empid INT, locality text ) returns TABLE(cvalue text, ctype text)
    LANGUAGE sql
     STABLE PARALLEL SAFE
AS $$

   select c.const_scopefk, c.constant_data_type
     ,case
       when const_scopefk = 1 then --local
       ( select distinct scu.constant_value, c.constant_data_type
        from s0constants_user scu

        where (scu.constant_name = constant_name
           and scu.empfkey = empid
           and scu.locality_name = locality)
       limit 1)

       when const_scopefk  = 2 then --user
        (select scu.constant_value, c.constant_data_type
        from s0constants_user scu

        where (scu.constant_name = constant_name
           and scu.empfkey = empid
           and scu.locality_name = 1)
           limit 1)

      when const_scopefk  = 1 then --global
        (select scu.constant_value, c.constant_data_type
        from s0constants_user scu

        where (scu.constant_name = constant_name
         and scu.empfkey = 0
        and scu.locality_name = 1)
        limit 1 )
    end
   from public.s0constants c
   where  c.constant_name = UPPER(constname);

$$

EDIT: I've updated table definitions

So I finally got this to work with this function:

returns table(  ctype int, cvalue char )
    LANGUAGE sql
     STABLE PARALLEL SAFE
AS $$

   select c.cttype,
     case
       when const_scopefk = 1 then --local
       ( select scu.constant_value
        from s0constants_user scu

        where (scu.constant_name = c.constant_name
           and scu.empfkey = empid
           and scu.locality_name = locality)
       limit 1)

       when const_scopefk  = 2 then --user
        (select scu.constant_value
        from s0constants_user scu

        where (scu.constant_name = c.constant_name
           and scu.empfkey = empid
           and scu.locality_name = '1')
           limit 1)

      when const_scopefk  = 1 then --global
        ( select scu.constant_value
        from s0constants_user scu

        where (scu.constant_name = c.constant_name
         and scu.empfkey = 0
        and scu.locality_name = '1')
        limit 1 )
    end
   from public.s0constants c
   where  c.constant_name = UPPER(stname);

$$;````


How are derived table implemented in MySQL When MySQL makes a derived table as a result of

SELECT (SELECT ...) etc
or SELECT * FROM a JOIN (SELECT * from B) etc
or SELECT * FROM (SELECT ...)

Are these derived temporary tables created in-memory or the file-system? Does it depend on the size of the derived table?

This is tricky question, specially since 5.6, in which the optimizer has changed the way subqueries are executed. Specially, having into account that the definition of "derived table" may change person to person

From 4.1 to 5.5, in many cases, either a temporary table would be created or a cross join will be done between tables, filtering later. In some cases, like the infamous SELECT ... IN (SELECT ...) a derived table will not be created, and it will be executed as a correlated subquery (one row at a time). See the examples here: http://forums.mysql.com/read.php?115,576862,576999#msg-576999

On that same thread, Roy Liseng comment the great subquery improvements of 5.6: http://forums.mysql.com/read.php?115,576862,577215#msg-577215 You can see a summary of the improvements on the 5.6 subquery optimization on the manual, including the creation of indexes on materialization, or its conversion into semi-joins. More details on some of the Oracle engineer blogs.

I think that I had to mention this because there are a lot of things going on on the optimizer, and not always a temporary table is created first "as is".

Regarding the other question, MySQL always tries to create internal temporary tables on MEMORY first, but failsback to disk if they are too large (larger than min(tmp_table_size,  max_heap_table_size) or some other restrictions (like having BLOBS).


MariaDB Uuid as Primary Key Performance Problem / Tweaks im upgrading our mariadb database from PRIMARY KEY INT AUTO_INCREMENT to UUID PRIMARY KEY. We have around 80 tables with multiple relations between them. I've build a migration script to change the id field to BINARY(16) and created for every row a Uuid v4. Then i've updated the relations with the new uuid (also as BINARY(16)). When i run multiple queries to do a performance benchmark, i have an average performance decrease from 15% on my local docker environment.
The id field is a PRIMARY KEY. The relation fields are marked as index and have Foreign Key constraints.
What can i do to improve the performance?
Most of the tables has round about 200k entries. The whole database has a size of 3.2GB

UUIDs are not nice when it comes to performance.  In particular, there is no relationship between the 'next' row you fetch via a UUID and all the other rows that are currently cached.
Docker possibly has a "small" value for innodb_buffer_pool_size, thereby leading to I/O when random accesses into the table happen.
UUID v1 can have its bits shuffled to make them "chronological", would remove most of the "randomness" of accesses.  But you are using v4.
Plan A:  Abandon UUIDs.
Plan B:  Use UUID v1 and shuffle the bits
Plan C:  Give docker enough room to have a big buffer_pool so that most of the indexes can be kept in RAM.
More:  http://mysql.rjweb.org/doc.php/uuid


Syntax error while creating DB2 Trigger What is wrong in the below trigger syntax, I am getting this error An unexpected token "INT" was found following " DECLARE NULL_ROW_ID".  Expected tokens may include:  "END-OF-STATEMENT".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.68.61

CREATE TABLE A (
    A_ID INT NOT NUll,
    A_CODE VARCHAR(100) NOT NULL,
    PRIMARY KEY(A_ID)
);

CREATE TABLE B (
    B_ID INT NOT NUll,
    B_CODE VARCHAR(100) NOT NULL,
    A_ID INT,
    PRIMARY KEY (B_ID),
    FOREIGN KEY (A_ID) REFERENCES A (A_ID)
);

CREATE OR REPLACE TRIGGER B_TRIGGER
AFTER UPDATE OF A_ID ON B
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
    WHEN (N.A_ID IS NULL)
    BEGIN ATOMIC
        DECLARE NULL_ROW_ID INT;
        SET NULL_ROW_ID = (SELECT A_ID FROM A WHERE A_CODE = 'Custom Null String');
        IF NULL_ROW_ID IS NULL THEN
            SIGNAL SQLSTATE '70000' SET MESSAGE_TEXT = 'Custom Null String row not found.';
        END IF;
    SET N.A_ID = NULL_ROW_ID;
    END
@

It's on DB2 Express-C 10.5

I agree with what others have said, it is most likely a problem with the statement terminator. Try adding:

--#SET TERMINATOR @
CREATE OR REPLACE TRIGGER B_TRIGGER
[...]
@
--#SET TERMINATOR ;

You might also consider using for example @ as your statement terminator globally. Another "undocumented" feature is to quote the ; in the trigger using to ending dashes. Example:

CREATE OR REPLACE TRIGGER B_TRIGGER
AFTER UPDATE OF A_ID ON B
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
    WHEN (N.A_ID IS NULL)
    BEGIN ATOMIC
        DECLARE NULL_ROW_ID INT; --
        SET NULL_ROW_ID = (SELECT A_ID FROM A WHERE A_CODE = 'Custom Null String'); --
        IF NULL_ROW_ID IS NULL THEN
            SIGNAL SQLSTATE '70000' SET MESSAGE_TEXT = 'Custom Null String row not found.'; --
        END IF; --
        SET N.A_ID = NULL_ROW_ID; --
    END ;

Personally I prefer to use - say - @ globally and change the environment to adjust for that


Storing images in MySQL db -- type? Possible Duplicate:
  Is it better to store images in a blob or just the url?

I need to recover images from an XML file stored on a remote server and save it to my database. Not sure how I should do so though, what type of field should the image be stored in?

Thanks!

You can store your images as blobs, but in practice you're usually better off storing them in the file system and just keeping the paths to the images in the database.

See: MySQL Docs: BLOB datatype


how can i write the max value of different consecutive values in the new column by length i have a data frame as "a".
a:
[,1] 1  2  3  0 1 2 0 [,2] 0 0 1 2 3 0 1 [,3] 1 2 3 4 5 0 0 and dim(a)=7*3

i want to create a new column including maximum value for each set of consecutive numbers till 0. and calculate for next set till next zero. like this:
result:
[,1] 3 3  3  0 2 2 0 [,2] 0 0 3 3 3 0 1 [,3] 5 5 5 5 5 0 0

unfortunatly i couldn't put the picture of my data frame(a).
please help me to write the right function

If you are doing a difference between first column ([1,]) and d1 you will get something like this:

SELECT id,d1
   ,id-d1 as diff_d1
FROM Table1

output:

id  d1  diff_d1
1   1   0
2   2   0
3   3   0
4   0   4
5   1   4
6   2   4
7   0   7

Doing a max on [d1] and grouping by [diff_d1] column you will get : here

maxD1
3
2
0

Putting all together

;WITH cteSource AS
(SELECT
   id,
   d1,d2,d3
   ,MAX(d1) OVER(PARTITION BY id-d1 ORDER BY id
             ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING)as maxD1
   ,MAX(d2) OVER(PARTITION BY id-d2 ORDER BY id
             ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING)as maxD2
   ,MAX(d3) OVER(PARTITION BY id-d3 ORDER BY id
             ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING)as maxD3
FROM Table1
)
SELECT id
     ,CASE WHEN d1<> 0 THEN maxD1
           ELSE 0  END AS d1
    ,CASE WHEN d2 <>  0 THEN maxD2
           ELSE 0  END AS d2
    ,CASE WHEN d3 <>  0 THEN maxD3
           ELSE 0  END AS d3
FROM cteSource

Output:

id  d1  d2  d3
1   3   0   5
2   3   0   5
3   3   3   5
4   0   3   5
5   2   3   5
6   2   0   0
7   0   1   0

dbfiddle


Merge sub-queries to a single query I am trying to merge the nested MYSQL queries into one

First of all I fire this query

select names , city , category from names_data group by names , city ,category ;

this produces a result of ~28000 from master names_data table

now for each entry in the result
I fire this query inside a python loop

select date, name, city , old_mul, new_mul from name_changes where name=' + name + ' and city=' + city + """' order by date desc limit 2 ;

I just want the last 2 records date wise for each name and city combination from this name_changes table

I have tried few JOINS and groups operation but was not able to achieve what this

Right Now I have 1 + 28000*1 queries

I want to reduce the queries

Help is really appreciated :)

p.s. the tables are different

This should work:

-- Query every name_changes where rownum <= per group limit (2)
select * from (
    -- subquery for attaching rownumber counter for `name:city` groups
    select `date`, name, city , old_mul, new_mul,
        case when @lag != concat(name, ':', city) then @rownum:=1 else @rownum:=@rownum+1 end as rownum,
        @lag:=concat(name, ':', city)
    from name_changes,
    -- init values
    (select @rownum:=0) init_rownum,
    (select @lag:=null) init_la
    order by name, city, d desc
) numbered
where rownum <= 2;

I had a previous answer about mimicking window functions in mysql: How do I write this query? where I explained this in more details.


Simple SQL queries not finishing I don't quite know how to formulate the question because we don't know much yet, but I would like to ask sooner than later because this looks like something that should not be neglected.

This week we started having issues with our database server. It appears to be a data consistency problem and it manifests by timeouts even on very simple queries and small tables. We "fixed" the problem by restarting the server earlier this week and it went away, but now it seems that it is coming back and this time on more crucial tables. For instance, I just did some investigation and I'm looking at a query like this:

SELECT * FROM table WHERE id = 1234

for a particular ID. The table has about 30+ million rows. But it seems that it happens only for a small fraction of records. I bet when I restart the server or backup and restore the database on another server, it will all be fine. But I will try.

At this point, I'm running:

DBCC CHECKTABLE ('table', NOINDEX)

but it seems it's going to run forever. When we hit the issues the first time, I checked the offending table and it was fine. This new table is much bigger.

Some background technical information:

SQL Server 2008 R2, Windows Server 2008 R2
AWS/EC2, m2.2xlarge, 32 GB RAM, 4 x 1TB RAID 0
almost no disk IO, it seems that most of the db is in memory
total db size: 100GB

The ELB volumes are "brand" new. We created them this week.

Edit: I just used the following command:

SELECT
    sqltext.TEXT,
    req.session_id,
    req.blocking_session_id,
    req.wait_type,
    req.wait_time,
    req.last_wait_type,
    req.wait_resource,
    req.open_transaction_count,
    req.transaction_id,
    req.total_elapsed_time
FROM
    sys.dm_exec_requests req
CROSS APPLY
    sys.dm_exec_sql_text(req.sql_handle) AS sqltext

and found that my query it waiting for a shared lock (LCK_M_S) where the blocking session is waiting for another shared lock blocked by a session that does not exist.

Edit 2: OK, the session exists (I found it using sys.dm_exec_sessions), but it does not appear to do anything now.

Edit 3: I can't find anything interesting about the session. I see what web server it comes from, but not much else.

Edit 4: Edit 4: We found a possible bug in our code: a function that was not making sure a database connection is closed. On the other hand, it looked like the transaction the function was using used to be correctly disposed in which case all locks should have been cleared. It's still not very clear to me, but it seems to be the likely reason. We are going to fix the bug and and keep an eye on it.

For me this problem was caused by a database connection left open by Visual Studio paused in debug mode.  Stopping the debug process allowed the query to complete immediately.


Need for backing up MySQL databases information_schema, performance_schema, mysql and phpmyadmin? I know it's not necessary to backup the information_schema database, but what about the following databases (besides the user-defined databases)?

performance_schema
mysql
phpmyadmin

(So, in case of problems, and we've to start from scratch (fresh MySQL-install), which databases do I've to place back)?)

performance_schema: Not necessary. It may contain performance metrics that are only good for the time you were using the instrumentation. It would serve no purpose to load collected metrics from the past into another MySQL instance or into the same MySQL instance at a much later time.
mysql : BE VERY CAREFUL HERE !!! If you backup mysql, you can only reload it into the same version of MySQL it came from. In particular, the mysql.user is neither forward nor backward compatible. See my old posts:

Oct 10, 2014 : MySQL service stops after trying to grant privileges to a user
Dec 24, 2012 :Backup and restore "mysql" database
Apr 12, 2012 : Cannot GRANT privileges as root (I give suggestions on how to backup GRANTS as SQL text with passwords encrypted)
If you have the slow log or general log enabled to write to the tables mysql.slow_log or mysql.general_log (See my old post How to enable MySQL general log?), such a backup could be huge. The entries may also be too old to want to restore. If you wish back the mysql schema, you should mysqldump it separate from user-defined databases.

phpmyadmin : If you wish to reload a backup of phpmyadmin into the same MySQL instance it came from, that would be fine.


how to get weekly unbound retention from bigquery? I Need to get the weekly retention users.
If user has made the transaction in week 4, that user is there in Week 0, week 1, week 2, week 3, week 4.
If User made transaction in week 0, and week 3 then user will be there in Week 0, week 1, week 2, week 3
Thanks @bbaird,
I am updating the question with precise information and question
Here is my table data

TxnId
userid
transactedWeek

T1
U1
0

T2
U2
0

T3
U3
0

T4
U4
0

T5
U1
0

T6
U2
0

T7
U6
0

T8
U1
1

T9
U8
1

T10
U12
1

T11
U3
1

T12
U5
1

T13
U9
2

T14
U15
2

T15
U20
3

T16
U15
3

T17
U15
3

T18
U8
3

T19
U9
4

T20
U20
5

The Result I am looking for

Week
active_users_count

0
11

1
8

2
4

3
4

4
2

5
1

Users count is kinda cumulative but unique users.
For example if user-1 has performed transaction 3 times in week-0, then count for week-0 is 1
If User-1 has performed 3 transactions in week-0, 2 transaction in week-2, 1 transaction in week-3,
then count will be like this
week-0 -> 1  (i.e. he has performed transaction >= week 0)
week-1 -> 1  (i.e. he has performed transaction >= week 1)
week-2 -> 1  (i.e. he has performed transaction >= week 2)
week-3 -> 1  (i.e. he has performed transaction >= week 3)
There can be any number of weeks. It is not fixed to 4 weeks or 8 weeks.

This should be a relatively straightforward aggregation by Week, but you will need to join all records greater than or equal to the current week and take your distinct count from there.
However, that's going to be a lot of rows for Bigquery to work through, so this will probably work better for the platform:
SELECT
  TransactedWeek AS Week
 ,SUM(SUM(LastWeekInd))
    OVER
      (
        PARTITION BY
          1
        ORDER BY
          TransactedWeek
        ROWS BETWEEN
          CURRENT ROW
            AND
          UNBOUNDED FOLLOWING
      ) AS active_users_count
FROM
  (
    SELECT DISTINCT
      UserId
     ,TransactedWeek
     ,CASE
        WHEN TransactedWeek = MAX(TransactedWeek) OVER (PARTITION BY UserId) THEN 1
        ELSE 0
      END AS LastWeekInd
    FROM
      <Your Table>
  ) x
GROUP BY
  TransactedWeek

You can add logic to limit the ranges in the inner query.
Note: if you have any weeks missing from your data, they will be missing in your final result (although the counts will still be accurate).  If you have weeks with no transactions, you may need to generate a list of weeks and left join these results to that.


Oracle 11gR2 (11.2.0.4) RMAN cold backup | Media recovery I'm planning to setup a test server with production data. The intended actions are, making a cold backup & restoring it in the Test server.

The cold backup routine is

run
{
shutdown immediate;
startup mount;
backup database;
}

As I am restoring a cold backup, should be bothered about losing some data? The database is on Archive logging mode. Should I do a recover media as part of the restore process?

SHUTDOWN

  IMMEDIATE

  Does not wait for current calls to complete or users to disconnect
  from the database.

  Further connects are prohibited. The database is closed and
  dismounted. The instance is shutdown and no instance recovery is
  required on the next database startup.

SHUTDOWN

  ABORT

  Performs an inconsistent shutdown of the target instance, with
  the following consequences:

  All current client SQL statements are
  immediately terminated.

  Uncommitted transactions are not rolled back until next startup.

  All connected users are disconnected.

  Instance recovery is performed on the database at next startup.

  IMMEDIATE

  Performs an immediate, consistent shutdown of the target
  database, with the following consequences:

  Current client SQL statements being processed by the database are allowed to complete.

  Uncommitted transactions are rolled back.

  All connected users are disconnected.

A cold backup is consistent already, requires no further recovery. All you need to do is open the restored database with:

alter database open resetlogs;

You will have your database as it existed when you took the cold backup. If you need a state newer than that, only then you need to recover further the restored database.


Implementing Feature max limit (Logical) so this is a more of a logical question.

I'm having trouble deciding on what I will use on implementing limit, let's say for example ticket limit where I have 2 plans, one is standard which have 100 ticket limit, and premium which have 500 ticket limit.

Which of these two is better for implementing such logic? I have 2 ideas:

A. Just count the number of unique tickets in the database for a specific user, and if it reached 100 or 500, implement a method that will restrict further ticket opening.

B. Create a table, which will have the user ID, and a column which will have an upper limit of 100 or 500 depending on the plan availed of the user. Then, each opening of ticket, will subtract from the upper limit in the table. And if the record reached 0, implement a method which will restrict further opening of ticket.

Or if you have any other ideas, I will be glad to have it

Let's analyze how much effort COUNT(*) will take...

For those numbers, either way will probably work.  Counting 500 rows is likely to take only a fraction of a second.  If you don't need to do that a million times a day, you probably won't run out of performance.

Either approach requires performing some form of test every time the user takes a ticket, so this does not seem to be a limiting factor.


Should I use separate address dimensions? I have a Star Schema model with two Dimensions (User and Store) each of them have addresses, which I store.

Each User and Store can just have one Address. Is it recommended to split the Dimension into a Address Dimension (with a 1:1 relationship)? Are there any technical arguments to handle like this? Or is it a common way to keep the address in the user/store dimension?

You should add the address to the user/store dimension as the address is a property of the dimension member, not the fact record.

Adding the address to the dimension allows you to define attribute relationships between country -> city -> user which should improve performance when you create hierarchies using those attributes.

It will usually also allow for easier calculated measures.

As a last point, having the address as attributes on the dimension will allow for easier handling of slowly changing dimensions if a user/store moves to a new address.


Triggers and the Transaction We recently made a change to create constraints based on logic previously used in stored procedures, and part of that included the use of INSTEAD OF triggers to centralize logic.

The logic is generally simplistic:

The stored procedure contains an INSERT (or UPDATE) statement against a view, which has INSTEAD OF INSERT/UPDATE triggers
The trigger contains the actual logic -- usually two statements, dealing with INSERT/UPDATE of a record into a main table and one (or more) supporting tables (incl. referential integrity).

The problem is when something like a CHECK constraint is hit within the statement in the trigger. The transaction gets dumped, and everything is rolled back. I'm looking at having to duplicate the CHECK/etc constraint in the offending stored procedures to validate prior to the trigger executing -- is there any alternative?

Probably not.  Violating a declarative constraint will always raise an error and prevent the transaction from committing writes that would violate the constraints.  The point of database constraints - checks, foreign keys, uniques etc. - is that they prevent invalid data from being recorded in the database by aborting writes that will violate the constraints.  The constraint violation raises an error and rolls back the transaction.

If you want the transaction to fail gracefully you will either have to deal with the exception or pre-validate the data before attempting to write it.  In the latter case you will have to replace the validations in the stored procedures or implement validation in the application.


SQL Server 2016 AlwaysON I'm looking to setup a two physical node WFC cluster (server 2016) which has no shared disks. Performing standalone install of SQL server 2016 Standard on each of the nodes and then configuring a BAG on top of that.

In terms of quoroum Im going with node majority as there is no shared disk or witness configured.

Has anyone out there done the similar and is this bad practice? Am I required to give it a qouroum?

Also seems when you shut down SQL service on the primary node there isn't automatic failover... hmm... even though its set the sync commit and auto failover. Something isn't right

Also seems when you shut down SQL service on the primary node there isn't automatic failover... hmm... even though its set the sync commit and auto failover. Something isn't right
From testing it appears if one node goes down then the AV resource just fails over to the other node. Just wondering whether we would have splitbrain scenario as using node majority?

Thoughts?

You have a two-node setup and no witness, meaning there would be only two votes in the quorum. This is why you cannot use automated failover currently--there aren't enough votes, and as such, the remaining node cannot get quorum when the other node is offline.

There are two problems with this setup.

Split Brain

If a network outage occurs, the two nodes can no longer communicate, so without a third vote (file share or disk witness), neither node has a way of knowing which cluster resources the other node has online.  Accordingly, the resources will be taken offline to avoid a "split brain," which is a term used to describe a situation where multiple nodes have the same resource(s) online.

Quorum

In order for a cluster to be considered healthy and online, over 50% of votes must be cast.  In this scenario the moment one node goes offline, the cluster will go offline.  With only two votes available, each vote is worth 50%, so if one node is down or they cannot communicate, neither node can get more than 50%.

Enter the Witness

For the above reasons, you need to utilise a witness (fileshare or disk) to ensure that over 50% of votes are cast when one node goes down, and the cluster remains healthy and online.

In your specific scenario you would need to utilise a file share witness or Azure witness, due to a lack of shared disks in the cluster.

If you are using Windows Server 2012 R2 and SQL 2012, you could also consider using Dynamic Witness rather than a witness.