texts: How to make mysqldump use /*!40101 SET character_set_client = utf8mf4 */;?

texts:
When I use mysqldump to export mysql database, it always produce a dump.sql containing

...some other things...
/*!40101 SET character_set_client = utf8*/;
...some other things...

This is the mysqldump command I use:

mysqldump -u root -p databaseName -R -E --single-transaction --default-character-set=utf8mb4 > dump.sql

The charset of the mysql database is utf8mb4 rather than utf8, the characters related variables is:

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | utf8mb4_unicode_ci         |
| collation_database       | utf8mb4_unicode_ci         |
| collation_server         | utf8mb4_unicode_ci         |
+--------------------------+----------------------------+

Why does mysqldump always add
/*!40101 SET character_set_client = utf8*/ rather than
/*!40101 SET character_set_client = utf8mb4*/?

What happens if /*!40101 SET character_set_client = utf8*/ is used?

Can we have mysqldump use /*!40101 SET character_set_client = utf8mb4*/?


texts: 500 databases or 1 database with 500 tables or just 1 table with all the records?

texts:
I currently have an application that is used by a single end customer. For ease of discussion assume the application only needs a single database table for all records. I now need to support multi-tenancy and so in the future I will have about 500 customers using the same database server.

1) A possible solution is to have a separate database for each customer. As there will be around 500 customers that means having MySQL/SQL Server/Oracle/etc... host 500 separate databases. This sounds like overkill for a database server running on an average cloud based server.

2) Another solution is to stick with the single database I already have but to have 500 different tables, one for each end customer. Each customer only averages around 2000 records in their own table. This is easy to implement and I would guess makes it easy to migrate the customer to another server, just move the entire table data over.

3) Lastly I could stick to using a single table in the single database. Instead I add an additional column that identifies the customer the record belongs to. But then the table ends up with about 1,000,000 records which is the aggregate of all 500 customers that average 2,000 each.

I do not know enough about performance and scaling to know 1, 2, 3 is going to give the best performance. Any ideas?


texts: Which Table Will Get Locked With This Query

texts:
INSERT INTO stats.cps
(time_stamp,entries,cust_id)
select start_time, count(*) , c_id from gb.str
where start_time between '$DATE10' and '$DATE'

group by start_time, src_id
order by start_time ASC

I had this query on a machine remote to the DB server and start_time wasn't an index so it was taking a while. I noticed on performance graphs the times it was executing there were spikes of increased load as expected that stopped appearing when I stopped the script that was executing the query.

Another application was misbehaving at those exact times, but that application used the gb.str table and not stats db where the data was inserted. The developers of that application think that this query was the reason for the poor performance of their application but gb.str shouldn't have been locked is my assumption. I ran long SELECT queries on gb.str to see if that application is getting a performance hit but it doesn't..

Would the query also lock gb.str to some extent? All the tables are TokuDB that came in with the MariaDB (10.0.22-MariaDB)


texts: How to fix a MySQL server with a full hard drive?

texts:
Overnight, our MySQL server completely filled the hard drive. Now, I cannot login via phpMyAdmin

`Cannot log in to the MySQL server`

or via the command line

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

or via mysqladmin

mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'

There is one big table I can delete as a temporary fix if I can login to the server, but I can't. How can I login?


texts: Multiple SQL Server instances with FileStream enabled

texts:
Is it possible to have two SQL Server instances, one 2008 R2 and another 2012, to run on the same machine with both having filestream enabled?

I tried to setup this in local dev machine and I can get the services running. But when I try to read a filestream from the 2008 R2 instance, I get a ArgumentException.

The path in both instance looks alright.

SQL Server 2008 R2

\\DEVMC\MSSQLSERVER\v1\TESTDB\dbo\coreFileStore\fileData\525F2031-8D8C-45FF-8386-E1DD5F11C960

SQL Server 2012

\\DEVMC\EXPRESS2012\v1\TESTDB12\dbo\coreFileStore\fileData\525F2031-8D8C-45FF-8386-E1DD5F11C960

Is there any magical setting which could make it work in both instances or is just simply not do-able?

Here is the exception stacktrace when trying to access the file using C# test code.

System.ArgumentException : An invalid parameter was passed to the function.
   at System.Data.SqlTypes.SqlFileStream.OpenSqlFileStream(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize)
   at System.Data.SqlTypes.SqlFileStream..ctor(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize)
   at System.Data.SqlTypes.SqlFileStream..ctor(String path, Byte[] transactionContext, FileAccess access)
   at SimpleTests.FileStreamTest.ReadFilestream(String connectionstring, String fileid) in FileStreamTest.cs: line 47

And here is the NUnit test method I used to verify the issue. FileStore access to TESTDB fails, but TESTDB12 works fine.

[TestCase(new object[] { "Data Source=localhost;Database=TESTDB;Integrated Security=True;Connect Timeout=120;", "525F2031-8D8C-45FF-8386-E1DD5F11C960" })]
[TestCase(new object[] { "Data Source=localhost\\express2012;Database=TESTDB12;Integrated Security=True;Connect Timeout=120;", "9AC28DC0-B927-4075-8D31-00E343E15663" })]
public void ReadFilestream(string connectionstring, string fileid)
{
    const string _selectSql =
        @"SELECT fileData.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT()
  FROM coreFileStore
  WHERE fileStoreId = @FileStoreId";

    using (SqlConnection connection = new SqlConnection(connectionstring))
    {
        connection.Open();
        using (SqlTransaction transaction = connection.BeginTransaction())
        {
            string filepath;
            byte[] txnInfo;
            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandText = _selectSql;
                command.Parameters.AddWithValue("FileStoreId", new Guid(fileid));
                command.Transaction = transaction;
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    reader.Read();
                    filepath = reader.GetString(0);
                    txnInfo = reader.GetValue(1) as byte[];
                }
            }

            Console.WriteLine(filepath);

            using (SqlFileStream sqlFileStream = new SqlFileStream(filepath, txnInfo, FileAccess.Read))
            {
                using (StreamReader reader = new StreamReader(sqlFileStream))
                {
                    string x = reader.ReadToEnd();
                    Console.WriteLine(x);
                }
            }
        }
    }
}


texts: Reset every Oracle sequence to the MAX value of its target table PK?

texts:
I finally got around to migrating from MySQL to Oracle and was pleasantly surprised just how well of a job the SQLDeveloper Migration tool did.  It even took my AUTOINCREMENT fields and created sequences for my tables in their place.  It also did a good job of migrating the data as well.

The only real loose end I see is that when it created the sequences, it did not take into account the existing data in the table and instead all of the sequences are starting NEXTVAL of 1.

I can imagine a simple PL/SQL script that will set the next value of the sequence based on the MAX value in the primary key column of a specific table to a specific sequence but then I have to do this over a hundred times and well I just don't have the patience for that.

I wonder if there is some way I can write a PL/SQL script that uses meta data in the SYSTEM schema to dynamically do this for every table/sequence pair in an entire user space?  Does anybody have any other better ideas how to handle this?  I ran out of interns BTW.


texts: Why student attendance is not considered a fact in data warehousing?

texts:
In a KimballGroup example student attendance is not considered a fact. Hence it considers this example as forming factless fact table. I don't really understand what is the difference between a fact and measure. Why a sale, which is also an event, is considered a fact but the event of attendance is not. What is the difference with this two events?

thanks


texts: Is there any impact whatsoever of enabling FILESTREAM on active SQL Server?

texts:
I may have to enable Filestream on a live SQL Server at work, but first need to know about the possible impact/implications of doing so on the performance/operation of the databases held therein.

I have done some reading and have not found anything daunting, but since it's fairly important, thought I had better check on here.


texts: What are the pros and cons of having more data-bearing members in a MongoDB replica set?

texts:
A 3 member replica set has the same fault tolerance if all 3 members contain a copy of the data or if one is an arbiter so why would I ever want all 3 members to have a copy of the data?

What is a situation where all members having a copy of the data makes sense?


texts: SQL Server Configuration Manger not applying permissions

texts:
I am trying to change the service account of a SQL Server 2014 instance to use an MSA.  The MSA has already been created and installed to this server.
I have changed it to a domain account previously and this worked fine.
Configuration manager comes back with "Process terminated unexpectedly" when it restarts the SQL Engine, checking the event log I see messages about not being able to access the error logs, when adding permissions to the error log there are then messages about not being able to access the master ldf and mdf.

I've never seen this issue previously so wondering if anyone has seen similar?
OS is 2012 R2
SQL is 204 EE
Thanks
Mat


texts: Error from master error log while doing a replication to slave

texts:
I have setup a master and slave MySQL running 5.7.17 in Linux machines. Both servers have a unique UUID as specified in the auto.cnf file. Besides, the master server has server-id=10 while slave has server-id=20.

However I keep getting the following error coming in the master's error log. I could not figure out why as the replication is running fine. The error starts to come out when the master's SQL thread is stopped.

2016-12-28T09:00:37.953467Z 27931 [Note]
 Start binlog_dump to master_thread_id(27931) slave_server(20),
 pos(hostnameA-bin.000117, 818913948)
2016-12-28T09:01:38.027487Z 27978 [Note]
 While initializing dump thread for slave with UUID <xxxxxxxx-xxx-xxxx-8bbc-xxxxxxxxxxxx>,
 found a zombie dump thread with the same UUID.
 Master is killing the zombie dump thread(27931).
2016-12-28T09:01:38.027652Z 27978 [Note]
 Start binlog_dump to master_thread_id(27978) slave_server(20),
 pos(hostnameA-bin.000117, 818913948)
2016-12-28T09:02:38.104298Z 28025 [Note]
 While initializing dump thread for slave with UUID <xxxxxxxx-xxx-xxxx-8bbc-xxxxxxxxxxxx>,
 found a zombie dump thread with the same UUID.
 Master is killing the zombie dump thread(27978).


texts:
How to use local variable in USE statement? I get error:

  Msg 911, Level 16, State 1, Line 6
  Database 'DWSource_@Country' does not exist. Make sure that the name is entered correctly.

Code:

DECLARE @Country varchar(3)
SET @Country='UKR'

USE DWSource_@Country

SELECT  @Country as country,
        count(*) as n_ALL,
        sum(case when LEN(EPOSTA)>0 then 1 else 0 end) as EMAIL1,
        sum(case when LEN(EPOSTA_2)>0 then 1 else 0 end) as EMAIL2

FROM    [dbo].[_data_CRM_S_PARTNER]
WHERE   VIR = 'SM'
    AND PRAVNA_OSEBA = 0
    AND PROBLEMATICEN = 0
    AND ISNULL([STATUS],0) IN (0,1)
    AND ID_DBCLUSTER IN ('112','122','132','212','222','232','312','322','332','311','321','331')

Thanks in advance!

Br,
Anja


texts:
I have to search in a table, all the photos that start with the code "2950".
Example: 2950_1, 2950_2 ...
Something like:

SELECT * FROM `vnd_imovelfoto` WHERE `codigofoto` = 2950


texts:
I have a server with 4 physical Xeon processors, each with 8 cores and each core is capable of 2 threads.

When I run MySQL queries I'm only seeing 1 CPU go to 100% in top and the total CPU usage at only 1.5%. I'm using MyISM as the MySQL engine.

Is it possible for MySQL to use all 64 threads instead of just the single one it is using right now?

If so, what outside of the default config do I need to change?

So far I tried everything in this article: Possible to make MySQL use more than one core?

put innodb_thread_concurrency at 0
innodb_read_io_threads and innodb_write_io_threads to 64.

The only other idea I had was to run the query 64 different times and piece the results together which actually does engage all the threads but that seems a rather crude way to accomplish the multi-threading.


texts: Can anyone tell me the query to list the databases and SSRS reports and different logins in a particular server ?


texts: ORACLE_HOME / ORACLE_SID variable - why do we need to export them ?

texts:
I am not sure if this is a bash / shell question or oracle question.
I am on linux and oracle 11g.

I have always export my ORACLE_HOME and ORACLE_SID variable and e.g. using invoking sqlplus is no issue.

I thought have thought that sqlplus is able to be invoked because it is in my PATH variable with $ORACLE_HOME/bin added to $PATH.

=============================================

Today, not sure why, i decided to unset ORACLE_HOME and ORACLE_SID and just declare them as local variable to my current shell.  My PATH is still the same. (contain the full path to oracle_home/bin)

ORACLE_HOME=/path/to/oracle_home
ORACLE_SID=orcl

and I invoke sqlplus from the shell.

==============================================

The sqlplus program is able to be located, but it is not able to run saying that i need to set my ORACLE_HOME

[oracle@SJOAM ~]$ sqlplus
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
[oracle@SJOAM ~]$

===============================================

My question is

1) why is sqlplus unable to know the ORACLE_HOME and ORACLE_SID since it is run from the current shell ?

is it because it is invoked as a child process and thus cannot access the parent process (bash) local variable ?

Regards,
Noob


texts: Load data into postgresql from file with trailing delimiter

texts:
Context

I receive a number of data files that I'd like to load on a monthly basis into Postgresql (currently running 11.4). However, some of the files each month are formatted such that they have a trailing delimiter at the end of each line.

For example, if I have the following table:

CREATE TABLE t1 (
  id integer,
  make varchar(50),
  model varchar(50)
);

The corresponding file looks like:

1|Ford|Taurus|
2|Honda|Civic|
3|Toyota|Camry|

Note that while the | character is used as a delimiter, there is an "extra" one at the end of each line -- which causes a load to fail:

=> BEGIN TRANSACTION;
BEGIN
=> TRUNCATE TABLE t1;
TRUNCATE TABLE
=> \copy t1 (id, make, model) FROM t1.dat WITH FREEZE NULL '' DELIMITER '|';
ERROR:  extra data after last expected column
CONTEXT:  COPY t1, line 1: "1|Ford|Taurus|"
=> COMMIT;
ROLLBACK

Solutions I've thought of so far:

Load into a temp table that has an extra dummy column, then INSERT AS SELECT; however I believe that loses the speed/efficiency of COPY WITH FREEZE, plus you touch the data twice which I'm guessing is bad as the size of it grows.
Add a dummy column onto each table; don't know a priori which files/tables need this.
Ask the data provider not to do this; it comes from a branch of the US Government, so a very long shot...
Pre-process the files to remove any / a single trailing delimiters; again don't know a priori which files will need this and it could change month to month.

Anyone have any other ideas or recommendations on how to solve this?


texts:
I have documents like:

{_id: "someid1", "bar": "somevaluebar1"}
{_id: "someid2", "foo": "somevaluefoo2", "bar": "somevaluebar2"}
{_id: "someid3", "foo": "somevaluefoo3", "zoo": "somevaluezoo3"}
{_id: "someid4", "zoo": "somevaluezoo4"}

If we query documents by "foo" the most and "bar" the second, does it make sense to create a compound shard key like { "foo" : 1, "bar" : 1, "_id" : 1 } because "foo" and "bar" might be missing too?

When I tried to run this command

sh.shardCollection("<your-db>", {{ "foo" : 1, "bar" : 1, "_id" : 1 }:"hashed"})

it gave me a syntax error.


texts: Can a non-owner of a table be allowed to disable the table's triggers?

texts: I would like for one user who is not a table owner, give the permissions to turn off and turn on the triggers. Can it be done?


texts:
I would like to know how to draw set of entities with composite attribute in Barker's notation. In UML you can do this in that way:

and in Chen's notation:

Please, help. I have final exam day after tomorrow.


texts: Why SQL Server does not apply Key Lookup after SELECT TOP?

texts:
I have one simple query that selects from 1 table, no join. All columns used for filter or sort have been indexed.

This is what the execution plan looks like:

+--------+     +-----+     +--------------+     +------------+
| SELECT | <== | Top | <== | Nested Loops | <== | Index Seek |
+--------+     +-----+     +--------------+ |   +------------+
                                            |
                                            |   +------------+
                                            |__ | Key Lookup |
                                                +------------+

SQL only reads the necessary rows when performs Index Seek, then follows with a Key Lookup for the missing columns (in the SELECT), sort everything, then select the top rows.

This is fine, except that if I have to select the last 10 rows and my data have like 80,000 rows in total, SQL will then do a Key Lookup on all 80k rows before return the last 10, which doesn't sound right.

I've tried with a dataset contains 11,000 rows. The Number of Rows Read is indeed 11,000. Although compare 2 plans (big vs small OFFSET), I don't see many differences in performance (same I/O and CPU cost). But in a production server where the number of rows may be much higher and the query is executed more frequently, will performance become a problem then?

Should I write the query myself to apply Key Lookup after SELECT TOP: firstly select the necessary row IDs only, then select all columns of those rows?


texts: SQL Server 2012 Remote Access to Certain Users

texts:
Recently, I've started working on SQL Server 2012. As there is a risk in allowing anyone to access SQL Server remotely, I'm trying enable remote connections only to specific users within my developer team.

How can I allow only a specific set of users to connect to SQL Server remotely?

As of now I've disabled Remote Access by using following Query

EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
EXECUTE sp_configure 'Remote access', 0;
RECONFIGURE;
GO EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;


texts: How to autoincrement Prod_id based on Product_Name using trigger in mysql

texts:
Product_Details table : (Fields) Prod_id(Primary key), Product_Name, Prefix and Slno(Auto incremented)

i need to increment Prod_Id based on the product name field and prefix field (Input will be given by user). i have  already concat prefix and id. but i cant achieve the sequence as i show below

    Product Name      Prefix      Prod_id
    BOOK              BO          BO_001
    FLOWER            FLOW        FLOW_001
    BOOK              BO          BO_002
    DESK              DE          DE_001
    DESK              DE          DE_002
    FLOWER            FLOW        FLOW_002

AS OF NOW I HAVE IMPLEMENTED A TRIGGER

DELIMITER $$
    CREATE TRIGGER tg_customer_details_INSERT
    BEFORE INSERT ON customer_details
    FOR EACH ROW
      BEGIN
        INSERT INTO product_details_seq VALUES (NULL);
        SET NEW.Slno = coalesce((select max(Slno) from product_details), 0) + 1;
         SET NEW.Prod_id = CONCAT((NEW.Prefix), LPAD(LAST_INSERT_ID(), 3, '0'));
      END$$
DELIMITER;

But in this code i am getting output like book_001, flower_002,book_003, desk_004,Flower_005....so on.. i need a separate sequence for each product name. Help with a trigger code will be much appreciated.

Thanks,
Acube.


texts:
I want to make my SSIS project can be configure through project parameters. So I use project parameters to store connection strings. But the connection string which I copied from the mysql connection manager not working. After I assign the parameter value which I copied from the connection manager it self, to connection manager by expression, It says:

  An error has occurred while connecting MySQL Server: Could not create a managed connection manager

How to fix this? I think this is a problem about connection string.


texts:
I was watching a video on pivot tables and got to thinking, why can't we just push in a sub query into the IN clause, instead of declaring the expected columns statically? Well, turns out you cant haha, but I wasn't satisfied with that so after some research found that Dynamic SQL will allow you to do something similar. Now, I'm not expert on Dynamic SQL by far, but for the most part I understand what is going on. I am, however, having an issue with a specific query. Here is the query:

DECLARE @SQLString AS NVARCHAR(MAX)
        , @custID AS NVARCHAR(MAX) ;
SELECT @Custid = ISNULL(@custid + ',','') + QUOTENAME(custid)
FROM (SELECT Distinct custid FROM Sales.Orders) AS custid

SET @SQLString = N'
With ptable AS
(
SELECT empid, custid , orderid
FROM sales.Orders
)
SELECT empid, ' + @custID + N'
FROM ptable
PIVOT (COUNT(orderid) For custid IN(' + @custID + N')) AS pivtable
ORDER BY empid'
EXEC @SQLString;

The idea here is to have all of the distinct customer ID's found in the order's table as a separate column to track all employee-customer orders. (This is all for practice and in the name of figuring out how SQL works). This table happens to have 91 unique customers. Here's the problem, it works for the first @CustID in the SELECT statement, however for the second @custid in the IN clause, it only returns the first 16 and a missing ] after the 16. Here is the outputted error message:

  Msg 203, Level 16, State 2, Line 16 The name ' With ptable AS (
  SELECT empid, custid , orderid FROM sales.Orders ) SELECT empid,
  [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51],[52],[53],[54],[55],[56],[58],[59],[60],[61],[62],[63],[64],[65],[66],[67],[68],[69],[70],[71],[72],[73],[74],[75],[76],[77],[78],[79],[80],[81],[82],[83],[84],[85],[86],[87],[88],[89],[90],[91]
  FROM ptable PIVOT (COUNT(orderid) For custid
  IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16'
  is not a valid identifier.

I am struggling to figure out why it worked for the first select clause then failed on the second one. I know this is a ridiculous example, but I guess my curiosity got the better of me and I just want to know. Any help is appreciated!


texts: Prefix full text search with numbers in SQL Server

texts:
If I create the following table in SQL Server 2008 R2:

CREATE TABLE test(
  id BIGINT IDENTITY NOT NULL,
  value VARCHAR(10),
  CONSTRAINT test_pk PRIMARY KEY (id)
);

CREATE FULLTEXT CATALOG test_catalog;

CREATE FULLTEXT INDEX ON test
  (
    value Language 0
  )
  KEY INDEX test_pk
  ON test_catalog;

ALTER FULLTEXT INDEX ON test START UPDATE POPULATION;

INSERT INTO test (value) VALUES ('Xstreet 7');
INSERT INTO test (value) VALUES ('Ystreet 8');
INSERT INTO test (value) VALUES ('Zstreet');
INSERT INTO test (value) VALUES ('Wstreet9');

Wait until the index is populated and perform a prefix search for 'Ystreet 8':

SELECT * from test WHERE CONTAINS(*, '"Ystreet 8*"');

I get no results. Why is that?

Searches for '"Ystreet 8"', '"Ystreet*"' and "'Wstreet9*"' works as expected.


texts: Scheduling duplication of two tables across instances

texts:
In my SQL Server 2012 database I have two tables:

[tbl_Customers] - about 30 rows
[tbl_Users] - about 10,000 rows

I need to schedule the two tables above to be copied to a new instance, say every 12 hours. I am considering setting up a SQL Azure account to act as the second instance.

[tbl_Users] is a popularly-accessed table, with CRUD operations being performed quite a lot, therefore the data changes often.  It doesn't matter too much about those changes being replicated on the second instance immediately (but it would be nice if possible if the option allows).

Can anyone recommend which approach they might take to achieve this?  I'm new to this type of thing so please don't hold back with the simplicity of your answers...!

I have SQL2012 Standard on my dev machine (but no SSIS at the moment although I could install it), but don't know if my existing SQL host and/or Microsoft have SSIS on their offerings.


texts: PostgreSQL Division In Query Not Working

texts:
So I am having a really off day or something, but for the life of me I can not figure this out. I am trying to divide tier1 by total to get a percentage of the total. I thought like this:

(tier1 / total) as per

But no luck. I have CAST these as int and still nothing. I will either get an error and the query won't execute or I will get a value of 0.

Here is what I am trying to work with:

SELECT count(student_id) as total
,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 1) as tier1
,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 2) as tier2
,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 3) as tier3
FROM national_assessments.aimsweb
WHERE general_outcome_measure = 'PSF' AND fall_september_tier IS NOT NULL

I had this idea as well but no luck:

SELECT tier1,total, tier1/total as test
FROM (
    SELECT count(student_id) as total
    ,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 1) as tier1
    ,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 2) as tier2
    ,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 3) as tier3
    FROM national_assessments.aimsweb
    WHERE general_outcome_measure = 'PSF' AND fall_september_tier IS NOT NULL
) as test


texts:
I wonder if the order in which records are locked is guaranteed when I use SELECT ... FOR UPDATE in cursors ?
For example,

DECLARE
CURSOR test_cursor IS
SELECT field1, field2
FROM table1
ORDER BY pk_field
FOR UPDATE OF field1;

When I open such a cursor, will it always lock records in the order of pk_field ?

From what I read so far order of locking is not guaranteed for regular SELECT ... ORDER BY pk_field FOR UPDATE , but I'm not sure if it's true for cursors. I'd expect it does lock in the order set in ORDER BY clause for cursors; however, I cannot find confirmation.

Thank you.


texts: How to whitelist all connections in MySQL

texts: It would be convenient for me to whitelist a server so that anyone with the username and password can connect to it remotely? Is this possible? I do understand this is horrible for security purposes if it's even possible (I haven't found anything that mentions a way to do such.)


texts: remove the instance while uninstall msi package

texts: I have uninstalled the XYZ.msi package from Control Panel.It is completely uninstalled,but it's instance is still available in SQL server 2005.Why the instance didn't remove after uninstall of the package?I want to remove that instance.Is any possibilities to remove the instance in SQL server while uninstalling the msi package?


texts:
I would like to create a relationship between to tables. Normally and in previous versions of sql server and the management studio I would go to database diagrams and draw the relation in the diagram.

I have an 2008 sql server and the ssms 2016 ctp2 client. Am I missing something? tested it too by connecting to an other sql server with other credentials etc.

it is there when I use remote desktop to work on the database server... and connect with a windows account. what am i doing wrong?

He lost me at the 48th second: https://youtu.be/PHjwX-cexIA?t=48s

this seems related to https://stackoverflow.com/a/21324448/169714
but what caused the diagram node to disappear when logged in remotely?


texts: I have been working on MySQL database whose size is growing quite huge and I want to save on space on my cloud. I have tables whose engine is Innodb which holds the largest amount of data. I have already run OPTIMIZE TABLE  on them which saved some space. Index data length is only a few MBs. I am looking for any other way to reduce the size of my growing tables and if possible the whole database. I am only keeping binary logs for 3 days for recovery. I will appreciate a solution that will cause no downtime or minimal downtime and risk. I also have a local replicated data of the same.


texts: suddedenly increased in size of Log files form 32GB to 70 GB

texts:
I am not DBA ,but I need to check on the size of database files .
my database log files earlier was around 32 GB but from last few days it starts increasing and finally reached to 70 GB . first I thought it was natural , but soon I realized  that I was wrong  .

For shrinking the size of log file ,firstly I used to determine the % of space occupied in log file  by using .

        DBCC SQLPERF(logspace)

Result was :- log size(MB) > 70 and Log Space Used > 94%
 with

          DBCC LOGINFO

I found more than 700 transactions having Status = '2'

After that I took "log as well as full backup"
and run above query again but still got >700  transactions having Status = '2'
detached and reattached , but still no difference in size .

Then I went for Shrinking log files and for testing purpose I gave the size 60GB .
 Then run following query again

   DBCC SQLPERF(logspace)

and result was :- log size(MB) > 66 and Log Space Used =  99.1258%.

Question is : How can I bring the size of log files near to earlier size i.e. around 32 GB or near by and how to keep check on it so that its size does not change dramatically .

** Recovery Mode :- FULL

Thanks in advance ....


texts:
I am reading a book which shows this example

SELECT wait_type ,
SUM(wait_time_ms / 1000) AS [wait_time_s]
FROM sys.dm_os_wait_stats DOWS
WHERE wait_type NOT IN ( 'SLEEP_TASK', 'BROKER_TASK_STOP',
'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT',
'CLR_MANUAL_EVENT', 'LAZYWRITER_SLEEP' )
GROUP BY wait_type
ORDER BY SUM(wait_time_ms) DESC

I want to know the exact role(meaning) of a keyword DOWS next to FROM statement. I tried to search but nothing helpful was found.
Thanks for your time!


texts:
I am using Postgres 9.5, and I am trying to create exception handling in a stored procedure/function.

I can't quite figure out how to raise the error should any of the 'INSERT' statements fail. If there is a failure, I want to 'ROLLBACK' the transaction so nothing is inserted, return the user friendly error to the user, and log the sql server error message somewhere. Is that possible to do inside a stored procedure?

Here is what I have so far, but it does not work:

CREATE FUNCTION CREATE_USER_FUNC (
role VARCHAR,
firstname VARCHAR,
lastname VARCHAR,
email VARCHAR,
username VARCHAR,
is_enabled BOOLEAN,
city VARCHAR,
state CHAR,
zipcode INTEGER,
password VARCHAR,
team VARCHAR,
OUT new_user_id INTEGER
)
RETURNS integer AS $$
BEGIN
INSERT INTO users (firstname, lastname, email, username, is_user_enabled, city, us_state, zipcode, user_password) VALUES($2, $3 ,$4, $5, $6, $7, $8, $9, $10) RETURNING user_id INTO new_user_id;
    RAISE EXCEPTION SQLSTATE '23502' USING MESSAGE = 'Values can not be null';
    RAISE EXCEPTION SQLSTATE '23514' USING MESSAGE = 'Password not the proper length';

INSERT INTO role_members (user_id, role_name) VALUES(new_user_id, $1);
    RAISE EXCEPTION SQLSTATE '23502' USING MESSAGE = 'Values not present in the table';

INSERT INTO team_members (user_id, dealership) VALUES(new_user_id, $11);
    RAISE EXCEPTION SQLSTATE '23502' USING MESSAGE = 'Values not present in the table';
EXCEPTION WHEN OTHERS THEN
    RAISE EXCEPTION
END;
$$ LANGUAGE plpgsql


texts: in SQL databases does an update rewrite all columns in the row or only those in the SET tuple?

texts:
i was wondering how a sql update works in two scenarios:

table with 3 columns: a, b, c

say the data value for a has to be changed.

1: update on a row setting all columns a, b, c (where b and c have not changed from what is stored)

2: update on a row setting only the a column value (because b and c have not changed)

is there any performance difference between these? im having a hard time finding information about how they actually work behind the scenes. from the few articles i found my understanding is that in an update the db will:

load the row (all columns) into memory
set the values from the set tuple (in order)
write the updated row back


texts: I have connection issue and its changing ip

texts:
I use DB 'Mysql 14.14 Distrib 5.7.24' and the OS is 'Ubuntu 18.04'

The DB server have local IP : 10.0.211.198

And For The application IP : 10.0.214.198

when I try connect form application server to db I get error in the connection but After I read it I found the mysql client change IP to Application IP as below screen

But when I try connect using telnet its successfully connected.

I try to remove db and reinstall it also try different client to connect and its same result.

Thank you


texts: I need to change the name for a few databases, however the databases are used by a lot of applications managed by several different teams. It would take some time for all the applications to finish changing connections and database name references. Is there a way to allow the change to be done seamlessly? Was looking at synonyms to temporarily allow references to the renamed database but unfortunately it is only for database level.


texts: Is it possible to query value of SQLNET.EXPIRE_TIME parameter?

texts:
Can somebody advice whether it is possible to query for current value of SQLNET.EXPIRE_TIME parameter in Oracle 11 DB?

Should

select * from v$parameter

return value of SQLNET.EXPIRE_TIME (if it has been configured)?


texts:
I'm currently studying oracle 11g,i had this question in mind but never found the answer even in the docs.
What happens if log files storage is full?
does the DB stop or block any DML or DDL execution? Does it delete ancient log files and run like nothing happened ?
Thank you !


texts:
I have been taught in my MSSQL classes, this is how to join two tables

select * from FirstTable A
JOIN SecondTable B
on A.ID= B.ID

Now in my professional life, I came across JOIN queries like this

select * from FirstTable A, SecondTable B
where A.ID=B.ID

I know the second option was once norm but perhaps now abandoned.

I find that in complex queries where I join 6+ tables + have a number of sub queries , the second form is a lot easier to understand and is short and pretty.

Questions

Which one should I use?
Is there an advantage of one over the other?


texts: How do I select row which has a max value in other column - PostgreSQL?

texts:
I'm just starting with PostgreSQL and I bet there is a simple answer for my qs.
I have a view like:
 county - candidate - votes
Abbeville - John Kasich - 157
Abbeville - Ben Carson - 305
Abbeville - Ted Cruz - 876
Abbeville - Jeb Bush - 236
Abbeville - Hillary Clinton - 1501
Abbeville - Marco Rubio - 740
Abbeville - Bernie Sanders - 312
Abbeville - Donald Trump - 1353
Abbot - Hillary Clinton - 0
Abbot - Bernie Sanders - 1
Abington - Ben Carson - 53
Abington - John Kasich - 299
Abington - Bernie Sanders - 1352

And I would like to write a query which returns who has most votes per county like:
    county - candidate - votes
    Abbeville - Hillary Clinton - 1501
    Abbot - Bernie Sanders - 1
    Abington - Bernie Sanders - 1352

I tried this query but cannot return right candidate:
select
distinct on (county) county , candidate ,max(votes) over(partition by county)
from primary_results_csv prc
group by county, candidate, votes
order by county

Thanks in advance for help.


texts: MySQL migration physical server VMware virtual server

texts: How to migrate physical MySQL server to VMware virtual server with all db and user privileges


texts:
I am adding MySQL Full Text search to an intranet and one of the things i'd like to do is return an excerpt back in the search result that includes the search term(s) the user searched for.

I initially thought that I could do this with locate() to get the index of the string minus ~10 characters and then substring around 200 characters after but this only works if there is a document that includes these words consecutively. For example:

SELECT  id,created_at, modified_at,
        SUBSTRING(content, (locate('pto request', content))) as excerpt
FROM    search_index
WHERE   MATCH (name,content) AGAINST ('pto request' IN BOOLEAN MODE)

The match clause finds documents that contains the words pto and request that may not be consecutive but the the locate function requires the exact string "pto request" to be somewhere in the document, if it isn't, an excerpt is not generated.

My first thought was to check for 0, if 0 locate didn't match anything so remove a word and try again and keep doing so until a locate finds a match, but this could get really messy.  Is there a better way?


texts: MySql - set my.cnf location at server start

texts:
I want to have two MySql servers running on the same system (for master-slave replication tests purposes).
To be able to do this I need both servers read different my.cnf files.
As I see at startup MySql tries to read from one of default locations.
How can I specify it own my.cnf location for MySql server?

PS.
I am about to use MySql 5.6 under Debian environment.


texts: Ola Hallengren maintenance script - how can I tell a backup file is still being written to?

texts: My question is whether there is a way to identify a backup file that is still being written to. I want to copy the completed backup files to another place. By reading the backup extension, I can't differentiate completed backup files from backup files that are still being written to. Any suggestions?


texts: Why does SQL Server allow a float(53) value that falls outside the range specified by Microsoft?

texts:
I have a particular table with a column data type of float(53), non null. According to the Microsoft Documentation, the smallest value above 0 that can be stored in this data type is is 2.23E-308. However, I am able to store a value much smaller, as small as 4.94065645841247E-324. See the following image of proof that SQL server can store and retrieve a value outside the range specified in the documentation:

When I run a query such as the following:

select 4.7E-309

SQL Server says: Warning: the floating point value '4.7E-309' is too small. It will be interpreted as 0.

The value inserted in the screenshot was inserted via the C++ ADO interface using record binding.

I have a few questions:

Why does SQL server handle values outside the range specified in the documentation?
Similarly, why does SQL Server issue a warning when an "out of range" value is in the query, when clearly the database can store values out of that range?

How are floating points stored on SQL database tables, and are there any SQL server settings that would strictly enforce the range on one SQL server as opposed to another?

I'm trying to troubleshoot an issue where an ADO record binding was (possibly) rejected due to the value being out of range, however all my test to reproduce the issue (on a different SQL server instance) show that SQL accepts values much beyond the limits specified in the documentation. Any information on how floating points are stored and handled in SQL (especially with relation to something like .NET or C++) would be much appreciated.

I have tested this with both SQL server 2005 and 2008 with the same results.


texts: How can a weak entity be related to a weak entity?

texts: As we can see in the entity-relationship diagram (ERD) below, the ATTENDANCE entity is related to the TRANSCRIPT entity. But how can entities in TRANSCRIPT be identified if there is no primary key in ATTENDANCE? Is the ERD wrong?


texts: Unable to connect oracle as sysdba tables have been dropped

texts:
I have a script which lists all tables belonging to the user and executes DROP for all of them.
By mistake, I logged in oracle with 'sys as sysdba' and ran above script. Due to which all sysdba tables are dropped.
Now i can not startup database instance. In alert log, it gives following error:

Sat Jul 20 15:28:21 2013
Errors in file orcl_ora_4276.trc:
ORA-00942: table or view does not exist
Error 942 happened during db open, shutting down database
USER: terminating instance due to error 942

I tried to flashback one droppd table, but it is giving error:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> FLASHBACK TABLE MAP_OBJECT TO BEFORE DROP;
ERROR:
ORA-03114: not connected to ORACLE

Please suggest if there is any way to restore all these tables.
Or if creating new database is the only way?


texts: Best practices on dealing with devs that want to use XP_CMDSHELL to BCP OUT

texts:
This is not a technical question. I would like to know the best practice to use  in a place where devs want to have SA for everything.

The most recent event is:

Devs want to use BCP OUT to export data to a txt file really fast.

What should I think about it? Should I say "why you need this to be THIS fast?"

I'm sure there's a lot of alternatives to export data to .TXT file. they can be slower but it does the job.

We don't use Windows login here, only logins, so I can't create a proxy account (I'm reading something about this).

Note: They use Delphi to write application program code.


texts: Can view tables from command line but not from pgadmin

texts:
I restored a cluster from base backups. The version of postgresql is 12.4. After starting the postgresql service, and logging into the cluster (from putty), I can see the dbs, and even the tables in DB that I need to check connectivity to :

However, when I ask the development team to test accessibility to the DB (they use pgadmin), they can login, but they see that the number of tables are 0 :
After that they refreshed the tables tab, and they see another error :

The user that is used to check from putty and pgadmin is the same.
Why is this, and how do I resolve this problem?


texts: Preserving create_time when loading a mysqldump file

texts: I want to make a copy of a MySQL database but preserve the information_schema.create_time for each of the tables. I haven't been able to find an option to do that with mysqldump.


texts: Why does SQL Server not do compound column statistics histograms?


texts:
To start, I have no technical experience. I own a home services business and I want a mySQL database for all my data. I know how I want it, I've played around with Caspio so I know how it will look/function from a user's perspective.

My question is in regards to how to have someone built it for me and what security measure to take to keep my website safe. I have hosting with siteground for my wordpress website. If its best to just create a new siteground account and incur another separate billing cost for it than I will do that but if it is safe to keep it on the same account than that is preferable due to savings and practicality. I went to cPanel, created the database (nothing in it, just created it), and created a user account for my developer to use when I hire one. I learned how to have a link for them to access it (mydomain.com/phpMyAdmin) and that is ready. I tested it, the login info for the user I created works.

So my actual question...Is giving them that link and the login credentials safe? That should only allow them to do things in the database right? By safe I mean my website and files and stuff, they wont have any access to any of that or cPanel right?


texts: Lock wait timeout exceeded on insert despite low lock_time in the slow query log

texts:
Our Java/Spring application reports occasional lock wait timeouts on single record INSERTs to a MySQL 5.5 InnoDB table. Here are a few slow query log records for some of the failed INSERTs.

# Query_time: 51.472333  Lock_time: 0.000090 Rows_sent: 0  Rows_examined: 0
SET timestamp=1340049365;
insert into Foo ( ...

# Query_time: 51.045967  Lock_time: 0.000131 Rows_sent: 0  Rows_examined: 0
SET timestamp=1340049476;
insert into Foo ( ...

# Query_time: 50.693427  Lock_time: 0.000128 Rows_sent: 0  Rows_examined: 0
SET timestamp=1340049561;
insert into Foo ( ...

Clearly the queries are hanging for a long time and seem to be hitting the exception right at around 50s, but the short lock time does not make sense to me. What can the queries be waiting for if not some lock? Would someone please shed some light onto this?


texts: Multicolumn extended statistiscs, NLS_SORT and NLS_COMP, CHAR columns, and the bane of my existance

texts:
This is as a result of this question.
So I'm working with a query that looks like this:

select count(*)
from table1
where col1 = 123
and col2 = '1';

The problem is this:  col2 is defined as a CHAR(1) and all sessions set NLS_SORT = 'BINARY_CI' and NLS_COMP = 'LINGUISTIC' .  (col1 is a NUMBER column.)

First off, without the NLS settings on for the sessions, defining a multicolumn extended stat over (col1,col2) works fine and produces appropriate cardinality.

But with NLS_SORT = 'BINARY_CI' and NLS_COMP = 'LINGUISTIC', it doesn't use the extended stats.  I believed its much like an index and the NLS settings - we had to define indexes with (NLSSORT("COL2",'nls_sort=''BINARY_CI''')) .

So, I implemented a multi column extended stats over ("COL1", (NLSSORT("COL2",'nls_sort=''BINARY_CI''')) .  But it still doesn't work (but an index defined the same will work).

In the predicate information, it shows that with the NLS settings on, col2 = '1' is transformed into:
NLSSORT(INTERNAL_FUNCTION(col2),'nls_sort=''BINARY_CI''')=HEXTORAW('3100') .  I believe that the INTERNAL_FUNCTION() is due to col2 being defined as CHAR(1).  I can't change that.

How can I create a multicolumn extended stat column group for these columns given my conditions?


texts: PostgeSQL 9.3: Is it safe to stop VACUUM FULL?

texts:
I'm using PostgreSQL 9.3 on RDS. Once in a while, I run a VACUUM FULL operation on the database. However, such operation can take quite a while and it blocks other tables, so the need to stop the operation might arise.

Is it safe to stop a VACUUM FULL operation that takes too long?


texts: Compare two columns and replace duplicates with nulls

texts:
I have a table like this:

id  | t1 | t2
---------------
 1  |  a |  b
 2  |  c |  a
 3  |  a |  e
 4  |  f |  g
 5  |  c |  c

I want to compare columns t1 and t2 with each other to remove matching values and get unique values in each columns, like this:

   t1 | t2
  -----------
    a |  b
    c |  null
 null |  e
    f |  g
  null| null

It doesn't matter much which row to choose to show the value and which other ones would show nulls. For instance, as value a is also found in row 3 of the original dataset, the following output would also be valid:

   t1 | t2
  -----------
 null |  b
    c |  null
    a |  e
    f |  g
  null| null

The end goal is to show all distinct values and no identical values across the two columns.


texts: Generate GRANT for all tables reference by a procedure

texts:
I have a couple of stored procedures in my DB in which each one select from a couple of tables. I need to construct a statement where I can give it a stored procedure and get the grant select statement for all the table that this stored procedure uses. I need this since my procedures use sp_executesql and hence users need explicit permissions on the tables as sp_executesql makes execute permission on the stored procedure not enough (as you might know the user would need select permission on the tables used by that SP as well).

I have so far done the below dynamic SQL statement, however, it only works if the stored procedure only selects from one table. I totally understand that it does not work for more due to the subquery in the where clause.

select 'GRANT SELECT ON [' + schema_name(schema_id) + '].[' + name + '] TO [User1]'
from sys.tables
where name like (SELECT distinct t.name
FROM sys.sql_dependencies d
INNER JOIN sys.procedures p ON p.object_id = d.object_id
INNER JOIN sys.tables     t ON t.object_id = d.referenced_major_id
where P.NAME LIKE 'myproc')

Error when SP uses more than 1 table:

  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

My required output for EACH table used by a stored procedure:

GRANT SELECT ON [dbo].[table1] TO [User1]

Is there another way to achieve this?


texts: Oracle: ORA-00942: "table or view does not exist" in SQL Developer

texts:
in SQL Developer when i get session information he return me this error message: (ORA-00942: "table or view does not exist"). Can i resolve this with add grant to privilege of my user? Wich command use for wich table or view system?
Best Regards to all.
Fabio.


texts:
I have installed Multiple MySQL instances and i have two configuration files one for each one is using default location of /etc/mysql/my.cnf.

other server i have started like

root@ABC-240:/usr/local/mysql# bin/mysqld_safe  --defaults-file=/etc/mysql/binary_my.cnf &

root@ABC-240:/usr/local/mysql# 120104 10:19:34 mysqld_safe Logging to syslog.
120104 10:19:34 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

I have set the error log in both of the configuration files but one of them as above is logging to syslogs.

Why is it like that?


texts:
I have City and Cars tables and I have this query:

UPDATE City
           Set status='good',
           marke1=(Select marke2 From Cars
                   where car_id=123
                     and city_id is null
                     and country is null )
           Where person_id=789

Problem is when country or city_id is not null I am still getting status='good'
           so I want to use the same (city_id is null and country is null) conditions for status but I'm not good enough in conditional statement. Any help please.


texts: SQL Server 2016 deprecation of DML keyword ROWGUIDCOL

texts:
I just found out that the DML keyword ROWGUIDCOL is deprecated, replacement is $rowguid; and that DML IDENTITYCOL keyword is deprecated, new name is $identity.

I don't understand why a non-variable (no dollar sign symbol) DML can be replaced with what is essentially an alias, which if I understand it, will sometimes evaluate back to ROWGUIDCOL and sometimes to something else?. Can anyone explain what is going on here? What does this deprecation mean?

In AdventureWorks sample, for example, I don't think it means I should do this:

CREATE TABLE [Sales].[Customer]
(
    [CustomerID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    ...
    [rowguid] [uniqueidentifier] $rowguid  NOT NULL,
    ...
);
GO

The above doesn't work obviously. So what is it saying is deprecated? Clearly not the keyword ROWGUIDCOL above?

I thought when something is deprecated that a new syntax must already exist and this would give me time to transition to a form that won't break in "SQL 2017+".

Origin:

Deprecated Database Engine Features in SQL Server 2016


texts: Is the concept of a clustered index in a DB design sensical when using SSDs?

texts:
When designing a SQL server data schema and the subsequent queries, sprocs, views, etc. does the notion of a clustered index and order of data on disk make any sense to consider for DB designs made explicitly to be deployed on SSD platforms?

http://msdn.microsoft.com/en-us/library/aa933131(v=sql.80).aspx
"A clustered index determines the physical order of data in a table."

On a physical disk platform, the design to consider them makes sense to me as a physical scan of the data to retrieve "sequential" rows can be more performant than a seek through the table.
On an SSD platform, all data read access uses an identical seek. There is no concept of "physical order" and data reads are not "sequential" in the sense that bits are stored on the same piece of silicon.

So, in the process of designining an application database is the clustered index consideration relevant to this platform?

My initial thought is that it is not because the idea of "ordered data" doesn't apply to SSDs storage and seek/retreival optimization.

EDIT: I know the SQL Server will create one, I'm just philosophizing about whether it makes sense to think about it during design/optimization.


texts: How to model a Letter Transportation business context in a database diagram?

texts:
I am trying to crate entity-relationship diagram for a transport company database. This database should store information about letters, deliveries, moving letters from one warehouse to another, and historical information. It should allow tracking letters, e.g., checking what letters were on certain date in a specific warehouse.
The process I want to model looks like this:

letter is collected from sender and it goes to warehouse,
then it could be transferred between warehouses,
at last, letter is delivered to recipient.

Business rules

Letters are sent and received by People, one letter can be sent by one particular Person, and also received by a specific Person.
There can be many People in database, if a Person is stored in the database it must be Sender or Receiver of some Letter.
Transports are made between different Warehouses and must consist of many Letters, Letters can be a part of multiple Transports (on different Dates)
Warehouses store multiple Letters
A Letter can be stored in few different Warehouses on different Dates (on ‘01.11’ it is in ‘Warehouse A’, and on ‘02.11’ in ‘Warehouse B’)
DateStorage - contains information about when a Letter has arrived to certain Warehouse
Transports are being done on certain Date, they depart from one Warehouse and go to another.
Letters can be delivered to recipients. If Delivery Attempt fails - there could be more attempts.

Current diagram
This is the database diagram I have created so far:

Relationship between Letters and Warehouses means that when letter is picked up by courier it is put to that warehouse.
Questions and current considerations
Is this diagram correct, what could I improve?
Is it OK that there are to relationship between transports and warehouses (from could be deduced from previous transports and initial warehouse of letter)?


texts: Excluding the standard MySQL databases from replication

texts: I have a prod database, say appdb, and I only care about the data in that db.  Is it safe to declare replicate-ignore-db for each of mysql,information_schema,performance_schema,test, as some tutorials show?  I define my own analytics users on the slave and manage permissions there.  I don't want extra users or tuning artefacts from master, in fact I may tune the slave separately...  However, I cannot recite the perils of replicate-do-db and replicate-ignore-db in my sleep, as some answers here advise, so am wondering what else should I care about in this case!


texts:
I am not from a DBMS background and I couldn't find much help from reading some resources once.
I have a requirement like:  There are 5 interfaces with some execution time. My goal is to find the top 2 interfaces which took the most time.
  RowNum   Interface    TIME
  1        Intf1        0
  2        Intf2        3
  3        intf5        1
  4        Intf2        4
  5        intf3        3
  6        Intf2        2
  7        intf3        5
  8        intf3        2
  9        inft4        2
 10        Intf2        0
 11        intf5        1

The max time taken is 5 which corresponds to Intf3 (so print all intf3  with their times) and the second max time taken is 4 corresponding to  Intf2(so print all intf2  with their times). So my expected output is:
RowNum  Interface   TIME
  7       intf3      5
  5       intf3      3
  8       intf3      2
  4       Intf2      4
  2       Intf2      3
  6       Intf2      2
  10      Intf2      0

I tried something like
select Row,Interface,Time
from (select Row,Interface,Time from NAMES order by Time desc)

but it didn't seem to work as I was expecting.


texts:
I am designing a database for a video game. I have some data structures like {x, y, z, dimension} used across multiple tables.

For instance :

Table player_death

id: int
player_id: int
death_x: int
death_y: int
death_z: int
death_dimension: int

Since {x, y, z, dimension} represents a geographic vector in the video game, a lot of table contain those columns. My question is : should I create a table named vector containing a pool of all the vectors used in my tables (and then use reference to them) or should I keep adding {x, y, z, dimension} columns to each table using a vector ? What is the best practice ? I am a beginner in database design, sorry if the question sounds stupid.


texts: Optimize view (and underlying table) for averaging timestamps into hours

texts:
I have this table:

CREATE TABLE spp.rtprices (
  "interval" timestamp without time zone NOT NULL,
  rtlmp numeric(12,6),
  rtmcc numeric(12,6),
  rtmcl numeric(12,6),
  node_id integer NOT NULL,
  CONSTRAINT rtprices_pkey PRIMARY KEY ("interval", node_id),
  CONSTRAINT rtprices_node_id_fkey FOREIGN KEY (node_id)
      REFERENCES spp.nodes (node_id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
)

And one more relevant index:

CREATE INDEX rtprices_node_id_interval_idx ON spp.rtprices (node_id, "interval");

Against it I've made this view:

  CREATE OR REPLACE VIEW spp.rtprices_hourly AS
  SELECT (rtprices."interval" - '00:05:00'::interval)::date::timestamp without time zone AS pricedate,
  date_part('hour'::text, date_trunc('hour'::text, rtprices."interval" - '00:05:00'::interval))::integer + 1 AS hour,
  rtprices.node_id,
  round(avg(rtprices.rtlmp), 2) AS rtlmp,
  round(avg(rtprices.rtmcc), 2) AS rtmcc,
  round(avg(rtprices.rtmcl), 2) AS rtmcl
  FROM spp.rtprices
  GROUP BY date_part('hour'::text, date_trunc('hour'::text, rtprices."interval" - '00:05:00'::interval))::integer + 1,
           rtprices.node_id,
           (rtprices."interval" - '00:05:00'::interval)::date::timestamp without time zone;

The point of which is to give averages of the numeric columns for each hour (The timestamps have data every 5 minutes). The problem is that a query for a single day for a single node_id takes upwards of 30 seconds for 24 records.

explain analyze select * from spp.rtprices_hourly
where node_id=20 and pricedate='2015-02-02'

Returns this:

   "HashAggregate  (cost=1128767.71..1128773.79 rows=135 width=28) (actual time=31155.023..31155.065 rows=24 loops=1)"
"  Group Key: ((date_part('hour'::text, date_trunc('hour'::text, (rtprices."interval" - '00:05:00'::interval))))::integer + 1), rtprices.node_id, (((rtprices."interval" - '00:05:00'::interval))::date)::timestamp without time zone"
"  ->  Bitmap Heap Scan on rtprices  (cost=10629.42..1128732.91 rows=2320 width=28) (actual time=25071.410..31153.715 rows=288 loops=1)"
"        Recheck Cond: (node_id = 20)"
"        Rows Removed by Index Recheck: 7142233"
"        Filter: (((("interval" - '00:05:00'::interval))::date)::timestamp without time zone = '2015-02-02 00:00:00'::timestamp without time zone)"
"        Rows Removed by Filter: 124909"
"        Heap Blocks: exact=43076 lossy=82085"
"        ->  Bitmap Index Scan on rtprices_node_id_interval_idx  (cost=0.00..10628.84 rows=464036 width=0) (actual time=68.999..68.999 rows=125197 loops=1)"
"              Index Cond: (node_id = 20)"
"Planning time: 5.243 ms"
"Execution time: 31155.392 ms"


texts: How to limit select output from sqlplus?

texts:
I stop using SQL Developer as found more convenient to use SQL Plus inside Emacs SQLi mode.

SQL Developer on selects with large amount of rows show only few rows. But SQL Plus tend to dump all rows that match conditions. I can put ROWNUM <= N but I often forget to do this.

Is there any option for SQL Plus to limit it output from select statement? I will put it to /opt/instantclient/glogin.sql in order to apply to each sesstion...


texts:
I've been working for a while with PostgreSQL and I managed to find out a way to use pg_rewind function in order to resynchronize an old master after a new promoted master (old standby). (using repmgr for HA clustering);

Currently I use a command similar with:

postgres –single -D -c archive_mode=on -c archive_command=false

in order to access singleusermode.

The next step is to make a checkpoint entering in opened terminal given by previous command: "checkpoint".

My question is the following. How do I pass "checkpoint" as I don't know "command" maybe in order to implement a script to do this automatically.


texts: I'm looking for recommendations for PostgreSQL training (on-site, off-site, remote) that covers administration, performance optimization, and clustering solutions like Slony and pgpool.  Finding courses via web searches hasn't gotten me very far as it seems many of the linked pages are out of date, and where they are updated, I'm not sure how to judge the quality of the course they offer.  Does anyone have recommendations for courses they took, had colleagues take, or themselves teach?


texts: One node causing entire Galera Cluster to remain in bad state

texts: I have a production MariaDB Galera 5 node cluster that has been running for about 8 months at our factory. One node had a powerfail / un-graceful shutdown (Ubuntu 16.04) and since then if that node is up, it causes the entire cluster to permanently respond with "wsrep has not yet prepared node for application use". If I shut down that node and restart the cluster, all is fine. My suspicion is the ungraceful shutdown caused some file system issues that cause the entire cluster to be unusable. Just curious if anyone else has seen this.


texts:
In mysqld.log I have thousands of lines like:

Table mysql/innodb_index_stats has length mismatch in the column name table_name.  Please run mysql_upgrade

So, according to virtually every post on the matter (and error msg itself):

mysql_upgrade -u root -p

Enter password:
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
...

and all tables in all databases are like

...
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
The sys schema is already up to date (version 1.5.1).
Checking databases.
...

But, I am still getting the error in the log

2019-01-27T18:37:15.304587Z 66190 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2019-01-27T18:37:15.304620Z 66190 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2019-01-27T18:37:15.304684Z 66190 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2019-01-27T18:37:15.304707Z 66190 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name.  Please run mysql_upgrade

The structure appears to be okay.  Is there a config issue?  Please advise.

The answer appears to be - reboot server.  I do not have rights to do this, but after admin rebooted, issue appears to be resolved.  Not sure why just rebooting MySQL did not fix?


texts:
I'm deploying a 12C dataguard on oracle.
When performing all procedures, start the instance in the standby, as nomount. I noticed that the standby listener is as BLOCKED status, making it impossible for connections in the database to make Duplicate through rman, it does not allow because of the blocked status of the listener.

Can someone help me?


texts: Error 10054 encountered exporting localhost SQL Server to .bacpac

texts:
I am trying to export a localhost SQL Server database to a .bacpac file, and am encountering

  error 10054: "A transport-level error has occurred when receiving
  results from the server... An existing connection was forcibly closed
  by the remote host".

Is there a setting I need to change to fix this issue?


texts:
We have a user table :

  CREATE TABLE `User` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(60) NOT NULL,
      `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `status` tinyint(4) NOT NULL DEFAULT '0',
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

I need to run a group by query where I need  a count of users with different status values (0, 1, 2, 3)

I tried following query :

select status, count(*)
from User
where
status in (0, 1, 2, 3)
group by status

this gives output for the status values present in the table e.g.

|    status            | count(*) |
|        0             |  10      |
|        1             |  11      |

but if the database does not contain let's say users with status values  = 2 or 3 these records don't appear with a count 0.

How do I go about this one ?


texts: How does the data move within a filegroup for its respective secondary data files

texts:
I am confused or do not have better understanding on how SQL works in below scenario and need some views:

For a database X, we have a FILEGROUP named FG which has 4 data files tagged to it as below

1. FG_data1.ndf which is restricted to 200 GB and current free space is 0 GB

2. FG_data2.ndf which is restricted to 200 GB and current free space is 10 GB

3. FG_data3.ndf which is restricted to 200 GB and current free space is 0 GB

4. FG_data4.ndf which is restricted to 200 GB and current free space is 60 GB

Apart from above we have PRIMARY FG with mdf data file set to unrestricted growth. All are set to auto grow by 10 GB

Now, i see alert for Filegroup FG being FULL and rebuild index job failing with same saying "could not allocate space for object within the table tb1 for the database above because the Filegroup is FULL"

But if i see from above data files tagged to FG, we still have 70 GB left, then how come its failing ?

Please suggest how SQL works here in allocating new data for case above?

Edit

  tb1 info

  This tb1 is partitioned and have 366 days of partitions for yearly
  defined scheme

  when queried for sys.dm_db_index_physical_stats i see Clustered index
  listing for each partition and some have avg frag value over 50 and
  some partition index below 30-20. How do i know whats going on in the
  background?

only 1 index that is clustered

name    rows         reserved        data         index_size       unused
tb1    433785377    445952328 KB    444075128 KB    1800504 KB  76696 KB


texts: Delete many rows from a large percona mysql db

texts:
I need a fresh opinion on the case. Any thoughts are appreciated.

Input: we have a huge percona mysql (5.5) database that takes a couple of Tb (terabytes). Tables on innodb engine.
More than a half (2/3) of that size should be deleted as quick as possible.
Also we have master-slave configuration.

As the quickest way to achieve that I am considering the following solution:
Execute for each table on the slave server (to avoid production downtime) :

Stop replication
Select the rows NOT to be deleted into an empty new table that has the same structure as the original table
Rename original table to "table_old", new table - to correct name
Drop the original table "table_old"
Start replication

The problem is that we have a lot of FK constraints. Also I am afraid to break the replication during this process.

Questions:
1) What the potential problems can be with FK constraints in this solution?
2) How do not break replication?
3) Opinions? Alternative solutions?

Thank you in advance.


texts:
I read somewhere online and in a Microsoft Press book there is a field declaration attribute that can be used to indicate NULL VARCHAR values should not consume space at the leaf-level instead of consuming a few characters per leaf.

Could someone help me identify where that attribute is?

I cant remember what the Attribute keyword is but I remember seeing it because of the value of saving the empty 2 bytes and make it a 0 byte storage for no data.


texts: SQL Server Integration Services 2012 - Legacy deployment

texts:
I'm currently reading a lot about SSIS and I have read that there are now two models for deploying a solution : the one using "Projects" and the one using "Packages".
Starting SQL Server 2012, a database generally called SSISDB can be created as the SSIS catalog.
My question is the following: let's say that I have created the SSISDB but that I will use the legacy deployment model, will the package I deployed be found in the SSISDB ?

Thanks in advance.

Jeff.


texts: How to measure or estimate the streaming replication lag in Postgres 9.2

texts:
I've been asked to reverse engineer an app that uses postgresql in the back end.
I can see that there is some database replica
tion going on... based on my reading, I *think it's called streaming replication.
The settings on the master server look like this:

wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32

My question is this:  what impacts the delay between creating a new record on the master, and when it shows up / is replicated to the slave?

From reading the manual, (http://www.postgresql.org/docs/current/static/hot-standby.html) I see that the model it uses is "eventual consistency"... paragraph 4 on that page states:

  The data on the standby takes some time to arrive from the primary
  server so there will be a measurable delay between primary and
  standby. Running the same query nearly simultaneously on both primary
  and standby might therefore return differing results. We say that data
  on the standby is eventually consistent with the primary.

But is there any pattern / ways to guesstimate how long it'll take?  Or is it really just arbitrary?

If you can point me in the right direction, I'd appreciate it.
Thanks.


texts:
This question popped into my head after reading this article.

However, the more I read about this topic,  the more it seems like a Dynamic SQL security issues. So i have 2 questions mainly:

Is there really any difference between this and a Dynamic SQL query that allows injections?
Why would there be a language like HQL that goes through Hybernate (MySQL) to get translated to SQL while using inconsistent character escape marks? It seems counter intuitive. Maybe i missed something.


texts: Linked Server - Map Windows Group to remote login

texts:
I have a MySQL server that I need to set up as a linked server in SQL Server 2014.

I need a login from the SQL Server (which is a Windows group) to impersonate a username at the MySQL end.

I can see from Googling that this cannot be done.

I can create a new Windows login for every user in the Windows Group and map them, but was wondering if there is a slicker way of doing this?

I am not an admin of the MySQL server so cannot make any config changes there.


texts: Can a Table Lock eliminate a deadlock or make it worse Oracle

texts:
We have a situation in which an Oracle Database is having deadlocks issues.

I said that perhaps we should lock into some table locks, and a guys response to me was

  "Unless I am misunderstanding what you mean by a table lock, I don’t think a table lock will eliminate deadlock … it could potentially exacerbate it."

Is what he is saying true?

Error we get: "Deadlock detected while waiting for resource Stack Trace"

Here is most of email on the error that we get:

System Error: Error for Step Type:REMOVE PRED OPEN MAN User Error:
ORA-00060: deadlock detected while waiting for resource Stack Trace:

Timestamp 2015/08/21 14:02:39:130363000130363000
dbms_utility.format_error_backtrace:
ORA-06512: at "OM.FOLDER_PKG", line 2661
ORA-06512: at "OM.DOC_LOG_STEP_PKG", line 2436
dbms_utility.format_error_stack:
ORA-00060: deadlock detected while waiting for resource

UPDATE:

This is PROBABLY useful, this is from a developer that is familiar with this happening every few months of which NO DBA at the company seems to be able to be able to figure it out.

This is what the main developer on this project said:

Unless I am misunderstanding what you mean by a table lock, I don’t think a table lock will eliminate deadlock … it could potentially exacerbate it.

e.g.

Transaction 1

Locks table X at 10:00:00 (all times are just examples)
Attempts to lock table Y at 10:00:02. DEADLOCK – transaction 2 is waiting on transaction 1 to unlock table X and transaction 1 is waiting on transaction 2 to unlock table Y

Transaction 2

Locks table Y at 10:00:00
Attempts to lock table X at 10:00:01. Blocks waiting for transaction 1 to release the lock

Can replace the word ‘table’ with ‘record’. Problem remains the same. Think lower granularity of table instead of record could make the problem worse.

We can handle the issue by:

Locking tables in a consistent order  - this is difficult to do because we have many thousands of lines of code we’d need to make sure that we’d be calling things in the same order. It would also lead to uglier code where natural processing flow would have been changed simply to avoid deadlock.
Locking all top level transactions on a single object at the very start (they can then lock sub-tables/ objects to their hearts content). The natural object on the folder side to do this one would be a WORK_FOLDER.
Rolling back on deadlock and retrying – we have already implemented this but it only does one retry and sometimes Oracle is taking too long to detect that the deadlock occurs. And occasionally that one retry is not enough.


texts: Why am I getting error 1064 when creating a table?

texts:
Here is my DDL statement:

create table if not exists 'fmrecruit'.'resource_type'(
  'ID' bigint not null auto_increment,
  'type' bigint null,
  'first_name' varchar(30) not null,
  'middle_name' varchar(30) null,
  'last_name' varchar(30) null,
  'org_name' varchar(45) null,
  'notes' varchar(256) null,
  'primary_skills' varchar(256) not null,
  'seconadary_skills' varchar(256) null,
  'created_by' varchar(25) not null,
  'created_date' timestamp not null,
  'updated_by' varchar(25) null,
  'updated_date' timestamp null,
  primary key('ID'),
  index resource_type_fk_idx('type' asc),
  constraint resource_type_fk foreign key('type') refrences fmrecruit.resource_type('ID')
  on delete no action
  on update no action)
  engine=innodb;


texts: whitelist for allowed user queries in postgres

texts:
Looking for a way to limited a user to a specific set of queries (i.e. a whitelist).

I currently need this as an extra security measure for a client app.

Is there a way to enable a table of "user allowed queries"? Anything that doesn't meet the regex pattern in that table (or however it works) receives a FATAL error.

UPDATE for clarity:

The client app only has 50 or so queries that ever execute, over and over again. So the list of expected queries is known. The only thing that changes between them is some values for id attribute in the WHERE clause.

For instance, I count these two select statements as 1 of the "50" (i.e. it's the same query, just the x value is different):

SELECT a, b, c
FROM table_a
WHERE x = 1;

The next query may be:

SELECT a, b, c
FROM table_a
WHERE x = 2;

What I'd ideally like to enforce is that all queries against the database should include a particular condition in the WHERE clause, i.e. organisation_id = x where the value of x would change depending on the user running the query.. but organisation_id exists in every table, and every query should contain this clause.


texts:
When I import some data from excel file to a table that is part of a merge replication, the data is imported successfully to the table, but those new records are not synced to subscribers that belong to the merge replication topology!

Is this by default?

Am I missing some configuration ?


texts: How to export data from a SQL Server database with hundreds of tables and timestamp columns?

texts:
I have a SQL Server database with hundreds of tables and they all have timestamp columns. I don't have access to backups or the server's file system. To use the export data wizard, I have to go into each table's mapping and set the timestamp column destination to Ignore. It's very time consuming. Using SSMS 2017 (17.3).

Any ideas on how to export the data with minimum effort?


texts: MySQL - GROUP BY not working with multiple CASE WHEN statements

texts:
I am working on a database with Kickstarter database, and I'm trying to:

Define short, medium-lengthed, and long campaigns
See how much each length of campaign raise
Convert different currencies into USD

My original code is as follows:
SELECT
CASE
    WHEN DATEDIFF(campaign.deadline,campaign.launched)<=30 THEN 'Short Campaign'
    WHEN DATEDIFF(campaign.deadline,campaign.launched)>30 AND DATEDIFF(campaign.deadline,campaign.launched)<=60 THEN 'Mid-lengthed Campaign'
    ELSE 'Long Campaign'
    END AS 'Campaign Length',
CASE
    WHEN currency.name='GBP' THEN ROUND(SUM(campaign.pledged)*0.80,2)
    WHEN currency.name='CAD' THEN ROUND(SUM(campaign.pledged)*1.36)
    WHEN currency.name='AUD' THEN ROUND(SUM(campaign.pledged)*1.43)
    WHEN currency.name='NOK' THEN ROUND(SUM(campaign.pledged)*9.28)
    WHEN currency.name='EUR' THEN ROUND(SUM(campaign.pledged)*0.87)
    WHEN currency.name='MXN' THEN ROUND(SUM(campaign.pledged)*22.48)
    WHEN currency.name='SEK' THEN ROUND(SUM(campaign.pledged)*0.04)
    WHEN currency.name='NZD' THEN ROUND(SUM(campaign.pledged)*1.53)
    WHEN currency.name='CHF' THEN ROUND(SUM(campaign.pledged)*0.94)
    WHEN currency.name='DKK' THEN ROUND(SUM(campaign.pledged)*6.52)
    WHEN currency.name='HKD' THEN ROUND(SUM(campaign.pledged)*7.75)
    WHEN currency.name='SGD' THEN ROUND(SUM(campaign.pledged)*1.39)
    WHEN currency.name='JPY' THEN ROUND(SUM(campaign.pledged)*107.11)
    ELSE ROUND(SUM(campaign.pledged),2)
END AS 'Amount Raised'
FROM campaign
LEFT JOIN currency ON currency.id=campaign.currency_id
GROUP BY `Campaign Length`;

I'm expecting a result of:
Campaign Length.      ---- Amount Raised
Short Campaign.       ---- XXXXXXXXXXXXX
Mid-Lengthed Campaign ---- XXXXXXXXXXXXX
Long Campaign.        ---- XXXXXXXXXXXXX

Where XXXXXX is the aggregate of the pledged amount after conversion. The original code shows:
Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ks_data.currency.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

So I improvised by adding additional GROUP BY, after realizing I can't GROUP BY Amount Raised:
SELECT
CASE
    WHEN DATEDIFF(campaign.deadline,campaign.launched)<=30 THEN 'Short Campaign'
    WHEN DATEDIFF(campaign.deadline,campaign.launched)>30 AND DATEDIFF(campaign.deadline,campaign.launched)<=60 THEN 'Mid-lengthed Campaign'
    ELSE 'Long Campaign'
    END AS 'Campaign Length',
CASE
    WHEN currency.name='GBP' THEN ROUND(SUM(campaign.pledged)*0.80,2)
    WHEN currency.name='CAD' THEN ROUND(SUM(campaign.pledged)*1.36)
    WHEN currency.name='AUD' THEN ROUND(SUM(campaign.pledged)*1.43)
    WHEN currency.name='NOK' THEN ROUND(SUM(campaign.pledged)*9.28)
    WHEN currency.name='EUR' THEN ROUND(SUM(campaign.pledged)*0.87)
    WHEN currency.name='MXN' THEN ROUND(SUM(campaign.pledged)*22.48)
    WHEN currency.name='SEK' THEN ROUND(SUM(campaign.pledged)*0.04)
    WHEN currency.name='NZD' THEN ROUND(SUM(campaign.pledged)*1.53)
    WHEN currency.name='CHF' THEN ROUND(SUM(campaign.pledged)*0.94)
    WHEN currency.name='DKK' THEN ROUND(SUM(campaign.pledged)*6.52)
    WHEN currency.name='HKD' THEN ROUND(SUM(campaign.pledged)*7.75)
    WHEN currency.name='SGD' THEN ROUND(SUM(campaign.pledged)*1.39)
    WHEN currency.name='JPY' THEN ROUND(SUM(campaign.pledged)*107.11)
    ELSE ROUND(SUM(campaign.pledged),2)
END AS 'Amount Raised'
FROM campaign
LEFT JOIN currency ON currency.id=campaign.currency_id
GROUP BY `Campaign Length`,currency.id;

But the result is instead not grouping as I intended:
Campaign Length.      ---- Amount Raised
Short Campaign.       ---- XXXXXXXXXXXXX
Mid-Lengthed Campaign ---- XXXXXXXXXXXXX
Long Campaign.        ---- XXXXXXXXXXXXX
Mid-Lengthed Campaign ---- XXXXXXXXXXXXX
Mid-Lengthed Campaign ---- XXXXXXXXXXXXX
Short Campaign.       ---- XXXXXXXXXXXXX

I've tried many ways, but couldn't find a solution


texts: Update with default values in case clause

texts:
I have a single table like

CREATE TABLE Mytable (ID int identity, Name nvarchar(10));
GO
INSERT INTO MyTable (Name) VALUES ('test1');
INSERT INTO MyTable (Name) VALUES ('test2');
GO
ALTER TABLE MyTable ADD CONSTRAINT DF_Name DEFAULT('test') FOR Name;

Now I want to update my Name column like this:

Update MyTable
set name = case ID when 1 then DEFAULT END;

But I am getting this error:

  Incorrect syntax near the keyword 'DEFAULT'

How can I use UPDATE with DEFAULT statement in the CASE clause?


texts: Cannot connect to mysql server error for master replication

texts: Am trying to connect to a mysql master which is a server running LAMP. It replies with an error "cannot connect to mysql server". Have also tried to connect using mysql -h XXX -u -p from the slave server but still replies the same error. On the LAMP server have also checked the iptables and allowed mysql port to allow incoming connection but still have the same error. Have also allowed my slave public ip but the error is just the same.


texts: I am trying to log into SSRS with SSMS to make a custom role. I am using my domain admin account that is able to log into the database engine side of things fine but when I try to go to reporting services I get "The username or password is not valid or you do not have permissino to access this server"


texts:
I get such errors when using these functions, what's is wrong?

pgdb=# SELECT distance('foo','zoo');
ERROR:  parse error - invalid geometry
LINE 1: SELECT distance('foo','zoo');
                        ^
HINT:  You must specify a valid OGC WKT geometry type such as POINT, LINESTRING or POLYGON
pgdb=#
pgdb=#
pgdb=# SELECT difference('foo','zoo');
ERROR:  parse error - invalid geometry
LINE 1: SELECT difference('foo','zoo');
                          ^
HINT:  You must specify a valid OGC WKT geometry type such as POINT, LINESTRING or POLYGON

May be these functions are not installed at all?


texts: Error creating table (Error in your sql syntax). What am I doing wrong?

texts:
I'm trying to create a data base, but I get this error:

  ERROR 1064 (42000) at line 37: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
  CONSTRAINT Users_Manager_ID_PK PRIMARY KEY(Manager_ID, Employee_ID))' at line 6

CREATE TABLE Customer
(
Customer_ID INTEGER(8) PRIMARY KEY,
f_Name VARCHAR(25) NOT NULL,
l_Name VARCHAR(25) NOT NULL);

CREATE TABLE Users
(
Manager_ID INTEGER(8) NOT NULL,
Employee_ID INTEGER(8) NOT NULL,
f_Name VARCHAR(25) NOT NULL,
l_Name VARCHAR(25) NOT NULL),
CONSTRAINT Users_Manager_ID_PK PRIMARY KEY(Manager_ID, Employee_ID));

CREATE TABLE Advertising
(
Sales_Person VARCHAR(25) PRIMARY KEY,
CONSTRAINT Advertising_Manager_ID_FK FOREIGN KEY(Manager_ID) REFERENCES Users(Manager_ID)),
CONSTRAINT Advertising_Employee_ID_FK FOREIGN KEY(Employee_ID) REFERENCES Users(Employee_ID));


texts:
I need to make a report of 3 differences tables.

I tried something but I have an error.

Here my structure:
http://sqlfiddle.com/#!9/7c0e3a

I need something like this:

|--------|---------------|-----------|------------|---------------|-------------|----------|-------------|-----------|
| RAT_Id | RAT_Name      | RAT_Price | booking_nb | booking_total | booking_pct | night_nb | night_total | night_pct |
|--------|---------------|-----------|------------|---------------|-------------|----------|-------------|-----------|
| 1      | April Special | 125.00    | 2          | 1             | 50          | 3        | 4           | 75        |
|--------|---------------|-----------|------------|---------------|-------------|----------|-------------|-----------|

Here what I tried but I have an error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':hotel_id ORDER BY t.RAT_Id ) q LEFT JOIN ( SELECT r.RAT_Id , COUNT(' at line 17:

SELECT q.RAT_Id
, q.RAT_Name
, SUM(IF(c.RAT_Id=q.RAT_Id,c.booking_nb,0))     AS booking_nb
, SUM(c.booking_nb)                             AS booking_total
, SUM(IF(c.RAT_Id=q.RAT_Id,c.booking_nb,0))
    * 100.0 / SUM(c.booking_nb)                 AS booking_pct
, SUM(IF(c.RAT_Id=q.RAT_Id,c.night_nb,0))       AS night_nb
, SUM(c.night_nb)                               AS night_total
, SUM(IF(c.RAT_Id=q.RAT_Id,c.night_nb,0))
* 100.0 / SUM(c.night_nb)                       AS night_pct
FROM ( SELECT t.RAT_Id
    , t.RAT_Name
    FROM ___Rates t
    WHERE t.RAT_HotelId = 'cus_AjGG401e9a840D'
    ORDER BY t.RAT_Id
) q
LEFT
JOIN (
    SELECT r.RAT_Id
    , COUNT(DISTINCT b.BOO_Id) AS booking_nb
    , COUNT(DISTINCT d.BIL_Id) AS night_nb
    FROM ___Rates r
    JOIN ___Bookings b
    ON d.BIL_RateId = r.RAT_Id
    JOIN `___BillableDatas` d
    ON d.BIL_BookingId = b.BOO_Id
    AND d.BIL_Date BETWEEN "2017-01-01" AND "2017-12-31"
    AND d.BIL_Type="Night"
    WHERE r.RAT_HotelId = 'cus_AjGG401e9a840D'
    GROUP
    BY r.RAT_Id
) c
ON 1=1
GROUP
BY q.RAT_Id
, q.RAT_Name
ORDER
BY night_nb DESC, q.RAT_Name ASC


texts: Efficient way to keep a count of entries within a timeframe? e.g. How many actions did user do in the last 7 days

texts:
I track user events - say, clicks - and would like to show them how often they've clicked in the last week, last 30 days, etc. So of course, as time progresses, that number needs to be updated.
Here is my naive attempt at just computing the number in a query:
Table format:
table_name
timestamp | timestamp with time zone
user_id | integer

Each row in the table represents another instance of the user event being logged.
The query I have right now:
SELECT count(*) FROM table_name WHERE user_id = 1
  AND timestamp >= NOW() - '7 days'::interval;

This works fine, especially with a bit of caching thrown in, but as someone who doesn't understand much about database internals, I have to wonder if there isn't a more efficient way than having to do a SELECT with a date comparison filter every time. Neither column here is indexed. So I'm curious, is there a better way? Like some special data type or something. Are there any problems with running a query like this frequently as the number of entries increases dramatically? Or is this actually the most common way to do it?


texts: We are taking COPY_ONLY (non-truncating) transaction log backups every 2 hours. Our requirement is to take the incremental log backup every 2 hours and not cumulative. I did not find an option in BACKUP LOG TSQL command. Is there a way to do this?


texts: InnoDB foreign key and index duplication?

texts:
I'm have a part table like this:

    part_id            INT
    manufacturer_id    INT
    supplier_id        INT
    part_number        VARCHAR(50)
    part_description   VARCHAR(120)

with foreign keys:

    CONSTRAINT fk_supplier_to_part FOREIGN KEY supplier_id REFERENCES supplier.supplier_id
    CONSTRAINT fk_manufacturer_to_part FOREIGN KEY manufacturer_id REFERENCES manufacturer.manufacturer_id

and indexes:

    KEY ix_supplier_id (part.supplier_id)
    KEY ix_manufacturer_id (part.manufacturer_id)
    KEY fk_supplier_to_part (part.supplier_id)
    KEY fk_manufacturer_to_part (part.manufacturer_id)

My question is, are the ix_supplier_id and ix_manufacturer_id indexes required? I know that a foreign key needs a 'matching index' but does the index need to have the same name as well as matching columns?


texts: InnoDB: Could not find a valid tablespace file for ' table '?

texts:
i have some project hosted by wampp server, which used to work without any issue.
this week , it can't serve anymore , examining  the mysql logs i got this lines:
2019-12-05 08:23:00 1464  InnoDB: Operating system error number 3 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
 2019-12-05 08:23:00 5320 [ERROR] InnoDB: Could not find a valid tablespace file for 'wordpressdev/b2s_post_sched_settings'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
 2019-12-05 08:23:00 5320 [ERROR] InnoDB: Tablespace open failed for '"wordpressdev"."b2s_post_sched_settings"', ignored.

how could i fix ?
update
i figured out that , that error comes because i have moved out wordpressdev folder from mysql 's data directory.
after putting it back the errorb change to :
2019-12-06 08:44:04 8084 [Note] InnoDB: Highest supported file format is Barracuda.
2019-12-06 08:44:04 1fb4  InnoDB: Operating system error number 5 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory. It may also be you have created a subdirectory
InnoDB: of the same name as a data file.
2019-12-06 08:44:04 8084 [ERROR] InnoDB: Could not find a valid tablespace file for 'wordpressdev/b2s_posts'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2019-12-06 08:44:04 8084 [ERROR] InnoDB: Tablespace open failed for '"wordpressdev"."b2s_posts"', ignored.

by googling i found that i need to give the full access permission to the data folder for the users , i did that but the problem persist


texts: How to get the dependent entities (referencing entities) for an User Defined Function (UDF)

texts: We have a database that has been copied, including stored procedures and some user defined functions.  I'd like to check whether a UDF is being used.  Is there a way to see which stored procedures call the UDF?


texts: Select one single row from table b for each row from table a

texts:
I have two tables:

A(name VARCHAR2(20), type VARCHAR2(20))

and

B (type VARCHAR2(20), ... (20 fields more))

I need to select a single row from B for each distinct name,type from A taking into account that table b contains millions of records and my current select runs for 20min+

SELECT *
FROM   (SELECT a.*,
               Row_number()
                 over (
                   PARTITION BY a.TYPE
                   ORDER BY a.TYPE) rn
        FROM   tablea a,
               tableb b
        WHERE  a.name = 'SEARCHED_NAME'
               AND b.TYPE = a.TYPE)
WHERE  rn = 1


texts:
Working on a POC running on Azure and trying to get familiar with DocumentDB especially in regards to best practices/configuration.   In our POC we have a very simple JSON doc with weather information and working on services to pull data back.  Here's the issue but not sure if it's our mechanism for returning data or if I need to increase the performance level of the database (didn't want to start here as it seems like throwing hardware at a problem which may be resolved in other ways).

The performance issue that I came across was regarding the indexing of dates.  Our web service method GetByDate needs to be able to form a range query to bring back all of the documents within a range of dates.  I added a range index to the ReadOn column to DocumentDB, which worked but at a significant performance hit.  Apparently some people are storing their dates as integers, others are storing both the string representation of the date and the integer, but only indexing the integer.

So hoping to hear recommendations for either modifications for how we are trying to query the database or if the only answer is to increase the performance level/scale out our DocumentDB even though it only has 5-10k records in it.


texts: postgresql: grouping with json columns, eliminating nulls

texts:
I have a postgres query that returns a result set simplified as follows, with an id column and two json columns.

my_id     |  col_a       |  col_b
(integer) |  (json)      |  (json)
----------------------------------------
 5001        ["a", "b"]     <NULL>
 5001        <NULL>         ["c", "d"]

My question is: what is the best way in postgres to aggregate this result? e.g.:

my_id     |  col_a       |  col_b
(integer) |  (json)      |  (json)
----------------------------------------
 5001        ["a", "b"]     ["c", "d"]


texts: What will happen if master goes down in peer to peer replication?

texts: In case of Peer to peer replication, if Master goes down, will the other two nodes continue synchronizing or not?


texts: how to set "result to text" with a tsql statement in sp?

texts:
is there any way to set "result to text" within of SP while this execute a process? a tsql stament?

Thanks.


texts: Create dump file using Oracle SQL Developer

texts:
I think I'm missing a simple point, but I couldn't find a way to use Oracle export (exp/expdp) and Oracle import (ipm/impdp) with Oracle SQL Developer (version 1.5.5)? Is there a way for it?

If possible, how can I make it point different Oracle export or import utilities (which are located different places and have different versions i.e. 10g , 11g)?


texts:
I have an Oracle DB working in production for more than a year.
I have my listener working:

[oracle@base admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                02-NOV-2016 14:22:38
Uptime                    1 days 20 hr. 3 min. 13 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /o/app/oracle/diag/tnslsnr/base/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=base)(PORT=1521)))
Services Summary...
Service "BASE" has 1 instance(s).
  Instance "BASE", status READY, has 1 handler(s) for this service...
Service "BASEXDB" has 1 instance(s).
  Instance "BASE", status READY, has 1 handler(s) for this service...
The command completed successfully

But I have no listener.ora:

ls /o/app/oracle/product/DB_11.2_SE/dbhome_2/network/admin/
samples  shrept.lst  tnsnames.ora

This is the content of my tnsnames.ora:

$ cat tnsnames.ora
BASE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.20.19)(PORT = 1521))
      (LOAD_BALANCE = yes)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = BASE)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.20.20)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

How is this possible? As far as I know, for the listener to work, I have to have a listener.ora file.

I have another Oracle database installed, and if I execute lsnrctl status, in the output I can see a line containing Listener Parameter File   /o/app/oracle/product/11.2/dbhome/network/admin/listener.ora, and I am not seeing it here. Also I have executed locate listener.ora, and nothing.