texts: Import .frm and .ibd files into a new instance of MySQL

texts:
I have the .frm and .ibd files from an old, non-working MySQL server (on Windows).  I took out the hard drive and was able to get the data directory copied to my machine.

How would I get them into MySQL on my development machine? I tried just copying the directory into the MySQL data directory, but that didn't work.

The old database is v5.5.4, and my version is v5.7.15. I'd be happy to install a matching MySQL version if that would help.

I saw Restoring MySQL Tables from .ibd, .frm and mysqllogbin files, but this database has over 800 tables in it - way too much for that process.


texts:
Just need a little help as new to SQL.

I have a table which contains readingtime (records every 4 seconds during the day) and an energy reading. I wanted to know how much energy was used in a day.
this is the query that i used

SELECT date(readingtime), max(energy)-min(energy)as total
FROM lights_1
WHERE readingtime between 20140407000000 and 20140409235959
GROUP BY date(readingtime) LIMIT 0,30;

This gave me the desired results for 1 table. I have 4 tables of these lights_2, Lights_3, lights_4 and i would like to display them together and hence match up the reading time so each day has the energy requirements for all for tables with the total. When i try and join tables i get a lot of errors!

Can anyone help.

thanks

Union gives

date(readingtime) total
2014-04-07        1244
2014-04-08        1039
2014-04-09        1389
2014-04-07        419
2014-04-08        1102
2014-04-09        482

I would like

date(readingtime) total_1  total_2
2014-04-07        1244      419
2014-04-08        1039      1039
2014-04-09        1389      1389


texts: Changing the column width in Transactional Replication

texts:
Changing the column width in SQL server Transaction Replication.
Can we do direct width change ? or we need to takeout the article and modify.
This change replicate to subscriber? or we need to do manually at subscriber


texts: Why doesn't time for a simple update scale linearly with the number of records?

texts:
I am running some performance tests and I started with a dataset of 900.000 records the update took 10 seconds or about 90000 records per second. Then I increased the number of records to 9.000.000 and the update took 170 seconds or about 52941 records updated per second.

Why does the number of records updated per seconds drop when the total number of records increases?

The distribution in the data is the same, the larger set is the smaller set copied 10 times. The size of the data and log files did indeed increase since I copied the data 10 times. I did shrink the log file after that. There is no other workload on the database server at this moment.

The stored procedure doing the update is the following:

CREATE PROCEDURE updatePoints
    @ids NVARCHAR(MAX)
AS
    DECLARE @points1 INT
    DECLARE @points2 INT

    SET @points1 = 1
    SET @points2 = 2

    UPDATE p
    SET points = (0 + CASE WHEN p.[x] = m.[x] AND p.[y] = m.[y]
                              THEN @points1
                              ELSE 0
                      END +
                      CASE WHEN p.[u] = m.[u] AND p.[v] = m.[v]
                              THEN @points2
                              ELSE 0
                      END)
    FROM p
    JOIN  m ON m.id = p.mid
    JOIN platform.Split(@ids) i ON i.Value = m.id
    WHERE m.[status] = 1


texts: The best practice for SQL + NoSQL database design

texts:
I’m in process of designing database architecture for my diploma project and I have second thoughts about different solutions.

I know and understand all my objectives can be done within  relational database, but having in mind thousands of users using the app, where data changes dynamically, create the question: should I proceed with both sql and nosql database in parallel?

So the app is based on relational db (sql server), but there are parts of the software where user can change data very dynamically: notes, handbook, kanban board, invoicing. What I worry more about than only changing the data, is creating new and deleting old (in that case there is no need to store duplicates of notes or revisions of invoices) and not ending up having tables with millions of records.

Additional question: is it okay to store different users fragile data (like notes) in one table, based on FK(user_id)?

I would love to hear your comments on it based on your experience. I tried with good practices on google but with no big luck.

Please advise or provide good book title for that matter.


texts: MySQL Character set and Collation Issue.?


texts:
I was in trouble with deleting the users in mongodb3.6.

I created a user with:

db.createUser({ user: "username" , pwd: "password", roles: ["root"]})

And I deleted it with:

db.dropUser("username")

Also in mongodb.conf I commented :

auth = true

However, afer restarting mongo, I tried to check the dbs and I got an error like this:

How could I solve this problem?


texts: Move values from one table to another one via stored procedure in sql server 2008

texts:
I have two tables

Login (userid, username, password)
audit_login (userid, Transaction_Date, Status (success or failed)

Now I want create a stored procedure in which, if anyone wants to login then their userid, the Transaction_Date and Status (success or failed) details should automatically come into another table audit_login.

I will then want to know how many times a login was successful.


texts: Database state set to an earlier state before creating new indexes

texts:
This is a remote server (SQL Server 2008).

We are trying to get benchmarking of some stored procedures. One stored procedure was taking avrg 44 seconds, we have checked this approx 20 times for a span of two weeks avrg was 44 seconds.

SQL Profiler suggested a missing index, we created the suggested index for checking the performance. The execution time reduced significantly after indexing.

We dropped the newly created index. Rebuild index was not performed, as we assumed drop index should rebuild the index for the table.

Client is also testing on same db. They also felt a performance gain in this particular operation. They are asking the cause for this gain, we have conveyed the above process, but index benefit can also be verified  with older DB state, therefor we need to reset the data base to earlier state where it was taking 44 seconds.

Database Engine can not be restarted and Offline/Online options can also not be performed as "Alter Any Database" permission can not be granted.

We are looking on below command.

CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

Permissions
Requires ALTER SERVER STATE permission on the server.

USE <<db Name>>
GO
GRANT ALTER TO your_user
GO

Need help to understand the possible cause behind this behaviour?

Does DROPCLEANBUFFERS also clear the index cache?


texts: Executing a table type parameter stored procedure or function within a select clause

texts:
Just a quick question. I have a stored procedure that takes two parameters, a varchar (table name) and a tabletype (range of primary key ID values), and uses them to return all FK connections to and from the table for the provided ID's.

The usage is tricky though, since you actually need to declare the tabletype, select the ID's into it, and then execute the procedure like so:

DECLARE @IDVALS SearchObjRef_Type

INSERT INTO @IDVALS
SELECT TOP 10 RAND_ID FROM RANDTABLE

EXEC usp_SearchObjRef @TABLENAME = 'RANDTABLE', @IDRANGE = @IDVALS

This works just fine, but I'd like to find a more comfortable, fast and easy way to use the procedure in standard selects. What would be the best way, assuming it's even possible, to do all that in one command?

For a logical example that obviously doesn't work, what I'd like to do is a form of the following:

EXEC usp_SearchObjRef @TABLENAME = 'RANDTABLE',
    @IDRANGE = (SELECT TOP 10 RAND_ID FROM RANDTABLE)


texts: Create transactional publication error in SQL Server 2014

texts:
In attempting to create a new Publication for Transactional Replication in SQL Server, I end up getting the following error, with pretty much no way around it:

  SQL Server could not create publication 'dfdsf'The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.Changed database context to 'farm'. (Microsoft SQL Server, Error: 50000)

For specifics, I did a very basic publication on a single table, with no row filtering. The security accounts used were created based on this tutorial.

This message does not seem very revealing and I cannot find a way around it. Is there any basic trail I can follow to find what I am doing wrong?


texts: Database design implementing support for different languages

texts:
Today I was practicing database design, for example I was trying to build a database design where I would like to store my favorite shows. I have normlaised this database to the third form (at least I think I have). But I have faced a problem with this design, actually, how can I preform a such query:

Is there any show that has language (let's say Italian) and status Emitting and there are more than 5 episodes available?

I think I have made a mistake with the language and show_internation_data table, but I am not sure...
Also if you could tell me how bad this design is and what to improve, or just throw an awesome article, that would be awesome!!
Diagram:
Here is the image:  http://i.stack.imgur.com/abhwx.png


texts: Issues with self signed certificates, SSL and MongoDB

texts:
Note: As specified in my comment I've figured out a solution to this issue regarding a single client/mongo connection.  The next step is to figure out a solution for a replica set involving this fix.

So I've been looking into adding SSL into my MongoDB replica set recently and ended up rebuilding Mongo 2.7.8-pre using SCONS.  It built fine and I was able to test the newer version of mongo.exe, mongod.exe and mongos.exe with the "allowSSL" mode without generating any certificates.

I was looking around at different tutorials on generating server and client side SSL certs with a root CA and came across this site:
http://acs.lbl.gov/~boverhof/openssl_certs.html

If you don't want to navigate to the page it basically:

Creates a CA
Creates a server key/cert pair
Creates a client key/cert pair

After generating everything I concatenated the keys and certificates into a server.pem and client.pem file because from the docs it seems like Mongo needs both in a .pem file in order for it to work properly.

This left me with:
 - ca.pem file with the root certificate
 - server.pem file with the server cert/key (subject - ...O = company1, OU = dept1...)
 - client.pem file with the client cert/key (subject - ...O = company1, OU = dept2...)

I was able to restart my services fine using ca.pem and server.pem.  However, when I went to connect to the nodes using the client with the following command:

mongo --ssl --sslPEMKeyFile "C:\MongoDB\ssl\client.pem" --sslCAFile "C:\MongoDB\ssl\ca.pem"

It gives me this error:

E NETWORK  SSL peer certificate validation failed:self signed certificate

I've tried to add the client cert to the root CA that I generated because it was suggested that this is my issue but it does not resolve the problem.  I tried adding the subject from the client certificate as a new user to the database as suggested for x509 authentication but this also does not resolve the issue.

I would appreciate any help or suggestions as to why my certificates are failing trust because I think at the moment I'm just being road-blocked by a lack of understanding.

Thanks!


texts:
Given this question on reddit, I cleaned up the query to point out where the issue was in the query. I use comma first and WHERE 1=1 to make modifying queries easier, so my queries generally end up like this:

SELECT
     C.CompanyName
    ,O.ShippedDate
    ,OD.UnitPrice
    ,P.ProductName
FROM
               Customers       as C
    INNER JOIN Orders          as O  ON C.CustomerID = O.CustomerID
    INNER JOIN [Order Details] as OD ON O.OrderID    = OD.OrderID
    INNER JOIN Products        as P  ON P.ProductID  = OD.ProductID
Where 1=1
--  AND O.ShippedDate Between '4/1/2008' And '4/30/2008'
    And P.productname = 'TOFU'
Order By C.CompanyName

Someone basically said that 1=1 is generally lazy and bad for performance.

Given that I don't want to "prematurely optimize" - I do want to follow good practices. I've looked at the query plans before, but generally only to find out what indexes I can add (or adjust) to make my queries run faster.

The question then really... does Where 1=1 cause bad things to happen? And if so, how can I tell?

Minor Edit: I've always 'assumed' as well that 1=1 would be optimized out, or at worst be negligible. Never hurts to question a mantra, like "Goto's are Evil" or "Premature Optimization..." or other assumed facts. Wasn't sure if 1=1 AND would realistically affect query plans or not. What about in subqueries? CTE's? Procedures?

I'm not one to optimize, unless needed... but if I'm doing something that is actually "bad", I'd like to minimize the effects or change where applicable.


texts: Inaccurate count when using multiple count functions in PostgreSQL

texts:
PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.890

I'm relatively new to SQL and am learning a lot of about writing queries as in my current work role. I encountered this problem that puzzles me. If I do a count of subscriptions that are paid with a null churn date, I get a count of 1279. This is the correct number. But once I add the count of customers that installed shopify, this count jumps to 1835. Why would this happen? Can anybody help me out? Thanks!

select
    count (s.account_id)
  , count (case when c.type ilike 'shopifychannel' and s.status = 'paid'
                then c.created_at else null end) as installed_shopify
from subscriptions s
join channels c on s.account_id = c.account_id
where s.status = 'paid'
  and s.churned_at is null

Count s.id will count the total number of customers who have s.status = 'paid'
Count case when 'shopifychannel' will count the total number of customers who have paid and installed a shopify channel. These are the outputs I am attempting to get.


texts: /dev/shm size recommendation for postgres database in docker

texts:
We have postgresql 11.7 database in docker container.
We had an issue "pq: could not resize shared memory segment "/PostgreSQL.XXX" to XXX bytes: No space left on device", described here.
Solution helped, we do not have an issue anymore. But which size should we set for shm_size for docker container with Postgres?
Is any recommendation? How can we calculate 'optimal size' for shm_size?


texts: Execute Permissions for a Store Procedure that creates databases

texts:
I have a Stored Procedure that creates a Database for a specific project which needs to run every month by one of our Data Analysts. The question is how do I structure this to enable the Analyst to run this Stored Procedure without granting them Create Database Permission.

I Tried
WITH EXECUTE AS OWNER/USER_NAME

The Analysts still get the below error:
Msg 262, Level 14, State 1, Line 67
CREATE DATABASE permission denied in database 'master'.

Any advice?


texts: Storing historical data in DB vs on disk

texts:
I've been pondering on alternative solutions to speed up data retrieval from my DB.
I currently have a relatively large database, about 1 billion rows and growing. We expect the DB to grow linearly with about 1.5 billion rows (300GB) per years. My DB is PostGreSQL on FreeBSD.

The database contains history sensor data which simply consists of a timestamp, a numeric value and 1 foreign key to a sensor. The data will never be updated, only written and is not accessed very often.

With this amount of data is it's not possible to keep everything in memory, but I still want fast data retrieval if possible. We're currently lookup at almost 6ms per table row lookup time because it's so far spread out over the harddisk that every lookup has maximum seek time.

I've been doing some small scale tests with storing data on disk and this seem a LOT faster. We're looking at 1.8s for a day from the DB versus 0.2ms retrieving it from disk (including value conversions). This is almost 10.000x faster.

Which leads to my question: Is there any particular reason not to store the history data as plain files on disk if I don't care about foreign key consistency?


texts: SQL Server Not Consuming all Memory - Insufficient System Memory in Resource Pool 'internal'

texts:
I am running SQL Server Standard 2014 with SP3. I am noticing a lot of activity on the log viewer around insufficient system memory in resource pool and memory Broker.

I have searched on this error and it was pointing back to SQL 2008 with a KB as a fix.
Am I looking at this right that SQL is only using less than 4GB of the memory?   We are Running Dynamics AX 2012, so the queries can be quite intense.

My physical server has 56Gb of memory and SQL has 16384 min and 36864 as max memory.
When querying SQL is shows that only 3.51 is being consumed.
I am using the following query to determine that which lines up with the Windows Task manager.

SELECT CONVERT(DEC(8,2),OSPM.physical_memory_in_use_kb/1024.0/1024,0) AS SQL_GB FROM sys.dm_os_process_memory OSPM
SELECT CONVERT(DEC(8,2),OSM.available_physical_memory_kb/1024.0/1024,0) AS AvailGB FROM sys.dm_os_sys_memory OSM

The memory seems available but SQL is only using a tiny portion, if I am looking at this right. My other test systems do not experience this type behavior.


texts: In Datagrip (using 2018.2.4), you can right-click on a query and choose 'Explain' which gives nicely formatted output. Is there anyway to get this nicely formatted output using instead 'Explain Analyze'?


texts:
I'm using MySQL 5.1 and want to upgrade to latest version 5.6.17 (from Windows Installer)
But when I installed, it used too much memory, about 420Mb although 5.1 vesion run only 8Mb memory. The setup type I choose:

Setup Type: Server Only

Config Type: Development Machine

I don't want it uses too much memory, how can I do, or what is lastest version of MySQL that use minimum memory like 5.1
I change config type to another type but It still uses than 400Mb ram


texts: Save Results as CSV *with* headers in SSMS

texts: With SQL Server 2008 R2, when I save the results as a CSV there are no headers. I can work around this by copying and pasting with the "Copy with Headers" , or just grab the headers and paste them in the CSV etc, but is there a better way to do this?


texts: User is in multiple Active Directory groups with multiple roles in database

texts:
I have a user that is a member of multiple Active Directory (AD) groups.  Within SQL Server 2012, these AD groups have been assigned to different database roles.  This user is not getting the correct privileges unless I add the AD user directly to the role (they are still a member of the groups as well). Is there an issue if a person is a member of different roles or would this be an AD issue when adding to SQL Server?

Preferably I would like only AD groups and not individual AD users within the databases.

One user in-particular is in a db role that has read access to a group of tables, another role that allows read write access to all tables.  Both roles are based on AD groups she needs to be a member of.  But because she is in both roles and AD groups, it is not allowing read write access unless her AD user is added to the read write role. (the AD group won't work)


texts: Why isn't my includeThreadNamesAsStatementComment JDBC parameter working?

texts:
The JDBC API lists a includeThreadNamesAsStatementComment parameter that seems to indicate it will prefix all queries with the Java thread ID.  I thought this would be useful in tying back DB activity to application logs.  I added it to my resource URLs and it seemed to only be working part of the time.
Curiously it was only the applications heartbeat thread that periodically pings the database as part of a health check.  None of the "real" queries were getting tagged.
Why isn't this working for all queries?


texts: Mongo exception: connect failed on reboot

texts:
I have Mongo installed on Ubuntu 16.04 machine, on VMWare. I start it using "mongo" command, last week there was no problem. But recently, it gives this error:

MongoDB shell version: 3.2.15
connecting to: test
2017-08-09T09:31:33.634+0300 W NETWORK  [thread1] Failed to connect to 127.0.0.1:27017, in(checking socket for error after poll), reason: errno:111 Connection refused
2017-08-09T09:31:33.712+0300 E QUERY    [thread1] Error: couldn't connect to server 127.0.0.1:27017, connection attempt failed :
connect@src/mongo/shell/mongo.js:229:14
@(connect):1:6

exception: connect failed

I know how to solve it, it is here: https://stackoverflow.com/questions/12831939/couldnt-connect-to-server-127-0-0-127017/34835813#34835813

But everytime I reboot Ubuntu machine, the problem repeats. Why, and how can I prevent this happen everytime?
Thanks.

Edit: Problem solved. You can see the solution below, in my comments to JJussi's answer.


texts: Postgres Logical Replication - "replication slot "mysub" was not created in this database"

texts:
I have two database servers 192.168.150.210 and 192.168.150.220
I created publication and subscription successfully for logical replication.

postgresql.conf file is modified on both servers.

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)

On both servers wal level is set to logical.

testha=# show wal_level;
 wal_level
-----------
 logical
(1 row

pg_hba.conf file is modified on both servers:

on server 192.168.150.210

host    all             all             192.168.150.220/32      md5

on server 192.168.150.220

host    all             all             192.168.150.210/32      md5

with user postgres I created table article on database testha

\c testha
create table article(idart int primary key,name varchar(10), quantity int);

just in case I also granted permission to that table for user postgres/postgres

grant all on article to postgres;

I created publication on server 192.168.150.210

CREATE PUBLICATION mypub FOR TABLE article;

I can see that everything is OK:

CREATE SUBSCRIPTION mysub CONNECTION 'dbname=testha host=192.168.150.210 user=postgres password=postgres port=5432' PUBLICATION mypub;

I tested connection and communication between servers is ok:

psql -h 192.168.150.220 -U postgres -W
psql -h 192.168.150.210 -U postgres -W

I restarted servers but in the logs I see errors:

ERROR:  replication slot "mysub" was not created in this database

ERROR:  could not start WAL streaming: ERROR:  replication slot "mysub" was not created in this database
LOG:  background worker "logical replication worker" (PID 30667) exited with exit code 1

What am I doing wrong. I followed instructions from multiple sites and did not find any additional instructions.
https://blog.dbi-services.com/postgresql-10-beta-1-logical-replication/


texts:
I have a query that runs from the sql engine in a progress DB. My problem is when a table or a record is locked from the progress4gl engine, I can't access it from sql engine and the query returns nothing for that lock.
Is there any way to access those rows or tables from a query in sql?


texts:
I am using Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production. Yes an ancient version but the license is embedded in an application that we use and this is the Oracle version that they use.

Here is an SQL query that I am using:

SELECT     EntryDate, COUNT(OrderNo)
FROM       Orders
GROUP BY   EntryDate;

I get results like:

EntryDate COUNT(OrderNo)
--------- --------------
01-NOV-12           543
02-NOV-12           555
03-NOV-12            91

There are currently 472 rows with dates up to today, so the above is only a small snapshot of results.

Is it possible to group the results by month instead of the current date i.e. get results like:

EntryDate COUNT(OrderNo)
--------- --------------
Nov12     X
Dec12     X
Jan14     X


texts: how to update information_schema.tables in mysql

texts:
i have a table A and i want to update the creation time of this table.
I understand i cant simple update ( using update query) as it is not any table.

Is there any way i can update it.

for example creation time is '2015-07-03 12:03:33' to '2015-06-03 12:03:33'


texts: How to determine cause of runtime increase given two query plans with SpillToTempDb warning

texts:
Two actual query plans were captured for two executions of one query:

  Plan "Fast" took ~1s and occurs about 90% of the time.

  Plan "Slow" took ~16s.

Since the graphical plans look identical to me, I dumped the XML versions and performed a diff to look at the text differences to be sure.

There are 2 "SpillToTempDb" warnings on the fast side and four on the slow side.  Looking that the 2 extra on the slow side:

One SpillToTempDb warning on "Parallelism (Repartition Streams) Cost 1%":

    <SpillToTempDb SpillLevel="0" />

On SpillToTempDb warning on "Parallelism (Distribute Streams) Cost 1%":

    <SpillToTempDb SpillLevel="0" />

The cost is the same (1%) for both the slow and fast cases.  Does that mean the warning can be ignored?  Is there a way to show "actual" times or costs.  That would be so much better!  Actual row counts are the same for the operation with the spill.

Besides performing a manual text diff of xml execution plans to find the differences in warnings, how can I tell what the 1500% increase in runtime is actually due to?

Differences except "RunTimeCountersPerThread" lines:

Left file: C:\Users\chrisr\Desktop\fast.sqlplan     Right file: C:\Users\chrisr\Desktop\slow.sqlplan
10                    <ThreadStat Branches="10" UsedThreads="85">
     10               <ThreadStat Branches="10" UsedThreads="73">
------------------------------------------------------------------------
------------------------------------------------------------------------
19                    <MemoryGrantInfo SerialRequiredMemory="1536" SerialDesiredMemory="10816" RequiredMemory="124224" DesiredMemory="133536" RequestedMemory="133536" GrantWaitTime="0" GrantedMemory="133536" MaxUsedMemory="105440" />
     19               <MemoryGrantInfo SerialRequiredMemory="1536" SerialDesiredMemory="10816" RequiredMemory="124224" DesiredMemory="133536" RequestedMemory="133536" GrantWaitTime="0" GrantedMemory="133536" MaxUsedMemory="107840" />

------------------------------------------------------------------------
     276                                                    <Warnings>
     277                                                      <SpillToTempDb SpillLevel="0" />
     278                                                    </Warnings>
------------------------------------------------------------------------------------------------------------------------------------------------
     630                                                                <Warnings>
     631                                                                  <SpillToTempDb SpillLevel="0" />
     632                                                                </Warnings>
------------------------------------------------------------------------

For slow case, tempdb before/after (select * sys.fn_virtualfilestats(db_id('tempdb'),null))
( only showing a few 100ms of latency ):

Should I be looking at the differences in the RunTimeCountersPerThread lines?  It appears it is just the order that is different between the Threads.

Possible hint (2013/04/26):  The query performs multiple inner merge joins and I found this on Paul White's blog:

  Exchange Spills

  Posted by:    Paul White NZ (view profile) Posted
  on:   Monday, March 07, 2011 at 3:29 AM

  ...these are almost always
  caused by a sequence of exchanges operation in order-preserving mode,
  separated by one or more operators that require sorted input e.g.
  Merge Join, Stream Aggregate, Segment...

  In SQL Server 2008 there is a Profiler event to show when this type of
  exchange 'deadlock' is resolved by spilling exchange packets to disk.

  The primary workaround is to avoid these plan shapes (especially
  parallel Merge Join at high DOP, in my experience). It is sometimes
  enough to force a hash or loop join instead using a hint, but this is
  advanced tuning and really requires detailed knowledge of the data
  concerned.


texts: Apply job aborting with <1 granule size unable to allocate, ASMM on

texts:
all

My team is trying to resolve an oracle issue we're seeing sporadically that is really causing us trouble.

We have an update process that imports .dmp files via a package that utilizes an Apply job to do so. (not our choice to implement it this way...)

The problem is, we're getting this apply job aborted with an error of 4031 unable to allocate <100 bytes (various values, usually from 60 to 90) from memory, with the shared pool marked as the culprit. ASMM is on. The server is provisioned ~500 MB of memory to run on (these are old Server 2003 environments so memory is in high demand). I am concerned about this error - it seems to me that the error should always report multiples of 4k, as that is our granule size, but perhaps I am mis-understanding the process of freeing up memory. I am also concerned that the server is not freeing up packages as needed.

I believe that the fix is to use dbms_shared_pool.keep("PACKAGE NAME") to pin the package used to run the apply job, so that it never has to allocate the memory again, but we need to test this fix before we deploy it, for various organizational reasons. We also plan to allocate more memory to the oracle database. Setting the minimum size is not a solution, as we've seen this issue when the size of the pool is above the minimum, with ASMM on. We've also tried setting the sga maximum size to a higher value, and pinning the package in question, but again, with no way to reproduce the issue, it is a waiting game to see if it ever happens again.

Our main problem is in reliably reproducing the problem - even when disabling ASMM and setting the shared pool as small as possible, we're not able to reliably reproduce the issue. We've gotten the shared pool small enough that the web interface can't even pull image tags out of the database, or query the login information from the appropriate table, yet the apply job still runs reliably.

We do not have an in-house DBA so naturally we're probably missing some obvious steps.

This issue is present sporadically on 2-3 servers in a 8 server environment. These individual servers all see similar use by different groups. I am unclear as to why only some of the servers encounter this problem, but have not been able to identify any smoking gun cause.

I am hoping for input in either reliably avoiding this issue entirely, or in reliably forcing the shared pool to be unable to allocate bytes to this job, so that we can confirm these fixes actually work.

Please bear in mind that I have read most of the 4031 posts on SO at this point - this problem has been under investigation for weeks. It is likely NOT appropriate to mark this question as duplicate and direct me to another question - I've read it already. It did not answer my question.This is a cross post from plain old Stackoverflow

I have examined the trace files for one of the servers but it was less than enlightening - I am unsure of what to look for.

We've tried flushing the shared pool, but again, with no way to reproduce the issue it turns into an "wait and see" fix, which isn't the answer that our funder is really interested in.


texts: Prevent columns from having same value in record

texts:
I'm working with PostgreSQL 10. I'd like to have a table that allows for the same value in a column, but not in the row. The table I created:

CREATE TABLE teams
(
  team_id SERIAL PRIMARY KEY
  team_lead character varying(250) NOT NULL,
  member1 character varying(250) NOT NULL,
  member2 character varying(250),
  member3 character varying(250)
  )

This would be allowed:

| team_lead     |  member1     | member2       |
------------------------------------------------
| Jane Doe      | Bill Smith   | Shirley Green |
| Jane Doe      | Carol Lewis  | John  Doe     |

This would NOT:

| team_lead  | member1     | member2       |
--------------------------------------------
| Jane Doe   | Jane Doe    | Shirley Green |
| Bill Smith | Carol Lewis | Bill Smith    |

Does anyone know how I would accomplish this? Thank you in advance.


texts: Mapping Fields based on unknown relationship

texts:
I have a challenging SQL query to work out (SQL Server).
I have one table that will show mappings between the two tables.
So below, field F1 is a scanner id

and table two, has the scanner Id in F1. F2 has the Location, and so on.

The top table can be updated to change mappings at any time.
So my question is how do I create a query so I know what field name relates to what data in table two.

I started off with an UNPIVOT, but didn't get too far as yet. If I could get the field name in the unpivot results then I am a long way to solving it.

SELECT ID, Data
FROM
(
select
ID,[F1] ,[F2]  ,[F3]  ,[F4]  ,[F5]  ,[F6]  ,[F7]  ,[F8]  ,[F9] ,[F10]
FROM [Inventory].[dbo].[ScannedDataCapture]

) AS cp
UNPIVOT
(
Data FOR Datas IN ([F1] ,[F2]  ,[F3]  ,[F4]  ,[F5]  ,[F6]  ,[F7]  ,[F8]  ,[F9] ,[F10])
) AS x

Thanks upfront. Scott


texts: Can I remove "/*!50100 PARTITION BY HASH..." to success with import?

texts:
I'm trying to import a database and get this message (Swedish lang phpMyadmin):

As you see, I get the message:

  #1064 - 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 'AUTO_INCREMENT=140059' at line 10

So I guess the problem is:

/*!50100 PARTITION BY HASH ( `feeditemsID` + YEAR(`feeddate`)) PARTITIONS 3 */

That is right before the auto_increment code. Here is the whole code:

CREATE TABLE IF NOT EXISTS `feeditems` (
   `feeditemsID` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `city` varchar(100) DEFAULT NULL,
   `companyID` int(10) unsigned NOT NULL DEFAULT '0',
   `fucID` int(11) NOT NULL DEFAULT '0',
   `feedID` int(10) unsigned NOT NULL DEFAULT '0',
   `title` varchar(255) NOT NULL,
   `description` text NOT NULL,
   `url` text NOT NULL,
   `feeddate` datetime NOT NULL,
   `region` varchar(100) DEFAULT NULL,
   `jobtype` int(11) NOT NULL DEFAULT '0',
   `jobinfomisc` text NOT NULL,
   `joblastdate` varchar(255) NOT NULL,
   `jobcontactname` varchar(255) NOT NULL,
   `jobcontactphone` varchar(255) NOT NULL,
   `jobcontactmail` varchar(255) NOT NULL,
   `jobcontactinfo` text NOT NULL,
   `jobworkers` varchar(255) NOT NULL,
   `jobActive` int(1) NOT NULL DEFAULT '0',
   `logo` varchar(50) DEFAULT NULL,
   `infokus` int(1) NOT NULL DEFAULT '0',
   `deleted` int(1) NOT NULL DEFAULT '0',
   PRIMARY KEY (`feeditemsID`,`feeddate`),
   KEY `deleted` (`deleted`),
   KEY `companyID` (`companyID`),
   KEY `feedID` (`feedID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH ( `feeditemsID` + YEAR(`feeddate`))
PARTITIONS 3 */ AUTO_INCREMENT=331451 ;

So the question is, can I just remove that part? Or what can be wrong? I'm not that good at stuffs like this. Would be happy for help here.


texts: Parsing a manifest.xml of a table schema to script?

texts:
I have a manifest.xml sent to me by a vendor that accompanies some extracts they are also sending.  I assume there should be a way to parse this into an actual table structure (or a script to create a table).  It's an extremely wide table (153 columns) and I'd rather not manually create a table if I can avoid it, especially since there are a variety of extracts coming and most are extremely wide.

Here's a sample of what the manifest.xml looks like that accompanies each file extract:

<DocumentElement>
  <SchemaTable>
    <ColumnName>somecolumn1</ColumnName>
    <ColumnOrdinal>0</ColumnOrdinal>
    <ColumnSize>4</ColumnSize>
    <NumericPrecision>10</NumericPrecision>
    <NumericScale>255</NumericScale>
    <IsUnique>false</IsUnique>
    <BaseColumnName>somecolumn1</BaseColumnName>
    <DataType>System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
    <AllowDBNull>false</AllowDBNull>
    <ProviderType>8</ProviderType>
    <IsIdentity>false</IsIdentity>
    <IsAutoIncrement>false</IsAutoIncrement>
    <IsRowVersion>false</IsRowVersion>
    <IsLong>false</IsLong>
    <ProviderSpecificDataType>System.Data.SqlTypes.SqlInt32, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ProviderSpecificDataType>
    <DataTypeName>int</DataTypeName>
    <NonVersionedProviderType>8</NonVersionedProviderType>
  </SchemaTable>
  <SchemaTable>
    <ColumnName>somecolumn2</ColumnName>
    <ColumnOrdinal>1</ColumnOrdinal>
    <ColumnSize>4</ColumnSize>
    <NumericPrecision>10</NumericPrecision>
    <NumericScale>255</NumericScale>
    <IsUnique>false</IsUnique>
    <BaseColumnName>somecolumn2</BaseColumnName>
    <DataType>System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
    <AllowDBNull>false</AllowDBNull>
    <ProviderType>8</ProviderType>
    <IsIdentity>false</IsIdentity>
    <IsAutoIncrement>false</IsAutoIncrement>
    <IsRowVersion>false</IsRowVersion>
    <IsLong>false</IsLong>
    <ProviderSpecificDataType>System.Data.SqlTypes.SqlInt32, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ProviderSpecificDataType>
    <DataTypeName>int</DataTypeName>
    <NonVersionedProviderType>8</NonVersionedProviderType>
  </SchemaTable>
  ...
</DocumentElement>


texts:
The SQL Sever 2014 installation our company has is of SQL Server 2014 SE. I need to install the latest version of SQL Server on there.

I understand that I can install 2014 SE and then apply updates to bring it to SQL Server 2014 SP2 CU7, but I was wondering if there is a way to directly install it to the latest version using the installer?


texts: how to Manipulate duplicated records using access

texts:
Using Microsoft Access, I have a number of records that have duplicated field-1. I need to form a query such that the duplicate records get unique ; BUT the next filed-2 data must be seen in columns next to each other the same no. of times. For example, now I have a table like this:
field-1  | field-2
1        | date1
2        | date1
1        | date2
3        | date1
3        | date2
1        | date3

And query output needed:
field-1  | (1st-duplicate) | (2nd-duplicate) | (3rd-duplicate) | .... (nth-duplicate)
1        | date1           | date2           | date3
2        | date1
3        | date1           | date2

How I can do that ?


texts: What is the best way to store SSH fingerprints and public keys?

texts:
I'm building a virtual Keyring in our DB to allow users to store PGP/GPG public keys with relation to email addresses so that they can optionally encrypt all communications our system sends them.  I'm just building the table now and wasn't sure about the column type to store the fingerprint.

From what I understand fingerprints will be 128 or 160 bits long, generally in the form of:

43:51:43:a1:b5:fc:8b:b7:0a:3a:a9:b1:0f:66:73:a8

... which I believe is just a 32bit MD5 hash / UUID.  I'm guessing I can store this as a 16bit Binary value, or a 32 char string...

Is there a best practice, and if so what is it, or lacking that, what's your preference?

As a related follow up, is there any reason to Encrypt the public key string itself, or is that redundant since it is public after all?

EDIT: BTW, this is MySQL currently 5.5, but will be 5.7 in the near future.**

EDIT 2: I can't find any examples out there of a table schema for storing this type of record and I admittedly have only minimal understanding beyond the basics of using public/private keys in my own role, so while I could discern that the fingerprint is a hash, I'm unaware of the different formats for public keys and what range their lengths can come in (i.e. what about the -----BEGIN CERTIFICATE----- components, what size field will these need?).

If possible can someone provide a basic CREATE TABLE statement with what you see as an optimal fieldset? Below is what I've created thus far, is it sufficient, would you change anything?

CREATE TABLE `public_keys` (
    `ai_col`  int UNSIGNED NOT NULL AUTO_INCREMENT ,
    /* ... other foreign keys ... */
    `label`  varchar(100) NULL ,
    `fingerprint`  binary(16) NOT NULL ,
    `key_type`  varchar(10) NOT NULL ,
    `public_key`  varchar(1024) NULL ,
    PRIMARY KEY (`ai_col`)
);


texts: Allow update access to only 2 tables in a database schema

texts:
I would like to have a login which is capable of accessing a specific schema but should be having only update access to 2/3 tables. Is it possible?

The Database runs on SQL Server 2008 R2. Kindly guide me if possible.


texts:
When i do Schema Compare in Visual Studio i see that spatial index in table was changed and this changes will included in generated script. But i do not want see this changes in my script. It's becouse i have a several databases and each database have own spatial index in tables.
In spatial index changed only BBox part:

BOUNDING_BOX = (XMAX = 500000, XMIN = 300000, YMAX = 1600000, YMIN = 1400000),

It's possible exclude spatial index from schema compare?


texts: MySQL view extremely slow despite indexes

texts:
I'm having an issue with a view, when I make a query it brings CPU to 100% and it keeps loading.

The view is:

https://pastebin.com/ZgB7rr91

channel_data_60 table contains about 1,200,000 records:

https://pastebin.com/mkmQH39p

and this is the source table:

https://pastebin.com/2xiCiwSM

and a screenshot of the EXPLAIN:

I read around and it seems UNION makes this kind of issues but I made a test without it and the query keeps loading forever.

Any help? Is the EXPLAIN result showing something useful?


texts: How to enable group replication again after dissolving a MySQL Innodb Cluster using mysqlsh?

texts:
When I dissolve a innodb cluster using mysqlsh it automatically disables replication on the instance.
How do I enable replication on the instance again so i can "start group replication" after a cluster dissolve? I'm getting these errors in the log after I dissolve a cluster and try to start group replication again. my group replication was working fine before the dissolve.
[ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Invalid hostname or IP address () assigned to the parameter local_node!'
[ERROR] [MY-011674] [Repl] Plugin group_replication reported: 'Unable to initialize the group communication engine'
[ERROR] [MY-011637] [Repl] Plugin group_replication reported: 'Error on group communication engine initialization'
[ERROR] [MY-011718] [Repl] Plugin group_replication reported: 'Error calling group communication interfaces while trying to leave the group'


texts: Getting data according to last hours from sqlite

texts:
I want to get records out of sqlite database according to hours. following are my questions

1) I have to extract all data from sqlite for past one hour. I have tried following query but it provide me data for all the hours in present day

Query:

SELECT * FROM Table1 where Date >= datetime('now','-1 hours')

Where Table1 is my table name and Date is coloumn name of type DATETIME

Eg: there are following record in database

When I fire query in sqlite firefox browser tool it returns me

which I do not want.

What should be the query to get past 1 hour data from database

2) What should be query to get the value out of database according to every hours, like I have to get data for past 1 hour, then data of past 1-2 hour, the data of past 2-3 hour i.e an hour data between two hours?

Any Help will be appreciated.


texts: Error message - view server state permission was denied - when connecting to SQL Server 2014 using SQL Server 2012 Management Studio

texts:
When right-clicking on a table and selecting "select top 1000 rows", I get this error:

Is there any workaround, other than the obvious upgrade to SQL Server 2014?


texts: MySQL Calculate percentage difference (not change) between 2 columns

texts:
The calculation formula for difference between 2 numbers should be:

Using the above formula I need to calculate the difference in percentage between N1 and N2.
Both numbers are the result of 2 separate SUM cases based on 2 ENUM values in col_type (please see below)

SELECT
  CONCAT('£ ',SUM(CASE WHEN t1.col_type = 'N1' THEN t2.qty * t1.price ELSE 0 END)) AS 'Total N1',
  CONCAT('£ ',SUM(CASE WHEN t1.col_type = 'N2' THEN t2.qty * t1.price ELSE 0 END)) AS 'Total N2'
FROM t2
  INNER JOIN t1
    ON t2.col_id = t1.col_id

The resulted output should be like:

+----------+----------+------------------------+
| Total N1 | Total N2 | Diff between N1 and N2 |
+----------+----------+------------------------+
|  3765.50 |  3246.15 | 14.81%                 |
+----------+----------+-----------------------

My question is how do I pass the values of N1 and N2 (already generated) to a 3rd column and calculate the difference between N1 and N2 using above formula?

Note: Please note that is percentage difference between 2 numbers and not the percentage change where (N2-N1)/N1 * 100 applies...

Thank you in advance for any reply...


texts: Rank variable value in MySQL is always being set to null

texts:
In a sample procedure I am trying to find the rank the of the cities according to the
    number of offices. I have a Location Table and an Office Table and a location can have
    multiple offices ( In my case I am assuming Boston has 3 offices and Bangalore has only
    2) offices and I want them to rank accordingly . I am using the below SP but it returns
    me the rank as 0 and 0 and also MySQL query browser is being crashed . I guess I am
    making some error here .

   BEGIN

   DECLARE rank INT DEFAULT 0;

   SELECT rank =rank+1 AS Rank,l.Location_Name,COUNT(*)
   AS Number_Of_Offices FROM employee_leaves_management_system.location l
   INNER JOIN
   employee_leaves_management_system.office o
   ON l.Location_ID = o.Office_Location_ID
   GROUP BY l.Location_Name
   ORDER BY Number_Of_Offices;

   END $$

A quick help will be much appreciated !!


texts: How to Capitalize letters that are after a special character?

texts:
I have checked the answer to this question and couldn't find an exception for names that start after a special character like dot.
for example ".TOM WILSON"

I have tried this query

declare @a varchar(30);

set @a = '.TOM WILSON';

select stuff((
       select ' '+upper(left(T3.V, 1))+lower(stuff(T3.V, 1, 1, ''))
       from (select cast(replace((select @a as '*' for xml path('')), ' ', '<X/>') as xml).query('.')) as T1(X)
         cross apply T1.X.nodes('text()') as T2(X)
         cross apply (select T2.X.value('.', 'varchar(30)')) as T3(V)
       for xml path(''), type
       ).value('text()[1]', 'varchar(30)'), 1, 1, '') as [Capitalize first letter only];

Is it possible to ask SQL skip special Characters?
I am new to the concept and will appreciate any source that can give me a better understanding.


texts:
I started a small business and have created an accounting DB with advice from my accountant.

In Part of my business I generate invoices for the same customers every month. I use a unique invoice number that helps with tracking etc.

So I haven't been able to get an auto number to work. Currently, I dump the records in monthly then manually update the invoice number, but it is taking too much time now. I have been trying to adapt some code I found online but I am new to the coding end of Access. Most of the time I have been able to get things done with queries and macros.

So this is the code that I am trying to adapt.

Private Sub InvUDB_Click()
Dim InvNo As DAO.Database
Dim rstMInvoice As DAO.Recordset
Dim rstInv1 As DAO.Recordset
   Set InvNo = CurrentDb
   Set rstMInvoice = InvNo.OpenRecordset("MInvoice")   '**** This is the line that the debugger opens up to after the error
   Set rstInv1 = InvNo.OpenRecordset("Inv1")

   rstMInvoice.MoveFirst
   Do Until rstInvoice.EOF
      If rstMInvoice!Invoice = 0 Then

         rstMInvoice.Edit
         rstMInvoice!Invoice = rstInv1!Inv
         rstMInvoice.Update

      End If
      rstMInvoice.MoveNext

      DoCmd.SetWarnings False
         DoCmd.OpenQuery "Invoice_Q000"
         DoCmd.OpenQuery "Invoice_Q002"
         DoCmd.SetWarnings True
   Loop
End Sub

I get an error message 3167:

  “You referred to a record that you deleted or that another user in a multiuser environment deleted. Move to another record, and then try the operation again.”

When I Debug it highlights The following row:

  Set rstMInvoice = InvNo.OpenRecordset("MInvoice")

I use the two queries below this line to generate a temp table with the new invoice number, (Max Inv No for current year and Month Plus 1).

I have tried running these queries in different places in the code with no luck.

As you can see I do not have a clue as how to write this or how to get this code to run. When I replace everything past the equal sign with a constant it works but it replaces all the qualifying records with that constant.

Can someone help me with this?


texts: Master to Slave to Slave Configuration in MySQL

texts:
We currently have 3 MySQL Servers running;

the first which contains production data and is due to be retired

mysqld  Ver 5.5.28-0ubuntu0.12.04.2 for debian-linux-gnu on x86_64 ((Ubuntu))

mysql> SHOW MASTER STATUS;
Empty set (0.00 sec)
the second which contains out of date snapshot of production data and is to take over from the first.

mysqld  Ver 5.5.28-0ubuntu0.12.04.3-log for debian-linux-gnu on x86_64 ((Ubuntu))

Master Status
the third which is already a slave of the second server.

mysqld  Ver 5.5.29-0ubuntu0.12.04.1 for debian-linux-gnu on x86_64 ((Ubuntu))

mysql> SHOW MASTER STATUS;
Empty set (0.01 sec)

My question is would I go about setting up the following?:

First Server (master) -> Second Server (slave) -> Third Server (slave)

Tutorial links welcome! I tried to Google, but I'm sure I've gotten the wrong keywords


texts:
I am using SQL server 2008 R2.
The DB is normally used from 9 am to 9 pm and the maximum db size is 10 GB.
Whether rebuild index offline causes any problem, if it is done during midnight (when no one is using db)?
or reorganize is enough for index maintenance?


texts: Replicating data to a head office with limitations

texts:
Here's my situation.  We have cash registers that send transactions to the store server.  With every transaction received, the store server updates tables... at the same time other management transactions modify tables.  At the end of the night we send everything to the head office for which they have all the info for all the stores.  usually stores and cash registers only use SQL Express (we support 2008 and up) as for the central head office DB, they usually use SQL Server Standard edition and up (again we support 2008 and up).

I'm trying to revamp that process due to the fact that right now our process requests from the stores all data of days that have been modified and that for 25 tables out of hundreds. The problem is that if 1 record changes for a previous day we send all data of that day that was touched for all 25 tables.

These 25 tables are daily summaries that are updated live at the store and the changes are sent to the head office once a night usually at midnight.

My first instinct was to create triggers on each of the 25 tables until I realized that every item of a transaction will trigger 25 different triggers.
Which quickly became unattractive.

my next try was to send each transaction once to the head office and replay everything at the head office, but that will be too much of a load at the head office since they might be receiving data from 200 stores all at once.

Ideally, I'm looking for a way to transfer only data from the 26 tables that NEED to be transferred.  Maybe by having the head office compare some type of Checksum of every table with a store's checksum of the same tables (SQL checksum won't work since the head office will always have more data).

I'm asking for different opinions to hopefully find other ideas I can explore.

very much appreciated.
Thank you


texts: Get notified when SQL Server 2008 Audit Server logs something?

texts:
I'm setting up SQL Server 2008 Audit Server to log any activities made by certain group of users of certain database. All I need to do is get notified when the Audit server logs something, so that I can send out emails to the admins notifying them of the logged operation.

Is there a way -an event that get raised or something- to tell SQL Server to run a certain job once an audit entry has been recorded?


texts: Temp DB space allocated- will it be reclaimed ever?

texts:
I have observed that the tempdb in the production environment has grown over to 100 GB after initializing it.

I understand that due to server load and some expensive queries have resulted to the growth of tempdb in a span of just two days. I have been observing this over a week now and find this size did not grow further and remains the same.

I have one mdf file configured for tempdb on the server having 8 logical cores. Though I could add multiple mdf files based on the logical cores.

I observe that the free page size and the physical size of temp db data file are the same in my case

SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

Help me understand if this physical space would ever be reclaimed automatically without we explicitly executing script to shrink the space or by restarting the server to reset the temp db allocation??


texts: Dodgy T-SQL Query Execution driving me crazy

texts:
SQL Server 2019.  Here's a link to a gist with the xml plan.
Hello, I am having a heck of a time understanding why this query is taking 0.02 seconds to execute when it can't find a record with one of the included statuses. When it finds a record that has one of the included statuses it tends to be much faster. I am guessing that is because the query stops once it finds 1 row that matches.
SELECT TOP 1 IDNum,
             FORMAT(Date, 'M/d/yy') AS theDate,
             Status,
             Rate
FROM   theDB
       INNER JOIN DomainTable
               ON theDB.IDNum = DomainTable.IDNum
WHERE  DomainIP = '127.0.0.1'
       AND status IN ( 'Active', 'To ReActivate', 'To Deactivate', 'Deactivate ASAP',
                       'SUSPENDED', 'SUSPENDED X', 'SUSPENDED Y', 'SUSPENDED Z' )
ORDER  BY theDB.IDNum DESC

(DomainIP belongs to DomainTable the others belong to theDB)
In the execution plan the largest cost is the TOP N SORT at 33% There is a clustered Index seek at 29%
There is a key lookup on DomainTable that is using 29%
The Index seek for the IP on DomainTable is 9%
My questions are:
is there any way to get that TOP N to not be so heavy?
0.02 seconds isn't that slow but also this query is pretty light. So I would like to optimize it as much as possible.
There are only four entries in DomainTable where the IP is 127.0.0.1 so it is essentially taking 0.2 sec to decide which one of those to return and it turns out the answer is none of them because the status isn't right on any of them.  Is there not a way to just create an index that keeps all of this information in RAM or something?


texts:
on SQL Server, i'm trying to query all of last months data based off of the time stamp, however, all those timestamps are in UTC and i need them to be in MST (the reason is mainly for the end dates)

So far this is what I have:

Select Vehicle.Timestamp
WHERE Vehicle.Timestamp >= DATEADD(mm,DATEDIFF(mm,32,GETUTCDATE()),0)
AND Vehicle.Timestamp < DATEADD(mm,DATEDIFF(mm,0,GETUTCDATE()),0)
order by Vehicle.Timestamp

i'm not entirely sure which way to move forward.

I've seen the use of switchoffset however, not sure the best way to apply it.


texts: How can i deploy ssrs report one server to another server

texts:
When I create a sample ssrs report and I deploy it, I get the following error:

  The specified report server URL http://localhost/reports could not be
  found. verify the syntax of the URL and that report server exits.


texts: XtraBackup fails with my.cnf Percona Configuration

texts:
After using a percona optimized .cnf file the backup fails
Installing my.cnf
# service mysql stop
(Installed my.cnf)
# rm ib_logfile0
# rm ib_logfile1
# service mysql start

Mysql creates new log files on startup

my.cnf
# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208

[client]

# CLIENT #
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld]

# GENERAL #
user = mysql
default_storage_engine = InnoDB
pid_file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
basedir = /usr/local/mysql
tmpdir = /tmp

# MyISAM #
key_buffer_size = 32M
myisam_recover = FORCE,BACKUP

# SAFETY #
max_allowed_packet = 16M
max_connect_errors = 1000000
skip_name_resolve
sql_mode = STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION
sysdate_is_now = 1
innodb = FORCE
innodb_strict_mode = 1
bind_address = 127.0.0.1

# DATA STORAGE #
datadir = /var/lib/mysql

# BINARY LOGGING #
log_bin = /var/lib/mysql/mysql-bin
expire_logs_days = 14
max_binlog_size = 100M
sync_binlog = 1

# CACHES AND LIMITS #
tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_type = 0
query_cache_size = 0
max_connections = 500
thread_cache_size = 50
open_files_limit = 65535
table_definition_cache = 1024
table_open_cache = 2048

# INNODB #
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
innodb_buffer_pool_size = 6G

# LOGGING #
log_error = /var/lib/mysql/mysql-error.log
log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log

error log
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

131210 11:53:50 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
131210 11:53:50 innobackupex: Connected to MySQL server
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".

innobackupex: Using mysql server version 5.6.14-log

innobackupex: Created backup directory /var/www/dyntest.dk

131210 11:53:50 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/tmp --tmpdir=/tmp --stream=tar
innobackupex: Waiting for ibbackup (pid=6728) to suspend
innobackupex: Suspend file '/tmp/xtrabackup_suspended_2'

xtrabackup_56 version 2.1.5 for MySQL server 5.6.11 Linux (x86_64) (revision id: undefined)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: Error: log file ./ib_logfile0 is of different size 268435456 bytes
InnoDB: than specified in the .cnf file 50331648 bytes!
innobackupex: Error: The xtrabackup child process has died at /usr/bin/innobackupex line 2579.


texts:
In my case, I have 5 directions: 1:North, 2:South, 3:East, 4:West, 0:Unknown. A user can stand in one direction of another user. If he is not standing in any direction, he will be considered in the unknown direction.

A, B, north
A, C, north
A, D, north
A, E, south
A, F, south
A, G, south
B, A, south
B, C, south
B, K, east

In the above table, B, C, D are standing in north direction of A and E, F, G are standing in direction south of A and so on.

Expected operation frequency:

I expect hardly 10 updates in a month for a user
I expect max 1000 (300 in general) entries against a user in bulk.
I expect hardly 15 insertions against a user in a month
I expect hardly 10 deletions in a month against a user

Another way I can think to design the DB for the above situation

A, B, 1, 2
A, C, 1
A, D, 1
A, E, 2
A, F, 2
A, G, 2
B, C, 2
B, K, 3

or

A, 1, [B,C,D]
A, 2, [E,F,G]
B, 2, [A, C]
B, 3, [K]

But this design will make the find, and update operations a bit complex. How should I design the DB for the above situation?

Update

I'm trying to create a DB for a social website.

I'm expecting more than 1 million users in the next 2 years.
There would be around 2000 find queries against a user in a month.


texts:
I want to create total field in this table. But in SQL server 2008 can't use 'order by' in OVER clause.

I have same PO and difference INV. I want to Qty field minus Quantity field and next row Total field minus Quantity field.
This is a structure table.

And this's a result.

This is my code but it's error at ORDER BY.

select t2.BaseRef 'PO',t2.BaseQty 'qty', t1.NumAtCard 'INV',
        t2.Quantity 'Quantity',
        t2.BaseQty - SUM(t2.Quantity) OVER (ORDER BY t1.DocDate) AS 'total'
from OPDN t1
        INNER JOIN PDN1 t2 ON t1.DocEntry = t2.DocEntry

Can I use other way for sort this data?


texts: Which allows for faster queries in a graph database: a node with multiple properties, or a node that points to multiple nodes?

texts:
I just started using Neo4j and I don't know when it'd be best to add properties to a node vs when to create more nodes. I'd assume for simple things like x and y coordinate data, it's better to use node properties. Then for stuff like user information it's better to use additional nodes.

Is there a threshold where it becomes more efficient to use one or the other? For instance, if all the properties in question don't have any children, is it always better to use node properties rather than additional nodes? Is there some cutoff like 10 or more properties it's better to use multiple nodes?

Here's the specific example I'm working with if it helps anyone understand my question. Users post blocks which in turn have a type, x coordinate, y coordinate, a url destination, and a number of upvotes. The left graph uses a single node for the block with multiple properties, and the right graph uses a new node for each property:


texts: Is it worth it to create an "Id" column?

texts:
I have a table which has a column Name, which is of type VARCHAR2(20), unique, cannot be null, and cannot be changed.

At first I used it as a Primary Key for this table but I was wondering if it was better to create another column Id (with a more appropriate naming of course) and handle table relations with it.

I know it's a common best practice to have an Id column but I've heard that clustering the DB with tons of meaningless Id columns is to be avoided (a Name column has more semantics attached to it).

What would you advise in this situation?


texts:
I have a long varchar field that I am exporting to a csv with BCP.  The export goes as it should, however in the actual cell itself the csv file shows X.XXXXXX+13 instead of XXXXXXXXXXXXXX

Am I missing a Cast() or BCP switch that could force the csv file to show XXXXXXXXXXXXXX?  Or since it is BCP this is the output you get?

Declare @sql nvarchar(max), @filename varchar(max)
Set @filename = 'C:\Test.csv'
Create Table #test (field1 varchar(100))
Insert Into #test (field1) Values
('XXXXXXXXXXXXXX')
Select @sql = 'bcp "SELECT ''field1'' UNION ALL SELECT CHAR(34)+CAST([field1] AS VARCHAR(MAX))+CHAR(34) FROM #Test''" queryout "'
SET @sql = @sql + @filename + '" -c -t, -T -S '+@@SERVERNAME
exec master..xp_cmdshell @sql


texts: Cleaning disabled SQL agent jobs in SQL Server 2005 and above

texts:
For many of our old servers, we have found that there are lot of SQL agent jobs which were either run as an ad-hoc for instant purpose and now no more needed OR Jobs which are not functional/working and replaced by some new jobs, are just sitting idle and occupying the unwanted attention on the job activity list:

Hence, I would like to know, should we be good to clean up all those disabled jobs?
(I am not sure if they are sitting idle there, what would be current or future impact)

If yes, what should be the safest way of cleaning them in one go, for cases where there is quite a big list of disabled jobs.


texts: Simplified/automated datafile free disk space reclaim

texts:
On Oracle version 11g:

After Googling, I can't find a simple way to reclaim free space after deleting a table.

I've found a lot of explanations, telling how the datafile becomes fragmented, the big stack of boring queries you have to run in order to move the "empty space" at end of the datafile (table by table... even when you have 200 tables !?).

Then you have to reduce the datafile size by "guessing" by how much you can reduce it, or you must know exactly what is your "block size"... And finally you should not forget to "rebuild the indexes".

See for example : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:54178027703899

and http://www.oracle-base.com/articles/misc/ReclaimingUnusedSpace.php

Is there a simple PL/SQL procedure which, given a tablespace name or datafile name, would to that job? Or any similar Oracle tool?


texts: SQL Server slow response when accessed from a remote client

texts:
We have a SQL Server in a remote location. If we access the server from the same location (internal IP, same IP as the server is using) the performance is good. We don't face any problem with connectivity.

But whenever we try to connect from a remote client (public IP) we are faced with a timeout error.

I have made an etc/host entry for the server.

What might be the reasons for such problems?

I have connected via ODBC in Management Studio. The connection has been established. I am facing the problem only when executing queries (any query).


texts: MongoDB 64 on Raspberry Pi 3 with SLES 12 64 bit

texts:
I am trying to run MongoDB 3.4 on raspberry pi 3 with an OS which was made by SUSE and SUSE is the only official 64 bit OS for raspberry pi (SLES 12 SP2). I have downloaded the MongoDB from the link below:

https://software.opensuse.org/download.html?project=server:database&package=mongodb

It is the only version which I became able to install properly but when the installation finishes, the service mongodb doesn't start with the error saying that: "Cannot start server. The default storage engine 'wiredTiger' is not available with this build of mongod. Please specify a different storage engine explicitly, e.g. --storageEngine=mmapv1., terminating". In the config file and by command I tried to change the storage engine to mmapv1 but it's unknown by mongodb according to the error log. Can anybody help me how to solve this problem and run mongodb 64 on raspberry pi 3?


texts: Is there a way to load a sql script with a specified encoding into a running SQL Server Management Studio (SSMS)?

texts:
Just as the question says.
I have scripts with 3 different encodings and feel offended by having to manually select of about 200 possibles I was never interested in. ( I didn't count them ).


texts: Transaction Log file size is increasing in my Azure (IAAS) SQL Server 2014 database

texts:
Recently we have migrated our application (both web app and database) to Azure (IASS). There we have a VM with SQL Server 2014 installed. Also I have restored all the necessary databases and SQL Server jobs on the server (from the existing On-Premise DB server). All the settings are verified and the application was working fine for a week.

Then we noticed a problem with the log file size (size is increasing  and reached around 42 GB and not able to execute any of the basic query and everything breaks :( ). The same DB and Job ran in on-premise server for a year and  this issue didn't happen.

Some information about the application and DB

The application contains a couple of SQL scheduling jobs which run each 2 hours of a day and the job take maximum of 5-8 minutes to complete.
Application contains less than 200 users and concurrent users are around 20-30.

So please let me know any one of your comments/input on this (Transaction log file size increment) issue. Also please let me know if you need any other information


texts: Is there any other way apart from sa to login to a migrated instance where the target environment is in a different domain?

texts:
We are migrating sql server instances from one domain to a different domain. New AD accounts/groups will be used in the new domain so SQL logins and database users in the source SQL instances will be unusable in the target instances.
Migration is by taking a snapshot backup of the host server and restoring to the target environment, which means the SQL instances have come across with the server images.
Having logged in to the migrated host server with new credentials, we now need to login to the SQL server to configure the security with new AD accounts.
Is there any other way of login in to the SQL server apart from doing so as sa with SQL authentication?
I am unable to use SSMS even in single user mode, as there is no valid AD login that can be used.
Does anyone have any other ideas please? Thank you so much.


texts: "Stored Procedures Invoked/sec" counter always increasing

texts: We've implemented some feature using service broker and deployed recently on production. I am observing that "Stored Procedures Invoked/sec" counter is always increasing. However, I can see that other counters like Tasks Running etc are stable. Why Stored Procedures Invoked/sec not stick to a range?


texts:
Google search has instant search feature. While you are typing, it finds the pages. Apple.com search box. StackExchange ask question part finds similar questions while you are typing question title.

I am building a collection of web applications in same system. I will add a general search functionality to search through storage of all web applications. But thinking that tables will have records of thousands of users, and that searching will be done for all applications, that can cost too much server power.

I consider caching, though it is like creating all database tables in a folder or memory with same information. I don't see any benefit from this approach.

How are businesses are building this kind of feature in products professionally? Is there any special technique, or can caching really solve it?

P.S. I am using PHP with MySQL.


texts: Allowing other host to connect to postgresql-9.1 and restart services in postgresql-9.1

texts:
We need to add few Ip address in our pg_hba.conf file so that some new servers can connect to our postgresql database. We did the changes to pg_hba.conf like below

old pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            password
host    all             all             10.150.0.0/16           password
host    all             all             10.156.14.0/23         password

New Ips to be added to pg_hba.conf

10.24.8.1
10.150.256.170
10.150.256.133

We had done the changes to pg_hba.conf and added entries like below(we directly added IP address without /16 or /32)
New pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            password
host    all             all             10.150.0.0/16           password
host    all             all             10.156.14.0/23         password
host    all             all             10.24.8.1              password
host    all             all             10.150.256.170         password
host    all             all             10.150.256.133         password

Now, we need to restart the postgresql-9.1 server. I tried the below commands but nothing worked.

postgres:~> service status postgresql-9.1
Absolute path to 'service' is '/sbin/service', so it might be intended to be run only by user with superuser privileges (eg. root).
-bash: service: command not found
postgres:~> service postgresql-9.1 status
Absolute path to 'service' is '/sbin/service', so it might be intended to be run only by user with superuser privileges (eg. root).
-bash: service: command not found
postgres:~> systemctl status postgresql-9.4
If 'systemctl' is not a typo you can run the following command to lookup the package that contains the binary:
    command-not-found systemctl
-bash: systemctl: command not found

This is the server info having postgresql-9.1 installed

postgres:~> uname -a
Linux camboxtest1 3.0.101-108.87-default #1 SMP Fri Feb 22 16:42:57 UTC 2019 (10e85a8) x86_64 x86_64 x86_64 GNU/Linux

Did i add the IP address in right format ? What will be the command to restart the postgresql-9.1 services?


texts: Affect of Shrinking Database before Transaction log backup

texts:
I am newbie in the world of Database administration, so excuse my stupid question :)

I have the following maintenance jobs scheduled through SQL Server Management Studio :

Full DB Backup at 2 am, 10 am & 6 Pm daily (after every 8 hours).
Transactional log backup at 2:30 am daily.
Shrink job at 9:30 am & 9:30 pm daily.

I have run the Shrink job at about 1 AM manually, after that the scheduled Transaction Log Backup got failed with following error

  Code: 0xC002F210     Source: Back Up Database (Transaction Log)
  Execute SQL Task     Description: Executing the query "BACKUP LOG
  [PRODDB] TO  DISK = N'S:\CAS Backup\Log..." failed with the following
  error: "BACKUP LOG cannot be performed because there is no current
  database backup.  BACKUP LOG is terminating abnormally.". Possible
  failure reasons: Problems with the query "ResultSet" property not
  set correctly parameters not set correctly or connection not
  established correctly.  End Error  DTExec: The package execution
  returned DTSER_FAILURE (1).  Started:  03:01:12  Finished: 03:01:12
  Elapsed:  0.359 seconds.  The package execution failed.  The step
  failed.,00:00:00,0,0,,,,0

I need to understand was manually running Shrink Job before transaction log backup failed the later at 2:30 AM??


texts: Will I have to restore controlfile in RMAN level 1 backupset before recover database?


texts:
I have a validation question regarding implementation of TLS 1.2.

What tools, scripts, processes, etc.. can be used to validate that TLS 1.2 is working when clients, webserver, and SQL Servers are communicating between each other?

Do I need to perform network packet sniffing, run traces, use process explorer? Is there a verbose option somewhere that can be turned on with logs reviewed?

Note: if it makes a difference, the SQL Servers involved run a variety of versions: 2008R2, 2012, and 2014.


texts:
I have a requirement to retrieve (extract) data from an Oracle database (either 9i or 10g) as at a given point in time.

I will need access to the data at this point of time for several days afterwards (as the database is large enough that the extract process will take several days to complete). The database is also in constant use.

For example, on January 3rd, I may want to query data as it was on January 1st and I may need access to the data as it was on January 1st for several days (maybe until about January 7th). What is the best way to achieve this?

I am not a DBA, but have looked into a number of solutions, that may form the basis of a solution, but would appreciate any advice on my best course of action.

I am aware of flashback and the ability to query a table as it was at a given point in time. I am, however aware that keeping the amount of history I'd need to (probably at least 7 days worth) is likely to make this infeasible based on the size and high usage of the database. I am also aware that when you set flashback to record a given number of days worth of data, you may end up with significantly less than this based on high usage, so this would seem like a risky approach anyway?

I am also aware of creating a Guaranteed Restore Point, but that this will only be an option if my database is 10g or above(?) If I did this, I believe I would have to turn Flashback off (?) as if I don't, flashback will have to keep logs all the way back to the time of the guaranteed restore point (and so in this example I'd end up with at least 7 days worth of history and risk the database running out of disk space). With a guaranteed restore point in place, is it possible to run a SELECT query on a table as at this restore point using something like the following:

CREATE RESTORE POINT test_restore_point GUARANTEE FLASHBACK DATABASE;

SELECT * FROM tbl1 AS OF test_restore_point;

The only examples I have seen involve actually restoring the database to the restore point (which I don't want to do) rather than querying from it. I am unable to test this for myself as I currently only have access to a 9i database.

I'd be very grateful for any advice on how I can achieve my objectives using this functionality or any other.

Thanks


texts: Remove sql server service from windows 7

texts:
I had installed and later uninstalled SQL Server 2005 . I used registry cleaner tools and deleted all files related to SQL Server . Still the services console shows entry for previous installed database instance name .
Services management console I get this entry in Description column.
Failed to Read Description Error code 2
How to delete them otherwise new installtion is showing problem.


texts: Can't create table or database in sql server as permission denied in database master . (Logged in with windows authentication )

texts:
Can't create table or database in sql server as permission denied in database master.
Log in authentication is Windows authentication (./SQLEXPRESS)


texts: MySQL replication - slave hangs during ALTER table or BULK update - v5.1.71

texts:
We are facing the above mention issue. In this case if we try to STOP SLAVE then it hangs and we even can to restart DB.

As I search in google for the same it looks like a bug. Id 38205 in V5.1.

My question is if we upgrade to 5.6, is it fixed and if yes what should be the exact version of 5.6.

Thanks
Shyama Prasad


texts:
Hello i am having a weird problem currently i have the following query

SELECT *
FROM `txt_campaign_message` `cm`
         LEFT JOIN `txt_campaign` `c` ON c.id = cm.campaign_id
         LEFT JOIN `txt_message` `m` ON m.id = cm.message_id
         LEFT JOIN `txt_campaign_subscriber` `cs` ON cs.campaign_id = cm.campaign_id
WHERE ((`c`.`user_id` = 2) AND (`m`.`message_type` = 'blast') AND (`c`.`active` = 1))
  AND (NOT (`cs`.`subscriber_id` IS NULL))
  AND cs.subscriber_id = (
    (IF
        (cm.date_range_from IS NOT NULL,
         (SELECT cs1.subscriber_id
           from `txt_campaign_subscriber` cs1
           WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from) AND DATE(cm.date_range_to)
         ),
         (cs.subscriber_id)
        )
    )
  )
  AND (`cm`.`campaign_id` = 1386)

which gave me an error

  Sub-query returns multiple rows

and i changed the query part AND cs.subscriber_id = (IF to use IN rather than = before the condition like below

SELECT *
FROM `txt_campaign_message` `cm`
         LEFT JOIN `txt_campaign` `c` ON c.id = cm.campaign_id
         LEFT JOIN `txt_message` `m` ON m.id = cm.message_id
         LEFT JOIN `txt_campaign_subscriber` `cs` ON cs.campaign_id = cm.campaign_id
WHERE ((`c`.`user_id` = 2) AND (`m`.`message_type` = 'blast') AND (`c`.`active` = 1))
  AND (NOT (`cs`.`subscriber_id` IS NULL))
  AND cs.subscriber_id IN (
    (IF
        (cm.date_range_from IS NOT NULL,
         (SELECT cs1.subscriber_id
           from `txt_campaign_subscriber` cs1
           WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from) AND DATE(cm.date_range_to)
         ),
         (cs.subscriber_id)
        )
    )
  )
  AND (`cm`.`campaign_id` = 1386)

But the error remains the same, to my surprise if i remove the if condition and just use the sub query all runs fine

SELECT *
FROM `txt_campaign_message` `cm`
         LEFT JOIN `txt_campaign` `c` ON c.id = cm.campaign_id
         LEFT JOIN `txt_message` `m` ON m.id = cm.message_id
         LEFT JOIN `txt_campaign_subscriber` `cs` ON cs.campaign_id = cm.campaign_id
WHERE ((`c`.`user_id` = 2) AND (`m`.`message_type` = 'blast') AND (`c`.`active` = 1))
  AND (NOT (`cs`.`subscriber_id` IS NULL))
  AND cs.subscriber_id IN (
         (SELECT cs1.subscriber_id
           from `txt_campaign_subscriber` cs1
           WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from) AND DATE(cm.date_range_to)
         )
  )
  AND (`cm`.`campaign_id` = 1386)

and when i add the condition back it starts throwing the same error, what am i doing wrong or what do i need to change if i am using the IF condition for the sub-query, i already changed the = to IN as i needed multiple rows to be compared.

I am using MariaDB (10.3) where i am facing the problem.


texts: InnoDB Tablespace critical error in great need of a fix

texts:
Link to screenshot : http://www.nouvellesduquartier.com/i/1/p/Munin_%20Critical_MySql_InnoDB_.JPG

Question: Could the error shown on the screenshot be the reason why my site is very slow?


texts: ERROR 1067 (42000) at line 7: Invalid default value for 'LAST_UPDATED'

texts:
Running MySQL 5.5, got the error above when I tried to import a .sql file

I've checked the SQL_MODE of my server and nothing similar to 'NO_ZERO_DATE' is found

As well, I've took out the text of 'STRICT_MODE' from my my.ini file and restarted the server, yet this error still persists

Here's the .sql file being imported

CREATE TABLE  `street_segment` (
  `ID` int(8) NOT NULL,
  `PROVINCE_CODE` varchar(2) NOT NULL,
  `POSTAL_CODE` varchar(6) NOT NULL,
  `MUNICIPALITY_NAME` varchar(30) default NULL,
  `STATUS` varchar(16) NOT NULL,
  `LAST_UPDATED` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `DIRECTORY_AREA_NAME` varchar(30) default NULL,
  `STREET_NAME` varchar(255) default NULL,
  `STREET_TYPE_CODE` varchar(6) default NULL,
  `STREET_DIRECTION_CODE` varchar(2) default NULL,
  `ADDRESS_SEQ_CODE` int(8) default NULL,
  `STREET_ADDRESS_FROM_NUMBER` varchar(6) default NULL,
  `STREET_ADDRESS_TO_NUMBER` varchar(6) default NULL,
  `NUMBER_SUFFIX_FROM` varchar(6) default NULL,
  `NUMBER_SUFFIX_TO` varchar(6) default NULL,
  `SUITE_NUMBER_FROM` varchar(6) default NULL,
  `SUITE_NUMBER_TO` varchar(6) default NULL,
  `STREET_NAME_ACCENT_F` int(1) default 0,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


texts: Database design for conditional detail table

texts:
I have an expense management system for monthly expenses. Expenses are of two types: one-time monthly expenses, and daily expenses for each day of month. I have created the following tables:

setupExpenses (ExpenseID, Title, IsDaily)
ExpenseMaster (ExpenseMasterID, Month, EmployeeID)
ExpenseDetails (ExpDetID, ExpenseMasterID, ExpenseID, Date, Amount)
ExpenseDetailsDaily(ID, ExpDetID, Date, Amount)

if isdaily is true in setupExpenses then an entry is made in the 3rd table otherwise I have entries in the other two transaction tables. Any better suggesstion is appreciated. This design doesn't feel right to me.


texts: How to do a case-insensitive LIKE in a case-sensitive database?

texts:
My vendor requires the data warehouse database to be case sensitive, but I need to do case-insensitive queries against it.

In a case-sensitive database, how would you write this to be case-insensitive?

    Where Name like '%hospitalist%'


texts: MySQL service stuck at starting after increasing 'innodb_log_file_size'

texts:
Cross posting this question here, since I haven't gotten an answer there: https://superuser.com/questions/1476105/mysql-service-stuck-at-starting-after-increasing-innodb-log-file-size

Good day everyone, We are trying to install the 'ESET Enterprise Inspector' on a server, which requires mysql. we installed MySQL Server 8.0 in the requirement page it says we have to use these parameters:

But when we try to start the mysql service, the progress bar goes to end (for two minutes) and it says "the service did not respond to the start or control request in a timely fashion" and it is stuck in starting status. the eset installer doesn't continue to install if we haven't used those parameters. The server has 32G of ram and we use innodb_buffer_pool_size=26G and innodb_log_file_size=13G

Does anyone know why the service doesn't start? Here is the my.ini file. Thanks in advance.


texts:
My applications often need an accurate but perhaps imperfect row count. Billion-row tables often get frequent writes, so "perfect" is a matter of definition and timing.

Microsoft SQL keeps very good metadata, allowing a query such as:

SELECT SUM(rows) FROM sys.partitions
WHERE object_id = object_id('TABLE_NAME')
AND index_id < 2;

(There are other methods with different metadata). This provides a nearly perfect row count, perhaps omitting certain in-progress transactions or other minutiae. Such techniques work for all but the most demanding situations and are practically instantaneous.

In Oracle, we can use statistics:

SELECT num_rows
FROM all_tables
WHERE table_name = 'TABLE_NAME'

This is unreliable because, if statistics have been gathered at all, they are often out of date depending on DBA policy.

I can sacrifice significant accuracy for some improvement in speed using sampling:

SELECT COUNT(*) * 1000 rc_sampled FROM lot_size SAMPLE(.1) SEED(42)

However this is inaccurate by design, still quite slow (115 seconds on a 500M row test), and is almost useless when the application does not already have a row count estimate. Running that SQL on a table with 800 rows is like asking, "what is the price of that candy bar, give or take $75.00?")

Does Oracle provide a practical means of getting an accurate, fast row count for arbitrary tables, such as that which Microsoft SQL and others provides?


texts: Query - Match Name to Multiple Unique Types

texts:
I'm trying to create an Access 2013 database query to display all insurance policy holders that have more than one type of insurance. I am not interested in policy holders that have only one type of insurance. If a policy holder has more than one type of insurance I need a list showing the policy holder “NAME” and each unique insurance “TYPE”.

The database table is named “INSURANCE” and contains a large number of record fields but I need to isolate only two record fields. Here is a sample of 15 records:

So, in the above example, I am not interested in “Jones” or “Miller” since they have only a single type of insurance. Since “Smith’, “Brown”, "Davis" and "Wilson" have multiple types of insurance I need a list showing their names and each unique type of insurance. For the above example, the query would produce the following result:

Thank you for your help.


texts: Compatibility level when upgrading a SQL Server 2005 database to SQL Server 2012

texts:
I would like to move my databases which are currently running on SQL Server 2005 to SQL Server 2012, using the Copy Database Wizard in SQL Server Management Studio.

Should I first set the database compatibility level to SQL Server 2012 (while the database still exists on SQL Server 2005) or should I set the compatibility level to SQL Server 2005 after I've moved the database?


texts: How does MySQL determine the 'index first key' and 'index last key' with indexes?

texts: In other words, how does MySQL find the result range with indexes on the first step when it's executing a select query?


texts: Msg 39011 SQL Server was unable to communicate with the LaunchPad service

texts:
I was playing with the new "R" scripting in SQL Server 2016 CTP 30. Finally got it working, thanks to this site.

I've had a problem that I'm sure others will run into as well.

All of a sudden the "R" scripts no longer worked, they returned this error:

  Msg 39011, Level 16, State 1, Line 1
  SQL Server was unable to communicate with the LaunchPad service. Please verify the configuration of the service.


texts: Availability Group - how much data was lost after forced failover

texts:
Let's consider a SQL Server AlwaysOn cluster with two nodes in asynchronous replication.
Is there any way to calculate how much data was lost after forced failover?
I mean in terms of time, to be able to know "I lost 1 hour of data or 1 minute".
I have considered checking LSNs but I don't know how to translate them into datetime.


texts: Reusing the same connection to read uncommited transaction

texts:
My question is similar to: how to spy on a transaction that is not yet committed in postgres for debugging
What I tried:

I start a pgbouncer instance, and set the max-db-connection to 1
From my node application, i connect to pg-bouncer
I BEGIN a transaction, and inserted a few rows, i.e: INSERT INTO USERS VALUES ('john')
I end the application without COMMIT
I connect to pgbouncer from another client (i.e: Datagrip)

Expected: I can continue from the uncommitted transaction, and SELECT * FROM USERS WHERE name = 'john', would retrieve the rows.
But what happens is that PgBouncer would close the connection ( closing because: unclean server (age=0s)) when a client connects, since it knows that there is an uncommited transaction. Is there a way to make this hack work with/without using PgBouncer?


texts: Will performance and speed improve when retrieving data from a .txt file instead of the Database?

texts:
I have a site that averages 40k page views a day and I sometimes hit around 100 concurrent users at any given time.

When a user enters the landing page of the site, the database is called to retrieve 6 random items and they are displayed on the webpage.

The database table has nearly 10k rows and it's using a fairly simplistic statement:

SELECT id, name FROM table ORDER BY RAND() LIMIT 6

Even when the landing page is cached, it still calls a .php file that still pulls 6 random items.

The SQL query generally takes around 0.0137 seconds to execute and currently isn't using an Index.

Today I changed it so that the database is called once per hour trough a cron job and the results are stored in a .txt file. The file is then read when a user hits the landing page and the contents are displayed.

I'm having difficulty measuring a speed improvement as I have no methodology to test with. I'm mainly doing this to try reduce CPU usage on the website as a whole.

I have many more live calls that I can convert over to storing in a .txt file but my question is:

Would it be faster to set up an index on the database and continually call it for each concurrent user or should I proceed with writing to a .txt file once per hour and calling from that?


texts: Steps to determine why NT AUTHORITY\SYSTEM has permission on one database, but not another

texts:
Have a service running under NT AUTHORITY\SYSTEM (Local System) and accessing local SQL Server database using Windows auth.

On one database they have permission to read/write - on other database (on local database server & same instance) appears to be read only permission.

How would you go about troubleshooting this and determining what is the cause of the different permissions?


texts:
I am trying to select names of employees who were born in 1985.

I am doing this but it is giving me an error.

SQL> select fname, lname, count(E#) as "total"
  2  extract(year from date, dob) as "year"
  3  from employee
  4  where "year"=1985;
extract(year from date, dob) as "year"
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected

SQL> select fname, lname, count(E#) as "total"
  2  from employee
  3  where extract(year from date, dob) =1985
  4  group by fname, lname;
where extract(year from date, dob) =1985
                        *
ERROR at line 3:
ORA-00936: missing expression


texts: Architecting table with multiple paths to common parent tables

texts:
I'm wondering how to architect a database when there are multiple paths (via foreign keys) to a common parent table.

For example I have the following tables:

Table: Students
PK StudentId

Table: Classes
PK: ClassId

Table: ClassStudents
PK: ClassStudentId:
FK: StudentId
FK: ClassId

Table: ClassAssignments
PK: ClassAssignmentId
FK: ClassId
FK: Assignment

Now I'm going to create a new table that store Assignments submissions from each student. What's the best way to structure it such that a student from another class can't get added to an assignment from this class?

TABLE: ClassStudentAssignments
FK: ClassAssignmentId
FK: ClassStudentId

Is there a way to handle this in the database design or would it just have to be done via the business logic?

Thanks in advance for any help.


texts: Two key attributes in an EER, what does it mean?

texts:
I'm currently drawing an EER diagram and stumbled across this problem:
Let's say I'm modeling a workplace where all employee's uniquely can be identified by their name (ie no employees share the exact same name) and their employee number. Is this a correct way to model it?

I'm not really sure on whether this is an ok design. Does this design say that Name and Emp_nr together form a unique Employee, or does it say that Name and Emp_nr independently from each other can identify my Employee? I've been searching everywhere and couldn't find a single example on where 2 key attributes is used.


texts: why the “size” field of a collection stat is larger than “storageSize” or totalSize of a collection in mongodb. “count” : 1552400

texts:
"count" : 1552400, "size" : 1571028800.0, "avgObjSize" : 1012, "storageSize" : 229552128,

from my understanding of the above stats, "avgObjSize" gives the size of a document in the collection and "size" gives the sum size of all the documents(no index size included) in the collection. but when i computed the total size of a collection 264282112 bytes. this is less than "size" 1571028800 in bytes. storage engine i am using here is wiredTiger and block compresses "snappy " is enabled.

can anyone please explain when the document stored to disk, does it store as compressed or does it store with the size of "avgObjSize" (in my case 1012 bytes).


texts: What are the SQL Server 2012 versions of FIRST() and LAST()?

texts:
I have a table with a value column.  I want to calculate the last row minus the first row, as shown here:

 id      value
  1       10
  2       45
  3       65
  4       95
  .       .
  .       .
  .       .
 500     200

I want to obtain 200 - 10 = 190

I've tried to use the below command in SQL Server 2012, however LAST and FIRST don't work.

SELECT LAST(Value) - FIRST(Value) FROM Counter;

What is the syntax for this command in SQL Server?


texts: Secure way to connect: Windows Authentication or SQL Server Authentication and why?

texts:
Which is the best way to create a connection string for SQL Server?

Should I be using a trusted connection string (windows authentication) or an untrusted connection string (sql server authentication) and why?

My concern is to make my connection string more secure, so it does not get compromised by an attack.

I have read on many forums that Windows Authentication is more secure, and if we use SQL Server Authentication then the user id and password could be sniffed over the network.


texts: Design - duplicating properties vs not allowing delete

texts:
I'm designing a database for a mobile app CMS that contains user data. These objects are Address and Job (a job can have 1 address and an address can have many jobs). The first version of a similar project basically inherited the address properties in the job, which would allow the user to delete the address while maintaining job properties. This essentially results in duplicate columns (both objects have street_address, city, state/province, country).

I'm curious about input as to whether the better design and experience is to either not allow object deletion or use a flag to indicate delete status and basically hide those from the user. This would allow the job to maintain its address foreign key even after the address is "deleted".


texts: How should I limit a table to N rows, discarding oldest?

texts:
I have a SQL Server table which I use to store events.

I only want the table to contain the 1000 most recently added rows, when row 1001 is inserted, row 1 should be deleted.

I have both a DateCreatedUTC column and an autonumber column.... I could write a query to execute a delete and trigger it on INSERT but I don't know that this is the most appropriate solution.